using System;
using System.Data;
using System.Data.SqlClient;

   class SqlDemo {
      static void Main(){
         string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";

         SqlConnection cn = new SqlConnection(connString);
         cn.Open();

         SqlTransaction objTrans = cn.BeginTransaction();

         try 
         {      
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Transaction = objTrans;

            cmd.CommandText = "DELETE Employee WHERE Id = 222";
            cmd.ExecuteNonQuery();

            objTrans.Commit();
        
            Console.WriteLine("Transaction Committed
" );
         }
         catch (System.Data.SqlClient.SqlException ex)
         {
            objTrans.Rollback();
            Console.WriteLine("Error - TRANSACTION ROLLED BACK
" + ex.Message);
         }
         catch (System.Exception ex)
         {
            Console.WriteLine("System Error
" + ex.Message);
         }
         finally
         {
            cn.Close();
         }
      }
   }

           
          

   

/*
Mastering Visual C# .NET
by Jason Price, Mike Gunderloy

Publisher: Sybex;
ISBN: 0782129110
*/

/*
  Example23_4.cs illustrates the use of transactions
*/

using System;
using System.Data;
using System.Data.SqlClient;

public class Example23_4
{

  public static void Main()
  {

    // formulate a string containing the details of the
    // database connection
    string connectionString =
      "server=localhost;database=Northwind;uid=sa;pwd=sa";

    // create a SqlConnection object to connect to the
    // database, passing the connection string to the constructor
    SqlConnection mySqlConnection =
      new SqlConnection(connectionString);

    // open the database connection using the
    // Open() method of the SqlConnection object
    mySqlConnection.Open();

    // step 1: create a SqlTransaction object and start the transaction
    // by calling the BeginTransaction() method of the SqlConnection
    // object
    SqlTransaction mySqlTransaction =
      mySqlConnection.BeginTransaction();

    // step 2: create a SqlCommand object to hold a SQL statement
    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    // step 3: set the Transaction property for the SqlCommand object
    mySqlCommand.Transaction = mySqlTransaction;

    // step 4: formulate a string containing the first INSERT statement
    string insertString =
      "INSERT INTO Customers (" +
      "  CustomerID, CompanyName, ContactName, Address" +
      ") VALUES (" +
      "  'T2COM', 'T2 Company', 'Jason Price', '1 Main Street'" +
      ")";

    // step 5: set the CommandText property of the SqlCommand object to
    // the INSERT string
    mySqlCommand.CommandText = insertString;

    // step 6: run the first INSERT statement
    Console.WriteLine("Running first INSERT statement");
    mySqlCommand.ExecuteNonQuery();

    // step 7: formulate a second INSERT statement
    insertString =
      "INSERT INTO Orders (" +
      "  CustomerID" +
      ") VALUES (" +
      "  'T2COM'" +
      ")";

    // step 8: set the CommandText property of the SqlCommand object to
    // the second INSERT string
    mySqlCommand.CommandText = insertString;

    // step 9: run the second INSERT statement
    Console.WriteLine("Running second INSERT statement");
    mySqlCommand.ExecuteNonQuery();

    // step 10: commit the transaction using the Commit() method
    // of the SqlTransaction object
    Console.WriteLine("Committing transaction");
    mySqlTransaction.Commit();

    // close the database connection using the Close() method
    // of the SqlConnection object
    mySqlConnection.Close();

  }

}


           
          

   

using System;
using System.Data;
using System.Data.SqlClient;

class ExecuteTransaction
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    mySqlConnection.Open();

    SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction();

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.Transaction = mySqlTransaction;

    mySqlCommand.CommandText =
      "INSERT INTO Employee (" +
      "  ID, FirstName" +
      ") VALUES (" +
      "  10, 'Jason'" +
      ")";

    Console.WriteLine("Running first INSERT statement");
    mySqlCommand.ExecuteNonQuery();

    mySqlCommand.CommandText =
      "INSERT INTO Employee (" +
      "  ID, FirstName" +
      ") VALUES (" +
      "  11, 'Jason'" +
      ")";

    Console.WriteLine("Running second INSERT statement");
    mySqlCommand.ExecuteNonQuery();

    Console.WriteLine("Committing transaction");
    mySqlTransaction.Commit();

    mySqlConnection.Close();
  }
}



           
          

   
 
using System;
using System.Data;
using System.Data.SqlClient;

class Savepoint {
    public static void Main() {
        SqlConnection mySqlConnection =
          new SqlConnection(
            "server=localhost;database=Northwind;uid=sa;pwd=sa"
          );
        mySqlConnection.Open();

        SqlTransaction mySqlTransaction = mySqlConnection.BeginTransaction();

        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
        mySqlCommand.Transaction = mySqlTransaction;

        mySqlCommand.CommandText =
          "INSERT INTO Customers ( " +
          "  CustomerID, CompanyName " +
          ") VALUES ( " +
          "  'J8COM', 'J8 Company' " +
          ")";
        int numberOfRows = mySqlCommand.ExecuteNonQuery();
        Console.WriteLine("Number of rows inserted = " + numberOfRows);
        mySqlTransaction.Save("SaveCustomer");
        mySqlCommand.CommandText = "INSERT INTO Orders (CustomerID ) VALUES ( 'J8COM' )";
        numberOfRows = mySqlCommand.ExecuteNonQuery();
        Console.WriteLine("Number of rows inserted = " + numberOfRows);

        mySqlTransaction.Rollback("SaveCustomer");

        mySqlCommand.CommandText =
              "SELECT CustomerID, CompanyName " +
              "FROM Customers " +
              "WHERE CustomerID = 'J8COM'";
        SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
        while (mySqlDataReader.Read()) {
            Console.WriteLine("mySqlDataReader[" CustomerID"] = " +
              mySqlDataReader["CustomerID"]);
            Console.WriteLine("mySqlDataReader[" CompanyName"] = " +
              mySqlDataReader["CompanyName"]);
        }
        mySqlDataReader.Close();

        mySqlCommand.CommandText = "DELETE FROM Customers WHERE CustomerID = 'J8COM'";
        numberOfRows = mySqlCommand.ExecuteNonQuery();
        Console.WriteLine("Number of rows deleted = " + numberOfRows);

        mySqlTransaction.Commit();

        mySqlConnection.Close();
    }
}

    

   

using System;
using System.Data;
using System.Data.SqlClient;

   class SchemaTable
   {
      static void Main(string[] args)
      {
         string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
         string sql = @"select * from employee";
         SqlConnection conn = new SqlConnection(connString);

         try {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();

            DataTable schema = reader.GetSchemaTable();

            foreach (DataRow row in schema.Rows)
            { 
               foreach (DataColumn col in schema.Columns){
                  Console.WriteLine(col.ColumnName + " = " + row[col]);
                  Console.WriteLine("Null value allowed: " + col.AllowDBNull);
               }
            }
            reader.Close();
         } catch(Exception e) {
            Console.WriteLine("Error Occurred: " + e);
         } finally {
            conn.Close();
         }
      }  
   }



           
          

   


using System;
using System.Data;
using System.Data.SqlClient;

class SchemaOnlyCommandBehavior
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText ="SELECT ID, FirstName, LastName FROM Employee WHERE ID = 8";

    mySqlConnection.Open();

    SqlDataReader productsSqlDataReader =mySqlCommand.ExecuteReader(CommandBehavior.SchemaOnly);
    DataTable myDataTable = productsSqlDataReader.GetSchemaTable();

    foreach (DataRow myDataRow in myDataTable.Rows)
    {
      Console.WriteLine("
New column details follow:");
      foreach (DataColumn myDataColumn in myDataTable.Columns)
      {
        Console.WriteLine(myDataColumn + "= " +
          myDataRow[myDataColumn]);
        if (myDataColumn.ToString() == "ProviderType")
        {
          Console.WriteLine(myDataColumn + "= " +
            ((System.Data.SqlDbType) myDataRow[myDataColumn]));
        }
      }
    }

    productsSqlDataReader.Close();
    mySqlConnection.Close();
  }
}

           
          

   


