public Image GetLogo(int codEmp) { string sql = ""; Image LogoEmp = null; try { sql = "SELECT logoEmp FROM EMPRESA where EmpresaId = " + codEmp; SqlCeConnection conexao = new SqlCeConnection(Funcoes.Busca_Conexao()); SqlCeCommand cmd = new SqlCeCommand(sql, conexao); conexao.Open(); SqlCeDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { if (!string.IsNullOrEmpty(rd.GetValue(0).ToString())) { LogoEmp = Funcoes.GetImage((byte[])rd.GetValue(0)); } } conexao.Close(); return(LogoEmp); } catch (Exception ex) { throw ex; } }
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 Image GetImagePro(int codPro) { string sql = ""; Image Imagem = null; try { sql = "SELECT ImagProd FROM PRODUTO where ProdutoId = " + codPro; SqlCeConnection conexao = new SqlCeConnection(Funcoes.Busca_Conexao()); SqlCeCommand cmd = new SqlCeCommand(sql, conexao); conexao.Open(); SqlCeDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { if (!string.IsNullOrEmpty(rd.GetValue(0).ToString())) { Imagem = Funcoes.GetImage((byte[])rd.GetValue(0)); } } conexao.Close(); return(Imagem); } catch (Exception ex) { throw ex; } }
public ReportSpecific(int studNo) { InitializeComponent(); this.studNo = studNo; conn.Open(); using (SqlCeCommand cmd = new SqlCeCommand("SELECT LastName + ', ' + FirstName + ' ' + COALESCE(MiddleName, '') AS [Full Name], ResidenceStatus, CounterProbi FROM StudentInfo WHERE StudentNo = @studentNo", conn)) { cmd.Parameters.AddWithValue("@studentNo", studNo); using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)) { if (reader.Read()) { int fullNameIndex = reader.GetOrdinal("Full Name"); fullName = Convert.ToString(reader.GetValue(fullNameIndex)); int residenceIndex = reader.GetOrdinal("ResidenceStatus"); residence = Convert.ToString(reader.GetValue(residenceIndex)); int probiCountIndex = reader.GetOrdinal("CounterProbi"); probiCount = Convert.ToInt32(reader.GetValue(probiCountIndex)); } } } txtStudNo.Text = studNo.ToString(); txtResidence.Text = residence; txtFullName.Text = fullName; txtProb.Text = AddOrdinal(probiCount); updateListView(); }
private void txtName_TextChanged(object sender, TextChangedEventArgs e) { SqlCeConnection conn = DBUtils.GetDBConnection(); conn.Open(); using (SqlCeCommand cmd = new SqlCeCommand("SELECT LastName, firstName from StudentInfo WHERE (LastName LIKE @lastName ) or (firstName LIKE @firstName)", conn)) { cmd.Parameters.AddWithValue("@lastName", txtLastName.Text + "%"); cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text + "%"); using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)) { lvListStudent.Items.Clear(); i = 1; if (reader.HasRows) { while (reader.Read()) { int lastNameIndex = reader.GetOrdinal("LastName"); string lastName = Convert.ToString(reader.GetValue(lastNameIndex)); int firstNameIndex = reader.GetOrdinal("firstName"); string firstName = Convert.ToString(reader.GetValue(firstNameIndex)); lvListStudent.Items.Add(new ListViewSearchStudent { i = this.i, LastName = lastName, FirstName = firstName }); i++; } } } } conn.Close(); }
public void BackgroundChanger() { LinearGradientBrush gradient = new LinearGradientBrush(); gradient.StartPoint = new System.Windows.Point(0.5, 0); gradient.EndPoint = new System.Windows.Point(0.5, 1); var primaire = ""; try { SqlCeCommand cmd = new SqlCeCommand("SELECT PrimaireColor FROM Apps WHERE Name='" + NameExe.Content.ToString() + "';"); cmd.Connection = con; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.GetValue(0).ToString() != "") { primaire = reader.GetValue(0).ToString(); } } } finally { con.Close(); } var PictoColor = "#FF85C1F5"; if (primaire.ToString() != "") { PictoColor = "#" + primaire; } System.Windows.Media.Color color = (System.Windows.Media.Color)System.Windows.Media.ColorConverter.ConvertFromString(PictoColor.ToString()); System.Windows.Media.Color colorEnd = (System.Windows.Media.Color)System.Windows.Media.ColorConverter.ConvertFromString("#003D3D3D"); GradientStop Start = new GradientStop(); Start.Color = color; Start.Offset = 0; GradientStop End = new GradientStop(); End.Color = colorEnd; End.Offset = 0.75; gradient.GradientStops.Add(Start); gradient.GradientStops.Add(End); Main.Background = gradient; Storyboard sb = (Storyboard)FindResource("NewGradientColor"); sb.Begin(this); }
private Applicant GetFromReader(SqlCeDataReader reader) { var a = new Applicant { ap_id_10023 = Convert.ToInt32(reader.GetValue(0)), ap_name_10023 = reader.GetValue(1).ToString(), ap_score_10023 = Convert.ToInt32(reader.GetValue(2)), ap_tests_taken_10023 = reader.GetValue(3).ToString() }; return(a); }
private void updateRecords() { SqlCeConnection conn = DBUtils.GetDBConnection(); conn.Open(); borrowers.Clear(); using (SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT bl.prodCode, ai.name, ai.manuf, bl.qty, bl.breakage, ai.size from BorrowerList bl INNER JOIN ApparatusInventory ai on bl.prodCode = ai.prodCode where bl.studentNo = @studentNo and bl.groupID = @grpID and bl.lockNo = @lockNo and bl.subject = @subj and bl.expName = @experiment and bl.dateReq = @dateReq and bl.dateExp = @dateExp", conn)) { cmd.Parameters.AddWithValue("@studentNo", studentNo); cmd.Parameters.AddWithValue("@subj", txtSubj.Text); cmd.Parameters.AddWithValue("@grpID", txtGrpID.Text); cmd.Parameters.AddWithValue("@experiment", txtExpName.Text); cmd.Parameters.AddWithValue("@lockNo", txtLockNo.Text); cmd.Parameters.AddWithValue("@dateReq", txtDateReq.Text); cmd.Parameters.AddWithValue("@dateExp", txtDateExp.Text); using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)) { if (reader.HasRows) { while (reader.Read()) { int nameIndex = reader.GetOrdinal("name"); string name = Convert.ToString(reader.GetValue(nameIndex)); int manufIndex = reader.GetOrdinal("manuf"); string manuf = Convert.ToString(reader.GetValue(manufIndex)); int qtyIndex = reader.GetOrdinal("qty"); int qty = Convert.ToInt32(reader.GetValue(qtyIndex)); int sizeIndex = reader.GetOrdinal("size"); string size = Convert.ToString(reader.GetValue(sizeIndex)); int breakageIndex = reader.GetOrdinal("breakage"); bool breakage = Convert.ToBoolean(reader.GetValue(breakageIndex)); borrowers.Add(new LVBorrower { breakage = breakage, prodName = name, manuf = manuf, qty = qty, size = size }); } } else { this.NavigationService.Navigate(new BorrowersList()); } } } }
//Get Reader from private Applicant GetFromReader(SqlCeDataReader reader) { var a = new Applicant { Id = Convert.ToInt32(reader.GetValue(0)), Name = reader.GetValue(1).ToString(), Score = Convert.ToInt32(reader.GetValue(2)), Test = reader.GetValue(3).ToString() }; return(a); }
// private void LoadOneProduct( int intProductID) // { // // Append the desired ProductID to the SELECT statement. // string strSQL = strGetOneProduct + intProductID; // // // A connection, a command, and a reader. // SqlCeConnection connDB = new SqlCeConnection(strConn); // SqlCeCommand cmndDB = new SqlCeCommand(); // SqlCeDataReader drdrDB; // // // Open the connection. // connDB.Open(); // // // Submit the SQL statement and receive // // the SqlCeReader for the one-row // // results set. // drdrDB = cmndDB.ExecuteReader(); // // // Read the first (only) row. // // Display it. Close the reader. // if ( drdrDB.Read() ) // { // LoadControlsFromRow(drdrDB); // } // drdrDB.Close(); // // // Close the connection. // connDB.Close(); // } private void LoadControlsFromRow(SqlCeDataReader drdrDB) { // Transfer the colum titles and the field // contents of the current row from the // reader to the form//s controls. lblProductID.Text = drdrDB.GetName(0); textProductID.Text = drdrDB.GetValue(0).ToString(); lblProductName.Text = drdrDB.GetName(1); textProductName.Text = drdrDB.GetValue(1).ToString(); lblCategoryName.Text = drdrDB.GetName(2); textCategoryName.Text = drdrDB.GetValue(2).ToString(); }
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 Sprint GetFromReader(SqlCeDataReader reader) { var s = new Sprint { ID = Convert.ToInt32(reader.GetValue(0)), Name = reader.GetValue(1).ToString(), Date = Convert.ToDateTime(reader.GetValue(2)), NumberOfDevelopers = Convert.ToInt32(reader.GetValue(3)), Status = reader.GetValue(4).ToString() }; return(s); }
private void searchUser_MouseLeftButtonUp(object sender, MouseButtonEventArgs e) { if (string.IsNullOrEmpty(txtUser.Text)) { MessageBox.Show("Username field is empty!"); txtUser.Focus(); } else { SqlCeConnection conn = DBUtils.GetDBConnection(); conn.Open(); using (SqlCeCommand cmd = new SqlCeCommand("Select COUNT(1) from Accounts where username = @username", conn)) { cmd.Parameters.AddWithValue("@username", txtUser.Text); int userCount; userCount = (int)cmd.ExecuteScalar(); if (userCount > 0) { using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT * from Accounts where username = @username", conn)) { cmd1.Parameters.AddWithValue("@username", txtUser.Text); using (SqlCeDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable)) { if (reader.HasRows) { reader.Read(); int firstNameIndex = reader.GetOrdinal("firstName"); string firstName = Convert.ToString(reader.GetValue(firstNameIndex)); int lastNameIndex = reader.GetOrdinal("lastName"); string lastName = Convert.ToString(reader.GetValue(lastNameIndex)); int securityQuestionIndex = reader.GetOrdinal("securityQuestion"); string securityQuestion = Convert.ToString(reader.GetValue(securityQuestionIndex)); txtFirstName.Text = firstName; txtLastName.Text = lastName; cmbQuestion.Text = securityQuestion; } } } } else { MessageBox.Show("User does not exist!"); } } } }
private void PopulateList() { String query = "SELECT * FROM " + db.TableName + " ORDER BY date DESC"; using (SqlCeConnection c = new SqlCeConnection(DataSource)) { c.Open(); using (SqlCeCommand cmd = new SqlCeCommand(query, c)) { using (SqlCeDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { String id = "" + reader.GetInt32(0); String rupee = "" + reader.GetValue(1); String dateTime = "" + reader.GetDateTime(2); ListAdd(id, rupee, dateTime); } reader.Close(); } } c.Close(); } }
private void btn_write_to_file_Click(object sender, EventArgs e) { StringBuilder b = new StringBuilder(); String query = "SELECT * FROM " + db.TableName; using (SqlCeConnection c = new SqlCeConnection(DataSource)) { c.Open(); using (SqlCeCommand cmd = new SqlCeCommand(query, c)) { using (SqlCeDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { b.AppendLine("" + reader.GetValue(1)); } reader.Close(); } } c.Close(); } api.FileWrite("currencyList", b.ToString()); }
private void WriteTable(string directoryPath, string tableName, Action <string> updateStatus) { int count = 0; int total = ExecuteScalar <int>($"SELECT COUNT(*) FROM {tableName}"); using (StreamWriter writter = new StreamWriter($"{directoryPath}/{tableName}.CSV")) { using (SqlCeCommand command = CreateCommand($"SELECT * FROM {tableName}")) { using (SqlCeDataReader dataReader = command.ExecuteReader()) { IEnumerable <int> fieldRange = Enumerable.Range(0, dataReader.FieldCount); CsvWriter.WriteRow(writter, fieldRange.Select(i => dataReader.GetName(i).ToLower()).ToArray()); while (dataReader.Read()) { updateStatus($"Exportando tabela \"{tableName}\" - linha {++count} de {total}."); CsvWriter.WriteRow( writter, fieldRange.Select(i => dataReader.GetValue(i)) .Select(i => { if (i is bool) { return(((bool)i) ? "1" : "0"); } else { return(i.ToString()); } }) .ToArray() ); } } }; } }
public static bool LoggedUserCheckPermission() { SqlCeConnection sqlConnection = new SqlCeConnection(); sqlConnection.ConnectionString = ConnectionDBString; SqlCeCommand sqlCommand = new SqlCeCommand(); sqlCommand.CommandType = System.Data.CommandType.Text; sqlCommand.CommandText = "select * from Users where username='******'"; sqlCommand.Connection = sqlConnection; sqlConnection.Open(); SqlCeDataReader rd = sqlCommand.ExecuteReader(); rd.Read(); if (rd.GetValue(3).ToString() == "Admin") { return(true); } else { return(false); } rd.Close(); sqlConnection.Close(); }
/// <summary> /// vérifier le donnée a déjà exist dans la base de données /// </summary> /// <param name="Table">La table</param> /// <param name="Colonne">Le colonne de la table</param> /// <param name="Value">La valeur du colonne</param> /// <returns>bool</returns> private bool DonneeIsExist(string Table, string Colonne, string Value) { CmdRecherche = ConnSql.CreateCommand(); //Rechercher ID familles en relation dans table "Familles" CmdRecherche.CommandText = "SELECT COUNT( " + Colonne + " ) FROM " + Table + " WHERE " + Colonne + "=@Value;"; CmdRecherche.Parameters.AddWithValue("@Value", Value); CmdRecherche.ExecuteNonQuery(); RdrReche = CmdRecherche.ExecuteReader(); try { int Count = -1; if (RdrReche.Read()) { Count = (int)RdrReche.GetValue(0); } if (Count > 0) { return(true); } else { return(false); } } catch (Exception ex) { Console.Out.WriteLine(ex.Message); return(false); } }
public List <int> amountPerson(string table) { string source = "Data Source=\"PersonsDB.sdf\"; password=\"qwerty\""; con = new SqlCeConnection(source); con.Open(); var f = @"Select COUNT(*) From " + table + " Where ДатаПриема <= @Data and ДатаУвольнения is NULL"; cmd = new SqlCeCommand(f, con); cmd.Parameters.AddWithValue("@Data", SqlDbType.DateTime).Value = DateTime.Today.ToString("d"); SqlCeDataReader reader = cmd.ExecuteReader(); var numbers = new List <int>(); bool hasRow = reader.Read(); if (hasRow) { numbers.Add(Convert.ToInt32(reader.GetValue(0))); } reader.Close(); return(numbers); }
private void SettingsItem_Click(object sender, RoutedEventArgs e) { if (SettingsItem.Opacity != 0 && NameExe.Content.ToString() != "") { try { SqlCeCommand cmd = new SqlCeCommand("SELECT Id FROM Apps WHERE Name='" + NameExe.Content.ToString() + "';"); cmd.Connection = con; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.GetValue(0).ToString() != "") { Properties.Settings.Default.ExId = reader.GetInt32(0); } } } finally { con.Close(); } try { Properties.Settings.Default.ExNa = NameExe.Content.ToString(); Properties.Settings.Default.Save(); Settings settings = new Settings(); settings.Owner = this; settings.ShowDialog(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { if (Properties.Settings.Default.Saved == true) { NameExe.Content = ""; IconExe.Source = null; list(); Button button = new Button(); button.Name = "A" + Properties.Settings.Default.ExId.ToString(); button.Click += Button_Click; button.RaiseEvent(new RoutedEventArgs(Button.ClickEvent)); } Properties.Settings.Default.Saved = false; Properties.Settings.Default.ExId = 0; Properties.Settings.Default.ExNa = ""; Properties.Settings.Default.Save(); } } }
private void selectedUserCheck() { sqlConnection.ConnectionString = Auth.ConnectionDBString; sqlCommand.CommandType = System.Data.CommandType.Text; sqlCommand.CommandText = "select * from Users where username='******'"; sqlCommand.Connection = sqlConnection; sqlConnection.Open(); SqlCeDataReader rd = sqlCommand.ExecuteReader(); while (rd.Read()) { tbUsername.Text = rd.GetValue(0).ToString(); tbNames.Text = rd.GetValue(1).ToString(); tbPassword.Text = rd.GetValue(2).ToString(); tbAdmin.Text = rd.GetValue(3).ToString(); } sqlConnection.Close(); }
private void RefreshUsers() { listboxUsers.Items.Clear(); listboxInfo.Items.Clear(); sqlConnection.ConnectionString = Auth.ConnectionDBString; sqlCommand.CommandType = System.Data.CommandType.Text; sqlCommand.CommandText = "SELECT * FROM Users"; sqlCommand.Connection = sqlConnection; sqlConnection.Open(); SqlCeDataReader rd = sqlCommand.ExecuteReader(); while (rd.Read()) { listboxUsers.Items.Add(rd.GetValue(0).ToString()); listboxInfo.Items.Add("Names: " + rd.GetValue(1).ToString() + " ,Password: "******" ,Permission: " + rd.GetValue(3).ToString()); } sqlConnection.Close(); }
private void btnRefresh_Click(object sender, EventArgs e) { string SqlCeQuery = "SELECT * FROM tblUsers"; SqlCeCommand cm = new SqlCeCommand(SqlCeQuery, cn); SqlCeDataReader dr = cm.ExecuteReader(); lbId.Items.Clear(); lbuserName.Items.Clear(); lbPass.Items.Clear(); lbEmail.Items.Clear(); while (dr.Read()) { lbuserName.Items.Add(dr.GetValue(0).ToString()); lbPass.Items.Add(dr.GetValue(2).ToString()); lbEmail.Items.Add(dr.GetValue(3).ToString()); lbId.Items.Add(dr.GetValue(4).ToString()); } }
private void ClAd_Click(object sender, RoutedEventArgs e) { try { SqlCeCommand cmd = new SqlCeCommand("SELECT DISTINCT Class FROM Apps WHERE Class = '" + ClNa.Text + "';"); cmd.Connection = con; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.GetValue(0).ToString() != "") { exists = true; } } } finally { con.Close(); } if ((bool)exists == false) { try { SqlCeCommand cmd = new SqlCeCommand("UPDATE Apps SET Class = '" + ClNa.Text + "' WHERE Id = '" + ExId + "';"); cmd.Connection = con; if (con.State == ConnectionState.Closed) { con.Open(); } cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { con.Close(); Start(); ClNa.Text = ""; } Properties.Settings.Default.Saved = true; Properties.Settings.Default.Save(); } else { MessageBox.Show("This class already exists. Please type a different name."); exists = false; } }
public string DoQuery(string query, bool ret) { if (!ret) { try { using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\Андрей свали с компа\Desktop\myDB.sdf")) { conn.Open(); using (SqlCeCommand c = new SqlCeCommand(query)) { c.Connection = conn; c.ExecuteNonQuery(); } } return("Execution completed sucsessful"); } catch (Exception exp) { return("Excecution error: " + exp.Message); } } else { try { string s = ""; using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\Андрей свали с компа\Desktop\myDB.sdf")) { conn.Open(); using (SqlCeCommand c = new SqlCeCommand(query)) { c.Connection = conn; SqlCeDataReader sql = c.ExecuteReader(); while (sql.Read()) { for (int i = 0; i < sql.FieldCount; i++) { s += sql.GetValue(i).ToString() + " "; } s += Environment.NewLine; } sql.Close(); } } return(s); } catch (Exception exp) { return("Excecution error: " + exp.Message); } } }
private void save_Click(object sender, RoutedEventArgs e) { try { SqlCeCommand cmd = new SqlCeCommand("SELECT * FROM Apps WHERE Name = '" + NewName.Text + "';"); cmd.Connection = con; if (con.State == ConnectionState.Closed) { con.Open(); } SqlCeDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (reader.GetValue(2).ToString() != "") { exists = true; } } } finally { con.Close(); } if ((bool)exists == false || NewName.Text == ExNa) { try { SqlCeCommand cmd = new SqlCeCommand("UPDATE Apps SET Name = '" + NewName.Text.ToString() + "', Class = '" + Class.SelectedItem.ToString() + "' WHERE Id = '" + ExId + "';"); cmd.Connection = con; if (con.State == ConnectionState.Closed) { con.Open(); } cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { con.Close(); } Properties.Settings.Default.Saved = true; Properties.Settings.Default.Save(); this.Close(); } else { MessageBox.Show("This name is already used. Please choose a different."); } exists = false; }
private void updateListView() { SqlCeConnection conn = DBUtils.GetDBConnection(); conn.Open(); using (SqlCeCommand cmd = new SqlCeCommand("SELECT * from ApparatusInventory", conn)) { summary.Clear(); using (SqlCeDataReader reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)) { while (reader.Read()) { int prodCodeIndex = reader.GetOrdinal("prodCode"); string prodCode = Convert.ToString(reader.GetValue(prodCodeIndex)); int inventNameIndex = reader.GetOrdinal("name"); string inventName = Convert.ToString(reader.GetValue(inventNameIndex)); int manufIndex = reader.GetOrdinal("manuf"); string manuf = Convert.ToString(reader.GetValue(manufIndex)); int qtyIndex = reader.GetOrdinal("qty"); int qty = Convert.ToInt32(reader.GetValue(qtyIndex)); int sizeIndex = reader.GetOrdinal("size"); string size = Convert.ToString(reader.GetValue(sizeIndex)); summary.Add(new LVOutstanding { i = i, prodCode = prodCode, inventName = inventName, manuf = manuf, qty = qty.ToString(), size = size, }); i++; } } } }
/* private bool executaComando() * { * string localBD = PegaCaminhoBD(); * using (SqlCeConnection conexao = new SqlCeConnection("Data Source="+localBD)) * { * SqlCeCommand comando; * conexao.Open(); * comando.Connection = conexao; * if (comando.ExecuteNonQuery() > 0) * { * return true; * } * else * { * return false; * } * } * }*/ public Usuarios VerificaLogin(string usuario, string senha) { string localBD = PegaCaminhoBD(); Usuarios ListaDeUsuarios = null; using (SqlCeConnection conexao = new SqlCeConnection("Data Source=" + localBD)) { SqlCeCommand comando = new SqlCeCommand("SELECT * FROM Usuarios WHERE Login = @login AND Senha = @senha"); comando.Parameters.AddWithValue("login", usuario); comando.Parameters.AddWithValue("senha", senha); conexao.Open(); comando.Connection = conexao; SqlCeDataReader leitor = comando.ExecuteReader(); while (leitor.Read()) { ListaDeUsuarios = new Usuarios { Id = (int)leitor.GetValue(0), Nome = leitor.GetValue(1).ToString(), Cpf = leitor.GetValue(2).ToString(), Login = leitor.GetValue(3).ToString(), Senha = leitor.GetValue(4).ToString(), Acesso = leitor.GetValue(5).ToString() }; } leitor.Close(); } return(ListaDeUsuarios); }
public void refreshContacts() { listboxUsers.Items.Clear(); listboxPass.Items.Clear(); listboxNames.Items.Clear(); listboxEmail.Items.Clear(); listboxAdmin.Items.Clear(); conn.ConnectionString = ConnectionDBString; cmd = new SqlCeCommand("SELECT * FROM UsersCEDB", conn); conn.Open(); SqlCeDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { listboxUsers.Items.Add(rd.GetValue(0).ToString()); listboxNames.Items.Add(rd.GetValue(1).ToString()); listboxPass.Items.Add(rd.GetValue(2).ToString()); listboxEmail.Items.Add(rd.GetValue(3).ToString()); if (rd.GetValue(4).ToString() == "2") { listboxAdmin.Items.Add("Admin"); } else if (rd.GetValue(4).ToString() == "1") { listboxAdmin.Items.Add("Member"); } else { listboxAdmin.Items.Add("Public"); } } conn.Close(); }
private void btnSpeGenReport_Click(object sender, RoutedEventArgs e) { SqlCeConnection conn = DBUtils.GetDBConnection(); conn.Open(); using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(1) from StudentInfo WHERE (lastName = @LastName) and (firstName = @firstName)", conn)) { cmd.Parameters.AddWithValue("@lastName", txtLastName.Text); cmd.Parameters.AddWithValue("@firstName", txtFirstName.Text); if (string.IsNullOrEmpty(txtLastName.Text) && string.IsNullOrEmpty(txtFirstName.Text)) { MessageBox.Show("No user input!"); } else if (string.IsNullOrEmpty(txtLastName.Text)) { MessageBox.Show("Please fill up the missing fields!"); txtLastName.Focus(); } else if (string.IsNullOrEmpty(txtFirstName.Text)) { MessageBox.Show("Please fill up the missing fields!"); txtFirstName.Focus(); } else { int studCount; studCount = (int)cmd.ExecuteScalar(); if (studCount > 0) { using (SqlCeCommand cmd1 = new SqlCeCommand("SELECT StudentNo from StudentInfo WHERE (lastName = @LastName) and (firstName = @firstName)", conn)) { cmd1.Parameters.AddWithValue("@lastName", txtLastName.Text); cmd1.Parameters.AddWithValue("@firstName", txtFirstName.Text); using (SqlCeDataReader reader = cmd1.ExecuteResultSet(ResultSetOptions.Scrollable)) { reader.Read(); int studNo = Convert.ToInt32(reader.GetValue(0)); ReportSpecific rs = new ReportSpecific(studNo); rs.studNo = studNo; rs.ShowDialog(); } } } else { MessageBox.Show("Student does not exist!"); } } } conn.Close(); }
void ActivateNewDeal(int newline, int level, bool loadFromDB, SqlCeDataReader sqlReader) { // ----------- 1. Создание сдачи в БД / загрузка сдачи из БД + REFLECT -------------- if (!loadFromDB) { // !!! Выполнить инициализацию данных, помеченных в REFLECT_Depends со значением {-1} // !!! Обычно это зона и порядковые номера // !!! При sub-deal для 1 стола -1, для 2 стола -2 и т.д. REFLECT_DEPENDENCES(newline, (this.isSplit ? -(level + 1) : -1)); } else { ArrayOfInt poss = new ArrayOfInt(); //колонки, в которые будут загружены данные; список всегда начинается с -1 (-2) poss.Add((this.isSplit ? -(level + 1) : -1)); for (int i = 0; i < DB_Deals_ColumnsNames.Keys.Count; i++) { int colPos = DB_Deals_ColumnsNames.Keys.ElementAt(i); //VALUES[newline, colPos] if (this.isSplit) { if (SUBDEALS_WhatLevel(colPos) == level || SUBDEALS_WhatLevel(colPos) == -1 && level == 0) { //ok } else continue; } // *** Загрузить из БД *** poss.Add(colPos); object val = sqlReader.GetValue(sqlReader.GetOrdinal(DB_Deals_ColumnsNames[colPos])); (VALUES[newline, colPos] as ISQLSerialize)._FromDataBase(val); } // Одним махом REFLECT для poss[] - т.е. для начала (-1, -2 для subdeal) и для загруженных данных REFLECT_DEPENDENCES(newline, poss); } // -------------- 2. Активация изменений данных: Changed -> SAVE, REFLECT ---------------- for (int i = 0; i < columnsDataClasses.Count; i++) { if (this.isSplit) { if (SUBDEALS_WhatLevel(i) == level || SUBDEALS_WhatLevel(i) == -1 && level == 0) { //ok } else continue; } // >>>>>>>>> Сначала SAVE, потом REFLECT <<<<<<<<<<<<<< // delme!!! if (DB_Deals_ColumnsNames.Keys.Contains(i)) (VALUES[newline, i] as BaseChangedData).Changed += OnInputChanged_SaveToDataBase; (VALUES[newline, i] as BaseChangedData).Changed += OnInputChanged; } // ----------------- 3. Присоединение данных к контролам ----------------- int startControlIndex; int endControlIndex; if (!this.isSplit || this.isSplit && level == 0) { startControlIndex = 0; endControlIndex = columnsControlsClasses.Count - 1; } else { startControlIndex = columnsControlsClasses.Count + (level - 1) * SUBDEALS_CONTROLS_GetSpliColumnsCount(); endControlIndex = startControlIndex + SUBDEALS_CONTROLS_GetSpliColumnsCount() - 1; } for (int i = startControlIndex; i <= endControlIndex; i++) { int columnIndex = (!this.isSplit ? i : SUBDEALS_CONTROLS_Get_Split_Coordinates(i).column); MethodInfo method; Type[] types; object[] parameters; int params_count = 0; //для sub-deal if (!this.isSplit) { types = new Type[CoVa_Dependences[columnIndex].Count]; for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++) types[j] = VALUES[newline, CoVa_Dependences[columnIndex, j]].GetType(); } else { params_count = CoVa_Dependences[columnIndex].Count + (CoVa_Dependences_SPLIT[columnIndex].Count / SUBDEALS.Count); types = new Type[params_count]; int ind = 0; for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++) { types[ind++] = VALUES[newline, CoVa_Dependences[columnIndex, j]].GetType(); } for (int j = 0; j < CoVa_Dependences_SPLIT[columnIndex].Count; j++) { int l = SUBDEALS_WhatLevel(CoVa_Dependences_SPLIT[columnIndex, j]); if (l == level) types[ind++] = VALUES[newline, CoVa_Dependences_SPLIT[columnIndex, j]].GetType(); } } method = CONTROLS[newline, i].GetType().GetMethod("AttachData", types); if (method != null) { if (!this.isSplit) { parameters = new object[CoVa_Dependences[columnIndex].Count]; for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++) parameters[j] = VALUES[newline, CoVa_Dependences[columnIndex, j]]; } else { parameters = new object[params_count]; int ind = 0; for (int j = 0; j < CoVa_Dependences[columnIndex].Count; j++) { parameters[ind++] = VALUES[newline, CoVa_Dependences[columnIndex, j]]; } for (int j = 0; j < CoVa_Dependences_SPLIT[columnIndex].Count; j++) { int l = SUBDEALS_WhatLevel(CoVa_Dependences_SPLIT[columnIndex, j]); if (l == level) parameters[ind++] = VALUES[newline, CoVa_Dependences_SPLIT[columnIndex, j]]; } } method.Invoke(CONTROLS[newline, i], parameters); } } }
private object DataReaderToObject(SqlCeDataReader reader, EntityType entity) { object entityObject = null; switch (entity) { case EntityType.Processor: entityObject = new EntProcessor(); break; case EntityType.OS: entityObject = new EntOS(); break; case EntityType.Bios: entityObject = new EntBios(); break; case EntityType.MotherBoard: entityObject = new EntMotherBoard(); break; case EntityType.Disk: entityObject = new EntDisk(); break; case EntityType.Memory: entityObject = new EntMemory(); break; case EntityType.LogicalDrive: entityObject = new EntLogicalDrive(); break; case EntityType.CDRom: entityObject = new EntCDRom(); break; case EntityType.Video: entityObject = new EntVideo(); break; case EntityType.Multimedia: entityObject = new EntMultimedia(); break; case EntityType.Monitor: entityObject = new EntMonitor(); break; case EntityType.Share: entityObject = new EntShare(); break; case EntityType.StartUp: entityObject = new EntStartUp(); break; case EntityType.Hotfix: entityObject = new EntHotfixes(); break; case EntityType.Processes: entityObject = new EntProcesses(); break; case EntityType.Softwares: entityObject = new EntSoftwares(); break; case EntityType.Services: entityObject = new EntServices(); break; case EntityType.IPRoutes: entityObject = new EntIPRoutes(); break; case EntityType.EnvironmentVar: entityObject = new EntEnvironmentVars(); break; case EntityType.Computer: entityObject = new EntComputer(); break; case EntityType.Printer: entityObject = new EntPrinter(); break; case EntityType.UserGroup: entityObject = new EntUserGroups(); break; case EntityType.NetworkAdapter: entityObject = new EntNetworkAdapter(); break; } Type t = entityObject.GetType(); PropertyInfo[] pi = t.GetProperties(); foreach (PropertyInfo prop in pi) { if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName") continue; switch (prop.PropertyType.Name) { case "String": string strValue = reader.GetString(reader.GetOrdinal(prop.Name)); prop.SetValue(entityObject, strValue, null); break; case "Int32": int intValue = reader.GetInt32(reader.GetOrdinal(prop.Name)); prop.SetValue(entityObject, intValue, null); break; case "Double": double dValue = reader.GetDouble(reader.GetOrdinal(prop.Name)); prop.SetValue(entityObject, dValue, null); break; case "DateTime": DateTime dtValue = DateTime.MinValue; if ( reader.GetValue(reader.GetOrdinal(prop.Name))== DBNull.Value) { dtValue = DateTime.MinValue; } else { dtValue = reader.GetDateTime(reader.GetOrdinal(prop.Name)); } prop.SetValue(entityObject, dtValue, null); break; case "Boolean": bool bValue = reader.GetBoolean(reader.GetOrdinal(prop.Name)); prop.SetValue(entityObject, bValue, null); break; } } return entityObject; }
// // GetUserFromReader // A helper function that takes the current row from the SqlCeDataReader // and hydrates a MembershipUser from the values. Called by the // MembershipUser.GetUser implementation. // private MembershipUser GetUserFromReader(SqlCeDataReader reader) { if (string.IsNullOrWhiteSpace(reader.GetString(1))) return null; object providerUserKey = null; string strGooid = Guid.NewGuid().ToString(); if (reader.GetValue(0).ToString().Length > 0) providerUserKey = new Guid(reader.GetValue(0).ToString()); else providerUserKey = new Guid(strGooid); string username = reader.GetString(1); string email = reader.GetString(2); string passwordQuestion = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); string comment = reader.IsDBNull(4) ? string.Empty : reader.GetString(4); bool isApproved = reader.IsDBNull(5) ? false : reader.GetBoolean(5); bool isLockedOut = reader.IsDBNull(6) ? false : reader.GetBoolean(6); DateTime creationDate = reader.IsDBNull(7) ? DateTime.UtcNow : reader.GetDateTime(7); DateTime lastLoginDate = reader.IsDBNull(8) ? DateTime.UtcNow : reader.GetDateTime(8); DateTime lastActivityDate = reader.IsDBNull(9) ? DateTime.UtcNow : reader.GetDateTime(9); DateTime lastPasswordChangedDate = reader.IsDBNull(10) ? DateTime.UtcNow : reader.GetDateTime(10); DateTime lastLockedOutDate = reader.IsDBNull(11) ? DateTime.UtcNow : reader.GetDateTime(11); MembershipUser u = new MembershipUser(this.Name, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate); return u; }
/// <summary> /// Converts a SqlCeDataReader to a DataSet /// <param name='reader'> /// SqlDataReader to convert.</param> /// <returns> /// DataSet filled with the contents of the reader.</returns> /// </summary> public static DataSet ConvertDataReaderToDataSet(SqlCeDataReader reader, string tabela) { DataSet dataSet = new DataSet(); do { // Create new data table DataTable schemaTable = reader.GetSchemaTable(); DataTable dataTable = new DataTable(tabela); if (schemaTable != null) { // A query returning records was executed for (int i = 0; i < schemaTable.Rows.Count; i++) { DataRow dataRow = schemaTable.Rows[i]; // Create a column name that is unique in the data table string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>"; // Add the column definition to the data table DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]); dataTable.Columns.Add(column); } dataSet.Tables.Add(dataTable); // Fill the data table we just created while (reader.Read()) { DataRow dataRow = dataTable.NewRow(); for (int i = 0; i < reader.FieldCount; i++) dataRow[i] = reader.GetValue(i); dataTable.Rows.Add(dataRow); } } else { // No records were returned DataColumn column = new DataColumn("RowsAffected"); dataTable.Columns.Add(column); dataSet.Tables.Add(dataTable); DataRow dataRow = dataTable.NewRow(); dataRow[0] = reader.RecordsAffected; dataTable.Rows.Add(dataRow); } } while (reader.NextResult()); return dataSet; }
public static IBaseObject Reader(SqlCeDataReader reader, string entity) { Assembly entityImpl = Assembly.GetAssembly(typeof (BaseObject)); string entityName = entityImpl.FullName.Substring(0, entityImpl.FullName.IndexOf(",")).Trim() + "." + entity; Type type = entityImpl.GetType(entityName); object obj = entityImpl.CreateInstance(entityName); foreach (PropertyInfo p in type.GetProperties()) { try { int index; object value; if (p.PropertyType == typeof(ITeacher)) { index = reader.GetOrdinal("Teacher_Id"); using (Command cmd = new Command("init_Teacher")) { try { string id = Convert.ToString(reader.GetValue(index)); value = UserGateway.Get(cmd, id, Role.Teacher) as ITeacher; } catch { value = null; } } } else if (p.PropertyType == typeof(ICourse)) { index = reader.GetOrdinal("Course_Id"); using (Command cmd = new Command("init_Course")) { try { string id = Convert.ToString(reader.GetValue(index)); value = CourseGateway.Get(cmd, id); } catch { value = null; } } } else if (p.PropertyType == typeof(IStudent)) { index = reader.GetOrdinal("Student_Id"); using (Command cmd = new Command("init_Student")) { try { string id = Convert.ToString(reader.GetValue(index)); value = UserGateway.Get(cmd, id, Role.Student) as IStudent; } catch { value = null; } } } else { index = reader.GetOrdinal(p.Name); value = reader.GetValue(index); value = value is DBNull ? null : value; } p.SetValue(obj, value, null); } catch { continue; } } return obj as IBaseObject; }
private void PopulateEntity(Entity entity, SqlCeDataReader reader) { entity.Id = reader.GetValue(reader.GetOrdinal("Id")).ToString(); entity.Code = (string)reader.GetValue(reader.GetOrdinal("Code")); entity.Label = (string)reader.GetValue(reader.GetOrdinal("Label")); entity.Data1 = (string)reader.GetValue(reader.GetOrdinal("Data1")); entity.Data2 = (string)reader.GetValue(reader.GetOrdinal("Data2")); entity.Data3 = (string)reader.GetValue(reader.GetOrdinal("Data3")); }