How to lock SQLServer Insert transactions!!!(Locking Master Table While Detail table records Insertion)

See more:

ASP.NET

SQL-Server

HI guys i need a big help!. That is basically i used 2 tables and as tblRates and tblRateDetail. and that tblrateDetail is relay on tblRates because tblrates rateCode(autogen number) is act as a foreign key in detail table.(Because Multi user Access)

E: X

E:x in tblRates

rateCode ratename maxweight add price
100 , testrate , 2.00 , 0.50



in tblratedetails

rateCode(fk) weight price
100 , 0.5 , 10
100 , 1.0 , 12
100 , 1.5 , 14
100 , 2.0 , 16

here i mention code example :

try
                    {
                        //setting procedure properties
                        procedureName = "Add_outrateMaster";
                        cmdType = CommandType.StoredProcedure;

                        parameters.Add(new SqlParameter("@rateType ", SqlDbType.VarChar, 2));
                        parameters.Add(new SqlParameter("@rateidentity", SqlDbType.VarChar, 100));
                        parameters.Add(new SqlParameter("@cusCode", SqlDbType.VarChar, 10));
                        parameters.Add(new SqlParameter("@countryCode", SqlDbType.VarChar, 10));
                        parameters.Add(new SqlParameter("@pkgType", SqlDbType.VarChar, 10));
                        parameters.Add(new SqlParameter("@cutoff", SqlDbType.Decimal));
                        parameters.Add(new SqlParameter("@maxWeight", SqlDbType.Decimal));
                        parameters.Add(new SqlParameter("@addunitPrice", SqlDbType.Decimal));
                        parameters.Add(new SqlParameter("@rateCategory", SqlDbType.VarChar, 10));
                        parameters.Add(new SqlParameter("@isFlat", SqlDbType.Int));
                        parameters.Add(new SqlParameter("@genDate", SqlDbType.DateTime));
                        parameters.Add(new SqlParameter("@Status", SqlDbType.Int));


                        //Inserting data through stored procedure
                        parameters[0].Value = DropDownrateCategory.SelectedValue.ToString().Trim();
                        parameters[1].Value = txtrateidentity.Text.Trim().Replace("'", "''");
                        parameters[2].Value = dropdown_Customers.SelectedValue.Replace("'", "''");
                        parameters[3].Value = dropdownCountry.SelectedValue.ToString().Trim().Replace("'", "''");
                        parameters[4].Value = dropdownPkgType.SelectedValue.ToString().Trim().Replace("'", "''");
                        parameters[5].Value = Double.Parse(txtCutoff.Text.Trim());
                        parameters[6].Value = Double.Parse(rateGridview.Rows[rateGridview.Rows.Count - 1].Cells[1].Text);
                        parameters[7].Value = Double.Parse(txt2ndaddprice.Text.Trim().Replace("'", "''"));
                        parameters[8].Value = dropdownShipmentType.SelectedValue.ToString(); 
                        parameters[9].Value = isflat;
                        String dateTime = DateTime.Now.ToString();
                        parameters[10].Value = dateTime;     //@genDate datetime,
                        parameters[11].Value = 0;

                        //pass to common procedure
                        int maxcode = rb.MAXCode();
                        Boolean status = Common.executeProcedure(procedureName, cmdType, parameters);

                        if (status == true)
                        {


                            //if Master record Enterd then Detail records must be entered.
                            //if (maxcode > 0)
                            //{
                            //    maxcode = maxcode + 1;
                            //}
                            //else
                            //{

                                maxcode = rb.MAXCode();
                                //getting Max rateCode from the tblRates.

                            //}
                            parameters.Clear();

                            for (int i = 0; i < rateGridview.Rows.Count; i++)
                            {
                                procedureName = "Add_ratesDetails";
                                cmdType = CommandType.StoredProcedure;

                                parameters.Add(new SqlParameter("@rateCode ", SqlDbType.BigInt));
                                parameters.Add(new SqlParameter("@minWeight", SqlDbType.Decimal));
                                parameters.Add(new SqlParameter("@maxWeight", SqlDbType.Decimal));
                                parameters.Add(new SqlParameter("@rate", SqlDbType.Decimal));
                                parameters.Add(new SqlParameter("@Status", SqlDbType.Int));

                                parameters[0].Value = maxcode;
                                parameters[1].Value = rateGridview.Rows[i].Cells[0].Text;
                                parameters[2].Value = rateGridview.Rows[i].Cells[1].Text;

                                if (CB_KeepEqual.Checked == false & i > 0)
                                {
                                    TextBox tb1 = rateGridview.Rows[i].FindControl("txtRate") as TextBox;
                                    parameters[3].Value = tb1.Text;
                                }
                                else
                                {
                                    parameters[3].Value = rateGridview.Rows[i].Cells[2].Text;

                                }
                                parameters[4].Value = 0;
                                Boolean status1 = Common.executeProcedure(procedureName, cmdType, parameters);
                                if (status1 == true)
                                {

                                    parameters.Clear();

                                }
                                else if (status1 == false)
                                {

                                    falls = falls + 1;
                                }

                            }

Guys can you help me on that i want to Lock tblRates While One user Entered data for the tblRateDetails.because otherwise it could be a huge problem.how i should lock the Users?Thanks in Advance!!!



P:s

Stored Procedures :


tblRateDetails

PROCEDURE dbo.Add_ratesDetails
	
	(
	@rateCode bigint,
	@minWeight Numeric ( 18,2),
	@maxWeight Numeric ( 18,2),
	@rate Numeric ( 18,2),
	@Status Int
	)
	
AS
	BEGIN

	IF  (@Status = 0)


		INSERT INTO tblRateDetails (rateCode,minWeight,maxWeight,rate) VALUES (@rateCode,@minWeight,@maxWeight,@rate)



	ELSE IF (@Status = 2)


	DELETE FROM tblRateDetails WHERE rateCode = @rateCode


	END

for tblRates

PROCEDURE dbo.Add_outrateMaster
	
	(
	@rateType varchar(2),
	@rateidentity	varchar(100),
	@cusCode	varchar(10),
	@countryCode	varchar(10),
	@pkgType	varchar(10),
	@cutoff	numeric(18, 2),
	@maxWeight	numeric(18, 2),
	@addunitPrice	numeric(18, 2),
	@rateCategory	varchar(10),
	@isFlat	bit,
	@genDate datetime,
	@Status int
	)
	
AS
	BEGIN

	IF  (@Status = 0)

		INSERT INTO tblRates (rateType,rateidentity,cusCode,countryCode,pkgType,cutoff,maxWeight,addunitPrice,rateCategory,isFlat, genDate) VALUES (@rateType,@rateidentity,@cusCode,@countryCode,@pkgType,@cutoff,@maxWeight,@addunitPrice,@rateCategory,@isFlat,@genDate)


	END