public string LoadSQL(string sqlFullPath, string templateID, params object[] obValues) { string sql = string.Empty; // null 값일 경우 공백으로 처리 string[] value = obValues.Select(s => (s == null ? "" : s.ToString())).ToArray(); string file = sqlFullPath;// Path.Combine(Global.ConfigInfo.SqlXmlPath, string.Format(@"{0}" //, FileName)); DBAgentMultiTemplate dbaMulti = null; XmlSerializer serializer = new XmlSerializer(typeof(DBAgentMultiTemplate)); XmlReader xr = XmlReader.Create(file); dbaMulti = serializer.Deserialize(xr) as DBAgentMultiTemplate; xr.Close(); // templateID 검색 var query = from a in dbaMulti.DBAgentTemplate where a.TemplateID == templateID select a; if (query != null) { DBAgentTemplate dt = query.First() as DBAgentTemplate; sql = MakeSQL(dt, value); } if (!sql.ToUpper().Contains("NOCOUNT")) { sql = " SET NOCOUNT ON " + "\n" + sql + "\n" + " SET NOCOUNT OFF "; } if (!sql.ToUpper().Contains("ARITHABORT")) { sql = " SET ARITHABORT ON " + "\n" + sql + "\n" + " SET ARITHABORT OFF "; } return(sql); }
public string LoadCondSQL(string sqlFullPath, string templateID, params object[] obValues) { string sql = string.Empty; // null 값일 경우 공백으로 처리 string[] value = obValues.Select(s => (s == null ? "" : s.ToString())).ToArray(); string file = sqlFullPath; // Path.Combine(Global.ConfigInfo.SqlXmlPath, string.Format(@"{0}" //, FileName)); DBAgentMultiTemplate dbaMulti = null; XmlSerializer serializer = new XmlSerializer(typeof(DBAgentMultiTemplate)); XmlReader xr = XmlReader.Create(file); dbaMulti = serializer.Deserialize(xr) as DBAgentMultiTemplate; xr.Close(); // templateID 검색 var query = from a in dbaMulti.DBAgentTemplate where a.TemplateID == templateID select a; if (query != null) { DBAgentTemplate dt = query.First() as DBAgentTemplate; //for (int i = 0; i < value.Length; i++) //{ // if (!value[i].Contains("''")) // { // value[i] = value[i].Replace("'", "''"); // } //} sql = MakeSQL(dt, value); } return(sql); }
private string MakeSQL(DBAgentTemplate dbaTemp, params string[] value) { string result = string.Empty; switch (dbaTemp.Information.SqlType) { default: case GlobalEnum.DBAgentSQLType.FixedSQL: // 고정 SQL if (value != null) { result = string.Format(dbaTemp.TemplateSQL.FixedSQL, value); } else { result = dbaTemp.TemplateSQL.FixedSQL; } break; case GlobalEnum.DBAgentSQLType.DynamicSQL: StringBuilder sb = new StringBuilder(); ProcessFixedSQL pfx = getValueCountinFixedSQL(dbaTemp.TemplateSQL.FixedSQL, value); string strSql = pfx.FixedSQL; List <DynamicBodyData> BodyList = dbaTemp.TemplateSQL.DynamicBodys; for (int i = pfx.Count; i < value.Length; i++) { if (!string.IsNullOrEmpty(value[i]) || (BodyList[i - pfx.Count].Gubun != null && BodyList[i - pfx.Count].Gubun == "1")) { strSql = strSql.Replace("#[" + BodyList[i - pfx.Count].Key + "]", String.Format(BodyList[i - pfx.Count].Body, value[i])); } else { strSql = strSql.Replace("#[" + BodyList[i - pfx.Count].Key + "]", string.Empty); } } result = strSql; break; } return(result); }
public string LoadSQLXml(string sqlFullPath, string templateID, params string[] value) { string sql = string.Empty; string file = sqlFullPath; //Path.Combine(Global.ConfigInfo.SqlXmlPath, string.Format(@"{0}" //, FileName)); DBAgentMultiTemplate dbaMulti = null; XmlSerializer serializer = new XmlSerializer(typeof(DBAgentMultiTemplate)); XmlReader xr = XmlReader.Create(file); dbaMulti = serializer.Deserialize(xr) as DBAgentMultiTemplate; xr.Close(); // templateID 검색 var query = from a in dbaMulti.DBAgentTemplate where a.TemplateID == templateID select a; if (query != null) { DBAgentTemplate dt = query.First() as DBAgentTemplate; for (int i = 0; i < value.Length; i++) { if (!value[i].Contains("@@@@")) { value[i] = value[i].Replace("'", "''"); } value[i] = value[i].Replace("@@@@", ""); } sql = MakeSQL(dt, value); } return(sql); }