public void Bind() { mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=baza;" + "UID=root;"); mySqlConnection.Open(); string query = "SELECT * FROM cennik"; mySqlDataAdapter = new MySqlDataAdapter(query, mySqlConnection); mySqlCommandBuilder = new MySqlCommandBuilder(mySqlDataAdapter); mySqlDataAdapter.UpdateCommand = mySqlCommandBuilder.GetUpdateCommand(); mySqlDataAdapter.DeleteCommand = mySqlCommandBuilder.GetDeleteCommand(); mySqlDataAdapter.InsertCommand = mySqlCommandBuilder.GetInsertCommand(); dataTable = new DataTable(); mySqlDataAdapter.Fill(dataTable); bindingSource = new BindingSource(); bindingSource.DataSource = dataTable; dataGridView3.DataSource = bindingSource; }
public MySqlTableContext( DataTable dataTable, MySqlConnection connection ) { this.Connection = connection; this.DataTable = dataTable; this.DataAdapter = new MySqlDataAdapter( string.Format( "SELECT * FROM {0} WHERE 1=0", this.DataTable.TableName ), this.Connection ); this.DataAdapter.UpdateBatchSize = 50; // Using workaround for MySQL Connector bug described at: // http://bugs.mysql.com/bug.php?id=39815 // Dispose the builder before setting adapter commands. MySqlCommandBuilder builder = new MySqlCommandBuilder( this.DataAdapter ); MySqlCommand updateCommand = builder.GetUpdateCommand(); MySqlCommand insertCommand = builder.GetInsertCommand(); MySqlCommand deleteCommand = builder.GetDeleteCommand(); builder.Dispose(); this.DataAdapter.UpdateCommand = updateCommand; this.DataAdapter.InsertCommand = insertCommand; this.DataAdapter.DeleteCommand = deleteCommand; this.DataAdapter.RowUpdating += new MySqlRowUpdatingEventHandler( DataAdapter_RowUpdating ); this.DataAdapter.RowUpdated += this.OnRowUpdated; // Create a command to fetch the last inserted id identityCommand = this.Connection.CreateCommand(); identityCommand.CommandText = "SELECT LAST_INSERT_ID()"; this.RefreshIdentitySeed(); }
public DataTable SqlDataTable(string strname, string str, out DataSet ds, out MySqlDataAdapter da) { try { conn.Open(); da = new MySqlDataAdapter(str, connstr); MySqlCommandBuilder thisBuilder = new MySqlCommandBuilder(da); ds = new DataSet(); da.Fill(ds, strname); DataTable mytable = new DataTable(); mytable = ds.Tables[0]; return mytable; } catch (Exception ex) { da = null; ds = null; System.Windows.Forms.MessageBox.Show(ex.Message); return null; } finally { conn.Close(); } }
public static DataTable getCommand() { DataSet ds = new DataSet(); DataTable dt = new DataTable(); try { // Ouverture de la connexion M_Connexion.Gestion.Open(); // Requête SQL String ReqSQL = "SELECT * FROM commande WHERE status = ?"; MySqlDataAdapter da = new MySqlDataAdapter(ReqSQL, M_Connexion.Gestion); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); da.SelectCommand.Parameters.AddWithValue("@status", "En cours"); da.Fill(ds, "commande"); dt = ds.Tables[0]; // Fermeture de la connexion M_Connexion.Gestion.Close(); } catch (Exception ex) { MessageBox.Show("Erreur :" + ex.Message); M_Connexion.Gestion.Close(); } return dt; }
public void LoadMySql(string serverName,// Адрес сервера (для локальной базы пишите "localhost") string userName, // Имя пользователя string dbName,//Имя базы данных int port, // Порт для подключения string password, string _table) { string connStr; string strTable; DataTable table; connStr = "Database="+dbName+";Data Source=" + serverName + ";User Id=" + userName + ";Password="******"SELECT * FROM " + strTable; // Строка запроса conn.Open(); MyData = new MySqlDataAdapter(sql,conn); MySqlCommandBuilder builder = new MySqlCommandBuilder(MyData); MyData.InsertCommand = builder.GetInsertCommand(); MyData.UpdateCommand = builder.GetUpdateCommand(); MyData.DeleteCommand = builder.GetDeleteCommand(); table = new DataTable(); MyData.Fill(table); UpdateGrid(table); }
public static DataTable getCategoriesTab() { DataSet ds = new DataSet(); DataTable dt = new DataTable(); try { // Ouverture de la connexion M_Connexion.Gestion.Open(); // Requête SQL String ReqSQL = "SELECT * FROM categorie"; MySqlDataAdapter da = new MySqlDataAdapter(ReqSQL, M_Connexion.Gestion); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); da.Fill(ds, "categorie"); dt = ds.Tables[0]; // Fermeture de la connexion M_Connexion.Gestion.Close(); } catch (Exception ex) { MessageBox.Show("Erreur :" + ex.Message); } return dt; }
public static Barang FindOneById(int id) { Database.OpenConnection(); string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id = @id"); Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter(); da.SelectCommand = new Sql.MySqlCommand(select, Database.conn); da.SelectCommand.Parameters.AddWithValue("@id", id); Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Database.CloseConnection(); Barang barang = new Barang(); if (ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; barang.setId(Convert.ToInt32(dr["id"])); barang.setNama(dr["nama"].ToString()); barang.setKode(dr["kode"].ToString()); barang.setJumlah(Convert.ToInt32(dr["jumlah"])); barang.setHargaHpp(Convert.ToDecimal(dr["harga_hpp"])); barang.setHargaJual(Convert.ToDecimal(dr["harga_jual"])); barang.setCreatedAt(Convert.ToDateTime(dr["created_at"])); barang.setUpdatedAt(Convert.ToDateTime(dr["updated_at"])); return(barang); } return(null); }
public void AutoIncrementColumns() { execSQL("DROP TABLE IF EXISTS Test"); execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)"); execSQL("INSERT INTO Test VALUES(NULL)"); MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]); DataRow row = ds.Tables[0].NewRow(); ds.Tables[0].Rows.Add(row); try { da.Update(ds); } catch (Exception ex) { Assert.Fail(ex.Message); } ds.Clear(); da.Fill(ds); Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]); Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]); cb.Dispose(); }
public void UpdateData(string tableName, string tableColumn, int limit, Action<DataRow> action) { var dataSet = new DataSet(); using (var connection = new MySqlConnection(GetConnectionString())) { connection.Open(); var q = string.Format(Query, tableName, tableColumn, limit); var adapter = new MySqlDataAdapter { SelectCommand = new MySqlCommand(q, connection) { CommandTimeout = 300 }, }; var builder = new MySqlCommandBuilder(adapter); adapter.Fill(dataSet); // Code to modify data in the DataSet here. foreach (DataRow row in dataSet.Tables[0].Rows) { action(row); } adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.Update(dataSet); } }
//meat and soft drinks private void button10_Click(object sender, EventArgs e) { listBox1.Items.Clear(); if (tablecheck == 2) { //meat try { string myConnection = conection; MySqlConnection myConn = new MySqlConnection(myConnection); MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(); //myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn); MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Meat + Fish' ;", myConn); MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter); myConn.Open(); MySqlDataReader reader = comand.ExecuteReader(); while (reader.Read()) { listBox1.Items.Add(reader.GetString(1)); listBox1.Items.Add(reader.GetString(2)); listBox1.Items.Add(reader.GetString(6)); } myConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } else if (tablecheck == 4) { //soft drinks try { string myConnection = conection; MySqlConnection myConn = new MySqlConnection(myConnection); MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(); //myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn); MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Soft Drinks' ;", myConn); MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter); myConn.Open(); MySqlDataReader reader = comand.ExecuteReader(); while (reader.Read()) { listBox1.Items.Add(reader.GetString(1)); listBox1.Items.Add(reader.GetString(2)); listBox1.Items.Add(reader.GetString(6)); } myConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
//Consultas a la Base de Datos (Poder llenar el DataGridView) public void consultar(string sql, string tabla) { ds.Tables.Clear(); da = new MySqlDataAdapter(sql, con); cmb = new MySqlCommandBuilder(da); da.Fill(ds, tabla); }
public static Penjualan FindOneByKode(string kode) { Database.OpenConnection(); string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE kode = @kode"); Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter(); da.SelectCommand = new Sql.MySqlCommand(select, Database.conn); da.SelectCommand.Parameters.AddWithValue("@kode", kode); Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Database.CloseConnection(); Penjualan penjualan = new Penjualan(); if (ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; penjualan.setId(Convert.ToInt32(dr["id"])); penjualan.setKode(dr["kode"].ToString()); penjualan.setCustomer(Customer.FindOneById(Convert.ToInt16(dr["id_customer"]))); penjualan.setCreatedAt(Convert.ToDateTime(dr["created_at"])); penjualan.setUpdatedAt(Convert.ToDateTime(dr["updated_at"])); return(penjualan); } return(null); }
public static DataTable FindAll() { Database.OpenConnection(); string select = String.Concat("SELECT * FROM ", nama_tabel); Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter(select, Database.conn); Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Database.CloseConnection(); DataTable dt = ds.Tables[0].Clone(); foreach (DataColumn dc in dt.Columns) { dc.DataType = typeof(string); } foreach (DataRow row in ds.Tables[0].Rows) { dt.ImportRow(row); } return(dt); }
private void GetData(string selectCommand) { try { // Specify a connection string. Replace the given value with a // valid connection string for a Northwind SQL Server sample // database accessible to your system. String connectionString = "server=localhost;uid=root;pwd=root;database=esalon;"; // Create a new data adapter based on the specified query. dataAdapter = new MySqlDataAdapter(selectCommand, connectionString); // Create a command builder to generate SQL update, insert, and // delete commands based on selectCommand. These are used to // update the database. MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter); // Populate a new data table and bind it to the BindingSource. DataTable table = new DataTable(); table.Locale = System.Globalization.CultureInfo.InvariantCulture; dataAdapter.Fill(table); bindingSource1.DataSource = table; } catch (MySqlException) { MessageBox.Show("Unable to connect to the database. Please check your connection string"); } }
internal static void SaveFromCache(string strTableName) { try { // Creates the data adapter and sets it with the update commands MySqlDataAdapter daAdapter = GetAdapter(strTableName); MySqlCommandBuilder cbBuilder = new MySqlCommandBuilder(daAdapter); // Updating table as is int nRowsUpdated = daAdapter.Update(Cache.SDB.Tables[strTableName]); Globals.LogFiles["DataBaseLog"].AddMessages(Globals.DbActivity.WRITE.ToString() + " at " + DateTime.Now, "Command: Adapter.Update(" + strTableName + ")", "Result: " + nRowsUpdated.ToString()); } // In the event of a databse exception catch (MySqlException e) { Globals.LogFiles["ErrorLog"].AddError(e.ErrorCode, e.Message, DateTime.Now); Globals.LogFiles["ErrorLog"].AddMessage(e.StackTrace); } // If any other exception occurs catch (Exception e) { Globals.LogFiles["ErrorLog"].AddError(Globals.ErrorCodes.SQL_ERROR, e.Message, DateTime.Now); Globals.LogFiles["ErrorLog"].AddMessages(e.StackTrace, e.InnerException.Message); } }
public static PenjualanDetail FindOneById(int id) { Database.OpenConnection(); string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id = @id"); Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter(); da.SelectCommand = new Sql.MySqlCommand(select, Database.conn); da.SelectCommand.Parameters.AddWithValue("@id", id); Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Database.CloseConnection(); PenjualanDetail penjualanDetail = new PenjualanDetail(); if (ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; penjualanDetail.setId(Convert.ToInt32(dr["id"])); penjualanDetail.setBarang(Barang.FindOneById(Convert.ToInt32(dr["id_barang"]))); penjualanDetail.setPenjualan(Penjualan.FindOneById(Convert.ToInt32(dr["id_penjualan"]))); penjualanDetail.setKuantitas(Convert.ToInt32(dr["kuantitas"])); penjualanDetail.setHargaBarang(Convert.ToDecimal(dr["harga_barang"])); penjualanDetail.setCreatedAt(Convert.ToDateTime(dr["created_at"])); penjualanDetail.setUpdatedAt(Convert.ToDateTime(dr["updated_at"])); return(penjualanDetail); } return(null); }
public static Supplier FindOneById(int id) { Database.OpenConnection(); string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id = @id"); Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter(); da.SelectCommand = new Sql.MySqlCommand(select, Database.conn); da.SelectCommand.Parameters.AddWithValue("@id", id); Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Database.CloseConnection(); Supplier supp = new Supplier(); if (ds.Tables[0].Rows.Count > 0) { DataRow dr = ds.Tables[0].Rows[0]; supp.setId(Convert.ToInt32(dr["id"])); supp.setNama(dr["nama"].ToString()); supp.setAlamat(dr["alamat"].ToString()); supp.setNomorTelepon(dr["nomor_telepon"].ToString()); supp.setKodePos(dr["kode_pos"].ToString()); supp.setKota(dr["kota"].ToString()); supp.setCreatedAt(Convert.ToDateTime(dr["created_at"])); supp.setUpdatedAt(Convert.ToDateTime(dr["updated_at"])); return(supp); } return(null); }
public static DataTable FindByIdPenjualan(int id_penjualan) { Database.OpenConnection(); string select = String.Concat("SELECT * FROM ", nama_tabel, " WHERE id_penjualan = @id_penjualan"); Sql.MySqlDataAdapter da = new Sql.MySqlDataAdapter(); da.SelectCommand = new Sql.MySqlCommand(select, Database.conn); da.SelectCommand.Parameters.AddWithValue("@id_penjualan", id_penjualan); Sql.MySqlCommandBuilder cb = new Sql.MySqlCommandBuilder(da); DataSet ds = new DataSet(); da.Fill(ds); Database.CloseConnection(); DataTable dt = ds.Tables[0].Clone(); foreach (DataColumn dc in dt.Columns) { dc.DataType = typeof(string); } foreach (DataRow row in ds.Tables[0].Rows) { dt.ImportRow(row); } return(dt); }
/// <summary> /// 插入数据通过Datatable /// </summary> /// <param name="_dt"></param> /// <returns>影响记录条数</returns> public override int DataTableInsert(DataTable _dt) { bool flag = false; int _nResult = 0; if (_dt == null) return _nResult; string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName); MySqlCommand _Command = (MySqlCommand)CreateCommand(_sCmdText, CommandType.Text); MySqlDataAdapter _adapter = new MySqlDataAdapter(_Command); MySqlDataAdapter _adapter1 = new MySqlDataAdapter(_Command); MySqlCommandBuilder _builder = new MySqlCommandBuilder(_adapter1); _adapter.InsertCommand = _builder.GetInsertCommand(); if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count) { flag = true;//因为表中有自增字段,所以CommandBuild生成的InserttCommand的参数中少了自增字段 foreach (DataColumn _dc in _dt.Columns) { if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName)) { _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName); _adapter.InsertCommand.CommandText = _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",@" + _dc.ColumnName); _adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, MySqlDbType.Decimal, _dc.MaxLength, _dc.ColumnName); if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count) break; } } } if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName)); this.BeginTransaction(); try { _adapter.InsertCommand.Transaction = _Command.Transaction; _Command.CommandText = "delete from " + _dt.TableName; _Command.ExecuteNonQuery(); _nResult = _adapter.Update(_dt); this.CommitTransaction(); } catch (Exception ex) { this.RollbackTransaction(); throw ex; } finally { if (flag) this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName)); } return _nResult; }
public DataTable RetDataTable(string sql) { data = new DataTable(); da = new MySqlDataAdapter(sql, conn); cb = new MySqlCommandBuilder(da); da.Fill(data); return data; }
public int SaveNewItems(ref DataTable NewItemTBL) { command = new MySqlCommand(SQL_SELECT_ITEM, GetDBConnection()); adapter = new MySqlDataAdapter(); adapter.SelectCommand = command; builder = new MySqlCommandBuilder(adapter); adapter.InsertCommand = builder.GetInsertCommand(); adapter.ContinueUpdateOnError = true; return adapter.Update(NewItemTBL); }
private void button1_Click(object sender, EventArgs e) { //Sets the environment to run an Sql command with the DataAdapter MySqlCommandBuilder cmb = new MySqlCommandBuilder(da); //Update command that actually updates the value in the DB with what was inputted in the grid(Only count can be altered) da.Update(ds, sTable); //Show successful completion and close the form MessageBox.Show("Table Updated\n\nThis form will now close"); this.Close(); }
private void frmrfid_Load(object sender, EventArgs e) { string strConn = "server=localhost;user id=root;database=pharma;password=;"; ad = new MySqlDataAdapter("select * from `rfid`", strConn); MySqlCommandBuilder builder = new MySqlCommandBuilder(ad); ad.Fill(this.newDataSet.rfid); ad.DeleteCommand = builder.GetDeleteCommand(); ad.UpdateCommand = builder.GetUpdateCommand(); ad.InsertCommand = builder.GetInsertCommand(); MySqlDataAdapter ad3; }
public void commitChanges(String query, DataTable dataTable) { lock (syncLock) { var adapter = new MySqlDataAdapter(); var mcb = new MySqlCommandBuilder(adapter); adapter.SelectCommand = new MySqlCommand(query, conn); adapter.Update(dataTable); dataTable.AcceptChanges(); } }
public DataSet RetDataSet(List <QueryHelper> list) { ds = new DataSet(); foreach (QueryHelper item in list) { da = new MySql.Data.MySqlClient.MySqlDataAdapter(item.Sql, comm); cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(da); da.Fill(ds, item.Table); } return(ds); }
private void btn_add_car_save_Click(object sender, EventArgs e) { try { f_car_ID = Int32.Parse(tb_CarID.Text); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); return; } f_year = tb_Year.Text; f_maker = tb_Maker.Text; f_model = tb_Model.Text; f_color = tb_color.Text; f_trim = tb_Trim.Text; try { f_MSRP = Int32.Parse(tb_MSRP.Text); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } myConn = new MySqlConnection(connStr); MySqlCommand myComm = myConn.CreateCommand(); MySqlDataReader Reader; myComm.CommandText = "INSERT INTO `khnz786`.`Inventory` (`Car_ID`, `Maker`, `Model`, `Trim`, `Color`, `Engine`, `MSRP`)" + "VALUES ("+ f_car_ID +", '"+f_maker+"', '"+f_model+"', '"+f_trim+"', '"+f_color+"', '"+f_year+"', '"+f_MSRP+"');"; myConn.Open(); MySQLDA = new MySqlDataAdapter(myComm.CommandText, myConn); MySQLCB = new MySqlCommandBuilder(MySQLDA); dt = new DataTable(); try { MySQLDA.Fill(dt); MessageBox.Show("Car added successfully!"); Clear(); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } BS = new BindingSource(); BS.DataSource = dt; }
private void btnSearch_Click(object sender, EventArgs e) { String search_value; String column_value = ""; int caseswitch = 0; caseswitch = ddlSearch_Column.SelectedIndex; switch (caseswitch) { case 0: column_value = "Last_Name"; break; case 1: column_value = "Phone"; break; case 2: column_value = "Customer_ID"; break; } search_value = tb_Search.Text; string connStr = "SERVER=www.freesql.org;DATABASE=khnz786;UID=khnz786;PASSWORD=ziakhan"; MySqlConnection myConn = new MySqlConnection(connStr); MySqlCommand myComm = myConn.CreateCommand(); MySqlDataReader Reader; if (search_value == "") { MessageBox.Show("Please enter a value into the search box."); return; } else { myComm.CommandText = "SELECT * FROM Customers WHERE " + column_value + "= '" + search_value + "'"; } myConn.Open(); MySqlDataAdapter MySQLDA = new MySqlDataAdapter(myComm.CommandText, myConn); MySqlCommandBuilder MySQLCB = new MySqlCommandBuilder(MySQLDA); DataTable dt = new DataTable(); MySQLDA.Fill(dt); BindingSource BS = new BindingSource(); BS.DataSource = dt; dg_Search.DataSource = BS; Reader = myComm.ExecuteReader(); //while (Reader.Read()) //{ // MessageBox.Show(column_value); //} myConn.Close(); }
public static void UpdateTable(object tableName) { try { var query = CreateSelectStatement(tableName.ToString()); var dadapt = CreateDataAddapter(query); var comBuild = new MySqlCommandBuilder(dadapt); dadapt.Update(DatabaseRecord.Tables[tableName.ToString()]); } catch (Exception e) { Console.WriteLine(e); } }
public void TestUpdate() { CreateDefaultTable(); MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); DataTable dt = new DataTable(); da.Fill(dt); DataRow dr = dt.NewRow(); dr["id2"] = 2; dr["name"] = "TestName1"; dt.Rows.Add(dr); int count = da.Update(dt); // make sure our refresh of auto increment values worked Assert.AreEqual(1, count, "checking insert count"); Assert.IsNotNull(dt.Rows[ dt.Rows.Count-1 ]["id"], "Checking auto increment column"); dt.Rows.Clear(); da.Fill(dt); dt.Rows[0]["id2"] = 3; dt.Rows[0]["name"] = "TestName2"; dt.Rows[0]["ts"] = DBNull.Value; DateTime day1 = new DateTime(2003, 1, 16, 12, 24, 0); dt.Rows[0]["dt"] = day1; dt.Rows[0]["tm"] = day1.TimeOfDay; count = da.Update(dt); Assert.IsNotNull(dt.Rows[0]["ts"], "checking refresh of record"); Assert.AreEqual(3, dt.Rows[0]["id2"], "checking refresh of primary column"); dt.Rows.Clear(); da.Fill(dt); Assert.AreEqual(1, count, "checking update count"); DateTime dateTime = (DateTime)dt.Rows[0]["dt"]; Assert.AreEqual(day1.Date, dateTime.Date, "checking date"); Assert.AreEqual(day1.TimeOfDay, dt.Rows[0]["tm"], "checking time"); dt.Rows[0].Delete(); count = da.Update(dt); Assert.AreEqual(1, count, "checking insert count"); dt.Rows.Clear(); da.Fill(dt); Assert.AreEqual(0, dt.Rows.Count, "checking row count"); cb.Dispose(); }
private void btnOK_Click(object sender, EventArgs e) { if (MenuSet.pwdSet == '0') { MessageBox.Show("没有设置该权限!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } string strPwd = txtPwd.Text.Trim(); string strSel = string.Format("SELECT * FROM rhdbs.user_info WHERE user_id='{0}' AND pwd='{1}'", TheToken.LoginUser, strPwd); DataTable dt = new DataTable(); MySqlConnection conn = new MySqlConnection(TheToken.DataConnStr); conn.Open(); MySqlDataAdapter da = new MySqlDataAdapter(strSel, conn); try { da.Fill(dt); } catch { MessageBox.Show("密码错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } try { if (txtPwdNew.Text != txtPwdNew2.Text) { MessageBox.Show("两次输入密码不一致!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } dt.Rows[0].BeginEdit(); dt.Rows[0]["pwd"] = txtPwdNew.Text; dt.Rows[0].EndEdit(); MySqlCommandBuilder scb = new MySqlCommandBuilder(da); scb.ReturnGeneratedIdentifiers = false; da.Update(dt.GetChanges()); dt.AcceptChanges(); } catch { MessageBox.Show("密码错误!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } MessageBox.Show("密码修改成功。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); }
private void boton_modificar_Click(object sender, EventArgs e) { MySqlCommandBuilder command_Builder; command_Builder = new MySqlCommandBuilder(dataAdapterActor); DataRow dFila2 = dataSetActor.Tables["Actor"].Rows[indice]; dFila2[1] = textBox2.Text; dFila2[2] = textBox3.Text; dFila2[3] = textBox4.Text; dataAdapterActor.Update(dataSetActor, "Actor"); button1.PerformClick(); }
private void btn_addCustomer_Click(object sender, EventArgs e) { try { f_customer_ID = Int32.Parse(tb_CustomerID.Text); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); return; } f_last_name = tb_LastName.Text; f_first_name = tb_FirstName.Text; f_DOB = tbm_DOB.Text; f_address = tb_Address.Text; f_city = tb_City.Text; f_state = tb_State.Text; f_zip = tb_Zip.Text; f_phone = tb_phone.Text; myConn = new MySqlConnection(connStr); MySqlCommand myComm = myConn.CreateCommand(); MySqlDataReader Reader; myComm.CommandText = "INSERT INTO `khnz786`.`Customers` (`Customer_ID`, `Last_Name`, `First_Name`, `DOB`, `Address`, `City`, `State`, `Zip`, `Phone`)" + "VALUES (" + f_customer_ID + ", '" + f_last_name + "', '" + f_first_name + "', '" + f_DOB + "', '"+ f_address +"', '"+ f_city +"', '"+ f_state +"', '"+ f_zip +"', '"+ f_phone +"');"; myConn.Open(); MySQLDA = new MySqlDataAdapter(myComm.CommandText, myConn); MySQLCB = new MySqlCommandBuilder(MySQLDA); dt = new DataTable(); try { MySQLDA.Fill(dt); MessageBox.Show("Customer added successfully!"); Clear(); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } BS = new BindingSource(); BS.DataSource = dt; }
private void Updator_Click(object sender, EventArgs e) { try { mconnect = new MySqlConnection(sconnect); mconnect.Open(); MySqlCommandBuilder cmb = new MySqlCommandBuilder(da); da.Update(ds.Tables["MetroList"]); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } if (mconnect != null) mconnect.Close(); }
private void button1_Click(object sender, EventArgs e) { try { commandBuilder = new MySqlCommandBuilder(dataAdapter); dataAdapter.Update(dt); dt = new DataTable(); dataAdapter.Fill(dt); //connection.Close(); dataGridView1.DataSource = dt; MessageBox.Show("Updated succesfully!", "Succes!", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show("There was an error while triyng to update!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// Metodo que actualiza un cambio del datagrid en la BD. /// </summary> /// <param name="sender"></param> Boton Actualizar. /// <param name="e"></param> Evento del boton. private void buttonModificar_Click(object sender, RoutedEventArgs e) { try { MySqlCommandBuilder builder = new MySqlCommandBuilder(adaptador); adaptador.UpdateCommand = builder.GetUpdateCommand(); int numeroCambios = adaptador.Update(dt); if (numeroCambios > 0) MessageBox.Show("Actualizado"); else MessageBox.Show("No se ha actualizado ningun registro"); } catch (Exception ex) { MessageBox.Show("Error al modificar la tabla: " + ex.ToString()); } }
public DataTable RetDataTable(string sql) { data = new DataTable(); try { da = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, comm); cb = new MySql.Data.MySqlClient.MySqlCommandBuilder(da); } catch (Exception ex) { throw new Exception(ex.Message); } finally { da.Fill(data); } return(data); }
public bebida() { InitializeComponent(); this.FormBorderStyle = FormBorderStyle.FixedSingle; this.myCmdQuery= new MySqlCommand(); this.myDataAdapter=new MySqlDataAdapter(); this.myBindingSource=new BindingSource(); this.myCommandBuilder =new MySqlCommandBuilder(); this.myDataSet=new DataSet(); this.myStringCon= "Server=localhost;" + "Database=pandc;" + "User ID=root;" + "Password=angel666;" + "Pooling=false;"; }
private void button8_Click(object sender, EventArgs e) { try { string myConnection = "datasource=localhost;port=3306;username=root;password=root"; MySqlConnection myConn = new MySqlConnection(myConnection); MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(); myDataAdapter.SelectCommand = new MySqlCommand("select * Hospital;", myConn); MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter); myConn.Open(); DataSet ds = new DataSet(); MessageBox.Show("З'єднання з БД успішне"); myConn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
/// <summary> /// Performs command and creates a datatable /// </summary> /// <param name="sqlCommand">Sql command to perform</param> /// <param name="tableName">Tablename to store under in cache</param> /// <returns></returns> public DataTable LoadNewData(MySqlCommand sqlCommand, string tableName) { //If a table alredy exists, return the data inside if (_tables.Any(x => (x.DataTable.TableName == tableName)) == true) { var aux = _tables.Find(x => (x.DataTable.TableName == tableName)); sqlCommand.Connection = Connection; aux.DataAdapter.SelectCommand = sqlCommand; aux.DataTable.Clear(); aux.DataAdapter.Fill(aux.DataTable); return(aux.DataTable); } sqlCommand.Connection = Connection; var mySqlDataAdapter = new MySqlDataAdapter(sqlCommand); var commandBuilder = new MySql.Data.MySqlClient.MySqlCommandBuilder(); var data = new DataTable(); mySqlDataAdapter.Fill(data); data.TableName = tableName; var tableConnection = new Model.MySqlConnectionHandler.TableConnection { DataAdapter = mySqlDataAdapter, CommandBuilder = commandBuilder, DataTable = data }; _tables.Add(tableConnection); return(data); }
//存储过程 public int CallSqlSP(string spName, params System.Object[] args) { System.Data.DataSet result = new System.Data.DataSet(); mDataAdapter.SelectCommand = new MySql.Data.MySqlClient.MySqlCommand("select * from sys.parameters where object_id=object_id('" + spName + "')", mConnection); MySql.Data.MySqlClient.MySqlCommandBuilder myCB = new MySql.Data.MySqlClient.MySqlCommandBuilder(mDataAdapter); mDataAdapter.Fill(result, "SP"); List <string> list = new List <string>(); foreach (System.Data.DataRow r in result.Tables[0].Rows) { list.Add(r["name"].ToString()); } if (args.Count() != list.Count()) { //参数不符 System.Diagnostics.Debug.WriteLine("运行存储过程失败:参数不符"); return(-1); } int reNum = -1; MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(spName, mConnection); cmd.CommandType = System.Data.CommandType.StoredProcedure; for (int i = 0; i < list.Count(); i++) { cmd.Parameters.AddWithValue(list[i], args[i].ToString()); } try { reNum = cmd.ExecuteNonQuery(); } catch (System.Exception) { return(-1); } return(reNum); }