public JToken GetEmployees(Employee searchOptions) { var dataSet = this._employeeContext.Get(searchOptions); DataTable dataTable; var mapper = new DataNamesMapper <Employee>(); if (dataSet.Tables.Count > 0) { dataTable = dataSet.Tables[0]; return(JToken.FromObject(mapper.Map(dataTable))); } else { return(JToken.FromObject("Không bản ghi nào!")); } }
public JToken GetSellers(int sellerID, string sellerName) { var dataSet = this._sellerContext.Get(sellerID, sellerName); DataTable dataTable; var mapper = new DataNamesMapper <Seller>(); if (dataSet.Tables.Count > 0) { dataTable = dataSet.Tables[0]; return(JToken.FromObject(mapper.Map(dataTable))); } else { return(JToken.FromObject("Không bản ghi nào!")); } }
public List <AddressModel> GetAllAddresses() { //Store raw query results to data table DataTable dt = new DataTable(); //SQLite stuff SQLiteCommand comm = null; SQLiteConnection connection = null; string query = "SELECT * FROM ADDRESS;"; //useable Data after conversion to be returned List <AddressModel> addressList = new List <AddressModel>(); try { connection = SQLiteHelper.OpenConn(); comm = new SQLiteCommand(query, connection); //Execute the command and load data to table SQLiteDataReader reader = comm.ExecuteReader(); dt.Load(reader); //Closes reader stream then connection reader.Close(); SQLiteHelper.CloseConn(); //Use Datamapper to map selected results to objects DataNamesMapper <AddressModel> mapper = new DataNamesMapper <AddressModel>(); addressList = mapper.Map(dt).ToList(); } catch (Exception e) { Console.WriteLine(e.Message); SQLiteHelper.CloseConn(); MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(addressList); }
static void Main(string[] args) { var priestsDataSet = DataSetGenerator.Priests(); DataNamesMapper <Person> mapper = new DataNamesMapper <Person>(); List <Person> persons = mapper.Map(priestsDataSet.Tables[0]).ToList(); var ranchersDataSet = DataSetGenerator.Ranchers(); persons.AddRange(mapper.Map(ranchersDataSet.Tables[0])); foreach (var person in persons) { Console.WriteLine("First Name: " + person.FirstName + ", Last Name: " + person.LastName + ", Date of Birth: " + person.DateOfBirth.ToShortDateString() + ", Job Title: " + person.JobTitle + ", Nickname: " + person.TakenName + ", Is American: " + person.IsAmerican); } Console.ReadLine(); }
public async Task <List <DepartamentoDTO> > Obtener(int?id = 0) { try { this._sQLCmdConfig.spName = "ObtenerDepartamento"; this._sQLCmdConfig.sqlParameter = this.ObtenerParametros(id); DataTable tabla = await new DBOperation(this._sQLCmdConfig).getValue(); DataNamesMapper <DepartamentoDTO> mapper = new DataNamesMapper <DepartamentoDTO>(); var response = mapper.Map(tabla).ToList(); return(response); } catch (Exception) { throw; } }
public ConfigParamProduct[] GetProductParams(string idProducto, out string codRetorno) { string mensaje; DataSet data = null; ConfigParamProduct[] resp = null; try { data = _ContratacionProducto.ConsultaParametrosProducto(idProducto, out codRetorno, out mensaje); } catch (Exception) { codRetorno = "503"; mensaje = ""; } if (data != null) { DataNamesMapper <ConfigParamProduct> mapperToConfigParam = new DataNamesMapper <ConfigParamProduct>(); resp = mapperToConfigParam.Map(data.Tables["Table"]).ToArray(); } return(resp); }
public EstadoCivilClienteDto ConsultaEstadoCivilCliente(string cedula) { string codigoRetorno, mensajeRetorno; DataSet resp = null; try { resp = _ContratacionProducto.ConsultaInformacionCliente(cedula, out codigoRetorno, out mensajeRetorno); } catch (Exception) { return(new EstadoCivilClienteDto { codError = "503" }); } if (codigoRetorno == "000") { DataNamesMapper <DatosEstCivilCliente> mapperToEstCivil = new DataNamesMapper <DatosEstCivilCliente>(); DatosEstCivilCliente cliente = mapperToEstCivil.Map(resp.Tables["DatosBasicosCliente"].Rows[0]); var estCivil = Mapper.Map <EstadoCivilClienteDto>(cliente); DatosEstCivilCYG cony = null; if (resp.Tables["DatosBasicosConyugeCliente"].Rows.Count > 0) { DataNamesMapper <DatosEstCivilCYG> mapperToCYG = new DataNamesMapper <DatosEstCivilCYG>(); cony = mapperToCYG.Map(resp.Tables["DatosBasicosConyugeCliente"].Rows[0]); Mapper.Map(cony, estCivil); } estCivil.codError = "200"; return(estCivil); } return(new EstadoCivilClienteDto { codError = "500" }); }
public JToken GetInvoicesJToken(int invoiceID, string company) { Invoice searchOptions = new Invoice { ID = 1, Company = company }; var dataSet = this._invoiceContext.Get(searchOptions); DataTable dataTable; var mapper = new DataNamesMapper <Invoice>(); if (dataSet.Tables.Count > 0) { dataTable = dataSet.Tables[0]; return(JToken.FromObject(mapper.Map(dataTable))); } else { return(JToken.FromObject("Không bản ghi nào!")); } }
public IEnumerable <TEntity> GetResultMappingNotAsync(CommandType CommandType, MySqlParameter[] sqlCommand, string CommandText) { try { DataSet ds = CreateDataSet(1); DataTable[] tables = new DataTable[ds.Tables.Count]; ds.Tables.CopyTo(tables, 0); Context.Database.OpenConnection(); using (DbCommand dbCommand = Context.Database.GetDbConnection().CreateCommand()) { dbCommand.CommandType = CommandType; dbCommand.CommandText = CommandText; if (sqlCommand != null) { dbCommand.Parameters.AddRange(sqlCommand); } if (dbCommand.Connection.State != ConnectionState.Open) { dbCommand.Connection.Open(); } using (DbDataReader reader = dbCommand.ExecuteReader()) { ds.Load(reader, LoadOption.OverwriteChanges, tables); } } Context.Database.CloseConnection(); var mapper = new DataNamesMapper <TEntity>(); return(mapper.Map(ds.Tables[0])); } catch (Exception ex) { return(null); } }
public async Task <DepartamentoDTO> Guardar(DepartamentoDTO entidad) { try { this._sQLCmdConfig.spName = "GuardarDepto"; this._sQLCmdConfig.sqlParameter = new List <SqlParameter>() { new SqlParameter("@idDepartamento", entidad.idDepartamento), new SqlParameter("@departamento", entidad.departamento) }; DataTable tabla = await new DBOperation(this._sQLCmdConfig).getValue(); DataNamesMapper <DepartamentoDTO> mapper = new DataNamesMapper <DepartamentoDTO>(); var response = mapper.Map(tabla).ToList().FirstOrDefault(); return(response); } catch (Exception) { throw; } }
static void Main(string[] args) { try { GlobalApp.oLog = new LOGFiles(GlobalApp.FolderLog.Trim(), "TestConsole", "Program.cs", "Main", "TestConsole", AssemblyInfo.Company.Trim()); GlobalApp.DetailLog = new List <DetailLOG>(); GlobalApp.iCount = 1; GlobalApp.Numero = 0; GlobalApp.Mensaje = string.Empty; GlobalApp.FechaActual = DateTime.Now; _oTime0 = DateTime.Now; if (Directory.Exists(GlobalApp.FolderLog.Trim()) == false) { Directory.CreateDirectory(GlobalApp.FolderLog.Trim()); } // Carpeta de los archivos LOG de la aplicación. if (Directory.Exists(GlobalApp.FolderTemporal.Trim()) == false) { Directory.CreateDirectory(GlobalApp.FolderTemporal.Trim()); } // Carpeta de archivo de reportes finales. if (Directory.Exists(GlobalApp.FolderLayOut.Trim()) == false) { Directory.CreateDirectory(GlobalApp.FolderLayOut.Trim()); } // Carpeta de archivo de layout. Console.WriteLine("Consola de aplicación en .NET Core.\nVersión " + AssemblyInfo.Version.ToString()); Console.WriteLine("México " + DateTime.Now.Year.ToString() + ".\n"); Console.WriteLine("Fecha de inicio: " + _oTime0.ToString()); GlobalApp.DetailLog.Add(new DetailLOG() { Id = GlobalApp.iCount++, Fecha = DateTime.Now.ToString("yyyy'/'MM'/'dd' 'hh':'mm':'ss'.'fff' 'tt"), TipoEvento = TipoInformacion.Informacion, Numero = 0, Comentario = "Fecha de inicio: " + _oTime0.ToString() }); // Inicializamos las variables. InitVars(); Console.WriteLine("Fecha universal: {0}", Tool.ToDateUniversal(DateTime.Now)); var _ValoraCifrar = "Que_Chingue_A_Su_Mother_AMLO_Y_EL_AMERICA"; var _strNewGUID = Guid.NewGuid().ToString(); Console.WriteLine("Valor a cifrar: {0}. Valor cifrado: {1}.", _ValoraCifrar, RijndaelManagedEncryption.EncryptRijndael(_ValoraCifrar, _strNewGUID)); Console.WriteLine("Valor desencriptado: {0}.", RijndaelManagedEncryption.DecryptRijndael(RijndaelManagedEncryption.EncryptRijndael(_ValoraCifrar, _strNewGUID), _strNewGUID)); // Mapeo de DataTables. var priestsDataSet = DataSetGenerator.Priests(); DataNamesMapper <Person> mapper = new DataNamesMapper <Person>(); List <Person> persons = mapper.Map(priestsDataSet.Tables[0]).ToList(); var ranchersDataSet = DataSetGenerator.Ranchers(); persons.AddRange(mapper.Map(ranchersDataSet.Tables[0])); foreach (var person in persons) { Console.WriteLine("First Name: " + person.FirstName + ", Last Name: " + person.LastName + ", Date of Birth: " + person.DateOfBirth.ToShortDateString() + ", Job Title: " + person.JobTitle + ", Nickname: " + person.TakenName + ", Is American: " + person.IsAmerican); } // Cargando el archivo de configuración de la aplicación 'appsettings.json'. var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true); IConfigurationRoot configuration = builder.Build(); // Leemos algunos de sus valores de la configuración,. string dbConn = configuration.GetSection("ConnectionStrings").GetSection("SQLServerConnectionBD").Value; string dbConn2 = configuration.GetSection("ConnectionStrings").GetSection("MariaDBConnectionBD").Value; // Limpiamos esta variable. configuration = null; Console.WriteLine("Cadena de conexión a Microsoft SQL Server: {0}", dbConn); Console.WriteLine("Cadena de conexión a MariaDB/MySQL Server: {0}", dbConn2); Console.WriteLine("Fecha universal: {0}", Tool.ToDateUniversal(DateTime.Now)); var strDato = "CapMax = ((DepositoConceptoJubilacionPension - CargosDomiciliados - CargosConceptoCreditos) * 0.4);nnCapMax=n((DepositoConceptoJubilacionPension - CargosDomiciliados - CargosConceptoCreditos) - CapMax) n> (NúmeroCasasComerciales < 2 ? 400 : 800)? CapMax : (NúmeroCasasComerciales < 2 ? 400 : 800);nnCapMax = CapMax > 0 ? CapMax : 0;tnnCapMax = NúmeroCasasComerciales >= 3 ? 0 : CapMax;"; Console.WriteLine("Dato anterior: {0}", strDato); strDato = Regex.Replace(strDato, Patrones.PatronAlphaLatino.Trim(), string.Empty); Console.WriteLine("Dato nuevo: {0}", strDato); var ArchivoReporteStr = string.Empty; // Uso de DBFactory. // El primer parametro es el nombre de la conexión a Base de Datos, según en el archivo AppSettings.json. // El segundo parametro es el tipo de conexión por plataforma de Base de Datos. Console.WriteLine("Haciendo una consulta SQL a Base de Datos (AWS Redshift via ODBC...)"); using (var oDb = new DBManager("AWSConnectionBD", DataBaseProviders.Odbc)) { // Carga de parametros. // var _oParam = new List<IDbDataParameter>(); // _oParam.Add(oDb.CreateParameter("@Id", valor1, System.Data.DBType.String)) // _oParam.Add(oDb.CreateParameter("@Id2", valor2, System.Data.DBType.String)) // var oDt = oDb.GetDataToDataTable("SQL_Command_Strng", System.Data.CommandType.Text, _oParam.ToArray()); var oDt = oDb.GetDataToDataTable("SELECT * FROM public.\"schema-convdatosgenerales-keops\" t1;", System.Data.CommandType.Text, null); Console.WriteLine("Consulta ejecutada correctamente. Total de registros: {0}.", oDt.Rows.Count); // Exportar un query de Base de Datos directo a archivo de texto plano. Console.WriteLine("Generando reporte de bases de datos..."); ArchivoReporteStr = Path.Combine(GlobalApp.FolderTemporal.Trim(), string.Format("{0}.txt", Guid.NewGuid().ToString())); oDb.ExportData(ArchivoReporteStr, "|", "SELECT * FROM public.\"schema-convdatosgenerales-keops\" t1;", System.Data.CommandType.Text, null); Console.WriteLine("El reporte se ha generado correctamente en {0}", ArchivoReporteStr.Trim()); } // Fin de la conexión de AWS Redshift via ODBC. Console.WriteLine("Haciendo una consulta SQL a Base de Datos (SQL Server Azure...)"); using (var oDb = new DBManager("SQLServerConnectionBD", DataBaseProviders.SQLServer)) { var oDt = oDb.GetDataToDataTable("SELECT * FROM products t1;", System.Data.CommandType.Text, null); Console.WriteLine("Consulta ejecutada correctamente. Total de registros: {0}.", oDt.Rows.Count); Console.WriteLine("Generando reporte de bases de datos..."); ArchivoReporteStr = Path.Combine(GlobalApp.FolderTemporal.Trim(), string.Format("{0}.csv", Guid.NewGuid().ToString())); oDb.ExportData(ArchivoReporteStr, ",", "SELECT * FROM products t1;", System.Data.CommandType.Text, null); Console.WriteLine("El reporte se ha generado correctamente en {0}", ArchivoReporteStr.Trim()); } // Fin de la conexión para SQL Server Azure. Console.WriteLine("Haciendo una consulta SQL a Base de Datos (MySQL Server/MariaDB Server...)"); using (var oDb = new DBManager("MariaDBConnectionBD", DataBaseProviders.MySQLServer)) { _oSb.Clear().AppendFormat("SHOW DATABASES;"); var s = oDb.GetDataToMapping <ListaBD>(_oSb.ToString(), System.Data.CommandType.Text, null); Console.WriteLine("Consulta ejecutada correctamente. Total de registros: {0}.", s.Count); // Recorremos la lista. Console.WriteLine("Leyendo la lista de Bases de Datos."); foreach (var u in s) { Console.WriteLine("{0}", u.Database.Trim()); } // Guardamos la lista de bases de datos en un archivo CSV. Console.WriteLine("Generando reporte de bases de datos..."); ArchivoReporteStr = Path.Combine(GlobalApp.FolderTemporal.Trim(), string.Format("{0}.dat", Guid.NewGuid().ToString())); oDb.ExportData(ArchivoReporteStr, "|", _oSb.ToString(), System.Data.CommandType.Text, null); Console.WriteLine("El reporte se ha generado correctamente en {0}", ArchivoReporteStr.Trim()); // Exportar directamente a Google Drive. Console.WriteLine("Generando reporte de bases de datos para Google Drive..."); var UrlSourceDrive = string.Empty; var IdKeyGoogleDrive = string.Empty; oDb.ExportDataToGoogleSheetsInFolderWithPermissions(ArchivoReporteStr.Replace(".dat", ".csv"), ",", "ClientId", "SecretId", GlobalApp.FolderPersonal.Trim(), "AplicacionGoogleAPI", "Identificador_Google_Drive", "*****@*****.**", GoogleDrivePermissions.Reader, GoogleDriveGroups.User, false, false, true, out UrlSourceDrive, out IdKeyGoogleDrive, _oSb.ToString(), System.Data.CommandType.Text, null); Console.WriteLine("Identificadores de Google Drive: {0} y {1}", UrlSourceDrive, IdKeyGoogleDrive); } // Fin de la conexión para MySQL Server/ MariaDB. _oSb = null; } catch (Exception oEx) { GlobalApp.Numero = 100; GlobalApp.Mensaje = string.Concat(((oEx.InnerException == null) ? oEx.Message.Trim() : oEx.InnerException.Message.ToString())); GlobalApp.DetailLog.Add(new DetailLOG() { Id = GlobalApp.iCount++, Fecha = DateTime.Now.ToString("yyyy'/'MM'/'dd' 'hh':'mm':'ss'.'fff' 'tt"), TipoEvento = TipoInformacion.ErrorProceso, Numero = GlobalApp.Numero, Comentario = GlobalApp.Mensaje }); Console.WriteLine("Ocurrieron errores al ejecutar este proceso: " + GlobalApp.Mensaje.Trim() + ". Seguimiento de pila: " + oEx.StackTrace.Trim()); } finally { // Limpiamos variables. _oTime1 = DateTime.Now; _oTimeTotal = new TimeSpan(_oTime1.Ticks - _oTime0.Ticks); DestroyVars(); // Obtengo la fecha de termino. GlobalApp.DetailLog.Add(new DetailLOG() { Id = GlobalApp.iCount++, Fecha = DateTime.Now.ToString("yyyy'/'MM'/'dd' 'hh':'mm':'ss'.'fff' 'tt"), TipoEvento = TipoInformacion.Informacion, Numero = 0, Comentario = "Fecha de termino: " + _oTime1.ToString() }); Console.WriteLine("Fecha de termino: " + _oTime1.ToString()); // Obtengo el tiempo transcurrido. GlobalApp.DetailLog.Add(new DetailLOG() { Id = GlobalApp.iCount++, Fecha = DateTime.Now.ToString("yyyy'/'MM'/'dd' 'hh':'mm':'ss'.'fff' 'tt"), TipoEvento = TipoInformacion.Informacion, Numero = 0, Comentario = "Tiempo transcurrido en ejecutarse este proceso: " + _oTimeTotal.ToString() }); Console.WriteLine("Tiempo transcurrido en ejecutarse este proceso: " + _oTimeTotal.ToString()); // Guardamos los mensajes en el log y limpiamos las variables. GlobalApp.oLog.ListEvents = GlobalApp.DetailLog; XMLSerializacion <LOGFiles> .WriteToXmlFile(Path.Combine(GlobalApp.FolderLog, string.Concat("LOGTestConsole_", DateTime.Now.ToString("yyyy''MM''dd''hh''mm''ss''fff"), ".xml")), GlobalApp.oLog, false); GlobalApp.DetailLog = null; GlobalApp.oLog = null; Console.WriteLine("Pulse cualquier tecla para salir..."); Console.ReadLine(); } }
//Get Assembly by Data public List <AssemblyGroupModel> GetAssemblyByData(string searchFor, AssemblySearch columnName) { //Store raw query results to data table DataTable dt = new DataTable(); //SQLite stuff SQLiteCommand comm = null; SQLiteConnection connection = null; string query = String.Format("SELECT * FROM {0} WHERE {0}.@C LIKE '%@param%';", TableName); //useable Data after conversion to be returned List <AssemblyGroupModel> assemblyList = new List <AssemblyGroupModel>(); try { connection = SQLiteHelper.OpenConn(); //comm = new SQLiteCommand(query, connection); switch (columnName) { case AssemblySearch.ID: query = query.Replace("@C", "AssemblyId"); comm = new SQLiteCommand(query, connection); int idRes; if (!int.TryParse(searchFor, out idRes)) { throw new Exception("Assembly ID search is restricted to numbers"); } comm.Parameters.Add("@param", DbType.Int32).Value = idRes; break; case AssemblySearch.PARTSID: query = query.Replace("@C", "PartsId"); comm = new SQLiteCommand(query, connection); int partIdRes; if (!int.TryParse(searchFor, out partIdRes)) { throw new Exception("Parts ID search is restricted to numbers"); } comm.Parameters.Add("@param", DbType.Int32).Value = partIdRes; break; case AssemblySearch.QUANTITY: query = query.Replace("@C", "PartsQuantity"); comm = new SQLiteCommand(query, connection); int quantity; if (!int.TryParse(searchFor, out quantity)) { throw new Exception("Parts ID search is restricted to numbers"); } comm.Parameters.Add("@param", DbType.Int32).Value = quantity; break; } //Execute the command and load data to table SQLiteDataReader reader = comm.ExecuteReader(); dt.Load(reader); //Closes reader stream then connection reader.Close(); SQLiteHelper.CloseConn(); //Use Datamapper to map selected results to objects DataNamesMapper <AssemblyGroupModel> mapper = new DataNamesMapper <AssemblyGroupModel>(); assemblyList = mapper.Map(dt).ToList(); } catch (Exception e) { Console.WriteLine(e.Message); SQLiteHelper.CloseConn(); MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(assemblyList); }
public DatosForSulicitudViewDto GetDatosInicio(ref DatosCliente datosCliente) { var cedula = datosCliente.Cedula; string codigoRetorno = "000", mensajeRetorno = ""; DataNamesMapper <DatosCliente> mapperToclient = new DataNamesMapper <DatosCliente>(); DataNamesMapper <CuentasSobreGiro> mapperToCtas = new DataNamesMapper <CuentasSobreGiro>(); DataNamesMapper <Holidays> mapperToHolidays = new DataNamesMapper <Holidays>(); DatosForSulicitudViewDto data = new DatosForSulicitudViewDto(); //obterner informacion del cliente DataSet infoCliente = null; try { infoCliente = _ContratacionProducto.ConsultaInformacionClienteCore("C", cedula, out codigoRetorno, out mensajeRetorno); } catch (Exception e) { data.codigoRetorno = "503"; data.mensajeRetorno = "Servicio \"ConsultaInformacionClienteCore\" no disponible. mas detalles: " + e.Message; return(data); } if (infoCliente.Tables.Contains("DatosBasicosCliente") && codigoRetorno == "000") { datosCliente = mapperToclient.Map(infoCliente.Tables["DatosBasicosCliente"].Rows[0]); datosCliente.Cedula = cedula; } else { data.codigoRetorno = "500"; data.mensajeRetorno = "Algo salió mal, Servicio \"ConsultaInformacionClienteCore\" no retornó informacion. mas detalles: " + mensajeRetorno; return(data); } //obtener cuentas para sobregiro del cliente DataSet infoCtas = null; try { infoCtas = _ContratacionProducto.ObtenerCuentasCCSobregiro("C", cedula, out codigoRetorno, out mensajeRetorno); } catch (Exception e) { data.codigoRetorno = "503"; data.mensajeRetorno = "Servicio \"ObtenerCuentasCCSobregiro\" no disponible. mas detalles: " + e.Message; return(data); } if (infoCtas.Tables.Contains("CUENTASOBREGIRO") && infoCtas.Tables["CUENTASOBREGIRO"].Rows.Count > 0 && codigoRetorno == "000") { data.Cuentas = mapperToCtas.Map(infoCtas.Tables["CUENTASOBREGIRO"]).ToArray(); } else { data.codigoRetorno = "500"; data.mensajeRetorno = "Algo salió mal, Servicio \"ObtenerCuentasCCSobregiro\" no retornó informacion. mas detalles: " + mensajeRetorno; return(data); } //obtener feriados DataSet infoDias = null; try { infoDias = _ContratacionProducto.ObtenerDiasFeriado("1", out codigoRetorno, out mensajeRetorno); } catch (Exception e) { data.codigoRetorno = "503"; data.mensajeRetorno = "Servicio \"ObtenerDiasFeriado\" no disponible. mas detalles: " + e.Message; return(data); } if (infoDias.Tables.Contains("DiasFeriados") && infoDias.Tables["DiasFeriados"].Rows.Count > 0 && codigoRetorno == "000") { data.Holidays = mapperToHolidays.Map(infoDias.Tables["DiasFeriados"]).ToArray(); } return(data); }
//Get customer by Data public List <CustomerModel> GetCustomerByData(string searchFor, CustomerSearch columnName) { //Store raw query results to data table DataTable dt = new DataTable(); //SQLite stuff SQLiteCommand comm = null; SQLiteConnection connection = null; string query = String.Format("SELECT * FROM {0} WHERE {0}.@C LIKE '%'||@param||'%';", TableName); //NEW FIX USE THIS CONCAT //useable Data after conversion to be returned List <CustomerModel> customerList = new List <CustomerModel>(); try { connection = SQLiteHelper.OpenConn(); //comm = new SQLiteCommand(query, connection); switch (columnName) { case CustomerSearch.ID: query = query.Replace("@C", "CustomerId"); comm = new SQLiteCommand(query, connection); int idRes; if (!int.TryParse(searchFor, out idRes)) { throw new Exception("Customer ID search is restricted to numbers"); } SQLiteParameter param = new SQLiteParameter("@param", DbType.Int32); //NEW FIX param.Value = idRes; comm.Parameters.Add(param); break; case CustomerSearch.FIRSTNAME: query = query.Replace("@C", "CustFName"); comm = new SQLiteCommand(query, connection); SQLiteParameter paramFName = new SQLiteParameter("@param", DbType.String); paramFName.Value = searchFor; comm.Parameters.Add(paramFName); break; case CustomerSearch.LASTNAME: query = query.Replace("@C", "CustLName"); comm = new SQLiteCommand(query, connection); SQLiteParameter paramLName = new SQLiteParameter("@param", DbType.String); paramLName.Value = searchFor; comm.Parameters.Add(paramLName); break; case CustomerSearch.EMAIL: query = query.Replace("@C", "CustEmail"); comm = new SQLiteCommand(query, connection); SQLiteParameter paramEmail = new SQLiteParameter("@param", DbType.String); paramEmail.Value = searchFor; comm.Parameters.Add(paramEmail); break; } //Execute the command and load data to table SQLiteDataReader reader = comm.ExecuteReader(); dt.Load(reader); //Closes reader stream then connection reader.Close(); SQLiteHelper.CloseConn(); //Use Datamapper to map selected results to objects DataNamesMapper <CustomerModel> mapper = new DataNamesMapper <CustomerModel>(); customerList = mapper.Map(dt).ToList(); //foreach (DataRow item in dt.Rows) //{ // Console.WriteLine(item["Street"].ToString()); //BREAKTHRUUUUUUUUU //} } catch (Exception e) { Console.WriteLine(e.Message); SQLiteHelper.CloseConn(); MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(customerList); }
public DatosClientePy GetInfoClientePy(string cedula, out string codError, out string mensajeError) { DataNamesMapper <DatosClientePy> mapperToclient = new DataNamesMapper <DatosClientePy>(); DatosClientePy infoCliente = null; DataSet data = null; try { data = _ContratacionProducto.ConsultaDatosClienteCoreAll(cedula, "C", out codError, out mensajeError); } catch (System.Exception) { codError = "503"; mensajeError = InitialConfig.getNotiMessage(codError, _settings).paragraph; return(null); } int paso = 0; int idSolicitud = 0; DataRow row = null; DataTable table = null; if (data.Tables.Contains("DatosBasicosCliente") && codError == "000") { row = data.Tables["DatosBasicosCliente"].Rows[0]; infoCliente = mapperToclient.Map(row); var pagina = row.Field <string>("ID_PASO_SOLICITUD"); var idSoli = row.Field <string>("ID_SOLICITUD"); paso = pagina.ToInt(); idSolicitud = idSoli.ToInt(); //EstadoCivilFromCore DataNamesMapper <EstadoCivilFromCore> mapperToEcFromCore = new DataNamesMapper <EstadoCivilFromCore>(); infoCliente.estadoCivilFormCore = mapperToEcFromCore.Map(row); } if (paso == 0 && idSolicitud == 0) { return(infoCliente); } if (paso >= 1) // Simulador | datos cliente { infoCliente.infoSolicitud = Mapper.Map <FromFormSolicitudCreditoDto>(row); //DataNamesMapper<SolicitudSeleccion> mapperToSeleccion1 = new DataNamesMapper<SolicitudSeleccion>(); //infoCliente.SolicitudSeleccionAnt = mapperToSeleccion1.Map(row); } if (paso >= 2)//no definido (estado Civil) { } if (paso >= 3) //Direccion domicilio | Confirmacion Datos Cliente { DataNamesMapper <FromFormInfoDomicilio> mapperToSeleccion3 = new DataNamesMapper <FromFormInfoDomicilio>(); infoCliente.infoDomicilio = mapperToSeleccion3.Map(row); infoCliente.ActionRoute = "InfoDomicilio"; } if (paso >= 4) //Carga Balances { } if (paso >= 5) //Carga IVA { } if (paso >= 6) //Ventas Cliente { DataNamesMapper <Models.Producto> mapperToSeleccion6 = new DataNamesMapper <Models.Producto>(); var seleccionado = new FromFormVentas(); seleccionado.productos = mapperToSeleccion6.Map(data.Tables["Productos"]).ToList(); seleccionado.numeroEmpleados = row["NUM_EMPLEADOS"].ToString().ToInt(); infoCliente.infoVentas = seleccionado; infoCliente.ActionRoute = "TusVentas"; } if (paso >= 7) //Clientes-Proveedores { List <ClientesProveedores> clientesProveedores = new List <ClientesProveedores>(); DataNamesMapper <ClientesProveedores> mapperToSeleccion7 = new DataNamesMapper <ClientesProveedores>(); FromFormClientes forWiew = new FromFormClientes(); if (data.Tables.Contains("Clientes") && codError == "000") { var t = data.Tables["Clientes"].getWithAddedCol("C"); clientesProveedores = mapperToSeleccion7.Map(t).ToList(); } if (data.Tables.Contains("Proveedores") && codError == "000") { var t = data.Tables["Proveedores"].getWithAddedCol("P"); var r = mapperToSeleccion7.Map(t).ToList(); if (clientesProveedores != null) { clientesProveedores = clientesProveedores.Concat(r).ToList(); } else { clientesProveedores = r; } } forWiew.clientes = clientesProveedores; infoCliente.infoClientesProveedores = forWiew; infoCliente.ActionRoute = "ClientesProveedores"; } if (paso >= 8) //Direccion Negocio { DataNamesMapper <FromFormInfoDirecNeg> mapperToSeleccion8 = new DataNamesMapper <FromFormInfoDirecNeg>(); infoCliente.infoNegocio = mapperToSeleccion8.Map(row); infoCliente.ActionRoute = "DireccionNegocio"; } if (paso >= 9) //Certificacion | Confirmacion Datos Negocio { } if (paso == 10) //Referencias Bancarias { } return(infoCliente); }
//Get Address by column public List <AddressModel> GetAddressByData(string searchFor, AddressSearch columnName) { //Store raw query results to data table DataTable dt = new DataTable(); //SQLite stuff SQLiteCommand comm = null; SQLiteConnection connection = null; string query = "SELECT * FROM ADDRESS WHERE ADDRESS.@C LIKE '%@param%';"; //useable Data after conversion to be returned List <AddressModel> addressList = new List <AddressModel>(); try { connection = SQLiteHelper.OpenConn(); //comm = new SQLiteCommand(query, connection); switch (columnName) { case AddressSearch.ID: query = query.Replace("@C", "AddressId"); comm = new SQLiteCommand(query, connection); int idRes; if (!int.TryParse(searchFor, out idRes)) { throw new Exception("Address ID search is restricted to numbers"); } comm.Parameters.Add("@param", DbType.Int32).Value = idRes; break; case AddressSearch.STREET: query = query.Replace("@C", "Street"); comm = new SQLiteCommand(query, connection); comm.Parameters.Add("@param", DbType.String).Value = searchFor; break; case AddressSearch.CITY: query = query.Replace("@C", "City"); comm = new SQLiteCommand(query, connection); comm.Parameters.Add("@param", DbType.String).Value = searchFor; break; case AddressSearch.ZIP: query = query.Replace("@C", "Zip"); comm = new SQLiteCommand(query, connection); int results; if (!int.TryParse(searchFor, out results)) { throw new Exception("ZIP code is restricted to numbers"); } comm.Parameters.Add("@param", DbType.Int32).Value = results; break; case AddressSearch.STATE: query = query.Replace("@C", "State"); comm = new SQLiteCommand(query, connection); comm.Parameters.Add("@param", DbType.String).Value = searchFor; break; } //Execute the command and load data to table SQLiteDataReader reader = comm.ExecuteReader(); dt.Load(reader); //Closes reader stream then connection reader.Close(); SQLiteHelper.CloseConn(); //Use Datamapper to map selected results to objects DataNamesMapper <AddressModel> mapper = new DataNamesMapper <AddressModel>(); addressList = mapper.Map(dt).ToList(); //foreach (DataRow item in dt.Rows) //{ // Console.WriteLine(item["Street"].ToString()); //BREAKTHRUUUUUUUUU //} } catch (Exception e) { Console.WriteLine(e.Message); SQLiteHelper.CloseConn(); MessageBox.Show(e.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } return(addressList); }
public void TestWhen_() { var priestsDataSet = GeneradorDataSet.Priests(); DataNamesMapper <Inventary> mapper = new DataNamesMapper <Inventary>(); List <Inventary> persons = mapper.Map(priestsDataSet.Tables[0]).ToList(); }