Integração SQL Server e C# com SqlClient e DataAdapter

Criação da Tabela no SQL Server

No banco SQL Server, execute o seguinte script para criar a tabela de CNH:

CREATE TABLE [dbo].[CNH](
    [identificador] [int] IDENTITY(1,1) NOT NULL,
    [numero] [varchar](250) NULL,
    [dataEmissao] [datetime] NULL,
    [dataValidade] [datetime] NULL,
    [categoriaCNH] [varchar](2) NOT NULL,
    [pessoa] [int] NOT NULL,
    CONSTRAINT [PK_CNH] PRIMARY KEY CLUSTERED 
    (
        [identificador] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Implementação do Código em C#

No código C#, utilize a classe SqlConnection para gerenciar as operações de banco de dados:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication5
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = GetConnectionString();

            // http://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx

            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = GetConnectionString();
                connection.Open();

                Console.WriteLine("State: {0}", connection.State);
                Console.WriteLine("ConnectionString: {0}", connection.ConnectionString);

                Insert(connection); // INSERT
                Update(connection); // UPDATE
                Delete(connection); // DELETE
                AdapterComDados(connection); // SELECT, DEPOIS PARA DATA ADAPTER
            }
        }

        /// <summary>
        /// Select e a seguir preenche com dados a partir de select
        /// </summary>
        private static void AdapterComDados(SqlConnection connection)
        {
            // http://msdn.microsoft.com/pt-br/library/bh8kx08z%28v=vs.110%29.aspx

            // Assumes that connection is a valid SqlConnection object.
            string queryString = "SELECT * FROM dbo.CNH";
            SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

            DataSet habilitados = new DataSet();
            adapter.Fill(habilitados, "CNH");
        }

        /// <summary>
        /// Exclui registro de banco
        /// </summary>
        private static void Delete(SqlConnection connection)
        {
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "DELETE cnh WHERE pessoa = 45";
            cmd.Connection = connection;

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Update de registro de banco
        /// </summary>
        private static void Update(SqlConnection connection)
        {
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "UPDATE [dbo].[CNH] SET [numero] = '66644455' WHERE pessoa = 2";
            cmd.Connection = connection;

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Insere um registro no banco
        /// </summary>
        private static void Insert(SqlConnection connection)
        {
            // http://msdn.microsoft.com/en-us/library/ms233812.aspx
            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = "INSERT CNH (NUMERO, DATAEMISSAO, DATAVALIDADE, CATEGORIACNH, PESSOA) VALUES ('7382882', '01/01/2011', '01/02/2012', 'B', '45')";
            cmd.Connection = connection;

            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Monta String de Conexão
        /// </summary>
        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code,
            // you can retrieve it from a configuration file.
            return "SERVER=127.0.0.1;Data Source=NOTE-PC;Initial Catalog=detran;" + "User Id=sa;Password=123456;";
        }
    }
}