TechBlog

For test purpose I did a little test in C#, where I did use ADO.NET to make a connection to my SQL Server.

The common way to access a database is to do the following:

1. Open a connection to the Database
2. Perform the Query using a recordset for the returned data
3. Extract Data needed and close the recordset
4. Close the connection

Lets take a close look on how to do it in C# ADO.NET

First you have to create a connection

  | 
 copy code |
?
 
 
string ConnStr = "Data Source=Sqlserver;Initial Catalog=Database;" +
     "Integrated Security=False;User ID=user;Password=password";
 
SqlConnection ADOconnection = new SqlConnection(ConnStr);
 
If Integrated Security is FALSE it is equal to Database Server Authentication, 
where as if Integrated Security is TRUE it is equal to Window Authentication and 
thereby User ID and Password will be ignored.

and then after wards open the connection

  | 
 copy code |
?
 
ADOconnection.Open();

now you are ready to create and execute a SQL command. First create the command:

  | 
 copy code |
?
 
 
SqlCommand ADOcommand = new SqlCommand("select * from [Customer]", ADOconnection);
 

and then execute it and return the query result into a recordset:

  | 
 copy code |
?
 
ADOreader = ADOcommand.ExecuteReader();

now you have executed the sqlcommand, got the result in the recordset ADOreader, which you now can parse through.

  | 
 copy code |
?
 
while (ADOreader.Read()) {
   String CustomerName = (String)ADOreader["Customer Name"];
}

Then finally close the recordset and the connection

  | 
 copy code |
?
 
ADOreader.Close();
ADOconnection.Close();

The Complete Project could look like this:

  | 
 copy code |
?
 
using System;
using System.Data;
using System.Data.SqlClient;
 
namespace ADO_console
{
   class Program
   {
        static void Main(string[] args)
        {
           string ConnStr = "Data Source=Sqlserver;" +
                  "Initial Catalog=Database;" +
                  "Integrated Security=false;" +
                  "User ID=user;Password=password";
 
           SqlConnection ADOconnection = new SqlConnection(ConnStr);
           SqlDataReader ADOreader = null;
 
           try
           {
               ADOconnection.Open();
               SqlCommand ADOcommand = new SqlCommand("select * from [customer]", ADOconnection);
               ADOreader = ADOcommand.ExecuteReader();
 
               while (ADOreader.Read())
               {
                   Int32 CustomerNo = (Int32)ADOreader["Customer No_"];
                   String CustomerName = (String)ADOreader["Customer Name"];
                   Console.WriteLine(CustomerNo + " " + CustomerName);
               }
           }
 
           finally
           {
               if (ADOreader != null)
              {
                  ADOreader.Close();
              }
              if (ADOconnection != null)
              {
                  ADOconnection.Close();
              }
           }
        }
   }
}