ADO.NET

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

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

ADOconnection.Open();

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

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

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


ADOreader = ADOcommand.ExecuteReader();

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


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

Then finally close the recordset and the connection


ADOreader.Close();
ADOconnection.Close();

The Complete Project could look like this:


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();
}
}
}
}
}