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!"); }