/// <summary>
/// 直接执行SQL语句
/// </summary>
/// <param name="procName">命令文本</param>
/// <returns></returns>
public int RunProcTran(string procName, SqlTransaction sqlLevel)
{
if (null == SessionFactory.getConnection())
{
return(0);
}
SqlCommand cmd = new SqlCommand(procName, SessionFactory.getConnection());//创建SqlCommand命令对象
cmd.Transaction = sqlLevel;
cmd.ExecuteNonQuery(); //执行SQL命令
return(1); //返回1，表示执行成功
}
/// <summary>
/// 直接执行SQL语句
/// </summary>
/// <param name="procName">命令文本</param>
/// <returns></returns>
public int RunProc(string procName)
{
if (null == SessionFactory.getConnection())
{
return(0);
}
SqlCommand cmd = new SqlCommand(procName, SessionFactory.getConnection()); //创建SqlCommand命令对象
cmd.ExecuteNonQuery(); //执行SQL命令
SessionFactory.Close(); //关闭数据库连接
return(1); //返回1，表示执行成功
}
public MessageLocal saveOrUpdateDataGridView(User user, string table, DataGridView dataGridView)
{
MessageLocal msg = new MessageLocal();
SqlConnection con = SessionFactory.getConnection();
SqlTransaction sqlTran = con.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
int tableID = getTableIDByName(table, sqlTran);
if (tableID < 0)
{
msg.Message = "该表格的信息已经被删除，无法更改";
sqlTran.Commit();
return(msg);
}
int i = 0;
for (i = 0; i < dataGridView.Columns.Count; i++)
{
SqlParameter[] prams =
{
database.MakeInParam("@user", SqlDbType.Int, 0, user.Id),
database.MakeInParam("@table", SqlDbType.Int, 0, tableID),
database.MakeInParam("@original_index", SqlDbType.Int, 0, i),
database.MakeInParam("@display_index", SqlDbType.Int, 0, dataGridView.Columns[i].DisplayIndex),
database.MakeInParam("@width", SqlDbType.Int, 0, dataGridView.Columns[i].Width),
database.MakeInParam("@sort", SqlDbType.Int, 0, 0),
};
// MessageBox.Show("original:[" + i + "] display:[" + dataGridView.Columns[i].DisplayIndex + "]");
database.RunProcTran("saveOrUpdate_table", prams, CommandType.StoredProcedure, sqlTran);
}
sqlTran.Commit();
}
catch (Exception e)
{
sqlTran.Rollback();
MessageBox.Show(e.Message);
msg.IsSucess = false;
msg.Message = "在保存表格样式时，出现错误！";
return(msg);
}
finally
{
con.Close();
}
msg.IsSucess = true;
return(msg);
}
/// <summary>
/// 创建一个SqlDataAdapter对象以此来执行命令文本
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams">参数对象</param>
/// <returns></returns>
private SqlDataAdapter CreateDataAdaper(string procName, SqlParameter[] prams, CommandType ct)
{
if (null == SessionFactory.getConnection())
{
return(null);
}
SqlDataAdapter dap = new SqlDataAdapter(procName, SessionFactory.getConnection()); //创建桥接器对象
dap.SelectCommand.CommandType = ct; //指定要执行的类型为命令文本
if (prams != null) //判断SQL参数是否不为空
{
foreach (SqlParameter parameter in prams) //遍历传递的每个SQL参数
{
dap.SelectCommand.Parameters.Add(parameter); //将SQL参数添加到执行命令对象中
}
}
//加入返回参数
dap.SelectCommand.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return(dap);//返回桥接器对象
}
/// <summary>
/// 创建一个SqlCommand对象以此来执行命令文本
/// </summary>
/// <param name="procName">命令文本</param>
/// <param name="prams"命令文本所需参数</param>
/// <returns>返回SqlCommand对象</returns>
public SqlCommand CreateCommand(string procName, SqlParameter[] prams, CommandType ct)
{
if (null == SessionFactory.getConnection())
{
return(null);
}
SqlCommand cmd = new SqlCommand(procName, SessionFactory.getConnection()); //创建SqlCommand命令对象
cmd.CommandType = ct; //指定要执行的类型为命令文本
// 依次把参数传入命令文本
if (prams != null) //判断SQL参数是否不为空
{
foreach (SqlParameter parameter in prams) //遍历传递的每个SQL参数
{
cmd.Parameters.Add(parameter); //将SQL参数添加到执行命令对象中
}
}
//加入返回参数
cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return(cmd);//返回SqlCommand命令对象
}
public MessageLocal deleteEntity(string procedure, string table, int id)
{
MessageLocal msg = new MessageLocal();
SqlParameter[] prams =
{
database.MakeInParam("@id", SqlDbType.Int, 0, id),
new SqlParameter("rval", SqlDbType.Int, 0)
};
prams[prams.Length - 1].Direction = ParameterDirection.ReturnValue;
SqlConnection con = SessionFactory.getConnection();
SqlTransaction sqlTran = con.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
DataSet dataSet = database.RunProcReturnTran(procedure, prams, table, CommandType.StoredProcedure, sqlTran);
if ("0".Equals(prams[prams.Length - 1].Value.ToString()))
{
msg.IsSucess = true;
}
sqlTran.Commit();
}
catch (Exception e)
{
sqlTran.Rollback();
msg.IsSucess = false;
msg.Message = e.Message;
MessageBox.Show(e.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
con.Close();
}
return(msg);
}
public MessageLocal saveOrUpdateXialiaoOrder(XialiaoOrder xialiaoOrder)
{
MessageLocal msg = new MessageLocal();
SqlConnection con = SessionFactory.getConnection();
SqlTransaction sqlTran = con.BeginTransaction(IsolationLevel.RepeatableRead);
LinkedList <SqlParameter> prams = new LinkedList <SqlParameter>();
try
{
msg = baseDao.runProcedureTran("saveOrUpdate_xialiao_order", constructParamsForXialiaoOrder(xialiaoOrder), "xialiao_order", sqlTran);
if (!msg.IsSucess)
{
throw new Exception(msg.Message);
}
// 如果是新增的下料订单，需要更新ID
string sn = msg.Message;
if (xialiaoOrder.Id <= 0)
{
DataSet dataset = database.RunProcReturnTran("SELECT id FROM tb_xialiao_order WHERE sn = '" + sn + "'", "tb_xialiao_order", CommandType.Text, sqlTran);
if (null != dataset && dataset.Tables[0].Rows.Count > 0)
{
xialiaoOrder.Id = int.Parse(getValue(dataset, "id"));
xialiaoOrder.Sn = sn;
}
else
{
throw new Exception("查询订单编号【" + xialiaoOrder.Sn + "】失败！");
}
}
foreach (XialiaoOrderItem item in xialiaoOrder.XialiaoOrderItems)
{
msg = baseDao.runProcedureTran("saveOrUpdate_xialiao_order_item", constructParamsForXialiaoOrderItem(item), "order_item", sqlTran);
if (!msg.IsSucess)
{
throw new Exception(msg.Message);
}
item.Id = int.Parse(msg.Message);
}
foreach (XialiaoOrderStats stats in xialiaoOrder.XialiaoOrderstats)
{
msg = baseDao.runProcedureTran("saveOrUpdate_xialiao_order_stats", constructParamsForXialiaoOrderStats(stats), "order_stats", sqlTran);
if (!msg.IsSucess)
{
throw new Exception(msg.Message);
}
stats.Id = int.Parse(msg.Message);
}
sqlTran.Commit();
}
catch (Exception e)
{
sqlTran.Rollback();
msg.Message = "保存失败！" + e.Message;
msg.IsSucess = false;
Console.WriteLine(e.StackTrace);
return(msg);
}
finally
{
con.Close();
}
msg.IsSucess = true;
return(msg);
}