TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Jes Sie
742
1.2k
282.1k
Transactions and stored proc in SQL SERVER 2014
Apr 18 2017 3:33 AM
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.
Reply
Answers (
4
)
get the size of a text file
Can any one tell me what is Biztalk?