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 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; }
public void TestBatchingMixed() { 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 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; }
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(); }
/// <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(); } }