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