public static void assignDonor(PatientInfo patientData) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "UPDATE patient SET assigned_donor=@assigned_donor, assigned_donor_contact = @assigned_donor_contact WHERE patient_id=@patient_id"; msqlCommand.Parameters.AddWithValue("@patient_id", patientData.id); msqlCommand.Parameters.AddWithValue("@assigned_donor", patientData.assignedDonor); msqlCommand.Parameters.AddWithValue("@assigned_donor_contact", patientData.donorContact); msqlCommand.ExecuteNonQuery(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
private void EditSptInfo(SettingsData returnEditedsettingsData) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = null; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); int idSptinfo = 1; msqlCommand.CommandText = "UPDATE sptinfo SET name='" + returnEditedsettingsData.Name + "', address='" + returnEditedsettingsData.Address + "', phone='" + returnEditedsettingsData.Phone + "', bill_disclaimer='" + returnEditedsettingsData.BillDisclaimer + "', invoice_prefix='" + returnEditedsettingsData.InvoicePrefix + "' WHERE id_sptinfo='" + idSptinfo + "'; "; msqlCommand.ExecuteNonQuery(); } catch (Exception er) { MessageBox.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } }
public static void assignEstimator(estimateInfo estimateToEdit) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "UPDATE estimate SET estimator=@estimator WHERE appNo=@appNo"; msqlCommand.Parameters.AddWithValue("@estimator", estimateToEdit.estimator); msqlCommand.Parameters.AddWithValue("@appNo", estimateToEdit.appsNo); msqlCommand.ExecuteNonQuery(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
private void DeleteStock(string stockToDelete) { msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); msqlCommand.CommandText = "DELETE FROM stock WHERE id= @vendorIdToDelete"; msqlCommand.Parameters.AddWithValue("@vendorIdToDelete", stockToDelete); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); } catch (Exception er) { MessageBox.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } }
public static string FetchePassword() { string passwordStr = string.Empty; MySql.Data.MySqlClient.MySqlConnection msqlConnection = null; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); msqlCommand.CommandText = "Select password from sptinfo;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); msqlReader.Read(); passwordStr = msqlReader.GetString("password"); } catch (Exception er) { //Assert//.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } return passwordStr; }
public static void EditSptPassword(string passwordStr) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = null; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); int idSptinfo = 1; msqlCommand.CommandText = "UPDATE sptinfo SET password='******' WHERE id_sptinfo='" + idSptinfo + "'; "; msqlCommand.ExecuteNonQuery(); } catch (Exception er) { //MessageBox.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } }
public static void ConnectInsertTocustomerPaymentTable(CustomerPaymentData customerDataObject) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; FeedcustomerData(msqlCommand, customerDataObject); } catch (Exception er) { } finally { msqlConnection.Close(); } }
void StateChanged_Executed(object sender, ExecutedEventArgs e) { string messgae = "불러오기를 완료하였습니다."; AMR_MST04Model model = new AMR_MST04Model(); model = (AMR_MST04Model)e.Parameter; MySqlManage crud = new MySqlManage(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString); DataSet ds = new DataSet(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.CommandText = "CALL sp_load(@sno, @datefrom, @dateto);"; cmd.Parameters.AddWithValue("@sno", model.MST04SNO); cmd.Parameters.AddWithValue("@datefrom", model.From.ToString("yyyy-MM-dd") + " 00"); cmd.Parameters.AddWithValue("@dateto", model.To.ToString("yyyy-MM-dd") + " 00"); ds = crud.CallSPMariaDBTable(crud.Connection, cmd); if (ds.Tables.Count > 0) { model.DataTable = ds.Tables[0]; CurrentForm.SearchComplete(model); } CurrentForm.ShowMessage(messgae); }
public bool Register_AfterCheck(string Username, string Password, string Email, string FullName) { MySql.Data.MySqlClient.MySqlConnection con = null; Database.Configuration.open(ref con); string sql = @" INSERT INTO `users`(`level`, `state`, `username`, `email`, `password`, `realname`) VALUES (@level, @state, @username, @email, @password, @realname)"; var defaultLevel = Util.Config.Get().GetInt32("registration_default_authlevel", 2); var defaultState = Util.Config.Get().GetInt32("registration_default_state", 0); using (var rdr = new MySql.Data.MySqlClient.MySqlCommand(sql, con)) { rdr.Parameters.AddWithValue("@level", defaultLevel); rdr.Parameters.AddWithValue("@state", defaultState); rdr.Parameters.AddWithValue("@username", Username); rdr.Parameters.AddWithValue("@realname", FullName); rdr.Parameters.AddWithValue("@email", Email); rdr.Parameters.AddWithValue("@password", Password); try { rdr.ExecuteNonQuery(); // Let it throw a error so i can see if it works } catch(Exception ex) { throw; return false; } } con.Close(); return true; }
public CityForeCast[] GetForecastFromDatabase() { //SqlConnection connection = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDb;Initial Catalog=weatherForecast;Integrated Security=True;Pooling=False"); MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(@"Server=eu-cdbr-azure-west-c.cloudapp.net;Database=BDMeteo;Uid=b95badd8e1dbad;Pwd=bde4c7b6;Pooling=True"); connection.Open(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SELECT * FROM tablemeteo", connection); MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader(); List<CityForeCast> forecasts = new List<CityForeCast>(); while (reader.Read()) { CityForeCast forecast = new CityForeCast(); forecast.City = (string)reader["City"]; forecast.description = (string)reader["Description"]; forecast.MaxTemp = (decimal)reader["Temperature"]; forecasts.Add(forecast); } reader.Close(); // Fermer le reader avant de fermer la connection connection.Close(); return forecasts.ToArray(); //var ctx = new weatherForecastEntities(); //var forecast = ctx.Tables.Select(f => new CityForeCast() //{ // City = f.City, // description = f.Description, // MaxTemp = (decimal)f.Temperature //}); //return forecast; }
public static void ConnectInsertToDoListTable(ToDoData tdData) { //define the connection reference and initialize it MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "INSERT INTO to_do(date_time,to_do,id)" + "VALUES(@date_time,@to_do,@id)"; msqlCommand.Parameters.AddWithValue("@date_time", tdData.date_time); msqlCommand.Parameters.AddWithValue("@to_do", tdData.to_do); msqlCommand.Parameters.AddWithValue("@id", tdData.id); msqlCommand.ExecuteNonQuery(); } catch (Exception er) { } finally { //always close the connection msqlConnection.Close(); } }
/// <summary> /// Для выполнения запросов к MySQL с возвращением 1 параметра. /// </summary> /// <param name="sql">Текст запроса к базе данных</param> /// <param name="connection">Строка подключения к базе данных</param> /// <returns>Возвращает значение при успешном выполнении запроса, текст ошибки - при ошибке.</returns> public static MyResult SqlScalar(string sql, string connection) { MyResult result = new MyResult(); try { MySql.Data.MySqlClient.MySqlConnection connRC = new MySql.Data.MySqlClient.MySqlConnection(connection); MySql.Data.MySqlClient.MySqlCommand commRC = new MySql.Data.MySqlClient.MySqlCommand(sql, connRC); connRC.Open(); try { result.ResultText = commRC.ExecuteScalar().ToString(); result.HasError = false; } catch (Exception ex) { result.ErrorText = ex.Message; result.HasError = true; } connRC.Close(); } catch (Exception ex)//Этот эксепшн на случай отсутствия соединения с сервером. { result.ErrorText = ex.Message; result.HasError = true; } return result; }
public bool AddAgent(string name, string surname, string phone, string email, string password) { DatabaseManager dbManager = new DatabaseManager(); Cryptography crypto = new Cryptography(); MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand(); return (dbManager.NonReturnQuery("INSERT INTO Agent (Agent_Name , Agent_Surname, Agent_Phone, Agent_Email, Agent_Password) VALUES ('" + name + "','" + surname + "','" + phone + "','" + email + "','" + Cryptography.CreateHash(password).ToString() + "');")); }
private void buttonOK_Click(object sender, EventArgs e) { var conn = this.dataset.CreateSQLConnection(); conn.Open(); var query = new MySql.Data.MySqlClient.MySqlCommand(String.Format("call settlement('{0}')",dateTimePickerSettleDate.Value.ToString("yyyy-MM-dd")), conn); query.ExecuteNonQuery(); this.Close(); }
public static int Execute(string sql) { using (var conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectString())) { conn.Open(); var cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn); int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } }
public override void SendClientMessage(int Type, string MessageValue) { MySql.Data.MySqlClient.MySqlCommand command = new MySql.Data.MySqlClient.MySqlCommand(); command.CommandText = "INSERT INTO messages (SessionToken, Type, MessageValue, DateCreated) VALUES (@SessionToken, @Type, @MessageValue, @DateCreated);"; command.Parameters.AddWithValue("@SessionToken", Program.sessionToken); command.Parameters.AddWithValue("@Type", Type); command.Parameters.AddWithValue("@MessageValue", MessageValue); command.Parameters.AddWithValue("@DateCreated", CommonCommunicator.UnixTime()); Database.Command(command); command.Dispose(); }
public static DataTable GetRemoteDataTable(string command, MySql.Data.MySqlClient.MySqlConnection msqConn) { DataTable rdt = new DataTable(); try { msqConn.Open(); MySql.Data.MySqlClient.MySqlCommand uploaderCmd = new MySql.Data.MySqlClient.MySqlCommand(command, msqConn); MySql.Data.MySqlClient.MySqlDataReader onj = uploaderCmd.ExecuteReader(); bool colWasPerf = false; int colIdx = 0; List<object> rowValues = new List<object>(); foreach (System.Data.Common.DbDataRecord ro in onj) { try { // get fields if (rdt.Columns.Count == 0 && !colWasPerf) { for (colIdx = 0; colIdx < ro.FieldCount; colIdx++) rdt.Columns.Add(ro.GetName(colIdx)); colWasPerf = true; } } catch { } try { for (colIdx = 0; colIdx < ro.FieldCount; colIdx++) rowValues.Add(ro[colIdx]); } catch { } try { rdt.Rows.Add(rowValues.ToArray()); rowValues.Clear(); } catch { } } onj.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } if (msqConn.State == System.Data.ConnectionState.Open) msqConn.Close(); return rdt; }
private void ConnectFetchFromSaleslistTable() { //define the connection reference and initialize it msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; //open the connection if (msqlConnection.State != System.Data.ConnectionState.Open) msqlConnection.Open(); TimeSpan diff = (TimeSpan)(endDatePicker.SelectedDate - startDatePicker.SelectedDate); msqlCommand.CommandText = "SELECT * FROM saleslist where date(saleslist.dateSales) >= DATE_SUB( @enddate, INTERVAL @diff DAY);"; msqlCommand.Parameters.AddWithValue("@enddate", endDatePicker.SelectedDate); msqlCommand.Parameters.AddWithValue("@diff", diff.Days); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); _salesDataCollection.Clear(); while (msqlReader.Read()) { SalesData salesData = new SalesData(); salesData.customerId = msqlReader.GetString("customerId"); salesData.customerName = msqlReader.GetString("customerName"); //add salesData.dateSales = msqlReader.GetDateTime("dateSales"); salesData.invoiceNo = msqlReader.GetString("invoiceNo"); salesData.payment = msqlReader.GetDouble("payment"); salesData.totalAmount = msqlReader.GetDouble("totalAmount"); //salesData.serialNo = (_salesDataCollection.Count + 1).ToString(); _salesDataCollection.Add(salesData); } } catch (Exception er) { MessageBox.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } }
private void ConnectFetchFromSaleslistTable(string invoiceNumber) { //define the connection reference and initialize it msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; //open the connection if (msqlConnection.State != System.Data.ConnectionState.Open) msqlConnection.Open(); msqlCommand.CommandText = "SELECT * FROM salesBilling WHERE invoiceNo = @invoiceNo;"; msqlCommand.Parameters.AddWithValue("@invoiceNo", invoiceNumber); MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); double totalVat = 0.0; while (msqlReader.Read()) { BillingData billedData = new BillingData(); billedData.amount = msqlReader.GetDouble("amount"); billedData.productName = msqlReader.GetString("description"); billedData.quantity = msqlReader.GetDouble("quantity"); billedData.vat = msqlReader.GetDouble("vat"); billedData.calVat = Convert.ToDouble(billedData.amount) * Convert.ToDouble(billedData.vat) * (.01); ; totalVat += billedData.calVat; billedData.rate = msqlReader.GetDouble("rate"); billedData.serialNo = billingItemListView.Items.Count + 1; //msqlReader.GetString("serialNo"); _billingCollection.Add(billedData); vatAmount.Content = totalVat; } } catch (Exception er) { MessageBox.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } }
public override Bitmap LoadImage(int image_id) { MySql.Data.MySqlClient.MySqlDataReader myData; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); string SQL; byte[] rawData; MemoryStream ms; UInt32 FileSize; Bitmap outImage; SQL = "SELECT image_name, image_size, image_data FROM images WHERE id ="; SQL += image_id.ToString(); try { cmd.Connection = Connection; cmd.CommandText = SQL; myData = cmd.ExecuteReader(); if (!myData.HasRows) throw new Exception("There are no blobs to save"); myData.Read(); FileSize = myData.GetUInt32(myData.GetOrdinal("image_size")); rawData = new byte[FileSize]; myData.GetBytes(myData.GetOrdinal("image_data"), 0, rawData, 0, (Int32)FileSize); ms = new MemoryStream(rawData); outImage = new Bitmap(ms); ms.Close(); ms.Dispose(); myData.Close(); myData.Dispose(); cmd.Dispose(); return outImage; } catch (MySql.Data.MySqlClient.MySqlException ex) { return null; } }
/// <summary> /// Loads XML from local database /// </summary> public void ImportFromMySQL(Database.DatabaseConfig xmlSource, Database.DatabaseConfig destination) { _xmlSource = xmlSource; _destination = destination; XMLImport importer = new XMLImport(); Database.MySQL.Database myDB = new Database.MySQL.Database(_destination); string connString = string.Format(DBConnectString, _xmlSource.ServerIp, _xmlSource.Schema, _xmlSource.Username, _xmlSource.Password); using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connString)) { conn.Open(); using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SELECT `fishbaseId`, `genus`, `species`, `xmlSummary`, `xmlPointData`, `xmlCommonNames`, `xmlPhotos` FROM `fishbaseraw`;"; using (MySql.Data.MySqlClient.MySqlDataReader reader = cmd.ExecuteReader()) { while(reader.Read()) { Model.FishClass fc = importer.Import( reader.GetInt32("fishbaseId"), reader.GetString("genus"), reader.GetString("species"), reader.GetString("xmlSummary"), reader.GetString("xmlPointData"), reader.GetString("xmlCommonNames"), reader.GetString("xmlPhotos") ); if (fc != null) { try { myDB.CreateFish(fc); } catch (Database.Exceptions.CreatureAlreadyExists ex) { myDB.UpdateFish(fc); } } } } } } }
public DataSet DataReport() { try{ MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_RapportView", conn); cmd.CommandType = CommandType.StoredProcedure; MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); return ds; }catch(Exception ex) { error = ex.Message; } return null; }
public int DoRegisterNewContactusindb(string path) { int returnVal = 0; MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlcommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlcommand.Connection = msqlConnection; //define the command text msqlcommand.CommandText = "insert into photos(size,img, description)" + "values( @size,@img, @description)"; //add values provided by user byte[] imgbytes = File.ReadAllBytes(path); msqlcommand.Parameters.AddWithValue("@size", imgbytes.Length); msqlcommand.Parameters.AddWithValue("@img", imgbytes); msqlcommand.Parameters.AddWithValue("@description", "This file is taken from " + path); msqlcommand.ExecuteNonQuery(); //close the connection msqlConnection.Close(); } catch (Exception er) { returnVal = 0; } finally { //always close the connection msqlConnection.Close(); } return returnVal; }
/*ejecuta la consulta que se pasa como parametro cogiendo la base de datos cuyo string de conexion esta en el parametro conexion del fichero de configuracion*/ public void ejecutar_sql(string sql) { try { MySql.Data.MySqlClient.MySqlConnection mscon = new MySql.Data.MySqlClient.MySqlConnection(Properties.Settings.Default.conexion); mscon.Open(); MySql.Data.MySqlClient.MySqlCommand mscom = new MySql.Data.MySqlClient.MySqlCommand(sql, mscon); mscom.ExecuteNonQuery(); mscon.Close(); } catch (MySql.Data.MySqlClient.MySqlException e) { LinkException le = new LinkException(e.Message); throw le; } }
private static int DoRegisterNewEmployeeInDb(EmployeeDetails employeeDetails) { int returnVal = 0; MySql.Data.MySqlClient.MySqlConnection msqlConnection = null; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=Mmm_mb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; //open the connection if (msqlConnection.State != System.Data.ConnectionState.Open) msqlConnection.Open(); msqlCommand.CommandText = "INSERT INTO employees(employee_id,employee_name,employee_dob,employee_address,employee_phone_no,employee_email,employee_join_date,department,salary,employee_manager_id) " + "VALUES(@employee_id,@employee_name,@employee_dob,@employee_address,@employee_phone_no,@employee_email,@employee_join_date,@department,@salary,@employee_manager_id)"; msqlCommand.Parameters.AddWithValue("@employee_id", employeeDetails.employeeId); msqlCommand.Parameters.AddWithValue("@employee_name", employeeDetails.employeeName); msqlCommand.Parameters.AddWithValue("@employee_dob", employeeDetails.employeeDob); msqlCommand.Parameters.AddWithValue("@employee_address", employeeDetails.employeeAddress); msqlCommand.Parameters.AddWithValue("@employee_phone_no", employeeDetails.employeePhoneNumber); msqlCommand.Parameters.AddWithValue("@employee_email", employeeDetails.employeeEmail); msqlCommand.Parameters.AddWithValue("@employee_join_date", employeeDetails.employeeJoinDate); msqlCommand.Parameters.AddWithValue("@department", employeeDetails.employeeDepartment); msqlCommand.Parameters.AddWithValue("@salary", employeeDetails.employeeSalary); msqlCommand.Parameters.AddWithValue("@employee_manager_id", employeeDetails.employeeManagerId); msqlCommand.ExecuteNonQuery(); returnVal = 1; } catch (Exception er) { returnVal = 0; } finally { //always close the connection msqlConnection.Close(); } return returnVal; }
/// <summary> /// Avec les informations sur le véhicule et sur le client, on utilise la procédure stockée SP_Nouvelle_Location pour créer un nouveau /// contrat. /// </summary> /// <returns><c>true</c>, if contrat de location was faired, <c>false</c> otherwise.</returns> /// <param name="niv">Niv.</param> /// <param name="iDClient">I D client.</param> /// <param name="kiloDebut">Kilo debut.</param> /// <param name="kiloPermis">Kilo permis.</param> /// <param name="surprimeKmEx">Surprime km ex.</param> /// <param name="montantMensuel">Montant mensuel.</param> /// <param name="nbrPaiement">Nbr paiement.</param> /// <param name="termeLocation">Terme location.</param> public bool FaireContratDeLocation(string niv, string iDClient, string kiloDebut, string kiloPermis, string surprimeKmEx, string montantMensuel, string nbrPaiement, string termeLocation) { try{ MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("SP_Nouvelle_Location", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("NIV_Vehicule", MySql.Data.MySqlClient.MySqlDbType.VarChar).Value = niv; cmd.Parameters.Add("ID_Client", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = iDClient; cmd.Parameters.Add("Kilometrage_Debut_Contrat", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = kiloDebut; cmd.Parameters.Add("Kilometrage_Permis", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = kiloPermis; cmd.Parameters.Add("Surprime_Km_excedentaire", MySql.Data.MySqlClient.MySqlDbType.Double).Value = surprimeKmEx; cmd.Parameters.Add("Montant_Mensuel", MySql.Data.MySqlClient.MySqlDbType.Double).Value = montantMensuel; cmd.Parameters.Add("Nombre_Paiements", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = nbrPaiement; cmd.Parameters.Add("Terme_Location", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = termeLocation; if(termeLocation == "48") { cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 4; } else if(termeLocation == "32") { cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 3; } else if (termeLocation == "24") { cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 2; } else if (termeLocation == "12") { cmd.Parameters.Add("AnneeDeLocation", MySql.Data.MySqlClient.MySqlDbType.Int32).Value = 1; } conn.Open(); cmd.ExecuteNonQuery(); return true; }catch(Exception ex) { error = ex.Message; } finally{ conn.Close (); } return false; }
private void Button_Click(object sender, RoutedEventArgs e) { MySql.Data.MySqlClient.MySqlConnection msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost; user id=root;password=technicise;database=my_schema;persist security info=false"); //open the connection if (msqlConnection.State != System.Data.ConnectionState.Open) msqlConnection.Open(); //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlcommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlcommand.Connection = msqlConnection; //define the command text msqlcommand.CommandText = "insert into phonebook(id,name,mobileno)" + "values(@id,@name,@mobileno)"; //add values provided by user msqlcommand.Parameters.AddWithValue("@id",id.Text); msqlcommand.Parameters.AddWithValue("@name", name.Text); msqlcommand.Parameters.AddWithValue("@mobileno", mobileno.Text); msqlcommand.ExecuteNonQuery(); //close the connection msqlConnection.Close(); //empty the text boxes id.Text = null; name.Text = null; mobileno.Text = null; MessageBox.Show("Info Added"); }
public void UpdateResets(int TestID, string MBSN, string OPSN, string ENGINESN) { try { if (_conn.State == System.Data.ConnectionState.Open) { _conn.Close(); } _conn.Open(); MySql.Data.MySqlClient.MySqlCommand _mySqlCommand; string _sql = String.Format( "UPDATE resets SET ", TestID); if (MBSN != null) { _sql += String.Format("mbsn='{0}',", MBSN); } if (OPSN != null) { _sql += String.Format("opsn='{0}',", OPSN); } if (ENGINESN != null) { _sql += String.Format("enginesn='{0}',", ENGINESN); } _sql += String.Format( " date='{0}'" + " WHERE testid='{1}'", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), TestID); _mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn); _mySqlCommand.ExecuteNonQuery(); _mySqlCommand.Dispose(); _conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine(ex.Message); } }
public Person GetPerson(long ID) { MySql.Data.MySqlClient.MySqlConnection conn; conn = new MySql.Data.MySqlClient.MySqlConnection(); try { conn.ConnectionString = connection.myConnectionString; conn.Open(); Person p = new Person(); MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null; String sqlString = "SELECT * FROM tblpersonnel WHERE ID = " + ID.ToString(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn); mySqlDataReader = cmd.ExecuteReader(); if (mySqlDataReader.Read()) { p.ID = mySqlDataReader.GetInt32(0); p.FirstName = mySqlDataReader.GetString(1); p.LastName = mySqlDataReader.GetString(2); p.PayRate = mySqlDataReader.GetDouble(3); p.StartDate = mySqlDataReader.GetDateTime(4); p.EndDate = mySqlDataReader.GetDateTime(5); p.Address = mySqlDataReader.GetString(6); p.State = mySqlDataReader.GetString(7); p.ZipCode = mySqlDataReader.GetInt64(8); p.PhoneNumber = mySqlDataReader.GetDouble(9); return(p); } else { return(null); } } catch (MySql.Data.MySqlClient.MySqlException ex) { throw ex; } finally { conn.Close(); } }
public static int DoRegisterNewContact(ContactInfo contactDetails) { int returnVal = 0; MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection(); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); //define the connection used by the command object msqlCommand.Connection = msqlConnection; msqlCommand.CommandText = "INSERT INTO contact(contactId,name,mobile,homePhone,officePhone,email,address,faxNumber,remark) " + "VALUES(@contactId,@name,@mobile,@homePhone,@officePhone,@email,@address,@faxNumber,@remark)"; msqlCommand.Parameters.AddWithValue("@contactId", contactDetails.id); msqlCommand.Parameters.AddWithValue("@name", contactDetails.name); msqlCommand.Parameters.AddWithValue("@mobile", contactDetails.mobileno); msqlCommand.Parameters.AddWithValue("@homePhone", contactDetails.homeno); msqlCommand.Parameters.AddWithValue("@officePhone", contactDetails.oficeno); msqlCommand.Parameters.AddWithValue("@email", contactDetails.email); msqlCommand.Parameters.AddWithValue("@address", contactDetails.address); msqlCommand.Parameters.AddWithValue("@faxNumber", contactDetails.faxno); msqlCommand.Parameters.AddWithValue("@remark", contactDetails.remark); msqlCommand.ExecuteNonQuery(); returnVal = 1; } catch (Exception er) { returnVal = 0; } finally { //always close the connection msqlConnection.Close(); } return returnVal; }
public List <Component> GetComponents() { List <Component> _list = new List <Component>(); try { if (_conn.State == System.Data.ConnectionState.Open) { _conn.Close(); } _conn.Open(); MySql.Data.MySqlClient.MySqlCommand _mySqlCommand; MySql.Data.MySqlClient.MySqlDataReader _dataReader; string _sql = "SELECT * FROM components;"; _mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn); _dataReader = _mySqlCommand.ExecuteReader(); while (_dataReader.Read()) { int _id; Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id); int _stock; Int32.TryParse(_dataReader.GetValue(8).ToString(), out _stock); int _yield; Int32.TryParse(_dataReader.GetValue(9).ToString(), out _yield); _list.Add(new Component(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _dataReader.GetValue(5).ToString(), _dataReader.GetValue(6).ToString(), _dataReader.GetValue(7).ToString(), _stock, _yield, _dataReader.GetValue(10).ToString(), _dataReader.GetValue(11).ToString())); } _dataReader.Close(); _mySqlCommand.Dispose(); _conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine(ex.Message); } return(_list); }
public void metodo() { articoli.Clear(); try { conn = new MySql.Data.MySqlClient.MySqlConnection(connessione); conn.Open(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.CommandText = "Select * from articolo"; cmd.Connection = conn; adapter.SelectCommand = cmd; adapter.Fill(articoli); cmbl = new MySql.Data.MySqlClient.MySqlCommandBuilder(adapter); dataGridView1.DataSource = articoli; cmd = new MySql.Data.MySqlClient.MySqlCommand(); string nomecli = textboxcliente.Text; string piva = getPartitaIvaDiCliente(nomecli); cmd.CommandText = "Select * from prezzo_articolo_cliente where nomecliente = @nomecli"; cmd.Parameters.AddWithValue("@nomecli", piva); cmd.Connection = conn; adapter.SelectCommand = cmd; adapter.Fill(prezzocliente); conn.Close(); foreach (DataRow row in prezzocliente.Rows) { foreach (DataRow art in articoli.Rows) { if (art["codicearticolo"].Equals(row["codicearticolo"])) { art["prezzolistino"] = (double)row["prezzocliente"] + 999999; } } } } catch (MySql.Data.MySqlClient.MySqlException e) { conn.Close(); throw; } }
public int GetPrinterIDFromTestBySN(string SN) { int _id = 0; try { if (_conn.State == System.Data.ConnectionState.Open) { _conn.Close(); } _conn.Open(); MySql.Data.MySqlClient.MySqlCommand _mySqlCommand; MySql.Data.MySqlClient.MySqlDataReader _dataReader; string _sql = "SELECT printersid FROM test WHERE sn='" + SN + "';"; _mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn); _dataReader = _mySqlCommand.ExecuteReader(); while (_dataReader.Read()) { if (_dataReader.GetValue(0).Equals(null)) { _id = 0; } else { Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id); } } _dataReader.Close(); _mySqlCommand.Dispose(); _conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine(ex.Message); } return(_id); }
public Component GetComponentByPN(string PN) { Component _component = null; try { if (_conn.State == System.Data.ConnectionState.Open) { _conn.Close(); } _conn.Open(); MySql.Data.MySqlClient.MySqlCommand _mySqlCommand; MySql.Data.MySqlClient.MySqlDataReader _dataReader; string _sql = String.Format("SELECT * FROM components WHERE pn='{0}';", PN); _mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn); _dataReader = _mySqlCommand.ExecuteReader(); while (_dataReader.Read()) { int _id; Int32.TryParse(_dataReader.GetValue(0).ToString(), out _id); int _stock; Int32.TryParse(_dataReader.GetValue(8).ToString(), out _stock); int _yield; Int32.TryParse(_dataReader.GetValue(9).ToString(), out _yield); _component = new Component(_id, _dataReader.GetValue(1).ToString(), _dataReader.GetValue(2).ToString(), _dataReader.GetValue(3).ToString(), _dataReader.GetValue(4).ToString(), _dataReader.GetValue(5).ToString(), _dataReader.GetValue(6).ToString(), _dataReader.GetValue(7).ToString(), _stock, _yield, _dataReader.GetValue(10).ToString(), _dataReader.GetValue(11).ToString()); } _dataReader.Close(); _mySqlCommand.Dispose(); _conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine(ex.Message); } return(_component); }
public void InsertPrinterDismantleTime(int UserID, int TestID, int PrintersID, DateTime StartDateTime, DateTime EndDateTime, TimeSpan Time) { try { if (_conn.State == System.Data.ConnectionState.Open) { _conn.Close(); } _conn.Open(); MySql.Data.MySqlClient.MySqlCommand _mySqlCommand; //DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") string _sql = String.Format("INSERT INTO dismantledtimes (userid, testid, printersid, start, end, time) VALUES " + "(" + //userid "'{0}', " + //testid "'{1}', " + //printersid "'{2}', " + //start "'{3}', " + //end "'{4}', " + //time "'{5}'" + ");", UserID, TestID, PrintersID, StartDateTime.ToString("yyyy-MM-dd HH:mm:ss"), EndDateTime.ToString("yyyy-MM-dd HH:mm:ss"), Time.TotalMinutes); _mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn); _mySqlCommand.ExecuteNonQuery(); _mySqlCommand.Dispose(); _conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine(ex.Message); } }
// return the creation of an incidence and the data of it public Incidencia_Data ListarIncidencias(MySql.Data.MySqlClient.MySqlConnection conn) { ArrayList Lista_Incidente = new ArrayList(); string Query = "SELECT * FROM Report;"; string SecondQuery = "Select COUNT(*) FROM mydb.Incidencia;"; var Cmd = new MySql.Data.MySqlClient.MySqlCommand(SecondQuery, conn);//command variable var Reader = Cmd.ExecuteReader(); Reader.Read(); //get quantity of items int Incidencia_Quantity = Convert.ToInt32(Reader["COUNT(*)"].ToString()); Reader.Close(); Cmd = new MySql.Data.MySqlClient.MySqlCommand(Query, conn); Reader = Cmd.ExecuteReader(); for (int i = 0; i < Incidencia_Quantity; i++) { Reader.Read(); Lista_Incidente.Add(Reader["Id_Incidencia"].ToString()); Lista_Incidente.Add(Reader["Asunto"].ToString()); Lista_Incidente.Add(Reader["Fecha_Registro"].ToString()); Lista_Incidente.Add(GetUser_byID(Reader["Id_Usuario"].ToString())); Lista_Incidente.Add(Reader["Tipo_Solicitud"].ToString()); Lista_Incidente.Add(Reader["Categoria_Incidente"].ToString()); Lista_Incidente.Add(Reader["Prioridad"].ToString()); Lista_Incidente.Add(Reader["Estado"].ToString()); Lista_Incidente.Add(Reader["Tecnico"].ToString()); } Incidencia_Data Lista = new Incidencia_Data { Activos = Lista_Incidente }; Reader.Close(); return(Lista); }
public void UpsertResets(int TestID, string MBSN, string OPSN, string ENGINESN) { try { if (_conn.State == System.Data.ConnectionState.Open) { _conn.Close(); } MySql.Data.MySqlClient.MySqlCommand _mySqlCommand; MySql.Data.MySqlClient.MySqlDataReader _dataReader; _conn.Open(); string _sql = String.Format("SELECT resetsid FROM resets WHERE testid='{0}';", TestID); _mySqlCommand = new MySql.Data.MySqlClient.MySqlCommand(_sql, _conn); _dataReader = _mySqlCommand.ExecuteReader(); int _value = 0; while (_dataReader.Read()) { Int32.TryParse(_dataReader.GetValue(0).ToString(), out _value); } _dataReader.Close(); _mySqlCommand.Dispose(); _conn.Close(); if (_value == 0) { InsertResets(TestID, MBSN, OPSN, ENGINESN); } else { UpdateResets(TestID, MBSN, OPSN, ENGINESN); } } catch (MySql.Data.MySqlClient.MySqlException ex) { Console.WriteLine(ex.Message); } }
public void Import(string s) { OpenConnection(); MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(); aCommand.Connection = m_connection; MySql.Data.MySqlClient.MySqlTransaction trc = m_connection.BeginTransaction(); aCommand.Transaction = trc; try { string[] p = s.Split(','); aCommand.CommandText = Part.GetByCodeSQLStatic(p[0]); MySql.Data.MySqlClient.MySqlDataReader re = aCommand.ExecuteReader(); Part fpart = Part.GetPart(re); re.Close(); if (fpart != null) { trc.Rollback(); return; } Unit u = GetUnit(aCommand, p[3], p[4]); PartGroup pg = GetPartGroup(aCommand, p[5], p[6]); Currency ccy = GetCurrency(aCommand, p[7], p[8]); PartCategory pc = GetPartCategory(aCommand, p[9], "KECIL"); Part part = new Part(); part.CODE = p[0]; part.NAME = p[1]; part.BARCODE = p[2]; part.UNIT = u; part.PART_GROUP = pg; part.CURRENCY = ccy; part.PART_CATEGORY = pc; aCommand.CommandText = part.GetInsertSQL(); aCommand.ExecuteNonQuery(); trc.Commit(); } catch (Exception x) { trc.Rollback(); throw x; } }
protected void Submit(object sender, EventArgs e) { String connStr = System.Configuration.ConfigurationManager.ConnectionStrings["WebConnString"].ToString(); conn = new MySql.Data.MySqlClient.MySqlConnection(connStr); conn.Open(); queryStr = "SELECT * FROM tasarimdersi.yazarkullanici WHERE KullaniciAdi='" + GirisUsername.Text + "' AND Sifre='" + GirisPassword.Text + "'"; cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn); reader = cmd.ExecuteReader(); name = ""; while (reader.HasRows && reader.Read()) { name = reader.GetString(reader.GetOrdinal("ID")); kAdi = reader.GetString(reader.GetOrdinal("KullaniciAdi")); isim = reader.GetString(reader.GetOrdinal("Isim")); soyisim = reader.GetString(reader.GetOrdinal("Soyisim")); email = reader.GetString(reader.GetOrdinal("Email")); } if (reader.HasRows) { Session["IDSes"] = name; Session["kAdiSes"] = kAdi; Session["IsimSes"] = isim; Session["SoyisimSes"] = soyisim; Session["EmailSes"] = email; Response.BufferOutput = true; Response.Redirect("MainYazar.aspx", false); } else { uyari.InnerText = "Giriş Başarısız"; } reader.Close(); conn.Close(); }
public ArrayList ObtenerPlatos(int idioma) { ArrayList arrayPlatos = new ArrayList(); MySql.Data.MySqlClient.MySqlDataReader mySQLReader; String sqlString = "CALL Select_all_plato(" + idioma.ToString() + ");"; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conexion); try { mySQLReader = cmd.ExecuteReader(); while (mySQLReader.Read()) { Plato p = new Plato(); p.id = mySQLReader.GetInt32(0); p.nombre = mySQLReader.GetString(1); p.descripcion = ""; p.imagen = mySQLReader.GetString(2); p.precio = mySQLReader.GetDouble(3); p.nombre_familia = mySQLReader.GetString(5); p.id_familia = mySQLReader.GetInt32(6); arrayPlatos.Add(p); } } catch (Exception) { conexion.Close(); Console.WriteLine("Error al hacer la consulta"); } finally { conexion.Close(); } return(arrayPlatos); }
public static SettingsData FetcheSettingsData() { SettingsData settingsDataObject = null; MySql.Data.MySqlClient.MySqlConnection msqlConnection = null; msqlConnection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;Password=technicise;database=sptdb;persist security info=False"); try { //define the command reference MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand(); msqlCommand.Connection = msqlConnection; msqlConnection.Open(); msqlCommand.CommandText = "Select * from sptinfo;"; MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader(); msqlReader.Read(); settingsDataObject = new SettingsData(); settingsDataObject.Address = msqlReader.GetString("address"); settingsDataObject.Name = msqlReader.GetString("name"); settingsDataObject.Phone = msqlReader.GetString("phone"); settingsDataObject.Password = msqlReader.GetString("password"); settingsDataObject.BillDisclaimer = msqlReader.GetString("bill_disclaimer"); settingsDataObject.InvoicePrefix = msqlReader.GetString("invoice_prefix"); //settingsDataObject.sptinfo = msqlReader.GetString("id_sptinfo"); } catch (Exception er) { //Assert//.Show(er.Message); } finally { //always close the connection msqlConnection.Close(); } return settingsDataObject; }
//GET METHOD public AccessPoint GetAccessPointsByName(string name) { MySql.Data.MySqlClient.MySqlDataReader mySqlDataReader = null; AccessPoint res = new AccessPoint(); string sqlString = "SELECT * FROM accesspoints WHERE Position ='" + name + "'"; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, connector.conn); mySqlDataReader = cmd.ExecuteReader(); if (mySqlDataReader.Read()) { res.Position = mySqlDataReader.GetString(0); res.Bssid = mySqlDataReader.GetString(1); res.EquipType = mySqlDataReader.GetString(2); } return(res); }
public void prendiarticoli() { try { conn = new MySql.Data.MySqlClient.MySqlConnection(connessione); conn.Open(); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); cmd.CommandText = "Select * from articolo"; cmd.Connection = conn; articoliadapter.SelectCommand = cmd; articoliadapter.Fill(articoli); articolibuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(articoliadapter); datagridarticoli.DataSource = articoli; conn.Close(); } catch (MySql.Data.MySqlClient.MySqlException e) { conn.Close(); throw; } }
public static void Connected2() { MyCon _con = new MyCon(); MySql.Data.MySqlClient.MySqlDataReader rdr = null; _con.ConnectDB(); using (MySql.Data.MySqlClient.MySqlConnection connect = _con.db.connect()){ connect.Open(); string sql = String.Format("{0}", "SELECT * FROM Customer"); MySql.Data.MySqlClient.MySqlCommand cmd = connect.CreateCommand(); cmd.CommandText = sql; cmd.CommandTimeout = 600; rdr = cmd.ExecuteReader(); if (rdr.HasRows) { Console.WriteLine(rdr); } connect.Close(); } }
private void BuscarVentaFechas(DateTime fechaIni, DateTime fechaFin) { try { MySql.Data.MySqlClient.MySqlCommand sql = new MySql.Data.MySqlClient.MySqlCommand(); sql.CommandText = "SELECT id, fecha, total, estado, abierta FROM venta WHERE (fecha BETWEEN ? AND ?) AND estado=1"; //Recuerda que en cualquier gestor de base de datos, las fechas van en orden yyyy-MM-dd, //y así te quitas muchos pedos Chava u.u sql.Parameters.AddWithValue("@fechaIni", fechaIni.ToString("yyyy-MM-dd") + " 00:00:00"); sql.Parameters.AddWithValue("@fechaFin", fechaFin.ToString("yyyy-MM-dd") + " 23:59:59"); dt = Clases.ConexionBD.EjecutarConsultaSelect(sql); } catch (MySql.Data.MySqlClient.MySqlException ex) { Clases.FuncionesGenerales.MensajeError("Ocurrio un error búscando las ventas. No se pudo conectar a la base de datos.", ex); } catch (Exception ex) { Clases.FuncionesGenerales.MensajeError("Ocurrio un error búscando las ventas. Ha ocurrido un error genérico.", ex); } }
public debtPaid(int summ) { InitializeComponent(); paidSumm = summ; tbxSum.Text = summ.ToString(); //Вывод в ComboBox имен агентов string cmd = "SELECT * FROM sklad.agents"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(tposDesktop.Properties.Settings.Default.testConnectionString)) { MySql.Data.MySqlClient.MySqlCommand com = new MySql.Data.MySqlClient.MySqlCommand(cmd, conn); DataTable dt = new DataTable(); MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(com); da.Fill(dt); cmbNameAg.DataSource = dt; cmbNameAg.DisplayMember = "Name"; cmbNameAg.ValueMember = "ID"; //cmbNameAg.SelectedIndex = -1; } }
protected override System.Data.IDbCommand getCommand(string storedProcedure) { MySql.Data.MySqlClient.MySqlCommand mCommand; if(CommandsCollection.Contains(storedProcedure)) { mCommand = (MySql.Data.MySqlClient.MySqlCommand) CommandsCollection[storedProcedure]; } else { MySql.Data.MySqlClient.MySqlConnection Conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString); Conn.Open(); mCommand = new MySql.Data.MySqlClient.MySqlCommand(storedProcedure,Conn); mCommand.CommandType = System.Data.CommandType.StoredProcedure; MySql.Data.MySqlClient.MySqlCommandBuilder.DeriveParameters(mCommand); Conn.Close(); Conn.Dispose(); CommandsCollection.Add(storedProcedure, mCommand); } mCommand.Connection = (MySql.Data.MySqlClient.MySqlConnection) this.Connection; return (System.Data.IDbCommand) mCommand; }
public static void InserirPanico(long cpf, float lati, float longi, int senha) { using (MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(GetStringDb())) { using (MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("INSERT INTO ocorrencias (cpf_cliente, datahora, latitude, longitude, senha_utilizada) VALUES (@cpf_cliente, @datahora, @latitude, @longitude, @senha)", con)) { cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("@cpf_cliente", cpf); cmd.Parameters.AddWithValue("@datahora", DateTime.Now); cmd.Parameters.AddWithValue("@latitude", lati); cmd.Parameters.AddWithValue("@longitude", longi); cmd.Parameters.AddWithValue("@senha", senha); con.Open(); cmd.ExecuteNonQuery(); con.Clone(); } } }
private static void PrepareCommand(MySql.Data.MySqlClient.MySqlCommand cmd, MySql.Data.MySqlClient.MySqlConnection conn, MySql.Data.MySqlClient.MySqlTransaction trans, string cmdText, MySql.Data.MySqlClient.MySqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) { cmd.Transaction = trans; } cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (MySql.Data.MySqlClient.MySqlParameter parm in cmdParms) { cmd.Parameters.Add(parm); } } }
public void DeleteNoTransaction(Event e, MySql.Data.MySqlClient.MySqlCommand command) { OpeningStock st = (OpeningStock)e;//this.Get(e.ID); m_command = command; try { if (getEventStatus(st.ID) == EventStatus.Confirm) { throw new Exception("Revise before delete"); } m_command.CommandText = OpeningStockItem.DeleteAllByEventSQL(st.ID); m_command.ExecuteNonQuery(); m_command.CommandText = OpeningStock.DeleteSQL(st.ID); m_command.ExecuteNonQuery(); } catch (Exception x) { throw x; } }
public void ConfirmNoTransaction(int id, MySql.Data.MySqlClient.MySqlCommand command) { m_command = command; try { Event events = this.Get(id); if (events.POSTED) //.EVENT_STATUS == EventStatus.Confirm) { throw new Exception("Status is already Posted/Confirm"); } Period p = AssertValidPeriod(events.TRANSACTION_DATE); doConfirm(events, p); events.ProcessConfirm(); this.UpdateStatus(events, true); updateStockCards(events.EVENT_ITEMS); } catch (Exception x) { throw x; } }
public void Delete(Character ch) { using (var con = new MySql.Data.MySqlClient.MySqlConnection(connectionString)) { var cmd = new MySql.Data.MySqlClient.MySqlCommand( "DELETE FROM CharactersSpecialty WHERE @CharacterID = CharacterID" , con ); cmd.Parameters.AddWithValue("@CharacterID", ch.ID); con.Open(); cmd.ExecuteNonQuery(); cmd = new MySql.Data.MySqlClient.MySqlCommand( "DELETE FROM Characters WHERE @CharacterID = CharacterID" , con ); cmd.Parameters.AddWithValue("@CharacterID", ch.ID); cmd.ExecuteNonQuery(); con.Close(); } }
private void employee() { conn = new MySql.Data.MySqlClient.MySqlConnection(constr); conn.Open(); data = "select EmployeeID,Name from hr.hire_emp"; cmd = new MySql.Data.MySqlClient.MySqlCommand(data, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { ListItem item = new ListItem(); item.Text = reader["Name"].ToString(); item.Value = reader["EmployeeID"].ToString(); DropDownList1.Items.Add(item); DropDownList2.Items.Add(item); } conn.Close(); }
private void button1_Click(object sender, EventArgs e) { String co = @"Server=127.0.0.1; DATABASE=tahuntsic; UID=root "; try { MySql.Data.MySqlClient.MySqlConnection c = new MySql.Data.MySqlClient.MySqlConnection(co); c.Open(); MySql.Data.MySqlClient.MySqlCommand comm = c.CreateCommand(); comm.CommandText = "SELECT * FROM users"; MySql.Data.MySqlClient.MySqlDataReader r = comm.ExecuteReader(); while (r.Read()) { Console.WriteLine(r.GetString(0) + "\t" + r.GetString(1) + "\t" + r.GetString(3)); } } catch (MySql.Data.MySqlClient.MySqlException ex) { MessageBox.Show(ex.Message); } }
private string getUserName(string userName) { MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null; string sql = "Select salt from quiz_taker_info where email = '" + userName.ToString() + "'"; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sql, conn); mySqlReader = cmd.ExecuteReader(); string salt; if (mySqlReader.Read()) { salt = mySqlReader.GetString(0); } else { salt = null; } mySqlReader.Close(); return(salt); }
private PartCategory GetPartCategory(MySql.Data.MySqlClient.MySqlCommand aCommand, string code, string name) { //MySql.Data.MySqlClient.MySqlCommand aCommand = new MySql.Data.MySqlClient.MySqlCommand(); aCommand.Connection = m_connection; aCommand.CommandText = PartCategory.GetByCodeSQLStatic(code.Trim()); MySql.Data.MySqlClient.MySqlDataReader r = aCommand.ExecuteReader(); PartCategory u = PartCategory.GetPartCategory(r); r.Close(); if (u == null) { u = new PartCategory(); u.CODE = code; u.NAME = name; aCommand.CommandText = u.GetInsertSQL(); aCommand.ExecuteNonQuery(); aCommand.CommandText = u.GetMaximumIDSQL(); u.ID = Convert.ToInt32(aCommand.ExecuteScalar()); } return(u); }
private static void load_db() { Core.Log.WritePlain("MySQL", "Connecting to database... (" + Globals.GetInstance().Config.GetValue("SQL_HOSTNAME") + ":3306)"); Globals.GetInstance().GameDatabase = new Core.DB.Instance(); Globals.GetInstance().GameDatabase.SetCredentials(Globals.GetInstance().Config.GetValue("SQL_HOSTNAME"), Globals.GetInstance().Config.GetValue("SQL_USERNAME"), Globals.GetInstance().Config.GetValue("SQL_PASSWORD"), Globals.GetInstance().Config.GetValue("SQL_DATABASE")); if (!Globals.GetInstance().GameDatabase.TestConnection()) { Core.Log.WriteError("Couldn't connect to database!"); Console.ReadKey(); Environment.Exit(0); } Core.Log.WritePlain("MySQL", "Done!"); Console.WriteLine(); MySql.Data.MySqlClient.MySqlConnection mConnection = Globals.GetInstance().GameDatabase.CreateConnection(); MySql.Data.MySqlClient.MySqlCommand mCommand = new MySql.Data.MySqlClient.MySqlCommand("UPDATE accounts SET online=0 WHERE 1;", mConnection); mCommand.ExecuteNonQuery(); mConnection.Close(); }
public long insertFile(string refnum, string fileName, Array byteArray, long fileSize, string fileLocation, string origFileName) { string sqlInsert = "INSERT INTO IMAGE_DETAILS(REF_ID, FILENAME, IMAGE, FILE_LOCATION, SIZE, ORIG_FILENAAME) " + "VALUES('" + refnum + "', '" + fileName + "', @IMAGE, '" + fileLocation + "', '" + fileSize + "', '" + origFileName + "')"; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlInsert, conn); conn.Open(); try { cmd.Parameters.AddWithValue("@IMAGE", byteArray); cmd.ExecuteNonQuery(); long id = cmd.LastInsertedId; return(id); } catch (Exception ex) { new Exception("Unable to insertFile: ", ex); } return(0); }
/// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param> /// <param name="cmdText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return(rdr); } catch { conn.Close(); throw; } }
// Return the update of an incident assigment public void UpdateAssignment(MySql.Data.MySqlClient.MySqlConnection conn, String Code, String Personnel) { DateTime date = DateTime.Today; String today = date.ToString("d", CultureInfo.CreateSpecificCulture("ja-JP")); String DB_Change = "UPDATE `mydb`.`Incidencia` SET `Fecha_Atencion` = '" + today + "' WHERE `Id_Incidencia` = " + Code + ";"; var Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Change, conn); //Insert comm var executer = Insert.ExecuteNonQuery(); //execute non query DB_Change = "UPDATE `mydb`.`Reportes` SET `Fecha_Asignacion` = '" + today + "' WHERE `Id_Reportes` = '" + Cedula_ByID(conn, Code) + "';"; Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Change, conn); //Insert comm executer = Insert.ExecuteNonQuery(); //execute non query DB_Change = "UPDATE `mydb`.`Incidencia` SET `Tecnico` = '" + Personnel + "' WHERE `Id_Incidencia` = " + Code + ";"; Insert = new MySql.Data.MySqlClient.MySqlCommand(DB_Change, conn); //Insert comm executer = Insert.ExecuteNonQuery(); //execute non query }