public DataSet TimKiem(string query) { try { if (conn.State != ConnectionState.Open) { conn.Open(); } sqlda = new MySqlDataAdapter("SELECT * FROM hocsinhsinhvien" + query, conn); cmdbuilder = new MySqlCommandBuilder(sqlda); sqlda.InsertCommand = cmdbuilder.GetInsertCommand(); sqlda.UpdateCommand = cmdbuilder.GetUpdateCommand(); sqlda.DeleteCommand = cmdbuilder.GetDeleteCommand(); dataset = new DataSet(); sqlda.Fill(dataset, "hocsinhsinhvien"); return(dataset); } catch (Exception e) { Console.WriteLine(e.Message); } finally { conn.Close(); } return(null); }
public void Sql() { mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=phonebook;" + "UID=root;" + "PASSWORD=;"); mySqlConnection.Open(); 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; dataGridView1.DataSource = bindingSource; bindingNavigator1.BindingSource = bindingSource; }
private void InitScheduler() { schedulerDataSet = new DataSet(); string selectAppointments = "SELECT * FROM Appointments"; string selectResources = "SELECT * FROM resources"; dbconnection = new MySqlConnection(connection.connectionString); dbconnection.Open(); appointmentDataAdapter = new MySqlDataAdapter(selectAppointments, dbconnection); appointmentDataAdapter.RowUpdated += appointmentDataAdapter_RowUpdated; appointmentDataAdapter.Fill(schedulerDataSet, "Appointments"); resourceDataAdapter = new MySqlDataAdapter(selectResources, dbconnection); resourceDataAdapter.Fill(schedulerDataSet, "Resources"); MapAppointmentData(); MapResourceData(); MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(appointmentDataAdapter); appointmentDataAdapter.InsertCommand = cmdBuilder.GetInsertCommand(); appointmentDataAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand(); appointmentDataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand(); schedulerStorage.Appointments.DataSource = schedulerDataSet; schedulerStorage.Appointments.DataMember = "Appointments"; schedulerStorage.Resources.DataSource = schedulerDataSet; schedulerStorage.Resources.DataMember = "Resources"; dbconnection.Close(); }
public override int ExecuteMUpdate(string sql, DataSet dataSet, params object[] paramValues) { try { OpenConnect(); List<string> sqls = new List<string>(); do { sqls.Add(sql.Substring(0, sql.IndexOf(";"))); sql = sql.Substring(sql.IndexOf(";") + 1); } while (sql.IndexOf(";") > 0); int i = 0; foreach (string SQL in sqls) { _adp.SelectCommand = new MySqlCommand(SQL, _conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(_adp); _adp.InsertCommand = cb.GetInsertCommand(); _adp.UpdateCommand = cb.GetUpdateCommand(); _adp.Update(dataSet.Tables[i]); i++; } return 1; } finally { CloseConnect(); } }
new void Refresh() { string connStr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false", "lserver", "SigData", "SigData009", "SigData"); conn = new MySqlConnection(connStr); // To emulate a long-running query, wait for a few seconds before retrieving the real data. MySqlCommand command = null; DataTable s = new DataTable();; command = new MySqlCommand(); command.CommandText = "Select * from NGL_Pastors"; command.Connection = conn; conn.Open(); dt = new DataTable(); da = new MySqlDataAdapter(command.CommandText, conn); cb = new MySqlCommandBuilder(da); //da.SelectCommand = command; da.InsertCommand = cb.GetInsertCommand(); da.DeleteCommand = cb.GetDeleteCommand(); da.UpdateCommand = cb.GetUpdateCommand(); this.da.Fill(dt); Grid.DataSource = dt; Grid.DataBind(); MoveLast(); }
public void queryTable(String cmd) { this.Validate(); mConn = new MySqlConnection(connectionString); mConn.Open(); //createTable(); mComm = new MySqlCommand(cmd, mConn); mAdapter = new MySqlDataAdapter(); mAdapter.SelectCommand = mComm; mTable = new DataTable(); mTable.Load(mComm.ExecuteReader()); MySqlCommandBuilder builder = new MySqlCommandBuilder(mAdapter); mAdapter.Update(mTable); builder.GetUpdateCommand(); //dataGridView1.AutoGenerateColumns = true; dataGridView1.DataSource = mTable; mConn.Close(); }
public Form1(string login, string pass) { InitializeComponent(); string ConnectString = "Database = аис_увп; Data Source = localhost; User Id=" + login + "; Password ="******";"; Form1.ConnectString = ConnectString; this.StartPosition = FormStartPosition.CenterParent; this.dataGridView1.BackgroundColor = Color.CornflowerBlue; dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //устанавливаем полное выделение строки button5.Enabled = false; button1.Enabled = false; using (MySqlConnection connection = new MySqlConnection(ConnectString)) { connection.Open(); dataAdapter = new MySqlDataAdapter(CommandText, connection); ds = new DataSet(); MySqlCommandBuilder bulder = new MySqlCommandBuilder(dataAdapter); dataAdapter.UpdateCommand = bulder.GetUpdateCommand(); dataAdapter.InsertCommand = bulder.GetInsertCommand(); dataAdapter.DeleteCommand = bulder.GetDeleteCommand(); dataAdapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; } }
private void addToolStripMenuItem_Click(object sender, EventArgs e) { try { //mTable = mTable.GetChanges(); //mAdapter.Update(mTable); //dataGridView1.Update(); //cb.RefreshSchema(); //mAdapter.UpdateCommand = cb.GetUpdateCommand(); //mAdapter.Update(mTable); //dataGridView1.DataSource = mTable; mConn = new MySqlConnection(connectionString); mConn.Open(); mAdapter = new MySqlDataAdapter(tableSelection, mConn); MySqlCommandBuilder builder = new MySqlCommandBuilder(mAdapter); mAdapter.Update(mTable); builder.GetUpdateCommand(); mConn.Close(); dataGridView1.Update(); this.Validate(); } catch (Exception ex) { MessageBox.Show(ex.Message); } mAdapter.Update(mTable); }
public static void AddBackgroundRecordDgv(DataGridView dgv, string tableName) { var connStr = "server=localhost;user=root;database=mydb;password=12332"; using (var conn = new MySqlConnection(connStr)) { try { conn.Open(); var sql = "SELECT * FROM " + tableName; var adapter = new MySqlDataAdapter(sql, conn); var builder = new MySqlCommandBuilder(adapter); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.Update((DataTable)dgv.DataSource); ((DataTable)dgv.DataSource).AcceptChanges(); MessageBox.Show(Helper.MessageAdded); } catch (Exception e) { MessageBox.Show(e.ToString()); } } }
// //XỬ LÝ VỚI TIỂU SỬ // //Lấy tiểu sử với mã định danh public DataSet getTieuSu(string madinhdanh) { try { if (conn.State != ConnectionState.Open) { conn.Open(); } sqlda = new MySqlDataAdapter("SELECT * FROM tieusu WHERE madinhdanh='" + madinhdanh + "'", conn); cmdbuilder = new MySqlCommandBuilder(sqlda); sqlda.InsertCommand = cmdbuilder.GetInsertCommand(); sqlda.UpdateCommand = cmdbuilder.GetUpdateCommand(); sqlda.DeleteCommand = cmdbuilder.GetDeleteCommand(); dataset = new DataSet(); sqlda.Fill(dataset, "tienantiensu"); return(dataset); } catch (Exception e) { Console.WriteLine(e.Message); } finally { conn.Close(); } return(null); }
public void Load() { mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=ISA;" + "UID=root;" + "PASSWORD=;"); mySqlConnection.Open(); string query = "SELECT * FROM contacts "; 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); mySqlDataAdapter.Fill(ds, start, viewnow, "contacts"); PreviousBtn.Enabled = false; bindingSource = new BindingSource(); bindingSource.DataSource = dataTable; dataGridView1.DataSource = ds.Tables[0]; bindingNavigator1.BindingSource = bindingSource; }
private void delte(object sender, EventArgs e) { mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=phone;" + "UID=root;" + "PASSWORD=;"); mySqlConnection.Open(); string query = "DELETE * FROM phonebook WHERE "; 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.Dispose(); ///Fill(dataTable); bindingSource = new BindingSource(); bindingSource.DataSource = dataTable; dataGridView1.DataSource = bindingSource; bindingNavigator1.BindingSource = bindingSource; }
public void BatchUpdatesAndDeletes() { st.execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))"); st.execSQL("INSERT INTO test VALUES (1, 'boo'), (2, 'boo'), (3, 'boo')"); MySqlTrace.Listeners.Clear(); MySqlTrace.Switch.Level = SourceLevels.All; GenericListener listener = new GenericListener(); MySqlTrace.Listeners.Add(listener); string connStr = st.GetConnectionString(true) + ";logging=true;allow batch=true"; using (MySqlConnection c = new MySqlConnection(connStr)) { c.Open(); MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", c); MySqlCommandBuilder cb = new MySqlCommandBuilder(da); da.UpdateCommand = cb.GetUpdateCommand(); da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; da.UpdateBatchSize = 100; DataTable dt = new DataTable(); da.Fill(dt); dt.Rows[0]["name"] = "boo2"; dt.Rows[1]["name"] = "boo2"; dt.Rows[2]["name"] = "boo2"; da.Update(dt); } Assert.Equal(1, listener.Find("Query Opened: UPDATE")); }
public DataTable DoQuery() { _dbConnector.Connect(); // Which stored procedure to use for retrieving data from server MySqlDataAdapter adapter = new MySqlDataAdapter() { SelectCommand = new MySqlCommand(Procedure, _dbConnector.DbConnectionInstance) { CommandType = CommandType.StoredProcedure } }; DataTable serverTable = new DataTable("TempTable"); // Populates DataTable with records from the server adapter.Fill(serverTable); // Avoid getting mark for archive column which causes index out of bounds during table comparison TargetDataTable.Columns.RemoveAt(TargetDataTable.Columns.Count - 1); // Do updates serverTable = CompareTable(serverTable, TargetDataTable); MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter); builder.GetUpdateCommand(); adapter.Update(serverTable); _dbConnector.Disconnect(); return(null); }
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); }
/// <summary> /// <para> 更新数据库</para> /// <para> 更新成功时返回true,失败时返回false</para> /// </summary> /// <param name="tmpTable"></param> /// <param name="tmpCmd"></param> public bool UpdateDatabase(DataTable tmpTable, string tmpCmd) { try { using (MySqlConnection conn = new MySqlConnection()) { conn.ConnectionString = connString; if (conn.State == ConnectionState.Closed) { conn.Open(); } #region 更新数据库 MySqlDataAdapter adapter = new MySqlDataAdapter(tmpCmd, conn); MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter); commandBuilder.GetUpdateCommand(); adapter.Update(tmpTable); tmpTable.AcceptChanges(); #endregion return(true); } } catch (MySqlException ex) //不能抓取到输入数据的格式错误???? { MessageBox.Show(ex.Message); tmpTable.RejectChanges();//拒绝用户此次修改 return(false); //throw;//注释该行语句的目的是:不抛出该异常,只给出错误提示信息,保证程序运行 } }
public override DataSet getAll() { try { if (conn.State != ConnectionState.Open) { conn.Open(); } sqlda = new MySqlDataAdapter("SELECT *, 'Delete' as 'Change' FROM nhankhau", conn); cmdbuilder = new MySqlCommandBuilder(sqlda); sqlda.InsertCommand = cmdbuilder.GetInsertCommand(); sqlda.UpdateCommand = cmdbuilder.GetUpdateCommand(); sqlda.DeleteCommand = cmdbuilder.GetDeleteCommand(); dataset = new DataSet(); sqlda.Fill(dataset, "nhankhau"); return(dataset); } catch (Exception e) { Console.WriteLine(e.Message); } finally { conn.Close(); } return(null); }
public int executeDataAdapter(DataTable tblName, string strSelectSql) { try { if (connection.State == 0) { createConn(); } adapter.SelectCommand.CommandText = strSelectSql; adapter.SelectCommand.CommandType = CommandType.Text; MySqlCommandBuilder DbCommandBuilder = new MySqlCommandBuilder(adapter); string insert = DbCommandBuilder.GetInsertCommand().CommandText.ToString(); string update = DbCommandBuilder.GetUpdateCommand().CommandText.ToString(); string delete = DbCommandBuilder.GetDeleteCommand().CommandText.ToString(); return(adapter.Update(tblName)); } catch (Exception ex) { throw ex; } }
public void updateChangesInTable(DataTable dt) { try { dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.Update(dt); } catch (MySqlException me) { setErrorDataAndShow("Adatbázis frissítési hiba.\n" + me.Message /*+me.Number*/ ); } catch (Exception e) { setErrorDataAndShow("Adatbázis frissítési hiba.\n" + e.Message /*+me.Number*/ ); } finally { } }
public Form1() { InitializeComponent(); this.StartPosition = FormStartPosition.CenterScreen; this.dataGridView1.BackgroundColor = Color.CornflowerBlue; dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //устанавливаем полное выделение строки dataGridView1.AllowUserToAddRows = false; //запрет на ручное добавление строк button5.Enabled = false; using (MySqlConnection connection = new MySqlConnection(ConnectString)) { connection.Open(); dataAdapter = new MySqlDataAdapter(CommandText, connection); ds = new DataSet(); MySqlCommandBuilder bulder = new MySqlCommandBuilder(dataAdapter); dataAdapter.UpdateCommand = bulder.GetUpdateCommand(); dataAdapter.InsertCommand = bulder.GetInsertCommand(); dataAdapter.DeleteCommand = bulder.GetDeleteCommand(); dataAdapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; } }
public void DgvCellUpdated() { try { MySqlConnection con = new MySqlConnection("datasource=localhost;port=3306;username=root"); MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM test.original", con); con.Open(); DataTable changes = ((DataTable)dgvOriginal.DataSource).GetChanges(); if (changes != null) { MySqlCommandBuilder mcb = new MySqlCommandBuilder(adapter); adapter.UpdateCommand = mcb.GetUpdateCommand(); adapter.Update(changes); ((DataTable)dgvOriginal.DataSource).AcceptChanges(); MessageBox.Show("Cell Updated"); return; } con.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
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; }
/// <summary> /// 테이블을 업데이트합니다. /// </summary> /// <param name="tableName">데이터를 업데이트할 테이블 이름</param> /// <param name="field"></param> /// <param name="value"></param> /// <param name="id"></param> /// <returns></returns> public bool UpdateData(DataTable dt) { bool ret = false; try { if (mSqlConnection.State == ConnectionState.Closed) { mSqlConnection.Open(); } string sQuery = $"Select * from {dt.TableName}"; MySqlDataAdapter myDA = new MySqlDataAdapter(sQuery, mSqlConnection); MySqlCommandBuilder cmb = new MySqlCommandBuilder(myDA); var sqlCommand = cmb.GetUpdateCommand(); myDA.SelectCommand = sqlCommand; int x = myDA.Update(dt); } catch (Exception e) { mSqlConnection.Close(); } mSqlConnection.Close(); return(ret); }
private void button1_Click(object sender, EventArgs e) { mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=mydatabase;" + "UID=root;" + "PASSWORD=;"); mySqlConnection.Open(); string query = "SELECT * FROM table1"; 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; dataGridView1.DataSource = bindingSource; bindingNavigator1.BindingSource = bindingSource; }
public void DgvCellUpdated() { try { MySqlConnection con = new MySqlConnection("datasource=localhost;port=3306;username=root"); MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM test.uc", con); con.Open(); DataTable changes = ((DataTable)dataGridView1.DataSource).GetChanges(); if (changes != null) { MySqlCommandBuilder mcb = new MySqlCommandBuilder(adapter); adapter.UpdateCommand = mcb.GetUpdateCommand(); adapter.Update(changes); ((DataTable)dataGridView1.DataSource).AcceptChanges(); MessageBox.Show("Cell Updated"); return; } } catch (Exception ex) { MessageBox.Show(ex.Message); } foreach (DataGridViewRow row in dataGridView1.Rows) { foreach (DataGridViewColumn col in dataGridView1.Columns) { //row.Cells[col.Index].Style.BackColor = Color.Green; //doesn't work //col.Cells[row.Index].Style.BackColor = Color.Green; //doesn't work dataGridView1[col.Index, row.Index].Style.BackColor = Color.Black; } } }
private void toolStripButton1_Click(object sender, EventArgs e) { string nameToSearch = toolStripTextBox1.Text; mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=mysql;" + "UID=root;" + "PASSWORD=;"); mySqlConnection.Open(); string query = "SELECT * FROM book WHERE Name = " + nameToSearch; 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; dataGridView1.DataSource = bindingSource; bindingNavigator1.BindingSource = bindingSource; }
/// <summary> /// make insert/update for specific table /// </summary> /// <param name="tableName">table name</param> /// <param name="dtable">datatable with values </param> /// <returns></returns> public static int saveChanges(String tableName, DataTable dtable, ref long resID) { int result = 0; string sqlStatement = createSqlStatement(tableName, null, 1, null); string sqlStLastInsertID = "SELECT LAST_INSERT_ID()"; using (var conn = new MySqlConnection(ConnectionString)) { using (var command = new MySqlCommand(sqlStatement, conn)) { //try to execute try { conn.Open(); command.Transaction = conn.BeginTransaction(); //fill dataset and take its first datatable using (var adapter = new MySqlDataAdapter(command)) { MySqlCommandBuilder bld = new MySqlCommandBuilder(adapter); adapter.UpdateCommand = bld.GetUpdateCommand(); adapter.InsertCommand = bld.GetInsertCommand(); result = adapter.Update(dtable); } //Взима ID-то на въведеният запис using (var commandLAST_ID = new MySqlCommand(sqlStLastInsertID, conn)) { ///Log.Trace(sqlStLastInsertID); IFD DD resID = commandLAST_ID.ExecuteScalar().MakeLong(); } // conn.Commit(); } catch (Exception ex) { MessageBox.Show(ex.Message); // conn.Rollback(); //Log.writeError(ex); if ("Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." != ex.Message) { return(-1); } } finally { if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } } } } return(result); }
private void textBox1_TextChanged(object sender, EventArgs e) { mySqlConnection = new MySqlConnection( "SERVER=localhost;" + "DATABASE=phone;" + "UID=root;" + "PASSWORD=;"); mySqlConnection.Open(); string k = textBox1.Text; string query = string.Format("SELECT * FROM phonebook WHERE name LIKE '%{0}' order by id asc", k); 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; dataGridView1.DataSource = bindingSource; bindingNavigator1.BindingSource = bindingSource; }
public DataSet TimKiem(string query) { try { if (conn.State != ConnectionState.Open) { conn.Open(); } if (!String.IsNullOrEmpty(query)) { query = " WHERE " + query; } sqlda = new MySqlDataAdapter("SELECT *, 'Delete' as 'Change' FROM nhankhau" + query, conn); cmdbuilder = new MySqlCommandBuilder(sqlda); sqlda.InsertCommand = cmdbuilder.GetInsertCommand(); sqlda.UpdateCommand = cmdbuilder.GetUpdateCommand(); sqlda.DeleteCommand = cmdbuilder.GetDeleteCommand(); dataset = new DataSet(); sqlda.Fill(dataset, "nhankhau"); return(dataset); } catch (Exception e) { Console.WriteLine(e.Message); } finally { conn.Close(); } return(null); }
private void LoadData() { try { SqlDataAdapter = new MySqlDataAdapter("SELECT *, 'Delete' AS `Delete` FROM `switchboard items`", MySqlConnection); sqlBuilder = new MySqlCommandBuilder(SqlDataAdapter); // inser, update, delete, and more ***** sqlBuilder.GetInsertCommand(); sqlBuilder.GetUpdateCommand(); sqlBuilder.GetDeleteCommand(); //init pull data set, remeber Andrew it; dataSet = new DataSet(); SqlDataAdapter.Fill(dataSet, "switchboard items"); dataGridView1.DataSource = dataSet.Tables["switchboard items"]; //For key for (int i = 0; i < dataGridView1.Rows.Count; i++) { DataGridViewLinkCell linkCell = new DataGridViewLinkCell(); dataGridView1[5, i] = linkCell; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка А101", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public void TestBatchingUpdates() { execSQL("CREATE TABLE Test (id INT, name VARCHAR(20), PRIMARY KEY(id))"); execSQL("INSERT INTO Test VALUES (1, 'Test 1')"); execSQL("INSERT INTO Test VALUES (2, 'Test 2')"); execSQL("INSERT INTO Test VALUES (3, 'Test 3')"); MySqlDataAdapter dummyDA = new MySqlDataAdapter("SELECT * FROM Test", conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(dummyDA); MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test ORDER BY id ASC", conn); da.UpdateCommand = cb.GetUpdateCommand(); da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; DataTable dt = new DataTable(); da.Fill(dt); dt.Rows[0]["id"] = 4; dt.Rows[1]["name"] = "new test value"; dt.Rows[2]["id"] = 6; dt.Rows[2]["name"] = "new test value #2"; da.UpdateBatchSize = 0; da.Update(dt); dt.Rows.Clear(); da.Fill(dt); Assert.AreEqual(3, dt.Rows.Count); Assert.AreEqual(2, dt.Rows[0]["id"]); Assert.AreEqual(4, dt.Rows[1]["id"]); Assert.AreEqual(6, dt.Rows[2]["id"]); Assert.AreEqual("new test value", dt.Rows[0]["name"]); Assert.AreEqual("Test 1", dt.Rows[1]["name"]); Assert.AreEqual("new test value #2", dt.Rows[2]["name"]); }