Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Connection
The Connection is the gatekeeper to working with a data source. It has operations for opening a connection or starting and committing a local transaction, as well as a creation function to use in creating a command object to work with the data source.
The first example in Listing 1 illustrates how to insert a row into the Customers table of the Northwind database.
Listing 1 SQL Provider Insert Demo
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace ConnectedOpsDemo
{
class SqlCmdInsertDemo
{
[STAThread]
static void Main(string[] args)
{
SqlConnection conn = null;
SqlTransaction txn = null;
try
{
// open connection to the data source
conn = new SqlConnection(
"server=(local);database=Northwind;" +
"integrated security=true;");
conn.Open();
// begin a local transaction with isolation level of
//serializable
txn = conn.BeginTransaction(IsolationLevel.Serializable);
// associate the command object with the connection and
// transaction along with setting the SQL command to execute
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = txn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO Customers VALUES" +
"('DALEM','Microsoft','Dale Michalk','ADC'," +
"'101 Main Street', 'Charlotte', 'NC', '28222'," +
"'USA','980-869-5309','980-867-5309')";
int rows = cmd.ExecuteNonQuery();
Console.WriteLine("INSERT Executed with {0} rows affected.",
rows);
// if we reach this point...we should commit the local
//transaction
txn.Commit();
}
catch (SqlException se)
{
Console.WriteLine("INSERT Failed with SQL Exception: {0}"
,se.Message);
// rollback the local transaction with a SQL Exception problem
if (txn != null)
txn.Rollback();
}
finally
{
// close the connection in the face of exceptions
if (conn != null)
conn.Close();
}
}
}
}
We start by creating the Connection object and passing in a connection string to the Northwind database. The next step is opening the configured connection and creating a local transaction using the BeginTransaction method of the Connection object. The isolation level of the transaction is configurable; in this example we use Serializable to ensure the highest level of consistency.