Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to call stored procedure in C#

Posted on 2006-11-30
26
Medium Priority
?
369 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
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!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

704 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