コード例 #1
0
        private StringBuilder Get_groupBy_sb(HashSet <ColumnTemplate> hs)
        {
            StringBuilder groupBy_sb = new StringBuilder();

            if (!this.HasAggregateFunction(hs))
            {
                return(groupBy_sb);
            }

            groupBy_sb.Append(" Group by ");
            int non_aggCount = 0;

            foreach (ColumnTemplate ct in hs)
            {
                if (ct.AggregateFunc != AggregateFunction.None)
                {
                    continue;
                }

                string colName = ct.ColName.Replace("]", "]]");
                groupBy_sb.Append("[").Append(colName).Append("]").Append(',');
                non_aggCount++;
            }
            HssStr.RemoveLastChar(groupBy_sb, ',');

            if (non_aggCount < 1)
            {
                groupBy_sb.Clear();
            }
            return(groupBy_sb);
        }
コード例 #2
0
ファイル: DB_insert.cs プロジェクト: huangss0/GTS0
        public SqlCommand GetSQL_cmd()
        {
            if (this.template == null)
            {
                Console.WriteLine("DB_insert error 0: no template");
                return(null);
            }
            if (string.IsNullOrEmpty(this.DBname) || string.IsNullOrEmpty(this.schema) || string.IsNullOrEmpty(this.tableName))
            {
                Console.WriteLine("DB_insert error 1: no name");
                return(null);
            }
            if (col_val_dic.Count < 1)
            {
                Console.WriteLine("DB_insert error 2: no column");
                return(null);
            }

            StringBuilder sql_sb = new StringBuilder("Insert into ");

            sql_sb.Append("[" + this.DBname.Replace("]", "]]") + "]");
            sql_sb.Append(".");
            sql_sb.Append("[" + this.schema.Replace("]", "]]") + "]");
            sql_sb.Append(".");
            sql_sb.Append("[" + this.tableName.Replace("]", "]]") + "] ");

            int           tempID = 0;
            SqlCommand    cmd = new SqlCommand();
            StringBuilder col_sb = new StringBuilder(), val_sb = new StringBuilder();

            foreach (KeyValuePair <string, SqlParameter> pair in this.col_val_dic)
            {
                string colName = pair.Key.Replace("]", "]]");
                string valName = "@Val" + tempID;

                col_sb.Append("[").Append(colName).Append("]").Append(',');
                val_sb.Append(valName).Append(',');

                SqlParameter val_sp = new SqlParameter();
                val_sp.Value         = pair.Value.Value;
                val_sp.SqlDbType     = pair.Value.SqlDbType;
                val_sp.ParameterName = valName;

                cmd.Parameters.Add(val_sp);
                ++tempID;
            }

            HssStr.RemoveLastChar(col_sb, ',');
            HssStr.RemoveLastChar(val_sb, ',');

            sql_sb.Append("(").Append(col_sb).Append(")");
            sql_sb.Append(" values ");
            sql_sb.Append("(").Append(val_sb).Append(") ");

            cmd.CommandText = sql_sb.ToString();
            return(cmd);
        }
コード例 #3
0
        /*--------------------------------------------------------------------------------------------------------*/
        /// <summary>
        /// For in statement. e.g. "where [ID] in ('1','2')"
        /// </summary>
        /// <param name="colName">column name</param>
        /// <param name="inFlag">true for in, false for not in</param>
        /// <param name="val_hs">list of values</param>
        private string Init_in(string colName, bool inFlag, HashSet <string> val_hs)
        {
            if (string.IsNullOrEmpty(colName))
            {
                return(null);
            }
            if (val_hs == null)
            {
                return(null);
            }

            StringBuilder list_sb = new StringBuilder();

            foreach (string str in val_hs)
            {
                if (str == null)
                {
                    continue;
                }

                string val_str = str.Replace("'", "''");
                list_sb.Append("'").Append(val_str).Append("'").Append(',');
            }
            HssStr.RemoveLastChar(list_sb, ',');

            StringBuilder sql_sb = new StringBuilder();

            sql_sb.Append('[').Append(colName.Replace("]", "]]")).Append(']');

            sql_sb.Append(' ');
            if (inFlag)
            {
                sql_sb.Append("in");
            }
            else
            {
                sql_sb.Append("not in");
            }
            sql_sb.Append(' ');

            sql_sb.Append('(').Append(list_sb).Append(')');

            return(sql_sb.ToString());
        }
