Tech
Forums
Jobs
Books
Events
Videos
Conference
Annual Conference
Bcrypt
Ai Conference
Live
More
Interviews
Certification
Training
Career
Members
News
Blogs
Contribute
An Article
A Blog
A Video
An Ebook
An Interview Question
Register
Login
4
Answers
Transactions and stored proc in SQL SERVER 2014
Ask a question
Ask a question
Upload Source Code:
Select only zip and rar file.
Hold On
7y
251
1
Reply
Can someone guide/show me how to make a transaction in sql server and how to call that in my code behind. The details are presented below.
I created a stored procedure as shown below:
ALTER
PROCEDURE
[dbo].[spInsertCashierSalesDetails_V2_From_Underwriting]
-- Add the parameters for the stored procedure here
(
@ID
int
,
@TransactionNumber nvarchar(50),
@CustomerNo nvarchar(50),
@CustomerName nvarchar(50),
@PolicyCINo nvarchar(50),
@MotorCINo nvarchar(50),
@ProductType nvarchar(50),
@COA_Code nvarchar(50),
@Debit
decimal
(18,2),
@Credit
decimal
(18,2),
@TransactionType nvarchar(50),
@AgentID nvarchar(50),
@Username nvarchar(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET
NOCOUNT
ON
;
-- Insert statements for procedure here
INSERT
INTO
[dbo].[CashierSalesDetails_V2]
(
[TransactionNumber]
,[CustomerNo]
,[CustomerName]
,[PolicyCINo]
,[MotorCINo]
,[ProductType]
,[COA_Code]
,[Debit]
,[Credit]
,[TransactionType]
,[AgentID]
,[Username])
VALUES
(
@TransactionNumber,
@CustomerNo,
@CustomerName,
@PolicyCINo,
@MotorCINo,
@ProductType,
@COA_Code,
@Debit,
@Credit,
@TransactionType,
@AgentID,
@Username)
END
In my code behind in C#, I called this sp
public
int
InsertCashierDetailsv2_FromUnderwriting(CashierDetailsTableV2 cashierDetailsTable)
{
using
(SqlConnection con = DBConnection.GetDbCon())
{
SqlCommand cmd =
new
SqlCommand(
"spInsertCashierSalesDetails_V2_From_Underwriting"
, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
try
{
cmd.Parameters.AddWithValue(
"@ID"
, cashierDetailsTable.ID);
cmd.Parameters.AddWithValue(
"@TransactionNumber"
, cashierDetailsTable.TransactionNumber);
cmd.Parameters.AddWithValue(
"@CustomerNo"
, cashierDetailsTable.CustomerNo);
cmd.Parameters.AddWithValue(
"@CustomerName"
, cashierDetailsTable.CustomerName);
cmd.Parameters.AddWithValue(
"@PolicyCINo"
, cashierDetailsTable.PolicyCINo ?? (
object
)DBNull.Value);
cmd.Parameters.AddWithValue(
"@MotorCINo"
, cashierDetailsTable.MotorCINo ?? (
object
)DBNull.Value);
cmd.Parameters.AddWithValue(
"@ProductType"
, cashierDetailsTable.ProductType);
cmd.Parameters.AddWithValue(
"@COA_Code"
, cashierDetailsTable.COA_Code);
cmd.Parameters.AddWithValue(
"@Debit"
, cashierDetailsTable.Debit);
cmd.Parameters.AddWithValue(
"@Credit"
, cashierDetailsTable.Credit);
cmd.Parameters.AddWithValue(
"@TransactionType"
, cashierDetailsTable.TransactionType);
cmd.Parameters.AddWithValue(
"@AgentID"
, cashierDetailsTable.AgentID);
cmd.Parameters.AddWithValue(
"@Username"
, cashierDetailsTable.Username);
return
cmd.ExecuteNonQuery();
}
catch
{
throw
;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
}
I also created 4 methods to insert almost the same data except for the COA_Code, Debit and Credit as shown below:
private
void
InsertCashierDetailsCOAAR()
{
CashierDetailsTableV2 cashierDetails =
new
CashierDetailsTableV2();
CashierDetailsDA insertCashierDetails =
new
CashierDetailsDA();
try
{
cashierDetails.TransactionNumber = lblTransactionNumber.Text;
cashierDetails.CustomerNo = txtCustomerNo.Text;
cashierDetails.CustomerName = txtCustomerName.Text;
cashierDetails.MotorCINo = ddlMotorCI.SelectedValue;
cashierDetails.ProductType = txtProductType.Text;
cashierDetails.COA_Code = ddlCOAAR.SelectedItem.Text;
cashierDetails.Debit = Convert.ToDecimal(txtTotalPrem.Text);
//insurance premium
cashierDetails.TransactionType = lblTransactionType.Text;
cashierDetails.AgentID = lblAgentCode.Text;
cashierDetails.Username = lblUserName.Text;
insertCashierDetails.InsertCashierDetailsv2_FromUnderwriting(cashierDetails);
InsertCashierDetailsCOAIP();
}
catch
(Exception ex)
{
throw
ex;
}
}
private
void
InsertCashierDetailsCOAIP()
{
CashierDetailsTableV2 cashierDetails =
new
CashierDetailsTableV2();
CashierDetailsDA insertCashierDetails =
new
CashierDetailsDA();
try
{
cashierDetails.TransactionNumber = lblTransactionNumber.Text;
cashierDetails.CustomerNo = txtCustomerNo.Text;
cashierDetails.CustomerName = txtCustomerName.Text;
cashierDetails.MotorCINo = ddlMotorCI.SelectedValue;
cashierDetails.ProductType = txtProductType.Text;
cashierDetails.COA_Code = ddlCOAIP.SelectedItem.Text;
cashierDetails.Credit = Convert.ToDecimal(txtNetPremium2.Text);
//insurance premium
cashierDetails.TransactionType = lblTransactionType.Text;
cashierDetails.AgentID = lblAgentCode.Text;
cashierDetails.Username = lblUserName.Text;
insertCashierDetails.InsertCashierDetailsv2_FromUnderwriting(cashierDetails);
InsertCashierDetailsCOAOT();
}
catch
(Exception ex)
{
throw
ex;
}
}
private
void
InsertCashierDetailsCOAOT()
{
CashierDetailsTableV2 cashierDetails =
new
CashierDetailsTableV2();
CashierDetailsDA insertCashierDetails =
new
CashierDetailsDA();
try
{
cashierDetails.TransactionNumber = lblTransactionNumber.Text;
cashierDetails.CustomerNo = txtCustomerNo.Text;
cashierDetails.CustomerName = txtCustomerName.Text;
cashierDetails.MotorCINo = ddlMotorCI.SelectedValue;
cashierDetails.ProductType = txtProductType.Text;
cashierDetails.COA_Code = ddlCOAOT.SelectedItem.Text;
cashierDetails.Credit = Convert.ToDecimal(txtVat.Text);
// vat
cashierDetails.TransactionType = lblTransactionType.Text;
cashierDetails.AgentID = lblAgentCode.Text;
cashierDetails.Username = lblUserName.Text;
insertCashierDetails.InsertCashierDetailsv2_FromUnderwriting(cashierDetails);
InsertCashierDetailsCOARF();
}
catch
(Exception ex)
{
throw
ex;
}
}
private
void
InsertCashierDetailsCOARF()
{
CashierDetailsTableV2 cashierDetails =
new
CashierDetailsTableV2();
CashierDetailsDA insertCashierDetails =
new
CashierDetailsDA();
try
{
cashierDetails.TransactionNumber = lblTransactionNumber.Text;
cashierDetails.CustomerNo = txtCustomerNo.Text;
cashierDetails.CustomerName = txtCustomerName.Text;
cashierDetails.MotorCINo = ddlMotorCI.SelectedValue;
cashierDetails.ProductType = txtProductType.Text;
cashierDetails.COA_Code = ddlCOARF.SelectedItem.Text;
cashierDetails.Credit = Convert.ToDecimal(txtRegistry.Text);
// registry fee
cashierDetails.TransactionType = lblTransactionType.Text;
cashierDetails.AgentID = lblAgentCode.Text;
cashierDetails.Username = lblUserName.Text;
insertCashierDetails.InsertCashierDetailsv2_FromUnderwriting(cashierDetails);
InsertCashierHeader();
}
catch
(Exception ex)
{
throw
ex;
}
}
I know that there is a better way of doing this. Thanks in advance.
Post
Reset
Cancel
Answers (
4
)
Next Recommended Forum
One by one combobox's item. Possible or not with c#?
How to make filter into combobox from Database properly