执行一条sql语句，参数传递越简单越好

我发现很多人在写新增和修改时，参数写得比较麻烦。先定义与表相关的所有参数，然后还得按顺序赋值，要是赋值顺序不正确，导致提交出错。

现在这里在定义参数的同时马上赋值，代码要少很多，例如：

第一种方法：

public static int CreateCar(T_CarList dataModel)
{
    OleDbParameter[] sps = { 
                             new OleDbParameter("@CompanyId",dataModel.CompanyId),
                             new OleDbParameter("@DepartmentId",dataModel.DepartmentId),
                             new OleDbParameter("@TypeId",dataModel.TypeId),
                             new OleDbParameter("@VehicleNo",dataModel.VehicleNo),
                             new OleDbParameter("@PlateNo",dataModel.PlateNo),
                             new OleDbParameter("@SiteNum",dataModel.SiteNum),
                             new OleDbParameter("@Notes",dataModel.Notes),
                             new OleDbParameter("@CreatedBy",dataModel.CreatedBy),
                           };
    string sql = @"INSERT INTO T_Car(CompanyId,DepartmentId,TypeId,VehicleNo,PlateNo,SiteNum,Notes,CreatedBy,CreatedDate)
                    VALUES(?,?,?,?,?,?,?,?,GETDATE());
                    SELECT @@IDENTITY;";
    DataSet ds = DbHelper.Current.ExecuteDataset(CommandType.Text, sql, sps);
    int newid = int.Parse(ds.Tables[0].Rows[0][0].ToString());
    return newid;
}

这种写法有问号。

第二种写法：

public static DataTable GetContactsInfo(int departmentId, int jobId, string name)
{
    string sql = @"select users.Id, users.RealName,Department=department.[Text],Job=userType.[Text],MobileNo=isnull(users.MobilePhone,''),Email=isnull(users.Email,''),Users.Word,DeptJobName= department.[Text] + '_' + userType.[Text] + '_' + users.RealName
                   from t_User users
                   left join t_DictDepartment department on users.DepartmentId=department.id
                   left join T_DictUserType userType on users.TypeId=userType.Id
                   where department.ParentId=1 and users.Dimission=0
                   and (?<=1 or users.DepartmentId=?)
                   and (?<=1 or users.TypeId=?)
                   and (len(isnull(?,''))=0 or users.RealName like '%'+?+'%')
                   order by users.RealName;";
    OleDbParameter[] parms = new OleDbParameter[6];
    parms[0] = new OleDbParameter("@DepartmentId", OleDbType.Integer);
    parms[0].Value = departmentId;
    parms[1] = new OleDbParameter("@DepartmentId", OleDbType.Integer);
    parms[1].Value = departmentId;
    parms[2] = new OleDbParameter("@JobId", OleDbType.Integer);
    parms[2].Value = jobId;
    parms[3] = new OleDbParameter("@JobId", OleDbType.Integer);
    parms[3].Value = jobId;
    parms[4] = new OleDbParameter("@Name", OleDbType.VarChar);
    parms[4].Value = name;
    parms[5] = new OleDbParameter("@Name", OleDbType.VarChar);
    parms[5].Value = name;

    DataSet ds = new DataSet();
    ds = DbHelper.ErpUser.ExecuteDataset(CommandType.Text, sql, parms);
    ds.DataSetName = "ContactList";
    if (ds.Tables.Count == 0) return null;
    if (ds.Tables[0].Rows.Count == 0) return null;
    return ds.Tables[0];
}

这种写法顺序必须对应得上，否则出错。

第三种写法：

/// <summary>
/// 创建CenterCategoryORM数据实例
/// </summary>
public static ExecuteResult CreateModel(CenterCategoryORM dataModel)
{
    string sqlStatement = @"INSERT INTO CenterCategory(NameCN,NameEN,Enabled,Remarks,CreatedBy,CreatedAt)
                        VALUES(@NameCN,@NameEN,@Enabled,@Remarks,@CreatedBy,getdate());
                        SELECT @@IDENTITY;";
    SqlCommand command = new SqlCommand();
    command.CommandText = sqlStatement; 
    command.Parameters.Add("@NameCN", SqlDbType.NVarChar, 200).Value = dataModel.NameCN;
    command.Parameters.Add("@NameEN", SqlDbType.NVarChar, 200).Value = dataModel.NameEN;

    command.Parameters.Add("@Enabled", SqlDbType.Bit).Value = 1;
    command.Parameters.Add("@Remarks", SqlDbType.NVarChar, 500).Value = dataModel.Remarks;
    command.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = dataModel.CreatedBy;
    return DBProvider.DefaultDBOperator.ExecuteScalar(command);
}

这种写法最简单，最明了。问题在哪里呢，大家只是对SqlCommand不太理解，不敢用。

以下是第三种方法相关的辅助方法：

