public List <string> GetInputFields(string xmbh, string table_name)
{
List <string> lst = new List <string>();
string sqlStr = $"select SJGJ_ID,SJBMC,ZDMC,SY,SSJCX,LX from ZDZD_{xmbh} where SJBMC ='{table_name}' and SFXS = 1 and(lx like '%w%' or LX like 'S') ";
var s_ds = _sqlBase.ExecuteDataset(sqlStr);
if (s_ds != null)
{
foreach (DataRow item in s_ds.Tables[0].Rows)
{
lst.Add(item["ZDMC"].ToString());
}
}
return(lst);
}
public static string GetMdataJson(string sql, string table_name, ESqlConnType connType = ESqlConnType.ConnectionStringJCJT)
{
StringBuilder sb = new StringBuilder();
Common.DBUtility.SqlBase sqlbase = new Common.DBUtility.SqlBase(connType);
DataSet ds = sqlbase.ExecuteDataset(sql);
DataTable dt = ds.Tables[0];
string json = Common.DBUtility.JsonHelper.SerializeObject(dt);
sb.Append(",\"" + table_name + "\":");
sb.Append(json);
return(sb.ToString());
}
public static string GetDataJson(string sqlstr, string tableName, ESqlConnType connType = ESqlConnType.ConnectionStringJCJT)
{
StringBuilder sb = new StringBuilder();
sb.Append("{\"calcData\":{\"\":");
string sql = string.Format(@sqlstr);
Common.DBUtility.SqlBase sqlbase = new Common.DBUtility.SqlBase(connType);
DataSet ds = sqlbase.ExecuteDataset(sql);
ds.Tables[0].TableName = tableName;
string json = Common.DBUtility.JsonHelper.SerializeObject(ds);
sb.Append(json);
sb.Append("}}");
return(sb.ToString());
}
public static string GetAfferentDataJson(string type, string sql, ESqlConnType connType = ESqlConnType.ConnectionStringCF, string m_json = null)
{
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb.Append("{\"calcData\":[{");
//string sql = string.Format(@"select top 1 * from " + tableName);
Common.DBUtility.SqlBase sqlbase = new Common.DBUtility.SqlBase(connType);
DataSet ds = sqlbase.ExecuteDataset(sql);
DataTable dt = ds.Tables[0];
string[] jcxm_list = dt.Rows[0]["jcxm"].ToString().Split('、', ',');
foreach (string item in jcxm_list)
{
sb2.Append("\"" + item + "\":{");
DataTable dt_json = ToDataTable(dt.Select(" jcxm like '%" + item.Trim() + "%'"));
string json = Common.DBUtility.JsonHelper.SerializeObject(dt_json);
sb2.Append("\"" + type + "\":" + json + "");
sb2.Append(",\"S_BY_RW_XQ\":[{");
sb2.Append("\"RECID\":\"19085206791636631332933\",");
sb2.Append("\"SJWCKSSJ\":\"1900/1/1 0:00:00\",");
sb2.Append("\"SJWCJSSJ\":\"1900/1/1 0:00:00\",");
sb2.Append("}]");
if (m_json != null)
{
sb2.Append(m_json);
}
sb2.Append("},");
}
sb.Append(sb2.ToString().TrimEnd(','));
sb.Append("}]}");
return(sb.ToString());
}
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);
}
}
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!");
}
