private void button1_Click(object sender, EventArgs e)
{
try
{
MySqlConnection myConn = new MySqlConnection();
myConn.Host = "192.168.69.8";
myConn.Port = 3306;
myConn.UserId = "cody";
myConn.Password = "******";
myConn.Open();
MySqlCommand myCommand = new MySqlCommand(" SELECT * FROM registration.regUser ;", myConn);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
myDataAdapter.SelectCommand = myCommand;
DataTable dbDataSet = new DataTable();
myDataAdapter.Fill(dbDataSet);
BindingSource bSource = new BindingSource();
bSource.DataSource = dbDataSet;
dataGridView1.DataSource = bSource;
myDataAdapter.Update(dbDataSet);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void BulkLoadColumnOrder()
{
execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
sw.WriteLine(i + ",col3,col2,col1");
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
int count = loader.Load();
Assert.AreEqual(20, count);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2]);
Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
}
public int fjöldi_rada()
{
MySqlConnection connection;
string connectionString = "Server=10.0.105.33;Database=Leikur;Uid=first;Pwd=first;";
connection = new MySqlConnection(connectionString);
connection.Open();
string query = @"SELECT * FROM spilari";
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
MySqlDataReader queryCommandReader = cmd.ExecuteReader();
DataTable dataTable = new DataTable();
dataTable.Load(queryCommandReader);
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds, "SQL Temp Table");
adapter.Dispose();
cmd.Dispose();
return ds.Tables[0].Rows.Count;
}
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 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 Bug19481()
{
execSQL("DROP TABLE Test");
execSQL("CREATE TABLE Test(ID INT NOT NULL AUTO_INCREMENT, " +
"SATELLITEID VARCHAR(3) NOT NULL, ANTENNAID INT, AOS_TIMESTAMP DATETIME NOT NULL, " +
"TEL_TIMESTAMP DATETIME, LOS_TIMESTAMP DATETIME, PRIMARY KEY (ID))");
execSQL("INSERT INTO Test VALUES (NULL,'224','0','2005-07-24 00:00:00'," +
"'2005-07-24 00:02:00','2005-07-24 00:22:00')");
execSQL("INSERT INTO Test VALUES (NULL,'155','24','2005-07-24 03:00:00'," +
"'2005-07-24 03:02:30','2005-07-24 03:20:00')");
execSQL("INSERT INTO Test VALUES (NULL,'094','34','2005-07-24 09:00:00'," +
"'2005-07-24 09:00:30','2005-07-24 09:15:00')");
execSQL("INSERT INTO Test VALUES (NULL,'224','54','2005-07-24 12:00:00'," +
"'2005-07-24 12:01:00','2005-07-24 12:33:00')");
execSQL("INSERT INTO Test VALUES (NULL,'155','25','2005-07-24 15:00:00'," +
"'2005-07-24 15:02:00','2005-07-24 15:22:00')");
execSQL("INSERT INTO Test VALUES (NULL,'094','0','2005-07-24 17:00:00'," +
"'2005-07-24 17:02:12','2005-07-24 17:20:00')");
execSQL("INSERT INTO Test VALUES (NULL,'224','24','2005-07-24 19:00:00'," +
"'2005-07-24 19:02:00','2005-07-24 19:27:00')");
execSQL("INSERT INTO Test VALUES (NULL,'155','34','2005-07-24 21:00:00'," +
"'2005-07-24 21:02:33','2005-07-24 21:22:55')");
execSQL("INSERT INTO Test VALUES (NULL,'094','55','2005-07-24 23:00:00'," +
"'2005-07-24 23:00:45','2005-07-24 23:22:23')");
DateTime date = DateTime.Parse("7/24/2005");
StringBuilder sql = new StringBuilder();
sql.AppendFormat(CultureInfo.InvariantCulture,
@"SELECT ID, ANTENNAID, TEL_TIMESTAMP, LOS_TIMESTAMP FROM Test
WHERE TEL_TIMESTAMP >= '{0}'", date.ToString("u"));
MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), conn);
DataSet dataSet = new DataSet();
da.Fill(dataSet);
}
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 FunctionsReturnStringAndDecimal()
{
execSQL("CREATE TABLE bug52187a (a decimal(5,2) not null)");
execSQL("CREATE TABLE bug52187b (b decimal(5,2) not null)");
execSQL("insert into bug52187a values (1.25)");
execSQL("insert into bug52187b values (5.99)");
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo c = new CultureInfo("pt-PT");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
string connStr = GetConnectionString(true) + ";functions return string=true";
try
{
using (MySqlConnection con = new MySqlConnection(connStr))
{
con.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"select *,(select b from bug52187b) as field_b from bug52187a", con);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1.25, dt.Rows[0][0]);
Assert.AreEqual(5.99, dt.Rows[0][1]);
}
}
finally
{
Thread.CurrentThread.CurrentCulture = curCulture;
Thread.CurrentThread.CurrentUICulture = curUICulture;
}
}
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 ShowCreateTable()
{
MySqlDataAdapter da = new MySqlDataAdapter("SHOW CREATE TABLE Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Columns.Count);
}
public void ShowCreateTable()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SHOW CREATE TABLE Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(2, dt.Columns.Count);
}
internal static void fill(string queryString, DataTable toReturn)
{
using (var conn = new MySqlConnection(connString)) {
conn.Open();
conn.ChangeDatabase(Server.MySQLDatabaseName);
using (MySqlDataAdapter da = new MySqlDataAdapter(queryString, conn)) {
da.Fill(toReturn);
}
conn.Close();
}
}
public void AdapterConcurrentException()
{
execSQL(
"CREATE TABLE T (" +
"id_auto int(11) NOT NULL AUTO_INCREMENT," +
"field varchar(50) DEFAULT NULL," +
"PRIMARY KEY (id_auto))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM T", conn);
da.InsertCommand = conn.CreateCommand();
da.InsertCommand.CommandText = @"INSERT INTO T(field) VALUES (@p_field);
SELECT * FROM T WHERE id_auto=@@IDENTITY";
da.InsertCommand.Parameters.Add("@p_field", MySqlDbType.VarChar, 50, "field");
da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
da.DeleteCommand = conn.CreateCommand();
da.DeleteCommand.CommandText = "DELETE FROM T WHERE id_auto=@id_auto";
da.DeleteCommand.Parameters.Add("@id_auto", MySqlDbType.Int32, 4, "id_auto");
DataSet ds = new DataSet();
da.Fill(ds, "T");
DataTable table = ds.Tables["T"];
DataRow r = table.NewRow();
r["field"] = "row";
table.Rows.Add(r);
da.Update(table);
Assert.AreEqual(r.RowState, DataRowState.Unchanged);
table.Rows[0].Delete();
r = table.NewRow();
r["field"] = "row2";
table.Rows.Add(r);
da.Update(table); // here was concurrencyviolation
da.Fill(ds);
Assert.AreEqual(ds.Tables["T"].Rows.Count, 1);
Assert.AreEqual(ds.Tables["T"].Rows[0]["field"], "row2");
}
public void CommentsInSQL()
{
string sql = "INSERT INTO Test /* my table */ VALUES (1 /* this is the id */, 'Test' );" +
"/* These next inserts are just for testing \r\n" +
" comments */\r\n" +
"INSERT INTO \r\n" +
" # This table is bogus\r\n" +
"Test VALUES (2, 'Test2')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable table = new DataTable();
da.Fill(table);
Assert.AreEqual(1, table.Rows[0]["id"]);
Assert.AreEqual("Test", table.Rows[0]["name"]);
Assert.AreEqual(2, table.Rows.Count);
Assert.AreEqual(2, table.Rows[1]["id"]);
Assert.AreEqual("Test2", table.Rows[1]["name"]);
}
public DataTable BuscarTipoPedido(string pedido)
{
DataTable Datos = new DataTable();
BDConexion cn = new BDConexion();
MySqlDataAdapter datosObtenidos = new MySqlDataAdapter();
conexionBD = cn.ObtenerConexion();
string consultaListaPedidos = "SELECT lp.id_ListaPedido as CODIGO, t.tipo as 'TIPO DE PEDIDO', p.numeroPedido as 'NUMERO DE PEDIDO', u.usuario as 'PERSONAL DE ATENCION', lp.pagoTotal as 'TOTAL A CANCELAR', lp.pendiente as 'PENDIENTE' " +
"FROM lista_pedido lp, tipo_pedido t, pedido p, usuario u " +
"WHERE lp.id_TipoPedido = t.id_TipoPedido " +
"AND lp.id_Pedido = p.id_Pedido " +
"AND lp.id_Usuario = u.id_Usuario " +
"AND t.tipo = '" + pedido + "'";
/*[CONSULTA]
* SELECT lp.id_ListaPedido as CODIGO, t.tipo as 'TIPO DE PEDIDO', p.numeroPedido as 'NUMERO DE PEDIDO', u.usuario as 'PERSONAL DE ATENCION', lp.pendiente as 'PENDIENTE'
* FROM lista_pedido lp, tipo_pedido t, pedido p, usuario u
* WHERE lp.id_TipoPedido = t.id_TipoPedido
* AND lp.id_Pedido = p.id_Pedido
* AND lp.id_Usuario = u.id_Usuario
* AND t.tipo = 'Sucursal';
* */
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conexionBD;
cmd.CommandText = consultaListaPedidos;
datosObtenidos.SelectCommand = cmd;
datosObtenidos.Fill(Datos);
}
catch (Exception ex)
{
MessageBox.Show("Error en la consulta a la tabla 'LISTA DE PEDIDOS' " + ex.Message);
}
return(Datos);
}
/// <summary>
/// GetUserList
/// </summary>
/// <param name="TenantID"></param>
public List <User> GetUserList(int TenantID, int UserID)
{
DataSet ds = new DataSet();
MySqlCommand cmd = new MySqlCommand();
List <User> users = new List <User>();
try
{
conn = Db.Connection;
MySqlCommand cmd1 = new MySqlCommand("SP_GetUserFullName", conn);
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@Tenant_ID", TenantID);
cmd1.Parameters.AddWithValue("@User_ID", UserID);
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd1;
da.Fill(ds);
if (ds != null && ds.Tables[0] != null)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
User user = new User();
user.UserID = Convert.ToInt32(ds.Tables[0].Rows[i]["UserID"]);
user.FullName = Convert.ToString(ds.Tables[0].Rows[i]["FullName"]);
user.ReporteeID = Convert.ToInt32(ds.Tables[0].Rows[i]["ReporteeID"]);
user.RoleID = Convert.ToInt32(ds.Tables[0].Rows[i]["RoleID"]);
user.RoleName = Convert.ToString(ds.Tables[0].Rows[i]["RoleName"]);
users.Add(user);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(users);
}
private void Form11_Load(object sender, EventArgs e)
{
try
{
dbconnection.Open();
string query = "select * from cars";
MySqlDataAdapter da = new MySqlDataAdapter(query, dbconnection);
DataTable dt = new DataTable();
da.Fill(dt);
comCarNumber.DataSource = dt;
comCarNumber.DisplayMember = dt.Columns["Car_Number"].ToString();
comCarNumber.ValueMember = dt.Columns["Car_ID"].ToString();
comCarNumber.Text = row[1].ToString();
query = "select * from expense_type";
da = new MySqlDataAdapter(query, dbconnection);
dt = new DataTable();
da.Fill(dt);
comType.DataSource = dt;
comType.DisplayMember = dt.Columns["Type"].ToString();
comType.ValueMember = dt.Columns["ID"].ToString();
comType.Text = row[2].ToString();
txtCost.Text = row[3].ToString();
dateTimePicker1.Text = row[4].ToString();
txtNote.Text = row[5].ToString();
load = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dbconnection.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string CN = ConfigurationManager.ConnectionStrings["DBMS"].ConnectionString;
using (MySqlConnection cn = new MySqlConnection(CN))
{
cn.Open();
string datet = DateTime.Now.ToString("dd-MM-yyyy");
BillDate.Text = datet;
string s = "Select * From Matches where Active = '0' order by DateTime DESC";
MySqlCommand cmd = new MySqlCommand(s, cn);
MySqlDataAdapter adp = new MySqlDataAdapter(cmd);
dt = new DataTable();
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
dt.AcceptChanges();
foreach (DataRow row in dt.Rows)
{
string rowDate = DateTime.Parse(row["DateTime"].ToString()).Date.ToString("dd-MM-yyyy");
if (datet != rowDate)
{
row.Delete();
}
}
dt.AcceptChanges();
}
else
{
emptyLedgerTable = true;
}
}
}
}
public DataTable RefreshAllQuestionsTable(string QRef, string QLevel, string QCategory, string QSubcategory, string QAtextContain, string QAlastFirst, string QAwhereToSearch)
{
MySqlDataAdapter SDA = new MySqlDataAdapter();
System.Data.DataTable dbDataSet = new System.Data.DataTable();
BindingSource bSource = new BindingSource();
string QuestionTable = DatabasesSettings.Default.QuestionDB_setTable1;
CanConnectStacks = false;
ConnectQAStacksDB();
if (mySqlQAStacksDB.State == ConnectionState.Open)
{
string Query;
Query = ("SELECT " +
" gq.QuestionID AS `ID`, qs.StackID AS `Stack`, " +
"gq.Question AS `Question`, "
+ "gq.Answer1 AS `Answer A`, " + "gq.Answer2 AS `Answer B`, "
+ "gq.Answer3 AS `Answer C`, " + "gq.Answer4 AS `Answer D`, " +
"gq.CorrectAnswer AS `Correct`, " +
" gq.TimesAnswered AS `Used` " +
"FROM " + QuestionTable + " gq " + " left join " +
DatabasesSettings.Default.QuestionDB_setTable5 +
" qs on gq.QuestionID=qs.QuestionID " +
"WHERE 1 " + " order by ID asc ");
MySqlCommand COMM;
COMM = new MySqlCommand(Query, mySqlQAStacksDB);
SDA.SelectCommand = COMM;
SDA.Fill(dbDataSet);
SDA.Dispose();
CanConnectStacks = true;
DisconnectDB(mySqlQAStacksDB);
return(dbDataSet);
}
else
{
return(null);
}
}
private void ReturnBookForm_Load(object sender, EventArgs e)
{
conn = new MySqlConnection(connStr);
try
{
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM member", conn);
MySqlDataAdapter adapter1 = new MySqlDataAdapter("SELECT * FROM book", conn);
DataTable dt1 = new DataTable();
DataTable dt = new DataTable();
adapter.Fill(dt);
adapter1.Fill(dt1);
dataGridView1.DataSource = dt;
dataGridView2.DataSource = dt1;
/*
* for(int i = 0; i < dt.Rows.Count; i++)
* {
* DataRow dr = dt.Rows[i];
* ListViewItem listitem = new ListViewItem(dr["ISBN"].ToString());
* listitem.SubItems.Add(dr["Title"].ToString());
* listitem.SubItems.Add(dr["Writer"].ToString());
* listitem.SubItems.Add(dr["Price"].ToString());
* listitem.SubItems.Add(dr["BookReply"].ToString());
* listitem.SubItems.Add(dr["BookLocation"].ToString());
* listitem.SubItems.Add(dr["EnterWith"].ToString());
* listitem.SubItems.Add(dr["BookCondition"].ToString());
* listitem.SubItems.Add(dr["Publisher"].ToString());
*
* }*/
conn.Close();
}
catch (Exception ex)
{
conn.Close();
MessageBox.Show("연결실패" + ex.Message);
Application.OpenForms["MemberManageForm"].Close();
}
}
public DataSet SelectUserByID(UserClass oClass)
{
string strSql;
strSql = "SELECT * " +
"FROM " + TABLE_NAME + " " +
"WHERE " + COLUMN_USER_ID + " = '" + oClass.ID + "' ";
DataSet myDataset = new DataSet();
try
{
if (myConnection.OpenConnection())
{
myCon = myConnection.ConnectionObject;
myDataAdapter = new MySqlDataAdapter(strSql, myCon);
myDataAdapter.Fill(myDataset);
return(myDataset);
}
else
{
return(null);
}
}
catch
{
return(null);
}
finally
{
if (myCon.State == ConnectionState.Open)
{
myCon.Close();
}
myCon.Dispose();
myDataAdapter = null;
}
}
private void btn_search_Click(object sender, EventArgs e)
{
//searching account number
//checking if account number existing
if (txt_saccno.Text == "")
{
MessageBox.Show("Incomplete field", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
//display accounts
String sqlconstring = "Data Source = LOCALHOST; Initial Catalog = it_2d; username = root; password = ''";
MySqlConnection sqlconnect = new MySqlConnection(sqlconstring);
MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter();
DataSet DS = new DataSet();
sqlconnect.Open();
MySqlCommand sqlcommand = new MySqlCommand();
//checking if the user account actually borrowed
//display data from tbl_borrow
sqlcommand.CommandText = "SELECT ``b_no` as 'Accession No.', `b_title` as 'Title', `b_author` as 'Author', `u_un` as 'Account No.', `d_borrowed` as 'Date Borrowed' FROM `tbl_borrow` WHERE `b_no` = '" + txt_saccno.Text + "'";
sqlcommand.CommandType = CommandType.Text;
sqlcommand.Connection = sqlconnect;
sqlcommand.ExecuteNonQuery();
sqlDataAdapter.SelectCommand = sqlcommand;
sqlDataAdapter.Fill(DS, "KAIBIGAN");
dataGridView1.DataSource = DS;
dataGridView1.DataMember = "KAIBIGAN";
sqlconnect.Close();
}
}
private void Compare_Load(object sender, EventArgs e)
{
dateTimeNow.Text = DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss");
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.WindowState = System.Windows.Forms.FormWindowState.Maximized;
cmbLL.Enabled = false;
btnCompare.Enabled = false;
label3.Visible = false;
label16.Visible = false;
gbSummary.Visible = false;
groupBox4.Visible = false;
string queryWODropDown = "SELECT wo_PTSN FROM tbl_wo ";
try
{
connection.Open();
using (MySqlDataAdapter adpt = new MySqlDataAdapter(queryWODropDown, connection))
{
DataTable dset = new DataTable();
adpt.Fill(dset);
for (int i = 0; i < dset.Rows.Count; i++)
{
cmbWOPtsn.Items.Add(dset.Rows[i][0]);
cmbWOPtsn.ValueMember = dset.Rows[i][0].ToString();
}
}
connection.Close();
}
catch (Exception ex)
{
// tampilkan pesan error
MessageBox.Show(ex.Message);
}
}
private void Form4_Load(object sender, EventArgs e)
{
ViewProducts.ReadOnly = true;
string connString = "Server=simplemodes.cyqmaqudzup4.us-east-2.rds.amazonaws.com;port=3307;Database=SimpleModes;user=SimpleModes;password=simplemodes";
MySqlConnection conn = new MySqlConnection(connString);
MySqlCommand command = conn.CreateCommand();
try
{
// load the data from products table into ViewProduct
string query = "Select * from product where quantity > 0";
using (MySqlDataAdapter adpt = new MySqlDataAdapter(query, conn))
{
DataSet dSet = new DataSet();
adpt.Fill(dSet);
ViewProducts.DataSource = dSet.Tables[0];
}
// load the customer cart from table into ViewCart
query = "select * from customer_cart";
using (MySqlDataAdapter adpt = new MySqlDataAdapter(query, conn))
{
DataSet dset = new DataSet();
adpt.Fill(dset);
ViewCart.DataSource = dset.Tables[0];
}
}
catch
{
MessageBox.Show("could not connect to DB.");
}
}
void FillComboBox()
{
MySqlConnection conn = new MySqlConnection(@"datasource=127.0.0.1;port=3306;SslMode=none;username=root;password=;database=wicrama;");
MySqlDataAdapter sda = new MySqlDataAdapter("select DISTINCT model from items", conn);
DataSet dt = new DataSet();
sda.Fill(dt);
comboBox1.DataSource = dt.Tables[0];
comboBox1.DisplayMember = "model";
comboBox1.ValueMember = "model";
MySqlDataAdapter sda1 = new MySqlDataAdapter("select DISTINCT part from items", conn);
DataSet dt1 = new DataSet();
sda1.Fill(dt1);
comboBox2.DataSource = dt1.Tables[0];
comboBox2.DisplayMember = "part";
comboBox2.ValueMember = "part";
conn.Close();
}
//Se a tabela estiver vazia ele desativa a dataGridView
public void check()
{
DataTable table = new DataTable();
MySqlDataAdapter adapter = new MySqlDataAdapter();
MySqlCommand command = new MySqlCommand("SELECT * FROM area", db.getConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
if (table.Rows.Count == 0)
{
dtareas.Enabled = false;
}
else
{
dtareas.Enabled = true;
dataview();
}
}
private float totalSum()
{
MySqlConnection conn = OpenConnection();
string sql = "Select Summa from books;";
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);
adapter.Fill(ds);
int n = ds.Tables[0].Rows.Count;
float data = 0;
for (int i = 0; i < n - 1; i++)
{
data += Convert.ToInt64(toString(ds.Tables[0].Rows[i].ItemArray[0].ToString()));
}
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
return(data);
}
private DataSet getOrder(String pSeq)
{
DataSet ds = null;
try
{
ds = new DataSet();
String sql = "";
sql = sql + " SELECT O.SEQ, O.PSEQ, O.PSSEQ, O.STEP, O.ETC, P.CODE, P.TITLE, P.TITLE2, P.IMAGE, PO.TITLE AS OPTION_TITLE ";
sql = sql + " FROM TB_ORDER O ";
sql = sql + " LEFT JOIN TB_PRODUCT P ON O.PSEQ = P.SEQ ";
sql = sql + " LEFT JOIN TB_PRODUCT_OPTION PO ON O.PSSEQ = PO.SSEQ ";
sql = sql + " WHERE O.SEQ = '" + pSeq + "' ";
MySqlDataAdapter adpt = new MySqlDataAdapter(sql, conn);
adpt.Fill(ds, "TB_ORDER");
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return(ds);
}
public void ConsultarRegistroUsuario(int id)
{
var dt = new DataTable();
var conexion = new MySqlConnection("Server=35.238.58.16; database=DB_PersonalProjects; user id=eduardo; password=12345;SslMode=none;");
var cmd = new MySqlCommand("select *from Informacion where id_usuario =" + id + "", conexion);
conexion.Open();
var da = new MySqlDataAdapter(cmd);
da.Fill(dt);
conexion.Close();
Informacion = new List <Datos>();
Datos datos = new Datos();
datos.id_usuario = int.Parse(dt.Rows[0]["id_usuario"].ToString());
datos.Nombre = dt.Rows[0]["nombre"].ToString();
datos.Direccion = dt.Rows[0]["direccion"].ToString();
datos.Telefono = dt.Rows[0]["telefono"].ToString();
datos.Correo = dt.Rows[0]["correo"].ToString();
datos.Edad = int.Parse(dt.Rows[0]["edad"].ToString());
Informacion.Add(datos);
}
private void button6_Click(object sender, EventArgs e)
{
MySqlCommand command = new MySqlCommand("SELECT* FROM TIPO_PRODUCTOS WHERE ID=#@id", conexion);
command.Parameters.Add("@id", MySqlDbType.Int32).Value = textBox1.Text;
MySqlDataAdapter dataAdapter = new MySqlDataAdapter(command);
DataTable reporte = new DataTable();
dataAdapter.Fill(reporte);
if (reporte.Rows.Count <= 0)
{
MessageBox.Show("No existen los datos ingresados");
}
else
{
textBox1.Text = reporte.Rows[0][0].ToString();
txtNombre.Text = reporte.Rows[0][1].ToString();
byte[] img = (byte[])reporte.Rows[0][2];
MemoryStream ms = new MemoryStream(img);
pictureBox1.Image = Image.FromStream(ms);
}
}
public static DataSet ExecuteDataSet(string sqlstr, params object[] commandParameters)
{
using (MySqlConnection conn = new MySqlConnection(ConnStr))
{
try
{
MySqlCommand cmd = conn.CreateCommand();
PrepareCommand(cmd, conn, null, CommandType.Text, sqlstr, commandParameters);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
conn.Close();
return(ds);
}
catch (Exception e)
{
conn.Dispose();
throw e;
}
}
}
private void Form32_Load(object sender, EventArgs e)
{
con = new MySqlConnection("server=localhost;username=root;pwd=niku2912;database=canteen");
con.Open();
// cmd = new MySqlCommand("select * from purchase_new", con);
da = new MySqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
// da.Fill(ds);
cmd = new MySqlCommand("select packing_product.prod_code ,packing_product.prod_name , packing_product.expiry_date ,stock.total_qty from stock,packing_product where stock.prod_code=packing_product.prod_code and stock.total_qty<10", con);
da = new MySqlDataAdapter(cmd);
da.Fill(ds);
CrystalReport9 myreport = new CrystalReport9();
myreport.SetDataSource(ds.Tables[0]);
crystalReportViewer1.ReportSource = myreport;
crystalReportViewer1.Show();
}
public DataTable ConsultarBitacora()
{
DataTable dtDetalle = new DataTable();
try
{
string sSQlqry = "select b.id_detalle_bit, b.id_receta, r.nombre, date(b.fecha), count(*) as Cantidad " +
"from bitacora_rec b " +
"join recetas r on r.id_receta = b.id_receta " +
"where date(b.fecha) = date(Now()) " +
"group by date(b.fecha), r.nombre;";
MySqlCommand command = new MySqlCommand(sSQlqry, xConnection);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
adapter.Fill(dtDetalle);
}
catch (Exception ex)
{
sLastError = ex.ToString();
}
return(dtDetalle);
}
public ShowUsers()
{
InitializeComponent();
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter();
DB db = new DB();
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dataGridView1.AllowUserToAddRows = false;
MySqlCommand command = new MySqlCommand("select * from users", db.getConnection());
db.openConnection();
adapter.SelectCommand = command;
adapter.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
db.closeConnection();
}
void ShowMarkers(string queryFill, string queryShow)
{
DataTable tap = new DataTable();
new MySqlDataAdapter(queryFill, con).Fill(tap);
huizen = tap.Rows.OfType <DataRow>().Select(dr => dr.Field <string>("Wijk")).ToList();
foreach (var huis in huizen)
{
MySqlDataAdapter sda = new MySqlDataAdapter(queryShow, con);
DataTable dt = new DataTable();
sda.Fill(dt);
foreach (DataRow i in dt.Rows)
{
string MyDoubleLong = i[1].ToString();
double Long = double.Parse(MyDoubleLong);
string MyDoubleLat = i[2].ToString();
double Lat = double.Parse(MyDoubleLat);
CreateMarker(i[0].ToString(), Long, Lat, i[3].ToString(), i[4].ToString(), i[5].ToString());
}
}
}
public DataSet executeQuery(MySqlCommand sql)
{
try
{
sql.Connection = abrirConexao();
sql.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter(sql);
DataSet ds = new DataSet();
da.Fill(ds);
fecharConexao(sql.Connection);
return(ds);
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
/// <summary>
/// 执行SQL语句 返回一个数据集
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns></returns>
public DataTable GetTable(string sql)
{
MySqlConnection OleConnection = new MySqlConnection(this.szOleConn);
OleConnection.Open();
MySqlDataAdapter OleAdapter = new MySqlDataAdapter(sql, this.szOleConn);
DataSet myDataSet = new DataSet();
try
{
OleAdapter.Fill(myDataSet);
return(myDataSet.Tables[0]);
}
catch (Exception er)
{
throw er;
}
finally
{
OleConnection.Close();
}
}
public DataTable ExecuteDataTable(string safeSql)
{
using (MySqlConnection Connection = new MySqlConnection(connectionString))
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
DataSet ds = new DataSet();
MySqlCommand cmd = new MySqlCommand(safeSql, Connection);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
try
{
da.Fill(ds);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return(ds.Tables[0]);
}
}
private void Mysqlinformation_Load(object sender, EventArgs e)
{
conn = new MySqlConnection();
conn.ConnectionString = Global.MyConnectionString;
conn.Open();
string str = "select * from mydatabase";
myadp = new MySqlDataAdapter(str, conn);
myds = new DataSet();
myadp.Fill(myds, "mydatabase");
textBox_user.Text = myds.Tables["mydatabase"].Rows[0]["user"].ToString();
textBox_password.Text = myds.Tables["mydatabase"].Rows[0]["password"].ToString();
textBox_dbName.Text = myds.Tables["mydatabase"].Rows[0]["dbName"].ToString();
textBox_host.Text = myds.Tables["mydatabase"].Rows[0]["host"].ToString();
textBox_port.Text = myds.Tables["mydatabase"].Rows[0]["port"].ToString();
//User = myds.Tables["mydatabase"].Rows[0]["user"].ToString();
//Password = myds.Tables["mydatabase"].Rows[0]["password"].ToString();
//DBName = myds.Tables["mydatabase"].Rows[0]["dbName"].ToString();
//Host = myds.Tables["mydatabase"].Rows[0]["host"].ToString();
//Port = myds.Tables["mydatabase"].Rows[0]["port"].ToString();
}
public static TransportModel getTransportById(int Id)
{
db.Open();
var adapter = new MySqlDataAdapter();
var command = new MySqlCommand("SELECT * FROM `transport` WHERE `Id` = @Id;", db);
var table = new DataTable();
command.Parameters.Add("@Id", MySqlDbType.Int32).Value = Id;
adapter.SelectCommand = command;
adapter.Fill(table);
db.Close();
return new TransportModel()
{
Id = Id,
Type = (string)table.Rows[0]["Type"],
Consumption = (int)table.Rows[0]["Consumption"]
};
}
private void showSup()
{
dgvRet.DataSource = null;
string sh = "SELECT * FROM po_return WHERE del_id IN (SELECT po_del_id FROM po_del WHERE sup_id = '" + user_id + "')";
conn.Open();
MySqlCommand comm = new MySqlCommand(sh, conn);
MySqlDataAdapter adp = new MySqlDataAdapter(comm);
comm.ExecuteNonQuery();
conn.Close();
DataTable dt = new DataTable();
adp.Fill(dt);
dgvRet.DataSource = dt;
offSet = 0;
dgvRet.Columns[0].Visible = false;
dgvRet.Columns[1].Visible = false;
dgvRet.Columns[2].HeaderText = "Return Date";
dgvRet.Columns[3].Visible = false;
dgvRet.Columns[4].HeaderText = "Date Receive";
dgvRet.Columns[5].Visible = false;
}
public DataTable Listar()
{
try
{
con.AbrirConexao();
sql = new MySqlCommand("SELECT prod.id_produto, prod.codigo, forn.nome, prod.nome, prod.descricao, unid.unidade, prod.valor " +
"FROM produtos prod " +
"INNER JOIN fornecedores forn on (forn.id_fornecedor = prod.id_fornecedor) " +
"INNER JOIN unidades unid on (unid.id_unidade = prod.id_unidade) " +
"order by prod.nome asc", con.con);
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = sql;
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
catch (Exception ex)
{
throw ex;
//MessageBox.Show("Erro ao Listar " + ex.Message);
con.FecharConexao();
}
}
public string[] ListarNumeroGrupo(string curso, string turno, out DataTable dataTable)
{
string query = "SET lc_time_names=es_ES;SELECT CONCAT('Numero: ',serial,' - ',monthname(fecha_inicio),' ',year(fecha_inicio),' - ',monthname(fecha_fin),' ',year(fecha_fin)), id_grupo from grupo WHERE nombre_curso = '" + curso + "' AND fecha_inicio<=curdate() AND curdate()<fecha_fin AND turno='" + turno + "'";
MySqlDataAdapter datosConsulta = objetoConexion.consultarDatos(query);
dataTable = new DataTable();
datosConsulta.Fill(dataTable);
int filas = dataTable.Rows.Count;
string[] periodo = new string[filas];
int i = 0;
if (filas > 0)
{
foreach (DataRow row in dataTable.Rows)
{
periodo[i] = row[0].ToString();
i++;
}
}
return(periodo);
}
public DataRow GetReportConfigInfo(string reportCode)
{
DataTable dataTable = new DataTable();
try
{
string query = "select field_caps, sql_from,detail_fieldsets from " + ConfigManager.GetReportTableName() + " where upper(report_code) ='" + reportCode.ToUpper() + "'";
using (MySqlConnection dbConnection = new MySqlConnection(ConnectionString))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter())
{
adapter.SelectCommand = new MySqlCommand(query, dbConnection);
adapter.Fill(dataTable);
return(dataTable.Rows[0]);
}
}
}
catch (Exception ex)
{
throw new Exception("From GetDataTable method:" + ex.Message);
}
return(null);
}
public void AmbiguousColumns()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE t1 (id INT)");
execSQL("CREATE TABLE t2 (id1 INT, id INT)");
execSQL(@"CREATE PROCEDURE spTest() BEGIN SELECT * FROM t1;
SELECT id FROM t1 JOIN t2 on t1.id=t2.id;
SELECT * FROM t2; END");
MySqlCommand cmd = new MySqlCommand("spTest", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
Assert.Fail("The above should have thrown an exception");
}
catch (Exception)
{
}
}
public void RespectBinaryFlags()
{
if (Version.Major >= 5 && Version.Minor >= 5) return;
string connStr = GetConnectionString(true) + ";respect binary flags=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT CONCAT('Trädgårdsvägen', 1)", c);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(dt.Rows[0][0] is byte[]);
}
connStr = GetConnectionString(true) + ";respect binary flags=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter(
"SELECT CONCAT('Trädgårdsvägen', 1)", c);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.IsTrue(dt.Rows[0][0] is string);
Assert.AreEqual("Trädgårdsvägen1", dt.Rows[0][0]);
}
}
public void UTF8AsColumnNames()
{
string connStr = GetConnectionString(true) + ";charset=utf8;pooling=false";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("select now() as 'Numéro'", c);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual("Numéro", dt.Columns[0].ColumnName);
MySqlCommand cmd = new MySqlCommand("SELECT NOW() AS 'Numéro'", c);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
int ord = reader.GetOrdinal("Numéro");
Assert.AreEqual(0, ord);
}
}
}
public void BlobAsUtf8()
{
execSQL(@"CREATE TABLE Test(include_blob BLOB, include_tinyblob TINYBLOB,
include_longblob LONGBLOB, exclude_tinyblob TINYBLOB, exclude_blob BLOB,
exclude_longblob LONGBLOB)");
byte[] utf8_bytes = new byte[4] { 0xf0, 0x90, 0x80, 0x80 };
Encoding utf8 = Encoding.GetEncoding("UTF-8");
string utf8_string = utf8.GetString(utf8_bytes, 0, utf8_bytes.Length);
// insert our UTF-8 bytes into the table
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?p1, ?p2, ?p3, ?p4, ?p5, ?p5)", conn);
cmd.Parameters.AddWithValue("?p1", utf8_bytes);
cmd.Parameters.AddWithValue("?p2", utf8_bytes);
cmd.Parameters.AddWithValue("?p3", utf8_bytes);
cmd.Parameters.AddWithValue("?p4", utf8_bytes);
cmd.Parameters.AddWithValue("?p5", utf8_bytes);
cmd.Parameters.AddWithValue("?p6", utf8_bytes);
cmd.ExecuteNonQuery();
// now check that the on/off is working
string connStr = GetConnectionString(true) + ";Treat Blobs As UTF8=yes;BlobAsUTF8IncludePattern=.*";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
Assert.AreEqual(typeof(string), col.DataType);
string s = (string)dt.Rows[0][0];
byte[] b = utf8.GetBytes(s);
Assert.AreEqual(utf8_string, dt.Rows[0][col.Ordinal].ToString());
}
}
// now check that exclusion works
connStr = GetConnectionString(true) + ";Treat Blobs As UTF8=yes;BlobAsUTF8ExcludePattern=exclude.*";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.StartsWith("exclude"))
Assert.AreEqual(typeof(byte[]), col.DataType);
else
{
Assert.AreEqual(typeof(string), col.DataType);
Assert.AreEqual(utf8_string, dt.Rows[0][col.Ordinal].ToString());
}
}
}
// now check that inclusion works
connStr = GetConnectionString(true) + ";Treat Blobs As UTF8=yes;BlobAsUTF8IncludePattern=include.*";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataColumn col in dt.Columns)
{
if (col.ColumnName.StartsWith("include"))
{
Assert.AreEqual(typeof(string), col.DataType);
Assert.AreEqual(utf8_string, dt.Rows[0][col.Ordinal].ToString());
}
else
Assert.AreEqual(typeof(byte[]), col.DataType);
}
}
}
public void InsertingUnsignedTinyInt()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL(@"CREATE TABLE Test(id TINYINT UNSIGNED NOT NULL,
id2 INT UNSIGNED, id3 TINYINT UNSIGNED, id4 INT UNSIGNED NOT NULL)");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?id2, ?id3, ?id4)", conn);
cmd.Parameters.Add("?id", MySqlDbType.UByte);
cmd.Parameters.Add("?id2", MySqlDbType.UByte);
cmd.Parameters.Add("?id3", MySqlDbType.UByte);
cmd.Parameters.Add("?id4", MySqlDbType.UByte);
cmd.Prepare();
cmd.Parameters[0].Value = 127;
cmd.Parameters[1].Value = 1;
cmd.Parameters[2].Value = 2;
cmd.Parameters[3].Value = 3;
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(127, dt.Rows[0][0]);
Assert.AreEqual(1, dt.Rows[0][1]);
Assert.AreEqual(2, dt.Rows[0][2]);
Assert.AreEqual(3, dt.Rows[0][3]);
}
public void ParameterLengths()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id int, name VARCHAR(255))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?name)", conn);
cmd.Parameters.Add("?id", MySqlDbType.Int32);
cmd.Parameters.Add("?name", MySqlDbType.VarChar);
cmd.Parameters[1].Size = 255;
cmd.Prepare();
cmd.Parameters[0].Value = 1;
cmd.Parameters[1].Value = "short string";
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(1, dt.Rows[0]["id"]);
Assert.AreEqual("short string", dt.Rows[0]["name"]);
}
public void UsingParametersTwice()
{
if (Version < new Version(4, 1)) return;
execSQL(@"CREATE TABLE IF NOT EXISTS Test (input TEXT NOT NULL,
UNIQUE (input(100)), state INT NOT NULL, score INT NOT NULL)");
MySqlCommand cmd = new MySqlCommand(@"Insert into Test (input,
state, score) VALUES (?input, ?st, ?sc) ON DUPLICATE KEY
UPDATE state=state|?st;", conn);
cmd.Parameters.Add(new MySqlParameter("?input", ""));
cmd.Parameters.Add(new MySqlParameter("?st", Convert.ToInt32(0)));
cmd.Parameters.Add(new MySqlParameter("?sc", Convert.ToInt32(0)));
cmd.Prepare();
cmd.Parameters["?input"].Value = "test";
cmd.Parameters["?st"].Value = 1;
cmd.Parameters["?sc"].Value = 42;
int result = cmd.ExecuteNonQuery();
Assert.AreEqual(1, result);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("test", dt.Rows[0]["input"]);
Assert.AreEqual(1, dt.Rows[0]["state"]);
Assert.AreEqual(42, dt.Rows[0]["score"]);
}
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();
}
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"]);
}
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);
}
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"]);
}
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 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 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();
}
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"]);
}
public void ReturningEmptyResultSet()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE test1 (id int AUTO_INCREMENT NOT NULL, " +
"Name VARCHAR(100) NOT NULL, PRIMARY KEY(id))");
execSQL("CREATE TABLE test2 (id int AUTO_INCREMENT NOT NULL, " +
"id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY(id))");
execSQL("INSERT INTO test1 (Id, Name) VALUES (1, 'Item1')");
execSQL("INSERT INTO test1 (Id, Name) VALUES (2, 'Item2')");
execSQL("INSERT INTO test2 (Id, Id1, Id2) VALUES (1, 1, 1)");
execSQL("INSERT INTO test2 (Id, Id1, Id2) VALUES (2, 2, 1)");
execSQL("CREATE PROCEDURE spTest(Name VARCHAR(100), OUT Table1Id INT) " +
"BEGIN SELECT t1.Id INTO Table1Id FROM test1 t1 WHERE t1.Name LIKE Name; " +
"SELECT t3.Id2 FROM test2 t3 WHERE t3.Id1 = Table1Id; END");
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spTest";
cmd.Parameters.AddWithValue("?Name", "Item3");
cmd.Parameters.Add("?Table1Id", MySqlDbType.Int32);
cmd.Parameters["?Table1Id"].Direction = ParameterDirection.Output;
DataSet ds = new DataSet();
if (prepare) cmd.Prepare();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
try
{
da.Fill(ds);
}
catch (MySqlException)
{
// on 5.1 this throws an exception that no rows were returned.
}
}
