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