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