Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account