473,735 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET

HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and
Visual C# .NET
View products that this article applies to.
This article was previously published under Q310070
For a Microsoft Visual Basic .NET version of this article, see 308049.
For a Microsoft Visual C++ .NET version of this article, see 310071.
For a Microsoft Visual J# .NET version of this article, see 320627.

This article refers to the following Microsoft .NET Framework Class
Library namespaces:
System.Data.Sql Client
System.Data.Ole Db
IN THIS TASK
SUMMARY

Use DataReader to Return Rows and Parameters
Use the ExecuteScalar Method of the Command Object
Use the ExecuteNonQuery Method of the Command Object
REFERENCES
SUMMARY
There are several ways to use ADO.NET to call a stored procedure and
to get back return values and return parameters, including:
Use a DataSet object to gather the returned rows and to work with
these rows in addition to the return values and the return parameters.
Use a DataReader object to gather the returned rows, to move through
these rows, and then to gather return values and return parameters.
Use the ExecuteScalar method to return the value from the first column
of the results' first row with the return values and the return
parameters. This is most useful with aggregate functions.
Use the ExecuteNonQuery method to return only the return parameters
and the return values. Any returned rows are discarded. This is most
useful for executing action queries.
This article demonstrates the last three methods and uses both the
SqlCommand and the OleDbCommand objects. Make sure that you copy only
the code for the managed provider that you are using. If you are not
sure which managed provider you should use, visit the following
Microsoft Developer Network Web site:
..NET Data Providers
http://msdn.microsoft.com/library/de...tproviders.asp

In each of the samples in this article, the parameters are added to
the Parameters collection of the Command object. When you use the
SqlCommand object, you do not have add the parameters in any
particular order, but the parameters must have the correct name. When
you use the OleDbCommand object, you must add the parameters in the
correct order, and you cannot use the parameters by name.

back to the top
Use DataReader to Return Rows and Parameters
You can use the DataReader object to return a read-only, forward-only
stream of data. The information that the DataReader contains can come
from a stored procedure. This example uses the DataReader object to
run a stored procedure that has an input and an output parameter and
then moves through the returned records to view the return parameters.
Create the following stored procedure on the server that is running
Microsoft SQL Server:Create Procedure TestProcedure
(
@au_idIN varchar (11),
@numTitlesOUT Integer OUTPUT
)
AS

select A.au_fname, A.au_lname, T.title
from authors as A join titleauthor as TA on
A.au_id=TA.au_i d
join titles as T
on T.title_id=TA.t itle_id
where A.au_id=@au_idI N
set @numTitlesOUT = @@Rowcount
return (5)

Create a new Visual C# .NET Windows Application project.
Use the using statement on the System and the System.Data namespaces
so that you do not have to qualify declarations in those namespaces
later in your code. Add this code to the top of the Form code module.
Make sure to copy only the code for the provider that you have
chosen.SQL Clientusing System.Data.Sql Client;

OLE DB Data Providerusing System.Data.Ole Db;

Replace the code in the private Form_Load event with the following
code:SQL ClientSqlConnec tion PubsConn = new SqlConnection
("Data Source=server;i ntegrated " +
"Security=sspi; initial catalog=pubs;") ;
SqlCommand testCMD = new SqlCommand
("TestProcedure ", PubsConn);

testCMD.Command Type = CommandType.Sto redProcedure;

SqlParameter RetVal = testCMD.Paramet ers.Add
("RetVal", SqlDbType.Int);
RetVal.Directio n = ParameterDirect ion.ReturnValue ;
SqlParameter IdIn = testCMD.Paramet ers.Add
("@au_idIN", SqlDbType.VarCh ar, 11);
IdIn.Direction = ParameterDirect ion.Input;
SqlParameter NumTitles = testCMD.Paramet ers.Add
("@numtitlesout ", SqlDbType.VarCh ar, 11);
NumTitles.Direc tion = ParameterDirect ion.Output ;

IdIn.Value = "213-46-8915";
PubsConn.Open() ;

SqlDataReader myReader = testCMD.Execute Reader();
Console.WriteLi ne ("Book Titles for this Author:");
while (myReader.Read( ))
{
Console.WriteLi ne ("{0}", myReader.GetStr ing (2));
};
myReader.Close( ) ;
Console.WriteLi ne("Number of Rows: " + NumTitles.Value );
Console.WriteLi ne("Return Value: " + RetVal.Value);

