/// <summary>
/// Builds INSERT INTO query for the specified <c>table</c>.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="table"></param>
/// <param name="connection"></param>
/// <param name="transaction">Can be <c>null</c>.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">
/// If the specified <c>table</c> is <c>null</c>, or
/// if the specified <c>connection</c> is <c>null</c>.
/// </exception>
/// <exception cref="ArgumentException">
/// If the specified <c>table</c> is not marked with <see cref="SqlTableAttribute"/> attribute, or
/// if the specified <c>table</c> does not contain any propoerties marked with <see cref="SqlParameterAttribute"/> attribute, or
/// if one of the properties is marked as being not nullable but its value is <c>null</c>.
/// </exception>
/// <exception cref="InvalidOperationException">
/// Failed to insert a new record into the specified <c>table</c>.
/// </exception>
public static Int32?Insert <T>(T table, SqlConnection connection, SqlTransaction transaction)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (connection == null)
{
throw new ArgumentNullException("connection");
}
SqlTableAttribute tableInfo = GetTableInfo <T>();
IDictionary <PropertyInfo, SqlParameterAttribute> tableProperties = GetSqlEntityProperties <T>();
if (tableProperties.Count == 0)
{
throw new ArgumentException(Resources.Argument_NoSqlParameterProps);
}
IDictionary <SqlParameter, Object> paramValueMap = new Dictionary <SqlParameter, Object>();
foreach (PropertyInfo tableProperty in tableProperties.Keys)
{
if (IsPrimaryKey(tableProperty))
{
continue;
}
Object qualifiedValue = GetQualifiedValue(tableProperty.GetValue(table, null));
SqlParameterAttribute propInfo = tableProperties[tableProperty];
if (qualifiedValue == null)
{
if (!propInfo.IsNullable)
{
throw new ArgumentException(String.Format(Resources.Argument_NotNullablePropIsNull, tableProperty.Name));
}
continue;
}
paramValueMap.Add(CreateSqlParameter(tableProperties[tableProperty]), qualifiedValue);
}
if (paramValueMap.Count == 0)
{
return(null);
}
SqlParameter[] parameters = new SqlParameter[paramValueMap.Keys.Count];
paramValueMap.Keys.CopyTo(parameters, 0);
SqlCommand command = new SqlCommand(CreateInsertQuery(tableInfo.Name, parameters), connection, transaction);
command.AddNamedParameterRange(parameters);
Object[] values = new Object[paramValueMap.Values.Count];
paramValueMap.Values.CopyTo(values, 0);
for (var i = 0; i < values.Length; i++)
{
command.Parameters[i].Value = values[i];
}
command.ExecuteNonQuery();
Decimal?identity = SqlExtensions.GetIdentity(connection, transaction);
if (identity.HasValue)
{
return(Convert.ToInt32(identity.Value));
}
throw new InvalidOperationException(String.Format(Resources.Argument_InsertIntoFailed, tableInfo.Name));
}
public static void Update <T>(T table, SqlConnection connection, SqlTransaction transaction)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (connection == null)
{
throw new ArgumentNullException("connection");
}
SqlTableAttribute tableInfo = GetTableInfo <T>();
IDictionary <PropertyInfo, SqlParameterAttribute> tableProperties = GetSqlEntityProperties <T>();
if (tableProperties.Count == 0)
{
throw new ArgumentException(Resources.Argument_NoSqlParameterProps);
}
IDictionary <SqlParameter, Object> paramValueMap = new Dictionary <SqlParameter, Object>();
SqlParameter pkParameter = null;
foreach (PropertyInfo tableProperty in tableProperties.Keys)
{
if (IsForeignKey(tableProperty))
{
continue;
}
Object qualifiedValue = GetQualifiedValue(tableProperty.GetValue(table, null));
SqlParameterAttribute propInfo = tableProperties[tableProperty];
if (qualifiedValue == null)
{
if (!propInfo.IsNullable)
{
throw new ArgumentException(String.Format(Resources.Argument_NotNullablePropIsNull, tableProperty.Name));
}
continue;
}
SqlParameter currentParam = CreateSqlParameter(tableProperties[tableProperty]);
if (IsPrimaryKey(tableProperty))
{
pkParameter = currentParam;
pkParameter.Value = qualifiedValue;
}
else
{
paramValueMap.Add(currentParam, qualifiedValue);
}
}
if (paramValueMap.Count == 0)
{
return;
}
SqlParameter[] parameters = new SqlParameter[paramValueMap.Keys.Count];
paramValueMap.Keys.CopyTo(parameters, 0);
SqlCommand command = new SqlCommand(CreateUpdateQuery(tableInfo.Name, pkParameter, parameters), connection, transaction);
command.AddNamedParameterRange(parameters);
command.AddNamedParameter(pkParameter);
Object[] values = new Object[paramValueMap.Values.Count + 1];
paramValueMap.Values.CopyTo(values, 0);
values[values.Length - 1] = pkParameter.Value;
for (var i = 0; i < values.Length; i++)
{
command.Parameters[i].Value = values[i];
}
command.ExecuteNonQuery();
}