How to Use an Input parameter in Stored Procedure with Datasets

[Sreedeep]

Dear Friends
I have a Query. I have a Stored Procedure Which takes an Input parameter . I want to fill Dataset with table based on the same input parameter.I have already used it with data reader . But with Data Adaptor and Data Set its not properly working .The Query i have used is

Select ItemNumber from ItemsTable where CatagoryId = (Select CatagoryId From CatagoryTable Where Catagoryname=@CatgName

So please Any one can tell me What are the Steps to be Given to get this Stored procedure working

Thanking You
Sreedeep

[Ramy Mahrous]

Ok, I'll help you regards this, but first tell me it's Windows application or web-based application?!

[Ramy Mahrous]

The answer in this post http://fci-h.blogspot.com/2008/08/how-to-use-dataset-and-adapters-with.html

[Sreedeep]

Ok, I'll help you regards this, but first tell me it's Windows application or web-based application?!

Thank you sir
i was not able to answer your question as its holiday here was aout of station
Its a Window Application
Hope to get the Reply
Bye
Regards
Sreedeep

[JerryShaw]

Okay - First I would suggest a change to the query

Instead of

Select ItemNumber from ItemsTable where CatagoryId = (Select CatagoryId From CatagoryTable Where Catagoryname=@CatgName

Make it:

Select i.ItemNumber from ItemsTable i 
JOIN CategoryTable c on i.CatagoryId=c.CatagoryId
where c.CatagoryName = @CatgName

To get the data into a dataset through a proc and adapter:

SqlConnection conn = new SqlConnection(<Your Connection String>);  
  SqlCommand cmd = new SqlCommand(<Procedure Name>, conn);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@CatgName",<Your Cat Name>);
  SqlDataAdapter adapter = new SqlDataAdapter(cmd);
  adapter.Fill( <Your dataSet or dataTable>);

Hope this Helps
Jerry

[Elmo_loves_you]

Jerry,

I have a question related to this post!

I am trying to develop a Windows Application interface to an SQL Server database. I have a stored procedure that executes properly and successfully adds to the table in the database.

However the C# code that I have written to pass data to the stored procedure via textboxes on the interface is not working as expected. I am not getting any obvious 'bugs' but the data is still not adding to the table.

I hope you can help me with this issue

public void setCustomerDetails()
        {
            try
            {
                //Open connection to the database
                myConnection.Open();

                //Declare myCommand properties
                myCommand = new SqlCommand("CreateCustomer", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                //Declare and Initialise the stored procedure parameters setting their type and size
                //as they are defined in the table properties!
                param_companyName = new SqlParameter("@companyName", SqlDbType.VarChar, 50);
                param_companyName.Value = txt_CompanyName.Text;

                param_contactForename = new SqlParameter("@forename", SqlDbType.VarChar, 50);
                param_contactForename.Value = txt_ContactForename.Text;

                param_contactSurname = new SqlParameter("@surname", SqlDbType.VarChar, 50);
                param_contactSurname.Value = txt_ContactSurname.Text;

                param_phoneNo = new SqlParameter("@phoneNo", SqlDbType.NVarChar, 50);
                param_phoneNo.Value = txt_PhoneNo.Text;

                param_mobileNo = new SqlParameter("@mobileNo", SqlDbType.NVarChar, 50);
                param_mobileNo.Value = txt_MobileNo.Text;

                param_altNo = new SqlParameter("@altNo", SqlDbType.NVarChar, 50);
                param_altNo.Value = txt_AlternativeNo.Text;

                myCommand.Parameters.Add(param_companyName);
                myCommand.Parameters.Add(param_contactForename);
                myCommand.Parameters.Add(param_contactSurname);
                myCommand.Parameters.Add(param_phoneNo);
                myCommand.Parameters.Add(param_mobileNo);
                myCommand.Parameters.Add(param_altNo);
               
                myCommand.ExecuteNonQuery();

                lbl_SuccessfulMessage.Text = "Success";
            }
            catch (SqlException ex)
            {
                lbl_SuccessfulMessage.Text = ex.Message;
            }
            finally
            {
                //Close database connection
                myConnection.Close();
            }
}

PS. I used this approach when I was working on an ASP.NET project :) and it worked fine

[JerryShaw]

Elmo,

Let me share with you (and others viewing) a better way to manage SQL procedure calls.
I have a Data Access Layer (DAL) static class that is full of little helper methods like the one below. Using this approach will make your code cleaner, more compact, and less buggy, and well... life a little easier.

Let dotNet do the work for you when possible.
Add this method, if you do not have a static class, then just remove the static assignment, and you can work on that aspect later.

// Note: GetSqlConnection simply returns my connection string.
public static void ExecuteNonQueryStoredProcedure(string ProcedureName, params SqlParameter[] values)
        {
            SqlConnection conn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(ProcedureName, conn);
            cmd.CommandTimeout = 60;
            cmd.CommandType = CommandType.StoredProcedure;
            if (values != null && values.Length > 0)
                cmd.Parameters.AddRange(values);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            finally // NO CATCH ON PURPOSE, want the error to escelate
            {
                conn.Close();
                conn.Dispose();
            }
        }

To use this method I use this format

try
{

  ExecuteNonQueryStoredProcedure("CreateCustomer", 
     new SqlParameter("@companyName", txt_CompanyName.Text) ,
     new SqlParameter("@forename", txt_ContactForename.Text) ,
     new SqlParameter("@surname", txt_ContactSurname.Text) ,
     new SqlParameter("@phoneNo", txt_PhoneNo.Text) ,
     new SqlParameter("@mobileNo", txt_MobileNo.Text) ,
     new SqlParameter("@altNo", txt_AlternativeNo.Text)    );
}
catch (SqlException eSql)
{
     // do something about it
}
catch (Exception e)
{
    // general catch
}

Much cleaner, and easier to debug. All the nitty gritty work is done the same way everyime in the Execute.... method. With that done, you can focus on your real objectives.

BTW: I didn't see anything "wrong" with your code... suggest proving the sproc works with manual entries using SQL studio.

// Jerry

[Elmo_loves_you]

Thanks very much Jerry, that looks very neat :) I'll give it a go and let you know how I get on

Thanks

[Elmo_loves_you]

// Note: GetSqlConnection simply returns my connection string.
public static void ExecuteNonQueryStoredProcedure(string ProcedureName, params SqlParameter[] values)
        {
            SqlConnection conn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(ProcedureName, conn);
            cmd.CommandTimeout = 60;
            cmd.CommandType = CommandType.StoredProcedure;
            if (values != null && values.Length > 0)
                cmd.Parameters.AddRange(values);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            finally // NO CATCH ON PURPOSE, want the error to escelate
            {
                conn.Close();
                conn.Dispose();
            }
        }

One last question Jerry. For the highlighted method call above. How did you write that method? I really do have a lot to learn !!

public void GetSqlConnection()
        {
            SqlConnection connection = new SqlConnection("THE LONG WINDED CONN STRING");
            //return connection;
        }

I havent got a static class set up at the moment ... but once I get this working I will be able to play about with it :)

