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