OLE DB Data ProviderOleDbCo nnection PubsConn = new OleDbConnection
("Provider=SQLO LEDB;Data Source=server;" +
"integrated Security=sspi;i nitial catalog=pubs;") ;
OleDbCommand testCMD = new OleDbCommand
("TestProcedure ", PubsConn);

testCMD.Command Type = CommandType.Sto redProcedure;

OleDbParameter RetVal = testCMD.Paramet ers.Add
("RetVal", OleDbType.Integ er);RetVal.Dire ction =
ParameterDirect ion.ReturnValue ;
OleDbParameter IdIn = testCMD.Paramet ers.Add
("@au_idIN", OleDbType.VarCh ar, 11);
IdIn.Direction = ParameterDirect ion.Input;
OleDbParameter NumTitles = testCMD.Paramet ers.Add
("@numtitlesout ", OleDbType.VarCh ar, 11);
NumTitles.Direc tion = ParameterDirect ion.Output;

IdIn.Value = "213-46-8915";

PubsConn.Open() ;

OleDbDataReader myReader = testCMD.Execute Reader();
Console.WriteLi ne ("Book Titles for this Author:");
while (myReader.Read( ))
{
Console.WriteLi ne ("{0}", myReader.GetStr ing (2));
};
myReader.Close( ) ;
Console.WriteLi ne("Number of Rows: " + NumTitles.Value );
Console.WriteLi ne("Return Value: " + RetVal.Value);

Modify the connection string for the Connection object to point to the
computer that is running SQL Server.
Run the code. Notice that the DataReader retrieves the records and
then returns the parameter values. You can use the Read method of the
DataReader object to move through the returned records.

The Output window displays the titles of two books, the return value
of 5, and the output parameter, which contains the number of records
(2). Notice that you must close the DataReader in the code to see the
parameter values. Additionally, note that you do not have to move
through all of the records to see the return parameters if the
DataReader is closed.
back to the top
Use the ExecuteScalar Method of the Command Object
You can use the ExecuteScalar method of the Command object to retrieve
parameter values. Additionally, ExecuteScalar returns the first column
of the first row of the stored procedure. This is most useful for
aggregate functions as in the following example.
Create the following stored procedure on the server that is running
SQL Server:Create Procedure TestProcedure2
(
@au_idIN varchar (11)
)
As
/* set nocount on */
select count (T.title)
from authors as A join titleauthor as TA on
A.au_id=TA.au_i d
join titles as T
on T.title_id=TA.t itle_id
where A.au_id=@au_idI N
Return(5)

Create a new Visual C# .NET Windows Application project.
Use the using statement on the System and the System.Data namespaces
so that you do not have to qualify declarations in those namespaces
later in your code. Add this code to the top of the Form code module.
Make sure that you copy only the code for the provider that you have
chosen.SQL Clientusing System.Data.Sql Client;

OLE DB Data Providerusing System.Data.Ole Db;

Add the following code to the Form_Load event:SQL Clientstring
strCount;
SqlConnection PubsConn = new SqlConnection
("Data Source=server;i ntegrated " +
"Security=sspi; initial catalog=pubs;") ;
SqlCommand testCMD = new SqlCommand
("TestProcedure 2", PubsConn);

testCMD.Command Type = CommandType.Sto redProcedure;

SqlParameter RetVal = testCMD.Paramet ers.Add
("RetVal", SqlDbType.Int);
RetVal.Directio n = ParameterDirect ion.ReturnValue ;
SqlParameter IdIn = testCMD.Paramet ers.Add
("@au_idIN", SqlDbType.VarCh ar, 11);
IdIn.Direction = ParameterDirect ion.Input;

IdIn.Value = "213-46-8915";

PubsConn.Open() ;

strCount =testCMD.Execut eScalar ().ToString() ;

Console.WriteLi ne("Number of Rows: " + strCount );
Console.WriteLi ne("Return Value: " + RetVal.Value);

OLE DB Data Providerstring strCount;
OleDbConnection PubsConn = new OleDbConnection
("Provider=SQLO LEDB;Data Source=server;" +
"integrated Security=sspi;i nitial catalog=pubs;") ;
OleDbCommand testCMD = new OleDbCommand
("TestProcedure 2", PubsConn);

