public int InsertFields(string xmbh, string jcxm, string sjbmc, string field, string lx)
{
try
{
string sqlStr = string.Format("select SJBMC,ZDMC,LX,SSJCX from [dbo].[ZDZD_{0}] where sjbmc ='{1}' and ZDMC='{2}'", xmbh, sjbmc, field);
var ds = _sqlDebugTool.ExecuteDataset(sqlStr);
if (null == ds)
{
return(-1);
}
DataTable dt = ds.Tables[0];
string dtLX = dt.Rows[0]["LX"].ToString();
string dtSSJCX = dt.Rows[0]["SSJCX"].ToString();
//J
//、抗冻性、
if (dtLX.Length == 0)
{
dtLX = lx;
}
else
{
dtLX += dtLX.Contains(lx) ? "" : "," + lx;
}
if (dtSSJCX.Length == 0)
{
dtSSJCX = $"、{jcxm}、";
}
else
{
dtSSJCX += dtSSJCX.Contains(jcxm) ? "" : $"{jcxm}、";
}
sqlStr = $" Update ZDZD_{xmbh} set LX = '{dtLX}',SSJCX ='{dtSSJCX}' where sjbmc ='{sjbmc}' and ZDMC='{field}'";
return(_sqlDebugTool.ExecuteNonQuery(sqlStr));
}
catch (Exception ex)
{
return(-1);
}
}
public int InsertParam(CalculateParam param)
{
try
{
var cout = 0;
string sqlStr = string.Format($"select * from H_Calculate_Param where SYXMBH ='{param.SYXMBH}' and RemoteTableName ='{param.RemoteTableName}' and RemoteZdName='{param.RemoteZdName}'");
var ds = _sqlBase.ExecuteDataset(sqlStr);
if (ds != null && ds.Tables[0].Rows.Count != 0)
{
//已有字段
return(-2);
}
sqlStr = $"select 1 from ZDZD_{param.SYXMBH} where ZDMC ='{param.RemoteZdName}' and SJBMC ='{param.RemoteTableName}'";
cout = _sqlBase.ExecuteNonQuery(sqlStr);
if (cout > 0)
{
return(1);
}
//sqlStr = $" INSERT INTO [dbo].[ZDZD_{param.SYXMBH}]([SJBMC],[ZDMC],[SFXS],[MUSTIN],[LX],[SY]) VALUES('{param.RemoteTableName}','{param.RemoteZdName}',0,0,'H,I','配置字段')";
sqlStr = $"insert into ZDZD_{param.SYXMBH} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('{param.RemoteTableName}', '{param.RemoteZdName}', '配置字段', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', 'False', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', 'H,I', NULL, NULL, NULL, NULL, NULL, NULL) " +
$"";
cout = _sqlDebugTool.ExecuteNonQuery(sqlStr);
if (cout > 0)
{
sqlStr = $"insert into H_Calculate_Param (SYXMBH,JCXM,LocalTableName,LocalZdName,RemoteTableName,RemoteZdName) values('{param.SYXMBH}','{param.JCXM}','{param.LocalTableName}','" +
$"{param.LocalZdName}','{param.RemoteTableName}','{param.RemoteZdName}')";
return(_sqlBase.ExecuteNonQuery(sqlStr));
//sqlStr = $"alter table {param.RemoteTableName} add {param.RemoteZdName} varchar(200) null ";
//return _sqlBase.ExecuteNonQuery(sqlStr);
}
//zdzd 插入失败
return(-1);
}
catch (Exception ex)
{
return(-1);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (MessageBox.Show("是否创建帮助表数据?", "Confirm Message", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
{
return;
}
string xmbh = string.IsNullOrEmpty(txt_xmbh.Text) ? "" : txt_xmbh.Text.Trim();
string fieldName = string.IsNullOrEmpty(txt_fieldName.Text) ? "" : txt_fieldName.Text.Trim();
string fieldMS = string.IsNullOrEmpty(txt_fieldMs.Text) ? "" : txt_fieldMs.Text.Trim();
string fieldType = string.IsNullOrEmpty(txt_fieldType.Text) ? "" : txt_fieldType.Text.Trim();
if (string.IsNullOrEmpty(xmbh))
{
MessageBox.Show("输入项目编号！");
return;
}
if (string.IsNullOrEmpty(fieldName))
{
MessageBox.Show("输入字段名！");
return;
}
if (string.IsNullOrEmpty(fieldMS))
{
MessageBox.Show("输入字段描述！");
return;
}
if (string.IsNullOrEmpty(fieldType))
{
MessageBox.Show("输入字段类型！");
return;
}
string sqlstr = string.Format($" select top 1 * FROM M_{xmbh}");
if (_sqlBase.ExecuteDataset(sqlstr) == null)
{
MessageBox.Show($"项目{xmbh}不存在！");
return;
}
int queryCount = 0;//返回受影响的行数
try
{
//判定是否已有字段
string sqlStr = $"select 1 from ZDZD_{xmbh} where ZDMC in ('HG_{fieldName}','HG_{fieldName}','{fieldName}')";
var ds2 = _sqlBase.ExecuteDataset(sqlStr);
var tableType = radio_m.Checked ? "M_" : "S_";
List <string> lst = new List <string>();
//zdzd表添加记录
if (ds2 != null && ds2.Tables[0].Rows.Count != 0)
{
MessageBox.Show($"添加失败,ZDZD_{xmbh}中已存在{fieldName}相关字段");
return;
}
else
{
string txtLX = string.IsNullOrEmpty(txt_lx.Text) ? "H" : txt_lx.Text.Trim();
string chksfxs = this.chk_SFXS.Checked ? "1" : "0";
string locstionStr = "1,1";
sqlStr = $"insert into ZDZD_{xmbh} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('{tableType}{xmbh}', 'HG_{fieldName}', '判定{fieldMS}', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', '{chksfxs}', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', '{txtLX}', NULL, NULL, NULL, NULL, NULL, '{locstionStr}') " +
$"";
lst.Add(sqlStr);
sqlStr = $"insert into ZDZD_{xmbh} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('M_{xmbh}', 'G_{fieldName}', '要求{fieldMS}', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', '{chksfxs}', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', '{txtLX}', NULL, NULL, NULL, NULL, NULL, '{locstionStr}') " +
$"";
lst.Add(sqlStr);
if (txt_bzCount.Text == "1")
{
sqlStr = $"insert into ZDZD_{xmbh} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('BZ_{xmbh}_DJ', 'G_{fieldName}', '{fieldMS}', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', '{chksfxs}', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', '{txtLX}', NULL, NULL, NULL, NULL, NULL, '{locstionStr}') ";
lst.Add(sqlStr);
}
else
{
for (int i = 1; i < Convert.ToInt16(txt_bzCount.Text) + 1; i++)
{
sqlStr = $"insert into ZDZD_{xmbh} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('BZ_{xmbh}_DJ', 'G_{fieldName}{i}', '{fieldMS}{i}', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', '{chksfxs}', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', '{txtLX}', NULL, NULL, NULL, NULL, NULL, '{locstionStr}') ";
lst.Add(sqlStr);
}
}
if (txt_STabCount.Text == "1")
{
sqlStr = $"insert into ZDZD_{xmbh} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('S_{xmbh}', '{fieldName}', '{fieldMS}', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', '{chksfxs}', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', '{txtLX}', NULL, NULL, NULL, NULL, NULL, '{locstionStr}')";
lst.Add(sqlStr);
}
else
{
for (int i = 1; i < Convert.ToInt16(txt_STabCount.Text) + 1; i++)
{
sqlStr = $"insert into ZDZD_{xmbh} ( SJBMC, ZDMC, SY, ZDLX, ZDCD1, ZDCD2, INPUTZDLX, KJLX, SFBHZD, BHMS,ZDSX, SFXS, XSCD, XSSX, SFGD, MUSTIN, DEFAVAL, HELPLNK, CTRLSTRING, ZDXZ,WXSSX, WSFXS, MSGINFO, EQLFUNC, HELPWHERE, GETBYBH, SSJCX, SFBGZD,VALIDPROC, LX, ZDSXSQL, ENCRYPT, FZYC, FZCS, NOSAVE, location)" +
$"VALUES('S_{xmbh}', '{fieldName}{i}', '{fieldMS}{i}', 'nvarchar', '200', '0', 'nvarchar', '', 'False', '', 'False', '{chksfxs}', '0', '367.0000', 'False', 'False', '', '', '', 'S', '367.0000', 'True', '', '', '', 'True', '', 'True', '', '{txt_lx}', NULL, NULL, NULL, NULL, NULL, '{locstionStr}')";
lst.Add(sqlStr);
}
}
}
string alterSql = "";
if (txt_bzCount.Text == "1")
{
alterSql = $"alter table BZ_{xmbh.Trim()}_DJ add G_{fieldName} {txt_fieldType.Text.Trim()};";
}
else
{
for (int i = 1; i < Convert.ToInt16(txt_bzCount.Text) + 1; i++)
{
alterSql += $"alter table BZ_{xmbh.Trim()}_DJ add G_{fieldName}{i} {txt_fieldType.Text.Trim()};";
}
}
queryCount = _sqlBase.ExecuteNonQuery(alterSql);
//主表 从表添加记录
var alterM = "";
//添加主/从表字段
alterM = $"alter table {tableType}{xmbh} add HG_{fieldName} nvarchar(15);";
alterM += $"alter table M_{xmbh} add G_{fieldName} {fieldType};";
if (txt_STabCount.Text == "1")
{
alterM += $"alter table S_{xmbh} add {fieldName} {fieldType};";
}
else
{
{
for (int i = 1; i < Convert.ToInt16(txt_STabCount.Text) + 1; i++)
{
alterM += $"alter table S_{xmbh} add {fieldName}{i} {fieldType};";
}
}
}
if (chk_syncJcJG.Checked)
{
_sqlJGJG.ExecuteNonQuery(alterM);
}
queryCount = _sqlBase.ExecuteNonQuery(alterM);
foreach (var item in lst)
{
try
{
//两个数据添加
_sqlBase.ExecuteNonQuery(item);
_sqlDebugTool.ExecuteNonQuery(item);
if (chk_syncJcJG.Checked)
{
queryCount = _sqlJGJG.ExecuteNonQuery(item);
}
}
catch (Exception ex)
{
throw ex;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
MessageBox.Show("Success!");
}