Solved

How to call stored procedure in C#

Posted on 2006-11-30
26
364 Views
Last Modified: 2011-10-03
Hi,

Can anyone help me that how to call stored procedure in the C#

I also have to pass parameters to the stored procedure

Thanks
0
Comment
Question by:dinesh_bali
  • 7
  • 6
  • 3
  • +3
26 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 18044001
Here's a good example with downloadable source code:
http://www.c-sharpcorner.com/Code/2003/Jan/InsOutsinCS.asp
0
 
LVL 9

Expert Comment

by:DrAske
ID: 18044032
SqlConnection x = new SqlConnection("conn string");
SqlCommand sqlcmd = x.CreateCommand();
sqlc.CommandType = CommandType.StoredProcedure;
sqlc.CommandText = "sp_name";
sqlc.parameters.add(new SqlParameter("@name", Value));
x.Open();
sqlc.ExecuteNonQuery();
0
 
LVL 9

Accepted Solution

by:
DrAske earned 500 total points
ID: 18044066
Sorry, forgot to refresh!!!

SqlConnection x = new SqlConnection("conn string"); // establish connection
SqlCommand sqlcmd = x.CreateCommand();    
sqlcmd.CommandType = CommandType.StoredProcedure;  
sqlcmd.CommandText = "sp_name";   // name of stored procedure
sqlcmd.parameters.add(new SqlParameter("@name", Value));  // to pass parameter (replace the arguements with yours)
x.Open();   // open connection
sqlcmd.ExecuteNonQuery();   // or ExecuteReader()

regards,Ahmad;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:Collindsouza
ID: 18045256
           SqlConnection myConnection = new SqlConnection("Connection String"); //Create Connection
            SqlCommand myCommand = myConnection.CreateCommand(); // Declare a Command
            myCommand.CommandText = "Stored Proc Name"; //Name the Stored proc
            myCommand.CommandTimeout = 120; // Connection Timeout

            // Mark the Command as a SPROC
            myCommand.CommandType = CommandType.StoredProcedure;

    1.        SqlParameter parameterName = new SqlParameter("@[Stored proc INPUT VARABLE]", SqlDbType.[DB TYPE Ex. INt, Varchar], [LENGTH:- 50 ]);
    2.        parameterName .Value = [Value for the Stored proc INPUT VARABLE] ;
    3.        myCommand.Parameters.Add(parameterName );
           
//Copy lines 1-3 for the number of input parameters

//Execure the Stored proc
 try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();

            }
            finally
            {
                myConnection.Close();
            }
0
 
LVL 5

Expert Comment

by:stumpy1
ID: 18046207
Download Microsoft Application Data Blocks from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

You will need the following using directives at the top of the code behind:
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

Then in your method/function you call your Stored procedure using the following:

      
//get your connection String.  I am just using inline text here for convenience but you will usually get it from your config file.
string connectionString = "Data Source=SQLServerName;Integrated Security=SSPI;Initial Catalog=DatabaseName;pooling=true;min pool size=5";

//create your parameters
SqlParameter[] parameters =
      {
            new SqlParameter("@parametername1", SqlDbType.Int, 4),
            new SqlParameter("@parametername2", SqlDbType.Char, 10),
            new SqlParameter("@parametername3", SqlDbType.Int, 4),
      };

//assign values to the parameters
parameters[0].Value = variable1;
parameters[1].Value = variable2;
parameters[2].Value = variable3;

//call the stored procedure
SqlDataReader myDR= SqlHelper.ExecuteReader(connectionString, "usp_Stored_Procedure_Name", parameters);


Note: SQLHelper has many other overloads and methods that abstracts/simplified all SQL DB calls.
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 18046238
personally I would say that mine is the most efficient as it will dispose of the SqlConnection after it has been used, using the using statement

SqlCommand SC1 = new SqlCommand();
SC1.ConnectionType = ConnectionType.StoredProcedure;
SC1.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString1"].ConnectionString);
SC1.CommandText = "StoredProc1";
SC1.Parameters.AddWithValue("@Param1",1);

//This will dispose of the connection after use, as well as closing, although I do not see any use of the dispose in earlier posts.

using(SC1.Connection)
{
SC1.Connection.Open();
SC1.ExecuteNonQuery();
}

Andrew
:-)
0
 
LVL 5

Expert Comment

by:stumpy1
ID: 18046612
Microsoft Application Data Blocks takes care of all that functionality and simplifies it through abstraction
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 18046635
Am I wrong in saying though that it redcues flexibility
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046765
MDAC(Microsoft Application Data Blocks) is quite bulky and carries a lot of unnecessary baggage
0
 
LVL 20

Expert Comment

by:REA_ANDREW
ID: 18046787
"MDAC(Microsoft Application Data Blocks) is quite bulky and carries a lot of unnecessary baggage"

Good Point, I agree
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046824
;)
0
 
LVL 5

Expert Comment

by:stumpy1
ID: 18046836
MDAC is Microsoft Data Access Coimponents which is not the same as Microsoft Application Blocks, they are 2 completely different Microsoft products.
MDAC http://msdn.microsoft.com/data/learning/mdac/default.aspx
Microsoft Application Data Blocks http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

The Microsoft.ApplicationBlocks.Data.dll for 1.1 is only 32K an makes a coder more efficient and leaves code much easier to view and maintain.
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18046870
OOPS.. My BAD

I meant Microsoft Application Data Blocks.. and not the MDAC as in Microsoft Data Access Coimponents
0
 
LVL 5

Expert Comment

by:stumpy1
ID: 18046936
Take a look at the comparisons between using Microsoft Application Data Blocks and not here http://aspnet.4guysfromrolla.com/articles/070203-1.aspx 
You can easily see why they are so good.  You are also provided with the source code for the DLL so you can change and/or optomize it if you wish.  We have been using them for a few years now and find them invaluable.

Plenty more info on the benefits of DAAB can be found here http://www.informit.com/guides/content.asp?g=dotnet&seqNum=339&rl=1
0
 
LVL 9

Expert Comment

by:DrAske
ID: 18273698
Why (Delete) ?? The Experts have answered the question.
0
 
LVL 5

Expert Comment

by:stumpy1
ID: 18274413
I would also like to know why Delete?  
Several different options/possible solutions were provided & explained so I think the question should be PAQ'd to improve EE's DB for future searches/reference.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18274548
I agree - question has been answered. Considering the askers history of abandoning questions, it is unlikely that we will get a response, so I suggest split:
Nightman & DrAske & Collindsouza & stumpy1 & REA_ANDREW
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18290885
i agree
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18365165
May i ask what was wrong with my solution and why have i been left out from the points split?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 18432921
You tell me how you would make the recommendations.
0
 
LVL 5

Expert Comment

by:Collindsouza
ID: 18434520
Split points between Nightman,DrAske,stumpy1, REA_ANDREW and Collindsouza(me)
0
 
LVL 29

Expert Comment

by:Nightman
ID: 18434530
I agree. If you're going to split, split across all.
0
 
LVL 5

Expert Comment

by:stumpy1
ID: 18435470
Agreed
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
FTP file download using c# 3 28
Why do I get the message "Message has been thrown by target of an invocation"? 22 73
linq, c# 8 31
orderby list (from Json) 1 28
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question