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"]);
}
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"));
}
