Solved

How to call stored procedure in C#

Posted on 2006-11-30
26
361 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Image(7) 1 53
System.Speech 2 11
Counting connections to SQL Server through C# 3 28
use of unassigned local variable 'id' 6 21
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…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

21 Experts available now in Live!

Get 1:1 Help Now