public object Converter(object entrada) { SqlCeDataReader dataReader = (SqlCeDataReader)entrada; Abastecimento abastecimento = new Abastecimento(); abastecimento.Id = dataReader.GetInt32(0); abastecimento.Data = dataReader.GetDateTime(2); abastecimento.Valor = dataReader.GetDecimal(6); abastecimento.ValorLitro = dataReader.GetDecimal(7); return(abastecimento); }
private bool loadBillData(SqlCeConnection connection) { try { using (SqlCeCommand command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM BILLMASTER WHERE ID = " + billToEdit.Value; using (SqlCeDataReader reader = command.ExecuteReader()) { reader.Read(); billDateField.Value = reader.GetDateTime(reader.GetOrdinal("BillDate")); object val = reader.GetValue(reader.GetOrdinal("CustomerID")); if (val == DBNull.Value) { cashBillButton.Checked = true; } else { creditBillButton.Checked = true; customerNameField.SelectedValue = (int)val; } decimal amount = reader.GetDecimal(reader.GetOrdinal("DiscountAmount")); discountField.Text = amount.ToString("N2"); amount = reader.GetDecimal(reader.GetOrdinal("ExpenseAmount")); expenseAmountField.Text = amount.ToString("N2"); val = reader.GetValue(reader.GetOrdinal("ExpenseText")); if (val != DBNull.Value) { expenseTextField.Text = (string)val; } } } loadBillDetails(connection); } catch (Exception ex) { string message = "An error occurred in loading the bill data. \nThe error text is as follows:\n" + Global.getExceptionText(ex); SystemSounds.Hand.Play(); Cursor.Current = Cursors.Default; MessageBox.Show(message, "Error in Loading Data", MessageBoxButtons.OK, MessageBoxIcon.Error); ErrorLogger.LogError(ex); return(false); } return(true); }
private static BSP_Ticket_Detalle DataReaderAObjeto(SqlCeDataReader rdrLector) { BSP_Ticket_Detalle oBSP_Ticket_Detalle = new BSP_Ticket_Detalle(); oBSP_Ticket_Detalle.ID = rdrLector.GetInt64(rdrLector.GetOrdinal("ID")); oBSP_Ticket_Detalle.ISO = rdrLector.GetString(rdrLector.GetOrdinal("ISO")); oBSP_Ticket_Detalle.ImpContado = rdrLector.GetDecimal(rdrLector.GetOrdinal("ImpContado")); oBSP_Ticket_Detalle.ImpCredito = rdrLector.GetDecimal(rdrLector.GetOrdinal("ImpCredito")); oBSP_Ticket_Detalle.Observaciones = rdrLector.GetString(rdrLector.GetOrdinal("Observaciones")); oBSP_Ticket_Detalle.IVA21 = rdrLector.GetDecimal(rdrLector.GetOrdinal("IVA21")); oBSP_Ticket_Detalle.TicketID = rdrLector.GetInt64(rdrLector.GetOrdinal("TicketID")); return(oBSP_Ticket_Detalle); }
private bool getReportDataSet(out BillItemsDataSet dataset) { DataGridViewRow row = invoicesGrid.CurrentRow; int billID = (int)row.Cells[0].Value; StringBuilder sql = new StringBuilder("SELECT Items.NAME, UM.UnitName, BD.Rate, BD.quantity") .Append(" FROM (SELECT * FROM BillDetails WHERE BILLID = ").Append(billID) .Append(") BD INNER JOIN Items ON ItemID = Items.ID ") .Append("INNER JOIN UnitOfMeasurement UM ON BD.UoMID = UM.ID"); string errorText; SqlCeConnection connection = Global.getDatabaseConnection(out errorText); try { using (SqlCeCommand command = connection.CreateCommand()) { command.CommandText = sql.ToString(); using (SqlCeDataReader reader = command.ExecuteReader()) { dataset = new BillItemsDataSet(); BillItemsDataSet.BillItemsTableRow tableRow; while (reader.Read()) { tableRow = dataset.BillItemsTable.NewBillItemsTableRow(); tableRow.ItemName = reader.GetString(0); tableRow.UoM = reader.GetString(1); tableRow.Rate = reader.GetDecimal(2); tableRow.Quantity = reader.GetDecimal(3); tableRow.Amount = tableRow.Rate * tableRow.Quantity; dataset.BillItemsTable.AddBillItemsTableRow(tableRow); } } } } catch (Exception ex) { Cursor.Current = Cursors.Default; SystemSounds.Exclamation.Play(); string message = "An error occurred in retrieving the bill details." + "\nThe error text is as follows:\n" + Global.getExceptionText(ex); Cursor.Current = Cursors.Default; MessageBox.Show(message, "Error Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); ErrorLogger.LogError(ex); dataset = null; return(false); } return(true); }
private static BSP_Ticket DataReaderAObjeto(SqlCeDataReader rdrLector) { BSP_Ticket oBSP_Ticket = new BSP_Ticket(); oBSP_Ticket.ID = rdrLector.GetInt64(rdrLector.GetOrdinal("ID")); oBSP_Ticket.Billete = rdrLector.GetInt64(rdrLector.GetOrdinal("Billete")); oBSP_Ticket.Tipo = rdrLector.GetString(rdrLector.GetOrdinal("Tipo")); if (!rdrLector.IsDBNull(rdrLector.GetOrdinal("FechaVenta"))) { oBSP_Ticket.FechaVenta = rdrLector.GetDateTime(rdrLector.GetOrdinal("FechaVenta")); } oBSP_Ticket.TarContado = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarContado")); oBSP_Ticket.TarCredito = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarCredito")); oBSP_Ticket.IVA105 = rdrLector.GetDecimal(rdrLector.GetOrdinal("IVA105")); oBSP_Ticket.ComPorcentaje = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComPorcentaje")); oBSP_Ticket.ComValor = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComValor")); oBSP_Ticket.ComOver = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComOver")); oBSP_Ticket.ComIVA = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComIVA")); oBSP_Ticket.Total = rdrLector.GetDecimal(rdrLector.GetOrdinal("Total")); oBSP_Ticket.Rg = rdrLector.GetBoolean(rdrLector.GetOrdinal("Rg")) ? BSP_Rg.Internacional : BSP_Rg.Doméstico; oBSP_Ticket.Moneda = rdrLector.GetBoolean(rdrLector.GetOrdinal("Moneda")) ? Moneda.Dolar : Moneda.Peso; oBSP_Ticket.Concepto = new Concepto { ID = rdrLector.GetInt64(rdrLector.GetOrdinal("IdConcepto")) }; oBSP_Ticket.Compania = new Compania { ID = rdrLector.GetInt64(rdrLector.GetOrdinal("IdCompania")) }; oBSP_Ticket.SemanaID = rdrLector.GetInt64(rdrLector.GetOrdinal("SemanaID")); return(oBSP_Ticket); }
public object Converter(object entrada) { SqlCeDataReader dataReader = (SqlCeDataReader)entrada; Abastecimento abastecimento = new Abastecimento(); abastecimento.Id = dataReader.GetInt32(0); abastecimento.Posto = (Posto)Negocio.NegocioFactory.Instancia.PostoNegocio.Obter(dataReader.GetInt32(1)); abastecimento.Data = dataReader.GetDateTime(2); abastecimento.Observacoes = dataReader.GetString(3); abastecimento.TanqueCheio = dataReader.GetBoolean(4); abastecimento.TipoCombustivel = (TipoCombustivel)Negocio.NegocioFactory.Instancia.TipoCombustivelNegocio.Obter(dataReader.GetInt32(5)); abastecimento.Valor = dataReader.GetDecimal(6); abastecimento.ValorLitro = dataReader.GetDecimal(7); return(abastecimento); }
public static User CreateUserFromId(int id) { User user = null; string command = "select id, login, firstname, lastname, isadmin from users where id=@id"; using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString)) { SqlCeCommand com = new SqlCeCommand(command, con); com.Parameters.Add("id", id); try { con.Open(); SqlCeDataReader reader = com.ExecuteReader(); while (reader.Read()) { user = new User( (int)reader.GetDecimal(0), reader.GetString(1) , reader.IsDBNull(2) ? "" : reader.GetString(2) , reader.IsDBNull(3) ? "" : reader.GetString(3) , reader.IsDBNull(4) ? false : reader.GetBoolean(4)); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } return(user); }
private void populateControlsWithData(SqlCeDataReader reader) { int customerID = reader.GetInt32(reader.GetOrdinal("CustomerID")); customerNameCombo.SelectedValue = customerID; paymentDatePicker.Value = reader.GetDateTime(reader.GetOrdinal("PaymentDate")); decimal amount = reader.GetDecimal(reader.GetOrdinal("Amount")); amountField.Text = amount.ToString("N2"); string paymentMode = reader.GetString(reader.GetOrdinal("PaymentMode")); switch (paymentMode) { case "C": cashButton.Checked = true; break; case "Q": chequeButton.Checked = true; break; case "D": demandDraftButton.Checked = true; break; } object value = reader.GetValue(reader.GetOrdinal("InstrumentNumber")); if (value != DBNull.Value) { instrumentNumberField.Text = (string)value; } value = reader.GetValue(reader.GetOrdinal("Notes")); if (value != DBNull.Value) { notesField.Text = (string)value; } string errorText; decimal?balanceAmount = GlobalMethods.GetCustomerBalance(customerID, out errorText); if (!balanceAmount.HasValue) { string message = "An error occurred in fetching the customer's balance from the database." + "\nThe error text is as follows:\n" + errorText; Cursor.Current = Cursors.Default; SystemSounds.Exclamation.Play(); MessageBox.Show(message, "Error in Fetching Account Balance", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } balanceAmount = balanceAmount.Value - amount; balanceField.Text = GlobalMethods.GetBalanceAsString(balanceAmount.Value); }
public static Funcionario PegarFuncionario(int id) { string sql = "SELECT * FROM [Funcionario] WHERE Id=@id"; SqlCeCommand comando = new SqlCeCommand(sql, conn); comando.Parameters.Add("@id", id); conn.Open(); SqlCeDataReader resposta = comando.ExecuteReader(); Funcionario func = new Funcionario(); while (resposta.Read()) { func.id = resposta.GetInt32(0); func.name = resposta.GetString(1); func.email = resposta.GetString(2); func.salario = resposta.GetDecimal(3); func.sexo = resposta.GetString(4); func.tipoContrato = resposta.GetString(5); func.dataCadastro = resposta.GetDateTime(6); if (resposta.IsDBNull(7)) { func.dataAtualizacao.Equals(null); } else { func.dataAtualizacao = resposta.GetDateTime(7); } } conn.Close(); return(func); }
public static async Task <Employee> GetEmployee(int id) { string sql = "SELECT * FROM Employee WHERE Id = @id"; SqlCeCommand command = new SqlCeCommand(sql, con); command.Parameters.Add("@id", id); await con.OpenAsync(); SqlCeDataReader result = (SqlCeDataReader)await command.ExecuteReaderAsync(); Employee employee = new Employee(); while (await result.ReadAsync()) { employee.Id = result.GetInt32(0); employee.Name = result.GetString(1); employee.Email = result.GetString(2); employee.Salary = result.GetDecimal(3); employee.Gender = result.GetString(4); employee.ContractType = result.GetString(5); //employee.RegisterDate = result.GetDateTime(6); } con.Close(); return(employee); }
public Station FetchStation(int stationId) { using (var com = new SqlCeCommand("SELECT * FROM Stanice WHERE Id = " + stationId, _connection)) { SqlCeDataReader reader = com.ExecuteReader(); if (reader.Read()) { var id = reader.GetInt32(0); var alt = (double)reader.GetDecimal(1); var lon = (double)reader.GetDecimal(2); var name = reader.GetString(3); var direction = reader.GetString(4); return(new Station(alt, lon, name, direction) { Id = id }); } return(null); } }
private static BO_Ticket DataReaderAObjeto(SqlCeDataReader rdrLector) { BO_Ticket oBO_Ticket = new BO_Ticket(); oBO_Ticket.ID = rdrLector.GetInt64(rdrLector.GetOrdinal("ID")); oBO_Ticket.IATA = rdrLector.GetInt64(rdrLector.GetOrdinal("IATA")); oBO_Ticket.Billete = rdrLector.GetInt64(rdrLector.GetOrdinal("Billete")); if (!rdrLector.IsDBNull(rdrLector.GetOrdinal("Fecha"))) { oBO_Ticket.Fecha = rdrLector.GetDateTime(rdrLector.GetOrdinal("Fecha")); } oBO_Ticket.Void = rdrLector.GetBoolean(rdrLector.GetOrdinal("Void")); if (!rdrLector.IsDBNull(rdrLector.GetOrdinal("CIA"))) { oBO_Ticket.Compania = new Compania { Codigo = rdrLector.GetString(rdrLector.GetOrdinal("CIA")) } } ; oBO_Ticket.Tarifa = rdrLector.GetDecimal(rdrLector.GetOrdinal("Tarifa")); oBO_Ticket.TarContado = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarContado")); oBO_Ticket.TarCredito = rdrLector.GetDecimal(rdrLector.GetOrdinal("TarCredito")); oBO_Ticket.Impuestos = rdrLector.GetDecimal(rdrLector.GetOrdinal("Impuestos")); oBO_Ticket.Comision = rdrLector.GetDecimal(rdrLector.GetOrdinal("Comision")); oBO_Ticket.ComOver = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComOver")); oBO_Ticket.Expediente = rdrLector.GetString(rdrLector.GetOrdinal("Expediente")); oBO_Ticket.IVA105 = rdrLector.GetDecimal(rdrLector.GetOrdinal("IVA10")); oBO_Ticket.IVAComision = rdrLector.GetDecimal(rdrLector.GetOrdinal("IVAComision")); oBO_Ticket.ComValor = rdrLector.GetDecimal(rdrLector.GetOrdinal("ComValor")); oBO_Ticket.Total = rdrLector.GetDecimal(rdrLector.GetOrdinal("Total")); oBO_Ticket.Factura = rdrLector.GetString(rdrLector.GetOrdinal("Factura")); oBO_Ticket.Pasajero = rdrLector.GetString(rdrLector.GetOrdinal("Pasajero")); oBO_Ticket.Vendedor = rdrLector.GetString(rdrLector.GetOrdinal("Vendedor")); oBO_Ticket.Moneda = rdrLector.GetBoolean(rdrLector.GetOrdinal("Moneda")) ? Moneda.Dolar : Moneda.Peso; oBO_Ticket.SemanaID = rdrLector.GetInt64(rdrLector.GetOrdinal("SemanaID")); return(oBO_Ticket); }
public static decimal?GetCustomerOpeningBalance(int customerID, out string errorText, SqlCeConnection connection = null) { if (connection == null) { connection = Global.getDatabaseConnection(out errorText); if (connection == null) { return(null); } } string sql = "SELECT OpeningBalance, BalanceType FROM Customers " + "WHERE ID = " + customerID; decimal openingBalance; string balanceType = null; try { using (SqlCeCommand command = connection.CreateCommand()) { command.CommandText = sql; using (SqlCeDataReader reader = command.ExecuteReader()) { reader.Read(); openingBalance = reader.GetDecimal(0); if (!reader.IsDBNull(1)) { balanceType = reader.GetString(1); } } } } catch (Exception ex) { errorText = Global.getExceptionText(ex); ErrorLogger.LogError(ex); return(null); } if (balanceType != null && balanceType.ToLower().Equals("d")) { openingBalance *= -1; //negate the amount to indicate debit balance } errorText = null; return(openingBalance); }
public static List <User> GetUsers(int user) { List <User> users = new List <User>(); string command = "select id, lastname, firstname from users where showinlist=1"; if (user != 0) { command += " and id=@id"; } command += " order by lastname"; using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString)) { SqlCeCommand com = new SqlCeCommand(command, con); if (user != 0) { com.Parameters.AddWithValue("id", user);//com.Parameters.Add("id", user); } try { con.Open(); SqlCeDataReader reader = com.ExecuteReader(); while (reader.Read()) { string lName = ""; string fName = ""; if (!reader.IsDBNull(1)) { lName = reader.GetString(1); } if (!reader.IsDBNull(2)) { fName += reader.GetString(2); } users.Add(new User((int)reader.GetDecimal(0), "", fName, lName, false)); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } return(users); }
public static User Create(string login, string password, bool passwordRequired) { User user = null; string command = "select id, login, firstname, lastname, isadmin from users where login=@login"; if (passwordRequired) { command += " and coalesce(password, '', '')=@password"; } using (SqlCeConnection con = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString)) { SqlCeCommand com = new SqlCeCommand(command, con); com.Parameters.AddWithValue("login", login); //com.Parameters.Add("login", login); if (passwordRequired) { com.Parameters.AddWithValue("password", password); //com.Parameters.Add("password", password); } try { con.Open(); SqlCeDataReader reader = com.ExecuteReader(); while (reader.Read()) { user = new User( (int)reader.GetDecimal(0), reader.GetString(1) , reader.IsDBNull(2) ? "" : reader.GetString(2) , reader.IsDBNull(3) ? "" : reader.GetString(3) , reader.IsDBNull(4) ? false : reader.GetBoolean(4)); } } catch (System.Data.SqlServerCe.SqlCeException se) { MessageBox.Show("Perhaps you need to update the database. Use the UPDATE parameter. Bye!"); Environment.Exit(1); } catch (Exception ex) { MessageBox.Show(ex.Message); } } return(user); }
public IList <Station> FetchStations() { IList <Station> stanice = new List <Station>(); using (var com = new SqlCeCommand("SELECT * FROM Stanice", _connection)) { SqlCeDataReader reader = com.ExecuteReader(); while (reader.Read()) { var id = reader.GetInt32(0); var alt = (double)reader.GetDecimal(1); var lon = (double)reader.GetDecimal(2); var name = reader.GetString(3); var direction = reader.GetString(4); stanice.Add(new Station(alt, lon, name, direction) { Id = id }); } } return(stanice); }
public void senddoc() { string connectionString; string filename = (ufile); StreamWriter sw = new StreamWriter(filename); connectionString = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cn = new SqlCeConnection(connectionString); cn.Open(); SqlCeDataAdapter da = new SqlCeDataAdapter("SELECT * FROM dok", cn); DataTable table = new DataTable(); da.Fill(table); string index = table.Rows[rownum][0].ToString(); SqlCeCommand cmdh = cn.CreateCommand(); cmdh.CommandText = "SELECT nazwadok, typ, data FROM dok WHERE id = ?"; cmdh.Parameters.Add("@d", SqlDbType.Int); cmdh.Prepare(); cmdh.Parameters["@d"].Value = int.Parse(index); SqlCeDataReader drh = cmdh.ExecuteReader(); while (drh.Read()) { sw.WriteLine(drh.GetString(0) + ";" + drh.GetString(1) + ";" + Convert.ToString(drh.GetSqlDateTime(2))); } SqlCeCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT dokid, kod, nazwa, cenazk, ilosc, stan, cenasp, vat FROM bufor WHERE dokid = ?"; cmd.Parameters.Add("@d", SqlDbType.Int); cmd.Prepare(); cmd.Parameters["@d"].Value = int.Parse(index); SqlCeDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { decimal n = dr.GetDecimal(4); int did = dr.GetInt32(0); sw.WriteLine(Convert.ToString(did) + ";" + dr.GetString(1) + ";" + dr.GetString(2) + ";" + dr.GetString(3) + ";" + Convert.ToString(n) + ";" + dr.GetString(5) + ";" + dr.GetString(6) + ";" + dr.GetString(7)); } sw.Close(); cn.Close(); }
private bool populatePaymentStatements(DateTime openingDate, AccountStatementDataSet dataset) { string sql = getPaymentRetrievalSQL(openingDate); string errorText; SqlCeConnection connection = Global.getDatabaseConnection(out errorText); try { using (SqlCeCommand command = connection.CreateCommand()) { command.CommandText = sql; using (SqlCeDataReader reader = command.ExecuteReader()) { AccountStatementDataSet.AccountStatementRow row; string mode; while (reader.Read()) { row = dataset.AccountStatement.NewAccountStatementRow(); row.TransactionDate = reader.GetDateTime(0); mode = reader.GetString(2); row.Description = "Payment received through " + (mode == "C" ? "cash" : (mode == "Q" ? "cheque" : "DD")); row.CreditAmount = reader.GetDecimal(1); dataset.AccountStatement.AddAccountStatementRow(row); } } } } catch (Exception ex) { string message = "An error occurred in fetching the account details from " + "the database. The error text is as follows:\n" + Global.getExceptionText(ex); Cursor.Current = Cursors.Default; MessageBox.Show(message, "Error Occurred", MessageBoxButtons.OK, MessageBoxIcon.Error); ErrorLogger.LogError(ex); return(false); } return(true); }
public void senddoc() { StreamWriter sw = new StreamWriter("picatch.imp", true, Encoding.GetEncoding(CultureInfo.CurrentCulture.TextInfo.ANSICodePage)); int dokid = 0; string connectionString; connectionString = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cn = new SqlCeConnection(connectionString); cn.Open(); SqlCeCommand cmdi = cn.CreateCommand(); cmdi.CommandText = "Select MAX(id) From dok"; SqlCeDataReader dri = cmdi.ExecuteReader(); while (dri.Read()) { dokid = dri.GetInt32(0); } SqlCeCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT dokid, kod, nazwa, cenazk, ilosc, stan, cenasp, vat FROM bufor WHERE dokid = ?"; cmd.Parameters.Add("@d", SqlDbType.Int); cmd.Prepare(); cmd.Parameters["@d"].Value = dokid; SqlCeDataReader dr = cmd.ExecuteReader(); System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo(); nfi.NumberDecimalSeparator = "."; while (dr.Read()) { decimal n = dr.GetDecimal(4); int did = dr.GetInt32(0); sw.WriteLine(Convert.ToString(did) + ";" + dr.GetString(1) + ";" + dr.GetString(2) + ";" + dr.GetString(3) + ";" + n.ToString(nfi) + ";" + dr.GetString(5) + ";" + dr.GetString(6) + ";" + dr.GetString(7)); } sw.Close(); cn.Close(); }
//public byte[] Image { get; set; } public void LoadTrainer(SqlCeDataReader reader) { TrainerID = reader.GetInt32(0); FName = reader["FirstName"].ToString(); LName = reader["LastName"].ToString(); Sex = reader["Sex"].ToString(); if (!reader.IsDBNull(3)) { DateOfBirth = reader.GetDateTime(3); } Street = reader["Street"].ToString(); Suburb = reader["Suburb"].ToString(); City = reader["City"].ToString(); HomePhone = reader["HomePhone"].ToString(); CellPhone = reader["CellPhone"].ToString(); Email = reader["email"].ToString(); if (!reader.IsDBNull(11)) { Salary = reader.GetDecimal(11); } Notes = reader["Notes"].ToString(); }
static private List <IdBalance> queryIdBalance(string query) { List <IdBalance> queryResults = new List <IdBalance>(); SqlCeConnection connection = new SqlCeConnection(Properties.Settings.Default.FFDBConnectionString); SqlCeCommand command = new SqlCeCommand(query, connection); connection.Open(); SqlCeDataReader reader = command.ExecuteReader(); // Iterate through the results while (reader.Read()) { queryResults.Add(new IdBalance(reader.GetInt32(0), reader.GetDecimal(1))); } // Always call Close the reader and connection when done reading reader.Close(); command.Dispose(); connection.Close(); return(queryResults); }
public static void FillComboUsers(ComboBox combo) { string command = "select id,login from users where showinlist='true' order by login"; using (SqlCeConnection connection = new SqlCeConnection(global::Programlancer.Properties.Settings.Default.ShopConnectionString)) { SqlCeCommand sqlCommand = new SqlCeCommand(command, connection); try { connection.Open(); SqlCeDataReader reader = sqlCommand.ExecuteReader(); while (reader.Read()) { combo.Items.Add(new ComboBoxItem(reader.GetString(1), (int)reader.GetDecimal(0))); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
private void FindIndex() { string kodbuf = search_t.Text; search_t.Text = "SZUKAM TOWARÓW W BAZIE"; search_t.Refresh(); string connectionString; connectionString = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cn = new SqlCeConnection(connectionString); cn.Open(); if (kodbuf != "" && toolStripComboBox1.Text != "" && toolStripComboBox1.Text != null) { SqlCeCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT dane.kod, dane.nazwa, stany.stan, magazyn.Nazwa AS magazyn, dane.cenasp, dane.stan as calystan, dane.vat, dane.cenazk, dane.cenahurt, dane.cenaoryg FROM magazyn INNER JOIN stany ON magazyn.MagId = stany.MagId INNER JOIN dane ON stany.kod = dane.kod where (magazyn.Nazwa = ?) AND (dane.kod = ?)"; cmd.Parameters.Add("@m", SqlDbType.NVarChar, 20); cmd.Parameters.Add("@k", SqlDbType.NVarChar, 20); cmd.Prepare(); cmd.Parameters["@m"].Value = toolStripComboBox1.Text; cmd.Parameters["@k"].Value = kodbuf; int test = 0; SqlCeDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if (!dr.IsDBNull(0)) { System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo(); nfi.NumberDecimalSeparator = "."; test = 1; decimal stan = decimal.Round(dr.GetDecimal(2), 0); decimal calystan = decimal.Parse(dr.GetString(5), nfi); dokstan = dr.GetString(5); cenasp = dr.GetString(4); nazwa = dr.GetString(1); kod = dr.GetString(0); cenazk = dr.GetString(7); vat = dr.GetString(6); cenah = Convert.ToString(decimal.Round(Convert.ToDecimal(dr.GetString(8), nfi) / ((Convert.ToDecimal(vat, nfi) + 100) / 100), 2)); cenao = dr.GetString(9); if (type == 0) { label1.Text = kod + "\n" + nazwa + "\n Detaliczna (brutto): " + cenasp; } else if (type == 1) { label1.Text = kod + "\n" + nazwa + "\n Hurt (netto): " + cenah + " Oryginalna (brutto): " + cenao + "\n\nStan: " + stan.ToString(); } if (stan > 0) { this.BackColor = Color.YellowGreen; label1.Text += " - OK !!!!!"; } else if (calystan > 0) { this.BackColor = Color.DodgerBlue; label1.Text += "\n TOWAR JEST NA INNYM MAGAZYNIE"; } else { this.BackColor = Color.Crimson; label1.Text += "\n BRAK TOWARU"; } DataGridTableStyle ts = new DataGridTableStyle(); SqlCeDataAdapter db = new SqlCeDataAdapter("SELECT stany.kod, stany.stan, magazyn.Nazwa AS magazyn FROM magazyn INNER JOIN stany ON magazyn.MagId = stany.MagId", cn); DataTable table2 = new DataTable(); db.SelectCommand = new SqlCeCommand("SELECT magazyn.Nazwa AS Magazyn, stany.stan As Stan FROM magazyn INNER JOIN stany ON magazyn.MagId = stany.MagId where (stany.kod = ?)", cn); db.SelectCommand.Parameters.Add("@k", SqlDbType.NVarChar, 15); db.SelectCommand.Parameters["@k"].Value = kodbuf; db.SelectCommand.ExecuteNonQuery(); db.Fill(table2); if (table2.Rows.Count != 0) { dataGridView1.DataSource = table2.DefaultView; } } } dr.Close(); if (test == 0) { label1.Text = "NIE ZNALEZIONO TOWARU O PODANYM KODZIE !!!!"; this.BackColor = Color.DarkOrange; } } else { MessageBox.Show("Nie wybrałeś magazynu lub nie podałeś kodu kreskowego towaru"); } search_t.Text = ""; search_t.Focus(); dataGridView1.Refresh(); cn.Close(); }
public void updatedata(Undersoft.Picatch.Agent.Main mainf) { while (running == true) { downloaddata(); try { Invoke((MethodInvoker)(() => exkom_t.Text += "KONWERTUJE DANE Z BAZY PCMARKET\n")); System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo(); nfi.NumberDecimalSeparator = "."; // string connectionString2; // connectionString2 = "server=" + serverpcm + ";user id=" + loginpcm + ";password="******";Trusted_Connection=no; database=" + bazapcm + ";connection timeout=30"; // pcmn = new SqlConnection(connectionString2); } catch (Exception ex) { Invoke((MethodInvoker)(() => exkom_t.Text = "BŁĄD KONWERSJI DANYCH Z BAZY PCMARKET\n")); } string connectionString; connectionString = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cn = new SqlCeConnection(connectionString); string connectionString3; connectionString3 = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cnn = new SqlCeConnection(connectionString3); string connectionString2; connectionString2 = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cnnn = new SqlCeConnection(connectionString2); int dbtest = 0; try { cnnn.Open(); cn.Open(); cnn.Open(); } catch (Exception ex) { Invoke((MethodInvoker)(() => exkom_t.Text += "Błąd połączenia z bazą\n")); dbtest = 1; } if (dbtest == 0) { try { SqlCeCommand cmd3 = cn.CreateCommand(); cmd3.CommandText = "INSERT INTO dane (typ, kod, nazwa, stan, cenazk, cenasp, vat, datazmian, cenahurt, cenaoryg) VALUES ('TOW', ?, ?, ?, ?, ?, ?, ?, ?, ?)"; cmd3.Parameters.Add("@k", SqlDbType.NVarChar, 15); cmd3.Parameters.Add("@n", SqlDbType.NVarChar, 40); cmd3.Parameters.Add("@s", SqlDbType.NVarChar, 10); cmd3.Parameters.Add("@cz", SqlDbType.NVarChar, 10); cmd3.Parameters.Add("@cs", SqlDbType.NVarChar, 10); cmd3.Parameters.Add("@v", SqlDbType.NVarChar, 5); cmd3.Parameters.Add("@d", SqlDbType.DateTime); cmd3.Parameters.Add("@ch", SqlDbType.NVarChar, 10); cmd3.Parameters.Add("@co", SqlDbType.NVarChar, 10); cmd3.Prepare(); SqlCeCommand cmd4 = cn.CreateCommand(); cmd4.CommandText = "INSERT INTO stany (kod, stan, MagId, Nazwa, datazmian) VALUES (?, ?, ?, ?, ?)"; cmd4.Parameters.Add("@k", SqlDbType.NVarChar, 15); cmd4.Parameters.Add("@i", SqlDbType.Decimal, 10); cmd4.Parameters["@i"].Precision = 10; cmd4.Parameters["@i"].Scale = 3; cmd4.Parameters.Add("@s", SqlDbType.Int, 11); cmd4.Parameters.Add("@cz", SqlDbType.NVarChar, 10); cmd4.Parameters.Add("@d", SqlDbType.DateTime); cmd4.Prepare(); SqlCeCommand cmd10 = cn.CreateCommand(); cmd10.CommandText = "UPDATE dane SET nazwa = ?, stan = ?, cenazk = ?, cenasp = ?, vat = ?, datazmian = ?, cenahurt = ?, cenaoryg = ? WHERE kod = ?"; cmd10.Parameters.Add("@n", SqlDbType.NVarChar, 40); cmd10.Parameters.Add("@s", SqlDbType.NVarChar, 10); cmd10.Parameters.Add("@cz", SqlDbType.NVarChar, 10); cmd10.Parameters.Add("@cs", SqlDbType.NVarChar, 10); cmd10.Parameters.Add("@v", SqlDbType.NVarChar, 5); cmd10.Parameters.Add("@d", SqlDbType.DateTime); cmd10.Parameters.Add("@ch", SqlDbType.NVarChar, 10); cmd10.Parameters.Add("@co", SqlDbType.NVarChar, 10); cmd10.Parameters.Add("@k", SqlDbType.NVarChar, 15); cmd10.Prepare(); SqlCeCommand cmd6 = cn.CreateCommand(); cmd6.CommandText = "UPDATE stany SET stan = ?, datazmian = ? WHERE (kod = ?) AND (MagId = ?)"; cmd6.Parameters.Add("@i", SqlDbType.Decimal, 10); cmd6.Parameters["@i"].Precision = 10; cmd6.Parameters["@i"].Scale = 3; cmd6.Parameters.Add("@d", SqlDbType.DateTime); cmd6.Parameters.Add("@k", SqlDbType.NVarChar, 15); cmd6.Parameters.Add("@m", SqlDbType.Int, 11); cmd6.Prepare(); SqlCeCommand cmdcheck = cnn.CreateCommand(); cmdcheck.CommandText = "SELECT count(*) FROM dane where kod = ?"; cmdcheck.Parameters.Add("@k", SqlDbType.NVarChar, 15); cmdcheck.Prepare(); SqlCeCommand cmdchecks = cnn.CreateCommand(); cmdchecks.CommandText = "SELECT count(*) FROM stany where kod = ? and MagId = ?"; cmdchecks.Parameters.Add("@k", SqlDbType.NVarChar, 15); cmdchecks.Parameters.Add("@m", SqlDbType.Int, 11); cmdchecks.Prepare(); SqlCeCommand cmd = cnnn.CreateCommand(); cmd.CommandText = "SELECT kod, nazwa, stan, cenazk, cenasp, vat, datazmian, cenahurt FROM bufordane"; cmd.Prepare(); // SqlCeCommand cmd5 = cnnn.CreateCommand(); // cmd5.CommandText = "SELECT kod, stan, MagId, Nazwa FROM buforstany"; // cmd5.Prepare(); SqlCeDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { string kod = sdr.GetString(0); string nazwa = sdr.GetString(1); string stan = sdr.GetString(2); // decimal.Round(sdr.GetDecimal(2), 2).ToString(nfi); string cenazk = sdr.GetString(3); // decimal.Round(sdr.GetDecimal(3), 2).ToString(nfi); string cenasp = sdr.GetString(4); // decimal.Round(sdr.GetDecimal(4), 2).ToString(nfi); string vat = sdr.GetString(5); // Convert.ToString(sdr.GetInt32(5)); DateTime impdatazmian = sdr.GetDateTime(6); string cenah = sdr.GetString(7); string cenao = sdr.GetString(8); try { int count = 0; cmdcheck.Parameters["@k"].Value = kod; SqlCeDataReader cmdcheckdr = cmdcheck.ExecuteReader(); while (cmdcheckdr.Read()) { count = cmdcheckdr.GetInt32(0); } cmdcheckdr.Close(); if (count > 0) { cmd10.Parameters["@n"].Value = (nazwa.Replace("?", "")).Replace(";", ""); cmd10.Parameters["@s"].Value = stan; cmd10.Parameters["@cz"].Value = cenazk; cmd10.Parameters["@cs"].Value = cenasp; cmd10.Parameters["@v"].Value = vat; cmd10.Parameters["@d"].Value = impdatazmian; cmd10.Parameters["@ch"].Value = cenah; cmd10.Parameters["@k"].Value = kod; cmd10.ExecuteNonQuery(); if (this.exkom_t.InvokeRequired) { Invoke((MethodInvoker)(() => exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + impdatazmian.ToString() + "; - Zaktualizowano\n")); Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length)); Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret())); } else { exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + datazmian.ToString() + "; - Zaktualizowano\n"; exkom_t.SelectionStart = exkom_t.Text.Length; exkom_t.ScrollToCaret(); } } else { cmd3.Parameters["@k"].Value = kod; cmd3.Parameters["@n"].Value = (nazwa.Replace("?", "")).Replace(";", ""); cmd3.Parameters["@s"].Value = stan; cmd3.Parameters["@cz"].Value = cenazk; cmd3.Parameters["@cs"].Value = cenasp; cmd3.Parameters["@v"].Value = vat; cmd3.Parameters["@d"].Value = impdatazmian; cmd3.Parameters["@ch"].Value = cenah; cmd3.Parameters["@co"].Value = cenao; cmd3.ExecuteNonQuery(); if (this.exkom_t.InvokeRequired) { Invoke((MethodInvoker)(() => exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + impdatazmian.ToString() + "; - Dodano\n")); Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length)); Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret())); } else { exkom_t.Text += kod + ";" + nazwa + ";" + stan + ";" + datazmian.ToString() + "; - Dodano\n"; exkom_t.SelectionStart = exkom_t.Text.Length; exkom_t.ScrollToCaret(); } } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } sdr.Close(); if (this.exkom_t.InvokeRequired) { Invoke((MethodInvoker)(() => exkom_t.Text += "Zaktualizowano dane o towarach\n")); // Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length)); // Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret())); } else { exkom_t.Text += "Zaktualizowano dane o towarach\n"; // exkom_t.SelectionStart = exkom_t.Text.Length; // exkom_t.ScrollToCaret(); } SqlCeCommand cmd2 = cn.CreateCommand(); cmd2.CommandText = "SELECT Nazwa, MagId FROM magazyn"; cmd2.Prepare(); SqlCeCommand cmd9 = cnn.CreateCommand(); cmd9.CommandText = "SELECT kod, stan, MagId, Nazwa, datazmian FROM buforstany WHERE MagId = @mag "; cmd9.Parameters.Add("@mag", SqlDbType.Int); cmd9.Prepare(); // SqlCommand cmd8 = pcmn.CreateCommand(); // cmd8.CommandText = "SELECT KodDod.Kod, Magazyn.Nazwa, Istw.MagId, Istw.StanMag AS Expr1 FROM Towar INNER JOIN Istw ON Towar.TowId = Istw.TowId INNER JOIN Magazyn ON Istw.MagId = Magazyn.MagId INNER JOIN KodDod ON Towar.TowId = KodDod.TowId WHERE (Towar.Aktywny = 1) AND Istw.MagId = @mag "; // cmd8.Parameters.Add("@mag", SqlDbType.Int); // cmd8.Prepare(); SqlCeDataReader cedr = cmd2.ExecuteReader(); while (cedr.Read()) { string magazyn = cedr.GetString(0); int magid = cedr.GetInt32(1); cmd9.Parameters["@mag"].Value = magid; // cmd8.Parameters["@mag"].Value = magid; SqlCeDataReader szdr = cmd9.ExecuteReader(); while (szdr.Read()) { int count = 0; cmdchecks.Parameters["@m"].Value = magid; cmdchecks.Parameters["@k"].Value = szdr.GetString(0); SqlCeDataReader cmdchecksdr = cmdchecks.ExecuteReader(); while (cmdchecksdr.Read()) { count = cmdchecksdr.GetInt32(0); } cmdchecksdr.Close(); if (count > 0) { cmd6.Parameters["@i"].Value = szdr.GetDecimal(1); cmd6.Parameters["@k"].Value = szdr.GetString(0); cmd6.Parameters["@m"].Value = magid; cmd6.ExecuteNonQuery(); if (this.exkom_t.InvokeRequired) { Invoke((MethodInvoker)(() => exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(1).ToString() + ";" + magazyn + "; - Zaktualizowano\n")); Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length)); Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret())); } else { exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(3).ToString() + ";" + magazyn + "; - Zaktualizowano\n"; exkom_t.SelectionStart = exkom_t.Text.Length; exkom_t.ScrollToCaret(); } } else { cmd4.Parameters["@k"].Value = szdr.GetString(0); cmd4.Parameters["@i"].Value = szdr.GetDecimal(3); cmd4.Parameters["@s"].Value = szdr.GetDecimal(2); cmd4.Parameters["@cz"].Value = szdr.GetString(1); cmd4.Parameters["@d"].Value = szdr.GetDateTime(4); cmd4.ExecuteNonQuery(); if (this.exkom_t.InvokeRequired) { Invoke((MethodInvoker)(() => exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(3).ToString() + ";" + magazyn + "; - Dodano\n")); Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length)); Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret())); } else { exkom_t.Text += szdr.GetString(0) + ";" + szdr.GetDecimal(3).ToString() + ";" + magazyn + "; - Dodano\n"; exkom_t.SelectionStart = exkom_t.Text.Length; exkom_t.ScrollToCaret(); } } } szdr.Close(); } cedr.Close(); cn.Close(); cnn.Close(); cnnn.Close(); if (this.exkom_t.InvokeRequired) { Invoke((MethodInvoker)(() => exkom_t.Text += "Zaktualizowano dane o stanach\n")); Invoke((MethodInvoker)(() => exkom_t.SelectionStart = exkom_t.Text.Length)); Invoke((MethodInvoker)(() => exkom_t.ScrollToCaret())); Invoke((MethodInvoker)(() => exkom_t.Text += "Dane aktualne na:" + DateTime.Now.ToString() + "\n")); } else { exkom_t.Text += "Zaktualizowano dane o stanach\n"; exkom_t.SelectionStart = exkom_t.Text.Length; exkom_t.ScrollToCaret(); } } catch (Exception eg) { cnn.Close(); cn.Close(); cnnn.Close(); Invoke((MethodInvoker)(() => exkom_t.Text += eg.ToString() + "\n")); } } Thread.Sleep(10000); // exkom_t.Text = ""; } }
private void pcmarket_schema_export_rem() { StreamWriter sw = new StreamWriter(@ufile + "\\doc" + DateTime.Now.Ticks.ToString() + ".txt", true, Encoding.GetEncoding("iso-8859-1")); // string index = Convert.ToString(selectedRow.Cells[0].Value); // string nrdok = Convert.ToString(selectedRow.Cells[4].Value); string connectionString; connectionString = "DataSource=Baza.sdf; Password=matrix1"; SqlCeConnection cn = new SqlCeConnection(connectionString); cn.Open(); SqlCeCommand cmd = cn.CreateCommand(); //cmd.CommandText = "SELECT * FROM dane WHERE bad_stan"; // cmd.Parameters.Add("@d", SqlDbType.Int); // cmd.Prepare(); // cmd.Parameters["@d"].Value = int.Parse(index); System.Globalization.NumberFormatInfo nfi = new System.Globalization.NumberFormatInfo(); nfi.NumberDecimalSeparator = "."; sw.WriteLine("TypPolskichLiter:LA"); sw.WriteLine("TypDok:RM"); sw.WriteLine("NrDok:REM/" + DateTime.Now.Ticks.ToString()); // sw.WriteLine("Data:" .ToString()); // sw.WriteLine("Magazyn:Mag nr 1"); // sw.WriteLine("SposobPlatn:GOT" ); // sw.WriteLine("TerminPlatn:0"); // sw.WriteLine("IndeksCentralny:NIE"); // sw.WriteLine("NazwaWystawcy:JUKADO FIRMA HANDLOWA KAROL WSZĘDYBYŁ" ); // sw.WriteLine("AdresWystawcy:83-110 TCZEW, " ); // sw.WriteLine("KodWystawcy:83-110" ); // sw.WriteLine("MiastoWystawcy:" ); // sw.WriteLine("UlicaWystawcy:" ); // sw.WriteLine("NIPWystawcy:" ); // sw.WriteLine("BankWystawcy:" + dr.GetString(17)); // sw.WriteLine("KontoWystawcy:" + dr.GetString(18)); // sw.WriteLine("TelefonWystawcy:" + dr.GetString(19)); // sw.WriteLine("NrWystawcyWSieciSklepow:4"); // sw.WriteLine("NrWystawcyObcyWSieciSklepow:"); // sw.WriteLine("NazwaOdbiorcy:" + dr.GetString(21)); // sw.WriteLine("AdresOdbiorcy:" + dr.GetString(22)); // sw.WriteLine("KodOdbiorcy:" + dr.GetString(23)); // sw.WriteLine("MiastoOdbiorcy:" + dr.GetString(24)); // sw.WriteLine("UlicaOdbiorcy:" + dr.GetString(25)); // sw.WriteLine("NIPOdbiorcy:" + dr.GetString(26)); // sw.WriteLine("BankOdbiorcy:" + dr.GetString(27)); // sw.WriteLine("KontoOdbiorcy:" + dr.GetString(28)); // sw.WriteLine("TelefonOdbiorcy:" + dr.GetString(29)); // sw.WriteLine("NrOdbiorcyWSieciSklepow:" + dr.GetString(30)); sw.WriteLine("PoziomCen:Mag"); // sw.WriteLine("IloscLinii:3"); // cmd.Dispose(); // dr.Dispose(); cmd = cn.CreateCommand(); cmd.CommandText = "SELECT * FROM dane WHERE bad_stan = 1"; SqlCeDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { string cenasp = "0"; if (!dr.IsDBNull(5)) { cenasp = dr.GetString(5); } sw.WriteLine("Linia:Nazwa{}Kod{" + dr.GetString(1) + "}Vat{}Jm{}Asortyment{}Sww{}PKWiU{}Ilosc{" + dr.GetDecimal(11).ToString(nfi) + "}Cena{n" + dr.GetString(4) + "}Wartosc{n0.00}IleWOpak{1}" + "CenaSp{b" + cenasp + "}StanPocz{" + dr.GetString(3) + "}" + "TowID{}" + "\n\r"); } cn.Close(); sw.Close(); }