public static void InsertDataTable(
this DbConnection connection,
DataTable table,
string tableName,
IDictionary <string, string> mappings,
Func <string, string> encloseIdentifier)
{
string fieldNames = mappings.Values.Select(x => encloseIdentifier(x)).Join(",");
string parameterNames = mappings.Values.Join(",").Replace(",", ",@").Prepend("@");
var columns = table.Columns.OfType <DataColumn>().Select(x => new { x.ColumnName, x.DataType });
const string INSERT_INTO_FORMAT = "INSERT INTO {0}({1}) VALUES({2})";
using (var command = connection.CreateCommand())
{
string commandText = string.Format(
INSERT_INTO_FORMAT,
tableName,
fieldNames,
parameterNames);
command.CommandType = CommandType.Text;
command.CommandText = commandText;
mappings.ForEach(mapping =>
{
var parameter = command.CreateParameter();
parameter.ParameterName = string.Concat("@", mapping.Value);
var column = columns.Single(x => x.ColumnName == mapping.Key);
parameter.DbType = DataTypeConvertor.GetDbType(column.DataType);
command.Parameters.Add(parameter);
});
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
foreach (DataRow row in table.Rows)
{
foreach (DataColumn column in table.Columns)
{
command.Parameters["@" + column.ColumnName].Value = row[column];
//command.Parameters["@" + column.ColumnName].Value =
// GetFormattedValue(column.DataType, row[column]);
}
command.ExecuteNonQuery();
}
//if (!alreadyOpen)
//{
// connection.Close();
//}
}
}
/// <summary>
/// <para>Inserts the specified entities into the specified Table. Properties are matched</para>
/// <para>with Sql Column Names by using the specified mappings dictionary.</para>
/// </summary>
/// <typeparam name="T">The type of entity to persist to Sql database.</typeparam>
/// <param name="connection">This DbConnection.</param>
/// <param name="entities">The entities to persist to Sql database.</param>
/// <param name="tableName">The table to insert the entities into.</param>
/// <param name="mappings">
/// <para>A Dictionary to use to map properties to Sql columns.</para>
/// <para>Key = Property Name, Value = Sql Column Name.</para>
/// </param>
public static void InsertCollection <T>(
this DbConnection connection,
IEnumerable <T> entities,
string tableName,
IDictionary <string, string> mappings,
Func <string, string> encloseIdentifier)
{
//using (var transactionScope = new TransactionScope()) //MySQL doesn't like this...
//{
string fieldNames = mappings.Values.Select(x => encloseIdentifier(x)).Join(",");
string parameterNames = mappings.Values.Join(",").Replace(",", ",@").Prepend("@");
var properties = typeof(T).GetProperties();
using (var command = connection.CreateCommand())
{
string commandText = string.Format(
INSERT_INTO_FORMAT,
encloseIdentifier(tableName),
fieldNames,
parameterNames);
command.CommandType = CommandType.Text;
command.CommandText = commandText;
mappings.ForEach(mapping =>
{
var parameter = command.CreateParameter();
parameter.ParameterName = string.Concat("@", mapping.Value);
var property = properties.Single(p => p.Name == mapping.Key);
parameter.DbType = DataTypeConvertor.GetDbType(property.PropertyType);
command.Parameters.Add(parameter);
});
foreach (T entity in entities)
{
properties.ForEach(property =>
{
command.Parameters["@" + property.Name].Value = property.GetValue(entity, null);
//command.Parameters["@" + property.Name].Value =
// GetFormattedValue(property.PropertyType, property.GetValue(entity, null));
});
command.ExecuteNonQuery();
}
// transactionScope.Complete();
//}
}
}
/// <summary>
/// Inserts [entities] into the specified table. The objects' property names are matched to column names
/// by using the specified mappings dictionary.
/// </summary>
/// <typeparam name="T">The type of entity to persist to the database.</typeparam>
/// <param name="connection">The DbConnection to use.</param>
/// <param name="entities">The entities to persist to the database.</param>
/// <param name="tableName">The name of the table to insert the entity into.</param>
/// <param name="mappings">
/// A System.Collection.Generic.IDictionary`2 used to map object properties to column names.
/// Key = Property Name, Value = Column Name.
/// </param>
/// <returns>The number of rows affected.</returns>
public static int InsertCollection <T>(this DbConnection connection, IEnumerable <T> entities, string tableName, IDictionary <string, string> mappings)
{
const string INSERT_INTO_FORMAT = "INSERT INTO {0}({1}) VALUES({2})";
string fieldNames = mappings.Values.Join(",");
string parameterNames = fieldNames.Replace(",", ",@").Prepend("@");
var properties = typeof(T).GetTypeInfo().GetProperties();
using (var command = connection.CreateCommand())
{
string commandText = string.Format(INSERT_INTO_FORMAT, tableName, fieldNames, parameterNames);
command.CommandType = CommandType.Text;
command.CommandText = commandText;
mappings.ForEach(mapping =>
{
var parameter = command.CreateParameter();
parameter.ParameterName = string.Concat("@", mapping.Value);
var property = properties.Single(p => p.Name == mapping.Key);
parameter.DbType = DataTypeConvertor.GetDbType(property.PropertyType);
command.Parameters.Add(parameter);
});
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
int rowsAffected = 0;
foreach (var entity in entities)
{
properties.ForEach(property =>
{
command.Parameters["@" + property.Name].Value =
GetFormattedValue(property.PropertyType, property.GetValue(entity, null));
});
rowsAffected += command.ExecuteNonQuery();
}
if (!alreadyOpen)
{
connection.Close();
}
return(rowsAffected);
}
}
/// <summary>
/// <para>Inserts the specified entities into the specified Table. Properties are matched</para>
/// <para>with Sql Column Names by using the specified mappings dictionary.</para>
/// </summary>
/// <typeparam name="T">The type of entity to persist to Sql database.</typeparam>
/// <param name="connection">This DbConnection.</param>
/// <param name="entities">The entities to persist to Sql database.</param>
/// <param name="tableName">The table to insert the entities into.</param>
/// <param name="mappings">
/// <para>A Dictionary to use to map properties to Sql columns.</para>
/// <para>Key = Property Name, Value = Sql Column Name.</para>
/// </param>
public static void InsertCollection <T>(this DbConnection connection, IEnumerable <T> entities, string tableName, IDictionary <string, string> mappings)
{
using (TransactionScope transactionScope = new TransactionScope())
{
const string INSERT_INTO_FORMAT = "INSERT INTO {0}({1}) VALUES({2})";
string fieldNames = mappings.Values.Join(",");
string parameterNames = fieldNames.Replace(",", ",@").Prepend("@");
PropertyInfo[] properties = typeof(T).GetProperties();
using (DbCommand command = connection.CreateCommand())
{
string commandText = string.Format(INSERT_INTO_FORMAT, tableName, fieldNames, parameterNames);
command.CommandType = CommandType.Text;
command.CommandText = commandText;
mappings.ForEach(mapping =>
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = string.Concat("@", mapping.Value);
PropertyInfo property = properties.Single(p => p.Name == mapping.Key);
parameter.DbType = DataTypeConvertor.GetDbType(property.GetType());
command.Parameters.Add(parameter);
});
connection.Open();
foreach (T entity in entities)
{
properties.ForEach(property =>
{
command.Parameters["@" + property.Name].Value =
GetFormattedValue(property.PropertyType, property.GetValue(entity, null));
});
command.ExecuteNonQuery();
}
connection.Close();
transactionScope.Complete();
}
}
}
public static DbType ToDbType(this Type type)
{
return(DataTypeConvertor.GetDbType(type));
}
public static ColumnInfoCollection GetColumnData(this SqlConnection connection, string tableName, string schema = "dbo")
{
const string CMD_COLUMN_INFO_FORMAT =
@"SELECT
COLUMN_NAME,
COLUMN_DEFAULT,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE,
ORDINAL_POSITION,
NUMERIC_PRECISION,
NUMERIC_SCALE,
COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS 'IsIdentity'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND TABLE_SCHEMA = @SchemaName";
const string CMD_IS_PRIMARY_KEY_FORMAT =
@"SELECT CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
WHERE CU.CONSTRAINT_NAME = T.Constraint_Name
AND CU.TABLE_NAME = T.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND CU.TABLE_NAME = @TableName
AND T.CONSTRAINT_SCHEMA = @SchemaName";
var list = new ColumnInfoCollection();
bool alreadyOpen = (connection.State != ConnectionState.Closed);
try
{
var foreignKeyColumns = connection.GetForeignKeyData(tableName, schema);
if (!alreadyOpen)
{
connection.Open();
}
using (var command = new SqlCommand(CMD_COLUMN_INFO_FORMAT, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add(new SqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
command.Parameters.Add(new SqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@SchemaName",
Value = schema
});
using (var reader = command.ExecuteReader())
{
ColumnInfo columnInfo = null;
while (reader.Read())
{
columnInfo = new ColumnInfo();
if (!reader.IsDBNull(0))
{
columnInfo.ColumnName = reader.GetString(0);
}
if (!reader.IsDBNull(1))
{
columnInfo.DefaultValue = reader.GetString(1);
}
else
{
columnInfo.DefaultValue = string.Empty;
}
if (foreignKeyColumns.Contains(columnInfo.ColumnName))
{
columnInfo.KeyType = KeyType.ForeignKey;
}
//else
//{
try
{
string type = reader.GetString(2);
columnInfo.DataTypeNative = type;
columnInfo.DataType = DataTypeConvertor.GetDbType(type.ToEnum <SqlDbType>(true));
}
catch (ArgumentNullException)
{
columnInfo.DataType = DbType.Object;
}
catch (ArgumentException)
{
columnInfo.DataType = DbType.Object;
}
//}
if (!reader.IsDBNull(3))
{
columnInfo.MaximumLength = reader.GetInt32(3);
}
if (!reader.IsDBNull(4))
{
if (reader.GetString(4).ToUpperInvariant().Equals("NO"))
{
columnInfo.IsNullable = false;
}
else
{
columnInfo.IsNullable = true;
}
}
if (!reader.IsDBNull(5))
{
columnInfo.OrdinalPosition = reader.GetInt32(5);
}
if (!reader.IsDBNull(6))
{
columnInfo.Precision = reader.GetByte(6);
}
if (!reader.IsDBNull(7))
{
columnInfo.Scale = reader.GetInt32(7);
}
if (!reader.IsDBNull(8))
{
columnInfo.IsAutoIncremented = reader.GetInt32(8) == 1 ? true : false;
}
list.Add(columnInfo);
}
}
}
}
finally
{
if (!alreadyOpen && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
#region Primary Keys
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = CMD_IS_PRIMARY_KEY_FORMAT;
command.Parameters.Add(new SqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@TableName",
Value = tableName
});
command.Parameters.Add(new SqlParameter
{
Direction = ParameterDirection.Input,
DbType = DbType.String,
ParameterName = "@SchemaName",
Value = schema
});
alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
string pkColumn = reader.GetString(0);
var match = list[pkColumn];
if (match != null)
{
match.KeyType = KeyType.PrimaryKey;
}
}
}
if (!alreadyOpen)
{
connection.Close();
}
}
#endregion Primary Keys
return(list);
}
public static ColumnInfoCollection GetColumnData(this OleDbConnection connection, string tableName)
{
var restrictions = new object[] { null, null, tableName };
var foreignKeyRestrictions = new object[] { null, null, null, null, null, tableName };
bool alreadyOpen = (connection.State != ConnectionState.Closed);
if (!alreadyOpen)
{
connection.Open();
}
DataTable columnsSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrictions);
DataTable primaryKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, restrictions);
DataTable foreignKeySchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, foreignKeyRestrictions);
if (!alreadyOpen)
{
connection.Close();
}
var columnData = new ColumnInfoCollection();
foreach (DataRow row in columnsSchema.Rows)
{
var columnInfo = new ColumnInfo
{
ColumnName = row.Field <string>("COLUMN_NAME"),
DataType = DataTypeConvertor.GetDbType((OleDbType)row.Field <int>("DATA_TYPE")),
IsNullable = row.Field <bool>("IS_NULLABLE")
};
if (row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value)
{
columnInfo.MaximumLength = row.Field <long>("CHARACTER_MAXIMUM_LENGTH");
}
columnInfo.OrdinalPosition = row.Field <long>("ORDINAL_POSITION");
if (row.Field <bool>("COLUMN_HASDEFAULT"))
{
columnInfo.DefaultValue = row.Field <string>("COLUMN_DEFAULT");
}
if (primaryKeySchema != null)
{
foreach (DataRow pkRow in primaryKeySchema.Rows)
{
if (columnInfo.ColumnName == pkRow.Field <string>("COLUMN_NAME"))
{
columnInfo.KeyType = KeyType.PrimaryKey;
break;
}
}
}
if (columnInfo.KeyType == KeyType.None)
{
if (foreignKeySchema != null)
{
foreach (DataRow fkRow in foreignKeySchema.Rows)
{
if (columnInfo.ColumnName == fkRow.Field <string>("FK_COLUMN_NAME"))
{
columnInfo.KeyType = KeyType.ForeignKey;
break;
}
}
}
}
if (row["NUMERIC_PRECISION"] != DBNull.Value)
{
columnInfo.Precision = row.Field <int>("NUMERIC_PRECISION");
}
if (row["NUMERIC_SCALE"] != DBNull.Value)
{
columnInfo.Scale = row.Field <int>("NUMERIC_SCALE");
}
columnData.Add(columnInfo);
}
columnsSchema.DisposeIfNotNull();
primaryKeySchema.DisposeIfNotNull();
foreignKeySchema.DisposeIfNotNull();
return(columnData);
}