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