Reader Level:
ARTICLE

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

Posted by Vipin Yadav Articles | Visual C# 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
  • 29664

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


}

COMMENT USING

Trending up