public BaseDal(ESqlConnType eSqlConnType, string selectJCJGName = "WH")
{
switch (eSqlConnType)
{
//case ESqlConnType.ConnectionStringMain:
// sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMain"].ConnectionString; //数据数据库连接
// sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringMain"].ConnectionString; //数据数据库连接
// break;
case ESqlConnType.ConnectionStringJCJT:
sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringJCJT_" + selectJCJGName].ConnectionString; //数据数据库连接
sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringJCJT_" + selectJCJGName].ConnectionString; //数据数据库连接
break;
//case ESqlConnType.ConnectionStringLocal:
// sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringLocal"].ConnectionString; //数据数据库连接
// sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringLocal"].ConnectionString; //数据数据库连接
// break;
//caldebugTool数据库
case ESqlConnType.ConnectionStringDebugTool:
sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringDebugTool"].ConnectionString; //数据数据库连接
sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringDebugTool"].ConnectionString; //数据数据库连接
break;
//case ESqlConnType.ConnectionStringWH:
// sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringWH"].ConnectionString; //数据数据库连接
// sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringWH"].ConnectionString; //数据数据库连接
// break;
//监管数据库
case ESqlConnType.ConnectionStringJCJG:
sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringJCJG_" + selectJCJGName].ConnectionString; //数据数据库连接
sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringJCJG_" + selectJCJGName].ConnectionString; //数据数据库连接
break;
}
}
public static string GetAfferentDataJson2(string type, string sql, ESqlConnType connType = ESqlConnType.ConnectionStringCF, string m_json = null, string y_json = null)
{
StringBuilder sb = new StringBuilder();
StringBuilder sb2 = new StringBuilder();
sb.Append("{\"data\":[{");
SqlBase sqlbase = new SqlBase(connType);
DataSet ds = sqlbase.ExecuteDataset(sql);
DataTable dt = ds.Tables[0];
string json = JsonHelper.SerializeObject(dt);
sb2.Append("\"" + type + "\":");
sb2.Append(json);
if (m_json != null)
{
sb2.Append(m_json);
}
if (y_json != null)
{
sb2.Append(y_json);
}
sb.Append(sb2.ToString().TrimEnd(','));
sb.Append("}],\"code\":1, \"message\":\"成功\"}");
return(sb.ToString());
}
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());
}
private string sqlConnectionStringWrite; //当前写数据库链接字符串
public BaseDal(ESqlConnType eSqlConnType)
{
switch (eSqlConnType)
{
case ESqlConnType.ConnectionStringMain:
sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMain"].ConnectionString; //数据数据库连接
sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringMain"].ConnectionString; //数据数据库连接
break;
case ESqlConnType.ConnectionStringJCJT:
sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringJCJT"].ConnectionString; //数据数据库连接
sqlConnectionStringWrite = ConfigurationManager.ConnectionStrings["ConnectionStringJCJT"].ConnectionString; //数据数据库连接
break;
}
}
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());
}
/// <summary>
/// 获取数据
/// </summary>
/// <param name="zdzdParms">查询参数</param>
/// <param name="queryNumber">查询条件</param>
/// <param name="connType">数据库连接</param>
/// <param name="dataType">数据源</param>
/// <returns></returns>
public string GetParams(List <string> zdzdParms, string queryNumber, ESqlConnType connType, string dataType = "CF")
{
string xmbh = this.txt_jcxmbh.Text.Trim();
if (string.IsNullOrEmpty(xmbh))
{
MessageBox.Show("err");
return("");
}
// zdzd表中获取需要获取的参数
string strParams = "";
strParams = _projectInfo.GetPar2(xmbh, zdzdParms, txt_y.Text, txtdatafiled.Text, connType, queryNumber);
if (string.IsNullOrWhiteSpace(strParams.Trim()))
{
MessageBox.Show("参数数据不能为空！", "调试", MessageBoxButtons.OK);
return("");
}
return(strParams);
}
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 SqlBase(ESqlConnType eSqlConnType) : base(eSqlConnType)
{
}
public SqlBase(ESqlConnType eSqlConnType, string dbName) : base(eSqlConnType, dbName)
{
}
public string GetYtableJson(string ytable, string datafiled, string BH, string jydbh, ESqlConnType connType)
{
string y_json = "";
string[] ytablist = ytable.Split('|');
string[] filedlist = datafiled.Split('|');
for (int i = 0; i < ytablist.Length; i++)
{
string where = string.Empty;
string[] fil = filedlist[i].Split(',');
foreach (var item in fil)
{
string item_s = string.Empty;
string item_y = string.Empty;
if (item.Contains(":"))
{
item_s = item.Split(':')[0];
item_y = item.Split(':')[1];
}
else
{
item_s = item;
item_y = item;
}
if (item.ToUpper().Contains("SYLB"))
{
where += " and sylb = '" + BH + "'";
}
else
{
where += " and [" + item_y + "] in (select [" + item_s + "] from S" + BH + " where JYDBH = '" + jydbh + "')";
}
}
#region 获取数据表字段
var _sqlBase_jcjt = new Common.DBUtility.SqlBase(ESqlConnType.ConnectionStringJCJT);
string result = string.Empty;
string sqlStr = $"select ZDMC from ZDZD_{BH} where ( SJBMC = '{ytablist[i]}') and( lx like '%I%' or lx like '%O%')";
var redata = _sqlBase_jcjt.ExecuteDataset(sqlStr);
if (redata != null)
{
foreach (DataRow item in redata.Tables[0].Rows)
{
result += item["ZDMC"].ToString() + ",";
}
}
if (result.Length > 0)
{
result = result.Substring(0, result.Length - 1);
}
#endregion
string temjson = JsonHelper.GetMdataJson($"select {result} from {ytablist[i]} where 1=1 {where}", ytablist[i], connType);
y_json += temjson;
}
return(y_json);
}
public string GetPar2(string BH, List <string> fields, string ytable, string datafiled, ESqlConnType connType, string wtdbh)
{
List <string> listDataJson = new List <string>();
string ParData = "";
if (fields.Count != 2)
{
return("");
}
string mFields = fields[0];
string sFields = fields[1];
string sqlStr = "";
//通过
sFields = string.IsNullOrEmpty(sFields) ? "s.* ,sBY.jcxmdh as jcxm" : "s." + sFields.Replace(",", ",s.") + ",sBY.jcxmdh as jcxm";
//sFields = string.IsNullOrEmpty(sFields) ? "* ,jcxm" : (sFields + ",jcxm");
sqlStr = $"select {sFields} from S_{BH} as s left join S_BY as sBY on s.RECID=sBY.RECID where s.BYZBRECID=(select RECID from M_BY where WTDBH = '{wtdbh}' AND YTDWBH in('{FormMain._qybh.Replace(",", "','")}') )";
try
{
string m_json = "";
//获取测试数据
if (string.IsNullOrEmpty(mFields))
{
mFields = "JCYJ,PDBZ";
}
else
{
mFields += ",JCYJ,PDBZ";
}
mFields = mFields.Replace("JCJGMS,", "");
m_json = JsonHelper.GetMdataJson($"select {mFields} from M_{BH} join M_BY on M_{BH}.RECID=M_BY.RECID where WTDBH = '{wtdbh}'AND YTDWBH in('{FormMain._qybh.Replace(",", "','")}') ", $"M_{BH}", connType);
//获取数据表
//if (!string.IsNullOrEmpty(ytable))
//{
// y_json = GetYtableJson(ytable, datafiled, BH, jydbh, connType);
//}
var retSDataJosn = JsonHelper.GetAfferentDataJson2($"S_{BH}", sqlStr.ToUpper().Replace(",S.JCXM", ""), connType, m_json);
listDataJson.Add(retSDataJosn);
}
catch (Exception ex)
{
return(ex.Message);
}
StringBuilder sb = new StringBuilder("");
for (int i = 0; i < listDataJson.Count; i++)
{
sb.Append(listDataJson[i] + "\r\n");
}
ParData = sb.ToString();
return(ParData);
}
/// <summary>
/// 获取参数
/// </summary>
/// <param name="BH">试验项目编号</param>
/// <param name="jydbh">主从表关联号</param>
/// <returns></returns>
public string GetPar(string BH, List <string> fields, string ytable, string datafiled, ESqlConnType connType, string jydbh = "")
{
List <string> listDataJson = new List <string>();
string ParData = "";
if (fields.Count != 2)
{
return("");
}
string mFields = fields[0];
string sFields = fields[1];
sFields = string.IsNullOrEmpty(sFields) ? "* ,jcxm" : (sFields + ",jcxm");
string sqlStr = $"select {sFields} from S{BH} where JYDBH='{jydbh}'";
try
{
string m_json = "";
string y_json = "";
if (string.IsNullOrEmpty(mFields))
{
mFields = "JCYJ,PDBZ,SYRQ";
}
else
{
mFields += ",JCYJ,PDBZ,SYRQ";
}
mFields = mFields.Replace("JCJGMS", "JGSM");
//获取测试数据
if (!string.IsNullOrEmpty(mFields))
{
m_json = JsonHelper.GetMdataJson($"select {mFields} from M{BH} where JYDBH ='{jydbh}' ", $"M_{BH}", connType);
}
//获取数据表
if (!string.IsNullOrEmpty(ytable))
{
y_json = GetYtableJson(ytable, datafiled, BH, jydbh, connType);
}
var retSDataJosn = JsonHelper.GetAfferentDataJson2($"S_{BH}", sqlStr, connType, m_json, y_json);
listDataJson.Add(retSDataJosn);
}
catch (Exception)
{
return("");
}
StringBuilder sb = new StringBuilder("");
for (int i = 0; i < listDataJson.Count; i++)
{
sb.Append(listDataJson[i] + "\r\n");
}
ParData = sb.ToString();
return(ParData);
}
