/* 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(); } }
Author: coder
Use of a transaction
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(); } }
Set a savepoint in a transaction
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(); } }
Get table Schema
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(); } } }
How to read a table schema
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(); } }
Read schema information using the FillSchema() method of a DataAdapter object
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); } } } }
Get Column data type and name from DataColumn
using System; using System.Data; using System.Data.OleDb; public class DatabaseInfo { public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the database"); String cmd = "SELECT * FROM Employee"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.SelectCommand = new OleDbCommand(cmd, con); DataSet ds = new DataSet(); adapter.Fill(ds, "Employee"); DataTable item = ds.Tables[0]; Console.WriteLine("Table name: {0}", item.TableName); Console.WriteLine("Its columns are:"); foreach (DataColumn col in item.Columns) Console.WriteLine("{0} {1}", col.ColumnName, col.DataType); con.Close(); } }