testCMD.Command Type = CommandType.Sto redProcedure;

OleDbParameter RetVal = testCMD.Paramet ers.Add
("RetVal", OleDbType.Integ er);
RetVal.Directio n = ParameterDirect ion.ReturnValue ;
OleDbParameter IdIn = testCMD.Paramet ers.Add
("@au_idIN", OleDbType.VarCh ar, 11);
IdIn.Direction = ParameterDirect ion.Input;

IdIn.Value = "213-46-8915";

PubsConn.Open() ;

strCount = testCMD.Execute Scalar().ToStri ng() ;

Console.WriteLi ne("Number of Rows: " + strCount);
Console.WriteLi ne("Return Value: " + RetVal.Value);

Modify the connection string for the Connection object to point to the
computer that is running SQL Server.
Run the code. Notice that the ExecuteScalar method of the Command
object returns the parameters. ExecuteScalar also returns the value of
column 1, row 1 of the returned rowset. Therefore, the value of
intCount is the result of the count function from the stored
procedure.
back to the top
Use the ExecuteNonQuery Method of the Command Object
This sample uses the ExecuteNonQuery method to run the query and to
return the parameter values. ExecuteNonQuery also returns the number
of records that are affected after the query runs. However,
ExecuteNonQuery does not return any rows or columns from the stored
procedure.

The ExecuteNonQuery method is most useful when you use INSERT, UPDATE,
or DELETE statements if you only have to know how many rows are
changed. In a stored procedure in which you are using only a SELECT
statement, you receive -1 because no rows are affected by the query.
Create the following stored procedure on the computer that is running
SQL Server:Create Procedure TestProcedure3
(
@au_idIN varchar (11),
@au_fnam varchar (30)
)

As
/* set nocount on */
Update authors set au_fname = @au_fnam
where au_id = @au_idin
return (5)

Create a new Visual C# .NET Windows Application project.
Use the using statement on the System and the System.Data namespaces
so that you do not have to qualify declarations in those namespaces
later in your code. Add this code to the top of the Form code module.
Make sure that you copy only the code for the provider that you have
chosen.SQL Clientusing System.Data.Sql Client;

OLE DB Data Providerusing System.Data.Ole Db;

Replace the code below the private Form1_Load event in the Form1 code
module with the following code:SQL Clientstring strRowAffect;
SqlConnection PubsConn = new SqlConnection
("Data Source=server;i ntegrated Security=sspi;" +
"initial catalog=pubs;") ;
SqlCommand testCMD = new SqlCommand
("TestProcedure 3", PubsConn);

testCMD.Command Type = CommandType.Sto redProcedure;

SqlParameter RetVal = testCMD.Paramet ers.Add
("RetVal", SqlDbType.Int);
RetVal.Directio n = ParameterDirect ion.ReturnValue ;
SqlParameter IdIn = testCMD.Paramet ers.Add
("@au_idIN", SqlDbType.VarCh ar, 11);
IdIn.Direction = ParameterDirect ion.Input;
SqlParameter FnameIn = testCMD.Paramet ers.Add
("@au_fnam", SqlDbType.VarCh ar, 30);
FnameIn.Directi on = ParameterDirect ion.Input;

IdIn.Value = "213-46-8915";
FnameIn.Value = "Marjorie";

PubsConn.Open() ;

strRowAffect =testCMD.Execut eNonQuery ().ToString() ;

Console.WriteLi ne("Number of Rows: " + strRowAffect );
Console.WriteLi ne("Return Value: " + RetVal.Value);

OLE DB Data Providerint intRowAffected;
OleDbConnection PubsConn = new OleDbConnection
("Provider=SQLO LEDB;Data Source=server;" +
"integrated Security=sspi;i nitial catalog=pubs;") ;
OleDbCommand testCMD = new OleDbCommand
("TestProcedure 3", PubsConn);

testCMD.Command Type = CommandType.Sto redProcedure;

OleDbParameter RetVal = testCMD.Paramet ers.Add
("RetVal", OleDbType.Integ er);
RetVal.Directio n = ParameterDirect ion.ReturnValue ;
OleDbParameter IdIn = testCMD.Paramet ers.Add
("@au_idIN", OleDbType.VarCh ar, 11);
IdIn.Direction = ParameterDirect ion.Input;
OleDbParameter FnameIn = testCMD.Paramet ers.Add
("@au_fname" , OleDbType.VarCh ar, 30);
FnameIn.Directi on = ParameterDirect ion.Input;