using System;
using System.Data;
using System.Data.SqlClient;

class FillSchema
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
    mySqlCommand.CommandText =
      "SELECT ProductID, ProductName " +
      "FROM Products;" +
      "SELECT OrderID " +
      "FROM Orders;" +
      "SELECT OrderID, ProductID, UnitPrice " +
      "FROM [Order Details];";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    mySqlDataAdapter.FillSchema(myDataSet, SchemaType.Mapped);
    mySqlConnection.Close();
    myDataSet.Tables["Table"].TableName = "Products";
    myDataSet.Tables["Table1"].TableName = "Orders";
    myDataSet.Tables["Table2"].TableName = "Order Details";

    foreach (DataTable myDataTable in myDataSet.Tables)
    {
      Console.WriteLine("

Reading from the " +
        myDataTable + "DataTable:
");

      foreach (DataColumn myPrimaryKey in myDataTable.PrimaryKey)
      {
        Console.WriteLine("myPrimaryKey = " + myPrimaryKey);
      }

      foreach (Constraint myConstraint in myDataTable.Constraints)
      {
        Console.WriteLine("myConstraint.IsPrimaryKey = " + ((UniqueConstraint) myConstraint).IsPrimaryKey);
        foreach (DataColumn myDataColumn in ((UniqueConstraint) myConstraint).Columns)
        {
          Console.WriteLine("myDataColumn.ColumnName = " + myDataColumn.ColumnName);
        }
      }

      foreach (DataColumn myDataColumn in myDataTable.Columns)
      {
        Console.WriteLine("
myDataColumn.ColumnName = " + myDataColumn.ColumnName);
        Console.WriteLine("myDataColumn.DataType = " + myDataColumn.DataType);

        Console.WriteLine("myDataColumn.AllowDBNull = " + myDataColumn.AllowDBNull);
        Console.WriteLine("myDataColumn.AutoIncrement = " + myDataColumn.AutoIncrement);
        Console.WriteLine("myDataColumn.AutoIncrementSeed = " + myDataColumn.AutoIncrementSeed);
        Console.WriteLine("myDataColumn.AutoIncrementStep = " + myDataColumn.AutoIncrementStep);
        Console.WriteLine("myDataColumn.MaxLength = " + myDataColumn.MaxLength);
        Console.WriteLine("myDataColumn.ReadOnly = " + myDataColumn.ReadOnly);
        Console.WriteLine("myDataColumn.Unique = " + myDataColumn.Unique);
      }
    }
  }
}