ADO.NET(三)调用存储过程
用一个命令对象调用存储过程,就是定义存储过程的名称,给过程的每个参数添加参数定义,然后调用一个Execute的函数。不怎么想写这个定义,感觉没什么意思,不写又觉得不好,算了,全当练习打字了。还是举例子子吧!~~
当然我们要有个存储过程:
CREATE PROCEDURE [update_user_table_1]
(@id_1 [int],
@id_2 [int],
@name_3 [varchar](50),
@age_4 [int],
@sex_5 [char](10),
@profession_6 [varchar](50))
AS UPDATE [first_dotnet].[dbo].[user_table]
SET [id] = @id_2,
[name] = @name_3,
[age] = @age_4,
[sex] = @sex_5,
[profession]= @profession_6
WHERE
( [id] = @id_1)
GO
然后我们就要用这个存储过程
string connStr = "server=127.0.0.1;database=first_dotnet;User ID=sa;password=;Persist Security Info=true;";
SqlConnection con = new SqlConnection(connStr);
string procName = "update_user_table_1";
try
{
SqlCommand myCommand = new SqlCommand(procName, con);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@id_1", 5);
myCommand.Parameters.Add("@id_2", 21);
myCommand.Parameters.Add("@name_3", "小七");
myCommand.Parameters.Add(" @age_4,", 30);
myCommand.Parameters.Add(" @sex_5", "男");
myCommand.Parameters.Add("@profession_6 ", "会计");
if (con.State != ConnectionState.Open)
{
con.Open();
}
int updataCount = myCommand.ExecuteNonQuery();
con.Close();
Console.WriteLine("已经插入了" + updataCount + "条记录");
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}