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