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