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 Bug51610()
{
MySqlCommand cmd = new MySqlCommand("SELECT 'ABC', (0/`QOH`) from (SELECT 1 as `QOH`) `d1`", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual("ABC", reader.GetString(0));
Assert.AreEqual(0, reader.GetInt32(1));
}
cmd.CommandText = "SELECT 'ABC', (0-`QOH`) from (SELECT 1 as `QOH`) `d1`";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual("ABC", reader.GetString(0));
Assert.AreEqual(-1, reader.GetInt32(1));
}
cmd.CommandText = "SELECT 'test 2010-03-04 @ 10:14'";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual("test 2010-03-04 @ 10:14", reader.GetString(0));
}
}
public void AggregateTypesTest()
{
execSQL("CREATE TABLE foo (abigint bigint, aint int)");
execSQL("INSERT INTO foo VALUES (1, 2)");
execSQL("INSERT INTO foo VALUES (2, 3)");
execSQL("INSERT INTO foo VALUES (3, 4)");
execSQL("INSERT INTO foo VALUES (3, 5)");
// Try a normal query
string NORMAL_QRY = "SELECT abigint, aint FROM foo WHERE abigint = {0}";
string qry = String.Format(NORMAL_QRY, 3);
MySqlCommand cmd = new MySqlCommand(qry, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
reader.GetInt64(0);
reader.GetInt32(1); // <--- aint... this succeeds
}
}
cmd.CommandText = "SELECT abigint, max(aint) FROM foo GROUP BY abigint";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
reader.GetInt64(0);
reader.GetInt64(1); // <--- max(aint)... this fails
}
}
}
public void LastInsertid()
{
execSQL("DROP TABLE Test");
execSQL("CREATE TABLE Test(id int auto_increment, name varchar(20), primary key(id))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(NULL, 'test')", conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(1, cmd.LastInsertedId);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
}
Assert.AreEqual(2, cmd.LastInsertedId);
cmd.CommandText = "SELECT id FROM Test";
cmd.ExecuteScalar();
Assert.AreEqual(-1, cmd.LastInsertedId);
}
public void Unicode()
{
if (version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (u2 varchar(255) CHARACTER SET ucs2)");
MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8");
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES ( CONVERT('困巫忘否役' using ucs2))", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
string s1 = reader.GetString(0);
Assert.AreEqual("困巫忘否役", s1);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
c.Close();
}
}
public void TestSequence()
{
MySqlCommand cmd = new MySqlCommand("insert into Test (id, name) values (?id, 'test')", conn);
cmd.Parameters.Add(new MySqlParameter("?id", 1));
for (int i = 1; i <= 8000; i++)
{
cmd.Parameters[0].Value = i;
cmd.ExecuteNonQuery();
}
int i2 = 0;
cmd = new MySqlCommand("select * from Test", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Assert.AreEqual(i2 + 1, reader.GetInt32(0), "Sequence out of order");
i2++;
}
reader.Close();
Assert.AreEqual(8000, i2);
cmd = new MySqlCommand("delete from Test where id >= 100", conn);
cmd.ExecuteNonQuery();
}
}
public void ProblemCharsInSQLUTF8()
{
if (Version < new Version(4, 1)) return;
execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), mt MEDIUMTEXT, " +
"PRIMARY KEY(id)) CHAR SET utf8");
using (MySqlConnection c = new MySqlConnection(GetConnectionString(true) + ";charset=utf8"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?text, ?mt)", c);
cmd.Parameters.AddWithValue("?id", 1);
cmd.Parameters.AddWithValue("?text", "This is my;test ? string–’‘’“”…");
cmd.Parameters.AddWithValue("?mt", "My MT string: ?");
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual("This is my;test ? string–’‘’“”…", reader.GetString(1));
Assert.AreEqual("My MT string: ?", reader.GetString(2));
}
}
}
public void FieldConversion()
{
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
short s = reader.GetInt16(0);
long l = reader.GetInt64(0);
string str = reader.GetString(1);
}
Assert.AreEqual(6, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT * FROM Test"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[3].Contains("Usage Advisor Warning: The field 'id' was converted to the following types: Int16,Int64"));
Assert.IsTrue(listener.Strings[4].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=8"));
Assert.IsTrue(listener.Strings[5].Contains("Query Closed"));
}
public void Blobs()
{
execSQL("CREATE TABLE Test (id INT, blob1 LONGBLOB, text1 LONGTEXT)");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, ?blob1, ?text1)", conn);
byte[] bytes = Utils.CreateBlob(400000);
string inStr = "This is my text";
cmd.Parameters.AddWithValue("?id", 1);
cmd.Parameters.AddWithValue("?blob1", bytes);
cmd.Parameters.AddWithValue("?text1", inStr);
cmd.Prepare();
int count = cmd.ExecuteNonQuery();
Assert.AreEqual(1, count);
cmd.CommandText = "SELECT * FROM Test";
cmd.Prepare();
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual(bytes.Length, reader.GetBytes(1, 0, null, 0, 0));
byte[] outBytes = new byte[bytes.Length];
reader.GetBytes(1, 0, outBytes, 0, bytes.Length);
for (int x = 0; x < bytes.Length; x++)
Assert.AreEqual(bytes[x], outBytes[x]);
Assert.AreEqual(inStr, reader.GetString(2));
}
}
public void AutoIncrement()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (testID int(11) NOT NULL auto_increment, testName varchar(100) default '', " +
"PRIMARY KEY (testID)) ENGINE=InnoDB DEFAULT CHARSET=latin1");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (NULL, 'test')", conn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY as 'Identity'";
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
int ident = Int32.Parse(reader.GetValue(0).ToString());
Assert.AreEqual(1, ident);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
}
}
public void BadIndexUsed()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test(id INT, name VARCHAR(20) PRIMARY KEY)");
execSQL("INSERT INTO Test VALUES (1, 'Test1')");
execSQL("INSERT INTO Test VALUES (2, 'Test2')");
execSQL("INSERT INTO Test VALUES (3, 'Test3')");
execSQL("INSERT INTO Test VALUES (4, 'Test4')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
MySqlCommand cmd = new MySqlCommand("SELECT name FROM Test WHERE id=3", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
Assert.AreEqual(6, listener.Strings.Count);
Assert.IsTrue(listener.Strings[0].Contains("Query Opened: SELECT name FROM Test WHERE id=3"));
Assert.IsTrue(listener.Strings[1].Contains("Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1"));
Assert.IsTrue(listener.Strings[2].Contains("Usage Advisor Warning: Query does not use an index"));
Assert.IsTrue(listener.Strings[3].Contains("Usage Advisor Warning: The following columns were not accessed: name"));
Assert.IsTrue(listener.Strings[4].Contains("Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=6"));
Assert.IsTrue(listener.Strings[5].Contains("Query Closed"));
}
public void Warnings()
{
if (version < new Version(4, 1)) return;
conn.InfoMessage += new MySqlInfoMessageEventHandler(WarningsInfoMessage);
execSQL("DROP TABLE IF EXISTS test");
execSQL("CREATE TABLE test (name VARCHAR(10))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES ('12345678901')", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
reader.Close();
}
}
public void CancelSelect()
{
if (version < new Version(5, 0)) return;
execSQL("CREATE TABLE Test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
for (int i=0; i < 10000; i++)
execSQL("INSERT INTO Test VALUES (NULL, 'my string')");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", conn);
cmd.CommandTimeout = 0;
int rows = 0;
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
cmd.Cancel();
try
{
while (reader.Read())
{
rows++;
}
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
}
Assert.IsTrue(rows < 10000);
}
public void Timeout()
{
for (int i=1; i < 10; i++)
execSQL("INSERT INTO Test VALUES (" + i + ", 'This is a long text string that I am inserting')");
// we create a new connection so our base one is not closed
MySqlConnection c2 = new MySqlConnection(conn.ConnectionString);
c2.Open();
KillConnection(c2);
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", c2);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Read();
reader.Read();
reader.Close();
Assert.Fail("We should not reach this code");
}
catch (Exception)
{
Assert.AreEqual(ConnectionState.Closed, c2.State);
}
finally
{
if (reader != null) reader.Close();
c2.Close();
}
}
public void TestReader()
{
execSQL("INSERT INTO Test VALUES('P', 'Test1', 'Test2')");
MySqlTransaction txn = conn.BeginTransaction();
MySqlConnection c = txn.Connection;
Assert.AreEqual( conn, c );
MySqlCommand cmd = new MySqlCommand("SELECT name, name2 FROM Test WHERE key2='P'",
conn, txn);
MySqlTransaction t2 = cmd.Transaction;
Assert.AreEqual( txn, t2 );
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
reader.Close();
txn.Commit();
}
catch (Exception ex)
{
Assert.Fail( ex.Message );
txn.Rollback();
}
finally
{
if (reader != null) reader.Close();
}
}
public void Bug6135()
{
execSQL("DROP TABLE IF EXISTS KLANT");
string sql = "CREATE TABLE `KLANT` (`KlantNummer` int(11) NOT NULL auto_increment, " +
"`Username` varchar(50) NOT NULL default '', `Password` varchar(100) NOT NULL default '', " +
"`Naam` varchar(100) NOT NULL default '', `Voornaam` varchar(100) NOT NULL default '', " +
"`Straat` varchar(100) NOT NULL default '', `StraatNr` varchar(10) NOT NULL default '', " +
"`Gemeente` varchar(100) NOT NULL default '', `Postcode` varchar(10) NOT NULL default '', " +
"`DefaultMail` varchar(255) default '', `BtwNr` varchar(50) default '', " +
"`ReceiveMail` tinyint(1) NOT NULL default '0', `Online` tinyint(1) NOT NULL default '0', " +
"`LastVisit` timestamp(14) NOT NULL, `Categorie` int(11) NOT NULL default '0', " +
"PRIMARY KEY (`KlantNummer`), UNIQUE KEY `UniqueUsername` (`Username`), " +
"UNIQUE KEY `UniqueDefaultMail` (`DefaultMail`) )";
createTable(sql, "MyISAM");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM KLANT", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
while (reader.Read()) { }
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
}
}
public void Simple()
{
execSQL("DROP TABLE IF EXISTS Test");
execSQL("CREATE TABLE Test (id INT, dec1 DECIMAL(5,2), name VARCHAR(100))");
execSQL("INSERT INTO Test VALUES (1, 345.12, 'abcd')");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1,345.12,'abcd')", conn);
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
cmd.Prepare();
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual(345.12, reader.GetDecimal(1));
Assert.AreEqual("abcd", reader.GetString(2));
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
}
}
public void VarBinary()
{
if (Version < new Version(4, 1)) return;
execSQL("DROP TABLE IF EXISTS test");
createTable("CREATE TABLE test (id int, name varchar(200) collate utf8_bin) charset utf8", "InnoDB");
execSQL("INSERT INTO test VALUES (1, 'Test1')");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", conn);
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
Assert.IsTrue(reader.Read());
object o = reader.GetValue(1);
Assert.IsTrue(o is string);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null) reader.Close();
}
}
public void AffectedRows()
{
MySqlCommand cmd = new MySqlCommand("SHOW TABLES", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
reader.Close();
Assert.AreEqual(-1, reader.RecordsAffected);
}
}
/// <summary>
/// Bug #57641 Substring out of range exception in ConsumeQuotedToken
/// </summary>
public void QuotedTokenAt300()
{
GenericListener listener = new GenericListener();
string sql = @"SELECT 1 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1`, 2 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2`,
3 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3`, 4 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4`,
5 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5`, 6 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6`;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
public void TestMultiPacket()
{
int len = 20000000;
st.suExecSQL("SET GLOBAL max_allowed_packet=64000000");
// currently do not test this with compression
if (st.conn.UseCompression) return;
using (MySqlConnection c = new MySqlConnection(st.GetConnectionString(true)))
{
c.Open();
byte[] dataIn = Utils.CreateBlob(len);
byte[] dataIn2 = Utils.CreateBlob(len);
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (?id, NULL, ?blob, NULL )", c);
cmd.CommandTimeout = 0;
cmd.Parameters.Add(new MySqlParameter("?id", 1));
cmd.Parameters.Add(new MySqlParameter("?blob", dataIn));
cmd.ExecuteNonQuery();
cmd.Parameters[0].Value = 2;
cmd.Parameters[1].Value = dataIn2;
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
byte[] dataOut = new byte[len];
long count = reader.GetBytes(2, 0, dataOut, 0, len);
Assert.Equal(len, count);
int i = 0;
try
{
for (; i < len; i++)
Assert.Equal(dataIn[i], dataOut[i]);
}
catch (Exception)
{
int z = i;
}
reader.Read();
count = reader.GetBytes(2, 0, dataOut, 0, len);
Assert.Equal(len, count);
for (int x = 0; x < len; x++)
Assert.Equal(dataIn2[x], dataOut[x]);
}
}
}
public void Simple()
{
execSQL("CREATE TABLE Test (id INT, name VARCHAR(20))");
execSQL("INSERT INTO Test VALUES (1, 'A')");
MySqlCommand cmd = new MySqlCommand("SELECT [id], [name] FROM [Test]", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual(1, reader.GetInt32(0));
Assert.AreEqual("A", reader.GetString(1));
}
}
public bool Buscarid( string id )
{
this.abrirConexion();
MySqlCommand myCommand = new MySqlCommand(this.queryBuscar( id ),myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
bool retorno = myReader.HasRows;
myReader.Close();
myReader = null;
myCommand.Dispose();
myCommand = null;
this.cerrarConexion();
return retorno;
}
public MySqlDataReader ExecuteReader(string query)
{
{
string mySelectQuery = query;
MySqlCommand myCommand = new MySqlCommand();
myCommand.Connection = OpenConnection();
myCommand.CommandText = query;
MySqlDataReader myReader = myCommand.ExecuteReader();
return myReader;
}
}
public MySqlDataReader ExecuteReader(string query, MySqlParameter[] mySqlParameters)
{
{
string mySelectQuery = query;
MySqlCommand myCommand = new MySqlCommand();
myCommand.Connection = OpenConnection();
myCommand.CommandText = query;
myCommand.Parameters.AddRange(mySqlParameters);
MySqlDataReader myReader = myCommand.ExecuteReader();
return myReader;
}
}
public void AutoIncrement()
{
execSQL("CREATE TABLE Test (testID int(11) NOT NULL auto_increment, testName varchar(100) default '', " +
"PRIMARY KEY (testID)) ENGINE=InnoDB DEFAULT CHARSET=latin1");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (NULL, 'test')", conn);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@IDENTITY as 'Identity'";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
int ident = Int32.Parse(reader.GetValue(0).ToString());
Assert.AreEqual(1, ident);
}
}
public void VarBinary()
{
if (Version < new Version(4, 1)) return;
createTable("CREATE TABLE test (id int, name varchar(200) collate utf8_bin) charset utf8", "InnoDB");
execSQL("INSERT INTO test VALUES (1, 'Test1')");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM test", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Assert.IsTrue(reader.Read());
object o = reader.GetValue(1);
Assert.IsTrue(o is string);
}
}
public void QuotedTokenAt300()
{
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string sql = @"SELECT 1 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1`, 2 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA2`,
3 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA3`, 4 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA4`,
5 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA5`, 6 AS `AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA6`;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
}
}
public void GetCharsOnLongTextColumn()
{
execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))");
execSQL("INSERT INTO Test (id, text1) VALUES(1, 'Test')");
MySqlCommand cmd = new MySqlCommand("SELECT id, text1 FROM Test", conn);
char[] buf = new char[2];
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
reader.GetChars(1, 0, buf, 0, 2);
Assert.AreEqual('T', buf[0]);
Assert.AreEqual('e', buf[1]);
}
}
public void LastInsertid()
{
execSQL("CREATE TABLE Test(id int auto_increment, name varchar(20), primary key(id))");
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(NULL, 'test')", conn);
cmd.ExecuteNonQuery();
Assert.AreEqual(1, cmd.LastInsertedId);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
}
Assert.AreEqual(2, cmd.LastInsertedId);
cmd.CommandText = "SELECT id FROM Test";
cmd.ExecuteScalar();
Assert.AreEqual(-1, cmd.LastInsertedId);
}
public IEnumerable <avgrating> avgRating(int id)
{
List <avgrating> feeds = new List <avgrating>();
try
{
connection.Open();
string sql = "select AVG(ratings.RATING) AS 'RATING' from feedback INNER JOIN ratings INNER JOIN appointments on feedback.RATING_ID=ratings.ID AND feedback.APPOINTMENT_ID=appointments.ID WHERE appointments.DOCTORS_ID='" + id + "' GROUP BY appointments.DOCTORS_ID";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
avgrating model = new avgrating();
model.rating = reader.GetFloat("RATING");
feeds.Add(model);
}
connection.Close();
return(feeds);
}
catch (Exception ex)
{
return(feeds);
}
}
void FillRows(List <string> departments, int dateCount)
{
for (int i = 0; i < departments.Count; i++)
{
string sql = $"SELECT {departments[i]} FROM {CheckShiftType()} WHERE date BETWEEN {DateRange()};";
MySqlCommand cmd = new MySqlCommand(sql, this.conn);
conn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
string[] row = new string[dateCount];
int index = 0;
while (dr.Read())
{
int employeeId = 0;
bool tryParse = int.TryParse(dr[0].ToString(), out employeeId);
//employeeId = Convert.ToInt32(dr[0].ToString());
//int employeeId = 101;
string name = "";
foreach (Statistics employee in allEmployees)
{
if (employee.id == employeeId)
{
name = $"{employee.firstName} {employee.lastName}";
break;
}
}
row[index] = name;
index++;
}
lvMorning.Items.Insert(i + 1, Convert.ToString(departments[i])).SubItems.AddRange(row);
lvAfternoon.Items.Insert(i + 1, Convert.ToString(departments[i])).SubItems.AddRange(row);
lvEvening.Items.Insert(i + 1, Convert.ToString(departments[i])).SubItems.AddRange(row);
conn.Close();
}
}
public List <Post> Listar()
{
List <Post> posts = new List <Post>();
using (MySqlConnection conexao = new MySqlConnection(this.connectionString))
{
conexao.Open();
MySqlCommand comando = new MySqlCommand();
comando.CommandText = "SELECT * FROM post";
comando.Connection = conexao;
MySqlDataReader reader = comando.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var post = new Post();
post.id = Guid.Parse(reader["id"].ToString());
post.categoria_id = Guid.Parse(reader["categoria_id"].ToString());
post.titulo = reader["titulo"].ToString();
post.conteudo = reader["conteudo"].ToString();
post.resumo = reader["resumo"].ToString();
post.tag = reader["tag"].ToString();
posts.Add(post);
}
}
}
return(posts);
}
public void TestNotAllowZerDateAndTime()
{
st.execSQL("SET SQL_MODE=''");
st.execSQL("INSERT INTO Test VALUES(1, 'Test', '0000-00-00', '0000-00-00', '00:00:00')");
st.execSQL("INSERT INTO Test VALUES(2, 'Test', '2004-11-11', '2004-11-11', '06:06:06')");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test", st.conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
Assert.True(reader.Read());
MySqlDateTime testDate = reader.GetMySqlDateTime(2);
Assert.False(testDate.IsValidDateTime, "IsZero is false");
Exception ex = Assert.Throws <MySqlConversionException>(() => reader.GetValue(2));
Assert.Equal(ex.Message, "Unable to convert MySQL date/time value to System.DateTime");
Assert.True(reader.Read());
DateTime dt2 = (DateTime)reader.GetValue(2);
Assert.Equal(new DateTime(2004, 11, 11).Date, dt2.Date);
}
}
public String actualizaPokemon(String id, String dato)
{
try
{
miClaseConexion.conexion.Open();
MySqlCommand consulta = new MySqlCommand("UPDATE pokemon set nombre = '" + dato + "' WHERE id='" + id + "'", miClaseConexion.conexion);
MySqlDataReader resultado = consulta.ExecuteReader();
String mensaje;
if (resultado.RecordsAffected == 1)
{
mensaje = "actualizado correctamente";
}
else
{
mensaje = "error al actualizar el nombre del pokemon";
}
miClaseConexion.conexion.Close();
return(mensaje);
}
catch (MySqlException e)
{
throw e;
}
}
/// <summary>
/// Load character's items.
/// </summary>
/// <param name="characterId"></param>
/// <returns></returns>
private void LoadCharacterItems(Character character)
{
using (var conn = this.GetConnection())
using (var mc = new MySqlCommand("SELECT `i`.*, `inv`.`sort`, `inv`.`equipSlot` FROM `inventory` AS `inv` INNER JOIN `items` AS `i` ON `inv`.`itemId` = `i`.`itemUniqueId` WHERE `characterId` = @characterId ORDER BY `sort` ASC", conn))
{
mc.Parameters.AddWithValue("@characterId", character.Id);
using (var reader = mc.ExecuteReader())
{
while (reader.Read())
{
var itemId = reader.GetInt32("itemId");
var amount = reader.GetInt32("amount");
var equipSlot = (EquipSlot)reader.GetByte("equipSlot");
// Check item, in case its data was removed
if (!ChannelServer.Instance.Data.ItemDb.Exists(itemId))
{
Log.Warning("ChannelDb.LoadCharacterItems: Item '{0}' not found, removing it from inventory.", itemId);
continue;
}
var item = new Item(itemId, amount);
if (!Enum.IsDefined(typeof(EquipSlot), equipSlot))
{
character.Inventory.AddSilent(item);
}
else
{
character.Inventory.SetEquipSilent(equipSlot, item);
}
}
}
}
}
public List <LawyerForGet> GetMyLawyers(string id)
{
List <LawyerForGet> list = new List <LawyerForGet>();
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT aspnetusers.Id AS userId, aspnetusers.FirstName, aspnetusers.LastName, lawsuits.procedure_id , lawyers.id FROM lawyers INNER JOIN aspnetusers ON aspnetusers.id=lawyers.user_id INNER JOIN lawsuits ON lawsuits.id=lawyers.lawsuit_id WHERE aspnetusers.Id=@id", conn);
cmd.Parameters.Add("@id", MySqlDbType.VarChar).Value = id;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new LawyerForGet()
{
Id = Convert.ToInt32(reader["id"]), userId = Convert.ToString(reader["userId"]), firstName = Convert.ToString(reader["FirstName"]), lastName = Convert.ToString(reader["LastName"]), procedure_id = Convert.ToString(reader["procedure_id"])
});
}
}
conn.Close();
}
return(list);
}
private void CboxTurma2_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
turma.NomeTurma = cboxTurma2.Text;
turma.ObterTurma();
MySqlConnection connection = new MySqlConnection("Server=localhost; port=3306; Database=db_escola; User ID=root;");
string selectQuery = "select * from aluno where idTurma ='" + turma.IdTurma + "';";
connection.Open();
MySqlCommand command = new MySqlCommand(selectQuery, connection);
MySqlDataReader reader = command.ExecuteReader();
cboxAluno.Items.Clear();
while (reader.Read())
{
cboxAluno.Items.Add(reader.GetString("nomeAluno"));
}
}
catch (Exception ex)
{
MessageBox.Show("Falha no Sistema: " + ex.Message);
}
}
public void autovalueaddedId()
{
AutoCompleteStringCollection col = new AutoCompleteStringCollection();
try
{
string selectQuery = "SELECT * FROM customer";
conn.Open();
MySqlCommand command = new MySqlCommand(selectQuery, conn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string ids = reader.GetString("id");
col.Add(ids);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
id_txt.AutoCompleteCustomSource = col;
conn.Close();
}
public static List<BuildData> GetBuilds(string Project, long LastBuildId)
{
List<BuildData> ReturnedBuilds = new List<BuildData>();
string ProjectLikeString = "%" + (Project == null ? String.Empty : GetProjectStream(Project)) + "%";
using (MySqlConnection Connection = new MySqlConnection(ConnectionString))
{
Connection.Open();
using (MySqlCommand Command = new MySqlCommand("SELECT Badges.Id, Badges.ChangeNumber, Badges.BuildType, Badges.Result, Badges.Url, Projects.Name, Badges.ArchivePath FROM ugs_db.Badges " +
"INNER JOIN ugs_db.Projects ON Projects.Id = Badges.ProjectId WHERE Badges.Id > @param1 AND Projects.Name LIKE @param2 ORDER BY Badges.Id", Connection))
{
Command.Parameters.AddWithValue("@param1", LastBuildId);
Command.Parameters.AddWithValue("@param2", ProjectLikeString);
using (MySqlDataReader Reader = Command.ExecuteReader())
{
while (Reader.Read())
{
BuildData Build = new BuildData();
Build.Id = Reader.GetInt32(0);
Build.ChangeNumber = Reader.GetInt32(1);
Build.BuildType = Reader.GetString(2).TrimEnd();
if (Enum.TryParse(Reader.GetString(3).TrimEnd(), true, out Build.Result))
{
Build.Url = Reader.GetString(4);
Build.Project = Reader.IsDBNull(5) ? null : Reader.GetString(5);
if (Build.Project == null || String.Compare(Build.Project, Project, true) == 0 || MatchesWildcard(Build.Project, Project))
{
ReturnedBuilds.Add(Build);
}
}
LastBuildId = Math.Max(LastBuildId, Build.Id);
}
}
}
}
return ReturnedBuilds;
}
private int CountNearEndLeaveEmployees()
{
int count = 0;
try
{
using (MySqlConnection conn =
new MySqlConnection(ConfigurationManager.ConnectionStrings["HRISConnection"].ConnectionString))
{
conn.Open();
string query = "SELECT from_date, to_date FROM tbl_leave WHERE tbl_leave.type IS NOT NULL && is_deleted = @is_deleted;";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("is_deleted", 0);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
DateTime endOfLeave;
double endLeave;
while (reader.Read())
{
endOfLeave = Convert.ToDateTime(reader[1]);
endLeave = (endOfLeave - DateTime.Now.Date).TotalDays;
if (endLeave >= 0 && endLeave <= 7)
{
count++;
}
}
}
}
}
catch (Exception)
{
count = 0;
}
return(count);
}
private static string ExecuteQuery(string query, string columnName)
{
if (MainWindow.connect.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = query;
cmd.Connection = MainWindow.connect.connection;
MySqlDataReader dataReader = cmd.ExecuteReader();
string result = null;
while (dataReader.Read())
{
result += dataReader[columnName] + " ";
}
dataReader.Close();
MainWindow.connect.CloseConnection();
return(result);
}
else
{
MainWindow.connect.CloseConnection();
return(null);
}
}
/// <summary>
/// Haal een lijst met de inhoud van de winkelmand.
/// </summary>
/// <param name="Klantnr"></param>
/// <returns></returns>
public List <Winkelmand> HaalWinkelmandOp(int Klantnr)
{
MySqlConnection Conn = new MySqlConnection(ConnSTR);
Conn.Open();
string QRY = "select foto,tblwinkelmanden.artikelnr,naam,aantal,prijs,(aantal * prijs) as totaal from tblartikelen inner join tblwinkelmanden on tblartikelen.artikelnr = tblwinkelmanden.artikelnr where tblwinkelmanden.klantnr = " + Klantnr;
MySqlCommand CMD = new MySqlCommand(QRY, Conn);
MySqlDataReader DTR = CMD.ExecuteReader();
List <Winkelmand> Lijst = new List <Winkelmand>();
while (DTR.Read())
{
Winkelmand _Winkelmand = new Winkelmand();
_Winkelmand.Foto = Convert.ToString(DTR["foto"]);
_Winkelmand.ArtikelNr = Convert.ToInt32(DTR["artikelnr"]);
_Winkelmand.Naam = Convert.ToString(DTR["naam"]);
_Winkelmand.Aantal = Convert.ToInt32(DTR["aantal"]);
_Winkelmand.Prijs = Convert.ToDouble(DTR["prijs"]);
_Winkelmand.Totaal = Convert.ToDouble(DTR["totaal"]);
Lijst.Add(_Winkelmand);
}
Conn.Close();
return(Lijst);
}
public IEnumerable <numberpatients> patientsVisited(int id)
{
List <numberpatients> feeds = new List <numberpatients>();
try
{
connection.Open();
string sql = "select count(patients.ID) 'COUNT' from patients INNER JOIN appointments on patients.ID=appointments.PATIENTS_ID where appointments.DOCTORS_ID='" + id + "'";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
numberpatients model = new numberpatients();
model.Count = reader.GetInt32("COUNT");
feeds.Add(model);
}
connection.Close();
return(feeds);
}
catch (Exception ex)
{
return(feeds);
}
}
public bool CategoriaFormatoSearch(string nombre, int id)
{
var mysql = new DBConnection.ConexionMysql();
query = String.Format("SELECT * " +
"FROM admin_categoria " +
"WHERE nombre='{0}' AND idpadre='{1}';", nombre, id);
mysql.AbrirConexion();
MySqlCommand consulta = new MySqlCommand(query, mysql.conectar);
MySqlDataReader buscar = consulta.ExecuteReader();
using (buscar)
{
while (buscar.Read())
{
if (!string.IsNullOrEmpty(buscar.GetString("nombre")))
{
return(true);
}
}
}
mysql.CerrarConexion();
return(false);
}
public void billno()
{
myConn = new MySqlConnection(myConnection1);
querry = "SELECT Cno FROM techbhukkad.main ORDER BY Cno DESC LIMIT 1; ";
MySqlCommand myCmd = new MySqlCommand(querry, myConn);
MySqlDataReader myDr;
try
{
myConn.Open();
myDr = myCmd.ExecuteReader();
while (myDr.Read())
{
Cno1 = myDr.GetString("Cno");
Cno1 = Convert.ToString(Convert.ToUInt32(Cno1) + 1);
textBox3.Text = Cno1;
}
myConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Techbhukkad Alert");
}
}
public bool CheckWholeSale(Int32 order_no, String pos_terminal)
{
bool found = false;
con = new MySqlConnection();
dbcon = new Conf.dbs();
con.ConnectionString = dbcon.getConnectionString();
String query = "SELECT pos_iswholesale AS a FROM pos_store ";
query += "WHERE (pos_orno = ?pos_orno) AND (pos_terminal = ?pos_terminal) AND (pos_iswholesale = 1)";
try
{
con.Open();
MySqlCommand cmd = new MySqlCommand(query, con);
cmd.Parameters.AddWithValue("?pos_orno", order_no);
cmd.Parameters.AddWithValue("?pos_terminal", pos_terminal);
cmd.ExecuteScalar();
MySqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr["a"].ToString() == "1")
{
found = true;
}
else
{
found = false;
}
}
}
finally
{
con.Close();
}
return(found);
}
//read input to join-table
public List <ArrivalCity> GetArrivals()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT arrival_cities.* FROM departure_cities
JOIN flights ON (departure_cities.id = flights.departure_city_id)
JOIN arrival_cities ON (flights.arrival_city_id = arrival_cities.id)
WHERE departure_cities.id = @DepartureCityId;";
MySqlParameter departureCityIdParameter = new MySqlParameter();
departureCityIdParameter.ParameterName = "@DepartureCityId";
departureCityIdParameter.Value = _id;
cmd.Parameters.Add(departureCityIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <ArrivalCity> arrivalCities = new List <ArrivalCity> {
};
while (rdr.Read())
{
int arrivalCityId = rdr.GetInt32(0);
string arrivalCityName = rdr.GetString(1);
ArrivalCity newArrivalCity = new ArrivalCity(arrivalCityName, arrivalCityId);
arrivalCities.Add(newArrivalCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(arrivalCities);
}
public string Id()
{
MySqlCommand cmd;
MySqlDataReader dr;
string x = "";
MySqlConnection cn = new MySqlConnection(Clases.Variables.scon);
try
{
cn.Open();
cmd = new MySqlCommand("SELECT MAX(Id)+1 as Id FROM historicobienes", cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
x = dr["Id"].ToString();
}
cn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error al llenar :" + ex.ToString());
}
return(x);
}
public List <Band> GetBands()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT bands.* FROM venue
JOIN bands_venues ON ( venue.id = bands_venues.venue_id)
JOIN bands ON ( bands_venues.band_id = bands.id) where venue.id = @venueId;";
MySqlParameter venueId = new MySqlParameter();
venueId.ParameterName = "@venueId";
venueId.Value = _id;
cmd.Parameters.Add(venueId);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Band> Bands = new List <Band> {
};
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string name = rdr.GetString(1);
string song = rdr.GetString(2);
Band MyBand = new Band(name, song, id);
Bands.Add(MyBand);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(Bands);
}
public UpgradeData GetUpgradeInfo(string UpgradeType, int UpgradeLevel)
{
UpgradeData data = new UpgradeData();
using (MySqlConnection conn = db.GetConnection())
{
//conn.Open();
string query = String.Format(
@"
SELECT
upgrade_type, upgrade_level,
upgrade_amount, upgrade_cost
FROM tb_upgrade_info
WHERE upgrade_type = '{0}' AND upgrade_level = {1}
", UpgradeType, UpgradeLevel);
Console.WriteLine(query);
using (MySqlCommand cmd = (MySqlCommand)conn.CreateCommand())
{
cmd.CommandText = query;
using (MySqlDataReader reader = (MySqlDataReader)cmd.ExecuteReader())
{
if (reader.Read())
{
data.UpgradeType = reader.GetString(0);
data.UpgradeLevel = reader.GetInt32(1);
data.UpgradeAmount = reader.GetInt32(2);
data.UpgradeCost = reader.GetInt32(3);
return(data);
}
}
}
}
return(null);
}
private void a()
{
String count = "SELECT COUNT(*) FROM employee ";
MySqlCommand command = new MySqlCommand(count, databaseConnection);
MySqlDataReader myaReader = command.ExecuteReader();
while (myaReader.Read())
{ //ID
num_employees = num_employees + myaReader.GetString(0) + "\n";
// MessageBox.Show("num_students" + num_students);
}
myaReader.Close();
num = int.Parse(num_employees);
name_employees = new string[num];
salary_employees = new string[num];
start_date_employees = new string[num];
end_date_employees = new string[num];
role_employees = new string[num];
}
public List <City> GetCities()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT cities.* FROM flights
JOIN cities_flights ON (flights.id = cities_flights.flight_id)
JOIN cities ON (cities_flights.city_id = cities.id)
WHERE flights.id = @FlightId;";
MySqlParameter flightIdParameter = new MySqlParameter();
flightIdParameter.ParameterName = "@FlightId";
flightIdParameter.Value = _id;
cmd.Parameters.Add(flightIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <City> cities = new List <City> {
};
while (rdr.Read())
{
int cityId = rdr.GetInt32(0);
string cityName = rdr.GetString(1);
City newCity = new City(cityName, cityId);
cities.Add(newCity);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(cities);
}
public string HaalStraatNaamOp(string postcode, string huisnummer)
{
try
{
OpenConn();
string query = $"SELECT `Aanduiding` FROM nl_bag_adresdata WHERE `Postcode` = '{postcode}' && `Huisnummer` = '{huisnummer}'";
MySqlCommand msCommand = new MySqlCommand(query, _MySQLconn);
MySqlDataReader mscReader = msCommand.ExecuteReader();
dtResult.Load(mscReader);
CloseConn();
if (dtResult.Rows.Count != 0)
{
return(dtResult.Rows[0][0].ToString());
}
else
{
return(null);
}
}
catch (System.Exception)
{
return(null);
}
}
private void LoadTodosAllThisWeek()
{
con.OpenCon();
string cmd = "SELECT * FROM todo WHERE class_id=@class_id AND done=0 AND duedate BETWEEN @date1 AND @date2";
MySqlCommand command = new MySqlCommand(cmd, con.getCon());
command.Parameters.AddWithValue("@class_id", user.ClasaId);
command.Parameters.AddWithValue("@date1", DateTime.Now.ToString("dd MMMM yyyy"));
command.Parameters.AddWithValue("@date2", DateTime.Now.AddDays(7).ToString("dd MMMM yyyy"));
using (MySqlDataReader reader = command.ExecuteReader())
{
todo todo = new todo();
while (reader.Read())
{
todo.id = reader["id"].ToString();
todo.title = reader["title"].ToString();
todo.description = reader["description"].ToString();
todo.link = reader["link"].ToString();
todo.file = reader["file"].ToString();
todo.user_id = reader["class_id"].ToString();
todo.done = reader["done"].ToString();
todo.mat = reader["mat"].ToString();
todo.duedate = reader["duedate"].ToString();
todosW.Add(todo);
}
}
con.CloseCon();
}
private void btn_deleteAdmin_Click(object sender, EventArgs e)
{
String driver = "datasource=localhost;port=3306;username=root;password=root";
String query = "DELETE FROM `hospital`.`admintable` WHERE `AdminLoginid`='" + this.txt_id.Text + "'; ";
MySqlConnection con = new MySqlConnection(driver);
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();
MessageBox.Show("Admin Data Deleted");
while (reader.Read())
{
}
}
catch (Exception ex)
{
MessageBox.Show("Error while Updating data to databse" + ex.ToString());
}
this.LoadAdminTable();
}
public IEnumerable <string> ListCommentss()
{
//Create an instance of a connection
MySqlConnection Conn = Blog.AccessDatabase();
//Open the connection between the web server and database
Conn.Open();
//Establish a new command (query) for our database
MySqlCommand cmd = Conn.CreateCommand();
//SQL QUERY
cmd.CommandText = "Select * from comments";
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Tag names
List <String> Comments = new List <string> {
};
//Loop Through Each Row the Result Set
while (ResultSet.Read())
{
//Access Column information by the DB column name as an index
string comment = ResultSet["commentdesc"] as string;
//Add the Author Name to the List
Comments.Add(comment);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of tags
return(Comments);
}
void Fillcombo2()
{
comboBox3.Items.Clear();
connection.Open();
string Query = "select Type from teacher where TName='" + comboBox1.Text + "'";
try
{
MySqlCommand command = new MySqlCommand(Query, connection);
reader = command.ExecuteReader();
while (reader.Read())
{
string Type = reader.GetString("Type");
comboBox3.Items.Add(Type);
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
connection.Close();
}
public IEnumerable <visitedpatients> visitedPatients(int id)
{
List <visitedpatients> feeds = new List <visitedpatients>();
try
{
connection.Open();
string sql = "Select patients.NAME,count(patients.ID) from patients INNER JOIN appointments on appointments.PATIENTS_ID=patients.ID WHERE MONTH(appointments.APP_DATETIME)=MONTH(CURRENT_DATE) AND appointments.DOCTORS_ID='" + id + "' GROUP BY patients.id HAVING (count(patients.ID)>2)";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
visitedpatients model = new visitedpatients();
model.NAME = reader.GetString("NAME");
feeds.Add(model);
}
connection.Close();
return(feeds);
}
catch (Exception ex)
{
return(feeds);
}
}
public Employee GetByIdAccount(string idAccount)
{
try
{
OpenConnection();
string query = "select * from Employee where idAccount = " + idAccount;
MySqlCommand command = new MySqlCommand(query, conn);
command.ExecuteNonQuery();
MySqlDataReader reader = command.ExecuteReader();
reader.Read();
int idPosition = 0;
byte[] imgArr = null;
if (!reader.IsDBNull(6))
{
idPosition = int.Parse(reader.GetString(6));
}
if (!reader.IsDBNull(9))
{
imgArr = Convert.FromBase64String(reader.GetString(9));
}
Employee employee = new Employee(int.Parse(reader.GetString(0)), reader.GetString(1),
reader.GetString(2), reader.GetString(3), reader.GetString(4),
DateTime.Parse(reader.GetString(5)), idPosition, DateTime.Parse(reader.GetString(7)),
int.Parse(idAccount), imgArr);
return(employee);
}
catch
{
return(null);
}
finally
{
CloseConnection();
}
}
