public static void ConfigureDeleteCommand(ObjectDef m, string schemaName, SqlCommand cmd, ObjectDefValues values = null)
{
cmd.CommandType = CommandType.Text;
string entityName = m.EntityName;
StringBuilder sb = new StringBuilder();
sb.AppendLine("DELETE ");
sb.AppendLine(string.Format("FROM [{0}].[{1}]", schemaName, entityName));
var qProps = m.GetOrderedProperties();
for (int i = 0; i < qProps.Count(); i++)
{
var mp = qProps.ElementAt(i);
if (qProps.ElementAt(i).IsKey)
{
sb.AppendLine(string.Format("WHERE [{0}] = @{0}", mp.ColumnName));
if (values != null)
{
cmd.Parameters.AddWithValue("@" + mp.ColumnName, values.Values[mp.Name] != null ? values.Values[mp.Name] : DBNull.Value);
}
}
}
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 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();
}
