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 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);
}
}
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);
}
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;
}
/// <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 void BatchUpdatesAndDeletes()
{
execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
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 = 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.AreEqual(1, listener.Find("Query Opened: UPDATE"));
}
public void TestBatchingMixed()
{
execSQL("DROP TABLE IF EXISTS Test");
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", conn);
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.DeleteCommand.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";
DataRow row = dt.NewRow();
row["id"] = 7;
row["name"] = "foobar";
dt.Rows.Add(row);
dt.Rows[1].Delete();
da.UpdateBatchSize = 0;
da.Update(dt);
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(3, dt.Rows.Count);
Assert.AreEqual(4, dt.Rows[0]["id"]);
Assert.AreEqual(6, dt.Rows[1]["id"]);
Assert.AreEqual(7, dt.Rows[2]["id"]);
Assert.AreEqual("Test 1", dt.Rows[0]["name"]);
Assert.AreEqual("new test value #2", dt.Rows[1]["name"]);
Assert.AreEqual("foobar", dt.Rows[2]["name"]);
}
private void GetData()
{
table_names = new String[] {
"client_balance",
"client_cashflow",
"client_cashflow_type",
"client_info",
"commodity_category",
"futures_account_balance",
"futures_account_info",
"futures_cashflow",
"futures_cashflow_type",
"futures_contracts",
"futures_transactions",
"futures_verbose_positions",
"options_contracts",
"options_direction_type",
"options_transactions",
"options_types",
"options_verbose_positions"
};
view_names = new String[]
{
"client_balance_join",
"client_cashflow_view",
"commodity_category_view",
"futures_account_balance_view",
"futures_cashflow_view",
"futures_contracts_view",
"futures_positions_summary",
"futures_transactions_view",
"futures_verbose_positions_view",
"options_contracts_view",
"options_direction_type_view",
"options_positions_summary",
"options_transactions_view",
"options_types_view",
"options_verbose_positions_view",
"non_trade_dates",
"business_state_view",
"option_settle_info_view",
"future_settle_info_view",
"business_current_state",
"option_position_settle_info",
"future_position_settle_info",
"business_overview"
};
String selectString = "";
foreach (String t in table_names)
{
selectString = String.Format("select * from {0};", t);
MySqlCommand command = new MySqlCommand(selectString, this.sql_connection);
MySqlDataAdapter adapter = new MySqlDataAdapter();
command.CommandType = System.Data.CommandType.Text;
adapter.SelectCommand = command;
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.FillSchema(this, System.Data.SchemaType.Source, t);
adapter.FillSchema(display_ds, SchemaType.Source, t);
adapter.Fill(this, t);
adapter.Fill(display_ds, t);
adapterDict.Add(t, adapter);
}
foreach (String t in view_names)
{
selectString = String.Format("select * from {0};", t);
if (t == "futures_verbose_positions_view")
selectString = "SELECT * FROM futures_verbose_positions;";
else if (t == "business_current_state")
selectString = "SELECT * FROM accum_business_pnl ORDER BY settle_day DESC LIMIT 1";
MySqlCommand command = new MySqlCommand(selectString, this.sql_connection);
MySqlDataAdapter adapter = new MySqlDataAdapter();
command.CommandType = System.Data.CommandType.Text;
adapter.SelectCommand = command;
if (t == "non_trade_dates")
{
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
}
adapter.FillSchema(this, System.Data.SchemaType.Source, t);
adapter.FillSchema(display_ds, SchemaType.Source, t);
adapter.Fill(this, t);
adapter.Fill(display_ds, t);
adapterDict.Add(t, adapter);
}
foreach (System.Data.DataTable table in this.Tables)
{
foreach (System.Data.DataColumn col in table.Columns)
{
col.AllowDBNull = true;
}
}
foreach (System.Data.DataTable table in this.display_ds.Tables)
{
foreach (System.Data.DataColumn col in table.Columns)
{
col.AllowDBNull = true;
}
}
{
System.Data.DataTable client_table = this.Tables["client_balance_join"];
var tmp_client_table = this.display_ds.Tables["client_balance_join"];
client_table.PrimaryKey = new System.Data.DataColumn[] { client_table.Columns["client_id"] };
tmp_client_table.PrimaryKey = new System.Data.DataColumn[] { tmp_client_table.Columns["client_id"] };
}
{
System.Data.DataTable sum_table = this.Tables["options_positions_summary"];
var tmp_sum_table = this.display_ds.Tables["options_positions_summary"];
sum_table.PrimaryKey = new System.Data.DataColumn[] { sum_table.Columns["client_id"], sum_table.Columns["contract_code"], sum_table.Columns["long_short"] };
tmp_sum_table.PrimaryKey = new System.Data.DataColumn[] { tmp_sum_table.Columns["client_id"], tmp_sum_table.Columns["contract_code"], tmp_sum_table.Columns["long_short"] };
}
{
System.Data.DataTable sum_table = this.Tables["futures_positions_summary"];
var tmp_sum_table = this.display_ds.Tables["futures_positions_summary"];
sum_table.PrimaryKey = new System.Data.DataColumn[] { sum_table.Columns["account_no"], sum_table.Columns["contract_code"], sum_table.Columns["long_short"] };
tmp_sum_table.PrimaryKey = new System.Data.DataColumn[] { tmp_sum_table.Columns["account_no"], tmp_sum_table.Columns["contract_code"], tmp_sum_table.Columns["long_short"] };
}
foreach (String t in table_names)
{
System.Data.DataTable table = Tables[t];
MySqlDataAdapter adapter = adapterDict[t];
System.Data.Common.DataTableMapping mapping = adapter.TableMappings.Add(t, t);
foreach (System.Data.DataColumn col in table.Columns)
{
String mappedName = "";
if (colNameDict.TryGetValue(col.ColumnName, out mappedName))
mapping.ColumnMappings.Add(col.ColumnName, mappedName);
}
}
foreach (String t in view_names)
{
System.Data.DataTable table = Tables[t];
MySqlDataAdapter adapter = adapterDict[t];
System.Data.Common.DataTableMapping mapping = adapter.TableMappings.Add(t, t);
foreach (System.Data.DataColumn col in table.Columns)
{
String mappedName = "";
if (colNameDict.TryGetValue(col.ColumnName, out mappedName))
mapping.ColumnMappings.Add(col.ColumnName, mappedName);
}
}
//this.Update();
foreach (System.Data.DataTable table in Tables)
{
foreach (System.Data.DataColumn col in table.Columns)
{
String mappedName = "";
if (colNameDict.TryGetValue(col.ColumnName, out mappedName))
col.ColumnName = mappedName;
}
}
foreach (System.Data.DataTable table in display_ds.Tables)
{
foreach (System.Data.DataColumn col in table.Columns)
{
String mappedName = "";
col.AllowDBNull = true;
if (colNameDict.TryGetValue(col.ColumnName, out mappedName))
col.ColumnName = mappedName;
}
}
this.Tables.Add("risk_info");
using (DataTable table = this.Tables["risk_info"])
{
table.Columns.Add("客户编号", Type.GetType("System.UInt32"));
table.Columns.Add("合约代码", Type.GetType("System.String"));
table.Columns.Add("标的代码", Type.GetType("System.String"));
table.Columns.Add("买卖方向", Type.GetType("System.String"));
table.Columns.Add("标的现价", Type.GetType("System.Decimal"));
table.Columns.Add("数量", Type.GetType("System.Double"));
table.Columns.Add("到期天数", Type.GetType("System.UInt32"));
table.Columns.Add("波动率", Type.GetType("System.Double"));
table.Columns.Add("Delta", Type.GetType("System.Double"));
table.Columns.Add("Gamma", Type.GetType("System.Double"));
table.Columns.Add("Theta", Type.GetType("System.Double"));
table.Columns.Add("Vega", Type.GetType("System.Double"));
table.Columns.Add("Rho", Type.GetType("System.Double"));
table.PrimaryKey = new DataColumn[] { table.Columns["客户编号"], table.Columns["合约代码"], table.Columns["买卖方向"] };
}
this.Tables.Add("risk_info_gross");
using (DataTable table = this.Tables["risk_info_gross"])
{
table.Columns.Add("标的代码", Type.GetType("System.String"));
table.Columns.Add("标的现价", Type.GetType("System.Decimal"));
table.Columns.Add("Delta", Type.GetType("System.Double"));
table.Columns.Add("Gamma", Type.GetType("System.Double"));
table.Columns.Add("Theta", Type.GetType("System.Double"));
table.Columns.Add("Vega", Type.GetType("System.Double"));
table.Columns.Add("Rho", Type.GetType("System.Double"));
table.PrimaryKey = new DataColumn[] { table.Columns["标的代码"] };
}
using (var table = this.display_ds.Tables["futures_verbose_positions_view"])
{
table.Columns.Add("盯市盈亏", Type.GetType("System.Decimal"));
}
//this.display_ds.Tables["business_state_view"].DefaultView.Sort = "结算日 DESC"; //将业务状态表按日期倒序排列
UpdateGreeks();
UpdateMarkToMarketPnl();
}
private void button4_Click(object sender, EventArgs e)
{
dgvSauer.Visible = false;
dataGridView1.Visible = true;
dbcMySql = new MySqlConnection(myconnectionstring);
dbcMySql.Open();
string strQuery = "";
strQuery = "SELECT * FROM Doedsau WHERE flokkID='" + strFlokkID + "'";
sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);
sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
dataGridView1.DataSource = bindingSource;
dbcMySql.Close();
dataGridView1.Columns["flokkID"].Visible = false;
MySqlConnection dbconn = new MySqlConnection(myconnectionstring);
MySqlCommand cmdSauID = dbconn.CreateCommand();
MySqlDataReader ReaderSauID;
cmdSauID.CommandText = "SELECT * FROM Sauer WHERE flokkID='" + strFlokkID + "'";
dbconn.Open();
ReaderSauID = cmdSauID.ExecuteReader();
ReaderSauID.Read();
String strSauID = ReaderSauID.GetValue(0).ToString();
dbconn.Close();
webBrowser1.ScriptErrorsSuppressed = true;
webBrowser1.Navigate("http://folk.ntnu.no/kenneaas/sau/doedSauID/index.php?sauID=" + strSauID);
}
/* Zapis zmian w tabeli towary (przycisk ZAPISZ) */
private void saveChanges_towary()
{
//SqlDataAdapter generuje update na podstawie poprzedniego zapytania
try
{
DataTable changes = ((DataTable)dataGridView_towary.DataSource).GetChanges(); //zmiany w tabeli towary
if (changes != null)
{
MySqlCommandBuilder mcb = new MySqlCommandBuilder(Database.mySqlDataAdapter_content);
Database.mySqlDataAdapter_content.UpdateCommand = mcb.GetUpdateCommand();
Database.mySqlDataAdapter_content.Update(changes);
((DataTable)dataGridView_towary.DataSource).AcceptChanges();
}
}
catch (Exception ex)
{
Alert.Warning(ex.Message);
}
}
/// <summary>
/// Update table when DataGridView row changed
/// </summary>
/// <param name="view">DataGridView control</param>
public void Update(DataGridView view)
{
if (this.m_mysqlConnection != null && this.m_mysqlConnection.State == ConnectionState.Open)
{
var changes = ((DataTable)view.DataSource).GetChanges();
if (changes != null)
{
var commandBuilder = new MySqlCommandBuilder(this.m_mysqlAdapter);
this.m_mysqlAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
this.m_mysqlAdapter.Update(changes);
((DataTable)view.DataSource).AcceptChanges();
}
}
}
public void fillDataGridSearchSheep(DataGridView dgwSearchSheep, String strFlokkID)
{
dbcMySql = new MySqlConnection(myconnectionstring);
dbcMySql.Open();
string strQuery = "";
strQuery = "SELECT * FROM Sauer WHERE flokkID='" + strFlokkID + "'";
sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);
sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
dgwSearchSheep.DataSource = bindingSource;
dbcMySql.Close();
dgwSearchSheep.Columns["flokkID"].Visible = false;
}
public void fillDataGridViewMapSearch(DataGridView dgvSauer, String strSearchName, String strFlokkID)
{
dbcMySql = new MySqlConnection(myconnectionstring);
dbcMySql.Open();
string strQuery = "";
strQuery = "SELECT * FROM Sauer WHERE flokkID='"+strFlokkID+"' AND (navn LIKE '%" + strSearchName + "%' OR sauID LIKE '%" + strSearchName + "%')";
sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);
sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
dgvSauer.DataSource = bindingSource;
dbcMySql.Close();
dgvSauer.Columns["flokkID"].Visible = false;
}
private void frmpayrolldetails_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'newDataSet.payrolldetails' table. You can move, or remove it, as needed.
this.payrolldetailsTableAdapter.Fill(this.newDataSet.payrolldetails);
string strConn = "server=localhost;user id=root;database=pharma;password=;";
ad = new MySqlDataAdapter("select * from `payrolldetails`", strConn);
MySqlCommandBuilder builder = new MySqlCommandBuilder(ad);
ad.Fill(this.newDataSet.payrolldetails);
ad.DeleteCommand = builder.GetDeleteCommand();
ad.UpdateCommand = builder.GetUpdateCommand();
ad.InsertCommand = builder.GetInsertCommand();
MySqlDataAdapter ad3;
}
/// <summary>
/// 更新,通过DataTable,IDataAdapter更新至数据库
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="ida">适配器</param>
/// <returns></returns>
public static bool UpdateDt(DataTable dt, ref IDataAdapter ida)
{
try
{
MySqlCommandBuilder cb = new MySqlCommandBuilder((MySqlDataAdapter)ida);
((MySqlDataAdapter)ida).UpdateCommand = cb.GetUpdateCommand();
((MySqlDataAdapter)ida).Update(dt);
//da.Dispose();
//dt.Dispose();
}
catch (Exception ce)
{
error = "\r\n错误信息:" + ce.Message;
Comdata.WriteLog("Update DataTable 失败\r\n" + ce.Message);
return false;
}
return true;
}
private void dgv_RowValidated(object sender, DataGridViewCellEventArgs e)
{
DataTable changes = ((DataTable)dgv.DataSource).GetChanges();
DataTable changes2 = ((DataTable)dgv2.DataSource).GetChanges();
if (changes != null)
{
MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
mySqlDataAdapter.Update(changes);
}
if (changes2 != null)
{
MySqlCommandBuilder mcb = new MySqlCommandBuilder(mySqlDataAdapter);
mySqlDataAdapter.UpdateCommand = mcb.GetUpdateCommand();
mySqlDataAdapter.Update(changes2);
};
}
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"]);
}
internal void getUpLicDataset()
{
string mySqlCmd =
" SELECT lic_db_id, mr_manufacturer, mr_serial_number, `valid`, `product_code` " +
"FROM systemlic " +
"WHERE CONCAT( mr_manufacturer, mr_serial_number ) " +
"IN ('Dell Inc.CR742N1', 'TOSHIBA7B357968Q', 'Compaq9X35KQDZD33W', 'Dell Inc.3T1ddffe', 'Dell Inc.3T182L1', 'Dell Inc.18Z82L1', 'IBML3HZH46') ";
MySqlDataAdapter dbDataAdptr = new MySqlDataAdapter(mySqlCmd, dbConn);
dbDataAdptr.SelectCommand.CommandType = System.Data.CommandType.Text;
MySqlCommandBuilder cmdBld = new MySqlCommandBuilder(dbDataAdptr);
DataTable syslic = new DataTable();
try
{
dbDataAdptr.Fill(syslic);
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
OutTable2Console(syslic);
Console.WriteLine("valid = " + syslic.Columns["valid"].DataType.ToString() + " " + syslic.Columns["product_code"].DataType.ToString());
syslic.PrimaryKey = new DataColumn[1] { syslic.Columns["lic_db_id"] };
syslic.Rows[3]["valid"] = 7;
syslic.Rows[1]["product_code"] = "";
OutTable2Console(syslic);
var ouput = dbDataAdptr.Update(syslic);
Console.WriteLine(cmdBld.GetUpdateCommand(true).CommandText);
// UPDATE `systemlic` SET `mr_manufacturer` = @mr_manufacturer, `mr_serial_number` = @mr_serial_number, `valid` = @valid, `product_code` = @product_code WHERE ((`l ic_db_id` = @Original_lic_db_id) AND (`mr_manufacturer` = @Original_mr_manufactu rer) AND (`mr_serial_number` = @Original_mr_serial_number) AND (`valid` = @Origi nal_valid) AND (`product_code` = @Original_product_code))
Console.WriteLine("updateOutput = " + ouput);
Console.ReadKey();
}
private void button5_Click(object sender, EventArgs e)
{
dgvSauer.Visible = false;
dataGridView1.Visible = true;
webBrowser1.ScriptErrorsSuppressed = true;
webBrowser1.Navigate("http://folk.ntnu.no/kenneaas/sau/doedFlokkID/index.php?flokkID=" + strFlokkID+"");
dbcMySql = new MySqlConnection(myconnectionstring);
dbcMySql.Open();
string strQuery = "";
strQuery = "SELECT * FROM Doedsau WHERE flokkID='" + strFlokkID + "'";
sqlAdapter = new MySqlDataAdapter(strQuery, dbcMySql);
sqlCommandBuilder = new MySqlCommandBuilder(sqlAdapter);
sqlAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
dataTable = new DataTable();
sqlAdapter.Fill(dataTable);
bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
dataGridView1.DataSource = bindingSource;
dbcMySql.Close();
dataGridView1.Columns["flokkID"].Visible = false;
}
/// <summary>
/// Commits any changes to segment data set to the database
/// </summary>
public void CommitChanges()
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString;
MySqlConnection connection = new MySqlConnection(connString);
MySqlDataAdapter dsAdapter = new MySqlDataAdapter();
dsAdapter.SelectCommand = new MySqlCommand("SELECT * FROM Segments WHERE Segments.Trip_ID = " + this.ID, connection);
MySqlCommandBuilder dsBuilder = new MySqlCommandBuilder(dsAdapter);
dsBuilder.GetUpdateCommand();
dsBuilder.GetInsertCommand();
dsBuilder.GetDeleteCommand();
try
{
dsAdapter.Update(this.SegmentDataSet, "Segments");
}
catch (Exception e)
{
System.Diagnostics.Debug.Write(e.ToString());
}
finally
{
dsBuilder.Dispose();
dsAdapter.Dispose();
connection.Dispose();
}
}
