Solved

How to call stored procedure in C#

Posted on 2006-11-30
26
366 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 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
Technology Partners: 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# Linq - Join two objects into one 3 54
Updating a single record in forEach using C#/Entity 7 39
Return array 3 35
c#  FTP ftpwebrequest URI invalid 6 47
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 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