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 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 MereSqlDataReader(SqlDataReader reader, bool byName) { _sqlDataReader = reader; //_mereTable = MereUtils.CacheCheck<T>(); _selectMereColumnsList = MereUtils.CacheCheck <T>().SelectMereColumns.ToList(); _byName = byName; }
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 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 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 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 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> /// Gets date for previous instance of day. /// </summary> /// <param name="startDate">Date to start at.</param> /// <param name="moveTo">Day of the week to move to.</param> /// <returns></returns> public static DateTime MereGetPrevDayOfWeekDate(this DateTime startDate, string moveTo) { return(MereUtils.GetPrevDayOfWeek(startDate, moveTo)); }
/// <summary> /// Converts linq expression to the string version of selected property's name /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TProp"></typeparam> /// <param name="src"></param> /// <returns>Returns property's name as string</returns> public static string GetPropName <T, TProp>(this Expression <Func <T, TProp> > src) { var pinfo = MereUtils.GetProperty(src); return(pinfo.Name); }
// public static DateTime GetDayByOccurrenceInMonth(this DateTime startDate, DayOfWeek day, int occurrence) { return(MereUtils.GetDayByOccurrenceInMonth(startDate, day, occurrence)); }
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 DateTime GetLastDayOfLastMonth(this DateTime now) { return(MereUtils.GetLastDayOfLastMonth(now)); }
public static bool Execute <T>(T src) where T : new() { return(Execute(src, MereUtils.GetDataSource <T>())); }
public static Task <bool> ExecuteAsync <T>(T src) where T : new() { return(ExecuteAsync(src, MereUtils.GetDataSource <T>())); }
public static DateTime GetLastDayOfYear(this DateTime now) { return(MereUtils.GetLastDayOfYear(now)); }
public static DateTime GoToNextMonth(this DateTime now, int month) { return(MereUtils.GoToNextMonth(now, month)); }
/// <summary> /// Checks if int is in range created using provided start and end values /// </summary> /// <param name="toCheck"></param> /// <param name="start"></param> /// <param name="end"></param> /// <returns></returns> public static bool InRange(this int toCheck, int start, int end) { return(MereUtils.InRange(toCheck, start, end)); }
/// <summary> /// Gets date for next instance of day. /// </summary> /// <param name="startDate">Date to start at.</param> /// <param name="moveTo">Day of the week to move to.</param> /// <returns></returns> public static DateTime MereGetNextDayOfWeekDate(this DateTime startDate, DayOfWeek moveTo) { return(MereUtils.GetNextDayOfWeek(startDate, moveTo)); }
public static DateTime GetLastDayLastQuarter(this DateTime now) { return(MereUtils.GetLastDayLastQuarter(now)); }
/// <summary> /// Gets date for instance of day next week. /// </summary> /// <param name="startDate">Date to start at.</param> /// <param name="moveTo">String version of day of the week to move to.</param> /// <param name="weeksAway">How many weeks away to find date 0 = next instance</param> /// <returns></returns> public static DateTime MereGetNextWeeksDayOfWeekDate(this DateTime startDate, string moveTo, int weeksAway) { return(MereUtils.GetNextWeeksDayOfWeek(startDate, moveTo, weeksAway)); }
/// <summary> /// Gets date for instance of day next week. /// </summary> /// <param name="startDate">Date to start at.</param> /// <param name="moveTo">String version of day of the week to move to.</param> /// <returns></returns> public static DateTime MereGetPrevWeeksDayOfWeekDate(this DateTime startDate, DayOfWeek moveTo) { return(MereUtils.GetPrevWeeksDayOfWeekDate(startDate, moveTo, 1)); }