public static string GetInsertStatement(System.Type type) { Dictionary <string, string> columnNames = DatabaseUtilities.getColumns(type); columnNames.Remove(DatabaseUtilities.GetKeyColumn(type).Key); return(string.Format("INSERT INTO {0}({1}) VALUES({2})", DatabaseUtilities.GetTableName(type), string.Join(",", columnNames.Keys), string.Join(",", columnNames.Values))); }
public int Insert(object obj) { System.Type type = obj.GetType(); DbCommand command = this.getCommand(type, new Func <System.Type, string>(DatabaseUtilities.GetInsertStatement)); string returnSql = command.CommandText; string newId = "InternalNewId"; if (command is System.Data.SqlClient.SqlCommand) { returnSql = "; SET @InternalNewId = SCOPE_IDENTITY()"; newId = "@" + newId; } else { returnSql = string.Format("RETURNING {0} INTO :InternalNewId", DatabaseUtilities.GetKeyColumn(type).Key); newId = ":" + newId; } DbCommand expr_7A = command; expr_7A.CommandText += returnSql; this.AddParametersFrom <object>(command, obj); this.AddOutParameter(command, newId, System.Data.DbType.Int32, 10); this.ExecuteNonQueryCommand(command); return((int)command.Parameters[newId].Value); }
public static List <string> PopulateListWithMatchingColumns <T>(this IDataReader reader, Action <T, IDataRecord> customMapper, List <string> columnNames, ColumnNameMapping[] customNameMappings, List <T> listToPopulate) { Dictionary <PropertyInfo, string> propertyMapping = DatabaseUtilities.getPropertyMapping <T>(columnNames, customNameMappings); while (reader.Read()) { T temp = System.Activator.CreateInstance <T>(); foreach (PropertyInfo property in propertyMapping.Keys) { string columnName = propertyMapping[property]; if (columnName != null) { property.SetValue(temp, Utilities.ConvertTo(property.PropertyType, reader[columnName]), null); } } if (customMapper != null) { customMapper(temp, reader); } listToPopulate.Add(temp); } return(( from p in propertyMapping.Values where p != null select p).ToList <string>()); }
public static void ValidateBindVariables(this System.Data.Common.DbCommand command) { if (command.CommandType != System.Data.CommandType.Text) { throw new Exception("Cannot call ValidateBindVariables if not parameterized query"); } List <string> bindVariables = DatabaseUtilities.ExtractBindVariables(command.CommandText); System.Data.Common.DbParameterCollection dbParameters = command.Parameters; foreach (string variable in bindVariables) { bool bound = false; foreach (System.Data.Common.DbParameter parameter in dbParameters) { if (parameter.ParameterName.Equals(variable, System.StringComparison.CurrentCultureIgnoreCase)) { bound = true; break; } } if (!bound) { throw new Exception(string.Format("{0} is not bound", variable)); } } }
public void AddParametersFrom <T>(DbCommand command, T input) { if (command.CommandType != System.Data.CommandType.Text) { throw new Exception("The feature is only supported for inline SQL"); } string[] parametersToAdd = DatabaseUtilities.ExtractBindVariables(command.CommandText).ToArray(); this.AddParametersFrom <T>(command, input, parametersToAdd); }
public static string GetUpdateStatement(System.Type type) { Dictionary <string, string> columnNames = DatabaseUtilities.getColumns(type); columnNames.Remove(DatabaseUtilities.GetKeyColumn(type).Key); return(string.Format("UPDATE {0} SET {1}", DatabaseUtilities.GetTableName(type), string.Join(",", from x in columnNames select string.Format("{0} = {1}", x.Key, x.Value)))); }
public T Get <T>(int id) { DbCommand command = this.getCommand(typeof(T), new Func <System.Type, string>(DatabaseUtilities.GetSelectStatement)); string whereClause = string.Format(" WHERE {0} = {1}", DatabaseUtilities.GetKeyColumn(typeof(T)).Key, (command is System.Data.SqlClient.SqlCommand) ? "@id" : ":id"); DbCommand expr_56 = command; expr_56.CommandText += whereClause; this.AddInParameter(command, (command is System.Data.SqlClient.SqlCommand) ? "@id" : ":id", System.Data.DbType.Int64, id); return(this.ExecuteAndReturn <T>(command).FirstOrDefault <T>()); }
public int Update(object obj) { System.Type type = obj.GetType(); System.Collections.Generic.KeyValuePair <string, string> key = DatabaseUtilities.GetKeyColumn(type); DbCommand command = this.getCommand(type, new Func <System.Type, string>(DatabaseUtilities.GetUpdateStatement)); string whereClause = string.Format(" WHERE {0} = {1}", key.Key, (command is System.Data.SqlClient.SqlCommand) ? ("@" + key.Value) : (":" + key.Value)); DbCommand expr_6B = command; expr_6B.CommandText += whereClause; this.AddParametersFrom <object>(command, obj); return(this.ExecuteNonQueryCommand(command)); }
public int Delete <T>(int id) { System.Type type = typeof(T); DbCommand command = this.GetSqlStringCommand("DELETE FROM " + DatabaseUtilities.GetTableName(type)); System.Collections.Generic.KeyValuePair <string, string> key = DatabaseUtilities.GetKeyColumn(type); string prefix = (command is System.Data.SqlClient.SqlCommand) ? "@" : ":"; string whereClause = string.Format(" WHERE {0} = {1}{2}", key.Key, prefix, key.Value); DbCommand expr_5C = command; expr_5C.CommandText += whereClause; this.AddInParameter(command, prefix + key.Value, System.Data.DbType.Int32, id); return(this.ExecuteNonQueryCommand(command)); }
public static List <T> AutoMap <T>(this IDataReader reader) { System.Type t = typeof(T); List <T> result; if (t.IsPrimitive || t.Equals(typeof(string)) || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(System.Nullable <>))) { result = DatabaseUtilities.autoMapSimpleType <T>(reader); } else { result = reader.AutoMap <T>(new ColumnNameMapping[0]); } return(result); }
private void addInParameterFrom <T>(DbCommand command, T input, string parameter, System.Reflection.PropertyInfo property) { object value = DatabaseUtilities.GetDbValue(property.GetValue(input, null), command); this.AddInParameter(command, parameter, typeof(System.DateTime?).IsAssignableFrom(property.PropertyType) ? System.Data.DbType.DateTime : System.Data.DbType.AnsiString, value); }
private System.Collections.Generic.List <string> addInParametersFrom <T>(DbCommand command, T input, params string[] parametersToAdd) { if (parametersToAdd == null || parametersToAdd.Length == 0) { throw new Exception("parametersToAdd is empty"); } System.Collections.Generic.IEnumerable <System.Reflection.PropertyInfo> properties = from p in Utilities.GetProperties(input) where p.CanRead select p; System.Collections.Generic.List <string> addedParameters = new System.Collections.Generic.List <string>(); System.Array.ForEach <string>(parametersToAdd, delegate(string parameter) { System.Reflection.PropertyInfo property = properties.FirstOrDefault((System.Reflection.PropertyInfo p) => p.Name.Equals(DatabaseUtilities.GetMatchingPropertyName(parameter), System.StringComparison.CurrentCultureIgnoreCase)); if (property != null) { this.addInParameterFrom <T>(command, input, parameter, property); addedParameters.Add(parameter); } }); return(addedParameters); }
public static string GetSelectStatement(System.Type type) { Dictionary <string, string> columnNames = DatabaseUtilities.getColumns(type); return(string.Format("SELECT {0} FROM {1}", DatabaseUtilities.buildSelectColumns(columnNames), DatabaseUtilities.GetTableName(type))); }
public static string GetMatchingPropertyName(string parameter) { if (string.IsNullOrEmpty(parameter)) { throw new System.ArgumentException("parameter cannot be null or empty"); } string result; lock (DatabaseUtilities._matchingProperties.SyncRoot) { if (DatabaseUtilities._matchingProperties["DBParameter" + parameter] == null) { DatabaseUtilities._matchingProperties["DBParameter" + parameter] = DatabaseUtilities.RemoveParameterPrefix(parameter).Pascalize(); } result = (string)DatabaseUtilities._matchingProperties["DBParameter" + parameter]; } return(result); }