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