/// <summary>
/// 执行非查询操作（增删改）
/// </summary>
/// <param name="nonQuerySql">SQL语句</param>
public static void ExecuteNonQuery(string nonQuerySql)
{
if (!string.IsNullOrEmpty(nonQuerySql))
{
SqlConnection con = GetConnect();
SqlCommand sqlCommand = new SqlCommand(nonQuerySql, con);
SqlTransaction sqlTransaction = con.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
try
{
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch (Exception e)
{
sqlTransaction.Rollback();
DevExpress.XtraEditors.XtraMessageBox.Show(e.Message, "数据出错(详情查看错误日志)");
LogsHelper.AddErrorLogs("执行SQL语句失败", $"SQL语句为 >> {nonQuerySql}");
}
}
}
/// <summary>
/// 添加信息到指定表格
/// </summary>
private void SaveFileInfo(bool isAdd)
{
bool isOtherType = cbo_categor.SelectedIndex == -1;
object primaryKey = Guid.NewGuid().ToString();
object stage = cbo_stage.SelectedValue;
object categor = cbo_categor.SelectedValue ?? cbo_categor.Tag;
object categorName = isOtherType ? cbo_categor.Text.Split('-')[1].Trim() : null;
object code = txt_fileCode.Text;
object name = txt_fileName.Text.Replace("'", "''");
object user = txt_User.Text;
object type = GetRadioValue(pal_type);
object pages = num_Pages.Value;
object count = num_Count.Value;
object amount = num_Amount.Value;
object date = txt_date.Text;
object unit = txt_Unit.Text;
object carrier = GetCarrierValue();
object link = GetFullStringBySplit(GetLinkList(2), "；", string.Empty);
string _fileId = GetFullStringBySplit(GetLinkList(1), ",", "'");
object remark = txt_Remark.Text;
object[] flink = GetFileLink();
if (isAdd)
{
if (isOtherType)
{
categor = Guid.NewGuid().ToString();
object pid = cbo_stage.SelectedValue;
string value = txt_fileCode.Text.Split('-')[0];
int sort = cbo_categor.Items.Count - 1;
object dicId = SqlHelper.ExecuteOnlyOneQuery($"SELECT dd_id FROM data_dictionary WHERE dd_name='{value}' AND dd_pId='{pid}'");
string sqlString = string.Empty;
if (dicId != null)
{
categor = dicId;
sqlString += $"DELETE FROM data_dictionary WHERE dd_name='{value}' AND dd_pId='{stage}';";
}
sqlString += "INSERT INTO data_dictionary (dd_id, dd_name, dd_note, dd_pId, dd_sort, extend_3, extend_4) " +
$"VALUES('{categor}', '{value}', '{name}', '{pid}', '{sort}', '{categorName}', '{1}');";
SqlHelper.ExecuteNonQuery(sqlString);
}
StringBuilder insertSql = new StringBuilder();
insertSql.Append("INSERT INTO processing_file_list (pfl_id, pfl_stage, pfl_categor, pfl_code, pfl_name, pfl_user, pfl_type, pfl_pages, pfl_count, pfl_amount, pfl_date, pfl_unit, pfl_carrier, pfl_link, pfl_file_id, pfl_obj_id, pfl_status, pfl_sort, pfl_remark, pfl_worker_id, pfl_worker_date) " +
$"VALUES( '{primaryKey}', '{stage}', '{categor}', '{code}', '{name}', '{user}', '{type}', '{pages}', '{count}', '{amount}', '{date}', '{unit}', '{carrier}', '{link}', '{GetFullStringBySplit(GetLinkList(1), ",", string.Empty)}', '{parentId}', -1, '{view.RowCount}', '{remark}', '{UserHelper.GetUser().UserKey}', '{DateTime.Now}');");
//将备份表中的文件标记为已选取
if (!string.IsNullOrEmpty(_fileId))
{
insertSql.Append($"UPDATE backup_files_info SET bfi_state=1 WHERE bfi_id IN ({_fileId});");
}
foreach (object item in flink)
{
insertSql.Append($"INSERT INTO file_link(id, code, fid) VALUES('{Guid.NewGuid().ToString()}', '{item}', '{primaryKey}');");
}
SqlHelper.ExecuteNonQuery(insertSql.ToString());
LogsHelper.AddWorkLog(WorkLogType.File, 1, BATCH_ID, 1, primaryKey);
}
else
{
string oldFileId = GetValue(SqlHelper.ExecuteOnlyOneQuery($"SELECT pfl_file_id FROM processing_file_list WHERE pfl_id='{fileId}';"));
StringBuilder updateSql = new StringBuilder();
if (!string.IsNullOrEmpty(oldFileId))
{
updateSql.Append($"UPDATE backup_files_info SET bfi_state=0 WHERE bfi_id IN ({ToolHelper.GetFullStringBySplit(oldFileId, ',', ",", "'")});");
}
updateSql.Append("UPDATE processing_file_list SET " +
$"[pfl_stage] = '{stage}'" +
$",[pfl_categor] = '{categor}'" +
$",[pfl_code] = '{code}'" +
$",[pfl_name] = '{name}'" +
$",[pfl_user] = '{user}'" +
$",[pfl_type] = '{type}'" +
$",[pfl_pages] = '{pages}'" +
$",[pfl_count] = '{count}'" +
$",[pfl_amount] = '{amount}'" +
$",[pfl_date] = '{date}'" +
$",[pfl_unit] = '{unit}'" +
$",[pfl_carrier] = '{carrier}'" +
$",[pfl_link] = '{link}'" +
$",[pfl_remark] = '{remark}'" +
$",[pfl_file_id] = '{GetFullStringBySplit(GetLinkList(1), ",", string.Empty)}'" +
$" WHERE pfl_id= '{fileId}';");
if (!string.IsNullOrEmpty(_fileId))
{
updateSql.Append($"UPDATE backup_files_info SET bfi_state=1 WHERE bfi_id IN ({_fileId});");
}
updateSql.Append($"DELETE FROM file_link WHERE fid='{fileId}';");
foreach (object item in flink)
{
updateSql.Append($"INSERT INTO file_link(id, code, fid) VALUES('{Guid.NewGuid().ToString()}', '{item}', '{fileId}');");
}
SqlHelper.ExecuteNonQuery(updateSql.ToString());
}
XtraMessageBox.Show("操作成功。", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
UpdateDataSource?.Invoke(view, parentId, -1);
}