public static void ConfigureCreateCommand(ObjectDef m, string schemaName, SqlCommand cmd, IEnumerable <object> values = null)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
sb.AppendLine(string.Format("INSERT INTO [{0}].[{1}](", schemaName, entityName));
var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Create)).OrderBy(p => p.PropertyOrder); //.Where(p => p.AllowCreate && !p.AutoGenerated).OrderBy(p => p.PropertyOrder);
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName; // (from mep in m.ModelEntityMappings where mep.ModelPropertyName.Equals(mp.Name) select mep.EntityPropertyName).SingleOrDefault();
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
sbParams.AppendLine(string.Format("\t@{0}", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
sbParams.AppendLine(string.Format("\t, @{0}", entityPropName));
}
if (values != null)
{
cmd.Parameters.AddWithValue("@" + entityPropName, values.ElementAt(i) != null ? values.ElementAt(i) : DBNull.Value);
}
}
sb.AppendLine(")");
sb.AppendLine("VALUES(");
sb.AppendLine(sbParams.ToString());
sb.AppendLine(")");
cmd.CommandText = sb.ToString();
}
public static void ConfigureGetAllCommand(ObjectDef m, string schemaName, SqlCommand cmd)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT ");
var q = m.GetOrderedProperties();
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
cmd.CommandText = sb.ToString();
}
public static void ConfigureFindCommand(ObjectDef m, string schemaName, SqlCommand cmd, params object[] keys)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT ");
var q = m.GetOrderedProperties();
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
var qKeys = m.GetKeys().Select(p => p.ColumnName);
for (int i = 0; i < qKeys.Count(); i++)
{
if (keys[i] != null)
{
sb.AppendLine(string.Format("WHERE [{0}] = @{0}", qKeys.ElementAt(i)));
cmd.Parameters.AddWithValue("@" + qKeys.ElementAt(i), keys[i]);
}
}
cmd.CommandText = sb.ToString();
}
public static void ConfigureCreateCommand(ObjectDef m, string schemaName, SqlCommand cmd, ObjectDefValues values = null)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
sb.AppendLine(string.Format("INSERT INTO [{0}].[{1}](", schemaName, entityName));
var q = m.GetOrderedProperties();
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
sbParams.AppendLine(string.Format("\t@{0}", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
sbParams.AppendLine(string.Format("\t, @{0}", entityPropName));
}
if (values != null)
{
cmd.Parameters.AddWithValue("@" + entityPropName, values.Values.ContainsKey(mp.Name) && values.Values[mp.Name] != null ? Convert.ChangeType(values.Values[mp.Name], TypeHelper.GetFullType(null, mp.TypeName)) : DBNull.Value);
}
}
sb.AppendLine(")");
sb.AppendLine("VALUES(");
sb.AppendLine(sbParams.ToString());
sb.AppendLine(")");
cmd.CommandText = sb.ToString();
}
public static void ConfigureFilterCommand(ObjectDef m, string schemaName, SqlCommand cmd, Condition cond)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT ");
var q = m.GetOrderedProperties();
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
if (cond != null)
{
string condString = cond.ToSqlString(true);
if (!string.IsNullOrEmpty(condString))
{
sb.AppendLine(string.Format("WHERE {0}", condString));
cmd.Parameters.AddRange(cond.GetSqlParameters().ToArray());
}
}
cmd.CommandText = sb.ToString();
}
public static void ConfigureFilterCommand(ObjectDef m, string schemaName, SqlCommand cmd, Condition cond, string orderByExpression, int maxRecordCount = -1)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
if (maxRecordCount > 0)
{
sb.AppendLine($"SELECT TOP({TopNParamName})");
}
else
{
sb.AppendLine("SELECT ");
}
var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Read)).OrderBy(p => p.PropertyOrder);; //.Where(p => p.AllowRead).OrderBy(p => p.PropertyOrder);
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName; // (from mep in m.ModelEntityMappings where mep.ModelPropertyName.Equals(mp.Name) select mep.EntityPropertyName).SingleOrDefault();
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
if (cond != null)
{
string condString = cond.ToSqlString(true);
if (!string.IsNullOrEmpty(condString))
{
sb.AppendLine(string.Format("WHERE {0}", condString));
cmd.Parameters.AddRange(cond.GetSqlParameters().ToArray());
}
}
var orderByCols = GetOrderByCols(q);
if (!string.IsNullOrEmpty(orderByExpression))
{
sb.AppendLine(string.Format("ORDER BY {0}", orderByExpression));
}
else
{
if (!string.IsNullOrEmpty(orderByCols))
{
sb.AppendLine(string.Format("ORDER BY {0}", orderByCols));
}
}
cmd.CommandText = sb.ToString();
if (maxRecordCount > 0)
{
cmd.Parameters.AddWithValue(TopNParamName, maxRecordCount);
}
}
public static void ConfigureFilterCommand(ObjectDef m, string schemaName, SqlCommand cmd, Condition cond, string orderByExpression, int pageNumber, int pageSize)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbColumns = new StringBuilder();
var q = m.GetOrderedProperties();
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sbColumns.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sbColumns.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
string columns = sbColumns.ToString();
string filters = string.Empty;
if (cond != null)
{
string condString = cond.ToSqlString(true);
if (!string.IsNullOrEmpty(condString))
{
filters = string.Format("WHERE {0}", condString);
cmd.Parameters.AddRange(cond.GetSqlParameters().ToArray());
}
}
sb.AppendLine(string.Format(";WITH {0}CTE AS", entityName));
sb.AppendLine("(");
sb.AppendLine("\tSELECT");
sb.AppendLine(columns);
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByExpression));
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
sb.AppendLine(filters);
sb.AppendLine(")");
sb.AppendLine("SELECT TOP(@PageSize)");
sb.AppendLine(columns);
sb.AppendLine(string.Format("FROM {0}CTE", entityName));
sb.AppendLine("WHERE RowNumber > (@PageNumber - 1) * @PageSize");
cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
cmd.CommandText = sb.ToString();
}
public static void ConfigureUpdateCommand(ObjectDef m, string schemaName, SqlCommand cmd, IEnumerable <object> values = null)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbFilters = new StringBuilder();
sb.AppendLine(string.Format("UPDATE [{0}].[{1}] SET", schemaName, entityName));
var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Update)).OrderBy(p => p.PropertyOrder); //.Where(p => (p.AllowUpdate && !p.AutoGenerated) || p.IsKey).OrderBy(p => p.PropertyOrder);
bool bKeyAdded = false;
bool setColumnAdded = false;
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName; // (from mep in m.ModelEntityMappings where mep.ModelPropertyName.Equals(mp.Name) select mep.EntityPropertyName).SingleOrDefault();
if (mp.IsKey)
{
if (!bKeyAdded)
{
sbFilters.Append(string.Format("WHERE [{0}] = @{0}", entityPropName));
bKeyAdded = true;
}
else
{
sbFilters.Append(string.Format("AND [{0}] = @{0}", entityPropName));
}
}
else
{
if (!setColumnAdded)
{
sb.AppendLine(string.Format("\t[{0}] = @{0}", entityPropName));
setColumnAdded = true;
}
else
{
sb.AppendLine(string.Format("\t, [{0}] = @{0}", entityPropName));
}
}
if (values != null)
{
cmd.Parameters.AddWithValue("@" + entityPropName, values.ElementAt(i) != null ? values.ElementAt(i) : DBNull.Value);
}
}
sb.AppendLine(sbFilters.ToString());
cmd.CommandText = sb.ToString();
}
public static void ConfigureUpdateCommand(ObjectDef m, string schemaName, SqlCommand cmd, ObjectDefValues values = null)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbFilters = new StringBuilder();
sb.AppendLine(string.Format("UPDATE [{0}].[{1}] SET", schemaName, entityName));
var q = m.GetOrderedProperties();
bool bKeyAdded = false;
bool setColumnAdded = false;
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (mp.IsKey)
{
if (!bKeyAdded)
{
sbFilters.Append(string.Format("WHERE [{0}] = @{0}", entityPropName));
bKeyAdded = true;
}
else
{
sbFilters.Append(string.Format("AND [{0}] = @{0}", entityPropName));
}
}
else
{
if (!setColumnAdded)
{
sb.AppendLine(string.Format("\t[{0}] = @{0}", entityPropName));
setColumnAdded = true;
}
else
{
sb.AppendLine(string.Format("\t, [{0}] = @{0}", entityPropName));
}
}
if (values != null)
{
cmd.Parameters.AddWithValue("@" + entityPropName, values.Values[mp.Name] != null ? Convert.ChangeType(values.Values[mp.Name], TypeHelper.GetFullType(null, mp.TypeName)) : DBNull.Value);
}
}
sb.AppendLine(sbFilters.ToString());
cmd.CommandText = sb.ToString();
}
public object EvalDefault(ObjectDefProperty mp)
{
CodeDomProvider provider = provider = CodeDomProvider.CreateProvider("CSharp");
CompilerParameters cp = new CompilerParameters();
cp.GenerateExecutable = false;
cp.GenerateInMemory = true;
cp.TreatWarningsAsErrors = false;
cp.ReferencedAssemblies.Add(this.GetType().Assembly.Location);
string key = KeyGenerator.GetString();
string source = @"
using System;
using Iv.Common;
using Iv.Text;
using Iv.Calendar;
namespace Namespace" + key + @"
{
public class Class" + key + @"
{
public static object Execute()
{
return " + mp.DefaultExpression + @";
}
}
}";
CompilerResults cr = provider.CompileAssemblyFromSource(cp, source);
if (cr.Errors.Count > 0)
{
//TODO: log errors
foreach (CompilerError ce in cr.Errors)
{
Console.Out.WriteLine(" {0}", ce.ToString());
}
return(null);
}
else
{
object obj = cr.CompiledAssembly.CreateInstance(string.Format("Namespace{0}.Class{0}", key));
MethodInfo mi = obj.GetType().GetMethod("Execute", BindingFlags.Public | BindingFlags.Static);
var result = mi.Invoke(obj, null);
mp.Value = result;
return(result);
}
}
public static void ConfigureFindCommand(ObjectDef m, string schemaName, SqlCommand cmd, params object[] keys)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
sb.AppendLine("SELECT ");
var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Update)).OrderBy(p => p.PropertyOrder); //.Where(p => p.AllowRead);
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName; // (from mep in m.ModelEntityMappings where mep.ModelPropertyName.Equals(mp.Name) select mep.EntityPropertyName).SingleOrDefault();
if (i == 0)
{
sb.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sb.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
var qKeys = (from mp in m.Properties
//join mep in m.ModelEntityMappings on mp.Name equals mep.ModelPropertyName
where mp.IsKey
select mp.ColumnName);
for (int i = 0; i < qKeys.Count(); i++)
{
if (keys.Any() && keys[i] != null)
{
sb.AppendLine(string.Format("WHERE [{0}] = @{0}", qKeys.ElementAt(i)));
cmd.Parameters.AddWithValue("@" + qKeys.ElementAt(i), keys[i]);
}
}
cmd.CommandText = sb.ToString();
}
public static void MapReaderObjectDefProperty(SqlDataReader rdr, ObjectDefProperty obj)
{
MapReader <ObjectDefProperty>(rdr, obj, DataOperation.Read);
}
public static void DeriveObjectDefPropertyParameters(SqlCommand cmd, ObjectDefProperty obj)
{
DeriveParameters <ObjectDefProperty>(cmd, obj);
}
public static void ConfigureFilterCommand(ObjectDef m, string schemaName, SqlCommand cmd, Condition cond, string orderByExpression, int pageNumber, int pageSize)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
StringBuilder sbColumns = new StringBuilder();
var q = m.Properties.Where(p => !p.HasIgnoreColumn(DataOperation.Read)).OrderBy(p => p.PropertyOrder); //.Where(p => p.AllowRead).OrderBy(p => p.PropertyOrder);
for (int i = 0; i < q.Count(); i++)
{
ObjectDefProperty mp = q.ElementAt(i);
var entityPropName = mp.ColumnName;
if (i == 0)
{
sbColumns.AppendLine(string.Format("\t[{0}]", entityPropName));
}
else
{
sbColumns.AppendLine(string.Format("\t, [{0}]", entityPropName));
}
}
string columns = sbColumns.ToString();
string filters = string.Empty;
if (cond != null)
{
string condString = cond.ToSqlString(true);
if (!string.IsNullOrEmpty(condString))
{
filters = string.Format("WHERE {0}", condString);
cmd.Parameters.AddRange(cond.GetSqlParameters().ToArray());
}
}
sb.AppendLine($"SELECT {TotalCountParamName} = COUNT(*)");
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
sb.AppendLine(filters);
sb.AppendLine(string.Format(";WITH {0}CTE AS", entityName));
sb.AppendLine("(");
sb.AppendLine("\tSELECT");
sb.AppendLine(columns);
var orderByCols = GetOrderByCols(q);
if (!string.IsNullOrEmpty(orderByExpression))
{
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByExpression));
}
else
{
if (!string.IsNullOrEmpty(orderByCols))
{
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", orderByCols));
}
else
{
var key = m.GetKey();
sb.AppendLine(string.Format("\t, ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber", key.Name));
}
}
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
sb.AppendLine(filters);
sb.AppendLine(")");
sb.AppendLine($"SELECT TOP({PageSizeParamName})");
sb.AppendLine(columns);
sb.AppendLine(string.Format("FROM {0}CTE", entityName));
sb.AppendLine($"WHERE RowNumber > ({PageNumberParamName} - 1) * {PageSizeParamName}");
cmd.Parameters.AddWithValue(PageNumberParamName, pageNumber);
cmd.Parameters.AddWithValue(PageSizeParamName, pageSize);
SqlParameter param = new SqlParameter(TotalCountParamName, SqlDbType.Int);
param.Direction = ParameterDirection.InputOutput;
param.Value = 0;
cmd.Parameters.Add(param);
cmd.CommandText = sb.ToString();
}