/// <summary>
/// 执行一个SqlCommand对象，并返回其执行结果: 是否成功。
/// 如果SqlCommand，如果SqlCommand并未指定Connection，则系统会自动指定默认的Connection。
/// 如果SqlCommand自行已经指定Connection，则系统使用Command外部带进来的Connection对象，其外部对象的Connection.State必须是关闭的.
/// </summary>
/// <param name="sqlCommand"></param>
/// <returns></returns>
public ExecuteResult ExecuteNonQuery(SqlCommand sqlCommand)
{
    ChangeNullToDBNullValue(sqlCommand);
    bool useDefaultConnection = false;
    if (sqlCommand.Connection == null)
    {
        useDefaultConnection = true;
        sqlCommand.Connection = new SqlConnection(this.connectionString);
    }
    else
    {
        useDefaultConnection = false;
        if (sqlCommand.Connection.State != ConnectionState.Closed)
        {
            throw new ArgumentException("SqlCommand's connection state must be closed.");
        }
    }
    sqlCommand.Connection.Open();
    sqlCommand.Transaction = sqlCommand.Connection.BeginTransaction();
    try
    {
        int rows = sqlCommand.ExecuteNonQuery();
        sqlCommand.Transaction.Commit();
        return new ExecuteResult() { ActionStatus = ActionStatusType.Success, ReturnValue = rows };
    }
    catch (SqlException ex)
    {
        sqlCommand.Transaction.Rollback();
        DBOperatorLogsWritter.WriteDBErrorLog(ex, sqlCommand);
        return new ExecuteResult() { ActionStatus = ActionStatusType.Fail, Message = ex.Message };
    }
    finally
    {
        sqlCommand.Connection.Close();
        if (useDefaultConnection)
        {
            sqlCommand.Connection = null;
        }
    }
}
/// <summary>
/// 执行一个SqlCommand語句，并返回其执行结果: 是否成功。
/// 系统会自动指定默认的Connection。
/// </summary>
/// <param name="sqlCommand"></param>
/// <returns></returns>
public ExecuteResult ExecuteNonQuery(string sqlCommandText)
{
    SqlCommand command = new SqlCommand(sqlCommandText);
    return ExecuteNonQuery(command);
}
/// <summary>
/// 执行一个SqlCommand对象，并返回其执行结果。
/// 如果SqlCommand，如果SqlCommand并未指定Connection，则系统会自动指定默认的Connection。
/// 如果SqlCommand自行已经指定Connection，则系统使用Command外部带进来的Connection对象，其外部对象的Connection.State必须是关闭的.
/// </summary>
/// <param name="sqlCommand"></param>
/// <returns></returns>
public ExecuteResult ExecuteScalar(SqlCommand sqlCommand)
{
    ChangeNullToDBNullValue(sqlCommand);
    bool useDefaultConnection = false;
    if (sqlCommand.Connection == null)
    {
        useDefaultConnection = true;
        sqlCommand.Connection = new SqlConnection(this.connectionString);
    }
    else
    {
        useDefaultConnection = false;
        if (sqlCommand.Connection.State != ConnectionState.Closed)
        {
            throw new ArgumentException("SqlCommand's connection state must be closed.");
        }
    }
    sqlCommand.Connection.Open();
    sqlCommand.Transaction = sqlCommand.Connection.BeginTransaction();
    try
    {
        object returnValue = sqlCommand.ExecuteScalar();
        sqlCommand.Transaction.Commit();
        return new ExecuteResult() { ActionStatus = ActionStatusType.Success, ReturnValue = returnValue };
    }
    catch (SqlException ex)
    {
        sqlCommand.Transaction.Rollback();
        DBOperatorLogsWritter.WriteDBErrorLog(ex, sqlCommand);
        return new ExecuteResult() { ActionStatus = ActionStatusType.Fail, Message = ex.Message };
    }
    finally
    {
        sqlCommand.Connection.Close();
        if (useDefaultConnection)
        {
            sqlCommand.Connection = null;
        }
    }
}
public ExecuteResult ExecuteScalar(string sqlCommandText)
{
    SqlCommand command = new SqlCommand(sqlCommandText);
    return ExecuteScalar(command);
}

/// <summary>
/// 将Command相关参数为Null值的转换成DBNull参数.
/// </summary>
/// <param name="command"></param>
private void ChangeNullToDBNullValue(SqlCommand command)
{
    foreach (SqlParameter para in command.Parameters)
    {
        if (para.Value == null)
        {
            para.Value = DBNull.Value;
        }
        else
        {
            if (para.Value is string)
            {
                if (string.IsNullOrWhiteSpace(para.Value.ToString()))
                {
                    para.Value = string.Empty;
                }
            }
        }
    }
}


//执行相关的类
public enum ActionStatusType
{
    Success,
    Fail
}
[Serializable]
public class ExecuteResult
{
    public ActionStatusType ActionStatus { get; set; }
    public string Message { get; set; }
    public object ReturnValue { get; set; }
}