public static Workbook ToExcel <T>(this IEnumerable <T> src) where T : new() { var myWb = new Workbook(); myWb.AddStyles(); var myWs = myWb.Worksheets.Add("one"); var headerRow = myWs.Table.Rows.Add(); var cols = MereUtils.CacheCheck <T>().SelectMereColumns.ToList(); foreach (var field in cols) { var myCol = new WorksheetColumn(150); myWs.Table.Columns.Add(myCol); headerRow.Cells.Add(field.DisplayName, DataType.String, "HeaderStyle"); } foreach (var rec in src) { var newRow = myWs.Table.Rows.Add(); newRow.AutoFitHeight = false; foreach (var field in cols) { var v = field.GetBase(rec); var val = v == null ? "" : v.ToString(); newRow.Cells.Add(val, DataType.String, (myWs.Table.Rows.IndexOf(newRow) % 2) == 0 ? "s31" : "s31Odd"); } } return(myWb); }
public async static Task <bool> MereBulkInsertAsync <T>(this IEnumerable <T> toInsertEn, int batchSize, bool truncateLength, MereDataSource mereDataSource) where T : new() { var toInsertList = toInsertEn.ToList(); if (toInsertList.Count <= 0) { return(true); } var mereTableMin = MereUtils.CacheCheck <T>(); var mereDataReader = new MereDataReader <T>(toInsertList, truncateLength); SqlConnection conn = mereDataSource == null?MereUtils.GetConnection <T>() : MereUtils.GetConnection(mereDataSource); using (conn) { await conn.OpenAsync(); using (var cpy = new SqlBulkCopy(conn)) { cpy.EnableStreaming = true; cpy.BulkCopyTimeout = 0; cpy.BatchSize = batchSize; mereTableMin.SelectMereColumns.ForEach( x => cpy.ColumnMappings.Add(x.ColumnName, x.ColumnName)); cpy.DestinationTableName = mereTableMin.TableName; await cpy.WriteToServerAsync(mereDataReader); } } return(true); }
public static bool MereBulkInsert <T>(this IEnumerable <T> toInsertEn, int batchSize, bool truncateLength) where T : new() { var toInsertList = toInsertEn.ToList(); if (toInsertList.Count <= 0) { return(true); } var mereTableMin = MereUtils.CacheCheck <T>(); var mereDataReader = new MereDataReader <T>(toInsertList, truncateLength); using (var conn = MereUtils.GetConnection <T>()) { conn.Open(); using (var cpy = new SqlBulkCopy(conn)) { cpy.EnableStreaming = true; cpy.BulkCopyTimeout = 0; cpy.BatchSize = batchSize; mereTableMin.SelectMereColumns.ForEach( x => cpy.ColumnMappings.Add(x.ColumnName, x.ColumnName)); cpy.DestinationTableName = mereTableMin.TableName; //cpy.NotifyAfter = 1; //cpy.SqlRowsCopied += (o, e) => Console.WriteLine(e.RowsCopied); cpy.WriteToServer(mereDataReader); } } return(true); }
public MereSqlDataReader(SqlDataReader reader, bool byName) { _sqlDataReader = reader; //_mereTable = MereUtils.CacheCheck<T>(); _selectMereColumnsList = MereUtils.CacheCheck <T>().SelectMereColumns.ToList(); _byName = byName; }
public static async Task <List <dynamic> > ExecuteQueryAsync <T>(this T instance, string sql) where T : new() { var mereTable = MereUtils.CacheCheck <T>(); var toReturn = new List <dynamic>(); using (var myCn = mereTable.GetConnection()) { var cmd = myCn.CreateCommand(); cmd.CommandText = sql; await myCn.OpenAsync(); using (var reader = new MereSqlDataReader <T>(await cmd.ExecuteReaderAsync())) { while (reader.Read()) { toReturn.Add((dynamic)reader); } myCn.Close(); } } return(toReturn); }
public MereSqlDataReader(SqlCommand cmd, bool byName) { _sqlDataReader = cmd.ExecuteReader(); //_mereTable = MereUtils.CacheCheck<T>(); _selectMereColumnsList = MereUtils.CacheCheck <T>().SelectMereColumns.ToList(); _byName = byName; }
public MereRepo() { _mereTable = MereUtils.CacheCheck <T>(); if (string.IsNullOrEmpty(_mereTable.KeyColumnName)) { throw new Exception("Must have a key column to use mere repo for right now"); } _keyColumn = _mereTable.GetMereColumn(_mereTable.KeyColumnName); }
public MereDataReader(IEnumerable <T> list) { if (list == null) { throw new NullReferenceException("list was null or empty"); } _mereTable = MereUtils.CacheCheck <T>(); FieldCount = _mereTable.SelectMereColumns.Count; _list = list.ToList(); }
public static MereDataSource Create <T>() where T : new() { var mereTable = MereUtils.CacheCheck <T>(); return(new MereDataSource { Environments = new Dictionary <string, MereDataSource>(), ServerName = mereTable.ServerName, DatabaseName = mereTable.DatabaseName, UserId = mereTable.UserId, Password = mereTable.Password, TableName = mereTable.TableName }); }
public static int ExecuteNonQuery <T>(this T instance, string sql) where T : new() { var mereTable = MereUtils.CacheCheck <T>(); int result; using (var myCn = mereTable.GetConnection()) { var cmd = myCn.CreateCommand(); cmd.CommandText = sql; myCn.Open(); result = cmd.ExecuteNonQuery(); myCn.Close(); } return(result); }
public static int TruncateTable <T>(this T instance) where T : new() { var mereTable = MereUtils.CacheCheck <T>(); int result; using (var myCn = mereTable.GetConnection()) { var cmd = myCn.CreateCommand(); cmd.CommandText = string.Format("TRUNCATE TABLE {0}", mereTable.TableName); myCn.Open(); result = cmd.ExecuteNonQuery(); myCn.Close(); } return(result); }
public static int ExecuteByKeyOrIdentity <T>(T toDelete) where T : new() { var mereTable = MereUtils.CacheCheck <T>(); var key = mereTable.SelectMereColumns.FirstOrDefault(x => x.IsKey || x.IsIdentity); int result; using (var myCn = mereTable.GetConnection()) { var cmd = myCn.CreateCommand(); cmd.Parameters.AddWithValue("@key", key.GetBase(toDelete)); cmd.CommandText = "DELETE FROM " + mereTable.TableName + " WHERE " + key.ColumnName + "=@key"; myCn.Open(); result = cmd.ExecuteNonQuery(); myCn.Close(); } return(result); }
public static IEnumerable <TCopyTo> MereCopyTo <T, TCopyTo>(this IEnumerable <T> toCopy) where T : new() where TCopyTo : new() { var toCopyList = toCopy.ToList(); var mereTableMinFrom = MereUtils.CacheCheck <T>(); var mereTableMinTo = MereUtils.CacheCheck <TCopyTo>(); foreach (var from in toCopyList) { var n = Activator.CreateInstance <TCopyTo>(); foreach (var fromProp in mereTableMinFrom.SelectMereColumns) { var mereColumn = mereTableMinTo.GetMereColumn(fromProp.ColumnName); if (mereColumn == null) { continue; } mereColumn.SetBase(n, fromProp.GetBase(from)); } yield return(n); } //var toReturn = new List<TCopyTo>(); // Parallel.For(0, toCopyList.Count, // i => // { // var n = Activator.CreateInstance<TCopyTo>(); // foreach (var fromProp in mereTableMinFrom.SelectMereColumns) // { // var mereColumn = mereTableMinTo.GetMereColumn(fromProp.ColumnName); // // if (mereColumn == null) // continue; // // mereColumn.SetBase(n, fromProp.GetBase(toCopyList[i])); // } // toReturn.Add(n); // }); //return toReturn; }
public async static Task <bool> MereBulkCopyAsync <T, TCopyTo>(IEnumerable <T> toCopyFrom, int batchSize = 1000, bool truncateLength = false) where T : new() where TCopyTo : new() { var toInsertList = toCopyFrom.MereCopyTo <T, TCopyTo>().ToList(); if (toInsertList.Count <= 0) { return(true); } var mereTableMin = MereUtils.CacheCheck <TCopyTo>(); var mereDataReader = new MereDataReader <TCopyTo>(toInsertList, truncateLength); using (var conn = mereTableMin.GetConnection()) { await conn.OpenAsync(); using (var cpy = new SqlBulkCopy(conn)) { cpy.EnableStreaming = true; cpy.BulkCopyTimeout = 0; cpy.BatchSize = batchSize; foreach (var mereColumn in mereTableMin.SelectMereColumns) { var col = mereColumn.ColumnName; cpy.ColumnMappings.Add(col, col); } cpy.DestinationTableName = mereTableMin.TableName; //cpy.NotifyAfter = 1; //cpy.SqlRowsCopied += (o, e) => Console.WriteLine(e.RowsCopied); await cpy.WriteToServerAsync(mereDataReader); } } return(true); }
public static int MereStraightUpBulkCopy <T, TDest>(bool includeIdentities = false) where T : new() where TDest : new() { var mereTableMin = MereUtils.CacheCheck <T>(); var mereTableMinTo = MereUtils.CacheCheck <TDest>(); var copied = 0; using (var conn = mereTableMin.GetConnection()) { var cmd = new SqlCommand { CommandTimeout = 0, Connection = conn }; var selProps = includeIdentities ? mereTableMin.SqlColumnNamesAll : mereTableMin.SqlColumnNamesNoIdentity; var sql = "INSERT INTO " + mereTableMinTo.DatabaseName + ".dbo." + mereTableMinTo.TableName + " SELECT " + selProps + " FROM " + mereTableMin.DatabaseName + ".dbo." + mereTableMin.TableName + " "; cmd.CommandText = sql; conn.Open(); copied = cmd.ExecuteNonQuery(); } return(copied); }
public static IEnumerable <dynamic> ExecuteCustomDynamicQueryToDynamic <T>(this T instance, string sql) where T : new() { var mereTable = MereUtils.CacheCheck <T>(); var toReturn = new List <dynamic>(); using (var myCn = mereTable.GetConnection()) { var cmd = myCn.CreateCommand(); cmd.CommandText = sql; myCn.Open(); using (var reader = new MereSqlDataReader <T>(cmd.ExecuteReader())) { while (reader.Read()) { toReturn.Add((dynamic)reader); } myCn.Close(); } } return(toReturn); }
/// <summary> /// Check cache for MereFile object for T if one does not exist then creates, adds to cache, and returns it. /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public static MereFile <T> CacheCheckFile <T>() where T : new() { var type = typeof(T); MereFile foundMereFile; if (MereFileCache.TryGetValue(type.TypeHandle, out foundMereFile)) { return((MereFile <T>)foundMereFile); } var mereTable = MereUtils.CacheCheck <T>(); var properties = TypeDescriptor.GetProperties(type).PropertyDescriptorCollectionToList(); var fields = properties.Where( w => (type.GetProperty(w.Name).GetCustomAttributes(typeof(MereFlatFileFieldAttribute), true).Any() || type.GetProperty(w.Name).GetCustomAttributes(typeof(MereDelimitedFileFieldAttribute), true).Any())) .Select( s => new { FlatFileFieldAttributes = type.GetProperty(s.Name) .GetCustomAttributes(typeof(MereFlatFileFieldAttribute), true) .Select(x => (MereFlatFileFieldAttribute)x), DelimitedFieldAttributes = type.GetProperty(s.Name) .GetCustomAttributes(typeof(MereDelimitedFileFieldAttribute), true) .Select(x => (MereDelimitedFileFieldAttribute)x), PropertyDescriptor = s }).ToList(); var mereFlatFileFields = new List <MereFileField>(); var mereDelimitedFileFields = new List <MereFileField>(); var mereFlatFileFieldRecords = new List <MereFile>(); var mereDelimitedFileFieldRecords = new List <MereFile>(); foreach (var field in fields) { var mereColumn = mereTable.AllMereColumns.GetMereColumnByPropertyName(field.PropertyDescriptor.Name); if (IsWritable(mereColumn.PropertyDescriptor.PropertyType)) { mereDelimitedFileFields.AddRange( field.DelimitedFieldAttributes.Select(fieldAttr => GetFileField(mereColumn, fieldAttr) )); mereFlatFileFields.AddRange( field.FlatFileFieldAttributes.Select(fieldAttr => GetFileField(mereColumn, fieldAttr) )); } else { //check if implements IEnumerable var isEnum = typeof(IEnumerable).IsAssignableFrom(field.PropertyDescriptor.PropertyType); var found = field.DelimitedFieldAttributes.Select(fieldAttr => { var enType = isEnum ? field.PropertyDescriptor.PropertyType.GetGenericArguments()[0] : field.PropertyDescriptor.PropertyType; var meth = typeof(MereFileUtils).GetMethod("CacheCheckSubRec") .MakeGenericMethod(new[] { enType }); var subMereFile = (MereFile)meth.Invoke(null, new[] { GetFileField(mereColumn, fieldAttr) }); return(subMereFile); }).ToList(); if (field.DelimitedFieldAttributes.Any()) { mereDelimitedFileFieldRecords.AddRange(found); } if (field.FlatFileFieldAttributes.Any()) { mereFlatFileFieldRecords.AddRange(found); } } } var fileTableAttrs = new { DelimitedFileTableTypeFormatAttrs = type.GetCustomAttributes(typeof(MereDelimitedFileTableTypeFormatAttribute), true) .Select(x => (MereFileTableTypeFormatAttribute)x), DelimitedFileTableAttr = (MereFileTableAttribute)type.GetCustomAttributes(typeof(MereDelimitedFileTableAttribute), true) .FirstOrDefault(), FlatFileTableAttr = (MereFileTableAttribute)type.GetCustomAttributes(typeof(MereFlatFileTableAttribute), true) .FirstOrDefault(), DelimitedFileTableTypeParsingAttrs = type.GetCustomAttributes(typeof(MereDelimitedFileTableTypeParsingAttribute), true) .Select(x => (MereFileTableTypeParsingAttribute)x), FlatFileTableTypeFormatAttrs = type.GetCustomAttributes(typeof(MereFlatFileTableTypeFormatAttribute), true) .Select(x => (MereFileTableTypeFormatAttribute)x), FlatFileTableTypeParsingAttrs = type.GetCustomAttributes(typeof(MereFlatFileTableTypeParsingAttribute), true) .Select(x => (MereFileTableTypeParsingAttribute)x), }; var mereFile = new MereFile <T> { FlatFileSubRecords = mereFlatFileFieldRecords, DelimitedSubRecords = mereDelimitedFileFieldRecords, DelimitedFileTableAttr = fileTableAttrs.DelimitedFileTableAttr, FlatFileTableAttr = fileTableAttrs.FlatFileTableAttr, DelimitedFields = mereDelimitedFileFields.ToList(), FlatFileFields = mereFlatFileFields.ToList(), DelimitedFileTableTypeFormats = fileTableAttrs.DelimitedFileTableTypeFormatAttrs.ToDictionary(x => x.TypeToFormat, x => x.ToStringFormat), FlatFileTableTypeFormats = fileTableAttrs.FlatFileTableTypeFormatAttrs.ToDictionary(x => x.TypeToFormat, x => x.ToStringFormat), DelimitedFileTableTypeParsingOptions = fileTableAttrs.DelimitedFileTableTypeParsingAttrs.Where(x => x.TypeToFormat != null).ToDictionary(x => x.TypeToFormat, x => x.FileFieldParsingOptions), FlatFileTableTypeParsingOptions = fileTableAttrs.FlatFileTableTypeParsingAttrs.Where(x => x.TypeToFormat != null).ToDictionary(x => x.TypeToFormat, x => x.FileFieldParsingOptions), DelimitedFileTableParsingOptions = fileTableAttrs.DelimitedFileTableTypeParsingAttrs.Where(x => x.TypeToFormat == null).SelectMany(x => x.FileFieldParsingOptions).ToList(), FlatFileTableParsingOptions = fileTableAttrs.FlatFileTableTypeParsingAttrs.Where(x => x.TypeToFormat == null).SelectMany(x => x.FileFieldParsingOptions).ToList() }; MereFileCache.TryAdd(type.TypeHandle, mereFile); return(mereFile); }
public static async Task <bool> ExecuteAsync <T>(T src, MereDataSource mereDataSource) where T : new() { var mereTable = MereUtils.CacheCheck <T>(); using (var myCn = MereUtils.GetConnection <T>(mereDataSource)) { await myCn.OpenAsync(); using (var tx = myCn.BeginTransaction()) { using (var cmd = myCn.CreateCommand()) { cmd.Transaction = tx; foreach (var sel in mereTable.SelectMereColumnsNoIdentity().Select((prop, index) => new { index, prop })) { var v = sel.prop.GetBase(src); if (v is DateTime && DateTime.Parse(v.ToString()).Year < 1753) { v = new DateTime(1753, 1, 1); } cmd.Parameters.AddWithValue("@" + sel.prop.ColumnName, v ?? DBNull.Value); // Changed .PropertyName to .ColumnName } var key = mereTable.SelectMereColumns.FirstOrDefault(w => w.IsKey); var identity = mereTable.SelectMereColumns.FirstOrDefault(w => w.IsIdentity); if (key != null) { var val = key.GetBase(src); cmd.Parameters.AddWithValue("@key", val); cmd.CommandText = mereTable.GetUpsertSqlWithKey(); if (identity != null) { var inserted = await cmd.ExecuteNonQueryAsync(); cmd.CommandText = "SELECT @@IDENTITY"; var reader = await cmd.ExecuteReaderAsync(); while (reader.Read()) { var id = reader[0]; if (id != DBNull.Value) { identity.SetBase(src, id); } } reader.Close(); tx.Commit(); return(inserted > 0); } } //defaults to insert if no key available cmd.CommandText = mereTable.SqlInsert; var inserted1 = await cmd.ExecuteNonQueryAsync(); tx.Commit(); if (inserted1 <= 0) { } return(inserted1 > 0); } } } }
//========================================================================================= public static bool MereBulkUpdate <T>( this IEnumerable <T> toUpdateEn, IEnumerable <String> keyFields, IEnumerable <String> setFields, int batchSize, bool truncateLength ) where T : new() { var toUpdateEnList = toUpdateEn.ToList(); if (toUpdateEnList.Count <= 0) { return(true); } var setFieldList = setFields.ToList(); if (setFieldList.Count <= 0) { return(true); } var keyFieldsList = keyFields.ToList(); if (keyFieldsList.Count <= 0) { return(true); } var mereTableMin = MereUtils.CacheCheck <T>(); var mereDataReader = new MereDataReader <T>(toUpdateEnList, truncateLength); using (var conn = MereUtils.GetConnection <T>()) { conn.Open(); using (var cpy = new SqlBulkCopy(conn)) { cpy.EnableStreaming = true; cpy.BulkCopyTimeout = 0; cpy.BatchSize = batchSize; //----------------------------------------------------------- var tempSetSb = new StringBuilder(); var identityColumns = new List <String>(); foreach (var column in mereTableMin.SelectMereColumns) { if (setFieldList.Contains(column.ColumnName) || keyFieldsList.Contains(column.ColumnName)) { if (column.IsIdentity) { identityColumns.Add("[" + column.ColumnName + "]"); } cpy.ColumnMappings.Add(column.ColumnName, column.ColumnName); tempSetSb.Append("[" + column.ColumnName + "],"); } } if (tempSetSb.Length > 0) { tempSetSb.Length--; } //----------------------------------------------------------- var tempTable = "##" + mereTableMin.TableName + "_" + Guid.NewGuid().ToString().Replace("-", "_"); var command = new SqlCommand("", conn); command.CommandText = String.Format( "Select top 0 {1} Into {2} from {3}{0}", "\r\n", tempSetSb, tempTable, mereTableMin.TableName); command.ExecuteNonQuery(); foreach (var identity in identityColumns) { command.CommandText = String.Format( "Alter Table {1}{0}Drop Column {2}{0}", "\r\n", tempTable, identity); command.ExecuteNonQuery(); command.CommandText = String.Format( "Alter Table {1}{0}Add {2} int{0}", "\r\n", tempTable, identity); command.ExecuteNonQuery(); } cpy.DestinationTableName = tempTable; cpy.WriteToServer(mereDataReader); //----------------------------------------------------------- var setSb = new StringBuilder(); setFieldList.ForEach(x => setSb.Append(String.Format( "t1.{1} = t2.{1},{0} ", "\r\n", x))); if (setSb.Length > 9) { setSb.Length = setSb.Length - 9; } var keySb = new StringBuilder(); keyFieldsList.ForEach(x => keySb.Append(String.Format( "t1.{1} = t2.{1}{0} AND ", "\r\n", x))); if (keySb.Length > 8) { keySb.Length = keySb.Length - 8; } //---------------------------------------------------------- command.CommandText = String.Format( "Update t1{0}" + " Set {3}{0}" + " From {1} t1{0}" + "Inner Join {2} t2{0}" + " On {4}", "\r\n", mereTableMin.TableName, tempTable, setSb, keySb); command.ExecuteNonQuery(); } } return(true); }