CREATE TABLE [dbo].[DocMaster](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DocID] [varchar](12) NOT NULL,
[DocName] [varchar](30) NOT NULL,
[DocVersion] [varchar](50) NOT NULL,
[ModifiedBy] [varchar](12) NOT NULL,
[ModifiedDt] [datetime] NOT NULL,
CONSTRAINT [PK_DocMaster] 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]
Stored Proc:
ALTER PROCEDURE [dbo].[InsertDoc]
@DocID varchar(12),
@DocName varchar(30),
@DocVersion varchar(12),
@ModifiedBy varchar(12),
@ModifiedDt DateTime
AS
BEGIN
insert into DocMaster
values(@DocID,@DocName,@DocVersion,@ModifiedBy,@ModifiedDt)
END
**********************************************
ALTER PROCEDURE [dbo].[SelectDoc]
@DocID varchar(12)
AS
BEGIN
Select * from DocMaster
Where DocID = @DocID
END
********************************************
ALTER PROCEDURE [dbo].[UpdateDoc]
@DocID varchar(12),
@DocName varchar(30),
@DocVersion varchar(12),
@ModifiedBy varchar(12),
@ModifiedDt DateTime
AS
BEGIN
Update DocMaster
set DocName=@DocName,DocVersion = @DocVersion,ModifiedBy =@ModifiedBy,ModifiedDt=@ModifiedDt
Where DocID = @DocID
END
**********************
protected void btnSave_Click(object sender, EventArgs e)
{
string cnStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
using (SqlConnection cn = new SqlConnection(cnStr))
{
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlParameter prmDocID = new SqlParameter("@DocID", txtDocID.Text);
SqlParameter prmDocName = new SqlParameter("@DocName", txtDocName.Text);
SqlParameter prmDocVsn = new SqlParameter("@DocVersion", txtDocVsn.Text);
SqlParameter prmModBy = new SqlParameter("@ModifiedBy", txtModifiedBy.Text);
SqlParameter prmModDt = new SqlParameter("@ModifiedDt", DateTime.Parse(txtModifiedDt.Text));
SqlCommand cmd = new SqlCommand("InsertDoc", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(prmDocID);
cmd.Parameters.Add(prmDocName);
cmd.Parameters.Add(prmDocVsn);
cmd.Parameters.Add(prmModBy);
cmd.Parameters.Add(prmModDt);
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
}
finally
{
cn.Close();
}
}
}
*********************************************************************
protected void btnUpdate_Click(object sender, EventArgs e)
{
using (SqlConnection cn = new SqlConnection(cnStr))
{
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlParameter prmDocID = new SqlParameter("@DocID", strDocID);
SqlParameter prmDocName = new SqlParameter("@DocName", lblDocName.Text);
SqlParameter prmDocVsn = new SqlParameter("@DocVersion", lblDocVsn.Text);
SqlParameter prmModBy = new SqlParameter("@ModifiedBy", lblModifiedBy.Text);
SqlParameter prmModDt = new SqlParameter("@ModifiedDt", DateTime.Parse(lblModifiedDt.Text));
SqlCommand cmd = new SqlCommand("UpdateDoc", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(prmDocID);
cmd.Parameters.Add(prmDocName);
cmd.Parameters.Add(prmDocVsn);
cmd.Parameters.Add(prmModBy);
cmd.Parameters.Add(prmModDt);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
cn.Close();
//Redirect to edit page
Response.Redirect("~/View.aspx?DocID=" + lblDocID.Text.Trim());
}
}
}
*********************************************************************
private void Show(string strDocID)
{
using (SqlConnection cn = new SqlConnection(cnStr))
{
try
{
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
SqlParameter prmDocID = new SqlParameter("@DocID", strDocID);
SqlCommand cmd = new SqlCommand("SelectDoc", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(prmDocID);
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
lblDocID.Text = sdr["DocID"].ToString();
lblDocName.Text = sdr["DocName"].ToString();
lblDocVsn.Text = sdr["DocVersion"].ToString();
lblModifiedBy.Text = sdr["ModifiedBy"].ToString();
lblModifiedDt.Text = sdr["ModifiedDt"].ToString();
}
}
catch (Exception ex)
{
}
finally
{
cn.Close();
}
}
}
*********************************
<configuration>
<connectionStrings>
<add name="ConStr"
connectionString="server=banka-pc;uid=sa;pwd=banka;database=accnt"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
