In this post, I explained single stored procedure for insert update and delete in sql server, that contains insert and update data. You can see the stored
procedure and table script below.
private void btnSave_Click(object
sender, EventArgs e)
{
if
(con.State == ConnectionState.Closed)
con.Open();
{
cmd = new
SqlCommand("Usp_BranchDetails ", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_branchcode
", txtChitNo.Text);
cmd.Parameters.AddWithValue("@p_branchname
", txtChitName.Text);
cmd.Parameters.AddWithValue("@p_abbr ",
txtChitAmount.Text);
cmd.Parameters.AddWithValue("@p_address
", txtChitInstAmount.Text);
cmd.Parameters.AddWithValue("@p_city ", txtNumOfInst.Text);
cmd.Parameters.AddWithValue("@p_state", txtBidDay.Text);
cmd.Parameters.AddWithValue("@p_pincode", mtxtStartDate.Text);
cmd.Parameters.AddWithValue("@p_phoneno", mtxtEndDate.Text);
cmd.Parameters.AddWithValue("@p_mobileno", txtDedAmount.Text);
cmd.Parameters.AddWithValue("@p_fax", txtNonBidInst.Text);
cmd.Parameters.AddWithValue("@p_branchtype", "A");
cmd.Parameters.AddWithValue("@p_openingdate", txtRemarks.Text);
cmd.Parameters.AddWithValue("@p_inchargename", Program.userID);
cmd.Parameters.AddWithValue("@p_inchargephonno", Program.userID);
cmd.Parameters.AddWithValue("@p_status", Program.userID);
cmd.Parameters.AddWithValue("@p_creator", Program.userID);
cmd.Parameters.AddWithValue("@p_mode", mode);
cmd.Parameters.Add("@p_result", SqlDbType.Int);
cmd.Parameters["@p_result"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int
result = Convert.ToInt32(cmd.Parameters["@p_result"].Value);
if
(result == -1)
{
MessageBox.Show("Please Check the Data.", "COMPANY");
}
else
{
if
(mode == "I")
MessageBox.Show("Information
Posted Successfully.", "COMPANY");
else
MessageBox.Show("Details
Updated Successfully.", "COMPANY");
getChitDetails();
Clear();
}
}
============================================================================
This is my stored
procedure
ALTER procedure [dbo].[Usp_Ins_BranchDetails]
(
@p_branchcode varchar(10),
@p_branchname varchar(30),
@p_abbr varchar(10),
@p_address varchar(100),
@p_city varchar(30),
@p_state varchar(20),
@p_pincode varchar(8),
@p_phoneno varchar(15),
@p_mobileno varchar(15),
@p_fax varchar(15),
@P_branchtype varchar(2),
@p_openingdate datetime,
@P_inchargename varchar(50),
@P_inchargephno varchar(20),
@p_status varchar(2),
@p_creater varchar(20),
@p_mode varchar(1),
@p_result int output
)
as
set nocount on
Begin
Begin Transaction
Begin Try
if (@p_mode='I')
Begin
insert into dbo.BRANCH_MASTER(BM_BRANCH_CODE,BM_BRANCH_NAME,
BM_ADDRESS,BM_CITY,BM_STATE,BM_PINCOE_CODE,BM_PHONE,BM_MOBILE,
BM_FAX,BM_BRANCH_TYPE,BM_OPENING_DATE,BM_INCHARGE_NAME,
BM_INCHARGE_PHNO,BM_STATUS,BM_CREATED_ON,BM_CREATED_BY,BM_ABBR)
values
(@p_branchcode,@p_branchname,@p_address,@p_city,@p_state,
@p_pincode,@p_phoneno,@p_mobileno,@p_fax,@P_branchtype,
@p_openingdate,@P_inchargename,@P_inchargephno,@p_status,
getdate(),@p_creater,@p_abbr)
End
Else
Begin
Update
BRANCH_MASTER set BM_BRANCH_NAME=@p_branchname,BM_ADDRESS=@p_address,
BM_CITY=@p_city,BM_STATE=@p_state, BM_PINCOE_CODE=@p_pincode,
BM_PHONE=@p_phoneno,BM_MOBILE=@p_mobileno,BM_FAX=@p_fax,BM_ABBR=@p_abbr,
BM_BRANCH_TYPE=@P_branchtype,BM_OPENING_DATE=@p_openingdate,
BM_INCHARGE_NAME=@P_inchargename,BM_INCHARGE_PHNO=@P_inchargephno,
BM_STATUS=@p_status,BM_MODIFIED_ON=getdate(),BM_MODIFIED_BY=getdate()
where
BM_BRANCH_CODE=@p_branchcode
End
Commit Transaction
set @p_result=1
End Try
Begin Catch
Rollback Transaction
set @p_result=-1
End Catch
End
=============================================================================
This is my table
script :
CREATE TABLE [dbo].[BRANCH_MASTER](
[BM_BRANCH_CODE] [varchar](10) NOT NULL,
[BM_ABBR] [nchar](10) NULL,
[BM_BRANCH_NAME] [varchar](30) NOT NULL,
[BM_ADDRESS] [varchar](100) NOT NULL,
[BM_CITY] [varchar](30) NULL,
[BM_STATE] [varchar](20) NOT NULL,
[BM_PINCOE_CODE] [varchar](8) NULL,
[BM_PHONE] [varchar](15) NOT NULL,
[BM_MOBILE] [varchar](15) NULL,
[BM_FAX] [varchar](15) NULL,
[BM_BRANCH_TYPE] [varchar](2) NOT NULL,
[BM_OPENING_DATE] [datetime] NOT
NULL,
[BM_EXPIRY_DATE] [datetime] NULL,
[BM_INCHARGE_NAME] [varchar](50) NOT NULL,
[BM_INCHARGE_PHNO] [varchar](20) NULL,
[BM_STATUS] [varchar](2) NULL,
[BM_CREATED_ON] [datetime] NOT NULL,
[BM_CREATED_BY] [varchar](12) NOT NULL,
[BM_MODIFIED_ON] [datetime] NULL,
[BM_MODIFIED_BY] [varchar](12) NULL,
CONSTRAINT
[PK__BRANCH_MASTER__1B0907CE] PRIMARY KEY CLUSTERED
(
[BM_BRANCH_CODE] 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
SET ANSI_PADDING OFF
GO
No comments:
Post a Comment