IdIn.Value = "213-46-8915";
FnameIn.Value = "Marjorie";

PubsConn.Open() ;
intRowAffected = testCMD.Execute NonQuery();

Console.WriteLi ne("Number of Rows affected: " + intRowAffected) ;
Console.WriteLi ne(RetVal.Value );

Modify the connection string for the Connection object to point to the
computer that is running SQL Server.
Run the code. The Output window displays the number of affected rows
(intRowAffect) and the value of the return parameter.
back to the top
REFERENCES
For additional information, visit the following MSDN Web sites:
Introduction to the .NET Framework Class Library
http://msdn.microsoft.com/library/de...asslibrary.asp

Retrieving Data Using the DataReader
http://msdn.microsoft.com/library/de...datareader.asp

back to the top
Jul 20 '05 #1
0 6702

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
7968
by: Chris | last post by:
Hello everyone I want to call a DB2 7.2 stored procedure via ADO in VC++ 7.1 It has 4 parameters, the 1st and the 4th are OUTPUT and the others are INPUT params. My code looks like this: (spCMD is a Command, spCON a Connection, spRS a Resultset, and spPARAM are Parameters) spCMD->Name = "mySPname";
7
3849
by: Malcolm Cook | last post by:
Hi, I've created and installed a custom UDF to populate my combobox, and have defined it per : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/html/acproRowSourceTypeFunctionParameters.asp I'm using ACC2002 in an ADP. However, I'm finding that he control variable (called "code" in the above documentation), is passed into my UDF with a value other than those listed
7
6337
by: M | last post by:
Is there any way to modify the following code so I can run it with any number of paramNames as well as any number of paramValues? So far it works with an array of paramValues, but since you can only pass a variable number of parameters in the last parameter of a method, I'm stumped. Perhaps the paramValues could be multidimensional? And a (?) has to be present for each paramName as well, correct? Thanks in advance.
11
3801
by: anony | last post by:
Hello, I can't figure out why my parameterized query from an ASP.NET page is dropping "special" characters such as accented quotes & apostrophes, the registered trademark symbol, etc. These symbols insert without problem from query analyzer, so that suggests it's something within ASP.NET. I've tried using .NET textbox web controls as well as html textareas. I have a test database set up with 4 fields: varchar, nvarchar, text, and...
1
2392
by: Sheldon Penner | last post by:
I have been trying to build a web form using the SQL Data Adapter Configuration Wizard to create a dataset based on a parameterized stored procedure. I find that if I select "Use existing stored procedure" on the "Choose a query type" screen, the following screen allows me to choose only one parameter, and the parameter must be selected from the output fields of the procedure. My proc has two parameters and neither one is returned in the...
3
4437
by: dew | last post by:
I have a stored procedure that retrieves the id of a client table, which is a guid To get that id, I have dim clientid as guid = GetClientID("OrgName") The GetClientID returns a string, because sometimes it's not a guid, but in this procedure it is. How do I convert the GetClientID into a Guid? I tried
0
3621
by: james.peer | last post by:
I'm fairly new to this so hopefully it is a dumb mistake... I need to create a simple stored procedure along the lines of: delimiter // CREATE DEFINER='root'@'localhost' PROCEDURE Insertuser() BEGIN insert into user (firstname,lastname) values (@fn, @ln); END; //
1
13831
by: Groningen | last post by:
Hi, I was wondering if someone could help me on this one. I've made a stored procedure in SQL Server Enterprise Manager. When I call the stored procedure in Visual Basic I didn't succeed to return a SQL value. I'm calling the procedure using the following VB source code: Dim param1 As ADODB.Parameter Dim aantal As Integer Set ADOCMD = New ADODB.Command
0
2770
TonFrere
by: TonFrere | last post by:
Hello, I'm building a windows form application in Visual C# using VS 2005. On my form I need to populate a combobox with Invoices# linked to the current reccord's Order# value. This means that: - The combobox should repopulate if the user changes the value of Order#. - The combobox should repopulate if the user moves through records (using the binding navigator. My problem is that whenever I repopulate the combobox, it's value is reset to...
0
8786
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9466
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9253
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8202
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4564
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2190
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.