コード例 #4
0
        private StringBuilder Get_selectCol_sb(HashSet <ColumnTemplate> hs)
        {
            StringBuilder col_sb = new StringBuilder();

            if (hs == null || hs.Count == 0)
            {
                return(col_sb);
            }

            foreach (ColumnTemplate ct in hs)
            {
                string colName = "[" + ct.ColName.Replace("]", "]]") + "]";
                string alia    = ct.aliaName;

                if (ct.AggregateFunc == AggregateFunction.Sum)
                {
                    if (string.IsNullOrEmpty(alia))
                    {
                        alia = "sum_" + ct.ColName;
                    }
                    alia = "[" + alia.Replace("]", "]]") + "]";

                    col_sb.Append("Sum(").Append(colName).Append(") as ").Append(alia).Append(',');
                }
                else
                {
                    if (string.IsNullOrEmpty(alia))
                    {
                        col_sb.Append(colName).Append(',');
                    }
                    else
                    {
                        alia = "[" + alia.Replace("]", "]]") + "]";
                        col_sb.Append(colName).Append(" as ").Append(alia).Append(',');
                    }
                }
            }
            HssStr.RemoveLastChar(col_sb, ',');

            return(col_sb);
        }
コード例 #5
0
        public SqlCommand GetSQL_cmd()
        {
            if (this.template == null)
            {
                Console.WriteLine("DB_update error 0: no template");
                return(null);
            }
            if (string.IsNullOrEmpty(this.DBname) || string.IsNullOrEmpty(this.schema) || string.IsNullOrEmpty(this.tableName))
            {
                Console.WriteLine("DB_update error 1: no name");
                return(null);
            }
            if (col_val_dic.Count < 1)
            {
                Console.WriteLine("DB_update error 2: no column");
                return(null);
            }

            if (!this.ExistCondition())
            {
                if (MessageBox.Show("No condition in update SQL", "Warning", MessageBoxButtons.YesNo) != DialogResult.Yes)
                {
                    return(null);
                }
            }

            StringBuilder sql_sb = new StringBuilder("Update ");

            sql_sb.Append("[" + this.DBname.Replace("]", "]]") + "]");
            sql_sb.Append(".");
            sql_sb.Append("[" + this.schema.Replace("]", "]]") + "]");
            sql_sb.Append(".");
            sql_sb.Append("[" + this.tableName.Replace("]", "]]") + "]");
            sql_sb.Append(" set ");

            StringBuilder val_sb = new StringBuilder();
            SqlCommand    cmd    = new SqlCommand();
            int           tempID = 0;

            foreach (KeyValuePair <string, SqlParameter> pair in this.col_val_dic)
            {
                string colName = pair.Key.Replace("]", "]]");
                string valName = "@Val" + tempID;

                val_sb.Append("[").Append(colName).Append("]");
                val_sb.Append(" = ");
                val_sb.Append(valName).Append(", ");

                SqlParameter val_sp = new SqlParameter();
                val_sp.Value         = pair.Value.Value;
                val_sp.SqlDbType     = pair.Value.SqlDbType;
                val_sp.ParameterName = valName;

                cmd.Parameters.Add(val_sp);
                ++tempID;
            }
            HssStr.RemoveLastChar(val_sb, ',');

            sql_sb.Append(val_sb);
            if (this.ExistCondition())
            {
                sql_sb.Append(" where ").Append(this.condition.SQL_str);
            }

            cmd.CommandText = sql_sb.ToString();
            return(cmd);
        }