Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }