我发现很多人在写新增和修改时,参数写得比较麻烦。先定义与表相关的所有参数,然后还得按顺序赋值,要是赋值顺序不正确,导致提交出错。
现在这里在定义参数的同时马上赋值,代码要少很多,例如:
第一种方法:
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; }
}