Implement a business
Since we have seen classes and members, let's take a look at the basic implementation. The next code is a simple situation that uses transactions to ensure two stored procedures - a deleted inventory from the table, another increase inventory in another table, or execute, or fail.
Using system;
Using system.drawing;
Using system.collections;
Using system.componentmodel;
Using system.windows.forms;
Using system.data;
Using system.data.sqlclient;
Using system.data.sqltypes;
... Public Void SPTRANSACTION (int Partid, int Numbermoved, int sitei)
{
// CREATE AND Open the Connection.
SqlConnection conn = new sqlConnection ();
String connString = "server = SQLINSTANCE; DATABASE = TEST;"
"Integrated Security = SSPI";
CONN.CONNECTIONSTRING = ConnString;
Cn.open ();
// Create The Commands and Related Parameters.
// cmddebit debits inventory from the warehouseinventory
// Table by Calling the DebitWarehouseInventory
// stored procedure.
SQLCommand cmddebit =
New SqlCommand ("DebitwarehouseInventory", CONN);
CMDDebit.commandtype = commandtype.storedProcedure;
Cmddebit.Parameters.Add ("@ partid", sqldbtype.int, 0, "partid");
Cmddebit.Parameters ["@ partid"]. Direction =
ParameterDirection.input;
CMDDebit.Parameters.Add ("@ Debit", SqldbType.int, 0, "Quantity");
CMDDebit.Parameters ["@ Debit"]. Direction =
ParameterDirection.input;
// cmdcredit add inventory to the siteinventory
// Table by Calling the CreditsiteInventory
// stored procedure.
SQLCommand cmdcredit =
New SqlCommand ("CreditsiteIndory", CONN);
cmdcredit.commandtype = commandtype.storedProcedure;
Cmdcredit.Parameters.Add ("@ partid", sqldbtype.int, 0, "partid");
Cmdcredit.Parameters ["@ partid"]. DIRECTION = parameterDirection.input;
cmdcredit.parameters.add
("@Credit", sqldbtype.int, 0, "quantity");
Cmdcredit.Parameters ["@ Credit"]. Direction =
ParameterDirection.input;
Cmdcredit.Parameters.Add ("@ SiteID", SqldbType.int, 0, "SiteID");
Cmdcredit.Parameters ["@ SiteID"]. Direction =
ParameterDirection.input;
// Begin The Transaction and Enlist the Commands.
Sqltransaction TRAN = conn.begintransaction ();
CMDDebit.Transaction = TRAN;
Cmdcredit.transaction = TRAN;
Try
{
// Execute the commands.
CMDDebit.Parameters ["@ partid"]. value = partid;
CMDDebit.Parameters ["@ Debit"]. value = Numbermoved;
Cmddebit.executenonquery ();
Cmdcredit.Parameters ["@ partid"]. value = partid;
Cmdcredit.Parameters ["@ credit"]. value = numbermoved;
Cmdcredit.Parameters ["@ SiteID"]. value = siteid;
cmdcredit.executenonquery ();
// commit the transaction.
TRAN.COMMIT ();
}
Catch (SQLException EX)
{
// roll back the transaction.
TRAN. ROLLBACK ();
// Additional Error Handling if Needed.
}
Finally
{
// close the connection.
CONN.CLOSE ();
}
}
// commit the outer transaction.
TRAN.COMMIT ();
}
Catch (OLEDBEXCEPTION EX)
{
// roll back the transaction.
TRAN. ROLLBACK ();
// Additional Error Handling if Needed.
}
Finally
{
// close the connection.
CONN.CLOSE ();
}
}
