public void AutoIncrementColumnsOnInsert()
{
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL AUTO_INCREMENT, " +
"name VARCHAR(100), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.InsertCommand.CommandText += "; SELECT last_insert_id()";
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Columns[0].AutoIncrement = true;
Assert.IsTrue(dt.Columns[0].AutoIncrement);
dt.Columns[0].AutoIncrementSeed = -1;
dt.Columns[0].AutoIncrementStep = -1;
DataRow row = dt.NewRow();
row["name"] = "Test";
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
cb.Dispose();
}
public void AutoIncrementColumnsOnInsert2()
{
execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL " +
"AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
MySqlCommand cmd = (MySqlCommand)(cb.GetInsertCommand() as ICloneable).Clone();
cmd.CommandText += "; SELECT last_insert_id() as id";
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.InsertCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows.Clear();
DataRow row = dt.NewRow();
row["name"] = "Test";
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("Test", dt.Rows[0]["name"]);
row = dt.NewRow();
row["name"] = "Test2";
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(2, dt.Rows[1]["id"]);
Assert.AreEqual("Test2", dt.Rows[1]["name"]);
Assert.AreEqual(1, dt.Rows[0]["id"]);
}
public void Search()
{
mySqlConnection.Open();
ds.Clear();
string query = "SELECT * FROM phonebook WHERE name like '%" + txtSearch.Text + "%'";
mySqlDataAdapter = new MySqlDataAdapter(query, mySqlConnection);
mySqlCommandBuilder = new MySqlCommandBuilder(mySqlDataAdapter);
mySqlDataAdapter.UpdateCommand = mySqlCommandBuilder.GetUpdateCommand();
mySqlDataAdapter.DeleteCommand = mySqlCommandBuilder.GetDeleteCommand();
mySqlDataAdapter.InsertCommand = mySqlCommandBuilder.GetInsertCommand();
ds = new DataSet();
mySqlDataAdapter.Fill(ds, "search");
bindingSource = new BindingSource();
bindingSource.DataSource = ds;
dataGridView1.DataSource = bindingSource;
dataGridView1.DataMember = "search";
mySqlConnection.Close();
}
public string SaveDatabase(Stream myStream)
{
// Create and open the connection in a using block. This
// ensures that all resources will be closed and disposed
// when the code exits.
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
// Open the connection in a try/catch block.
try
{
connection.Open();
if (connection.State == ConnectionState.Open)
{
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand();
command.Connection = connection;
myDataSet = new DataSet();
myDataSet.ReadXml(myStream, XmlReadMode.ReadSchema);
command.CommandText = CreateTables(myDataSet);
command.ExecuteNonQuery();
for (int i = 0; i < myDataSet.Tables.Count; i++)
{
command.CommandText = "SELECT * FROM " + myDataSet.Tables[i].TableName;
command.CommandType = CommandType.Text;
adapter.SelectCommand = command;
var builder = new MySqlCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
adapter.Update(myDataSet, myDataSet.Tables[i].TableName);
}
return("Pomyślnie odtworzono bazę danych.");
}
}
catch (Exception ex)
{
return(ex.Message);
}
}
return("Nie udało się odtworzyć bazy danych.");
}
//从数据库获取所有rank数据
public void DownloadRankData()
{
string strSQL = "SELECT * FROM RankData";
MySqlCommand command = new MySqlCommand(strSQL, mDBConnect.mConnection);
mAdap = new MySqlDataAdapter(command);
MySqlCommandBuilder msb = new MySqlCommandBuilder(mAdap);
mAdap.InsertCommand = msb.GetInsertCommand();
mAdap.UpdateCommand = msb.GetUpdateCommand();
mAdap.DeleteCommand = msb.GetDeleteCommand();
mDataSet = new DataSet();
mAdap.Fill(mDataSet, "rankdata");
foreach (DataRow i in mDataSet.Tables["rankdata"].Rows)
{
RankData rd = new RankData();
ServerFrame.DB.DBConnect.FillObject(rd, i);
mRankDatas[rd.RoleId] = rd;
}
}
/// <summary>
/// Creates a MySQLDataAdpter with the given CommandText and associates it with the implemented connection.
/// </summary>
/// <returns></returns>
/// <remarks></remarks>
public MySqlDataAdapter DataAdapter(MySqlCommand Command, bool UseCommandBuilder)
{
MySqlDataAdapter DA = new MySqlDataAdapter(Command);
MySqlCommandBuilder cb = default(MySqlCommandBuilder);
if (UseCommandBuilder)
{
cb = new MySqlCommandBuilder(DA);
cb.SetAllValues = false;
DA.DeleteCommand = cb.GetDeleteCommand();
DA.UpdateCommand = cb.GetUpdateCommand();
DA.InsertCommand = cb.GetInsertCommand();
DA.InsertCommand.Connection = DA.SelectCommand.Connection;
DA.UpdateCommand.Connection = DA.SelectCommand.Connection;
DA.DeleteCommand.Connection = DA.SelectCommand.Connection;
}
return(DA);
}
public DataTable UpdateTable(string Query, DataTable tbl)
{
var conn = new MySqlConnection(cadena_mysql);
MySqlCommand cmd = new MySqlCommand(Query, conn);
MySqlDataAdapter da = new MySqlDataAdapter(Query, conn);
DataTable dt = new DataTable();
var builder = new MySqlCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();
da.DeleteCommand = builder.GetDeleteCommand();
da.InsertCommand = builder.GetInsertCommand();
conn.Open();
da.SelectCommand = cmd;
da.Update(tbl);
tbl.AcceptChanges();
conn.Close();
return(tbl);
}
private void InitializeData()
{
using (MySqlCommand cmd = new MySqlCommand(string.Format("select * from {0}", this.tableName), dao.Connection))
{
cmd.CommandTimeout = 30;
adapter = new MySqlDataAdapter(cmd);
MySqlCommandBuilder cBuilder = new MySqlCommandBuilder(adapter);
cBuilder.SetAllValues = false;
cBuilder.ConflictOption = ConflictOption.OverwriteChanges;
adapter.InsertCommand = cBuilder.GetInsertCommand();
adapter.UpdateCommand = cBuilder.GetUpdateCommand();
adapter.Fill(dataSet);
//A DataView kell a filtereléshez
dataView = dataSet.Tables[0].DefaultView;
autoGrid.DataSource = dataView;
}
CustomizeTableUI();
}
private void 提交选择讲授课程()
{
try
{
using (MySqlConnection conn = new MySqlConnection(mysqlConnectionString))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("select * from section;", conn);
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
MySqlCommandBuilder myCommandBuilder = new MySqlCommandBuilder(sda);
sda.InsertCommand = myCommandBuilder.GetInsertCommand();
sda.UpdateCommand = myCommandBuilder.GetUpdateCommand();
sda.DeleteCommand = myCommandBuilder.GetDeleteCommand();
sda.Update(dataTableBackground); //更新
conn.Close();
}
}
catch (Exception e)
{
MessageBox.Show("无法进入课程目录系统", "严重错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void UsingFunctions()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
st.execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
st.execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
st.execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name, now() as ServerTime FROM Test", st.conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
da.Update(dt);
da.SelectCommand.CommandText = "SELECT id, name, CONCAT(name, ' boo') as newname from Test where id=4";
dt.Clear();
da.Fill(dt);
Assert.Equal(1, dt.Rows.Count);
Assert.Equal("test1", dt.Rows[0]["name"]);
Assert.Equal("test1 boo", dt.Rows[0]["newname"]);
dt.Rows[0]["id"] = 5;
da.Update(dt);
dt.Clear();
da.SelectCommand.CommandText = "SELECT * FROM Test WHERE id=5";
da.Fill(dt);
Assert.Equal(1, dt.Rows.Count);
Assert.Equal("test1", dt.Rows[0]["name"]);
da.SelectCommand.CommandText = "SELECT *, now() as stime FROM Test WHERE id<4";
cb = new MySqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.InsertCommand = cb.GetInsertCommand();
}
/// <summary>
/// 表批量写入
/// 根据行数据 RowState 状态新增、修改
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="sqlEmpty">查询空表脚本，默认*，可选列，会影响数据更新的列</param>
/// <param name="dataAdapter">执行前修改（命令行脚本、超时等信息）</param>
/// <param name="openTransaction">开启事务，默认开启</param>
/// <returns></returns>
public int BulkBatchMySQL(DataTable dt, string sqlEmpty = null, Action <MySqlDataAdapter> dataAdapter = null, bool openTransaction = true)
{
return(SafeConn(() =>
{
var connection = (MySqlConnection)Connection;
MySqlTransaction transaction = openTransaction ? (MySqlTransaction)(Transaction = connection.BeginTransaction()) : null;
var cb = new MySqlCommandBuilder();
if (string.IsNullOrWhiteSpace(sqlEmpty))
{
var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.MySQL);
sqlEmpty = SqlEmpty(sntn);
}
cb.DataAdapter = new MySqlDataAdapter
{
SelectCommand = new MySqlCommand(sqlEmpty, connection, transaction)
};
cb.ConflictOption = ConflictOption.OverwriteChanges;
var da = new MySqlDataAdapter
{
InsertCommand = (MySqlCommand)cb.GetInsertCommand(true),
UpdateCommand = (MySqlCommand)cb.GetUpdateCommand(true)
};
da.InsertCommand.CommandTimeout = 300;
da.UpdateCommand.CommandTimeout = 300;
//执行前修改
dataAdapter?.Invoke(da);
var num = da.Update(dt);
transaction?.Commit();
return num;
}));
}
public void update(String sqlStr, DataSet ds, String tableName)
{
if (conn != null)
{
try
{
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(sqlStr, conn);
MySqlCommandBuilder scb = new MySqlCommandBuilder(dataAdapter);
dataAdapter.InsertCommand = scb.GetInsertCommand();
dataAdapter.DeleteCommand = scb.GetDeleteCommand();
dataAdapter.UpdateCommand = scb.GetUpdateCommand();
dataAdapter.Update(ds, tableName);
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
else
{
MessageBox.Show("数据库未连接！");
}
}
public DataTable updateDataTable(string database, string table, DataTable ds)
{
if (this.Connected)
{
string cmd = "select * from " + database + "." + table;
try
{
MySqlDataAdapter sda = new MySqlDataAdapter(cmd, sConn);
MySqlCommandBuilder scb = new MySqlCommandBuilder(sda);
sda.UpdateCommand = scb.GetUpdateCommand();
sda.InsertCommand = scb.GetInsertCommand();
sda.DeleteCommand = scb.GetDeleteCommand();
sda.Update(ds);
}
catch (Exception ex)
{
errorText = ex.Message;
connected = false;
}
}
return(ds);
}
private void LoadData()
{
try
{
SqlDataAdapter = new MySqlDataAdapter("SELECT *, 'Delete' AS `Delete` FROM `list of departments`", MySqlConnection);
sqlBuilder = new MySqlCommandBuilder(SqlDataAdapter); // inser, update, delete, and more *****
sqlBuilder.GetInsertCommand();
sqlBuilder.GetUpdateCommand();
sqlBuilder.GetDeleteCommand();
//init pull data set, remeber Andrew it;
dataSet = new DataSet();
SqlDataAdapter.Fill(dataSet, "list of departments");
dataGridView1.DataSource = dataSet.Tables["list of departments"]; //For key
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
DataGridViewLinkCell linkCell = new DataGridViewLinkCell();
dataGridView1[3, i] = linkCell;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Ошибка А101", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Editing_Load(object sender, EventArgs e)
{
try
{
myConnection.Open();
string filds1 = filds.Text;
string tables1 = tables.Text;
string query = String.Format("SELECT {0} From {1}", filds1, tables1);//SQL Запрос
//Вывод данных в DataGridView
adap = new MySqlDataAdapter(query, myConnection);
MySqlCommandBuilder bulder = new MySqlCommandBuilder(adap);
adap.UpdateCommand = bulder.GetUpdateCommand();
adap.InsertCommand = bulder.GetInsertCommand();
adap.DeleteCommand = bulder.GetDeleteCommand();
adap.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception)
{
MessageBox.Show("Что-то пошло не так,проверьте правильность ввода данных");
this.Close();
}
}
public void AutoIncrementColumnsOnInsert2()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (id INT UNSIGNED NOT NULL " +
"AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
MySqlCommand cmd = (MySqlCommand)(cb.GetInsertCommand() as ICloneable).Clone();
cmd.CommandText += "; SELECT last_insert_id() as id";
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.InsertCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows.Clear();
DataRow row = dt.NewRow();
row["name"] = "Test";
dt.Rows.Add(row);
da.Update(dt);
Assert.Equal(1, Convert.ToInt32(dt.Rows[0]["id"]));
Assert.Equal("Test", dt.Rows[0]["name"]);
row = dt.NewRow();
row["name"] = "Test2";
dt.Rows.Add(row);
da.Update(dt);
Assert.Equal(2, Convert.ToInt32(dt.Rows[1]["id"]));
Assert.Equal("Test2", dt.Rows[1]["name"]);
Assert.Equal(1, Convert.ToInt32(dt.Rows[0]["id"]));
}
public void UsingFunctions()
{
execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name, now() as ServerTime FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
da.Update(dt);
da.SelectCommand.CommandText = "SELECT id, name, CONCAT(name, ' boo') as newname from Test where id=4";
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test1", dt.Rows[0]["name"]);
Assert.AreEqual("test1 boo", dt.Rows[0]["newname"]);
dt.Rows[0]["id"] = 5;
da.Update(dt);
dt.Clear();
da.SelectCommand.CommandText = "SELECT * FROM Test WHERE id=5";
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test1", dt.Rows[0]["name"]);
da.SelectCommand.CommandText = "SELECT *, now() as stime FROM Test WHERE id<4";
cb = new MySqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.InsertCommand = cb.GetInsertCommand();
}
private void ShowDB(int curPage)
{
ds.Tables.Clear();
mySqlConnection.Open();
int PreviousPageOffSet = (curPage - 1) * pgSize;
string query;
if (!sort)
{
query = "SELECT * FROM phonebook";
}
else
{
if (ascDate)
{
query = "SELECT * FROM phonebook ORDER BY date ASC";
}
else
{
query = "SELECT * FROM phonebook ORDER BY date DESC";
}
}
mySqlDataAdapter = new MySqlDataAdapter(query, mySqlConnection);
mySqlCommandBuilder = new MySqlCommandBuilder(mySqlDataAdapter);
mySqlDataAdapter.UpdateCommand = mySqlCommandBuilder.GetUpdateCommand();
mySqlDataAdapter.DeleteCommand = mySqlCommandBuilder.GetDeleteCommand();
mySqlDataAdapter.InsertCommand = mySqlCommandBuilder.GetInsertCommand();
ds = new DataSet();
mySqlDataAdapter.Fill(ds, "allrows");
mySqlDataAdapter.Fill(ds, PreviousPageOffSet, pgSize, "phonebook");
bindingSource = new BindingSource();
bindingSource.DataSource = ds;
dataGridView1.DataSource = bindingSource;
dataGridView1.DataMember = "phonebook";
mySqlConnection.Close();
}
/// <summary>
/// Подготовка к разбору прайса, чтение таблиц
/// </summary>
public void Prepare()
{
_logger.Debug("начало Prepare");
daForbidden = new MySqlDataAdapter(
String.Format("SELECT PriceCode, LOWER(Forbidden) AS Forbidden FROM farm.Forbidden WHERE PriceCode={0}", _priceInfo.PriceCode), _connection);
daForbidden.Fill(dsMyDB, "Forbidden");
dtForbidden = dsMyDB.Tables["Forbidden"];
_logger.Debug("загрузили Forbidden");
daSynonym = new MySqlDataAdapter(String.Format(@"SELECT
s.SynonymCode,
LOWER(s.Synonym) AS Synonym,
s.ProductId,
s.Junk,
p.CatalogId,
c.Pharmacie,
lower(s.Canonical) as Canonical
FROM farm.Synonym s
join catalogs.products p on p.Id = s.ProductId
join Catalogs.Catalog c on c.Id = p.CatalogId
WHERE s.PriceCode = {0}",
parentSynonym), _connection);
daSynonym.Fill(dsMyDB, "Synonym");
dtSynonym = dsMyDB.Tables["Synonym"];
_logger.Debug("загрузили Synonym");
daExcludes = new MySqlDataAdapter(
String.Format("SELECT Id, CatalogId, ProducerSynonym, PriceCode, OriginalSynonymId FROM farm.Excludes where PriceCode = {0}", parentSynonym), _connection);
var cbExcludes = new MySqlCommandBuilder(daExcludes);
daExcludes.InsertCommand = cbExcludes.GetInsertCommand();
daExcludes.InsertCommand.CommandTimeout = 0;
daExcludes.Fill(dsMyDB, "Excludes");
dtExcludes = dsMyDB.Tables["Excludes"];
_logger.Debug("загрузили Excludes");
dtExcludes.Constraints.Add("ProducerSynonymKey", new[] { dtExcludes.Columns["CatalogId"], dtExcludes.Columns["ProducerSynonym"] }, false);
_logger.Debug("построили индекс по Excludes");
daSynonymFirmCr = new MySqlDataAdapter(
String.Format(@"
SELECT
SynonymFirmCrCode,
CodeFirmCr,
LOWER(Synonym) AS Synonym,
(aps.ProducerSynonymId is not null) as IsAutomatic,
Canonical
FROM
farm.SynonymFirmCr
left join farm.AutomaticProducerSynonyms aps on aps.ProducerSynonymId = SynonymFirmCr.SynonymFirmCrCode
WHERE SynonymFirmCr.PriceCode={0} and Canonical is not null
",
parentSynonym),
_connection);
daSynonymFirmCr.Fill(dsMyDB, "SynonymFirmCr");
daSynonymFirmCr.InsertCommand = new MySqlCommand(@"SELECT farm.CreateProducerSynonym(?PriceCode, ?CodeFirmCr, ?OriginalSynonym, ?IsAutomatic);");
daSynonymFirmCr.InsertCommand.Parameters.Add("?PriceCode", MySqlDbType.Int64);
daSynonymFirmCr.InsertCommand.Parameters.Add("?OriginalSynonym", MySqlDbType.String);
daSynonymFirmCr.InsertCommand.Parameters.Add("?CodeFirmCr", MySqlDbType.Int64);
daSynonymFirmCr.InsertCommand.Parameters.Add("?IsAutomatic", MySqlDbType.Bit);
daSynonymFirmCr.InsertCommand.Connection = _connection;
dtSynonymFirmCr = dsMyDB.Tables["SynonymFirmCr"];
dtSynonymFirmCr.Columns.Add("OriginalSynonym", typeof(string));
dtSynonymFirmCr.Columns.Add("InternalProducerSynonymId", typeof(long));
dtSynonymFirmCr.Columns["InternalProducerSynonymId"].AutoIncrement = true;
_logger.Debug("загрузили SynonymFirmCr");
var adapter = new MySqlDataAdapter(@"select
b.ProductId, p.CatalogId, b.ProducerId, b.EAN13, c.Pharmacie
from Catalogs.BarcodeProducts b
join Catalogs.Products p on b.ProductId = p.Id
join Catalogs.Catalog c on c.Id = p.CatalogId" , _connection);
barcodes = new DataTable();
adapter.Fill(barcodes);
_producerResolver = new ProducerResolver(_stats, dtExcludes, dtSynonymFirmCr);
_producerResolver.Load(_connection);
daUnrecExp = new MySqlDataAdapter(
String.Format("SELECT * FROM farm.UnrecExp WHERE PriceItemId={0} LIMIT 0", priceItemId), _connection);
var cbUnrecExp = new MySqlCommandBuilder(daUnrecExp);
daUnrecExp.AcceptChangesDuringUpdate = false;
daUnrecExp.InsertCommand = cbUnrecExp.GetInsertCommand();
daUnrecExp.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
daUnrecExp.InsertCommand.CommandTimeout = 0;
daUnrecExp.Fill(dsMyDB, "UnrecExp");
dtUnrecExp = dsMyDB.Tables["UnrecExp"];
dtUnrecExp.Columns["AddDate"].DataType = typeof(DateTime);
dtUnrecExp.Columns.Add("InternalProducerSynonymId", typeof(long));
_logger.Debug("загрузили UnrecExp");
daZero = new MySqlDataAdapter(
String.Format("SELECT * FROM farm.Zero WHERE PriceItemId={0} LIMIT 0", priceItemId), _connection);
var cbZero = new MySqlCommandBuilder(daZero);
daZero.InsertCommand = cbZero.GetInsertCommand();
daZero.InsertCommand.CommandTimeout = 0;
daZero.Fill(dsMyDB, "Zero");
dtZero = dsMyDB.Tables["Zero"];
_logger.Debug("загрузили Zero");
daForb = new MySqlDataAdapter(
String.Format("SELECT * FROM farm.Forb WHERE PriceItemId={0} LIMIT 0", priceItemId), _connection);
var cbForb = new MySqlCommandBuilder(daForb);
daForb.InsertCommand = cbForb.GetInsertCommand();
daForb.InsertCommand.CommandTimeout = 0;
daForb.Fill(dsMyDB, "Forb");
dtForb = dsMyDB.Tables["Forb"];
dtForb.Constraints.Add("ForbName", new[] { dtForb.Columns["Forb"] }, false);
_logger.Debug("загрузили Forb");
if (_priceInfo.IsUpdating)
{
var loadExistsWatch = Stopwatch.StartNew();
LoadCore();
_logger.Debug("Загрузили предложения");
if (_existsCores.Count > 0)
{
LoadCosts();
_logger.Debug("Загрузили цены");
}
if (_saveInCore)
{
_searcher = new Searcher(_existsCores, new[] { typeof(Offer).GetField("CodeOKP") });
}
else
{
_searcher = new Searcher(_existsCores);
}
loadExistsWatch.Stop();
_logger.InfoFormat("Загрузка и подготовка существующего прайса, {0}с", loadExistsWatch.Elapsed.TotalSeconds);
}
_logger.Debug("конец Prepare");
}
public void UsingFunctions()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, name) VALUES (1,'test1')");
execSQL("INSERT INTO Test (id, name) VALUES (2,'test2')");
execSQL("INSERT INTO Test (id, name) VALUES (3,'test3')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name, now() as ServerTime FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
da.Update(dt);
da.SelectCommand.CommandText = "SELECT id, name, CONCAT(name, ' boo') as newname from Test where id=4";
dt.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test1", dt.Rows[0]["name"]);
Assert.AreEqual("test1 boo", dt.Rows[0]["newname"]);
dt.Rows[0]["id"] = 5;
da.Update(dt);
dt.Clear();
da.SelectCommand.CommandText = "SELECT * FROM Test WHERE id=5";
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test1", dt.Rows[0]["name"]);
da.SelectCommand.CommandText = "SELECT *, now() as stime FROM Test WHERE id<4";
cb = new MySqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.InsertCommand = cb.GetInsertCommand();
}
/// <summary>
/// Метод подгружает базу данных в соответствии с выбранным элементом из соответствующего ComboBox
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void BtnLoad_Click(object sender, RoutedEventArgs e)
{
try
{
int a = CmbChooseTable.SelectedIndex;
switch (a) // в зависимости от выбора таблицы, будет сформирован запрос
{
case 0:
NewQuery = "SELECT * FROM Students";
break;
case 1:
NewQuery = "SELECT * FROM Faculties";
break;
case 2:
NewQuery = "SELECT * FROM Studying_Groups";
break;
case 3:
NewQuery = "SELECT * FROM Partners_and_Organizations";
break;
default:
System.Windows.MessageBox.Show("Не выбрана существующая позиция");
break;
}
For_ActionBtns.Visibility = Visibility.Visible;
WorkBenchStack.Visibility = Visibility.Visible;
LowerActionPanel.Visibility = Visibility.Visible;
///
/// Вывод таблицы из БД в DataGrid (В теории - нужно добыть IP)
///
if (NewQuery != "")
{
using (BaseConn.BuildConnection = new MySqlConnection(OpenConnection))
{
BaseConn.BuildConnection.Open();
MySqlCommand FillDGrid = new MySqlCommand(NewQuery, BaseConn.BuildConnection);
MySqlDataReader DReader = FillDGrid.ExecuteReader(); // инициализирует чтение из БД
// если что, выше объявлены поля для adapter, datatable, newquery, openconnection;
adapter = new MySqlDataAdapter(NewQuery, BaseConn.BuildConnection); // адаптер для работы с записью (необязательно для записи здесь, можно снести в метод на клик
// но здесь он нужен для уточнения команд на апдейт удаление и добавление
DTable = new DataTable(); // для сохранения данных из БД во "временное хранилище"
DTable.Load(DReader); // подгружает в "хранилище" данные полученные из БД
MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter);
//ниже прописываются основные виды команд
adapter.InsertCommand = cb.GetInsertCommand();
adapter.UpdateCommand = cb.GetUpdateCommand();
adapter.DeleteCommand = cb.GetDeleteCommand();
Workingbench.AutoGenerateColumns = true; // подрубаем автогенерацию колонок, чтобы выводило те наименования, что в БД находятся
Workingbench.ItemsSource = DTable.DefaultView; // привязка данных из datatable к datagrid
System.Windows.MessageBox.Show("Таблица выведена!");
BaseConn.BuildConnection.Close();
}
}
else
{
System.Windows.MessageBox.Show("Ошибка формирования запроса: запрос на выборку пуст");
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show("" + ex);
System.Windows.MessageBox.Show("Ошибка инициализации подключения, повторите снова или обратитесь к системному администратору");
}
}
/// <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);
}
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();
}
public static string TangMa12Kytu(string gioitinh, string namsinh)
{
string str_matinh = "074";
string str_magioitinh = null;
string str_manamsinh = null;
string sausocuoi = null;
string kq = null;
MySqlDataAdapter sqlda;
DataSet dataset = null;
MySqlCommandBuilder cmdbuilder;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
sqlda = new MySqlDataAdapter("select madinhdanh from nhankhau where gioitinh='" + gioitinh + "' and year(ngaysinh)='" + namsinh + "'ORDER BY madinhdanh desc", conn);
cmdbuilder = new MySqlCommandBuilder(sqlda);
sqlda.InsertCommand = cmdbuilder.GetInsertCommand();
sqlda.UpdateCommand = cmdbuilder.GetUpdateCommand();
sqlda.DeleteCommand = cmdbuilder.GetDeleteCommand();
dataset = new DataSet();
sqlda.Fill(dataset, "bangmadinhdanh");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
}
int i_namsinh = Int16.Parse(namsinh);
if (i_namsinh > 1900 & i_namsinh <= 1999)
{
if (String.Compare(gioitinh, "nam", true) == 0)
{
str_magioitinh = "0";
}
if (String.Compare(gioitinh, "nu", true) == 0)
{
str_magioitinh = "1";
}
}
if (i_namsinh >= 2000 & i_namsinh <= 2099)
{
if (String.Compare(gioitinh, "nam", true) == 0)
{
str_magioitinh = "2";
}
if (String.Compare(gioitinh, "nu", true) == 0)
{
str_magioitinh = "3";
}
}
if (i_namsinh >= 2100 & i_namsinh <= 2199)
{
if (String.Compare(gioitinh, "nam", true) == 0)
{
str_magioitinh = "4";
}
if (String.Compare(gioitinh, "nu", true) == 0)
{
str_magioitinh = "5";
}
}
if (i_namsinh >= 2200 & i_namsinh <= 2299)
{
if (String.Compare(gioitinh, "nam", true) == 0)
{
str_magioitinh = "6";
}
if (String.Compare(gioitinh, "nu", true) == 0)
{
str_magioitinh = "7";
}
}
if (i_namsinh >= 2300 & i_namsinh <= 2399)
{
if (String.Compare(gioitinh, "nam", true) == 0)
{
str_magioitinh = "8";
}
if (String.Compare(gioitinh, "nu", true) == 0)
{
str_magioitinh = "9";
}
}
str_manamsinh = namsinh.Substring(2);
string str_madinhdanh;
try
{
str_madinhdanh = dataset.Tables["bangmadinhdanh"].Rows[0][0].ToString();
}
catch (Exception e)
{
sausocuoi = "000001";
kq = str_matinh + str_magioitinh + str_manamsinh + sausocuoi;
return(kq);
}
sausocuoi = str_madinhdanh.Substring(6);
int x = Int32.Parse(sausocuoi) + 1;
sausocuoi = x.ToString();
string str = null;
for (int i = 0; i < 6 - sausocuoi.Length; i++)
{
str = str + "0";
}
kq = str_matinh + str_magioitinh + str_manamsinh + str + sausocuoi;
return(kq);
}
/// <summary>
/// Writes all Changes in a Dataset to the Table
/// </summary>
/// <param name="tableName">Name of the Table to update</param>
/// <param name="dataSet">DataSet set contains the Changes that sould be written</param>
/// <exception cref="DatabaseException"></exception>
public void SaveDataSet(string tableName, DataSet dataSet)
{
if (dataSet.HasChanges() == false)
{
return;
}
switch (connType)
{
case ConnectionType.DATABASE_XML:
{
try
{
dataSet.WriteXml(connString + tableName + ".xml");
dataSet.AcceptChanges();
dataSet.WriteXmlSchema(connString + tableName + ".xsd");
}
catch (Exception e)
{
throw new DatabaseException("Could not save Databases in XML-Files!", e);
}
break;
}
case ConnectionType.DATABASE_MYSQL:
{
try
{
MySqlConnection conn = new MySqlConnection(connString);
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * from `" + tableName + "`", conn);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
conn.Open();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
catch (Exception ex)
{
throw new DatabaseException("Could not save the Database-Table", ex);
}
break;
}
/* case ConnectionType.DATABASE_MSSQL:
* {
* try
* {
* SqlConnection conn = new SqlConnection(connString);
* SqlDataAdapter adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
* SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
*
* adapter.DeleteCommand = builder.GetDeleteCommand();
* adapter.UpdateCommand = builder.GetUpdateCommand();
* adapter.InsertCommand = builder.GetInsertCommand();
*
* DataSet changes = dataSet.GetChanges();
*
* adapter.Update(changes, tableName);
* dataSet.AcceptChanges();
* }
* catch (Exception ex)
* {
* throw new DatabaseException("Could not save the Database-Table", ex);
* }
*
* break;
* }
* case ConnectionType.DATABASE_ODBC:
* {
* try
* {
* OdbcConnection conn = new OdbcConnection(connString);
* OdbcDataAdapter adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
* OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);
*
* adapter.DeleteCommand = builder.GetDeleteCommand();
* adapter.UpdateCommand = builder.GetUpdateCommand();
* adapter.InsertCommand = builder.GetInsertCommand();
*
* DataSet changes = dataSet.GetChanges();
*
* adapter.Update(changes, tableName);
* dataSet.AcceptChanges();
* }
* catch (Exception ex)
* {
* throw new DatabaseException("Could not save the Database-Table", ex);
* }
*
* break;
* }
* case ConnectionType.DATABASE_OLEDB:
* {
* try
* {
* OleDbConnection conn = new OleDbConnection(connString);
* OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
* OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
*
* adapter.DeleteCommand = builder.GetDeleteCommand();
* adapter.UpdateCommand = builder.GetUpdateCommand();
* adapter.InsertCommand = builder.GetInsertCommand();
*
* DataSet changes = dataSet.GetChanges();
*
* adapter.Update(changes, tableName);
* dataSet.AcceptChanges();
* }
* catch (Exception ex)
* {
* throw new DatabaseException("Could not save the Database-Table", ex);
* }
* break;
* }*/
}
}
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 button6_Click_1(object sender, EventArgs e)
{
if (page == 1)
{
mySqlConnection = new MySqlConnection(
"SERVER=localhost;" +
"DATABASE=phone;" +
"UID=root;" +
"PASSWORD=;");
mySqlConnection.Open();
string k = textBox1.Text;
string query = "SELECT * FROM phonebook Limit 11,11";
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;
dataGridView1.DataSource = bindingSource;
bindingNavigator1.BindingSource = bindingSource;
page++;
}
else if (page == 2)
{
mySqlConnection = new MySqlConnection(
"SERVER=localhost;" +
"DATABASE=phone;" +
"UID=root;" +
"PASSWORD=;");
mySqlConnection.Open();
string k = textBox1.Text;
string query = "SELECT * FROM phonebook Limit 22,11";
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;
dataGridView1.DataSource = bindingSource;
bindingNavigator1.BindingSource = bindingSource;
page++;
}
}