ADO Connection
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Demonstrates how to work with SqlConnection objects
/// </summary>
class SqlConnectionDemo
{
static void Main()
{
// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
SqlDataReader rdr = null;
try
{
// 2. Open the connection
conn.Open();
// 3. Pass the connection to a command object
SqlCommand cmd = new SqlCommand("select * from Customers", conn);
//
// 4. Use the connection
//
// get query results
rdr = cmd.ExecuteReader();
// print the CustomerID of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if (rdr != null)
{
rdr.Close();
}
// 5. Close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
Command CRUD
using System;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Demonstrates how to work with SqlCommand objects
/// </summary>
class SqlCommandDemo
{
SqlConnection conn;
public SqlCommandDemo()
{
// Instantiate the connection
conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
}
// call methods that demo SqlCommand capabilities
static void Main()
{
SqlCommandDemo scd = new SqlCommandDemo();
Console.WriteLine();
Console.WriteLine("Categories Before Insert");
Console.WriteLine("------------------------");
// use ExecuteReader method
scd.ReadData();
// use ExecuteNonQuery method for Insert
scd.InsertData();
Console.WriteLine();
Console.WriteLine("Categories After Insert");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery method for Update
scd.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories After Update");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery method for Delete
scd.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories After Delete");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteScalar method
int numberOfRecords = scd.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records: {0}", numberOfRecords);
}
/// <summary>
/// use ExecuteReader method
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Insert
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Update
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteNonQuery method for Delete
/// </summary>
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();
}
}
}
/// <summary>
/// use ExecuteScalar method
/// </summary>
/// <returns>number of records</returns>
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 ExecuteNonQuery to send command
count = (int)cmd.ExecuteScalar();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return count;
}
}
DataReader
using System;
using System.Data;
using System.Data.SqlClient;
namespace Lesson04
{
class ReaderDemo
{
static void Main()
{
ReaderDemo rd = new ReaderDemo();
rd.SimpleRead();
}
public void SimpleRead()
{
// declare the SqlDataReader, which is used in
// both the try block and the finally block
SqlDataReader rdr = null;
// create a connection object
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
// create a command object
SqlCommand cmd = new SqlCommand(
"select * from Customers", conn);
try
{
// open the connection
conn.Open();
// 1. get an instance of the SqlDataReader
rdr = cmd.ExecuteReader();
// print a set of column headers
Console.WriteLine(
"Contact Name City Company Name");
Console.WriteLine(
"------------ ------------ ------------");
// 2. print necessary columns of each record
while (rdr.Read())
{
// get the results of each column
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
// print out the results
Console.Write("{0,-25}", contact);
Console.Write("{0,-20}", city);
Console.Write("{0,-25}", company);
Console.WriteLine();
}
}
finally
{
// 3. close the reader
if (rdr != null)
{
rdr.Close();
}
// close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
DataSet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
class DisconnectedDataForm : Form
{
private SqlConnection conn;
private SqlDataAdapter daCustomers;
private DataSet dsCustomers;
private DataGrid dgCustomers;
private const string TableName = "Customers";
// initialize form with DataGrid and Button
public DisconnectedDataForm()
{
// fill dataset
InitData();
// set up datagrid
dgCustomers = new DataGrid();
dgCustomers.Location = new Point(5, 5);
dgCustomers.Size = new Size(
this.ClientRectangle.Size.Width - 10,
this.ClientRectangle.Height - 50);
dgCustomers.DataSource = dsCustomers;
dgCustomers.DataMember = TableName;
// create update button
Button btnUpdate = new Button();
btnUpdate.Text = "Update";
btnUpdate.Location = new Point(
this.ClientRectangle.Width/2 - btnUpdate.Width/2,
this.ClientRectangle.Height - (btnUpdate.Height + 10));
btnUpdate.Click += new EventHandler(btnUpdateClicked);
// make sure controls appear on form
Controls.AddRange(new Control[] { dgCustomers, btnUpdate });
}
// set up ADO.NET objects
public void InitData()
{
// instantiate the connection
conn = new SqlConnection(
"Server=(local);DataBase=Northwind;Integrated Security=SSPI");
// 1. instantiate a new DataSet
dsCustomers = new DataSet();
// 2. init SqlDataAdapter with select command and connection
daCustomers = new SqlDataAdapter(
"select CustomerID, CompanyName from Customers", conn);
// 3. fill in insert, update, and delete commands
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daCustomers);
// 4. fill the dataset
daCustomers.Fill(dsCustomers, TableName);
}
// Update button was clicked
public void btnUpdateClicked(object sender, EventArgs e)
{
// write changes back to DataBase
daCustomers.Update(dsCustomers, TableName);
}
// start the Windows Form
static void Main()
{
Application.Run(new DisconnectedDataForm());
}
}
Adding Parameters to Queries
using System;
using System.Data;
using System.Data.SqlClient;
class ParamDemo
{
static void Main()
{
// conn and reader declared outside try
// block for visibility in finally block
SqlConnection conn = null;
SqlDataReader reader = null;
string inputCity = "London";
try
{
// instantiate and open connection
conn = new
SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();
// don't ever do this!
// SqlCommand cmd = new SqlCommand(
// "select * from Customers where city = '" + inputCity + "'";
// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
"select * from Customers where city = @City", conn);
// 2. define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@City";
param.Value = inputCity;
// 3. add new parameter to command object
cmd.Parameters.Add(param);
// get data stream
reader = cmd.ExecuteReader();
// write each record
while(reader.Read())
{
Console.WriteLine("{0}, {1}",
reader["CompanyName"],
reader["ContactName"]);
}
}
finally
{
// close reader
if (reader != null)
{
reader.Close();
}
// close connection
if (conn != null)
{
conn.Close();
}
}
}
}
Transaction
http://www.informit.com/articles/article.aspx?p=29843&seqNum=3
Working With ADO.NET Transactions
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
cmd.Transaction = myTrans
'Show Database before modifications
GetAndBindData(datagrid1)
'Execute Database Change #1
ExecuteSQL(cmd, "UPDATE Fruits SET Quantity = 4 " + _
"WHERE Name = 'Apple'")
'Execute Database Change #2
Try
ExecuteSQL(cmd, "DELETE FROM FRUITS")
Throw New Exception("A random horrible database error")
myTrans.Commit()
Catch
myTrans.Rollback()
End Try
'Requery to make sure changes are gone
GetAndBindData(datagrid7)
conn.Close()
