public override void AddColumnsByEntityType(Type entityType)
{
foreach (PropertyInfo p in entityType.GetProperties())
{
if (p.PropertyType == typeof(IntPtr) ||
p.PropertyType == typeof(UIntPtr) ||
(p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(List <>)))
{
continue;
}
SqlDatabaseTableColumnWindows c = new SqlDatabaseTableColumnWindows();
c.ColumnName = p.Name;
c.DataType = SqlTypeConverterWindows.Instance.GetSqlTypeNameFromDotNetType(
p.PropertyType,
EntityReader.IsTypeIsNullable(p.PropertyType));
_columns.Add(c);
}
}
public int Update(
object e,
string columnName,
bool disposeConnectionAfterExecute,
SqlConnection connection,
SqlTransaction transaction)
{
int result = -1;
Type entityType = e.GetType();
List <SqlParameter> parameters = new List <SqlParameter>();
StringBuilder sqlUpdateCommand = new StringBuilder();
sqlUpdateCommand.AppendLine(string.Format("UPDATE [{0}]", entityType.Name));
sqlUpdateCommand.AppendLine("SET ");
PropertyInfo[] entityProperties = entityType.GetProperties();
if (entityProperties.Length < 1)
{
throw new ArgumentException(string.Format(
"{0} has no properties to update in the local database.",
entityType.FullName));
}
bool firstUpdateColumn = true;
StringBuilder whereClause = new StringBuilder();
whereClause.AppendLine("WHERE ");
foreach (PropertyInfo p in entityProperties)
{
if ((p.PropertyType != typeof(string) &&
p.PropertyType != typeof(byte) &&
p.PropertyType != typeof(byte[])) &&
(p.PropertyType.IsClass ||
p.PropertyType.IsEnum ||
p.PropertyType.IsInterface ||
p.PropertyType.IsNotPublic ||
p.PropertyType.IsPointer))
{
continue;
}
SqlDatabaseTableColumnWindows column = (SqlDatabaseTableColumnWindows)_columns[p.Name];
if (!firstUpdateColumn)
{
sqlUpdateCommand.AppendLine(",");
}
object value = p.GetValue(e, null);
if (value == null)
{
value = DBNull.Value;
}
SqlParameter parameter = new SqlParameter(string.Format("@{0}", p.Name), value)
{
SqlDbType = column.SqlDbType
};
parameters.Add(parameter);
sqlUpdateCommand.Append(string.Format("[{0}] = @{0}", p.Name));
if (string.IsNullOrEmpty(columnName)) //Look up the record to update based on the key columns because no column name was specified.
{
if (column.IsKey)
{
if (!firstUpdateColumn)
{
whereClause.AppendLine(",");
}
whereClause.Append(string.Format("[{0}] = @{0}", p.Name));
}
}
firstUpdateColumn = false;
}
sqlUpdateCommand.AppendLine();
if (!string.IsNullOrEmpty(columnName)) //Look up the record to update based on the specified column name.
{
whereClause.Append(string.Format("[{0}] = @{0}", columnName));
}
sqlUpdateCommand.Append(whereClause);
try
{
if (connection == null)
{
connection = new SqlConnection(_connectionString);
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
string commandText = sqlUpdateCommand.ToString();
using (SqlCommand command = new SqlCommand(commandText, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
parameters.ForEach(p => command.Parameters.Add(p));
result = command.ExecuteNonQuery();
if (result < 0)
{
if (string.IsNullOrEmpty(columnName))
{
throw new Exception(string.Format("Could not update {0} on database against key columns.", entityType.Name));
}
else
{
object columnValue = EntityReaderGeneric <object> .GetPropertyValue(columnName, e, true);
throw new Exception(string.Format("Could not update {0} on database with value {1} on column {2}.", entityType.Name, columnValue, columnName));
}
}
}
}
finally
{
if (disposeConnectionAfterExecute &&
connection != null &
connection.State != ConnectionState.Closed)
{
connection.Dispose();
}
}
return(result);
}
public int Insert(
object e,
bool disposeConnectionAfterExecute,
SqlConnection connection,
SqlTransaction transaction)
{
int result = -1;
Type entityType = e.GetType();
List <SqlParameter> parameters = new List <SqlParameter>();
StringBuilder sqlInsertCommand = new StringBuilder();
sqlInsertCommand.AppendLine(string.Format("INSERT INTO [{0}]", entityType.Name));
sqlInsertCommand.AppendLine("(");
PropertyInfo[] entityProperties = entityType.GetProperties();
if (entityProperties.Length < 1)
{
throw new ArgumentException(string.Format(
"{0} has no properties to update in the local database.",
entityType.FullName));
}
bool firstInsertColumn = true;
foreach (PropertyInfo p in entityProperties) //The columns to insert.
{
if ((p.PropertyType != typeof(string) &&
p.PropertyType != typeof(byte) &&
p.PropertyType != typeof(byte[])) &&
(p.PropertyType.IsClass ||
p.PropertyType.IsEnum ||
p.PropertyType.IsInterface ||
p.PropertyType.IsNotPublic ||
p.PropertyType.IsPointer))
{
continue;
}
if (!firstInsertColumn)
{
sqlInsertCommand.AppendLine(",");
}
object value = p.GetValue(e, null);
if (value == null)
{
value = DBNull.Value;
}
SqlDatabaseTableColumnWindows column = (SqlDatabaseTableColumnWindows)_columns[p.Name];
parameters.Add(new SqlParameter(string.Format("@{0}", p.Name), value)
{
SqlDbType = column.SqlDbType
});
sqlInsertCommand.Append(string.Format("[{0}]", p.Name));
firstInsertColumn = false;
}
sqlInsertCommand.AppendLine();
sqlInsertCommand.AppendLine(")");
sqlInsertCommand.AppendLine("VALUES");
sqlInsertCommand.AppendLine("(");
bool firstInsertValue = true;
foreach (SqlParameter p in parameters)
{
if (!firstInsertValue)
{
sqlInsertCommand.AppendLine(",");
}
sqlInsertCommand.Append(string.Format("{0}", p.ParameterName));
firstInsertValue = false;
}
sqlInsertCommand.AppendLine();
sqlInsertCommand.AppendLine(")");
try
{
if (connection == null)
{
connection = new SqlConnection(_connectionString);
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (SqlCommand command = new SqlCommand(sqlInsertCommand.ToString(), connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
parameters.ForEach(p => command.Parameters.Add(p));
result = command.ExecuteNonQuery();
if (result < 1)
{
throw new Exception(string.Format(
"Could not insert {0} into database with value {1} on column {2}.",
entityType.Name));
}
}
}
finally
{
if (disposeConnectionAfterExecute &&
connection != null &&
connection.State != ConnectionState.Closed)
{
connection.Dispose();
}
}
return(result);
}