public static object LoadCoded788(string sCodeID, string JMBookingID, string Drug, string sJurisID)
{
IDataReader objReader = new TableDB().TableWhere(KPIHlp.CVI32(sJurisID), KPIHlp.CVI32(sCodeID), string.Empty, String.Empty, JMBookingID, Drug);
DataSet DSCoded = new DataSet();
DSCoded.Tables.Add();
DSCoded.Load(objReader, LoadOption.PreserveChanges, DSCoded.Tables[0]);
if (objReader != null && !objReader.IsClosed)
{
objReader.Close();
}
List <Coded> LstCoded = null;
if (DSCoded != null && DSCoded.Tables[0].Rows.Count > 0)
{
LstCoded = (from CodedRow in DSCoded.Tables[0].AsEnumerable().Cast <DataRow>()
select new Coded
{
CodeValue = Convert.ToString(CodedRow["CodeValue"]).Trim(),
Description = Convert.ToString(CodedRow["Description"])
}).ToList();
}
return((new System.Web.Script.Serialization.JavaScriptSerializer()).Serialize(LstCoded));
}
/// <summary>
/// Compare table to table
/// </summary>
private static TableDB ValidationByTable(TableDB tbl)
{
string tblName = tbl.TableName;
// validate header field
try
{
int bashSize = int.Parse(ConfigurationManager.AppSettings["BashSize"]);
for (int i = 0; i < Math.Ceiling((decimal)tbl.RecordCount / bashSize); i++)
{
var dataTableMySql = DBMySQLUtils.DataTableInMySql(tblName, i + 1, bashSize);
var dataTableSqlServer = DBSqlServerUtils.DataTableInSqlServer(tblName, i + 1, bashSize);
CompareTableMySqlAndSqlServer(dataTableMySql, dataTableSqlServer, tbl);
}
}
catch (SqlException e)
{
if (e.Number == 208)
{
tbl.Problems.Clear();
tbl.Problems.Add("Table is not found in Sql Server");
}
}
catch (Exception e)
{
//tbl.Problems.Add(e.Message);
}
return(tbl);
}
private void GetDB()
{
App.menuVM.GetDBMenus();
TableDB db = new TableDB();
App.tableList = db.GetTable();
}
private TableDB GetClassAttribute()
{
TableDB tableDefinifiton = (TableDB)Attribute.GetCustomAttribute(typeof(T), typeof(TableDB));
if (tableDefinifiton == null)
{
throw new Exceptions.DarkException(string.Format("The attribute was not found in the class '{0}'.", typeof(T).Name));
}
return(tableDefinifiton);
}
public bool Delete()
{
TableDB tableDefinifiton = GetClassAttribute();
if (tableDefinifiton.IsStoreProcedure)
{
return(ActionsObject(DbManagerTypes.Delete));
}
else
{
return(ActionsObjectCode(DbManagerTypes.Delete, tableDefinifiton));
}
}
private string GetRealNameClass()
{
string Nombre = "";
TableDB tableDefinifiton = GetClassAttribute();
if (tableDefinifiton.IsMappedByLabels)
{
Nombre = tableDefinifiton.Name;
}
else
{
Nombre = typeof(T).Name;
}
return(Nombre);
}
public static List <ListItem> GetDrugData(string BookingID, Int32 sJurisID)
{
DataSet objDs = new DataSet();
IDataReader objReader = new TableDB().TableWhere(sJurisID, (int)TableID.JMMDrug, string.Empty, String.Empty, BookingID);
List <ListItem> customers = new List <ListItem>();
while (objReader.Read())
{
customers.Add(new ListItem
{
Value = objReader["ID"].ToString(),
Text = objReader["Description"].ToString()
});
}
if (objReader != null && !objReader.IsClosed)
{
objReader.Close();
}
return(customers);
}
private bool ActionsObjectCode(DbManagerTypes dbManagerTypes, TableDB tableDefinifiton)
{
//TableDB tableDefinifiton = GetClassAttribute();
bool result = false;
//mapeo de tabla con los nombres de los campos ya existentes
string sentencia = "";
string sentenciaVariables = "";
foreach (var prop in typeof(T).GetProperties())
{
PropertyInfo propertyInfo = Element.GetType().GetProperty(prop.Name);
ColumnDB hiddenAttribute = (ColumnDB)propertyInfo.GetCustomAttribute(typeof(ColumnDB));
if (hiddenAttribute == null)
{
throw new Exceptions.DarkException(string.Format("The attribute was not found in the attribute '{0}', if you don´t want to use mapTable, please set IsMappedByLabels = false", prop.Name));
}
if (tableDefinifiton.IsMappedByLabels)
{
if (string.IsNullOrEmpty(hiddenAttribute.Name))
{
throw new Exceptions.DarkException(string.Format("The attribute {0} was setting like mapping column, the name is missing", prop.Name));
}
}
else
{
hiddenAttribute.Name = prop.Name;
}
if (dbManagerTypes == DbManagerTypes.Add)
{
if (!hiddenAttribute.IsKey && hiddenAttribute.IsMapped)
{
sentencia += hiddenAttribute.Name + ",";
sentenciaVariables += "@" + hiddenAttribute.Name + ",";
}
}
else if (dbManagerTypes == DbManagerTypes.Update)
{
if (!hiddenAttribute.IsKey && hiddenAttribute.IsMapped)
{
sentencia += hiddenAttribute.Name + " = @" + hiddenAttribute.Name + ",";
}
else if (hiddenAttribute.IsKey && hiddenAttribute.IsMapped)
{
sentenciaVariables = hiddenAttribute.Name + " = @" + hiddenAttribute.Name + "";
}
else
{
}
}
else if (dbManagerTypes == DbManagerTypes.Delete)
{
if (hiddenAttribute.IsKey)
{
sentenciaVariables = hiddenAttribute.Name + " = @" + hiddenAttribute.Name + "";
}
}
else
{
throw new Exceptions.DarkException(string.Format("Delete action is not active"));
}
}
if (dbManagerTypes == DbManagerTypes.Add)
{
string Statement = string.Format("INSERT INTO {0}({1}) VALUES({2})", Nametable, sentencia.Substring(0, sentencia.Length - 1), sentenciaVariables.Substring(0, sentenciaVariables.Length - 1));
List <ProcedureModel> procedureModels = new List <ProcedureModel>();
foreach (var prop in typeof(T).GetProperties())
{
PropertyInfo propertyInfo = Element.GetType().GetProperty(prop.Name);
ColumnDB hiddenAttribute = (ColumnDB)propertyInfo.GetCustomAttribute(typeof(ColumnDB));
if (!hiddenAttribute.IsKey && hiddenAttribute.IsMapped)
{
procedureModels.Add(new ProcedureModel {
Namefield = tableDefinifiton.IsMappedByLabels ? hiddenAttribute.Name : prop.Name, value = propertyInfo.GetValue(Element)
});
}
}
dBConnection.StartInsert(Statement, procedureModels);
result = true;
}
else if (dbManagerTypes == DbManagerTypes.Update)
{
string Statement = string.Format("UPDATE {0} SET {1} WHERE {2} ", Nametable, sentencia.Substring(0, sentencia.Length - 1), sentenciaVariables);
List <ProcedureModel> procedureModels = new List <ProcedureModel>();
foreach (var prop in typeof(T).GetProperties())
{
PropertyInfo propertyInfo = Element.GetType().GetProperty(prop.Name);
ColumnDB hiddenAttribute = (ColumnDB)propertyInfo.GetCustomAttribute(typeof(ColumnDB));
if (hiddenAttribute.IsMapped)
{
procedureModels.Add(new ProcedureModel {
Namefield = tableDefinifiton.IsMappedByLabels ? hiddenAttribute.Name : prop.Name, value = propertyInfo.GetValue(Element)
});
}
}
dBConnection.StartUpdate(Statement, procedureModels);
result = true;
}
else if (dbManagerTypes == DbManagerTypes.Delete)
{
string Statement = string.Format("DELETE FROM {0} WHERE {1} ", Nametable, sentenciaVariables);
List <ProcedureModel> procedureModels = new List <ProcedureModel>();
foreach (var prop in typeof(T).GetProperties())
{
PropertyInfo propertyInfo = Element.GetType().GetProperty(prop.Name);
ColumnDB hiddenAttribute = (ColumnDB)propertyInfo.GetCustomAttribute(typeof(ColumnDB));
if (hiddenAttribute.IsMapped && hiddenAttribute.IsKey)
{
procedureModels.Add(new ProcedureModel {
Namefield = tableDefinifiton.IsMappedByLabels ? hiddenAttribute.Name : prop.Name, value = propertyInfo.GetValue(Element)
});
}
}
dBConnection.StartDelete(Statement, procedureModels);
result = true;
}
else
{
throw new Exceptions.DarkException(string.Format("Delete action is not active"));
}
return(result);
}
private List <T> DataReader(string SqlStatements)
{
TableDB tableDefinifiton = GetClassAttribute();
System.Data.SqlClient.SqlDataReader Data = dBConnection.GetDataReader(SqlStatements);
List <T> Response = new List <T>();
while (Data.Read())
{
object exFormAsObj = Activator.CreateInstance(typeof(T));
foreach (var prop in typeof(T).GetProperties())
{
PropertyInfo propertyInfo = exFormAsObj.GetType().GetProperty(prop.Name);
ColumnDB hiddenAttribute = (ColumnDB)propertyInfo.GetCustomAttribute(typeof(ColumnDB));
if (hiddenAttribute == null)
{
throw new Exceptions.DarkException(string.Format("The attribute was not found in the attribute '{0}', if you don´t want to use mapTable, please set IsMappedByLabels = false", prop.Name));
}
if (hiddenAttribute.IsMapped)
{
string NombrePropiedad = "";
if (tableDefinifiton.IsMappedByLabels)
{
NombrePropiedad = hiddenAttribute.Name.Trim();
}
else
{
NombrePropiedad = prop.Name;
}
try
{
if (prop.PropertyType.Equals(typeof(DateTime)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? DateTime.Now : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ChangeType(value, TypeCode.DateTime), null);
}
if (prop.PropertyType.Equals(typeof(DateTime?)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? null : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, value, null);
}
if (prop.PropertyType.Equals(typeof(TimeSpan)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? null : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, value, null);
}
if (prop.PropertyType.Equals(typeof(double)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? 0 : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ChangeType(value, TypeCode.Double), null);
}
if (prop.PropertyType.Equals(typeof(float)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? 0 : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ToSingle(value), null);
}
if (prop.PropertyType.Equals(typeof(Decimal)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? 0 : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ChangeType(value, TypeCode.Double), null);
}
if (prop.PropertyType.Equals(typeof(string)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? "" : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ChangeType(value, TypeCode.String), null);
}
if (prop.PropertyType.Equals(typeof(bool)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? false : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ChangeType(value, TypeCode.Boolean), null);
}
if (prop.PropertyType.Equals(typeof(int)))
{
var value = Data.GetValue(Data.GetOrdinal(NombrePropiedad)) is System.DBNull ? 0 : Data.GetValue(Data.GetOrdinal(NombrePropiedad));
propertyInfo.SetValue(exFormAsObj, Convert.ChangeType(value, propertyInfo.PropertyType), null);
}
}
catch (Exception ex)
{
throw new Exceptions.DarkException(string.Format("The attribute'{0}' has an error, {1}", prop.Name, ex.Message));
}
}
}
Response.Add((T)exFormAsObj);
}
Data.Close();
return(Response);
}
/// <summary>
/// Validate table data
/// </summary>
/// <param name="mySqlDatatable"></param>
/// <param name="sqlServerDatatable"></param>
/// <param name="tbl"></param>
/// <returns></returns>
private static bool ValidateTableData(DataTable mySqlDatatable, DataTable sqlServerDatatable, TableDB tbl)
{
var isOk = true;
// Compare data type
for (int i = 0; i < mySqlDatatable.Columns.Count; i++)
{
try
{
var mySqlCellVal = mySqlDatatable.Columns[i];
var sqlServerCellVal = sqlServerDatatable.Columns[i];
if (mySqlCellVal.DataType != sqlServerCellVal.DataType)
{
throw new ArrayTypeMismatchException(mySqlDatatable.Columns[i].ColumnName);
}
}
catch (Exception e)
{
if (e is ArrayTypeMismatchException)
{
tbl.Problems.Add($"Field {e.Message} type not match");
}
tbl.Problems = tbl.Problems.Distinct().ToList();
isOk = false;
}
}
if (mySqlDatatable.Rows.Count > sqlServerDatatable.Rows.Count)
{
tbl.Problems.Add($"Missing {mySqlDatatable.Rows.Count - sqlServerDatatable.Rows.Count} records");
return(false);
}
//foreach (DataRow row in mySqlDatatable.Rows)
for (int i = 0; i < mySqlDatatable.Rows.Count; i++)
{
try
{
foreach (DataColumn col in mySqlDatatable.Columns)
{
var mySqlCellVal = mySqlDatatable.Rows[i][col.ColumnName];
var sqlServerCellVal = sqlServerDatatable.Rows[i][col.ColumnName];
if (mySqlCellVal.ToString() != sqlServerCellVal.ToString())
{
throw new InvalidCompareException();
}
}
}
catch (Exception e)
{
if (e is InvalidCompareException)
{
tbl.Problems.Add($"Data is not matching");
}
tbl.Problems = tbl.Problems.Distinct().ToList();
isOk = false;
}
}
return(isOk);
}
private static bool ValidateTableHeader(DataTable mySqlDatatable, DataTable sqlServerDatatable, TableDB tbl)
{
var isOk = true;
var differenceheaders = new List <string>();
foreach (DataColumn col in mySqlDatatable.Columns)
{
bool isHeaderNameNotEqual = sqlServerDatatable.Columns.Contains(col.ToString());
if (!isHeaderNameNotEqual)
{
differenceheaders.Add(col.ToString());
isOk = false;
}
}
if (differenceheaders.Any())
{
tbl.Problems.Add($"Missing table header: {string.Join(",", differenceheaders.Select(row => row))}");
}
return(isOk);
}
/// <summary>
/// Read excel file -> compare with sql server. Using paging for each table query
/// </summary>
/// <param name="tableDataCsvPath">Csv table data file path</param>
/// <param name="tableMetadataCsvPath">Csv table metadata file path</param>
/// <param name="tbl">Table to compare</param>
/// <returns></returns>
private static bool ValidDataFromCsvAndSqlServer(string tableDataCsvPath, string tableMetadataCsvPath, TableDB tbl)
{
int bashSize = int.Parse(ConfigurationManager.AppSettings["BashSize"]);
DataTable csvData = new DataTable();
//read column names
List <string> colNames = new List <string>();
//read column data type
List <string> colDataTypes = new List <string>();
using (var tblMetadataCsvReader = new TextFieldParser(tableMetadataCsvPath, Encoding.Default, true))
{
tblMetadataCsvReader.SetDelimiters(new string[] { ";" });
tblMetadataCsvReader.HasFieldsEnclosedInQuotes = true;
tblMetadataCsvReader.TrimWhiteSpace = false;
while (!tblMetadataCsvReader.EndOfData)
{
List <string> cellData = tblMetadataCsvReader.ReadFields().ToList();
if (cellData.Count() < 2)
{
tbl.Problems.Add($"Table {tbl.TableName} metadata corrupted");
return(false);
}
colNames.Add(cellData[0]);
colDataTypes.Add(cellData[1]);
}
}
//read row data
using (var csvReader = new TextFieldParser(tableDataCsvPath, Encoding.Default, true))
{
csvReader.SetDelimiters(new string[] { ";" });
csvReader.HasFieldsEnclosedInQuotes = true;
csvReader.TrimWhiteSpace = false;
// Set column datatype
for (int i = 0; i < colNames.Count(); i++)
{
Type colType = typeof(string);
if (listInt.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
colType = typeof(int);
}
else if (listDecimal.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
colType = typeof(decimal);
}
else if (listDate.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
colType = typeof(DateTime);
}
DataColumn datecolumn = new DataColumn(colNames[i], colType)
{
AllowDBNull = true
};
csvData.Columns.Add(datecolumn);
}
int page = 1;
int csvRow = 0;
int sqlServerRow = 0;
// Set row data
while (!csvReader.EndOfData)
{
csvRow++;
string[] cellData = csvReader.ReadFields();
object[] cellDataParsed = new object[cellData.Length];
//Making empty value as null
for (int i = 0; i < cellData.Length; i++)
{
if (cellData[i] == "")
{
cellDataParsed[i] = null;
}
else if (listInt.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
if (int.TryParse(cellData[i], out int parsed))
{
cellDataParsed[i] = parsed;
}
}
else if (listDecimal.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
if (decimal.TryParse(cellData[i], NumberStyles.Any, new NumberFormatInfo()
{
NumberDecimalSeparator = "."
}, out decimal parsed))
{
cellDataParsed[i] = parsed;
}
}
else if (listDate.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
if (DateTime.TryParse(cellData[i], out DateTime parsed))
{
cellDataParsed[i] = parsed;
}
}
else if (listStr.Contains(colDataTypes[i], StringComparer.OrdinalIgnoreCase))
{
cellDataParsed[i] = cellData[i];
}
}
csvData.Rows.Add(cellDataParsed);
// Compare with sql server
if (csvData.Rows.Count == bashSize || csvReader.EndOfData)
{
DataTable DBServer = DBSqlServerUtils.GetDataTablePaging(tbl.TableName, colNames.ToArray(), page, bashSize);
sqlServerRow += DBServer.Rows.Count;
CompareTableMySqlAndSqlServer(csvData, DBServer, tbl);
csvData.Rows.Clear();
page++;
}
}
if (csvRow > sqlServerRow)
{
//tbl.Problems.Add($"Missing {csvRow - sqlServerRow} records");
csvData.Rows.Clear();
return(false);
}
else if (csvRow <= 0)
{
DataTable DBServer = DBSqlServerUtils.GetDataTablePaging(tbl.TableName, colNames.ToArray(), page, bashSize);
CompareTableMySqlAndSqlServer(csvData, DBServer, tbl);
csvData.Rows.Clear();
}
}
return(true);
}
/// <summary>
/// Compare datatable mysql and tabledata sqlserver
/// </summary>
/// <param name="mySqlDatatable"></param>
/// <param name="sqlServerDatatable"></param>
/// <param name="tbl">Table to compare</param>
/// <returns></returns>
private static bool CompareTableMySqlAndSqlServer(DataTable mySqlDatatable, DataTable sqlServerDatatable, TableDB tbl)
{
var dataset1 = ConvertValueType(mySqlDatatable);
var dataset2 = ConvertValueType(sqlServerDatatable);
var isOk = ValidateTableHeader(dataset1, dataset2, tbl);
if (!isOk)
{
return(isOk);
}
isOk = ValidateTableData(dataset1, dataset2, tbl);
return(isOk);
}