SqlParameter Value or SqlValue?
本文关键字：SqlValue or Value SqlParameter | 更新日期: 2023-09-27 18:27:04
我收到一个错误，上面写着：
SqlParameterCollection只接受非null的SqlParameter类型对象，而不接受字符串对象
在此代码上：
.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar,128).Value = username);
如果我将其更改为：
.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar,128).SqlValue = username);
难道不应该只重视工作吗？
sqlValue不是数据库类型吗？
这是我使用的DAL：
public class DBAccess : IDisposable
{
private IDbCommand cmd = new SqlCommand();
private string strConnectionString = "";
private bool handleErrors = false;
private string strLastError = "";
public DBAccess()
{
strConnectionString = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
SqlConnection cnn = new SqlConnection();
cnn.ConnectionString = strConnectionString;
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
}
public CommandType CommandType
{
get
{
return cmd.CommandType;
}
set
{
cmd.CommandType = value;
}
}
public IDataReader ExecuteReader()
{
IDataReader reader = null;
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
this.Open();
}
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return reader;
}
public IDataReader ExecuteReader(string commandtext)
{
IDataReader reader = null;
try
{
cmd.CommandText = commandtext;
reader = this.ExecuteReader();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return reader;
}
public object ExecuteScalar()
{
object obj = null;
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
this.Open();
}
obj = cmd.ExecuteScalar();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return obj;
}
public object ExecuteScalar(string commandtext)
{
object obj = null;
try
{
cmd.CommandText = commandtext;
obj = this.ExecuteScalar();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return obj;
}
public int ExecuteNonQuery()
{
int i = -1;
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
this.Open();
}
i = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return i;
}
public int ExecuteNonQuery(string commandtext)
{
int i = -1;
try
{
cmd.CommandText = commandtext;
i = this.ExecuteNonQuery();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return i;
}
public DataSet ExecuteDataSet()
{
SqlDataAdapter da = null;
DataSet ds = null;
try
{
if (cmd.Connection.State == ConnectionState.Closed)
{
this.Open();
}
da = new SqlDataAdapter();
da.SelectCommand = (SqlCommand)cmd;
ds = new DataSet();
da.Fill(ds);
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return ds;
}
public DataSet ExecuteDataSet(string commandtext)
{
DataSet ds = null;
try
{
cmd.CommandText = commandtext;
ds = this.ExecuteDataSet();
}
catch (Exception ex)
{
if (handleErrors)
strLastError = ex.Message;
else
throw;
}
return ds;
}
public int CommandTimeout
{
get
{
return cmd.CommandTimeout;
}
set
{
cmd.CommandTimeout = value;
}
}
public IDbConnection Connection
{
get
{
return cmd.Connection;
}
set
{
cmd.Connection = value;
}
}
public string CommandText
{
get
{
return cmd.CommandText;
}
set
{
cmd.CommandText = value;
cmd.Parameters.Clear();
}
}
public IDataParameterCollection Parameters
{
get
{
return cmd.Parameters;
}
}
public IDbTransaction Transaction
{
get
{
return cmd.Transaction;
}
set
{
cmd.Transaction = value;
}
}
public void AddParameter(string paramname, object paramvalue)
{
var param = new SqlParameter(paramname, paramvalue);
cmd.Parameters.Add(param);
}
public void AddParameter(IDataParameter param)
{
cmd.Parameters.Add(param);
}
public IDbTransaction BeginTransaction()
{
var tran = cmd.Connection.BeginTransaction();
cmd.Transaction = tran;
return tran;
}
public void CommitTransaction()
{
cmd.Transaction.Commit();
}
public void RollbackTransaction()
{
cmd.Transaction.Rollback();
}
public System.Data.ConnectionState State
{
get
{
return cmd.Connection.State;
}
}
public string ConnectionString
{
get
{
return strConnectionString;
}
set
{
strConnectionString = value;
}
}
private void Open()
{
cmd.Connection.Open();
}
public bool HandleExceptions
{
get
{
return handleErrors;
}
set
{
handleErrors = value;
}
}
public string LastError
{
get
{
return strLastError;
}
}
public void Dispose()
{
cmd.Connection.Close();
cmd.Connection.Dispose();
cmd.Dispose();
}
}
这似乎解决了问题。
.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar, 128) { Value = username });
实际上，即使是第二行代码也无法工作，因为两个
new SqlParameter("@username", SqlDbType.NVarChar,128).Value = username
和
new SqlParameter("@username", SqlDbType.NVarChar,128).SqlValue = username
是计算为字符串对象的表达式，因为错误告诉您SqlParameterCollection不接受字符串对象。
添加参数值的一种更简洁的方法是这样的：
.Parameters.Add("@username", SqlDbType.NVarChar, 128).Value = username;
这是可能的，因为Add返回一个SqlParameter对象，因此您可以在同一行设置其Value属性。
尝试这样的代码。
SqlParameter param = new SqlParameter("@username",SqlDbType.NVarChar, 128);
param.Value = username;
command.Parameters.Add(param);
参见http://msdn.microsoft.com/en-us/library/40959t6x（v=vs.110）.aspx。
尝试AddWithValue。
command.Parameters.AddWithValue("@username", username);