Transactions and stored proc in SQL SERVER 2014

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.

Hi Nitin, what I'm trying to say is that from what I have in my sp is not a transaction (the one with begin trans and commit). What I would like to know is how can I insert the data in my 4 private void methods that I created using a sql transaction to the DB in such a way that when 1 private method fails, the rest will roll back. Below is the table where I would like to insert:

GO
/****** Object: Table [dbo].[CashierSalesDetails_V2] Script Date: 4/19/2017 8:33:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CashierSalesDetails_V2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [nvarchar](50) NULL,
[CustomerNo] [nvarchar](50) NOT NULL,
[CustomerName] [nvarchar](50) NULL,
[PolicyCINo] [nvarchar](50) NULL,
[MotorCINo] [nvarchar](50) NULL,
[ProductType] [nvarchar](50) NULL,
[COA_Code] [nvarchar](50) NULL,
[Debit] [decimal](18, 2) NULL,
[Credit] [decimal](18, 2) NULL,
[TransactionType] [nvarchar](50) NULL,
[AgentID] [nvarchar](50) NULL,
[Username] [nvarchar](50) NULL,
CONSTRAINT [PK_CashierSalesDetails_V2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CashierSalesDetails_V2] WITH CHECK ADD CONSTRAINT [FK_CashierSalesDetails_V2_Agents1] FOREIGN KEY([AgentID])
REFERENCES [dbo].[Agents] ([AgentID])
GO
ALTER TABLE [dbo].[CashierSalesDetails_V2] CHECK CONSTRAINT [FK_CashierSalesDetails_V2_Agents1]
GO

I'm seeking for a better way to insert my required data.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jes Sie9y ago
Thanks Nitin, I will to figure out your sample.
Nitin Sontakke9y ago
Got it.

Unfortunately, I am not having patience to go through all of your code. So let me just give you a hint and you pick-up from there.

From what I understand, you are inserting identical data few times with minor exception. If yes, here is a thought. I am NOT saying this is best solution, but I think it is a better solution.

You write a sp in which you accept all parameters, which means, common1, common2, common3, diff1, diff2, diff3, diff4, etc.

Then in sp write inserts as follows:
insert into table (col1, col2, col3...) values
(common1, common2, common3, diff1)
,(common1, common2, common3, diff2)
,(common1, common2, common3, diff3)
,(common1, common2, common3, diff4)
Hope you get the point. The above form of insert either succeeds or fails in total, just as transaction.
Who said that number of parameters to sp should match with number of columns in table?
Jes Sie9y ago
Hi Nitin, what I'm trying to say is that from what I have in my sp is not a transaction (the one with begin trans and commit). What I would like to know is how can I insert the data in my 4 private void methods that I created using a sql transaction to the DB in such a way that when 1 private method fails, the rest will roll back. Below is the table where I would like to insert:
GO

/****** Object:  Table [dbo].[CashierSalesDetails_V2]    Script Date: 4/19/2017 8:33:55 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CashierSalesDetails_V2](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TransactionNumber] [nvarchar](50) NULL,
    [CustomerNo] [nvarchar](50) NOT NULL,
    [CustomerName] [nvarchar](50) NULL,
    [PolicyCINo] [nvarchar](50) NULL,
    [MotorCINo] [nvarchar](50) NULL,
    [ProductType] [nvarchar](50) NULL,
    [COA_Code] [nvarchar](50) NULL,
    [Debit] [decimal](18, 2) NULL,
    [Credit] [decimal](18, 2) NULL,
    [TransactionType] [nvarchar](50) NULL,
    [AgentID] [nvarchar](50) NULL,
    [Username] [nvarchar](50) NULL,
CONSTRAINT [PK_CashierSalesDetails_V2] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CashierSalesDetails_V2]  WITH CHECK ADD  CONSTRAINT [FK_CashierSalesDetails_V2_Agents1] FOREIGN KEY([AgentID])
REFERENCES [dbo].[Agents] ([AgentID])
GO

ALTER TABLE [dbo].[CashierSalesDetails_V2] CHECK CONSTRAINT [FK_CashierSalesDetails_V2_Agents1]
GO
I'm seeking for a better way to insert my required data.
Nitin Sontakke9y ago
Sorry. I didn't understand the question. You started with "transaction" and ended with "there is better way to do this". Where exactly are you seeking advice?

Better leave transaction management to SQL Server, unless it is causing an issue to you right now.