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);
        }
Exemple #3
0
        private void GetDB()
        {
            App.menuVM.GetDBMenus();

            TableDB db = new TableDB();

            App.tableList = db.GetTable();
        }
Exemple #4
0
        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);
        }
Exemple #5
0
        public bool Delete()
        {
            TableDB tableDefinifiton = GetClassAttribute();

            if (tableDefinifiton.IsStoreProcedure)
            {
                return(ActionsObject(DbManagerTypes.Delete));
            }
            else
            {
                return(ActionsObjectCode(DbManagerTypes.Delete, tableDefinifiton));
            }
        }
Exemple #6
0
        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);
        }
Exemple #8
0
        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);
        }
Exemple #9
0
        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);
        }