This article summarizes the C# call SQL2000 stored procedure method and shares it for your reference. The specific implementation method is as follows:
Debugging environment: 2005, SQL 2000, WINDOWS XP sp2.
Language: C#
1. Call a stored procedure with input parameters
First of all, we naturally create a stored procedure in the query analyzer~~ As shown below:
@uid int,
@pwd varchar(255)
as
select UserName from users where uid = @uid and PassWord = @pwd
go
Next, let’s take a look at how to use C# to call this stored procedure in VS 2005.
Method 1:
();//Open the database connection
SqlCommand cmd = new SqlCommand("exec proc_1 @uid=1,@pwd=admin", conn);//Write statements in Command that call stored procedures
//You should see that it is very similar to directly performing in the query analyzer, "exec stored procedure name @parameter 1=parameter value, @parameter 2=parameter value"
SqlDataReader sdr = ();//Execute stored procedures
while (())
{
(sdr["UserName"].ToString());//Output the query value
}
();
();
Method 2:
();//Open the database connection
SqlCommand cmd = new SqlCommand("proc_1", conn);//Stored procedure name
= ;//Set the command type as a stored procedure
SqlParameter[] param = new SqlParameter[]{
new SqlParameter("uid", , 4),
new SqlParameter("pwd", ,255),
};//Define parameters, these are the parameters to be called in the stored procedure, and you can also add parameters by directly using the method
param[0].Value = 1;// Assign a value to the parameter
param[1].Value = "admin";
(param);//Be sure to add the parameters you just defined to the cmd parameter, otherwise the previous parameters will be useless.
string sname =(string)();//If the stored procedure returns a single value, we can directly retrieve the desired value
(sname);
();
();
2. Call a stored procedure with output parameters
What we talk about above is just a stored procedure that calls input parameters. Here is a brief description of how to call stored procedure that has output parameters.
First, let’s modify the previous stored procedure first, as shown below:
@uid int,
@pwd varchar(255),
@UserName varchar(255) output -- Here we add an output variable, remember to add output
as
select @UserName=UserName from users where uid = @uid and PassWord = @pwd
go
--The following are the methods called in the query analyzer
declare @n varchar(255) --Declare a variable to pass parameters
exec proc_1 1,admin,@n output --note, to be marked as output variable
print @n
Let's take a look at the call method in :
();
SqlCommand cmd = new SqlCommand("proc_1", conn);
= ;
("uid", 1);
("pwd", "admin");
("username", , 255);
["username"].Direction = ;//Set the parameter to the output parameter
();
string sname = (string)["username"].Value;//Get the value of the output parameter
(sname);
();
In this way, it is very simple to implement the method of calling stored procedures.
I hope this article will be helpful to everyone's C# programming.