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