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 AutoIncrementColumnsOnInsert()
{
execSQL("DROP TABLE IF EXISTS Test");
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);
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";
try
{
dt.Rows.Add(row);
da.Update(dt);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
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 MultiWord()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), dt DATETIME, tm TIME, `multi word` int, PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = 1;
row["name"] = "Name";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["multi word"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["multi word"]);
dt.Rows[0]["multi word"] = 3;
da.Update(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[0]["multi word"]);
}
public void MultiWord()
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = 1;
row["name"] = "Name";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["multi word"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["multi word"]);
dt.Rows[0]["multi word"] = 3;
da.Update(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(3, dt.Rows[0]["multi word"]);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
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 LastOneWins()
{
execSQL("INSERT INTO Test (id, name) VALUES (1, 'Test')");
MySqlCommandBuilder cb = new MySqlCommandBuilder(
new MySqlDataAdapter("SELECT * FROM Test", conn));
MySqlDataAdapter da = cb.DataAdapter;
cb.ConflictOption = ConflictOption.OverwriteChanges;
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
execSQL("UPDATE Test SET name='Test2' WHERE id=1");
dt.Rows[0]["name"] = "Test3";
Assert.AreEqual(1, da.Update(dt));
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("Test3", dt.Rows[0]["name"]);
}
public void AutoIncrementColumnsOnInsert2()
{
execSQL("DROP TABLE IF EXISTS Test");
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);
cb.ReturnGeneratedIdentifiers = true;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows.Clear();
try
{
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"]);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Assert.AreEqual(1, dt.Rows[0]["id"]);
}
public void OriginalInName()
{
CreateDefaultTable();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ToString(); // keep the compiler happy
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = DBNull.Value;
row["id2"] = 1;
row["name"] = "Test";
row["dt"] = DBNull.Value;
row["tm"] = DBNull.Value;
row["ts"] = DBNull.Value;
row["OriginalId"] = 2;
dt.Rows.Add(row);
da.Update(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["OriginalId"]);
}
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"]);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string myConnection = "server=127.0.0.1;datasource = localhost;port = 3306; username = merch;password=ialsql1851;database=studenttest;";
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
//myDataAdapter.SelectCommand = new MySqlCommand("describe students;", myConn);
myDataAdapter.SelectCommand = new MySqlCommand("SELECT * FROM students;", myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
//DataSet ds = new DataSet();
DataTable table = new DataTable();
myDataAdapter.Fill(table);
BindingSource bSource = new BindingSource();
bSource.DataSource = table;
dataGridView1.DataSource = bSource;
//dataGridView1.DataMember = table;
myDataAdapter.Update(table);
////////////////////////////////
//using (SqlConnection sqlConn = new SqlConnection("yourConnectionString"))
//{
//using (SqlCommand cmd = new SqlCommand())
//{
//SqlCommand cmd = new SqlCommand
//cmd.CommandText = "SELECT first_name, last_name FROM students";
//cmd.Parameters.Add("@first_name", SqlDbType.Text).Value = "Andy",myConn);
// cmd.Parameters. ("SELECT first_name, last_name Where first_name='Andy';");
//cmd.Connection = myConn;
MySqlCommand mSqlCmd = myConn.CreateCommand();
mSqlCmd.CommandText = "select * from students WHERE first_Name = 'Andy'";
MySqlDataReader mSqlReader;
//myConn.Open();
DataSet ds = new DataSet();
mSqlReader = mSqlCmd.ExecuteReader();
DataTable dtCustomers = new DataTable();
dtCustomers.Load(mSqlReader);
// foreach (DataRow row in dtCustomers.Rows)
// textBox1.Text = dtCustomers.Rows[1]["last_name"]; Console.WriteLine(row["last_name"].ToString())
foreach (DataRow row in dtCustomers.Rows)
{
//Console.WriteLine(row["customerName"].ToString());
textBox1.Text = row["last_name"].ToString();
}
//Console.ReadKey();
//using (SqlDataReader reader = cmd.ExecuteReader())
//{
// if (reader.Read())
//{
// textBox1.Text = (string)reader.GetValue[0];
//textBox2.Text = (string)reader[1];
//}
//}
//}
//}
//myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//MessageBox.Show("Connected");
}
private void orders(DateTime beginDateTime, DateTime endDateTime)
{
/* listViewOrders.Columns.Add("ORDER ID", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Date and Time", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Table number", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Waiter ID", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Paid", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Cash or Card", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Status", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Menu Item ID", -2, HorizontalAlignment.Left);
* listViewOrders.Columns.Add("Quantity orderd", -2, HorizontalAlignment.Left);*/
int cashORcard = 0;
int status = 0;
int paid = 0;
int waiter = 0;
int table = 0;
string sCashOrCard = "";
string sPaid = "";
string sStatus = "";
DateTime date = new DateTime();
int orderID = 0;
string orderQuery = " SELECT * FROM `ORDER` WHERE `Order_Date_Time` >='" + beginDateTime.ToString("yyyy-MM-dd H:mm:ss") + "' AND `Order_Date_Time` <= '" + endDateTime.ToString("yyyy-MM-dd H:mm:ss") + "'";
MySqlCommand cmmd = new MySqlCommand();
cmmd.CommandText = orderQuery;
cmmd.Connection = connection;
try
{
adap = new MySqlDataAdapter(cmmd);
build = new MySqlCommandBuilder(adap);
DataTable ds = new DataTable();
adap.Fill(ds);
order = ds;
}
catch
{
}
MySqlCommand cmd = new MySqlCommand(orderQuery, connection);
// data reader
var itemOrder = new ListViewItem(new[] { "", "", "", "", "", "", "", "", "" });
//listBox1.Items.Add(" ORDER ID Date \t \tTable \t Waiter ID "+" "+ "Cash or Card \t Paid \t Status \t Menu Item ID \t Quantity");
foreach (DataRow r in order.Rows)
{
orderID = int.Parse(r["Order_ID"].ToString());
date = DateTime.Parse(r["Order_Date_Time"].ToString());
table = int.Parse(r["Table_nr"].ToString());
waiter = int.Parse(r["Waiter_ID"].ToString());
status = int.Parse(r["Status"].ToString());
if (status == 0)
{
sStatus = "NotDelivered";
}
else
{
sStatus = "Delivered";
}
cashORcard = int.Parse(r["CashOrCard"].ToString());
if (cashORcard == 0)
{
sCashOrCard = "Cash";
}
else
{
sCashOrCard = "Card";
}
paid = int.Parse(r["Paid"].ToString());
if (paid == 0)
{
sPaid = "false";
}
else
{
sPaid = "true";
}
// get Order detail info on order id
orderQuery = " SELECT * FROM `ORDER-DETAIL` WHERE Order_ID = '" + orderID + "'";
connection.Open();
//put in comand
cmd = new MySqlCommand(orderQuery, connection);
MySqlDataReader dataR1 = cmd.ExecuteReader();
// data reader
while (dataR1.Read())
{
//dataR.GetValue();
//listViewOrders.Items.Add(orderID.ToString(), date.ToString, table.ToString,waiter.ToString,cashORcard.ToString,paid.ToString(),status.ToString, dataR["MenuItemID"] +"",dataR["Quantity_Orderd"] +"");
itemOrder = new ListViewItem(new[] { orderID.ToString(), date.ToString(), table.ToString(), waiter.ToString(), cashORcard.ToString(), paid.ToString(), status.ToString(), dataR1["Menu_Item_ID"] + "", dataR1["Quantity_Ordered"] + "" });
//listViewOrders.Items.Add(itemOrder);
listBoxOrders.Items.Add(" ORDER ID: " + orderID.ToString() + "\t Date:" + date.ToString() + "\t Table:" + table.ToString() + " \tWaiter ID: " + waiter.ToString() + " \t" + sCashOrCard + " \t Paid:" + sPaid + "\t " + sStatus + "\tMenu Item ID: " + dataR1["Menu_Item_ID"] + " \tQuantity: " + dataR1["Quantity_Ordered"] + "");
listBoxOrders.Items.Add("=================================================================================================================================================");
}
//close data reader
dataR1.Close();
//close connection
connection.Close();
}
/*connection.Open();
*
* //put in comand
*
* while (dataR.Read())
* {
* //dataR.GetValue();
*
*
* //listViewOrders.Items.Add(dataR["Order_ID"]+","+ dataR["Order_DateTime"] + "," + dataR["Table_nr"] + "," + dataR["Waiter_ID"] + "," + dataR["Paid"] + "," + dataR["CashOrCard"] + "," + dataR["Status"] );
*
*
* }
*
* // close data reader
* dataR.Close();
* // close connection
* connection.Close();*/
MessageBox.Show("Done");
}
public void DifferentDatabase()
{
if (Version < new Version(4, 1)) return;
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')");
conn.ChangeDatabase(database1);
MySqlDataAdapter da = new MySqlDataAdapter(
String.Format("SELECT id, name FROM `{0}`.Test", database0), conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
ds.Tables[0].Rows[0]["id"] = 4;
DataSet changes = ds.GetChanges();
da.Update(changes);
ds.Merge(changes);
ds.AcceptChanges();
cb.Dispose();
conn.ChangeDatabase(database0);
}
public void SemicolonAtEndOfSQL()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(100), PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES(1, 'Data')");
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `Test`;", conn);
da.FillSchema(ds, SchemaType.Source, "Test");
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 2;
da.Update(dt);
dt.Clear();
da.Fill(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Rows[0]["id"]);
}
private void UpdateMobNameButton_Click(object sender, EventArgs e)
{
if (Util.IsEmpty(MobNamecomboBox.Text))
{
MessageBox.Show(" WARNING: Name to be Changed can't be null.");
return;
}
string originalName = MobNamecomboBox.Text.Replace("'", "''");
if (Util.IsEmpty(ChangedNametextBox.Text))
{
MessageBox.Show(" WARNING: Name to Set can't be null.");
return;
}
string newName = ChangedNametextBox.Text.Replace("'", "''");
int MobrowsAffected = 0;
int MobxAmbientBehaviourrowsAffected = 0;
int DataquestrowsAffected = 0;
int DropTemplateXItemTemplaterowsAffected = 0;
int MobDropTemplaterowsAffected = 0;
int NPCTemplaterowsAffected = 0;
int InventoryCreatorrowsAffected = 0;
int LootTemplaterowsAffected = 0;
int MobXLootTemplaterowsAffected = 0;
int LootOTDrowsAffected = 0;
int LootGeneratorAffected = 0;
try
{
Util.Connection.Open();
MySqlDataAdapter MobNameVerifdataAdapter = new MySqlDataAdapter();
string selectCommand = "select * from mob where name = '" + newName + "'";
// Create a new data adapter based on the specified query.
MobNameVerifdataAdapter = new MySqlDataAdapter(selectCommand, Util.Connection);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
MySqlCommandBuilder commandBuilderMobNameVerif = new MySqlCommandBuilder(MobNameVerifdataAdapter);
DataTable MobNameVerifData = new DataTable();
MobNameVerifData.Clear();
// Populate a new data table.
MobNameVerifData.Locale = System.Globalization.CultureInfo.InvariantCulture;
MobNameVerifdataAdapter.Fill(MobNameVerifData);
if (MobNameVerifData.Rows.Count > 0)
{
if (MessageBox.Show(MobNameVerifData.Rows.Count + " Mobs already have this name. Ignore and rename it?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No)
{
Util.Connection.Close();
return;
}
}
MySqlCommand MobCommand = Util.Connection.CreateCommand();
MobCommand.CommandText = "update Mob set name='" + newName + "' where name ='" + originalName + "'";
MobrowsAffected = MobCommand.ExecuteNonQuery();
if (UpdateMobxAmbientBehaviourcheckBox.Checked)
{
MySqlCommand MobxAmbientBehaviourCommand = Util.Connection.CreateCommand();
MobxAmbientBehaviourCommand.CommandText = "update MobxAmbientBehaviour set source='" + newName + "' where source ='" + originalName + "'";
MobxAmbientBehaviourrowsAffected = MobxAmbientBehaviourCommand.ExecuteNonQuery();
}
if (UpdateDataquestcheckBox.Checked)
{
MySqlCommand DataquestCommand = Util.Connection.CreateCommand();
DataquestCommand.CommandText = "update dataquest set startname='" + newName + "' where startname ='" + originalName + "'";
DataquestrowsAffected = DataquestCommand.ExecuteNonQuery();
//Todo replace sourcename too
}
if (UpdateDropTemplateXItemTemplatecheckBox.Checked)
{
MySqlCommand DropTemplateXItemTemplateCommand = Util.Connection.CreateCommand();
DropTemplateXItemTemplateCommand.CommandText = "update DropTemplateXItemTemplate set templatename='" + newName + "' where templatename ='" + originalName + "'";
DropTemplateXItemTemplaterowsAffected = DropTemplateXItemTemplateCommand.ExecuteNonQuery();
MySqlCommand MobDropTemplateCommand = Util.Connection.CreateCommand();
MobDropTemplateCommand.CommandText = "update MobDropTemplate set mobname='" + newName + "' where mobname ='" + originalName + "'";
MobDropTemplaterowsAffected = MobDropTemplateCommand.ExecuteNonQuery();
MySqlCommand MobDropTemplateCommand2 = Util.Connection.CreateCommand();
MobDropTemplateCommand2.CommandText = "update MobDropTemplate set loottemplatename='" + newName + "' where loottemplatename ='" + originalName + "'";
MobDropTemplateCommand2.ExecuteNonQuery();
}
if (UpdateNPCTemplatecheckBox.Checked)
{
MySqlCommand NPCTemplateCommand = Util.Connection.CreateCommand();
NPCTemplateCommand.CommandText = "update npctemplate set name='" + newName + "' where name ='" + originalName + "'";
NPCTemplaterowsAffected = NPCTemplateCommand.ExecuteNonQuery();
//Update NPCTemplate Name combobox value
foreach (DataRow myRow in NPCTemplateDatatable.Rows)
{
if (myRow["name"].ToString() == MobNamecomboBox.Text.ToString())
{
myRow["name"] = ChangedNametextBox.Text;
}
}
}
if (UpdateInventoryCreatorcheckBox.Checked)
{
MySqlCommand InventoryCreatorCommand = Util.Connection.CreateCommand();
InventoryCreatorCommand.CommandText = "update inventory set creator='" + newName + "' where creator ='" + originalName + "'";
InventoryCreatorrowsAffected = InventoryCreatorCommand.ExecuteNonQuery();
}
if (UpdateMobXLootTemplatecheckBox.Checked)
{
MySqlCommand DropTemplateXItemTemplateCommand = Util.Connection.CreateCommand();
DropTemplateXItemTemplateCommand.CommandText = "update loottemplate set templatename='" + newName + "' where templatename ='" + originalName + "'";
LootTemplaterowsAffected = DropTemplateXItemTemplateCommand.ExecuteNonQuery();
MySqlCommand MobXLootTemplateCommand = Util.Connection.CreateCommand();
MobXLootTemplateCommand.CommandText = "update MobXLootTemplate set mobname='" + newName + "' where mobname ='" + originalName + "'";
MobXLootTemplaterowsAffected = MobXLootTemplateCommand.ExecuteNonQuery();
MySqlCommand MobXLootTemplateCommand2 = Util.Connection.CreateCommand();
MobXLootTemplateCommand2.CommandText = "update MobXLootTemplate set loottemplatename='" + newName + "' where loottemplatename ='" + originalName + "'";
MobXLootTemplateCommand2.ExecuteNonQuery();
}
if (UpdateLootOTDcheckBox.Checked)
{
MySqlCommand LootOTDCommand = Util.Connection.CreateCommand();
LootOTDCommand.CommandText = "update loototd set mobname='" + newName + "' where mobname ='" + originalName + "'";
LootOTDrowsAffected = LootOTDCommand.ExecuteNonQuery();
}
if (UpdateLootGeneratorcheckBox.Checked)
{
MySqlCommand LootGeneratorCommand = Util.Connection.CreateCommand();
LootGeneratorCommand.CommandText = "update lootgenerator set mobname='" + newName + "' where mobname ='" + originalName + "'";
LootGeneratorAffected = LootGeneratorCommand.ExecuteNonQuery();
}
Util.Connection.Close();
}
catch (MySqlException s)
{
MessageBox.Show(s.Message);
}
MessageBox.Show(string.Format(" Table Mob rows affected: {0}\n Table MobxAmbientBehaviour rows affected: {1}\n Table Dataquest rows affected: {2}\n Table DropTemplateXItemTemplate rows affected: {3}\n Table MobDropTemplate rows affected: {4}\n Table NPCTemplate rows affected: {5}\n Table Inventory rows affected: {6}\n Table LootTemplate rows affected: {7}\n Table MobXLootTemplate rows affected: {8}\n Table LootOTD rows affected: {9}\n Table LootGenerator rows affected: {10}\n ",
MobrowsAffected, MobxAmbientBehaviourrowsAffected, DataquestrowsAffected, DropTemplateXItemTemplaterowsAffected, MobDropTemplaterowsAffected, NPCTemplaterowsAffected, InventoryCreatorrowsAffected, LootTemplaterowsAffected, MobXLootTemplaterowsAffected, LootOTDrowsAffected, LootGeneratorAffected));
}
private void UpdateDGV(DataGridView DGV, string str)
{
DataTable DT = new DataTable();
DGV.Rows.Clear();
connection.Open();
MSDA = new MySqlDataAdapter(str, connection);
MySqlCommandBuilder MSCB = new MySqlCommandBuilder(MSDA);
try
{
MSDA.Fill(DT);
}
catch (Exception)
{
MessageBox.Show(MESSAGES[0]);
}
foreach (DataRow row in DT.Rows)
{
try
{
switch (DGV.ColumnCount)
{
case 2: DGV.Rows.Add(row[0].ToString(), row[1].ToString()); break;
case 3: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString()); break;
case 4: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString()); break;
case 5: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString()); break;
case 6: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString()); break;
case 7: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString()); break;
case 8: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString()); break;
case 9: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString()); break;
case 10: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString()); break;
case 11: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString()); break;
case 12: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString(), row[11].ToString()); break;
case 13: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString(), row[11].ToString(), row[12].ToString()); break;
case 14: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString(), row[11].ToString(), row[12].ToString(), row[13].ToString()); break;
case 15: DGV.Rows.Add(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[6].ToString(), row[7].ToString(), row[8].ToString(), row[9].ToString(), row[10].ToString(), row[11].ToString(), row[12].ToString(), row[13].ToString(), row[14].ToString()); break;
}
}
catch (Exception)
{
MessageBox.Show(MESSAGES[2]);
}
}
connection.Close();
DT.Rows.Clear();
DT.Dispose();
}
private void itemOrderReportViewer_Load(object sender, EventArgs e)
{
//set start date parameter
ParameterFields From = new ParameterFields();
ParameterField PID = new ParameterField();
PID.Name = "FromDate";
ParameterDiscreteValue val = new ParameterDiscreteValue();
val.Value = Reports.startDate;
PID.CurrentValues.Add(val);
From.Add(PID);
itemOrderrptViewer.ParameterFieldInfo = From;
//set end date parameter
ParameterField PID1 = new ParameterField();
PID1.Name = "ToDate";
ParameterDiscreteValue val1 = new ParameterDiscreteValue();
val1.Value = Reports.endDate;
PID1.CurrentValues.Add(val1);
From.Add(PID1);
itemOrderrptViewer.ParameterFieldInfo = From;
//create data table
DataTable itemtblTemp = new DataTable();
DataTable idtbl = new DataTable();
itemtbl.Columns.Add("io_id");
itemtbl.Columns.Add("client_id");
itemtbl.Columns.Add("creation_time");
itemtbl.Columns.Add("item_id");
itemtbl.Columns.Add("qty");
itemtbl.Columns.Add("name");
itemtbl.Columns.Add("released");
itemtbl.Columns.Add("latest_delivery_time");
//create item id table
try
{
String query;
if (Reports.sortbysupclient == false)
{
query = "SELECT io_id , client_id, creation_time, released, latest_delivery_time FROM itemorder where approval='Approved' AND creation_time between '" + Reports.startDate + "' and '" + Reports.endDate + "' ";
}
else
{
query = "SELECT io_id , client_id, creation_time, released , latest_delivery_time FROM itemorder where approval='Approved' AND creation_time between '" + Reports.startDate + "' and '" + Reports.endDate + "' AND client_id='" + Reports.supclientval + "' ";
}
var dataAdapter = new MySqlDataAdapter(query, DatabaseHandler.MySQLConnectionString);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
dataAdapter.Fill(idtbl);
Console.WriteLine("Rows are " + idtbl.Rows.Count);
if (idtbl.Rows.Count == 0)
{
MessageBox.Show("NO Entries available");
this.Close();
return;
}
}
catch (Exception er)
{
MessageBox.Show("Error Occured! ");
Console.WriteLine(er.Message);
return;
}
int noOfRows = idtbl.Rows.Count;
for (int i = 0; i < noOfRows; i++)
{
try
{
String query = "SELECT itemorder_item.io_id, itemorder_item.item_id ,itemorder_item.qty , item.name FROM itemorder_item INNER JOIN item ON itemorder_item.item_id = item.item_id where itemorder_item.io_id= '" + idtbl.Rows[i][0].ToString() + "' ";
var dataAdapter = new MySqlDataAdapter(query, DatabaseHandler.MySQLConnectionString);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
dataAdapter.Fill(itemtblTemp);
Console.WriteLine(itemtblTemp.Rows.Count);
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
if (itemtblTemp.Rows.Count == 0)
{
MessageBox.Show("Error Occured! Please check input details!");
return;
}
int noOfRows2 = itemtblTemp.Rows.Count;
for (int i = 0; i < noOfRows2; i++)
{
DataRow rw = itemtbl.NewRow();
rw["io_id"] = itemtblTemp.Rows[i][0];
rw["item_id"] = itemtblTemp.Rows[i][1];
rw["qty"] = itemtblTemp.Rows[i][2];
rw["name"] = itemtblTemp.Rows[i][3];
for (int j = 0; j < noOfRows; j++)
{
if (itemtblTemp.Rows[i][0].ToString() == idtbl.Rows[j][0].ToString())
{
rw["client_id"] = idtbl.Rows[j][1];
rw["creation_time"] = idtbl.Rows[j][2];
rw["released"] = idtbl.Rows[j][3];
rw["latest_delivery_time"] = idtbl.Rows[j][4];
}
}
itemtbl.Rows.Add(rw);
}
Console.WriteLine("item tbl rows" + itemtbl.Rows.Count);
int noOfRows3 = itemtbl.Rows.Count;
itemsumtbl.Columns.Add("item_id");
itemsumtbl.Columns.Add("name");
itemsumtbl.Columns.Add("qty");
int noOfRows4 = 0;
for (int i = 0; i < noOfRows3; i++)
{
noOfRows4 = itemsumtbl.Rows.Count;
int count = 0;
for (int j = 0; j < noOfRows4; j++)
{
if (itemtbl.Rows[i][3].ToString() == itemsumtbl.Rows[j][0].ToString())
{
itemsumtbl.Rows[j][2] = Convert.ToInt32(itemtbl.Rows[i][4].ToString()) + Convert.ToInt32(itemsumtbl.Rows[j][2].ToString());
count++;
}
}
if (count == 0)
{
DataRow rw = itemsumtbl.NewRow();
rw["item_id"] = itemtbl.Rows[i][3];
rw["qty"] = itemtbl.Rows[i][4];
rw["name"] = itemtbl.Rows[i][5];
itemsumtbl.Rows.Add(rw);
}
}
if (Reports.summarize == true)
{
CrystalReports.itemOrderSummarizedReport itemsumrpt = new CrystalReports.itemOrderSummarizedReport();
itemsumrpt.Database.Tables["itemsumtbl"].SetDataSource(itemsumtbl);
itemOrderrptViewer.ReportSource = null;
itemOrderrptViewer.ReportSource = itemsumrpt;
}
else
{
CrystalReports.itemOrderReport itemrpt = new CrystalReports.itemOrderReport();
itemrpt.Database.Tables["itemtbl"].SetDataSource(itemtbl);
itemOrderrptViewer.ReportSource = null;
itemOrderrptViewer.ReportSource = itemrpt;
}
}
public void ParsingTender()
{
var s = DownloadString.DownL(_tn.Href);
if (string.IsNullOrEmpty(s))
{
Log.Logger("Empty string in ParsingTender()", _tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var dateUpd = DateTime.Now;
_tn.PurNum = (navigator
.SelectSingleNode(
"//td[contains(., 'Номер закупки') or contains(., 'Номер запроса') or contains(., 'Номер процедуры')]/following-sibling::td/a")
?.Value ?? "").Trim();
if (string.IsNullOrEmpty(_tn.PurNum))
{
_tn.PurNum =
(navigator.SelectSingleNode(
"//td[contains(., 'Номер закупки') or contains(., 'Номер запроса') or contains(., 'Номер процедуры') or contains(., 'Номер закупки')]/following-sibling::td")
?.Value ?? "").Replace("ЕИС", "").Trim();
}
if (string.IsNullOrEmpty(_tn.PurNum))
{
Log.Logger("cannot find purNum in ", _tn.Href);
return;
}
var eis = (navigator
.SelectSingleNode(
"//td[contains(., 'Номер закупки') or contains(., 'Номер запроса') or contains(., 'Номер процедуры')]/following-sibling::td/span/a/acronym")
?.Value ?? "").Trim();
if (eis.Contains("ЕИС"))
{
//Log.Logger("EIS tender", _tn.PurNum);
return;
}
var noticeVersion = (navigator
.SelectSingleNode(
"//td[contains(., 'Состояние лота')]/following-sibling::td")
?.Value ?? "").Trim();
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", noticeVersion);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
//Log.Logger("This tender is exist in base", PurNum);
return;
}
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
//DateTime dateNew = DateTime.Parse(pr.DatePublished);
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
if (!string.IsNullOrEmpty(_tn.OrgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", _tn.OrgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "";
var email = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", _tn.OrgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
GetPlacingWay(connect, out var idPlacingWay);
var biddingDateT = (navigator
.SelectSingleNode(
"//td[span[contains(., 'Начало') and contains(., 'проведения')]]/following-sibling::td/text()[1]")
?.Value ?? "").Replace(" ", " ").Trim();
biddingDateT = Regex.Replace(biddingDateT, @"\s+", " ");
biddingDateT = biddingDateT.GetDateWithMonth();
var biddingDate = biddingDateT.ParseDateUn("dd MM yyyy HH:mm");
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", biddingDate);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", noticeVersion);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
var cusName = (navigator
.SelectSingleNode(
"//td[contains(., 'Наименование') and contains(., 'заказчика')]/following-sibling::td")
?.Value ?? "").Replace(" ", " ").Trim();
if (!string.IsNullOrEmpty(cusName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", cusName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", cusName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var docs = htmlDoc.DocumentNode.SelectNodes(
"//h3/following-sibling::div//table/tbody/tr") ??
new HtmlNodeCollection(null);
foreach (var doc in docs)
{
var nameDd = (doc.SelectSingleNode("./td[1]/a")?.InnerText ?? "").Trim();
if (!nameDd.Contains("Извещение"))
{
continue;
}
var exp = (doc.SelectSingleNode("./td[2]")?.InnerText ?? "").Trim();
if (!exp.Contains("(действующая)"))
{
continue;
}
var documents = doc.SelectNodes("./td[4]/div/a") ??
new HtmlNodeCollection(null);
foreach (var dd in documents)
{
var urlAttT = (dd?.Attributes["href"]?.Value ?? "").Trim();
var fName = (dd?.InnerText ?? "").Trim();
var urlAtt = $"https://etp.setonline.ru{urlAttT}";
if (!string.IsNullOrEmpty(fName))
{
var insertAttach =
$"INSERT INTO {AppBuilder.Prefix}attachment SET id_tender = @id_tender, file_name = @file_name, url = @url";
var cmd10 = new MySqlCommand(insertAttach, connect);
cmd10.Prepare();
cmd10.Parameters.AddWithValue("@id_tender", idTender);
cmd10.Parameters.AddWithValue("@file_name", fName);
cmd10.Parameters.AddWithValue("@url", urlAtt);
cmd10.ExecuteNonQuery();
}
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", _tn.Nmck);
cmd18.Parameters.AddWithValue("@currency", _tn.Currency);
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name, sum = @sum";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.Parameters.AddWithValue("@sum", _tn.Nmck);
cmd19.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
/// <summary>
/// 批量更新设备在线状态 需修改语法
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool UpdateSrvEquipmentStatusBatch(DataTable dt)
{
if (dt == null || dt.Rows.Count == 0)
{
return(false);
}
MySqlDataAdapter sd = null;
DataTable dataTable = new DataTable();
int reslut = -1;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
// sd = new MySqlDataAdapter("SELECT SRV_ID,SRV_RMT_TIME,SRV_RMT_STATUS,SRV_PHYSICAL_CODE FROM Srv WITH(NOLOCK)", conn);
sd = new MySqlDataAdapter("SELECT SRV_ID,SRV_RMT_TIME,SRV_RMT_STATUS,SRV_PHYSICAL_CODE FROM Srv", conn);
MySqlCommandBuilder scb = new MySqlCommandBuilder(sd);
sd.UpdateCommand = scb.GetUpdateCommand();
sd.UpdateCommand = new MySqlCommand("update Srv set SRV_RMT_TIME=@SRV_RMT_TIME, SRV_RMT_STATUS=@SRV_RMT_STATUS where SRV_PHYSICAL_CODE=@SRV_PHYSICAL_CODE", conn);
sd.UpdateCommand.Parameters.Add("@SRV_RMT_TIME", MySqlDbType.DateTime, 6, "SRV_RMT_TIME");
sd.UpdateCommand.Parameters.Add("@SRV_RMT_STATUS", MySqlDbType.VarChar, 255, "SRV_RMT_STATUS");
sd.UpdateCommand.Parameters.Add("@SRV_PHYSICAL_CODE", MySqlDbType.VarChar, 255, "SRV_PHYSICAL_CODE");
sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0;
sd.Fill(dataTable);
if (dataTable.Rows.Count > 0)//添加于20180116 SRV表中的数据来源？
{
for (int i = 0; i < dt.Rows.Count; i++)
{
dataTable.Rows[i].BeginEdit();
var rows = dataTable.Select("SRV_PHYSICAL_CODE='" + dt.Rows[i]["srv_physical_code"] + "'");
if (rows != null && rows.Length > 0)
{
rows[0]["SRV_RMT_TIME"] = DateTime.Parse(dt.Rows[i]["srv_time"].ToString());
rows[0]["SRV_RMT_STATUS"] = "在线";
}
dataTable.Rows[i].EndEdit();
}
reslut = sd.Update(dataTable);
}
}
catch (Exception ex)
{
log.Error("批量更新设备在线状态发生异常", ex);
}
finally
{
dataTable.Clear();
sd.Dispose();
dataTable.Dispose();
conn.Close();
}
return(reslut != -1);
}
//상품 주문 버튼
private void btnPdOrder_Click(object sender, EventArgs e)
{
string constring = "datasource=localhost; database=smartcart; username=root; password=apmsetup";
string QueryPdOrder1 = "SELECT * FROM product WHERE name = '" + cbBoxPdName.SelectedItem + "'";
string sQuantity = null;
string PdQuantity = null;
MySqlConnection PdconDB1 = new MySqlConnection(constring);
MySqlCommand PdcmdDB1 = new MySqlCommand(QueryPdOrder1, PdconDB1);
MySqlDataReader myreader1;
try
{
PdconDB1.Open();
myreader1 = PdcmdDB1.ExecuteReader();
while (myreader1.Read())
{
sQuantity = myreader1.GetString("quantity");
PdQuantity = textPdQuantity.Text;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + 11);
}
string QueryPdOrder2 = "UPDATE product SET quantity = " + (Convert.ToInt32(sQuantity) + Convert.ToInt32(PdQuantity)) + " WHERE name = '" + cbBoxPdName.SelectedItem + "'";
MySqlConnection PdconDB2 = new MySqlConnection(constring);
MySqlCommand PdcmdDB2 = new MySqlCommand(QueryPdOrder2, PdconDB2);
MySqlDataReader myreader2;
try
{
PdconDB2.Open();
myreader2 = PdcmdDB2.ExecuteReader();
MySqlDataAdapter adapter = new MySqlDataAdapter(QueryPdOrder2, PdconDB2);
MySqlCommandBuilder builder = new MySqlCommandBuilder(adapter);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + 33);
}
// 주문 시 입고내역 데이터베이스에 추가
string QueryPdOrder3 = "INSERT INTO inbound (product, quantity, date) VALUES ('" + cbBoxPdName.SelectedItem + "', '" + PdQuantity + "', '" + "2020-03-17')";
MySqlConnection PdconDB3 = new MySqlConnection(constring);
MySqlCommand PdcmdDB3 = new MySqlCommand(QueryPdOrder3, PdconDB3);
MySqlDataReader myreader3;
try
{
PdconDB3.Open();
myreader3 = PdcmdDB3.ExecuteReader();
MySqlDataAdapter adapter1 = new MySqlDataAdapter(QueryPdOrder3, PdconDB3);
MySqlCommandBuilder builder1 = new MySqlCommandBuilder(adapter1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
cbBoxPdName.SelectedIndex = -1;
textPdQuantity.Text = "";
MessageBox.Show("주문되었습니다.", "관리자");
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
var dateUpd = DateTime.Now;
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND end_date = @end_date";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var s = DownloadString.DownLHttpPostWithCookiesAll(_tn.Href, ParserSpgr.HttpsT2Federal1Ru,
ParserSpgr.Cookies);
if (string.IsNullOrEmpty(s))
{
Log.Logger(
$"Empty string in {GetType().Name}.{System.Reflection.MethodBase.GetCurrentMethod().Name}",
_tn.Href);
return;
}
var htmlDoc = new HtmlDocument();
htmlDoc.LoadHtml(s);
var navigator = (HtmlNodeNavigator)htmlDoc.CreateNavigator();
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
//row.AcceptChanges();
//row.SetModified();
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var customerId = 0;
var organiserId = 0;
var orgName =
navigator.SelectSingleNode("//td[contains(., 'Предприятие-инициатор:')]/following-sibling::td")
?.Value?.Trim() ?? "";
if (!string.IsNullOrEmpty(orgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = navigator.SelectSingleNode("//td[contains(., 'Телефон:')]/following-sibling::td")
?.Value?.Trim() ?? "";
var email = "";
var contactPerson =
navigator.SelectSingleNode(
"//td[contains(., 'Ответственный за размещение лота:')]/following-sibling::td")
?.Value
?.Trim() ?? "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
PlacingWay = navigator.SelectSingleNode("//b[contains(., 'Тип лота: ')]/span")?.Value?.Trim() ?? "";
GetPlacingWay(connect, out var idPlacingWay);
var printForm = _tn.Href;
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", "");
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
AddAttachments(htmlDoc, connect, idTender);
AddLots(htmlDoc, connect, idTender, customerId);
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
/// <summary>
/// Retrieves parameter information from the stored procedure specified in the <see cref="DbCommand"/> and populates the Parameters collection of the specified <see cref="DbCommand"/> object.
/// </summary>
/// <param name="discoveryCommand">The <see cref="DbCommand"/> to do the discovery.</param>
/// <remarks>The <see cref="DbCommand"/> must be a <see cref="SqlCommand"/> instance.</remarks>
protected override void DeriveParameters(System.Data.Common.DbCommand discoveryCommand)
{
MySqlCommandBuilder.DeriveParameters((MySqlCommand)discoveryCommand);
}
//pizza and hot drinks
private void button8_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
if (tablecheck == 2)
{
//pizza
try
{
//this code recieves a list of items, descriptions and prices from the server.
//each line of the server table is put into listBox1 in the following order:
// meal name
// description
// price
string myConnection = conection;
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Pizza' ;", myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
MySqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader.GetString(1));
listBox1.Items.Add(reader.GetString(2));
listBox1.Items.Add(reader.GetString(6));
}
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (tablecheck == 4)
{
//hot drinks
try
{
string myConnection = conection;
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
//myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn);
MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Hot Drinks' ;", myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
MySqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader.GetString(1));
listBox1.Items.Add(reader.GetString(2));
listBox1.Items.Add(reader.GetString(6));
}
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public void UpdateDecimalColumns()
{
execSQL("CREATE TABLE Test (id int not null auto_increment primary key, " +
"dec1 decimal(10,1))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = DBNull.Value;
row["dec1"] = 23.4;
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(23.4, dt.Rows[0]["dec1"]);
cb.Dispose();
}
//starters, sides nad sundaes
private void button11_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
if (tablecheck == 1)
{
//starters
try
{
string myConnection = conection;
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
//myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn);
MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Starters' ;", myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
MySqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader.GetString(1));
listBox1.Items.Add(reader.GetString(2));
listBox1.Items.Add(reader.GetString(6));
}
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (tablecheck == 2)
{
//sides
try
{
string myConnection = conection;
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
//myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn);
MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Sides' ;", myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
MySqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader.GetString(1));
listBox1.Items.Add(reader.GetString(2));
listBox1.Items.Add(reader.GetString(6));
}
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
else if (tablecheck == 3)
{
//sundaes
try
{
string myConnection = conection;
MySqlConnection myConn = new MySqlConnection(myConnection);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
//myDataAdapter.SelectCommand = new MySqlCommand("select Table_ID from demo.table where Table_Status = 'Available' and Seat_Numbers = '" + searchValue.ToString() + "'", myConn);
MySqlCommand comand = new MySqlCommand("select * from demo.menu_item where Item_Type = 'Gelato Sundaes' ;", myConn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);
myConn.Open();
MySqlDataReader reader = comand.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader.GetString(1));
listBox1.Items.Add(reader.GetString(2));
listBox1.Items.Add(reader.GetString(6));
}
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public void InsertDateTimeValue()
{
using (MySqlConnection c = new MySqlConnection(conn.ConnectionString +
";allow zero datetime=yes"))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, dt FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("id", typeof(int)));
dt.Columns.Add(new DataColumn("dt", typeof(DateTime)));
da.Fill(dt);
DateTime now = DateTime.Now;
DataRow row = dt.NewRow();
row["id"] = 1;
row["dt"] = now;
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
cb.Dispose();
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(now.Date, ((DateTime)dt.Rows[0]["dt"]).Date);
}
}
//查询功能
private void btnSearch_Click(object sender, EventArgs e)
{
//string datetime = dateTimePicker1.Text;
int num = Convert.ToInt32(numericUpDown1.Value);
string Device_Code = comboBoxDevice_Code.Text; //取出设备编号
string projiectName = comboBoxProjectName.Text; //取出工地名称
if (chBoxAllDevice.Checked == true) //如果全部设备被选中
{
if (chkSearchByDate.Checked) //如果选中按照时间查询，则按照时间查询(默认按照时间查询被选中）
{
bindDateWhereall();
}
else//否则查询所有的，没有任何条件
{
bindgvwAll();
}
}
else//否则就是按照时间和两个commbox来查询
{
if (chkSearchByDate.Checked == true)//如果选中时间查询就做时间，和两个下拉框的查询
{
bindgvwAllByDate();
}
else//否则就是做两个下拉框的模糊查询
{
if (Device_Code == "" && projiectName == "")
{
MessageBox.Show("请输入正确的关键字");
bindgvwAll();
return;
}
else if (projiectName == "")
{
string sqlString = "SELECT a.`Device_Code` as '设备编号',a.`Date` as '时间',a.`Dust` as '粉尘',a.`Noise` as '噪音',b.Project_Name as '工地名称' FROM `tb_project` b INNER JOIN `tb_monitordata01` a on a.`Project_ID`=b.`Project_ID` where a.`Device_Code`like '%" + Device_Code + "%' limit " + num + "";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sqlString, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
else if (Device_Code == "")
{
string sqlString = "SELECT a.`Device_Code` as '设备编号',a.`Date` as '时间',a.`Dust` as '粉尘',a.`Noise` as '噪音',b.Project_Name as '工地名称' FROM `tb_project` b INNER JOIN `tb_monitordata01` a on a.`Project_ID`=b.`Project_ID` where b.`Project_Name`like '%" + projiectName + "%' limit " + num + "";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sqlString, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
else
{
string sqlString = "SELECT a.`Device_Code` as '设备编号',a.`Date` as '时间',a.`Dust` as '粉尘',a.`Noise` as '噪音',b.Project_Name as '工地名称' FROM `tb_project` b INNER JOIN `tb_monitordata01` a on a.`Project_ID`=b.`Project_ID` where b.`Project_Name`like '%" + projiectName + "%' and a.`Device_Code`like '%" + Device_Code + "%' limit " + num + "";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sqlString, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
}
}
}
public void NotLastOneWins()
{
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, 'Test')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.CompareAllSearchableValues;
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
execSQL("UPDATE Test SET name='Test2' WHERE id=1");
try
{
dt.Rows[0]["name"] = "Test3";
da.Update(dt);
Assert.Fail("This should not work");
}
catch (DBConcurrencyException)
{
}
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("Test2", dt.Rows[0]["name"]);
}
private void btnnSearch_Click(object sender, EventArgs e)
{
string Device_Code = comboBoxDevice_Code.Text;
string ProjectName = comboBoxProjectName.Text;
string timeBegin = dateTimePicker1.Value.ToShortDateString() + " " + dateTimePicker2.Value.ToShortTimeString();
string timeEnd = dateTimePicker3.Value.ToShortDateString() + " " + dateTimePicker4.Value.ToShortTimeString();
if (chkSearchByDate.Checked == true && chBoxAllDevice.Checked == true && chkhour.Checked == true)
{
string sql = "select a.device_code as '设备编号',b.project_Name as '工地名称',count(*) as '总数据',COUNT(IF(a.Dust>0,1, NULL)) as '粉尘',avg(Dust) as '粉尘平均值',min(Dust) as '粉尘最小值',max(Dust) as '粉尘最大值', COUNT(IF(a.Noise>0,1, NULL)) as '噪声',avg(Noise) as '噪音平均值',min(Noise) as '噪音最小值',max(Noise) as '噪音最大值' from `tb_monitordata01` a inner join `tb_project` b on a.project_id=b.project_id where a.date between '" + timeBegin + "' and '" + timeEnd + "'";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
else if (chkSearchByDate.Checked == true && chBoxAllDevice.Checked == false && chkhour.Checked == true)
{
string sql = "select a.device_code as '设备编号',b.project_Name as '工地名称',count(*) as '总数据',COUNT(IF(a.Dust>0,1, NULL)) as '粉尘',avg(Dust) as '粉尘平均值',min(Dust) as '粉尘最小值',max(Dust) as '粉尘最大值', COUNT(IF(a.Noise>0,1, NULL)) as '噪声',avg(Noise) as '噪音平均值',min(Noise) as '噪音最小值',max(Noise) as '噪音最大值' from `tb_monitordata01` a inner join `tb_project` b on a.project_id=b.project_id where a.Device_code='" + Device_Code + "' AND b.project_Name='" + ProjectName + "' and a.date between '" + timeBegin + "' and '" + timeEnd + "'";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
else if (chkSearchByDate.Checked == true && chBoxAllDevice.Checked == true && chkday.Checked == true)
{
string starthour = dateTimePicker1.Value.ToString();
string endhour = dateTimePicker3.Value.ToString();
string sql = "select a.device_code as '设备编号',b.project_Name as '工地名称',count(*) as '总数据',COUNT(IF(a.Dust>0,1, NULL)) as '粉尘',avg(Dust) as '粉尘平均值',min(Dust) as '粉尘最小值',max(Dust) as '粉尘最大值', COUNT(IF(a.Noise>0,1, NULL)) as '噪声',avg(Noise) as '噪音平均值',min(Noise) as '噪音最小值',max(Noise) as '噪音最大值' from `tb_monitordata01` a inner join `tb_project` b on a.project_id=b.project_id where a.date between '" + starthour + "' and '" + endhour + "'";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
else if (chkSearchByDate.Checked == true && chBoxAllDevice.Checked == false && chkday.Checked == true)
{
string starthour = dateTimePicker1.Value.ToString();
string endhour = dateTimePicker3.Value.ToString();
string sql = "select a.device_code as '设备编号',b.project_Name as '工地名称',count(*) as '总数据',COUNT(IF(a.Dust>0,1, NULL)) as '粉尘',avg(Dust) as '粉尘平均值',min(Dust) as '粉尘最小值',max(Dust) as '粉尘最大值', COUNT(IF(a.Noise>0,1, NULL)) as '噪声',avg(Noise) as '噪音平均值',min(Noise) as '噪音最小值',max(Noise) as '噪音最大值' from `tb_monitordata01` a inner join `tb_project` b on a.project_id=b.project_id where a.Device_code='" + Device_Code + "' AND b.project_Name='" + ProjectName + "' and a.date between '" + timeBegin + "' and '" + timeEnd + "'";
using (MySqlConnection connPi = new MySqlConnection(serverConnectionString))
{
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connPi);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
adapter.Fill(table);
gvwAll.DataSource = mySourceQueryData;
mySourceQueryData.DataSource = table;
gvwAll.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
}
}
public void UpdatingWithDateInKey()
{
execSQL("CREATE TABLE Test (cod INT, dt DATE, PRIMARY KEY(cod, dt))");
execSQL("INSERT INTO Test (cod, dt) VALUES (1, '2006-1-1')");
execSQL("INSERT INTO Test (cod, dt) VALUES (2, '2006-1-2')");
execSQL("INSERT INTO Test (cod, dt) VALUES (3, '2006-1-3')");
execSQL("INSERT INTO Test (cod, dt) VALUES (4, '2006-1-4')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test ORDER BY cod", conn);
MySqlCommandBuilder bld = new MySqlCommandBuilder(da);
bld.ConflictOption = ConflictOption.OverwriteChanges;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["cod"] = 6;
da.Update(dt);
dt.Clear();
da.SelectCommand.CommandText = "SELECT * FROM Test WHERE cod=6";
da.Fill(dt);
Assert.AreEqual(6, dt.Rows[0]["cod"]);
}
private void GetData()
{
try
{
//string connectionString = "Data Source='IT-PC\\SQLEXPRESS';Initial Catalog=Personalia;User ID=itmis;Password=itmis"; //ConfigurationManager.AppSettings["connectionString"];
MySqlConnection connection = new MySqlConnection(GlobalVariables.strCon);
// Create a DataSet.
data = new DataSet();
// Add data from the Customers table to the DataSet.
masterDataAdapter = new
MySqlDataAdapter("select * from tblcutt where STYLEID='" + txtStyleID.Text + "' AND COLORID='" + txtColorID.Text + "'", connection);
// masterDataAdapter.TableMappings.Add("Orders", "tblcutt");
masterDataAdapter.Fill(data, "tblcutt");
sqlCommandBuilder = new MySqlCommandBuilder(masterDataAdapter);
// detail
if (data.Tables[0].Rows.Count == 0)
{
string cutno;
cutno = "";
detailsDataAdapter = new
MySqlDataAdapter("select * from tblcuttdetail where CUTNO='" + cutno + "'", connection);
detailsDataAdapter.Fill(data, "tblcuttdetail");
}
for (int i = 0; i < data.Tables[0].Rows.Count; i++)
{
string cutno;
cutno = Convert.ToString(data.Tables[0].Rows[i]["CUTNO"]);
detailsDataAdapter = new
MySqlDataAdapter("select * from tblcuttdetail where CUTNO='" + cutno + "'", connection);
detailsDataAdapter.Fill(data, "tblcuttdetail");
}
sqlCommandBuilder = new MySqlCommandBuilder(detailsDataAdapter);
// Establish a relationship between the two tables.
DataRelation relation = new DataRelation("tblcuttdetail",
data.Tables["tblcutt"].Columns["CUTNO"],
data.Tables["tblcuttdetail"].Columns["CUTNO"]);
data.Relations.Add(relation);
// Bind the master data connector to the Customers table.
masterBindingSource.DataSource = data;
masterBindingSource.DataMember = "tblcutt";
// Nav.BindingSource = masterBindingSource;
// Bind the details data connector to the master data connector,
// using the DataRelation name to filter the information in the
// details table based on the current row in the master table.
detailsBindingSource.DataSource = masterBindingSource;
detailsBindingSource.DataMember = "tblcuttdetail";
//combo size
//Month Data Source
string selectQueryStringSize = "SELECT SIZEID FROM tblproductionsize";
MySqlDataAdapter sqlDataAdapterSize = new MySqlDataAdapter(selectQueryStringSize, connection);
MySqlCommandBuilder sqlCommandBuilderSize = new MySqlCommandBuilder(sqlDataAdapterSize);
DataTable dataTableMonth = new DataTable();
sqlDataAdapterSize.Fill(dataTableMonth);
BindingSource bindingSourceSize = new BindingSource();
bindingSourceSize.DataSource = dataTableMonth;
// end combo size
detailsDataGridView.Columns.Clear();
DataGridViewComboBoxColumn Columnsize = new DataGridViewComboBoxColumn();
Columnsize.DataPropertyName = "SIZEID";
Columnsize.HeaderText = "SIZEID";
Columnsize.Width = 120;
Columnsize.DataSource = bindingSourceSize;
Columnsize.ValueMember = "SIZEID";
Columnsize.DisplayMember = "SIZEID";
detailsDataGridView.Columns.Add(Columnsize);
// combo
//Adding Year TextBox
DataGridViewTextBoxColumn ColumnYear = new DataGridViewTextBoxColumn();
ColumnYear.HeaderText = "QTY";
ColumnYear.Width = 80;
ColumnYear.DataPropertyName = "QTY";
detailsDataGridView.Columns.Add(ColumnYear);
//
//tambahan
/*
* txtOrder.DataBindings.Clear();
* txtDate.DataBindings.Clear();
* txtRemarks.DataBindings.Clear();
* txtSupplierID.DataBindings.Clear();
*
* txtOrder.DataBindings.Add(new Binding("Text", masterBindingSource, "OrderID", true, DataSourceUpdateMode.OnPropertyChanged));
* txtDate.DataBindings.Add(new Binding("Text", masterBindingSource, "Date", true, DataSourceUpdateMode.OnPropertyChanged));
* txtRemarks.DataBindings.Add(new Binding("Text", masterBindingSource, "Remarks", true, DataSourceUpdateMode.OnPropertyChanged));
* txtSupplierID.DataBindings.Add(new Binding("Text", masterBindingSource, "SupplierID", true, DataSourceUpdateMode.OnPropertyChanged));
*/
//txtOrder.DataBindings.Add(new Binding("Text", masterBindingSource, "OrderID", true));
}
catch (MySqlException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
// Oppdater en rad med en spesifikk primærnøkkel i en angitt tabell
public static bool UpdateRow(string tableName, string primaryKey, DataRow rowData)
{
if (!initialized || (!usingLocalDatabase && !CheckConnection()))
{
return(false);
}
DataRow selectedRow = null;
if (tableName == "booking")
{
selectedRow = bookingTable.Rows.Find(primaryKey);
selectedRow.BeginEdit();
selectedRow = rowData;
selectedRow.EndEdit();
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daBooking);
daBooking.Update(ds, "booking");
}
}
else if (tableName == "ansatte")
{
selectedRow = employeesTable.Rows.Find(primaryKey);
selectedRow.BeginEdit();
selectedRow = rowData;
selectedRow.EndEdit();
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daEmployees);
daEmployees.Update(ds, "ansatte");
}
}
else if (tableName == "bestillinger")
{
selectedRow = ordersTable.Rows.Find(primaryKey);
selectedRow.BeginEdit();
selectedRow = rowData;
selectedRow.EndEdit();
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daOrders);
daOrders.Update(ds, "bestillinger");
}
}
else if (tableName == "rom")
{
selectedRow = roomTable.Rows.Find(primaryKey);
selectedRow.BeginEdit();
selectedRow = rowData;
selectedRow.EndEdit();
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daRoom);
daRoom.Update(ds, "rom");
}
}
else if (tableName == "romtyper")
{
selectedRow = roomTypesTable.Rows.Find(primaryKey);
selectedRow.BeginEdit();
selectedRow = rowData;
selectedRow.EndEdit();
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daRoomTypes);
daRoomTypes.Update(ds, "romtyper");
}
}
string outputString = "";
for (int i = 0; i < rowData.ItemArray.Length; i++)
{
outputString += rowData[i].ToString() + ", ";
}
Console.WriteLine("UPDATE: " + outputString + "INTO " + tableName);
ds.AcceptChanges();
OnUpdate();
return(true);
}
public void AutoIncrementColumns()
{
execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)");
execSQL("INSERT INTO Test VALUES(NULL)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataSet ds = new DataSet();
da.Fill(ds);
Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
DataRow row = ds.Tables[0].NewRow();
ds.Tables[0].Rows.Add(row);
da.Update(ds);
ds.Clear();
da.Fill(ds);
Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
cb.Dispose();
}
static void Main(string[] args)
{
Console.WriteLine("Введите имя пользователя: ");
string name = Console.ReadLine();
while (name.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите имя пользователя: ");
name = Console.ReadLine();
}
Console.WriteLine("Введите пароль: ");
string password = "";
while (true)
{
ConsoleKeyInfo i = Console.ReadKey(true);
if (i.Key == ConsoleKey.Enter)
{
if (password.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите пароль: ");
}
else
{
break;
}
}
else if (i.Key == ConsoleKey.Backspace)
{
if (password.Length > 0)
{
password = password.Remove(password.Length - 1);
Console.Write("\b \b");
}
}
else
{
password += i.KeyChar;
Console.Write("*");
}
}
while (password.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите пароль: ");
password = Console.ReadLine();
}
Console.WriteLine();
Console.WriteLine("Введите имя сервера: ");
string server_name = Console.ReadLine();
while (server_name.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите имя сервера: ");
server_name = Console.ReadLine();
}
Console.WriteLine("Введите имя базы данных: ");
string db_name = Console.ReadLine();
while (db_name.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите имя базы данных: ");
db_name = Console.ReadLine();
}
connectionString = $"uid={name}; Password={password}; server={server_name}; database={db_name};";
Connection = new MySqlConnection(connectionString);
dataSet = new DataSet($"{db_name}");
Console.Clear();
var allOK = true;
Console.WriteLine($"БД \"{db_name}\" ");
try
{
Connection.Open();
}
catch
{
Console.WriteLine("Ошибка подключения к БД:");
Console.ReadKey();
allOK = false;
}
finally
{
Console.WriteLine("Свойство подключения:");
Console.WriteLine($"\tСтрока подключения: {Connection.ConnectionString}");
Console.WriteLine($"\tБаза данных: {Connection.Database}");
Console.WriteLine($"\tСервер: {Connection.DataSource}");
if (allOK)
{
Console.WriteLine($"\tВерсия сервера: {Connection.ServerVersion}");
}
Connection.Close();
Console.WriteLine($"\tСостояние: {Connection.State}");
if (!allOK)
{
Environment.Exit(0);
}
}
Console.WriteLine("Введите имя таблицы: ");
var TableName = Console.ReadLine();
while (server_name.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите имя таблицы: ");
TableName = Console.ReadLine();
}
Console.WriteLine("Введите название столбцов: ");
string cols_t = Console.ReadLine();
while (cols_t.Length == 0)
{
Console.WriteLine("Вы ничего не ввели!");
Console.WriteLine("Введите название столбцов: ");
cols_t = Console.ReadLine();
}
int col_num = 2;
for (int i = 0; i < cols_t.Length; i++)
{
if (cols_t[i] == ' ')
{
col_num++;
}
}
var myCom = new MySqlCommand();
myCom.CommandType = CommandType.TableDirect;
myCom.CommandText = TableName;
try
{
Connection.Open();
}
catch
{
Connection.Close();
throw;
}
myCom.Connection = Connection;
var myReader = myCom.ExecuteReader();
string t_name = myReader.GetName(0);
Connection.Close();
if (cols_t.Contains(t_name))
{
col_num--;
}
cols_t = cols_t.Replace($"{t_name}", "");
cols_t = $"`{t_name} " + cols_t + "`";
cols_t = cols_t.Replace(" ", " ");
string cols = cols_t.Replace(" ", "`, `");
var query = $"SELECT {cols} FROM {TableName}";
var da = new MySqlDataAdapter(query, Connection);
da.Fill(dataSet, TableName);
string [] cols_names = new string [col_num];
for (int i = 0; i < col_num; i++)
{
cols_names[i] = dataSet.Tables[TableName].Columns[i].ColumnName;
}
var comBuilder = new MySqlCommandBuilder(da);
var index = 0;
var TotalRows = dataSet.Tables[TableName].Rows.Count;
var drow = dataSet.Tables[TableName].Rows[index];
ShowTable(TableName, col_num);
Console.WriteLine("Текущая запись");
for (int i = 0; i < col_num; i++)
{
Console.Write($"#{drow[cols_names[i]],10}: ");
}
Console.SetCursorPosition(0, Console.CursorTop);
ConsoleKeyInfo cki;
do
{
cki = Console.ReadKey(true);
switch (cki.Key)
{
case ConsoleKey.DownArrow:
if (index <= TotalRows - 1)
{
index++;
if (index == TotalRows)
{
index = 0;
}
}
break;
case ConsoleKey.UpArrow:
if (index >= 0)
{
index--;
if (index == -1)
{
index = TotalRows - 1;
}
}
break;
case ConsoleKey.T:
if ((cki.Modifiers & ConsoleModifiers.Control) != 0)
{
ShowTable($"{TableName}", col_num);
}
break;
case ConsoleKey.L:
if ((cki.Modifiers & ConsoleModifiers.Control) != 0)
{
ShowTableInDB($"{TableName}");
}
break;
case ConsoleKey.Enter:
if (drow.RowState == DataRowState.Deleted)
{
break;
}
Console.WriteLine("\nВведите значения полей:");
foreach (DataColumn column in dataSet.Tables[TableName].Columns)
{
Console.Write($"{column.ColumnName}: ");
var val = Console.ReadLine();
drow[column.ColumnName] = val;
}
break;
case ConsoleKey.Backspace:
drow.RejectChanges();
break;
case ConsoleKey.R:
if ((cki.Modifiers & ConsoleModifiers.Control) != 0)
{
da.Fill(dataSet, TableName);
}
index = 0;
break;
case ConsoleKey.F2:
try
{
da.Update(dataSet, TableName);
dataSet.Tables[TableName].AcceptChanges();
Console.WriteLine("\nДанные сохранены!");
}
catch (DBConcurrencyException ex)
{
Console.WriteLine($"Возникло исключение: {ex.Message}");
}
TotalRows = dataSet.Tables[TableName].Rows.Count;
if (index != 0)
{
index--;
}
break;
case ConsoleKey.Delete:
drow.Delete();
break;
case ConsoleKey.Insert:
myCom = new MySqlCommand();
myCom.CommandType = CommandType.TableDirect;
myCom.CommandText = TableName;
try
{
Connection.Open();
}
catch
{
Connection.Close();
throw;
}
myCom.Connection = Connection;
myReader = myCom.ExecuteReader();
t_name = myReader.GetName(0);
Connection.Close();
Console.WriteLine(t_name);
myCom = new MySqlCommand();
myCom.CommandText = $"SELECT `{t_name}` FROM {TableName} ORDER BY `{t_name}` DESC";
myCom.Connection = Connection;
var curConState = Connection.State;
Connection.Open();
var pkey = (int)myCom.ExecuteScalar();
Connection.Close();
var row = dataSet.Tables[TableName].NewRow();
row[0] = ++pkey;
dataSet.Tables[TableName].Rows.Add(row);
var ins_index = dataSet.Tables[TableName].Rows.IndexOf(row);
index = ins_index;
break;
}
TotalRows = dataSet.Tables[0].Rows.Count;
drow = dataSet.Tables[TableName].Rows[index];
var s = " ";
if (drow.RowState == DataRowState.Deleted)
{
s = $"#{drow[0, DataRowVersion.Original],10}: {drow[1, DataRowVersion.Original],-20}";
}
else
{
s = $"#{drow[0],10}: {drow[1],-20} {drow.RowState.ToString(),10}";
}
Console.Write(s.PadRight(30));
Console.SetCursorPosition(0, Console.CursorTop);
} while (cki.Key != ConsoleKey.Escape);
}
public void UpdateNullTextFieldToEmptyString()
{
CreateDefaultTable();
execSQL("INSERT INTO Test (id, id2, name) VALUES (1, 1, NULL)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ToString(); // keep the compiler happy
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["name"] = "";
int updateCnt = da.Update(dt);
Assert.AreEqual(1, updateCnt);
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("", dt.Rows[0]["name"]);
}
public void QuietOpenAndClose()
{
execSQL("CREATE TABLE Test (id INT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES(1)");
using (MySqlConnection c = new MySqlConnection(GetConnectionString(true)))
{
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
Assert.IsTrue(c.State == ConnectionState.Closed);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(c.State == ConnectionState.Closed);
Assert.AreEqual(1, dt.Rows.Count);
dt.Rows[0][0] = 2;
DataRow[] rows = new DataRow[1];
rows[0] = dt.Rows[0];
da.Update(dt);
Assert.IsTrue(c.State == ConnectionState.Closed);
dt.Clear();
c.Open();
Assert.IsTrue(c.State == ConnectionState.Open);
da.Fill(dt);
Assert.IsTrue(c.State == ConnectionState.Open);
Assert.AreEqual(1, dt.Rows.Count);
cb.Dispose();
}
}
public void BatchUpdatesAndDeletes()
{
st.execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
st.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 = st.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.Equal(1, listener.Find("Query Opened: UPDATE"));
}
public void TestUpdate()
{
CreateDefaultTable();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.NewRow();
dr["id2"] = 2;
dr["name"] = "TestName1";
dt.Rows.Add(dr);
int count = da.Update(dt);
// make sure our refresh of auto increment values worked
Assert.AreEqual(1, count, "checking insert count");
Assert.IsNotNull(dt.Rows[dt.Rows.Count - 1]["id"],
"Checking auto increment column");
dt.Rows.Clear();
da.Fill(dt);
dt.Rows[0]["id2"] = 3;
dt.Rows[0]["name"] = "TestName2";
dt.Rows[0]["ts"] = DBNull.Value;
DateTime day1 = new DateTime(2003, 1, 16, 12, 24, 0);
dt.Rows[0]["dt"] = day1;
dt.Rows[0]["tm"] = day1.TimeOfDay;
count = da.Update(dt);
Assert.IsNotNull(dt.Rows[0]["ts"], "checking refresh of record");
Assert.AreEqual(3, dt.Rows[0]["id2"], "checking refresh of primary column");
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, count, "checking update count");
DateTime dateTime = (DateTime)dt.Rows[0]["dt"];
Assert.AreEqual(day1.Date, dateTime.Date, "checking date");
Assert.AreEqual(day1.TimeOfDay, dt.Rows[0]["tm"], "checking time");
dt.Rows[0].Delete();
count = da.Update(dt);
Assert.AreEqual(1, count, "checking insert count");
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(0, dt.Rows.Count, "checking row count");
cb.Dispose();
}
private void button2_Click(object sender, EventArgs e)
{
string yr, m, d;
yr = DateTime.Now.Year.ToString();
m = DateTime.Now.Month.ToString();
d = DateTime.Now.Day.ToString();
int d4 = Convert.ToInt32(d) + 1;
DialogResult result = new DialogResult();
result = MessageBox.Show("Veriyi güncellemek istiyormusunuz?", "Uyarı", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
//baglanti = new MySqlConnection(bag);
if (result == DialogResult.Yes)
{
try{
cmdb = new MySqlCommandBuilder(adapter);
/* DataSet uds = new DataSet();
* uds = dataSet.GetChanges(DataRowState.Modified);
*
* adapter.Update(uds);*/
adapter.Update(dataSet, "customer_case");
if (baglanti.State == ConnectionState.Closed)
{
baglanti.Open();
}
String sqlTotal2 = "SELECT CC.ODEMETIPI,SUM(CC.TL) AS TL, SUM(CC.EURO) AS EURO, SUM(CC.DOLAR) AS DOLAR FROM customer_case cc WHERE DATE BETWEEN '"
+ yr + "-" + m + "-" + d + "' AND " + "'" + yr + "-" + m + "-" + d4 + "'" + "GROUP BY CC.ODEMETIPI";
//if (DataGridList.Name == "dtgridSalesList")
// sql = sql + " ORDER BY SALE_CODE";
DataTable dtTotal2 = new DataTable();
MySqlDataAdapter adapterTotal2 = new MySqlDataAdapter();
MySqlCommand commandTotal2 = new MySqlCommand();
commandTotal2.CommandText = sqlTotal2;
commandTotal2.Connection = baglanti;
adapterTotal2.SelectCommand = commandTotal2;
//baglanti.Open();
adapterTotal2.Fill(dtTotal2);
grdCase_Total.DataSource = dtTotal2;
baglanti.Close();
grdCase_Total.Columns[0].HeaderText = "Ödeme Tipi";
grdCase_Total.Columns[1].HeaderText = "TL";
grdCase_Total.Columns[1].DefaultCellStyle.Format = "N";
grdCase_Total.Columns[2].HeaderText = "€";
grdCase_Total.Columns[2].DefaultCellStyle.Format = "N";
grdCase_Total.Columns[3].HeaderText = "$";
grdCase_Total.Columns[3].DefaultCellStyle.Format = "N";
}
catch (global::System.Exception ex)
{
MessageBox.Show((ex.Message + (" : " + "Bir hata olustu, lütfen daha sonra tekrar deneyiniz...")), "Uyarı", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
public void UpdateExtendedTextFields()
{
execSQL("CREATE TABLE Test (id int, notes MEDIUMTEXT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES(1, 'This is my note')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ToString(); // keep the compiler happy
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["notes"] = "This is my new note";
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual("This is my new note", dt.Rows[0]["notes"]);
}
public void DeriveParametersDoesNotExist()
{
using var cmd = new MySqlCommand("xx_does_not_exist", m_database.Connection);
cmd.CommandType = CommandType.StoredProcedure;
Assert.Throws <MySqlException>(() => MySqlCommandBuilder.DeriveParameters(cmd));
}
public void UseAdapterPropertyOfCommandBuilder()
{
CreateDefaultTable();
execSQL("INSERT INTO Test (id, id2, name) VALUES (NULL, 1, 'Test')");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder();
cb.DataAdapter = da;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["name"] = "Test Update";
int updateCnt = da.Update(dt);
Assert.AreEqual(1, updateCnt);
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("Test Update", dt.Rows[0]["name"]);
}
public void ParsingTender()
{
using (var connect = ConnectToDb.GetDbConnection())
{
connect.Open();
var selectTend =
$"SELECT id_tender FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND end_date = @end_date AND type_fz = @type_fz AND doc_publish_date = @doc_publish_date AND notice_version = @notice_version";
var cmd = new MySqlCommand(selectTend, connect);
cmd.Prepare();
cmd.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd.Parameters.AddWithValue("@type_fz", TypeFz);
cmd.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd.Parameters.AddWithValue("@notice_version", _tn.Status);
var dt = new DataTable();
var adapter = new MySqlDataAdapter {
SelectCommand = cmd
};
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return;
}
var dateUpd = DateTime.Now;
var cancelStatus = 0;
var updated = false;
var selectDateT =
$"SELECT id_tender, date_version, cancel FROM {AppBuilder.Prefix}tender WHERE purchase_number = @purchase_number AND type_fz = @type_fz";
var cmd2 = new MySqlCommand(selectDateT, connect);
cmd2.Prepare();
cmd2.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd2.Parameters.AddWithValue("@type_fz", TypeFz);
var adapter2 = new MySqlDataAdapter {
SelectCommand = cmd2
};
var dt2 = new DataTable();
adapter2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
{
updated = true;
if (dateUpd >= (DateTime)row["date_version"])
{
row["cancel"] = 1;
}
else
{
cancelStatus = 1;
}
}
var commandBuilder =
new MySqlCommandBuilder(adapter2)
{
ConflictOption = ConflictOption.OverwriteChanges
};
adapter2.Update(dt2);
var printForm = _tn.Href;
var customerId = 0;
var organiserId = 0;
var orgName = "ООО «ТД «Агат»";
if (!string.IsNullOrEmpty(orgName))
{
var selectOrg =
$"SELECT id_organizer FROM {AppBuilder.Prefix}organizer WHERE full_name = @full_name";
var cmd3 = new MySqlCommand(selectOrg, connect);
cmd3.Prepare();
cmd3.Parameters.AddWithValue("@full_name", orgName);
var dt3 = new DataTable();
var adapter3 = new MySqlDataAdapter {
SelectCommand = cmd3
};
adapter3.Fill(dt3);
if (dt3.Rows.Count > 0)
{
organiserId = (int)dt3.Rows[0].ItemArray[0];
}
else
{
var phone = "8 495 777-777-1";
var email = "*****@*****.**";
var inn = "";
var kpp = "";
var contactPerson = "";
var addOrganizer =
$"INSERT INTO {AppBuilder.Prefix}organizer SET full_name = @full_name, contact_phone = @contact_phone, contact_person = @contact_person, contact_email = @contact_email, inn = @inn, kpp = @kpp";
var cmd4 = new MySqlCommand(addOrganizer, connect);
cmd4.Prepare();
cmd4.Parameters.AddWithValue("@full_name", orgName);
cmd4.Parameters.AddWithValue("@contact_phone", phone);
cmd4.Parameters.AddWithValue("@contact_person", contactPerson);
cmd4.Parameters.AddWithValue("@contact_email", email);
cmd4.Parameters.AddWithValue("@inn", inn);
cmd4.Parameters.AddWithValue("@kpp", kpp);
cmd4.ExecuteNonQuery();
organiserId = (int)cmd4.LastInsertedId;
}
}
GetEtp(connect, out var idEtp);
var idPlacingWay = 0;
var insertTender =
$"INSERT INTO {AppBuilder.Prefix}tender SET id_region = @id_region, id_xml = @id_xml, purchase_number = @purchase_number, doc_publish_date = @doc_publish_date, href = @href, purchase_object_info = @purchase_object_info, type_fz = @type_fz, id_organizer = @id_organizer, id_placing_way = @id_placing_way, id_etp = @id_etp, end_date = @end_date, scoring_date = @scoring_date, bidding_date = @bidding_date, cancel = @cancel, date_version = @date_version, num_version = @num_version, notice_version = @notice_version, xml = @xml, print_form = @print_form";
var cmd9 = new MySqlCommand(insertTender, connect);
cmd9.Prepare();
cmd9.Parameters.AddWithValue("@id_region", 0);
cmd9.Parameters.AddWithValue("@id_xml", _tn.PurNum);
cmd9.Parameters.AddWithValue("@purchase_number", _tn.PurNum);
cmd9.Parameters.AddWithValue("@doc_publish_date", _tn.DatePub);
cmd9.Parameters.AddWithValue("@href", _tn.Href);
cmd9.Parameters.AddWithValue("@purchase_object_info", _tn.PurName);
cmd9.Parameters.AddWithValue("@type_fz", TypeFz);
cmd9.Parameters.AddWithValue("@id_organizer", organiserId);
cmd9.Parameters.AddWithValue("@id_placing_way", idPlacingWay);
cmd9.Parameters.AddWithValue("@id_etp", idEtp);
cmd9.Parameters.AddWithValue("@end_date", _tn.DateEnd);
cmd9.Parameters.AddWithValue("@scoring_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@bidding_date", DateTime.MinValue);
cmd9.Parameters.AddWithValue("@cancel", cancelStatus);
cmd9.Parameters.AddWithValue("@date_version", dateUpd);
cmd9.Parameters.AddWithValue("@num_version", 1);
cmd9.Parameters.AddWithValue("@notice_version", _tn.Status);
cmd9.Parameters.AddWithValue("@xml", _tn.Href);
cmd9.Parameters.AddWithValue("@print_form", printForm);
var resInsertTender = cmd9.ExecuteNonQuery();
var idTender = (int)cmd9.LastInsertedId;
Counter(resInsertTender, updated);
if (!string.IsNullOrEmpty(orgName))
{
var selectCustomer =
$"SELECT id_customer FROM {AppBuilder.Prefix}customer WHERE full_name = @full_name";
var cmd13 = new MySqlCommand(selectCustomer, connect);
cmd13.Prepare();
cmd13.Parameters.AddWithValue("@full_name", orgName);
var reader7 = cmd13.ExecuteReader();
if (reader7.HasRows)
{
reader7.Read();
customerId = (int)reader7["id_customer"];
reader7.Close();
}
else
{
reader7.Close();
var insertCustomer =
$"INSERT INTO {AppBuilder.Prefix}customer SET reg_num = @reg_num, full_name = @full_name, is223=1, inn = @inn";
var cmd14 = new MySqlCommand(insertCustomer, connect);
cmd14.Prepare();
var customerRegNumber = Guid.NewGuid().ToString();
cmd14.Parameters.AddWithValue("@reg_num", customerRegNumber);
cmd14.Parameters.AddWithValue("@full_name", orgName);
cmd14.Parameters.AddWithValue("@inn", "");
cmd14.ExecuteNonQuery();
customerId = (int)cmd14.LastInsertedId;
}
}
var lotNum = 1;
var insertLot =
$"INSERT INTO {AppBuilder.Prefix}lot SET id_tender = @id_tender, lot_number = @lot_number, max_price = @max_price, currency = @currency, finance_source = @finance_source";
var cmd18 = new MySqlCommand(insertLot, connect);
cmd18.Prepare();
cmd18.Parameters.AddWithValue("@id_tender", idTender);
cmd18.Parameters.AddWithValue("@lot_number", lotNum);
cmd18.Parameters.AddWithValue("@max_price", "");
cmd18.Parameters.AddWithValue("@currency", "");
cmd18.Parameters.AddWithValue("@finance_source", "");
cmd18.ExecuteNonQuery();
var idLot = (int)cmd18.LastInsertedId;
var insertLotitem =
$"INSERT INTO {AppBuilder.Prefix}purchase_object SET id_lot = @id_lot, id_customer = @id_customer, name = @name";
var cmd19 = new MySqlCommand(insertLotitem, connect);
cmd19.Prepare();
cmd19.Parameters.AddWithValue("@id_lot", idLot);
cmd19.Parameters.AddWithValue("@id_customer", customerId);
cmd19.Parameters.AddWithValue("@name", _tn.PurName);
cmd19.ExecuteNonQuery();
var insertCustomerRequirement =
$"INSERT INTO {AppBuilder.Prefix}customer_requirement SET id_lot = @id_lot, id_customer = @id_customer, delivery_place = @delivery_place, max_price = @max_price, delivery_term = @delivery_term";
var cmd16 = new MySqlCommand(insertCustomerRequirement, connect);
cmd16.Prepare();
cmd16.Parameters.AddWithValue("@id_lot", idLot);
cmd16.Parameters.AddWithValue("@id_customer", customerId);
cmd16.Parameters.AddWithValue("@delivery_place", "");
cmd16.Parameters.AddWithValue("@max_price", "");
cmd16.Parameters.AddWithValue("@delivery_term", _tn.Requirements);
cmd16.ExecuteNonQuery();
TenderKwords(connect, idTender);
AddVerNumber(connect, _tn.PurNum, TypeFz);
}
}
public void UnsignedTypes()
{
execSQL("CREATE TABLE Test (b TINYINT UNSIGNED PRIMARY KEY)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataView dv = new DataView(dt);
DataRowView row;
row = dv.AddNew();
row["b"] = 120;
row.EndEdit();
da.Update(dv.Table);
row = dv.AddNew();
row["b"] = 135;
row.EndEdit();
da.Update(dv.Table);
cb.Dispose();
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (b MEDIUMINT UNSIGNED PRIMARY KEY)");
execSQL("INSERT INTO Test VALUES(20)");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test WHERE (b > ?id)", conn);
cmd.Parameters.Add("?id", MySqlDbType.UInt16).Value = 10;
using (MySqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
Assert.AreEqual(20, dr.GetUInt16(0));
}
}
/// <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 UpdateDataSet()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
string s = (string)dt.Rows[0][2];
Assert.AreEqual("Text field", s);
byte[] inBuf = Utils.CreateBlob(512);
dt.Rows[0].BeginEdit();
dt.Rows[0]["blob1"] = inBuf;
dt.Rows[0].EndEdit();
DataTable changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
dt.Clear();
da.Fill(dt);
cb.Dispose();
byte[] outBuf = (byte[])dt.Rows[0]["blob1"];
Assert.AreEqual(inBuf.Length, outBuf.Length,
"checking length of updated buffer");
for (int y = 0; y < inBuf.Length; y++)
Assert.AreEqual(inBuf[y], outBuf[y], "checking array data");
}
public static string QuoteTable(this string sqlQuery, string tableName)
{
var cb = new MySqlCommandBuilder();
return(string.Format(sqlQuery, cb.QuoteIdentifier(tableName)));
}
public void DateTimeInDataTable()
{
execSQL("INSERT INTO Test VALUES(1, Now(), '0000-00-00', NULL, NULL)");
using (MySqlConnection c = new MySqlConnection(
conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true"))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row["id"] = 2;
row["dt"] = new MySqlDateTime(DateTime.Now);
row["d"] = new MySqlDateTime(DateTime.Now);
row["t"] = new TimeSpan(1, 1, 1);
row["ts"] = DBNull.Value;
dt.Rows.Add(row);
da.Update(dt);
dt.Rows.Clear();
da.Fill(dt);
Assert.AreEqual(2, dt.Rows.Count);
cb.Dispose();
}
}
/// <summary>绑定
/// 绑定
/// </summary>
/// <param name="strTableName"></param>
private void BindGrid(string strTableName)
{
string strSql = "SELECT * FROM " + strTableName;
switch (m_dbType)
{
case SqlType.SqlServer:
SqlConnection conn = new SqlConnection(m_databaseSchema.ConnectionString);
SqlCommand cmd = new SqlCommand(strSql, conn);
m_sda = new SqlDataAdapter(cmd);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_sda);
m_ds = new DataSet();
m_sda.Fill(m_ds);
conn.Close();
//绑定数据源
grdTable.DataSource = m_ds.Tables[0];
break;
case SqlType.Oracle:
break;
case SqlType.MySql:
MySqlConnection myconn = new MySqlConnection(m_databaseSchema.ConnectionString);
MySqlCommand mycmd = new MySqlCommand(strSql, myconn);
m_mysda = new MySqlDataAdapter(mycmd);
MySqlCommandBuilder mycmdBuilder = new MySqlCommandBuilder(m_mysda);
m_ds = new DataSet();
m_mysda.Fill(m_ds);
myconn.Close();
//绑定数据源
grdTable.DataSource = m_ds.Tables[0];
break;
case SqlType.SQLite:
SQLiteConnection sqliteconn = new SQLiteConnection(m_databaseSchema.ConnectionString);
SQLiteCommand sqlitecmd = new SQLiteCommand(strSql, sqliteconn);
m_sqlitesda = new SQLiteDataAdapter(sqlitecmd);
SQLiteCommandBuilder sqlitecmdBuilder = new SQLiteCommandBuilder(m_sqlitesda);
m_ds = new DataSet();
m_sqlitesda.Fill(m_ds);
sqliteconn.Close();
//绑定数据源
grdTable.DataSource = m_ds.Tables[0];
break;
case SqlType.SqlServerCe:
break;
case SqlType.PostgreSql:
break;
case SqlType.Db2:
break;
case SqlType.Accesss:
break;
default:
break;
}
}
public void TestAllowZeroDateTime()
{
execSQL("TRUNCATE TABLE Test");
execSQL("INSERT INTO Test (id, d, dt) VALUES (1, '0000-00-00', '0000-00-00 00:00:00')");
using (MySqlConnection c = new MySqlConnection(
conn.ConnectionString + ";pooling=false;AllowZeroDatetime=true"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.IsTrue(reader.GetValue(1) is MySqlDateTime);
Assert.IsTrue(reader.GetValue(2) is MySqlDateTime);
Assert.IsFalse(reader.GetMySqlDateTime(1).IsValidDateTime);
Assert.IsFalse(reader.GetMySqlDateTime(2).IsValidDateTime);
try
{
reader.GetDateTime(1);
Assert.Fail("This should not succeed");
}
catch (MySqlConversionException)
{
}
}
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.Fill(dt);
dt.Rows[0]["id"] = 2;
DataRow row = dt.NewRow();
row["id"] = 3;
row["d"] = new MySqlDateTime("2003-9-24");
row["dt"] = new MySqlDateTime("0000/0/00 00:00:00");
dt.Rows.Add(row);
da.Update(dt);
dt.Clear();
da.Fill(dt);
Assert.AreEqual(2, dt.Rows.Count);
MySqlDateTime date = (MySqlDateTime)dt.Rows[1]["d"];
Assert.AreEqual(2003, date.Year);
Assert.AreEqual(9, date.Month);
Assert.AreEqual(24, date.Day);
cb.Dispose();
}
}
/// <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("Ошибка инициализации подключения, повторите снова или обратитесь к системному администратору");
}
}
public void MultiUpdate()
{
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 * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["id"] = 4;
dt.Rows[0]["name"] = "test4";
dt.Rows[1]["id"] = 5;
dt.Rows[1]["name"] = "test5";
dt.Rows[2]["id"] = 6;
dt.Rows[2]["name"] = "test6";
DataTable changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
dt.Rows[0]["id"] = 7;
dt.Rows[0]["name"] = "test7";
dt.Rows[1]["id"] = 8;
dt.Rows[1]["name"] = "test8";
dt.Rows[2]["id"] = 9;
dt.Rows[2]["name"] = "test9";
changes = dt.GetChanges();
da.Update(changes);
dt.AcceptChanges();
cb.Dispose();
}
private void SikronizirajStavkeRacuna()
{
if (Klase.Racuni.ProvjeriAktivnost(Convert.ToInt32(listBox1.SelectedItem) - 1) == true)
{ // ONLY DELETE DATA FROM TABLES IF ACTIVE OTHERWISE JUST READ
using (MySqlConnection sqlconn = new MySqlConnection(Login.constring))
{
sqlconn.Open();
string referenca_na_godinu = ""; // TO BE CHANGED LATER
string query = "DELETE FROM racuni_stavke" + referenca_na_godinu + " WHERE id_racun = " + Convert.ToInt32(listBox1.SelectedItem);
MySqlCommand mySqlCommand = new MySqlCommand(query, sqlconn);
mySqlCommand.ExecuteNonQuery();
string query1 = "SELECT * FROM racuni_stavke" + referenca_na_godinu + " WHERE id_racun = " + Convert.ToInt32(listBox1.SelectedItem);
MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(query1, sqlconn);
DataSet ds = new DataSet();
ds.Clear();
mySqlDataAdapter.Fill(ds);
MySqlCommandBuilder cb = new MySqlCommandBuilder(mySqlDataAdapter);
mySqlDataAdapter.UpdateCommand = cb.GetUpdateCommand();
DataTable dt = ds.Tables[0];
dt.Clear();
var StanjeSkladista = Klase.RacuniStavke.GetFromStanjeSkladista();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
try
{
if (Convert.ToInt32(dataGridView1.Rows[i].Cells["sifra"].Value) != 0)
{
Artikl art = new Artikl();
StanjeSkladista.TryGetValue(dataGridView1.Rows[i].Cells["sifra"].Value.ToString(), out art);
dt.Rows.Add(new String[] {
listBox1.SelectedItem.ToString(),
dataGridView1.Rows[i].Cells["sifra"].Value.ToString(),
dataGridView1.Rows[i].Cells["naziv"].Value.ToString(),
dataGridView1.Rows[i].Cells["kolicina"].Value.ToString(),
art.min_mpc.ToString(),
art.MPC.ToString(),
dataGridView1.Rows[i].Cells["MPC_Popust"].Value.ToString(),
dataGridView1.Rows[i].Cells["MPC_Prodano"].Value.ToString(),
dataGridView1.Rows[i].Cells["index_stavke"].Value.ToString()
});
}
mySqlDataAdapter.Update(dt); //Updating the values but I only want to update if not the same
}
catch { };
}
}
}
}
public void QuoteAndUnquoteIdentifiers()
{
MySqlCommandBuilder cb = new MySqlCommandBuilder();
Assert.AreEqual("`boo`", cb.QuoteIdentifier("boo"));
Assert.AreEqual("`bo``o`", cb.QuoteIdentifier("bo`o"));
Assert.AreEqual("`boo`", cb.QuoteIdentifier("`boo`"));
// now do the unquoting
Assert.AreEqual("boo", cb.UnquoteIdentifier("`boo`"));
Assert.AreEqual("`boo", cb.UnquoteIdentifier("`boo"));
Assert.AreEqual("bo`o", cb.UnquoteIdentifier("`bo``o`"));
}
private void button1_Click(object sender, EventArgs e)
{
MySqlCommandBuilder mycb = new MySqlCommandBuilder(adapter);
adapter.Update(myds, "機台資料表");
}
public void SpecialCharactersInFieldNames()
{
execSQL("CREATE TABLE Test (`col%1` int PRIMARY KEY, `col()2` int, `col<>3` int, `col/4` int)");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
cb.ToString(); // keep the compiler happy
DataTable dt = new DataTable();
da.Fill(dt);
DataRow row = dt.NewRow();
row[0] = 1;
row[1] = 2;
row[2] = 3;
row[3] = 4;
dt.Rows.Add(row);
da.Update(dt);
}
private void Machine_FormClosing(object sender, FormClosingEventArgs e)
{
int added = 0;
int deleted = 0;
int modified = 0;
foreach (DataRow dr in myds.Tables["機台資料表"].Rows)
{
switch (dr.RowState)
{
case DataRowState.Added:
added++;
break;
case DataRowState.Deleted:
deleted++;
break;
case DataRowState.Modified:
modified++;
break;
case DataRowState.Unchanged:
break;
default:
break;
}
}
if (added != 0 || deleted != 0 || modified != 0)
{
String result = "";
result += (added == 0 ? "" : "【新增】 " + added + "筆新資料\n")
+ (deleted == 0 ? "" : "【刪除】 " + deleted + "筆資料\n")
+ (modified == 0 ? "" : "【變更】 " + modified + "筆資料\n")
+ "要儲存嗎？";
DialogResult dialogResult = MessageBox.Show(result, "要儲存嗎？", MessageBoxButtons.YesNoCancel);
if (dialogResult == DialogResult.Yes)
{
conn.Open();
MySqlCommandBuilder mycb = new MySqlCommandBuilder(adapter);
adapter.Update(myds, "機台資料表");
conn.Close();
}
else if (dialogResult == DialogResult.No)
{
//do something
}
else if (dialogResult == DialogResult.Cancel)
{
e.Cancel = true;
}
}
}
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();
}
private void top10(DateTime beginDateTime, DateTime endDateTime)
{
//Hello sp
// count menu items to make menu items list and amount
MySqlCommand cmmd = new MySqlCommand();
string queryCount = "SELECT * FROM `MENU-ITEM` ";
int listSize = 0;
connection.Open();
//MySqlCommand cmmd = new MySqlCommand();
cmmd.CommandText = queryCount;
cmmd.Connection = connection;
try
{
adap = new MySqlDataAdapter(cmmd);
build = new MySqlCommandBuilder(adap);
DataTable ds = new DataTable();
adap.Fill(ds);
menu = ds;
}
catch (Exception e)
{
MessageBox.Show("Error " + e.Message);
}
listSize = menu.Rows.Count;
int[,] menuItemsList = new int[listSize, 2];
int j = 0;
foreach (DataRow r in menu.Rows)
{
menuItemsList[j, 0] = int.Parse(r["Menu_Item_ID"].ToString());
j++;
}
//listSize = cmd.ExecuteReader()["count"];
connection.Close();
/*string[] Top10names = new string[10];
* int[] Top10amount = new int[10];*/
// fill arrays
string menuQuery = "SELECT * FROM `MENU-ITEM`";
MySqlCommand cmd = new MySqlCommand(menuQuery, connection);
/*
* connection.Open();
* //put in comand
* MySqlCommand cmd = new MySqlCommand(menuQuery, connection);
* MySqlDataReader dataR = cmd.ExecuteReader();
* // data reader
* while (dataR.Read())
* {
* int menuItemID = int.Parse(dataR["Menu_Item_ID"] + "");
* for (int i = 0; i< listSize; i++)
* {
* menuItemsList[i,0] = menuItemID;
* menuItemsList[i, 1] = 0;
* }
*
* }
*
* dataR.Close();
* connection.Close();*/
/*for (int i = 0; i < 6; i++)
* {
* menuItemsList[i, 0] = i+1;
* }*/
// go get orders in the selected time period
int quantity = 0;
int orderID = 0;
int menuID = 0;
menuQuery = "SELECT * FROM ORDERS where Order_DateTime >= '" + beginDateTime.ToString("yyyy-MM-dd H:mm:ss") + "' AND Order_DateTime <= '" + endDateTime.ToString("yyyy-MM-dd H:mm:ss") + "' ";
cmmd = new MySqlCommand();
cmmd.CommandText = menuQuery;
cmmd.Connection = connection;
try
{
adap = new MySqlDataAdapter(cmmd);
build = new MySqlCommandBuilder(adap);
DataTable ds = new DataTable();
adap.Fill(ds);
order10 = ds;
}
catch
{
}
foreach (DataRow r in order10.Rows)
{
// go get menu items connected to ID and add
orderID = int.Parse(r["Order_ID"].ToString());
int prodID = int.Parse(r["MenuItemID"].ToString());
int count = int.Parse(r["Quantity_Orderd"].ToString());
for (int i = 0; i < listSize; i++)
{
if (menuItemsList[i, 0] == prodID)
{
menuItemsList[i, 1] += count;
}
}
//menuQuery = "SELECT * FROM `ORDERS-DETAIL` WHERE Order_ID = '" + orderID + "' ";
//connection.Open();
//put in comand
//cmd = new MySqlCommand(menuQuery, connection);
//MySqlDataReader dataR2 = cmd.ExecuteReader();
// data reader
/*while (dataR.Read())
* {
* menuID = int.Parse(dataR["MenuItemID"] + "");
* quantity = int.Parse(dataR["Quantity_Orderd"] + "");
*
* for (int i = 0; i < listSize; i++)
* {
* if (menuItemsList[i, 0] == menuID)
* {
* menuItemsList[i, 1] += quantity;
* }
*
* }
*
* }*/
// close data reader
//dataR.Close();
// close connection
//connection.Close();
}
int temp1 = 0;
int temp2 = 0;
for (int i = 0; i < listSize; i++)
{
for (int k = 0; k < listSize - 1; k++)
{
if (menuItemsList[k, 1] < menuItemsList[k + 1, 1])
{
temp1 = menuItemsList[k, 0];
temp2 = menuItemsList[k, 1];
menuItemsList[k, 0] = menuItemsList[k + 1, 0];
menuItemsList[k, 1] = menuItemsList[k + 1, 1];
menuItemsList[k + 1, 0] = temp1;
menuItemsList[k + 1, 1] = temp2;
}
}
}
int p = listSize;
for (int k = listSize - 1; (k >= 0) && (k >= listSize - 10); k--)
{
menuQuery = "SELECT * FROM `MENU-ITEM` WHERE `Menu_Item_ID` = '" + menuItemsList[k, 0] + "' ";
connection.Open();
//put in comand
cmd = new MySqlCommand(menuQuery, connection);
MySqlDataReader dataR2 = cmd.ExecuteReader();
// data reader
while (dataR2.Read())
{
listBoxTop10.Items.Add((p - k).ToString() + ". " + dataR2["Item_Name"] + "");
//MessageBox.Show((p - k) + " " + dataR2["Item_Name"]);
}
// close data reader
dataR2.Close();
// close connection
connection.Close();
}
MessageBox.Show("Done");
}
private void PurchaseOrderViewer_Load(object sender, EventArgs e)
{
//set start date parameter
ParameterFields From = new ParameterFields();
ParameterField PID = new ParameterField();
PID.Name = "FromDate";
ParameterDiscreteValue val = new ParameterDiscreteValue();
val.Value = Reports.startDate;
PID.CurrentValues.Add(val);
From.Add(PID);
porptviewer.ParameterFieldInfo = From;
//set end date parameter
ParameterField PID1 = new ParameterField();
PID1.Name = "ToDate";
ParameterDiscreteValue val1 = new ParameterDiscreteValue();
val1.Value = Reports.endDate;
PID1.CurrentValues.Add(val1);
From.Add(PID1);
porptviewer.ParameterFieldInfo = From;
//create data table
DataTable idtbl = new DataTable();
DataTable materialtblTemp = new DataTable();
materialtbl.Columns.Add("po_id");
materialtbl.Columns.Add("supplier_id");
materialtbl.Columns.Add("creation_time");
materialtbl.Columns.Add("material_id");
materialtbl.Columns.Add("qty");
materialtbl.Columns.Add("name");
materialtbl.Columns.Add("recieved");
//create po id table
try
{
String query;
if (Reports.sortbysupclient == false)
{
query = "SELECT po_id, creation_time, supplier_id, recieved FROM purchaseorder where approval='Approved' AND creation_time between '" + Reports.startDate + "' and '" + Reports.endDate + "' ";
}
else
{
query = "SELECT po_id, creation_time, supplier_id, recieved FROM purchaseorder where approval='Approved' AND creation_time between '" + Reports.startDate + "' and '" + Reports.endDate + "' AND supplier_id='" + Reports.supclientval + "' ";
}
var dataAdapter = new MySqlDataAdapter(query, DatabaseHandler.MySQLConnectionString);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
dataAdapter.Fill(idtbl);
if (idtbl.Rows.Count == 0)
{
MessageBox.Show("NO Entries available");
this.Close();
return;
}
}
catch (Exception er)
{
MessageBox.Show("Error Occured! ");
Console.WriteLine(er.Message);
return;
}
Console.WriteLine("id tbl rows" + idtbl.Rows.Count);
int noOfRows = idtbl.Rows.Count;
for (int i = 0; i < noOfRows; i++)
{
try
{
String query = "SELECT purchaseorder_item.po_id ,purchaseorder_item.material_id ,purchaseorder_item.qty , raw_material.name FROM purchaseorder_item INNER JOIN raw_material ON purchaseorder_item.material_id = raw_material.material_id where purchaseorder_item.po_id= '" + idtbl.Rows[i][0].ToString() + "' ";
var dataAdapter = new MySqlDataAdapter(query, DatabaseHandler.MySQLConnectionString);
var commandBuilder = new MySqlCommandBuilder(dataAdapter);
dataAdapter.Fill(materialtblTemp);
Console.WriteLine(materialtblTemp.Rows.Count);
if (materialtblTemp.Rows.Count == 0)
{
MessageBox.Show("Error Occured! Please check input details!");
return;
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
}
int noOfRows2 = materialtblTemp.Rows.Count;
for (int i = 0; i < noOfRows2; i++)
{
DataRow rw = materialtbl.NewRow();
rw["po_id"] = materialtblTemp.Rows[i][0];
rw["material_id"] = materialtblTemp.Rows[i][1];
rw["qty"] = materialtblTemp.Rows[i][2];
rw["name"] = materialtblTemp.Rows[i][3];
for (int j = 0; j < noOfRows; j++)
{
if (materialtblTemp.Rows[i][0].ToString() == idtbl.Rows[j][0].ToString())
{
rw["supplier_id"] = idtbl.Rows[j][2];
rw["creation_time"] = idtbl.Rows[j][1];
rw["recieved"] = idtbl.Rows[j][3];
}
}
materialtbl.Rows.Add(rw);
}
int noOfRows3 = materialtbl.Rows.Count;
materialsumtbl.Columns.Add("material_id");
materialsumtbl.Columns.Add("name");
materialsumtbl.Columns.Add("qty");
int noOfRows4 = 0;
for (int i = 0; i < noOfRows3; i++)
{
noOfRows4 = materialsumtbl.Rows.Count;
int count = 0;
for (int j = 0; j < noOfRows4; j++)
{
if (materialtbl.Rows[i][3].ToString() == materialsumtbl.Rows[j][0].ToString())
{
materialsumtbl.Rows[j][2] = Convert.ToDouble(materialtbl.Rows[i][4].ToString()) + Convert.ToDouble(materialsumtbl.Rows[j][2].ToString());
count++;
}
}
if (count == 0)
{
DataRow rw = materialsumtbl.NewRow();
rw["material_id"] = materialtbl.Rows[i][3];
rw["qty"] = materialtbl.Rows[i][4];
rw["name"] = materialtbl.Rows[i][5];
materialsumtbl.Rows.Add(rw);
}
}
if (Reports.summarize == true)
{
CrystalReports.purchaseOrderSummarizedReport materialsumrpt = new CrystalReports.purchaseOrderSummarizedReport();
materialsumrpt.Database.Tables["materialsumtbl"].SetDataSource(materialsumtbl);
porptviewer.ReportSource = null;
porptviewer.ReportSource = materialsumrpt;
}
else
{
CrystalReports.purchaseOrder_rpt po_order_rpt = new CrystalReports.purchaseOrder_rpt();
po_order_rpt.Database.Tables["materialtbl"].SetDataSource(materialtbl);
porptviewer.ReportSource = null;
porptviewer.ReportSource = po_order_rpt;
}
}
//public static DataSet Query(string sql) { // ikke bruk denne
// if (!initialized) return null;
// if (conn == null || conn.State == ConnectionState.Closed) {
// Console.WriteLine("Query: Ingen database er tilkoblet");
// return null;
// }
// MySqlCommand cmd = new MySqlCommand(sql, conn);
// MySqlDataAdapter da = new MySqlDataAdapter(cmd);
// MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
// DataSet qds = new DataSet("result");
// da.Fill(qds, "result");
// Console.WriteLine(sql);
// return qds;
//}
// Legg inn en rad i en angitt tabell
public static bool InsertRow(string tableName, DataRow rowData)
{
if (!initialized || (!usingLocalDatabase && !CheckConnection()))
{
return(false);
}
if (tableName == "booking")
{
DataRow newRow = bookingTable.NewRow();
newRow[0] = rowData[0];
newRow[1] = rowData[1];
newRow[2] = rowData[2];
newRow[3] = rowData[3];
newRow[4] = rowData[4];
bookingTable.Rows.Add(newRow);
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daBooking);
daBooking.Update(ds, "booking");
}
}
else if (tableName == "ansatte")
{
DataRow newRow = employeesTable.NewRow();
newRow[0] = rowData[0];
newRow[1] = rowData[1];
newRow[2] = rowData[2];
newRow[3] = rowData[3];
newRow[4] = rowData[4];
employeesTable.Rows.Add(newRow);
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daEmployees);
daEmployees.Update(ds, "ansatte");
}
}
else if (tableName == "bestillinger")
{
DataRow newRow = ordersTable.NewRow();
newRow[0] = rowData[0];
newRow[1] = rowData[1];
newRow[2] = rowData[2];
newRow[3] = rowData[3];
newRow[4] = rowData[4];
newRow[5] = rowData[5];
newRow[6] = rowData[6];
newRow[7] = rowData[7];
ordersTable.Rows.Add(newRow);
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daOrders);
daOrders.Update(ds, "bestillinger");
}
}
else if (tableName == "rom")
{
DataRow newRow = roomTable.NewRow();
newRow[0] = rowData[0];
newRow[1] = rowData[1];
roomTable.Rows.Add(newRow);
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daRoom);
daRoom.Update(ds, "rom");
}
}
else if (tableName == "romtyper")
{
DataRow newRow = roomTypesTable.NewRow();
newRow[0] = rowData[0];
newRow[1] = rowData[1];
newRow[2] = rowData[2];
roomTypesTable.Rows.Add(newRow);
if (!usingLocalDatabase)
{
MySqlCommandBuilder cb = new MySqlCommandBuilder(daRoomTypes);
daRoomTypes.Update(ds, "romtyper");
}
}
string outputString = "";
for (int i = 0; i < rowData.ItemArray.Length; i++)
{
outputString += rowData[i].ToString() + ", ";
}
Console.WriteLine("INSERT: " + outputString + "INTO " + tableName);
ds.AcceptChanges();
OnUpdate();
return(true);
}
