如何在c#中正确地传递参数
本文关键字:参数 正确地 | 更新日期: 2023-09-27 18:14:02
我的存储过程如下:
ALTER PROCEDURE [dbo].[Insert_tblCustomer]
-- Add the parameters for the stored procedure here
@Username varchar(20) = null,
@Password varchar(20)= null,
@UserType varchar(20)='User',
@FirstName varchar(50)=null,
@LastName varchar(50)=null,
@DateOfBirth varchar(50)=null,
@Gender varchar(10)=null,
@Unit_No int = null,
@St_No int=null,
@St_Name varchar(20)=null,
@Suburb varchar(20)=null,
@State varchar(20)=null,
@Postcode int=null,
@Email varchar(50)='',
@Phone varchar(15)=null
为…有5个字段,即用户名,密码,名,姓和电子邮件是必须字段。现在从c#,我正在尝试这个:
dbConnection target = new dbConnection(); // TODO: Initialize to an appropriate value
string _query = string.Empty; // TODO: Initialize to an appropriate value
SqlParameter[] param = new SqlParameter[5]; // TODO: Initialize to an appropriate value
param[0] = new SqlParameter("@Username", "hakoo");
param[1] = new SqlParameter("@Password", "hakoo");
param[2] = new SqlParameter("@FirstName", "Hakoo");
param[3] = new SqlParameter("@LastName", "Hakoo");
param[4] = new SqlParameter("@Email", "haks@abc.com");
bool expected = true; // TODO: Initialize to an appropriate value
bool actual;
actual = target.Execute_InsertQuery("dbo.Insert_tblCustomer", param);
但是,我得到Assertfail异常。我是使用存储过程与测试的新手。有人能纠正我的错误吗?
" add Assert part"
bool expected = true; // TODO: Initialize to an appropriate value
bool actual;
actual = target.Execute_InsertQuery("dbo.Insert_tblCustomer", param);
dbConnection是我创建的核心类,我把通用查询:
public bool Execute_InsertQuery(string _query, SqlParameter[] param)
{
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = OpenConnection();
cmd.CommandText = _query;
cmd.Parameters.AddRange(param);
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Console.Write(ex.StackTrace.ToString());
return false;
}
}
获取错误的更新:我尝试以这种方式传递所有参数:
dbConnection c = new dbConnection();
System.Data.SqlClient.SqlParameter[] param = new System.Data.SqlClient.SqlParameter[15];
param[0] = new SqlParameter("@Username", "hakoo");
param[1] = new SqlParameter("@Password", "hakoo");
param[2] = new SqlParameter("@UserType", "User");
param[3] = new SqlParameter("@FirstName", "Hakoo");
param[4] = new SqlParameter("@LastName", "Hakoo");
param[5] = new SqlParameter("@DateOfBirth", "02/11/88");
param[6] = new SqlParameter("@Gender", "");
param[7] = new SqlParameter("@Unit_No", null);
param[8] = new SqlParameter("@St_No", 25);
param[9] = new SqlParameter("@St_Name", "anc st");
param[10] = new SqlParameter("@Suburb", "ancst");
param[11] = new SqlParameter("@State", "assadd@few");
param[12] = new SqlParameter("@Postcode", 2615);
param[13] = new SqlParameter("@Email", "abc@def.com.au");
param[14] = new SqlParameter("@Phone", "165103548");
c.Execute_InsertQuery("dbo.Insert_tblCustomer", param);
但是对于这个,我得到这个错误:
参数化查询'(@Username nvarchar(5),@Password nvarchar(5),@UserType nvarchar('期望参数'@Unit_No',该参数未提供。我可以在SQL Server中执行这个Store过程。
传递参数没有单一的最佳方式。我要说的是,在命令(SqlCommand)之外有一个像SqlParameter这样的东西是一个坏主意——如果你还没有处理数据库,我只会把它们作为字符串/int/等传递,或者作为对象的成员。
在没有看到命令相关代码的情况下,我只能说:42
可以帮助指定命令类型。
SqlCommand cmd = new SqlCommand();
cmd.Connection = OpenConnection();
cmd.CommandText = _query;
cmd.CommandType = CommandType.StoredProcedure;