Solved

How to call stored procedure in C#

Posted on 2006-11-30
26
360 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:Collindsouza
Comment Utility
           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
Comment Utility
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
Comment Utility
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
Comment Utility
Microsoft Application Data Blocks takes care of all that functionality and simplifies it through abstraction
0
 
LVL 20

Expert Comment

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

Expert Comment

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

Expert Comment

by:REA_ANDREW
Comment Utility
"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
Comment Utility
;)
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 5

Expert Comment

by:stumpy1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Why (Delete) ?? The Experts have answered the question.
0
 
LVL 5

Expert Comment

by:stumpy1
Comment Utility
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
Comment Utility
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
Comment Utility
i agree
0
 
LVL 5

Expert Comment

by:Collindsouza
Comment Utility
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
Comment Utility
You tell me how you would make the recommendations.
0
 
LVL 5

Expert Comment

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

Expert Comment

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

Expert Comment

by:stumpy1
Comment Utility
Agreed
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction                                                 Was the var keyword really only brought out to shorten your syntax? Or have the VB language guys got their way in C#? What type of variable is it? All will be revealed.   Also called…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now