[JerryShaw]

yes it does exactly that:
Below is a short version. In my production environment, it takes a parameter called an "Alias" that lets the method fetch additional information from configuration for building the connection string such as Windows or SQL authentication, the server and database names, user and password information.

This short version should help you.

public static SqlConnection GetSqlConnection()
{
    string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True",serverName,catalogName);

    return new SqlConnection(connectionString);
}

[Elmo_loves_you]

Hi,

Sorry for posting again but this is really starting to annoy me now.

I have created a form with textboxes for user input!

Stored procedures to add the parameter data to the table (which executes correctly and adds new data to the table)

Then I wrote this code ... I have changed it to match the code that I used for my university ASP.NET project which added new data to the database successfully. I was able to perform all CRUD tasks.

But for some reason I cant get the data to add to the table, stepping through the code indicates that the connection is Open!! So I really cant understand whats going wrong.

myConnection is just an SqlConnection myConnection = new SqlConnection("sjkfskjfhskjhf"); declared at top of code

//Declare myCommand properties
            myCommand = new SqlCommand("CreateCustomer", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            
            //Open connection to the database
            myConnection.Open();

            myCommand.Parameters.Add(new SqlParameter("@companyName", SqlDbType.VarChar, 50, "CompanyName"));
            myCommand.Parameters["@companyName"].Value = txt_CompanyName.Text;

            myCommand.Parameters.Add(new SqlParameter("@forename", SqlDbType.VarChar, 50, "ContactForename"));
            myCommand.Parameters["@forename"].Value = txt_ContactForename.Text;

            myCommand.Parameters.Add(new SqlParameter("@surname", SqlDbType.VarChar, 50, "ContactSurname"));
            myCommand.Parameters["@surname"].Value = txt_ContactSurname.Text;

            myCommand.Parameters.Add(new SqlParameter("@phoneNo", SqlDbType.NVarChar, 50, "PhoneNo"));
            myCommand.Parameters["@phoneNo"].Value = txt_PhoneNo.Text;

            myCommand.Parameters.Add(new SqlParameter("@mobileNo", SqlDbType.NVarChar, 50, "MobileNo"));
            myCommand.Parameters["@mobileNo"].Value = txt_MobileNo.Text;

            myCommand.Parameters.Add(new SqlParameter("@altNo", SqlDbType.NVarChar, 50, "AlternativeNo"));
            myCommand.Parameters["@altNo"].Value = txt_AlternativeNo.Text;

            myCommand.ExecuteNonQuery();
            myCommand.Dispose();
            myConnection.Close();

            lbl_SuccessfulMessage.Text = "Success";

Please help lol :(

[JerryShaw]

Your c# code appears to be correct. The ExecuteNonQuery method returns an int value on the success or failure of the transaction. I suggest that you inspect the return value. If it is a zero (0) then the SQL Server processed your request without error.

I would also suggest that you do not specify the type when you add a parameter. Let ADO figure that out for you at run time. If you ever need to change a parameter type in the stored procedure, you will have to update your SQL code to match. This can be avoided by letting ADO manage the type for you.

Last suggestion, Use Parameter.AddWithValue to streamline your assignments.

Good Luck