public DataSet SaveData(DataSet oDs) { string sMethodName = "[public void SaveData(DataSet oDs)]";
//========================================================== //-- Establish local variables //========================================================== string sProcName; string sConnString = "Server=(local);Database=Northwind;Integrated Security=SSPI"; SqlDataAdapter oDa = new SqlDataAdapter(); SqlTransaction oTrn = null; SqlConnection oCn = null; SqlCommand oInsCmd = null; SqlCommand oUpdCmd = null; SqlCommand oDelCmd = null;
try { //========================================================== //-- Set up the Connection //========================================================== oCn = new SqlConnection(sConnString);
//========================================================== //-- Open the Connection and create the Transaction //========================================================== oCn.Open(); oTrn = oCn.BeginTransaction();
//========================================================== //-- Set up the INSERT Command //========================================================== sProcName = "prInsert_Order"; oInsCmd = new SqlCommand(sProcName, oCn, oTrn); oInsCmd.CommandType = CommandType.StoredProcedure; oInsCmd.Parameters.Add(new SqlParameter("@sCustomerID", SqlDbType.NChar, 5, "CustomerID")); oInsCmd.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); oInsCmd.Parameters.Add(new SqlParameter("@sShipCity", SqlDbType.NVarChar, 30, "ShipCity")); oInsCmd.Parameters.Add(new SqlParameter("@sShipCountry", SqlDbType.NVarChar, 30, "ShipCountry")); oDa.InsertCommand = oInsCmd;
//========================================================== //-- Set up the UPDATE Command //========================================================== sProcName = "prUpdate_Order"; oUpdCmd = new SqlCommand(sProcName, oCn, oTrn); oUpdCmd.CommandType = CommandType.StoredProcedure; oUpdCmd.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); oUpdCmd.Parameters.Add(new SqlParameter("@dtOrderDate", SqlDbType.DateTime, 8,"OrderDate")); oUpdCmd.Parameters.Add(new SqlParameter("@sShipCity", SqlDbType.NVarChar, 30, "ShipCity")); oUpdCmd.Parameters.Add(new SqlParameter("@sShipCountry", SqlDbType.NVarChar, 30, "ShipCountry")); oDa.UpdateCommand = oUpdCmd;
//========================================================== //-- Set up the DELETE Command //========================================================== sProcName = "prDelete_Order"; oDelCmd = new SqlCommand(sProcName, oCn, oTrn); oDelCmd.CommandType = CommandType.StoredProcedure; oDelCmd.Parameters.Add(new SqlParameter("@nOrderID", SqlDbType.Int, 4, "OrderID")); oDa.DeleteCommand = oDelCmd;
//========================================================== //-- Save all changes to the database //========================================================== oDa.Update(oDs.Tables["Orders"]);
oTrn.Commit(); oCn.Close(); } catch (DBConcurrencyException exDBConcurrency) { //======================================================= //-- Roll back the transaction //======================================================= oTrn.Rollback();
//-------------------------------------------------------- //-- May want to rethrow the Exception at this point. //-- This depends on how you want to handle the concurrency //-- issue. //-------------------------------------------------------- //-- throw(exDBConcurrency); } catch (Exception ex) { //========================================================== //-- Roll back the transaction //========================================================== oTrn.Rollback();
//-------------------------------------------------------- //-- Rethrow the Exception //-------------------------------------------------------- throw; } finally { oInsCmd.Dispose(); oUpdCmd.Dispose(); oDelCmd.Dispose(); oDa.Dispose(); oTrn.Dispose(); oCn.Dispose(); } oCn.Close(); return oDs; } |