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);
}
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);
}
/*--------------------------------------------------------------------------------------------------------*/
/// <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());
}
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);
}
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);
}