Sqlser2005 Table[BookMaster] Structure:
BookID int
Title nvarchar(50)
Author nvarchar(50)
Pubs nvarchar(50)
Category nvarchar(50)
EntryDate datetime
Price decimal(18, 2)
PurchasedFrom nvarchar(50)
BookFor nchar(10)
Status int
Stored procedure:
CREATE PROCEDURE [dbo].[SPADDBOOK]
@Title nvarchar(50)=null,
@Author nvarchar(50)=null,
@Pubs nvarchar(50)=null,
@Category nvarchar(50)=null,
@EntryDate nvarchar(50)=null,
@Price float=null,
@PurchasedFrom nvarchar(50)=null,
@BookFor nvarchar(50)=null,
@BookId int output,
@Status int output
AS
BEGIN
SET NOCOUNT ON;
set @Price= cast(@Price as float)
INSERT INTO BookMaster(Title,Author,Pubs,Category,EntryDate,Price,PurchasedFrom,BookFor) values(@Title,@Author,@Pubs,@Category,@EntryDate,@Price,@PurchasedFrom,@BookFor)
SET @BookId =@@identity
IF @@error=0
SET @Status=1
else
SET @Status=0
END
Front End Code FRM CODE:
public partial class BooksList : Form
{
DataAccess DA=new DataAccess();
DataTable DT;
private void button2_Click(object sender, EventArgs e)
{
int ValidationFlag = 1;
if (txtBookTitle.Text == "")
{ MessageBox.Show("Please Enter Book Title"); txtBookTitle.Focus(); ValidationFlag = 0; }
else if (txtAuthor.Text == "")
{ MessageBox.Show("Please Enter Book Author"); txtAuthor.Focus(); ValidationFlag = 0; }
else if (txtentrydate.Text == "")
{ MessageBox.Show("Please Enter Book Title"); txtentrydate.Focus(); ValidationFlag = 0; }
else if (txtprice.Text == "")
{ MessageBox.Show("Please Enter Book Entry Date"); txtprice.Focus(); ValidationFlag = 0; }
else if (txtPublication.Text == "")
{ MessageBox.Show("Please Enter Book Publication"); txtPublication.Focus(); ValidationFlag = 0; }
else if (txtpurchasefrom.Text == "")
{ MessageBox.Show("Please Enter Book Purchase From"); txtpurchasefrom.Focus(); ValidationFlag = 0; }
if (ValidationFlag == 1)
{
string BookType;
if (rbrent.Checked == true)
{
BookType = "RENT";
}
else
{
BookType = "SALE";
}
string[,] PramIn = new string[8, 2] {
{"@Title",txtBookTitle.Text},
{"@Author",txtAuthor.Text},
{"@Pubs",txtPublication.Text},
{"@Category",txtcategory.Text},
{"@EntryDate",txtentrydate.Text},
{"@Price",txtprice.Text},
{"@PurchasedFrom",txtpurchasefrom.Text},
{"@BookFor",BookType}
};
string[,] PramOut = new string[2, 2] {
{"@BookId","SqlDbType.Int"},
{"@Status","SqlDbType.Int"}
};
string[,] OutResult;
int NewBookId=0;
int StatusId=0;
try
{
DataAccess DA = new DataAccess();
OutResult = (string[,])DA.CallStoredProcedure(PramIn, PramOut, "SPADDBOOK");
NewBookId = Convert.ToInt32(OutResult[0, 1]);
StatusId = Convert.ToInt32(OutResult[1, 1]);
}
catch (Exception ex)
{
LblMsg.Text = "::ERROR:: " + ex.Message;
}
if (StatusId == 1)
{
LblMsg.Text =LblMsg.Text+ " New Book Is Added Sucessfuly. Book Id is NewBookId: " + NewBookId.ToString();
}
else
{
LblMsg.Text =LblMsg.Text+ " There is some problem in Database ";
}
}
}
private void BooksList_Load(object sender, EventArgs e)
{
DT = DA.CallStoredProcedure("SPBOOKLIST");
dataGridView1.DataSource = DT;
dataGridView1.AutoGenerateColumns = false;
dataGridView1.AutoSize = true;
dataGridView1.BorderStyle = BorderStyle.Fixed3D;
// Put the cells in edit mode when user enters them.
dataGridView1.EditMode = DataGridViewEditMode.EditOnEnter;
dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
DataGridViewColumn column = new DataGridViewCheckBoxColumn();
column.HeaderText = "Delete";
column.Name = "Sold";
column=new DataGridViewCheckBoxColumn();
column.DataPropertyName = "BookID";
column.Name = "BookID";
dataGridView1.Columns.Add(column);
// Initialize and add a check box column.
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "Title";
column.Name = "Title";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "Author";
column.Name = "Author";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "Pubs";
column.Name = "Pubs";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "Category";
column.Name = "Category";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "EntryDate";
column.Name = "EntryDate";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "Price";
column.Name = "Price";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "PurchasedFrom";
column.Name = "PurchasedFrom";
dataGridView1.Columns.Add(column);
column = new DataGridViewTextBoxColumn();
column.DataPropertyName = "BookFor";
column.Name = "BookFor";
dataGridView1.Columns.Add(column);
column = new DataGridViewCheckBoxColumn();
column.DataPropertyName = "Status";
column.Name = "Sold Status";
dataGridView1.Columns.Add(column);
dataGridView1.AllowUserToResizeColumns = true;
dataGridView1.AllowUserToAddRows = true;
dataGridView1.Columns["Title"].SortMode = DataGridViewColumnSortMode.Automatic;
}
}
Data Access Class Code:
class DataAccess
{
SqlConnection Conn;
SqlDataAdapter DA= new SqlDataAdapter();
SqlCommandBuilder CB;
DataTable DT;
public SqlConnection OpenConnection()
{
Conn = new SqlConnection("data source=;database=bookmaster;Integrated Security=SSPI");
return Conn;
}
public Array CallStoredProcedure(string[,] MyINPram, string[,] MyOUTPram, string MySPName)
{
string Result;
Conn = OpenConnection();
SqlCommand CmdSp = new SqlCommand();
CmdSp.Connection = Conn;
CmdSp.CommandText = MySPName.ToString();
CmdSp.CommandType = CommandType.StoredProcedure;
//input parameters
for (int i = 0; i < MyINPram.Length / 2; i++)
{
CmdSp.Parameters.AddWithValue(MyINPram[i, 0], MyINPram[i, 1]);
}
//outparameters
for (int i = 0; i < MyOUTPram.Length / 2; i++)
{
if (MyOUTPram[i, 1] == "SqlDbType.VarChar")
{
CmdSp.Parameters.Add(MyOUTPram[i, 0], SqlDbType.VarChar, 150);
}
else
{
CmdSp.Parameters.Add(MyOUTPram[i, 0], SqlDbType.Int, 4);
//CmdSp.Parameters.Add(MyOUTPram[i, 0], MyOUTPram[i,1]);
}
CmdSp.Parameters[MyOUTPram[i, 0]].Direction = ParameterDirection.Output;
}
Conn.Open();
CmdSp.ExecuteNonQuery();
Conn.Close();
string[,] Temparray = new string[MyOUTPram.Length / 2, 2];
for (int i = 0; i < MyOUTPram.Length / 2; i++)
{
Result = Convert.ToString(CmdSp.Parameters[MyOUTPram[i, 0]].Value);
//Result = (string)CmdSp.Parameters["@Msg"].Value;
Temparray[i, 0] = MyOUTPram[i, 0];
Temparray[i, 1] = Result;
}
return Temparray;
}
public DataTable CallStoredProcedure(string MySPName)
{
Conn = OpenConnection();
SqlCommand cm = new SqlCommand(MySPName, Conn);
cm.CommandType = CommandType.StoredProcedure;
DA.SelectCommand = cm;
DT = new DataTable();
DA.Fill(DT);
return DT;
}
}