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;
} |
