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;
// 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)