using System.Data;
using System.Data.SqlClient;
///
/// Demonstrates how to work with ADO.net objects
///
class AdoNetDemo
{
SqlConnection conn;
public AdoNetDemo()
{
// Instantiate the connection
conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
}
// call methods that demo SqlCommand capabilities
static void Main()
{
AdoNetDemo adoNetDemo = new AdoNetDemo();
Console.WriteLine();
Console.WriteLine("Categories Before Insert");
Console.WriteLine("------------------------");
// use ExecuteReader method
adoNetDemo.ReadData();
// use ExecuteNonQuery method for Insert
adoNetDemo.Insertdata();
Console.WriteLine();
Console.WriteLine("Categories After Insert");
Console.WriteLine("------------------------------");
adoNetDemo.ReadData();
// use ExecuteNonQuery method for Update
adoNetDemo.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories After Update");
Console.WriteLine("------------------------------");
adoNetDemo.ReadData();
// use ExecuteNonQuery method for Delete
adoNetDemo.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories After Delete");
Console.WriteLine("------------------------------");
adoNetDemo.ReadData();
// use ExecuteScalar method
int numberOfRecords = adoNetDemo.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records: {0}", numberOfRecords);
// Just to stop the screen
Console.ReadKey();
}
///
/// use ExecuteReader method
///
public void ReadData()
{
SqlDataReader rdr = null;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query results
rdr = cmd.ExecuteReader();
// print the CategoryName of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///
/// use ExecuteNonQuery method for Insert
///
public void Insertdata()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string insertString = @" insert into Categories (CategoryName, Description) values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///
/// use ExecuteNonQuery method for Update
///
public void UpdateData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string updateString = @"update Categories set CategoryName = 'Other' where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///
/// use ExecuteNonQuery method for Delete
///
public void DeleteData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string deleteString = @"delete from Categories where CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///
/// use ExecuteScalar method
///
///
public int GetNumberOfRecords()
{
int count = -1;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteScalar to send command
count = (int)cmd.ExecuteScalar();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return count;
}
}
