Reader Level:
Articles

Call any stored procedure which take input parameters and return output parameters

By Vipin Yadav on February 01, 2008
This one generic function can call any Stored Prodedure which take input parameters and return output parameters with the help of two-dimensions array. This is very helpful specially when you designing n-tier application.
  • 0
  • 0
  • 30209

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;
 }


}

Vipin Yadav

Regards Vipin Yadav R Systems International Ltd.An ISO 27001 and CMMI Level 5 CompanyC-40 Sector 59 Noida(INDIA)Mobile:9971992759

Personal Blog: http://www.sapient.com
COMMENT USING

Trending up