protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List<DbColumnMapping>();
var sql = ""
+ "SELECT ISC.TABLE_NAME ,ISC.COLUMN_NAME "
+ " ,CASE WHEN ISI.PRIMARY_KEY = 1 THEN CAST(1 AS Bit) ELSE CAST(0 AS Bit) END AS IsPrimaryKey"
+ " ,CASE WHEN ISC.AUTOINC_INCREMENT = 1 THEN CAST(1 AS Bit) ELSE CAST(0 AS Bit) END as IsAuto"
+ " FROM INFORMATION_SCHEMA.COLUMNS ISC "
+ " LEFT OUTER JOIN INFORMATION_SCHEMA.INDEXES ISI "
+ " ON ISC.TABLE_NAME = ISI.TABLE_NAME AND ISC.COLUMN_NAME = ISI.COLUMN_NAME";
using (var conn = this.OpenConnection())
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
var dr = cmd.ExecuteReader();
while (dr.Read()) {
var clm = dr["COLUMN_NAME"] as string;
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString) {
TableName = dr["TABLE_NAME"] as string,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = (bool)dr["IsPrimaryKey"],
IsAutoIncementing = (bool)dr["IsAuto"]
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List <DbColumnMapping>();
var sql = ""
+ "SELECT c.TABLE_NAME, c.COLUMN_NAME, kcu.CONSTRAINT_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, tc.CONSTRAINT_TYPE, "
+ "CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, "
+ "(CASE ((SELECT CASE (LENGTH(pg_get_serial_sequence(quote_ident(c.TABLE_NAME), c.COLUMN_NAME)) > 0) WHEN true THEN 1 ELSE 0 END) + "
+ "(SELECT CASE (SELECT pgc.relkind FROM pg_class pgc WHERE pgc.relname = c.TABLE_NAME || '_' || c.COLUMN_NAME || '_' || 'seq') WHEN 'S' THEN 1 ELSE 0 END)) "
+ "WHEN 0 THEN false ELSE true END) AS IsAuto "
+ "FROM information_schema.columns c "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu "
+ "ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc "
+ "ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME "
+ "WHERE c.TABLE_SCHEMA = 'public'";
using (var dr = this.OpenReader(sql)) {
while (dr.Read())
{
var clm = dr["COLUMN_NAME"] as string;
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString)
{
TableName = dr["TABLE_NAME"] as string,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = (bool)dr["IsPrimaryKey"],
IsAutoIncementing = (bool)dr["IsAuto"]
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List <DbColumnMapping>();
var sqlPragma = "PRAGMA table_info({0})";
foreach (var tableName in DbTableNames)
{
var schemaSql = string.Format("SELECT 1 FROM sqlite_master WHERE tbl_name='{0}' and sql LIKE '%WITHOUT ROWID%'", tableName);
var noRowId = Convert.ToInt32(this.ExecuteScalar(schemaSql));
using (var dr = this.OpenReader(string.Format(sqlPragma, tableName)))
{
while (dr.Read())
{
var clm = dr[1] as string;
string dbType = dr.GetString(2);
bool isPk = dr.GetBoolean(5);
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString)
{
TableName = tableName,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = dr.GetBoolean(5),
IsAutoIncementing = (isPk && (dbType.Equals("integer", StringComparison.CurrentCultureIgnoreCase)) && (noRowId == 0))
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
}
protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List <DbColumnMapping>();
var sql = ""
+ "SELECT c.TABLE_NAME, c.COLUMN_NAME, "
+ " CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, "
+ " CASE (COLUMNPROPERTY(object_id(tc.TABLE_NAME), kcu.COLUMN_NAME, 'IsIdentity')) WHEN 1 THEN CAST(1 AS Bit) ELSE CAST(0 AS Bit) END as IsAuto "
+ "FROM INFORMATION_SCHEMA.COLUMNS c "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu "
+ "ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc "
+ "ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME";
using (var conn = this.OpenConnection()) {
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var clm = dr["COLUMN_NAME"] as string;
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString)
{
TableName = dr["TABLE_NAME"] as string,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = (bool)dr["IsPrimaryKey"],
IsAutoIncementing = (bool)dr["IsAuto"]
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
}
public void ReserveAutoIdsForItems(IEnumerable <T> items)
{
if (items.Count() > 0)
{
DbColumnMapping autoPkColumn = null;
if (TableMapping.PrimaryKeyMapping[0].IsAutoIncementing)
{
// We need this later:
autoPkColumn = TableMapping.PrimaryKeyMapping[0];
// We have to use some tricks to batch insert the proper sequence values:
string sequenceName = string.Format("{0}_{1}_seq", this.TableMapping.DBTableName, autoPkColumn.ColumnName);
int itemCount = items.Count();
string sqlReservedSequenceValues = string.Format("SELECT nextval('\"{0}\"') FROM generate_series( 1, {1} ) n", sequenceName, itemCount);
// Load up the reserved values into the Id field for each item:
using (var dr = this.Database.OpenReader(sqlReservedSequenceValues)) {
int row = 0;
while (dr.Read())
{
var curr = items.ElementAt(row);
var props = curr.GetType().GetProperties();
var pkProp = props.FirstOrDefault(p => p.Name == autoPkColumn.PropertyName);
var converted = Convert.ChangeType(dr[0], pkProp.PropertyType);
pkProp.SetValue(curr, converted, null);
row++;
}
}
}
}
}
protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List <DbColumnMapping>();
var sql = ""
+ "SELECT ISC.TABLE_NAME ,ISC.COLUMN_NAME "
+ " ,CASE WHEN ISI.PRIMARY_KEY = 1 THEN CAST(1 AS Bit) ELSE CAST(0 AS Bit) END AS IsPrimaryKey"
+ " ,CASE WHEN ISC.AUTOINC_INCREMENT = 1 THEN CAST(1 AS Bit) ELSE CAST(0 AS Bit) END as IsAuto"
+ " FROM INFORMATION_SCHEMA.COLUMNS ISC "
+ " LEFT OUTER JOIN INFORMATION_SCHEMA.INDEXES ISI "
+ " ON ISC.TABLE_NAME = ISI.TABLE_NAME AND ISC.COLUMN_NAME = ISI.COLUMN_NAME";
using (var conn = this.OpenConnection())
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var clm = dr["COLUMN_NAME"] as string;
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString)
{
TableName = dr["TABLE_NAME"] as string,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = (bool)dr["IsPrimaryKey"],
IsAutoIncementing = (bool)dr["IsAuto"]
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
protected override void WriteToDb(Event[] listToWrite)
{
// Don't do anything if list is empty.
if (CurrentList.Count == 0)
{
return;
}
try
{
}
catch (InvalidOperationException invalidEx)
{
if (invalidEx.Message == "The given ColumnMapping does not match up with any column in the source or destination.")
{
var columns = String.Join(",", DbColumnMapping.Select(d => d.DestinationColumn));
HandleError(new Exception($"Missing/Invalid table columns. Required columns: {columns}", invalidEx));
}
else
{
HandleError(invalidEx);
}
}
catch (Exception e)
{
HandleError(e);
}
}
protected override void WriteToDb(Event[] listToWrite)
{
// Don't do anything if list is empty.
if (CurrentList.Count == 0)
{
return;
}
try
{
using (SqlConnection con = new SqlConnection(_connectionString))
{
//create object of SqlBulkCopy which help to insert
using (SqlBulkCopy objbulk = new SqlBulkCopy(con))
{
CustomDataReader customDataReader = new CustomDataReader(listToWrite);
objbulk.DestinationTableName = TableName;
foreach (var mapping in DbColumnMapping)
{
objbulk.ColumnMappings.Add(mapping);
}
con.Open();
if (BatchSize <= 1) // use sync method if BatchSize is < 1
{
objbulk.WriteToServer(customDataReader);
}
else // use async methodd
{
objbulk.WriteToServerAsync(customDataReader);
}
}
}
}
catch (InvalidOperationException invalidEx)
{
if (invalidEx.Message == "The given ColumnMapping does not match up with any column in the source or destination.")
{
var columns = String.Join(",", DbColumnMapping.Select(d => d.DestinationColumn));
HandleError(new Exception($"Missing/Invalid table columns. Required columns: {columns}", invalidEx));
}
else
{
HandleError(invalidEx);
}
}
catch (Exception e)
{
HandleError(e);
}
}
public static object[] GetInsertParamValues <T>(this T insertedItem, DbColumnMapping pkMap)
{
var expando = insertedItem.ToExpando();
var settings = (IDictionary <string, object>)expando;
var mappedPkPropertyName = pkMap.PropertyName;
if (pkMap.IsAutoIncementing)
{
var col = settings.FirstOrDefault(x => x.Key.Equals(mappedPkPropertyName, StringComparison.OrdinalIgnoreCase));
settings.Remove(col);
}
return(settings.Values.ToArray());
}
public IEnumerable <string> BuildColumnDefintions(DbColumnMapping <IDbProvider> column)
{
yield return(column.ColumnName);
yield return(column.ColumnTypeName);
if (column.PrimaryKeyAttribute != null)
{
yield return("PRIMARY KEY");
if (column.PrimaryKeyAttribute.Order.HasValue)
{
yield return(this.GetString(column.PrimaryKeyAttribute.Order.Value));
}
if (column.PrimaryKeyAttribute.IsAutoIncrement)
{
yield return("AUTOINCREMENT");
}
}
}
public static object[] GetUpdateParamValues <T>(this T updatedItem, DbColumnMapping pkMap)
{
var expando = updatedItem.ToExpando();
var settings = (IDictionary <string, object>)expando;
var mappedPkPropertyName = pkMap.PropertyName;
var args = (from item in settings
where !item.Key.Equals(mappedPkPropertyName, StringComparison.OrdinalIgnoreCase) &&
item.Value != null
select item.Value).ToList();
if (args.Any())
{
//add the key
args.Add(settings.First(x => x.Key.Equals(mappedPkPropertyName, StringComparison.OrdinalIgnoreCase))
.Value);
}
else
{
throw new InvalidOperationException("No parsable object was sent in - could not divine any name/value pairs");
}
return(args.ToArray());
}
protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List<DbColumnMapping>();
var sql = ""
+ "SELECT c.TABLE_NAME, c.COLUMN_NAME, kcu.CONSTRAINT_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, tc.CONSTRAINT_TYPE, "
+ "CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, "
+ "(CASE ((SELECT CASE (LENGTH(pg_get_serial_sequence(quote_ident(c.TABLE_NAME), c.COLUMN_NAME)) > 0) WHEN true THEN 1 ELSE 0 END) + "
+ "(SELECT CASE (SELECT pgc.relkind FROM pg_class pgc WHERE pgc.relname = c.TABLE_NAME || '_' || c.COLUMN_NAME || '_' || 'seq') WHEN 'S' THEN 1 ELSE 0 END)) "
+ "WHEN 0 THEN false ELSE true END) AS IsAuto "
+ "FROM information_schema.columns c "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu "
+ "ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc "
+ "ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME "
+ "WHERE c.TABLE_SCHEMA = 'public'";
using (var conn = this.OpenConnection()) {
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
var dr = cmd.ExecuteReader();
while (dr.Read()) {
var clm = dr["COLUMN_NAME"] as string;
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString) {
TableName = dr["TABLE_NAME"] as string,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = (bool)dr["IsPrimaryKey"],
IsAutoIncementing = (bool)dr["IsAuto"]
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
}
protected override void LoadDbColumnsList()
{
this.DbColumnsList = new List<DbColumnMapping>();
var sql = ""
+ "SELECT c.TABLE_NAME, c.COLUMN_NAME, "
+ " CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, "
+ " CASE (COLUMNPROPERTY(object_id(tc.TABLE_NAME), kcu.COLUMN_NAME, 'IsIdentity')) WHEN 1 THEN CAST(1 AS Bit) ELSE CAST(0 AS Bit) END as IsAuto "
+ "FROM INFORMATION_SCHEMA.COLUMNS c "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu "
+ "ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME "
+ "LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc "
+ "ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME";
using (var conn = this.OpenConnection()) {
using (var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
var dr = cmd.ExecuteReader();
while (dr.Read()) {
var clm = dr["COLUMN_NAME"] as string;
var newColumnMapping = new DbColumnMapping(this.DbDelimiterFormatString) {
TableName = dr["TABLE_NAME"] as string,
ColumnName = clm,
PropertyName = clm,
IsPrimaryKey = (bool)dr["IsPrimaryKey"],
IsAutoIncementing = (bool)dr["IsAuto"]
};
this.DbColumnsList.Add(newColumnMapping);
}
}
}
}
