public static string GetDBZFJ(string ip, string port, string sidtype, string sid, string uid, string pass)
{
string[] m_ConnectStringModel =
{
"DATA SOURCE =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})) (CONNECT_DATA=(SID={2})));USER ID={3};PASSWORD ={4}",
"DATA SOURCE =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})) (CONNECT_DATA=(SERVICE_NAME={2})));USER ID={3};PASSWORD ={4}"
};
string connectstring = String.Format(m_ConnectStringModel[Convert.ToInt32(sidtype)], ip, port, sid, uid, pass);
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper(XMLDbHelper.DbHelperType.ORACLE, connectstring, true);
DataSet ds = af.GetNlsDatabaseParameters();
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataRow[] drs = ds.Tables[0].Select("PARAMETER='NLS_CHARACTERSET'");
if (drs.Length == 1)
{
try
{
return(drs[0]["VALUE"].ToString());
}
catch { }
}
}
return("");
}
public static void GetDBSetWindow()
{
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
Form form = af.GetDBSetUserFrom();
form.ShowDialog();
}
/// <summary>
/// 查询列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <T_BASE_SJYPZModel> QueryList(string swhere, string orders)
{
List <T_BASE_SJYPZModel> models = new List <T_BASE_SJYPZModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.PZBM,");
sBuilder.Append("T.PZMC,");
sBuilder.Append("T.SJIP,");
sBuilder.Append("T.SJPORT,");
sBuilder.Append("T.SJSID,");
sBuilder.Append("T.SJUSERID,");
sBuilder.Append("T.SJPASSWORD,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC ");
sBuilder.Append("FROM T_BASE_SJYPZ T ");
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_BASE_SJYPZModel model = new T_BASE_SJYPZModel();
model.PZBM = ToString(dr["PZBM"]);
model.PZMC = ToString(dr["PZMC"]);
model.SJIP = ToString(dr["SJIP"]);
model.SJPORT = ToString(dr["SJPORT"]);
model.SJSID = ToString(dr["SJSID"]);
model.SJUSERID = ToString(dr["SJUSERID"]);
model.SJPASSWORD = ToString(dr["SJPASSWORD"]);
model.SFSC = ToInt(dr["SFSC"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
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"]);
models.Add(model);
}
}
return(models);
}
public static List <DataTable> GetDataSet(Model.T_BASE_SJYPZModel sjy, string sql, ref string errorinfo)
{
List <DataTable> models = new List <DataTable>();
string connectstring = String.Format(m_ConnectStringModel[Convert.ToInt32(sjy.BL1)], sjy.SJIP, sjy.SJPORT, sjy.SJSID, sjy.SJUSERID, Careysoft.Basic.Public.DES.Decrypt(sjy.SJPASSWORD, "EPad@)!!"));
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper(XMLDbHelper.DbHelperType.ORACLE, connectstring, true);
if (!af.Connected())
{
errorinfo = "目标数据无法连接!";
return(models);
}
string[] sqlArray = sql.Split(';');
for (int i = 0; i < sqlArray.Length; i++)
{
if (!String.IsNullOrEmpty(sqlArray[i]))
{
DataSet ds = af.Query(sqlArray[i]);
if (ds != null && ds.Tables.Count > 0)
{
models.Add(ds.Tables[0]);
}
}
}
return(models);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public T_D_TASK_MSTModel Query(string id)
{
T_D_TASK_MSTModel model = new T_D_TASK_MSTModel();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T.GROUPID,");
sBuilder.Append("T.TASKNAME,");
sBuilder.Append("T.TASKNUMBER,");
sBuilder.Append("T.TASKDONUMBER,");
sBuilder.Append("T.BEGINDATETIME,");
sBuilder.Append("T.LASTDATETIME,");
sBuilder.Append("T.INTERVAL,");
sBuilder.Append("T.INTERVALTYPE,");
sBuilder.Append("T.INTERVALADDTYPE,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.SFJY,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC,");
sBuilder.Append("T.TASKDISCRIBE ");
sBuilder.Append("FROM T_D_TASK_MST T ");
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.TASKNAME = ToString(dr["TASKNAME"]);
model.TASKNUMBER = ToInt(dr["TASKNUMBER"]);
model.TASKDONUMBER = ToInt(dr["TASKDONUMBER"]);
model.BEGINDATETIME = ToString(dr["BEGINDATETIME"]);
model.LASTDATETIME = ToString(dr["LASTDATETIME"]);
model.INTERVAL = ToInt(dr["INTERVAL"]);
model.INTERVALTYPE = ToString(dr["INTERVALTYPE"]);
model.INTERVALADDTYPE = ToString(dr["INTERVALADDTYPE"]);
model.SFSC = ToInt(dr["SFSC"]);
model.SFJY = ToInt(dr["SFJY"]);
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.TASKDISCRIBE = ToString(dr["TASKDISCRIBE"]);
model.GROUPID = ToString(dr["GROUPID"]);
}
model.SlvList = QuerySlvList(id);
return(model);
}
public static bool SJYConnected(string ip, string port, string sid, string uid, string pass)
{
string m_ConnectStringModel = "DATA SOURCE =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})) (CONNECT_DATA=(SID={2})));USER ID={3};PASSWORD ={4}";
string connectstring = String.Format(m_ConnectStringModel, ip, port, sid, uid, pass);
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper(XMLDbHelper.DbHelperType.ORACLE, connectstring, true);
return(af.Connected());
}
/// <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="pzbm"></param>
/// <returns></returns>
public T_BASE_SJYPZModel Query(string pzbm)
{
T_BASE_SJYPZModel model = new T_BASE_SJYPZModel();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.PZBM,");
sBuilder.Append("T.PZMC,");
sBuilder.Append("T.SJIP,");
sBuilder.Append("T.SJPORT,");
sBuilder.Append("T.SJSID,");
sBuilder.Append("T.SJUSERID,");
sBuilder.Append("T.SJPASSWORD,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC ");
sBuilder.Append("FROM T_BASE_SJYPZ T ");
sBuilder.Append("WHERE T.PZBM=:PZBM ");
OracleParameter[] oparams =
{
new OracleParameter(":PZBM", OracleType.VarChar)
};
oparams[0].Value = pzbm;
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.PZBM = ToString(dr["PZBM"]);
model.PZMC = ToString(dr["PZMC"]);
model.SJIP = ToString(dr["SJIP"]);
model.SJPORT = ToString(dr["SJPORT"]);
model.SJSID = ToString(dr["SJSID"]);
model.SJUSERID = ToString(dr["SJUSERID"]);
model.SJPASSWORD = ToString(dr["SJPASSWORD"]);
model.SFSC = ToInt(dr["SFSC"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
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"]);
}
return(model);
}
public static DateTime GetDBDatetime(string ip, string port, string sidtype, string sid, string uid, string pass)
{
string[] m_ConnectStringModel =
{
"DATA SOURCE =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})) (CONNECT_DATA=(SID={2})));USER ID={3};PASSWORD ={4}",
"DATA SOURCE =(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})) (CONNECT_DATA=(SERVICE_NAME={2})));USER ID={3};PASSWORD ={4}"
};
string connectstring = String.Format(m_ConnectStringModel[Convert.ToInt32(sidtype)], ip, port, sid, uid, pass);
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper(XMLDbHelper.DbHelperType.ORACLE, connectstring, true);
return(af.GetDbDatetimestamp());
}
/// <summary>
/// 更新执行时间
/// </summary>
/// <param name="taskId"></param>
/// <param name="lastDatetime"></param>
/// <returns></returns>
public int UpdateTaskLastDateTime(string taskId, string lastDatetime)
{
string sql = "UPDATE T_D_TASK_MST SET LASTDATETIME=:LASTDATETIME WHERE ID=:ID";
OracleParameter[] oracleParameters =
{
new OracleParameter(":LASTDATETIME", OracleType.VarChar),
new OracleParameter(":ID", OracleType.VarChar)
};
oracleParameters[0].Value = lastDatetime;
oracleParameters[1].Value = taskId;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
return(af.ExecuteNonQuery(sql, oracleParameters));
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Modify(T_BASE_UNITTYPEModel model)
{
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DateTime sjc = af.GetDbDatetimestamp();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("UPDATE T_BASE_UNITTYPE SET ");
sBuilder.Append("LXMC=:LXMC,");
sBuilder.Append("FLXBM=:FLXBM,");
sBuilder.Append("SFSC=:SFSC,");
sBuilder.Append("BL1=:BL1,");
sBuilder.Append("BL2=:BL2,");
sBuilder.Append("BL3=:BL3,");
sBuilder.Append("CJR=:CJR,");
sBuilder.Append("CJSJ=:CJSJ,");
sBuilder.Append("XGR=:XGR,");
sBuilder.Append("XGSJ=:XGSJ,");
sBuilder.Append("SJC=:SJC ");
sBuilder.Append("WHERE LXBM=:LXBM ");
OracleParameter[] oparams =
{
new OracleParameter(":LXBM", OracleType.VarChar),
new OracleParameter(":LXMC", OracleType.VarChar),
new OracleParameter(":SFSC", OracleType.Number),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", 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(":FLXBM", OracleType.VarChar)
};
oparams[0].Value = model.LXBM;
oparams[1].Value = model.LXMC;
oparams[2].Value = model.SFSC;
oparams[3].Value = model.BL1;
oparams[4].Value = model.BL2;
oparams[5].Value = model.BL3;
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.FLXBM; //model.SJC;
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
private List <T_S_TASK_SLV_SLVModel> QuerySlvSlvList(string slvid)
{
string swhere = String.Format(" T.TASKSLVID='{0}'", slvid);
string orders = "T.ID";
List <T_S_TASK_SLV_SLVModel> models = new List <T_S_TASK_SLV_SLVModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T.TASKSLVID,");
sBuilder.Append("T.SQLDATASLVID,");
sBuilder.Append("T.SQLDATASLVVAL,");
sBuilder.Append("T.SQLDARASLVNAME,");
sBuilder.Append("T.SQLDARASQLTYPE,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3,");
sBuilder.Append("T.SJC ");
sBuilder.Append("FROM T_S_TASK_SLV_SLV T ");
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_S_TASK_SLV_SLVModel model = new T_S_TASK_SLV_SLVModel();
model.ID = ToString(dr["ID"]);
model.TASKSLVID = ToString(dr["TASKSLVID"]);
model.SQLDATASLVID = ToString(dr["SQLDATASLVID"]);
model.SQLDATASLVVAL = ToString(dr["SQLDATASLVVAL"]);
model.SQLDARASLVNAME = ToString(dr["SQLDARASLVNAME"]);
model.SQLDARASQLTYPE = ToString(dr["SQLDARASQLTYPE"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
model.SJC = ToString(dr["SJC"]);
models.Add(model);
}
}
return(models);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int Delete(string id)
{
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("DELETE FROM T_D_TASK_MST ");
sBuilder.Append("WHERE ID=:ID ");
OracleParameter[] oparams =
{
new OracleParameter(":ID", OracleType.VarChar)
};
oparams[0].Value = id;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="pzbm"></param>
/// <returns></returns>
public int Delete(string pzbm)
{
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("DELETE FROM T_BASE_SJYPZ ");
sBuilder.Append("WHERE PZBM=:PZBM ");
OracleParameter[] oparams =
{
new OracleParameter(":PZBM", OracleType.VarChar)
};
oparams[0].Value = pzbm;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public int Delete(string username)
{
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("DELETE FROM T_B_USER ");
sBuilder.Append("WHERE USERNAME=:USERNAME ");
OracleParameter[] oparams =
{
new OracleParameter(":USERNAME", OracleType.VarChar)
};
oparams[0].Value = username;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(T_B_USERModel model)
{
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("INSERT INTO T_B_USER(");
sBuilder.Append("USERNAME,");
sBuilder.Append("PASSWORD,");
sBuilder.Append("XM,");
sBuilder.Append("USERTYPE,");
sBuilder.Append("SFJY,");
sBuilder.Append("BL1,");
sBuilder.Append("BL2,");
sBuilder.Append("BL3");
sBuilder.Append(") VALUES(");
sBuilder.Append(":USERNAME,");
sBuilder.Append(":PASSWORD,");
sBuilder.Append(":XM,");
sBuilder.Append(":USERTYPE,");
sBuilder.Append(":SFJY,");
sBuilder.Append(":BL1,");
sBuilder.Append(":BL2,");
sBuilder.Append(":BL3");
sBuilder.Append(")");
OracleParameter[] oparams =
{
new OracleParameter(":USERNAME", OracleType.VarChar),
new OracleParameter(":PASSWORD", OracleType.VarChar),
new OracleParameter(":XM", OracleType.VarChar),
new OracleParameter(":USERTYPE", OracleType.VarChar),
new OracleParameter(":SFJY", OracleType.Number),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar)
};
oparams[0].Value = model.USERNAME;
oparams[1].Value = model.PASSWORD;
oparams[2].Value = model.XM;
oparams[3].Value = model.USERTYPE;
oparams[4].Value = model.SFJY;
oparams[5].Value = model.BL1;
oparams[6].Value = model.BL2;
oparams[7].Value = model.BL3;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="lxbm"></param>
/// <returns></returns>
public T_BASE_UNITTYPEModel Query(string lxbm)
{
T_BASE_UNITTYPEModel model = new T_BASE_UNITTYPEModel();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.LXBM,");
sBuilder.Append("T.FLXBM,");
sBuilder.Append("T.LXMC,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC ");
sBuilder.Append("FROM T_BASE_UNITTYPE T ");
sBuilder.Append("WHERE T.LXBM=:LXBM ");
OracleParameter[] oparams =
{
new OracleParameter(":LXBM", OracleType.VarChar)
};
oparams[0].Value = lxbm;
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.LXBM = ToString(dr["LXBM"]);
model.FLXBM = ToString(dr["FLXBM"]);
model.LXMC = ToString(dr["LXMC"]);
model.SFSC = ToInt(dr["SFSC"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
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"]);
}
return(model);
}
public List <T_D_SQLDATA_SLVModel> QuerySlvList(string mstid)
{
List <T_D_SQLDATA_SLVModel> models = new List <T_D_SQLDATA_SLVModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T.MSTID,");
sBuilder.Append("T.PARAMETERNAME,");
sBuilder.Append("T.PARAMETERTYPE,");
sBuilder.Append("T.PARAMETERDISC,");
sBuilder.Append("T.DEFAULTVALUE,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3,");
sBuilder.Append("T.SJC ");
sBuilder.Append("FROM T_D_SQLDATA_SLV T ");
sBuilder.Append("WHERE T.MSTID=:MSTID ");
OracleParameter[] oparams =
{
new OracleParameter(":MSTID", OracleType.VarChar)
};
oparams[0].Value = mstid;
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DataSet ds = af.Query(sBuilder.ToString(), oparams);
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
T_D_SQLDATA_SLVModel model = new T_D_SQLDATA_SLVModel();
model.ID = ToString(dr["ID"]);
model.MSTID = ToString(dr["MSTID"]);
model.PARAMETERNAME = ToString(dr["PARAMETERNAME"]);
model.PARAMETERTYPE = ToString(dr["PARAMETERTYPE"]);
model.PARAMETERDISC = ToString(dr["PARAMETERDISC"]);
model.DEFAULTVALUE = ToString(dr["DEFAULTVALUE"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
model.SJC = ToString(dr["SJC"]);
models.Add(model);
}
}
return(models);
}
/// <summary>
/// 查询列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <T_B_USERModel> QueryList(string swhere, string orders)
{
List <T_B_USERModel> models = new List <T_B_USERModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.USERNAME,");
sBuilder.Append("T.PASSWORD,");
sBuilder.Append("T.XM,");
sBuilder.Append("T.USERTYPE,");
sBuilder.Append("T.SFJY,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3 ");
sBuilder.Append("FROM T_B_USER T ");
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_B_USERModel model = new T_B_USERModel();
model.USERNAME = ToString(dr["USERNAME"]);
model.PASSWORD = ToString(dr["PASSWORD"]);
model.XM = ToString(dr["XM"]);
model.USERTYPE = ToString(dr["USERTYPE"]);
model.SFJY = ToInt(dr["SFJY"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
models.Add(model);
}
}
return(models);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public T_B_USERModel Query(string username)
{
T_B_USERModel model = new T_B_USERModel();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.USERNAME,");
sBuilder.Append("T.PASSWORD,");
sBuilder.Append("T.XM,");
sBuilder.Append("T.USERTYPE,");
sBuilder.Append("T.SFJY,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3 ");
sBuilder.Append("FROM T_B_USER T ");
sBuilder.Append("WHERE T.USERNAME=:USERNAME ");
OracleParameter[] oparams =
{
new OracleParameter(":USERNAME", OracleType.VarChar)
};
oparams[0].Value = username;
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.USERNAME = ToString(dr["USERNAME"]);
model.PASSWORD = ToString(dr["PASSWORD"]);
model.XM = ToString(dr["XM"]);
model.USERTYPE = ToString(dr["USERTYPE"]);
model.SFJY = ToInt(dr["SFJY"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
}
return(model);
}
private List <Model.T_D_TASK_SLVModel> QuerySlvList(string mstid)
{
string swhere = String.Format(" T.MSTID='{0}'", mstid);
string orders = "T.ID";
List <T_D_TASK_SLVModel> models = new List <T_D_TASK_SLVModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T4.LXMC GROUPMC,");
sBuilder.Append("T3.PZMC SJYMC,");
sBuilder.Append("T3.PZBM SJYBM,");
sBuilder.Append("T2.SQLDATANAME,");
sBuilder.Append("T2.SQLTYPE,");
sBuilder.Append("T2.SQL,");
sBuilder.Append("T2.SFJY SQLDATASFJY,");
sBuilder.Append("T2.SFSC SQLDATASFSC,");
sBuilder.Append("T.MSTID,");
sBuilder.Append("T.SQLDATAID,");
sBuilder.Append("T.BL1,");
sBuilder.Append("T.BL2,");
sBuilder.Append("T.BL3,");
sBuilder.Append("T.SJC,");
sBuilder.Append("T.TASKTYPE,");
sBuilder.Append("T.OUTPUTTYPE,");
sBuilder.Append("T.OUTPUTPATH ");
sBuilder.Append("FROM T_D_TASK_SLV T ");
sBuilder.Append("LEFT JOIN T_D_SQLDATA_MST T2 ON T.SQLDATAID=T2.ID ");
sBuilder.Append("LEFT JOIN T_BASE_SJYPZ T3 ON T2.SJYID=T3.PZBM ");
sBuilder.Append("LEFT JOIN T_BASE_UNITTYPE T4 ON T2.UNITTYPEID=T4.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_TASK_SLVModel model = new T_D_TASK_SLVModel();
model.ID = ToString(dr["ID"]);
model.MSTID = ToString(dr["MSTID"]);
model.SQLDATAID = ToString(dr["SQLDATAID"]);
model.BL1 = ToString(dr["BL1"]);
model.BL2 = ToString(dr["BL2"]);
model.BL3 = ToString(dr["BL3"]);
model.SJC = ToString(dr["SJC"]);
model.TASKTYPE = ToString(dr["TASKTYPE"]);
model.OUTPUTTYPE = ToString(dr["OUTPUTTYPE"]);
model.OUTPUTPATH = ToString(dr["OUTPUTPATH"]);
model.GROUPMC = ToString(dr["GROUPMC"]);
model.SJYMC = ToString(dr["SJYMC"]);
model.SQLDATANAME = ToString(dr["SQLDATANAME"]);
model.SQLTYPE = ToString(dr["SQLTYPE"]);
model.SQL = ToString(dr["SQL"]);
model.SJYBM = ToString(dr["SJYBM"]);
model.SQLDATASFJY = ToInt(dr["SQLDATASFJY"]);
model.SQLDATASFSC = ToInt(dr["SQLDATASFSC"]);
model.SlvList = QuerySlvSlvList(model.ID);
models.Add(model);
}
}
return(models);
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(T_BASE_SJYPZModel model)
{
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DateTime sjc = af.GetDbDatetimestamp();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("INSERT INTO T_BASE_SJYPZ(");
sBuilder.Append("PZBM,");
sBuilder.Append("PZMC,");
sBuilder.Append("SJIP,");
sBuilder.Append("SJPORT,");
sBuilder.Append("SJSID,");
sBuilder.Append("SJUSERID,");
sBuilder.Append("SJPASSWORD,");
sBuilder.Append("SFSC,");
sBuilder.Append("BL1,");
sBuilder.Append("BL2,");
sBuilder.Append("BL3,");
sBuilder.Append("CJR,");
sBuilder.Append("CJSJ,");
sBuilder.Append("XGR,");
sBuilder.Append("XGSJ,");
sBuilder.Append("SJC");
sBuilder.Append(") VALUES(");
sBuilder.Append(":PZBM,");
sBuilder.Append(":PZMC,");
sBuilder.Append(":SJIP,");
sBuilder.Append(":SJPORT,");
sBuilder.Append(":SJSID,");
sBuilder.Append(":SJUSERID,");
sBuilder.Append(":SJPASSWORD,");
sBuilder.Append(":SFSC,");
sBuilder.Append(":BL1,");
sBuilder.Append(":BL2,");
sBuilder.Append(":BL3,");
sBuilder.Append(":CJR,");
sBuilder.Append(":CJSJ,");
sBuilder.Append(":XGR,");
sBuilder.Append(":XGSJ,");
sBuilder.Append(":SJC");
sBuilder.Append(")");
OracleParameter[] oparams =
{
new OracleParameter(":PZBM", OracleType.VarChar),
new OracleParameter(":PZMC", OracleType.VarChar),
new OracleParameter(":SJIP", OracleType.VarChar),
new OracleParameter(":SJPORT", OracleType.VarChar),
new OracleParameter(":SJSID", OracleType.VarChar),
new OracleParameter(":SJUSERID", OracleType.VarChar),
new OracleParameter(":SJPASSWORD", OracleType.VarChar),
new OracleParameter(":SFSC", OracleType.Number),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", 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)
};
oparams[0].Value = af.GetID(T_BASE_PUBLIC.UNITNAMEKEY, "T_BASE_SJYPZ");//model.PZBM;
oparams[1].Value = model.PZMC;
oparams[2].Value = model.SJIP;
oparams[3].Value = model.SJPORT;
oparams[4].Value = model.SJSID;
oparams[5].Value = model.SJUSERID;
oparams[6].Value = model.SJPASSWORD;
oparams[7].Value = model.SFSC;
oparams[8].Value = model.BL1;
oparams[9].Value = model.BL2;
oparams[10].Value = model.BL3;
oparams[11].Value = model.CJR;
oparams[12].Value = sjc.ToString("yyyyMMddHHmmss");//model.CJSJ;
oparams[13].Value = model.XGR;
oparams[14].Value = model.XGSJ;
oparams[15].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff");//model.SJC;
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Modify(T_BASE_SJYPZModel model)
{
XMLDbHelper.FactoryDbHelper af = new XMLDbHelper.FactoryDbHelper();
DateTime sjc = af.GetDbDatetimestamp();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("UPDATE T_BASE_SJYPZ SET ");
sBuilder.Append("PZMC=:PZMC,");
sBuilder.Append("SJIP=:SJIP,");
sBuilder.Append("SJPORT=:SJPORT,");
sBuilder.Append("SJSID=:SJSID,");
sBuilder.Append("SJUSERID=:SJUSERID,");
sBuilder.Append("SJPASSWORD=:SJPASSWORD,");
sBuilder.Append("SFSC=:SFSC,");
sBuilder.Append("BL1=:BL1,");
sBuilder.Append("BL2=:BL2,");
sBuilder.Append("BL3=:BL3,");
sBuilder.Append("CJR=:CJR,");
sBuilder.Append("CJSJ=:CJSJ,");
sBuilder.Append("XGR=:XGR,");
sBuilder.Append("XGSJ=:XGSJ,");
sBuilder.Append("SJC=:SJC ");
sBuilder.Append("WHERE PZBM=:PZBM ");
OracleParameter[] oparams =
{
new OracleParameter(":PZBM", OracleType.VarChar),
new OracleParameter(":PZMC", OracleType.VarChar),
new OracleParameter(":SJIP", OracleType.VarChar),
new OracleParameter(":SJPORT", OracleType.VarChar),
new OracleParameter(":SJSID", OracleType.VarChar),
new OracleParameter(":SJUSERID", OracleType.VarChar),
new OracleParameter(":SJPASSWORD", OracleType.VarChar),
new OracleParameter(":SFSC", OracleType.Number),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", 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)
};
oparams[0].Value = model.PZBM;
oparams[1].Value = model.PZMC;
oparams[2].Value = model.SJIP;
oparams[3].Value = model.SJPORT;
oparams[4].Value = model.SJSID;
oparams[5].Value = model.SJUSERID;
oparams[6].Value = model.SJPASSWORD;
oparams[7].Value = model.SFSC;
oparams[8].Value = model.BL1;
oparams[9].Value = model.BL2;
oparams[10].Value = model.BL3;
oparams[11].Value = model.CJR;
oparams[12].Value = model.CJSJ;
oparams[13].Value = model.XGR;
oparams[14].Value = sjc.ToString("yyyyMMddHHmmss"); //model.XGSJ;
oparams[15].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff"); //model.SJC;
int ret = af.ExecuteNonQuery(sBuilder.ToString(), oparams);
return(ret);
}
/// <summary>
/// 修改
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Modify(T_D_TASK_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_TASK_MST SET ");
sBuilder.Append("TASKNAME=:TASKNAME,");
sBuilder.Append("TASKNUMBER=:TASKNUMBER,");
sBuilder.Append("TASKDONUMBER=:TASKDONUMBER,");
sBuilder.Append("BEGINDATETIME=:BEGINDATETIME,");
sBuilder.Append("LASTDATETIME=:LASTDATETIME,");
sBuilder.Append("INTERVAL=:INTERVAL,");
sBuilder.Append("INTERVALTYPE=:INTERVALTYPE,");
sBuilder.Append("INTERVALADDTYPE=:INTERVALADDTYPE,");
sBuilder.Append("SFSC=:SFSC,");
sBuilder.Append("SFJY=:SFJY,");
sBuilder.Append("CJR=:CJR,");
sBuilder.Append("CJSJ=:CJSJ,");
sBuilder.Append("XGR=:XGR,");
sBuilder.Append("XGSJ=:XGSJ,");
sBuilder.Append("SJC=:SJC,");
sBuilder.Append("GROUPID=:GROUPID,");
sBuilder.Append("TASKDISCRIBE=:TASKDISCRIBE ");
sBuilder.Append("WHERE ID=:ID ");
OracleParameter[] oparams =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":TASKNAME", OracleType.VarChar),
new OracleParameter(":TASKNUMBER", OracleType.Number),
new OracleParameter(":TASKDONUMBER", OracleType.Number),
new OracleParameter(":BEGINDATETIME", OracleType.VarChar),
new OracleParameter(":LASTDATETIME", OracleType.VarChar),
new OracleParameter(":INTERVAL", OracleType.Number),
new OracleParameter(":INTERVALTYPE", OracleType.VarChar),
new OracleParameter(":SFSC", OracleType.Number),
new OracleParameter(":SFJY", OracleType.Number),
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(":TASKDISCRIBE", OracleType.VarChar),
new OracleParameter(":INTERVALADDTYPE", OracleType.VarChar),
new OracleParameter(":GROUPID", OracleType.VarChar),
};
oparams[0].Value = model.ID;
oparams[1].Value = model.TASKNAME;
oparams[2].Value = model.TASKNUMBER;
oparams[3].Value = model.TASKDONUMBER;
oparams[4].Value = model.BEGINDATETIME;
oparams[5].Value = model.LASTDATETIME;
oparams[6].Value = model.INTERVAL;
oparams[7].Value = model.INTERVALTYPE;
oparams[8].Value = model.SFSC;
oparams[9].Value = model.SFJY;
oparams[10].Value = model.CJR;
oparams[11].Value = model.CJSJ;
oparams[12].Value = model.XGR;
oparams[13].Value = sjc.ToString("yyyyMMddHHmmss"); //model.XGSJ;
oparams[14].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff"); //model.SJC;
oparams[15].Value = model.TASKDISCRIBE;
oparams[16].Value = model.INTERVALADDTYPE;
oparams[17].Value = model.GROUPID;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams));
foreach (Model.T_D_TASK_SLVModel m in model.SlvList)
{
if (String.IsNullOrEmpty(m.ID))
{
if (m.SFSC == 0)
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("INSERT INTO T_D_TASK_SLV(");
sBuilder.Append("ID,");
sBuilder.Append("MSTID,");
sBuilder.Append("SQLDATAID,");
sBuilder.Append("BL1,");
sBuilder.Append("BL2,");
sBuilder.Append("BL3,");
sBuilder.Append("SJC,");
sBuilder.Append("TASKTYPE,");
sBuilder.Append("OUTPUTTYPE,");
sBuilder.Append("OUTPUTPATH");
sBuilder.Append(") VALUES(");
sBuilder.Append(":ID,");
sBuilder.Append(":MSTID,");
sBuilder.Append(":SQLDATAID,");
sBuilder.Append(":BL1,");
sBuilder.Append(":BL2,");
sBuilder.Append(":BL3,");
sBuilder.Append(":SJC,");
sBuilder.Append(":TASKTYPE,");
sBuilder.Append(":OUTPUTTYPE,");
sBuilder.Append(":OUTPUTPATH");
sBuilder.Append(")");
OracleParameter[] oparams1 =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":MSTID", OracleType.VarChar),
new OracleParameter(":SQLDATAID", OracleType.VarChar),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar),
new OracleParameter(":TASKTYPE", OracleType.VarChar),
new OracleParameter(":OUTPUTTYPE", OracleType.VarChar),
new OracleParameter(":OUTPUTPATH", OracleType.VarChar)
};
oparams1[0].Value = af.GetID(T_BASE_PUBLIC.UNITNAMEKEY, "T_D_TASK_SLV");//m.ID;
oparams1[1].Value = model.ID;
oparams1[2].Value = m.SQLDATAID;
oparams1[3].Value = m.BL1;
oparams1[4].Value = m.BL2;
oparams1[5].Value = m.BL3;
oparams1[6].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff");//m.SJC;
oparams1[7].Value = m.TASKTYPE;
oparams1[8].Value = m.OUTPUTTYPE;
oparams1[9].Value = m.OUTPUTPATH;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams1));
foreach (Model.T_S_TASK_SLV_SLVModel mm in m.SlvList)
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("INSERT INTO T_S_TASK_SLV_SLV(");
sBuilder.Append("ID,");
sBuilder.Append("TASKSLVID,");
sBuilder.Append("SQLDATASLVID,");
sBuilder.Append("SQLDATASLVVAL,");
sBuilder.Append("BL1,");
sBuilder.Append("BL2,");
sBuilder.Append("BL3,");
sBuilder.Append("SJC,");
sBuilder.Append("SQLDARASLVNAME,");
sBuilder.Append("SQLDARASQLTYPE");
sBuilder.Append(") VALUES(");
sBuilder.Append(":ID,");
sBuilder.Append(":TASKSLVID,");
sBuilder.Append(":SQLDATASLVID,");
sBuilder.Append(":SQLDATASLVVAL,");
sBuilder.Append(":BL1,");
sBuilder.Append(":BL2,");
sBuilder.Append(":BL3,");
sBuilder.Append(":SJC,");
sBuilder.Append(":SQLDARASLVNAME,");
sBuilder.Append(":SQLDARASQLTYPE");
sBuilder.Append(")");
OracleParameter[] oparams2 =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":TASKSLVID", OracleType.VarChar),
new OracleParameter(":SQLDATASLVID", OracleType.VarChar),
new OracleParameter(":SQLDATASLVVAL", OracleType.VarChar),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar),
new OracleParameter(":SQLDARASLVNAME", OracleType.VarChar),
new OracleParameter(":SQLDARASQLTYPE", OracleType.VarChar)
};
oparams2[0].Value = af.GetID(T_BASE_PUBLIC.UNITNAMEKEY, "T_S_TASK_SLV_SLV");//mm.ID;
oparams2[1].Value = oparams1[0].Value;
oparams2[2].Value = mm.SQLDATASLVID;
oparams2[3].Value = mm.SQLDATASLVVAL;
oparams2[4].Value = mm.BL1;
oparams2[5].Value = mm.BL2;
oparams2[6].Value = mm.BL3;
oparams2[7].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff");//mm.SJC;
oparams2[8].Value = mm.SQLDARASLVNAME;
oparams2[9].Value = mm.SQLDARASQLTYPE;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams2));
}
}
}
else
{
if (m.SFSC == 1)
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("DELETE FROM T_D_TASK_SLV ");
sBuilder.Append("WHERE ID=:ID ");
OracleParameter[] oparams3 =
{
new OracleParameter(":ID", OracleType.VarChar)
};
oparams3[0].Value = m.ID;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams3));
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("DELETE FROM T_S_TASK_SLV_SLV ");
sBuilder.Append("WHERE TASKSLVID=:TASKSLVID ");
OracleParameter[] oparams4 =
{
new OracleParameter(":TASKSLVID", OracleType.VarChar)
};
oparams4[0].Value = m.ID;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams4));
}
else
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("UPDATE T_D_TASK_SLV SET ");
sBuilder.Append("MSTID=:MSTID,");
sBuilder.Append("SQLDATAID=:SQLDATAID,");
sBuilder.Append("BL1=:BL1,");
sBuilder.Append("BL2=:BL2,");
sBuilder.Append("BL3=:BL3,");
sBuilder.Append("SJC=:SJC,");
sBuilder.Append("TASKTYPE=:TASKTYPE,");
sBuilder.Append("OUTPUTTYPE=:OUTPUTTYPE,");
sBuilder.Append("OUTPUTPATH=:OUTPUTPATH ");
sBuilder.Append("WHERE ID=:ID ");
OracleParameter[] oparams5 =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":MSTID", OracleType.VarChar),
new OracleParameter(":SQLDATAID", OracleType.VarChar),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar),
new OracleParameter(":TASKTYPE", OracleType.VarChar),
new OracleParameter(":OUTPUTTYPE", OracleType.VarChar),
new OracleParameter(":OUTPUTPATH", OracleType.VarChar)
};
oparams5[0].Value = m.ID;
oparams5[1].Value = m.MSTID;
oparams5[2].Value = m.SQLDATAID;
oparams5[3].Value = m.BL1;
oparams5[4].Value = m.BL2;
oparams5[5].Value = m.BL3;
oparams5[6].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff");//m.SJC;
oparams5[7].Value = m.TASKTYPE;
oparams5[8].Value = m.OUTPUTTYPE;
oparams5[9].Value = m.OUTPUTPATH;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams5));
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("DELETE FROM T_S_TASK_SLV_SLV ");
sBuilder.Append("WHERE TASKSLVID=:TASKSLVID ");
OracleParameter[] oparams6 =
{
new OracleParameter(":TASKSLVID", OracleType.VarChar)
};
oparams6[0].Value = m.ID;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams6));
foreach (Model.T_S_TASK_SLV_SLVModel mm in m.SlvList)
{
sBuilder.Remove(0, sBuilder.Length);
sBuilder.Append("INSERT INTO T_S_TASK_SLV_SLV(");
sBuilder.Append("ID,");
sBuilder.Append("TASKSLVID,");
sBuilder.Append("SQLDATASLVID,");
sBuilder.Append("SQLDATASLVVAL,");
sBuilder.Append("BL1,");
sBuilder.Append("BL2,");
sBuilder.Append("BL3,");
sBuilder.Append("SJC,");
sBuilder.Append("SQLDARASLVNAME,");
sBuilder.Append("SQLDARASQLTYPE");
sBuilder.Append(") VALUES(");
sBuilder.Append(":ID,");
sBuilder.Append(":TASKSLVID,");
sBuilder.Append(":SQLDATASLVID,");
sBuilder.Append(":SQLDATASLVVAL,");
sBuilder.Append(":BL1,");
sBuilder.Append(":BL2,");
sBuilder.Append(":BL3,");
sBuilder.Append(":SJC,");
sBuilder.Append(":SQLDARASLVNAME,");
sBuilder.Append(":SQLDARASQLTYPE");
sBuilder.Append(")");
OracleParameter[] oparams7 =
{
new OracleParameter(":ID", OracleType.VarChar),
new OracleParameter(":TASKSLVID", OracleType.VarChar),
new OracleParameter(":SQLDATASLVID", OracleType.VarChar),
new OracleParameter(":SQLDATASLVVAL", OracleType.VarChar),
new OracleParameter(":BL1", OracleType.VarChar),
new OracleParameter(":BL2", OracleType.VarChar),
new OracleParameter(":BL3", OracleType.VarChar),
new OracleParameter(":SJC", OracleType.VarChar),
new OracleParameter(":SQLDARASLVNAME", OracleType.VarChar),
new OracleParameter(":SQLDARASQLTYPE", OracleType.VarChar)
};
oparams7[0].Value = af.GetID(T_BASE_PUBLIC.UNITNAMEKEY, "T_S_TASK_SLV_SLV");//mm.ID;
oparams7[1].Value = m.ID;
oparams7[2].Value = mm.SQLDATASLVID;
oparams7[3].Value = mm.SQLDATASLVVAL;
oparams7[4].Value = mm.BL1;
oparams7[5].Value = mm.BL2;
oparams7[6].Value = mm.BL3;
oparams7[7].Value = sjc.ToString("yyyy-MM-dd HH:mm:ss.ffffff");//mm.SJC;
oparams7[8].Value = mm.SQLDARASLVNAME;
oparams7[9].Value = mm.SQLDARASQLTYPE;
listParamters.Add(new XMLDbHelper.Paramers(sBuilder.ToString(), oparams7));
}
}
}
}
int ret = af.ExecuteNonQueryTransaction(listParamters);
return(ret);
}
/// <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 List <T_D_TASK_MSTModel> QueryList(string swhere, string orders)
{
List <T_D_TASK_MSTModel> models = new List <T_D_TASK_MSTModel>();
StringBuilder sBuilder = new StringBuilder();
sBuilder.Append("SELECT ");
sBuilder.Append("T.ID,");
sBuilder.Append("T.GROUPID,");
sBuilder.Append("T.TASKNAME,");
sBuilder.Append("T.TASKNUMBER,");
sBuilder.Append("T.TASKDONUMBER,");
sBuilder.Append("T.BEGINDATETIME,");
sBuilder.Append("T.LASTDATETIME,");
sBuilder.Append("T.INTERVAL,");
sBuilder.Append("T.INTERVALTYPE,");
sBuilder.Append("T.INTERVALADDTYPE,");
sBuilder.Append("T.SFSC,");
sBuilder.Append("T.SFJY,");
sBuilder.Append("T.CJR,");
sBuilder.Append("T.CJSJ,");
sBuilder.Append("T.XGR,");
sBuilder.Append("T.XGSJ,");
sBuilder.Append("T.SJC,");
sBuilder.Append("T.TASKDISCRIBE ");
sBuilder.Append("FROM T_D_TASK_MST T ");
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_TASK_MSTModel model = new T_D_TASK_MSTModel();
model.ID = ToString(dr["ID"]);
model.TASKNAME = ToString(dr["TASKNAME"]);
model.TASKNUMBER = ToInt(dr["TASKNUMBER"]);
model.TASKDONUMBER = ToInt(dr["TASKDONUMBER"]);
model.BEGINDATETIME = ToString(dr["BEGINDATETIME"]);
model.LASTDATETIME = ToString(dr["LASTDATETIME"]);
model.INTERVAL = ToInt(dr["INTERVAL"]);
model.INTERVALTYPE = ToString(dr["INTERVALTYPE"]);
model.INTERVALADDTYPE = ToString(dr["INTERVALADDTYPE"]);
model.SFSC = ToInt(dr["SFSC"]);
model.SFJY = ToInt(dr["SFJY"]);
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.TASKDISCRIBE = ToString(dr["TASKDISCRIBE"]);
model.GROUPID = ToString(dr["GROUPID"]);
models.Add(model);
}
}
return(models);
}
/// <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);
}
