/// <summary>
/// 查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T_D_SQLDATA_MSTModel Query(string id)
{
T_D_SQLDATA_MSTModel model = new T_D_SQLDATA_MSTModel();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T.SJYID,");
sBuilder.Append("T2.PZMC SJYMC,");
sBuilder.Append("T.SQLDATANAME,");
sBuilder.Append("T.SQLDATADISCRIBE,");
sBuilder.Append("T.SQL,");
sBuilder.Append("T.SQLTYPE,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.SFJY,");
sBuilder.Append("T.UNITTYPEID,");
sBuilder.Append("T3.LXMC UNITTYPENAME ");
sBuilder.Append("FROM T_D_SQLDATA_MST T ");
sBuilder.Append("LEFT JOIN T_BASE_SJYPZ T2 ON T.SJYID=T2.PZBM ");
sBuilder.Append("LEFT JOIN T_BASE_UNITTYPE T3 ON T.UNITTYPEID=T3.LXBM ");
sBuilder.Append("WHERE T.ID=:ID ");
OracleParameter[] oparams =
{
new OracleParameter(":ID", OracleType.VarChar)
};
oparams[0].Value = id;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DataSet ds = af.Query(sBuilder.ToString(), oparams);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow dr = ds.Tables[0].Rows[0];
model.ID = ToString(dr["ID"]);
model.SJYID = ToString(dr["SJYID"]);
model.SQLDATANAME = ToString(dr["SQLDATANAME"]);
model.SQLDATADISCRIBE = ToString(dr["SQLDATADISCRIBE"]);
model.SQL = ToString(dr["SQL"]);
model.SQLTYPE = ToString(dr["SQLTYPE"]);
model.CJR = ToString(dr["CJR"]);
model.CJSJ = ToString(dr["CJSJ"]);
model.XGR = ToString(dr["XGR"]);
model.XGSJ = ToString(dr["XGSJ"]);
model.SJC = ToString(dr["SJC"]);
model.SFSC = ToInt(dr["SFSC"]);
model.SFJY = ToInt(dr["SFJY"]);
model.UNITTYPEID = ToString(dr["UNITTYPEID"]);
model.SJYMC = ToString(dr["SJYMC"]);
model.UNITTYPENAME = ToString(dr["UNITTYPENAME"]);
model.SLVList = QuerySlvList(id);
}
return(model);
}
/// <summary>
/// 查询列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <T_D_SQLDATA_MSTModel> QueryList(string swhere, string orders)
{
List <T_D_SQLDATA_MSTModel> models = new List <T_D_SQLDATA_MSTModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T.SJYID,");
sBuilder.Append("T2.PZMC SJYMC,");
sBuilder.Append("T.SQLDATANAME,");
sBuilder.Append("T.SQLDATADISCRIBE,");
sBuilder.Append("T.SQL,");
sBuilder.Append("T.SQLTYPE,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.SFJY,");
sBuilder.Append("T.UNITTYPEID,");
sBuilder.Append("T3.LXMC UNITTYPENAME ");
sBuilder.Append("FROM T_D_SQLDATA_MST T ");
sBuilder.Append("LEFT JOIN T_BASE_SJYPZ T2 ON T.SJYID=T2.PZBM ");
sBuilder.Append("LEFT JOIN T_BASE_UNITTYPE T3 ON T.UNITTYPEID=T3.LXBM ");
if (!String.IsNullOrEmpty(swhere))
{
sBuilder.Append(String.Format("WHERE {0} ", swhere));
}
if (!String.IsNullOrEmpty(orders))
{
sBuilder.Append(String.Format("ORDER BY {0} ", orders));
}
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DataSet ds = af.Query(sBuilder.ToString());
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
T_D_SQLDATA_MSTModel model = new T_D_SQLDATA_MSTModel();
model.ID = ToString(dr["ID"]);
model.SJYID = ToString(dr["SJYID"]);
model.SQLDATANAME = ToString(dr["SQLDATANAME"]);
model.SQLDATADISCRIBE = ToString(dr["SQLDATADISCRIBE"]);
model.SQL = ToString(dr["SQL"]);
model.SQLTYPE = ToString(dr["SQLTYPE"]);
model.CJR = ToString(dr["CJR"]);
model.CJSJ = ToString(dr["CJSJ"]);
model.XGR = ToString(dr["XGR"]);
model.XGSJ = ToString(dr["XGSJ"]);
model.SJC = ToString(dr["SJC"]);
model.SFSC = ToInt(dr["SFSC"]);
model.SFJY = ToInt(dr["SFJY"]);
model.UNITTYPEID = ToString(dr["UNITTYPEID"]);
model.SJYMC = ToString(dr["SJYMC"]);
model.UNITTYPENAME = ToString(dr["UNITTYPENAME"]);
models.Add(model);
}
}
return(models);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Modify(T_D_SQLDATA_MSTModel model)
{
return(access.Modify(model));
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Modify(T_D_SQLDATA_MSTModel model)
{
List <XMLDbHelper.Paramers> listParamters = new List <XMLDbHelper.Paramers>();
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DateTime sjc = af.GetDbDatetimestamp();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("UPDATE T_D_SQLDATA_MST SET ");
sBuilder.Append("SJYID=:SJYID,");
sBuilder.Append("SQLDATANAME=:SQLDATANAME,");
sBuilder.Append("SQLDATADISCRIBE=:SQLDATADISCRIBE,");
sBuilder.Append("SQL=:SQL,");
sBuilder.Append("SQLTYPE=:SQLTYPE,");
sBuilder.Append("CJR=:CJR,");
sBuilder.Append("CJSJ=:CJSJ,");
sBuilder.Append("XGR=:XGR,");
sBuilder.Append("XGSJ=:XGSJ,");
sBuilder.Append("SJC=:SJC,");
sBuilder.Append("SFSC=:SFSC,");
sBuilder.Append("SFJY=:SFJY,");
sBuilder.Append("UNITTYPEID=:UNITTYPEID ");
sBuilder.Append("WHERE ID=:ID ");
OracleParameter[] oparams =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":SJYID", OracleType.VarChar),
new OracleParameter(":SQLDATANAME", OracleType.VarChar),
new OracleParameter(":SQLDATADISCRIBE", OracleType.VarChar),
new OracleParameter(":SQL", OracleType.Clob),
new OracleParameter(":SQLTYPE", OracleType.VarChar),
new OracleParameter(":CJR", OracleType.VarChar),
new OracleParameter(":CJSJ", OracleType.VarChar),
new OracleParameter(":XGR", OracleType.VarChar),
new OracleParameter(":XGSJ", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar),
new OracleParameter(":SFSC", OracleType.Number),
new OracleParameter(":SFJY", OracleType.Number),
new OracleParameter(":UNITTYPEID", OracleType.VarChar)
};
oparams[0].Value = model.ID;
oparams[1].Value = model.SJYID;
oparams[2].Value = model.SQLDATANAME;
oparams[3].Value = model.SQLDATADISCRIBE;
oparams[4].Value = model.SQL;
oparams[5].Value = model.SQLTYPE;
oparams[6].Value = model.CJR;
oparams[7].Value = model.CJSJ;
oparams[8].Value = model.XGR;
oparams[9].Value = sjc.ToString("yyyyMMddHHmmss"); //model.XGSJ;
oparams[10].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff"); //model.SJC;
oparams[11].Value = model.SFSC;
oparams[12].Value = model.SFJY;
oparams[13].Value = model.UNITTYPEID;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams));
foreach (T_D_SQLDATA_SLVModel m in model.SLVList)
{
if (String.IsNullOrEmpty(m.ID))
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("INSERT INTO T_D_SQLDATA_SLV(");
sBuilder.Append("ID,");
sBuilder.Append("MSTID,");
sBuilder.Append("PARAMETERNAME,");
sBuilder.Append("PARAMETERTYPE,");
sBuilder.Append("PARAMETERDISC,");
sBuilder.Append("DEFAULTVALUE,");
sBuilder.Append("BL1,");
sBuilder.Append("BL2,");
sBuilder.Append("BL3,");
sBuilder.Append("SJC");
sBuilder.Append(") VALUES(");
sBuilder.Append(":ID,");
sBuilder.Append(":MSTID,");
sBuilder.Append(":PARAMETERNAME,");
sBuilder.Append(":PARAMETERTYPE,");
sBuilder.Append(":PARAMETERDISC,");
sBuilder.Append(":DEFAULTVALUE,");
sBuilder.Append(":BL1,");
sBuilder.Append(":BL2,");
sBuilder.Append(":BL3,");
sBuilder.Append(":SJC");
sBuilder.Append(")");
OracleParameter[] oparams1 =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":MSTID", OracleType.VarChar),
new OracleParameter(":PARAMETERNAME", OracleType.VarChar),
new OracleParameter(":PARAMETERTYPE", OracleType.VarChar),
new OracleParameter(":PARAMETERDISC", OracleType.VarChar),
new OracleParameter(":DEFAULTVALUE", OracleType.VarChar),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar)
};
oparams1[0].Value = af.GetID(T_BASE_PUBLIC.UNITNAMEKEY, "T_D_SQLDATA_MST"); //model.ID;
oparams1[1].Value = oparams[0].Value; //model.MSTID;
oparams1[2].Value = m.PARAMETERNAME; //model.PARAMETERNAME;
oparams1[3].Value = m.PARAMETERTYPE; //model.PARAMETERTYPE;
oparams1[4].Value = m.PARAMETERDISC; //model.PARAMETERDISC;
oparams1[5].Value = m.DEFAULTVALUE; //model.DEFAULTVALUE;
oparams1[6].Value = m.BL1; //model.BL1;
oparams1[7].Value = m.BL2; //model.BL2;
oparams1[8].Value = m.BL3; //model.BL3;
oparams1[9].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff"); //model.SJC;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams1));
}
else
{
if (m.SFSC == 1)
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("DELETE FROM T_D_SQLDATA_SLV WHERE ID=:ID");
OracleParameter[] oparams2 =
{
new OracleParameter(":ID", OracleType.VarChar)
};
oparams2[0].Value = m.ID;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams2));
}
else
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("UPDATE T_D_SQLDATA_SLV SET ");
sBuilder.Append("MSTID=:MSTID,");
sBuilder.Append("PARAMETERNAME=:PARAMETERNAME,");
sBuilder.Append("PARAMETERTYPE=:PARAMETERTYPE,");
sBuilder.Append("PARAMETERDISC=:PARAMETERDISC,");
sBuilder.Append("DEFAULTVALUE=:DEFAULTVALUE,");
sBuilder.Append("BL1=:BL1,");
sBuilder.Append("BL2=:BL2,");
sBuilder.Append("BL3=:BL3,");
sBuilder.Append("SJC=:SJC ");
sBuilder.Append("WHERE ID=:ID ");
OracleParameter[] oparams3 =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":MSTID", OracleType.VarChar),
new OracleParameter(":PARAMETERNAME", OracleType.VarChar),
new OracleParameter(":PARAMETERTYPE", OracleType.VarChar),
new OracleParameter(":PARAMETERDISC", OracleType.VarChar),
new OracleParameter(":DEFAULTVALUE", OracleType.VarChar),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar)
};
oparams3[0].Value = m.ID;
oparams3[1].Value = m.MSTID;
oparams3[2].Value = m.PARAMETERNAME;
oparams3[3].Value = m.PARAMETERTYPE;
oparams3[4].Value = m.PARAMETERDISC;
oparams3[5].Value = m.DEFAULTVALUE;
oparams3[6].Value = m.BL1;
oparams3[7].Value = m.BL2;
oparams3[8].Value = m.BL3;
oparams3[9].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff");//m.SJC;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams3));
}
}
}
int ret = af.ExecuteNonQueryTransaction(listParamters);
return(ret);
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(T_D_SQLDATA_MSTModel model)
{
return(access.Add(model));
}