//Select statement
public MySqlDataReader Select(string query)
{
MySqlDataReader dataReader = null;
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
//return list to be displayed
return(dataReader);
}
else
{
return(dataReader);
}
}
/// <summary>
/// Writes the contents of the database to the console.
/// </summary>
public void WriteDbContentsToConsole()
{
string query = string.Format(@"SELECT Value FROM {0}.{1}", database, table);
using (MySqlConnection connection = new MySqlConnection(mySqlmanager.GetConnectionString()))
{
using (MySqlCommand command = new MySqlCommand(query, connection))
{
try
{
connection.Open();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader["Value"]);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
}
public void eliminarPensionario(string npension)
{
string query = "SELECT * FROM Pension WHERE NUM_PENSION = @npension";
con.Conectar();
MySqlCommand consulta = new MySqlCommand(query, con.conexion);
consulta.Parameters.Add(new MySqlParameter("@npension", npension));
consulta.ExecuteNonQuery();
MySqlDataReader dr = consulta.ExecuteReader();
if (dr.Read())
{
dr.Close();
query = "DELETE FROM Pension WHERE NUM_PENSION = @npension";
consulta = new MySqlCommand(query, con.conexion);
consulta.Parameters.Add(new MySqlParameter("@npension", npension));
consulta.ExecuteNonQuery();
}
else
{
Mensajes.mensaje("Pensionario inexistente", 1500);
}
}
public IEnumerable <MacroData> GetAll()
{
List <MacroData> listData = new List <MacroData>();
MySqlConnection conn = MysqlConnect.Conn();
MySqlCommand command = MysqlConnect.getSqlCommand("select * from tb_sample_macrodata", conn);
conn.Open();
MySqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
MacroData data = new MacroData {
id = reader.GetInt32(0), indexCode = reader.GetString(1), indexName = reader.GetString(2), tradeName = reader.GetString(3), informationSource = reader.GetString(4), unit = reader.GetString(5), indexData = reader.GetDouble(6), frequency = reader.GetString(7), relaseDate = reader.GetDateTime(8)
};
listData.Add(data);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
reader.Close();
}
conn.Close();
return(listData);
}
public void Russian()
{
if (Version < new Version(4, 1))
{
return;
}
execSQL("CREATE TABLE Test (id int, name VARCHAR(200) CHAR SET cp1251)");
using (MySqlConnection c = new MySqlConnection(conn.ConnectionString + ";charset=utf8"))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, 'щьеи')", c);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM Test";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual("щьеи", reader.GetString(1));
}
}
}
public static string GetPriceItem(string id)
{
using (MySqlConnection con = new MySqlConnection(DatabaseHelper.GetSQLiteConnectionString()))
{
con.Open();
string query = "";
query = $"Select UnitQuantityPrice From item where id='{id}'";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
string user = dataReader["UnitQuantityPrice"].ToString();
con.Close();
return(user);
}
con.Close();
return("0");
}
}
static List <string> GetProductNames(string connStr)
{
MySqlConnection conn = new MySqlConnection();
using (conn)
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT name FROM products;";
MySqlDataReader dr = cmd.ExecuteReader();
List <string> productNames = new List <string>();
while (dr.Read())
{
string name = dr["name"].ToString();
productNames.Add(name);
}
return(productNames);
}
}
/// <summary>
/// Checks whether a supplied user name exists or not
/// </summary>
/// <param name="username">The user name</param>
/// <returns>True if the username is already in the table,
/// false if the username is not in the table</returns>
public bool UserExists(string username)
{
int returnValue = 0;
string Query = "SELECT COUNT(*) FROM users where (usr_Name=" +
"'" + username + "') LIMIT 1";
MySqlCommand verifyUser = new MySqlCommand(Query, this.connection);
try {
verifyUser.CommandTimeout = 0;
verifyUser.ExecuteNonQuery();
MySqlDataReader myReader = verifyUser.ExecuteReader();
while (myReader.Read() != false)
{
returnValue = myReader.GetInt32(0);
}
myReader.Close();
} catch (Exception excp) {
Exception myExcp = new Exception("Could not verify user. Error: " +
excp.Message, excp);
throw (myExcp);
}
if (returnValue == 0)
{
return(false);
}
else
{
return(true);
}
}
public Funcionario GetContaNumFuncionario(int numFuncionario)
{
_logger.LogDebug("A executar [FuncionarioDAO -> GetContaNumFuncionario]");
try
{
_connectionDBService.OpenConnection();
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = _connectionDBService.Connection;
cmd.CommandText = "get_funcionario_numero";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?numero", numFuncionario);
cmd.Parameters["?numero"].Direction = ParameterDirection.Input;
using (MySqlDataReader var = cmd.ExecuteReader())
{
Funcionario funcionario = null;
if (var.Read())
{
funcionario = new Funcionario {
IdFuncionario = var.GetInt32(0), Nome = var.GetString(1), NumFuncionario = numFuncionario, Password = var.GetString(2)
};
}
return(funcionario);
}
}
}
catch (Exception) { throw; }
finally
{
_connectionDBService.CloseConnection();
}
}
public static DBPrepaidCards GetDataByAmount(int amount, string cableType)
{
DBPrepaidCards data = null;
MySqlConnection con = DBConnection.ConnectDatabase();
try
{
MySqlCommand cmd = new MySqlCommand("SELECT amount,pin,id,cableType FROM " + tablename + " WHERE id=(SELECT MIN(id) from " + tablename + " WHERE cableType='" + cableType + "' AND amount=" + amount + ") ", con);
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
data = new DBPrepaidCards();
data.Amount = reader.GetInt32(0);
data.Pin = reader.GetString(1);
data.Id = reader.GetInt32(2);
data.CableType = reader.GetString(3);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
MessageBox.Show(ex.ToString());
}
finally
{
con.Close();
}
return(data);
}
public IEnumerable <string> ListArticles()
{
//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 Articles";
//Gather Result Set of Query into a variable
MySqlDataReader ResultSet = cmd.ExecuteReader();
//Create an empty list of Article Titles
List <String> ArticleTitles = 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 ArticleTitle = ResultSet["articletitle"] as string;
//Add the Author Name to the List
ArticleTitles.Add(ArticleTitle);
}
//Close the connection between the MySQL Database and the WebServer
Conn.Close();
//Return the final list of articles
return(ArticleTitles);
}
private void rellenarCamposUsuario()
{
if (comboBoxListadoUsuarios.SelectedIndex != -1)
{
String idmensaje = comboBoxListadoUsuarios.Text.Substring(0, 4);
try
{
// Iniciamos la conexion.
conexion.Open();
// Aqui hariamos la consulta.
sentenciaSQL = "SELECT id_cliente,nombre,apellido1,apellido2,telefono_fijo,telefono_movil,correo_electronico " +
"FROM sql27652.clientes " +
"WHERE id_cliente = " + idmensaje + ";";
comando = new MySqlCommand(sentenciaSQL, conexion);
resultado = comando.ExecuteReader();
if (resultado.Read())
{
textBox_numCliente.Text = resultado.GetString("id_cliente");
textBox_nombre.Text = resultado.GetString("nombre");
textBox_apellido1.Text = resultado.GetString("apellido1");
textBox_apellido2.Text = resultado.GetString("apellido2");
textBox_correoElectronico.Text = resultado.GetString("correo_electronico");
textBox_telefonoFijo.Text = resultado.GetInt32("telefono_fijo").ToString();
textBox_telefonoMovil.Text = resultado.GetInt32("telefono_movil").ToString();
}
conexion.Close();
}
catch
{
mostrarMensajaError();
}
}
}
private void idsearch3_Click(object sender, EventArgs e)
{
if (idclaim.Text.Equals(""))
{
update(dataGridView2, "SELECT * FROM recclaimvendor_id WHERE recClaimVendor_ID NOT IN (SELECT recClaimVendor_ID FROM recreceiveclaim )");
add2.Enabled = false;
}
else
{
datagrid("SELECT * FROM detailclaimvendor WHERE recClaimVendor_ID ='" + idclaim.Text + "' AND recClaimVendor_ID NOT IN (SELECT recClaimVendor_ID FROM recreceiveclaim )", dataGridView2);
cmd = new MySqlCommand("SELECT * FROM detailclaimvendor WHERE recClaimVendor_ID ='" + idclaim.Text + "' AND recClaimVendor_ID NOT IN (SELECT recClaimVendor_ID FROM recreceiveclaim )", con);
con.Open();
read = cmd.ExecuteReader();
if (read.Read())
{
add2.Enabled = true;
}
else
{
MessageBox.Show("ไม่พบรหัสนี้", "ไม่พบ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
con.Close();
}
private List <RoadFlow.Data.Model.MenuUser> DataReaderToList(MySqlDataReader dataReader)
{
List <RoadFlow.Data.Model.MenuUser> menuUserList = new List <RoadFlow.Data.Model.MenuUser>();
while (dataReader.Read())
{
RoadFlow.Data.Model.MenuUser menuUser = new RoadFlow.Data.Model.MenuUser();
menuUser.ID = dataReader.GetString(0).ToGuid();
menuUser.MenuID = dataReader.GetString(1).ToGuid();
menuUser.SubPageID = dataReader.GetString(2).ToGuid();
menuUser.Organizes = dataReader.GetString(3);
menuUser.Users = dataReader.GetString(4);
if (!dataReader.IsDBNull(5))
{
menuUser.Buttons = dataReader.GetString(5);
}
if (!dataReader.IsDBNull(6))
{
menuUser.Params = dataReader.GetString(6);
}
menuUserList.Add(menuUser);
}
return(menuUserList);
}
} // End show drug name.
public void getDrugID()
{
try
{
string db = databaseConnectionStringTextBox.Text;
MySqlConnection con = new MySqlConnection(db);
con.Open();
MySqlCommand sqlCommand = new MySqlCommand("SELECT `product`.`id` AS 'id' FROM `supplier` JOIN `supplier_price_list` ON `supplier`.`id`=`supplier_price_list`.`supplier_id` JOIN `product` ON `product`.`id`=`supplier_price_list`.`product_id` WHERE `product`.`name`='" + this.searchProductTextBox.Text + "' GROUP BY `supplier_price_list`.`product_id`", con);
MySqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
string id = reader.GetInt64("id").ToString();
drugIDTextBox.Text = id;
}
con.Close();
}
catch (Exception)
{
//MessageBox.Show(ex.Message);
//MessageBox.Show("Error has occured while searching order list price............!", "ORDER PRICE SEARCH ERROR", MessageBoxButtons.AbortRetryIgnore, MessageBoxIcon.Error);
}
}
//读取数据函数
void ReaderData()
{
MySqlCommand mySqlCommand = new MySqlCommand("Select * from unity;", dbConnection);
MySqlDataReader reader = mySqlCommand.ExecuteReader();
try
{
while (reader.Read())
{
if (reader.HasRows)
{
print("ID:" + reader.GetInt32(0) + "--Name：" + reader.GetString(1) + "--Sex：" + reader.GetString(2));
}
}
}
catch (Exception)
{
Console.WriteLine("查询失败了！");
}
finally
{
reader.Close();
}
}
} // End show drug name.
//fetch id from supplier and use the foreign key
public void supplierForeignKey()
{
try
{
string db = databaseConnectionStringTextBox.Text;
MySqlConnection con = new MySqlConnection(db);
con.Open();
MySqlCommand mc = new MySqlCommand("SELECT * FROM supplier WHERE name='" + this.addSupplierCombo.Text + "'", con);
MySqlDataReader n = mc.ExecuteReader();
while (n.Read())
{
String supid = n.GetInt32("id").ToString();
supplierIDTextBox.Text = supid;
}
con.Close();
}
catch (Exception)
{
}
}
public static bool InsertTipoEjercicio(Ejercicios _ejercicio)
{
int idUltimoTipoCreado = 0;
bool exito = false;
connection.Close();
connection.Open();
string proceso = "AltaTipoEjercicio";
MySqlCommand cmd = new MySqlCommand(proceso, connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("NombreTipoEjercicio_in", _ejercicio.NombreTipoEjercicio);
MySqlDataReader r = cmd.ExecuteReader();
while (r.Read())
{
idUltimoTipoCreado = Convert.ToInt32(r["ID"].ToString());
}
exito = true;
connection.Close();
return(exito);
}
public List <Item> GetItems()
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT items.* FROM categories
JOIN categories_items ON (categories.id = categories_items.category_id)
JOIN items ON (categories_items.item_id = items.id)
WHERE categories.id = @CategoryId;";
MySqlParameter categoryIdParameter = new MySqlParameter();
categoryIdParameter.ParameterName = "@CategoryId";
categoryIdParameter.Value = _id;
cmd.Parameters.Add(categoryIdParameter);
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
List <Item> items = new List <Item> {
};
while (rdr.Read())
{
int itemId = rdr.GetInt32(0);
string itemDescription = rdr.GetString(1);
Item newItem = new Item(itemDescription, itemId);
items.Add(newItem);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(items);
}
public IEnumerable <Todo> GetTodos()
{
try
{
using (MySqlConnection connection = new MySqlConnection("Your connectionString + SslMode=None"))
{
connection.Open();
MySqlCommand getCommand = connection.CreateCommand();
getCommand.CommandText = "SELECT whatToDO FROM todo";
using (MySqlDataReader reader = getCommand.ExecuteReader())
{
while (reader.Read())
{
_todoViewModel._allToDos.Add(new Todo(reader.GetString("whatToDO")));
}
}
}
}
catch (MySqlException)
{
// Handle it :)
}
return(_todoViewModel.AllTodos);
}
private Boolean login(string username, string password)
{
string SQL = "SELECT username,password FROM user"; /// ini berguna untuk membaca password dan unsername yang di simpan di database
con.Open();
MySqlCommand cmd = new MySqlCommand(SQL, con);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if ((username == reader.GetString(0)) && (password == reader.GetString(1)))
{
con.Close();
return(true);
}
else if ((username != reader.GetString(0)) && (password != reader.GetString(1)))
{
}
}
con.Close();
return(false);
}
public static string GetisLogin()
{
using (MySqlConnection con = new MySqlConnection(DatabaseHelper.GetSQLiteConnectionString()))
{
con.Open();
string query = "";
query = $"Select * From user WHERE isLogin='******' limit 1";
MySqlCommand cmd = new MySqlCommand(query, con);
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
string user = dataReader["Id"].ToString();
con.Close();
return(user);
}
con.Close();
return("0");
}
}
private static List <Person> FindAll()
{
var list = new List <Person>();
var SQL = "Select * from person";
lock (myConnection)
{
myConnection.Open();
MySqlCommand myCommand = new MySqlCommand(SQL, myConnection);
MySqlDataReader myReader = myCommand.ExecuteReader();
myCommand.Dispose();
if (myReader.HasRows)
{
while (myReader.Read())
{
var person = new Person((int)myReader["Id"], myReader["Name"].ToString());
list.Add(person);
}
}
CloseConnection();
myCommand.Dispose();
}
return(list);
}
public IList <tcpInfo> getTcpDo(string tcpName)
{
List <tcpInfo> lstTcpDo = new List <tcpInfo>(); //新建一个列表,用以存储tcpInfo类的信息
strSql = "select tcp_person,tcp_stm,tcp_etm,tcp_done,tcp_span from tcpdo where tcp_prjname ='" + tcpName + "' order by tcp_doid DESC";
try
{
conn.Open();
//新建mysql命令
cmd = new MySqlCommand(strSql, conn);
//执行查寻; ExecuteReader() 方法的 CommandBehavior.CloseConnection 参数,会在DataReader对象关闭时也同时关闭Connection对象
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
//每读一次,将读到的信息整合成tcpInfo类
lstTcpDo.Add(new tcpInfo(
(dr.IsDBNull(0)) ? "" : dr.GetString(0),
(dr.IsDBNull(1)) ? "" : dr.GetString(1), //获取字符串的方法
(dr.IsDBNull(2)) ? "" : dr.GetString(2),
(dr.IsDBNull(3)) ? "" : dr.GetString(3),
(dr.IsDBNull(4)) ? "" : dr.GetString(4)
));
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//关闭DataReader对象
dr.Close();
dr.Dispose();
}
return(lstTcpDo);
}
public static int NomExiste(string nom)
{
int id = 0;
try
{
Connexion con = new Connexion();
con.OpenConnection();
String req = "SELECT id FROM fournisseur WHERE nom = '" + nom + "' ; ";
MySqlCommand cmd = new MySqlCommand(req, con.connexion);
MySqlDataReader dt = cmd.ExecuteReader();
while (dt.Read())
{
id = dt.GetInt16(0);
}
con.CloseConnection();
return(id);
}
catch (Exception e)
{
MessageBox.Show("" + e.Message);
return(-1);
}
}
private void button1_Click(object sender, EventArgs e)
{
string connStr = "server=localhost;user=root;password=;database=mydb;port=3306;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
string sql = "SELECT username FROM users WHERE username=\"" + textBox1.Text + "\";";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
string name = "";
name = Convert.ToString(rdr[0]);
if (name != "")
{
MessageBox.Show("Пользователь " + name + " уже зарегестрирован");
}
else
{
MessageBox.Show("Пользователь ");
string regis = "INSERT INTO `mydb`.`users`(`username`,`password`)VALUES(\"" + textBox1.Text + "\",\"" + textBox2.Text + "\");";
MySqlCommand regiscmd = new MySqlCommand(regis, conn);
regiscmd.ExecuteNonQuery();
}
}
rdr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
public static int IdExisteJournal(int idd)
{
int id = 0;
try
{
Connexion con = new Connexion();
con.OpenConnection();
String req = " SELECT id FROM journalArticle WHERE idArticle= " + idd + " ";
MySqlCommand cmd = new MySqlCommand(req, con.connexion);
MySqlDataReader dt = cmd.ExecuteReader();
while (dt.Read())
{
id = dt.GetInt16(0);
}
con.CloseConnection();
return(id);
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return(-1);
}
}
public BindableCollection <Munkaruha> GetAllCikkek()
{
BindableCollection <Munkaruha> cikkek = new BindableCollection <Munkaruha>();
MySqlConnection connection = getConnection();
MySqlCommand command = new MySqlCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "GetAllCikkek";
command.Connection = connection;
try
{
OpenConnection(connection);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
cikkek.Add(
new Munkaruha()
{
Id = int.Parse(reader["ID"].ToString()),
Cikkszam = reader["number"].ToString(),
Cikknev = reader["name"].ToString(),
CikkcsoportId = int.Parse(reader["groupid"].ToString()),
Cikkcsoport = reader["group"].ToString(),
Mertekegyseg = reader["unit"].ToString()
}
);
}
CloseConnection(connection);
}
catch (Exception e)
{
Logger.Log(e.Message);
}
return(cikkek);
}
public async Task ExecuteReaderAsync()
{
executeSQL("CREATE TABLE CMDReaderAsyncTest (id int)");
executeSQL("CREATE PROCEDURE CMDReaderAsyncSpTest() BEGIN INSERT INTO CMDReaderAsyncTest VALUES(1); SELECT SLEEP(2); SELECT 'done'; END");
MySqlCommand proc = new MySqlCommand("CMDReaderAsyncSpTest", Connection);
proc.CommandType = CommandType.StoredProcedure;
using (MySqlDataReader reader = await proc.ExecuteReaderAsync() as MySqlDataReader)
{
Assert.NotNull(reader);
Assert.True(reader.Read(), "can read");
Assert.True(reader.NextResult());
Assert.True(reader.Read());
Assert.Equal("done", reader.GetString(0));
reader.Close();
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM CMDReaderAsyncTest";
object cnt = proc.ExecuteScalar();
Assert.Equal(1, Convert.ToInt32(cnt));
}
}
public List <Beheerder> getBeheerdersFromDB()
{
//de connectie met de databank maken
MySqlConnection conn = new MySqlConnection(_connectionString);
//Het SQL-commando definiëren
MySqlCommand cmd = new MySqlCommand("select * from studiepunten.beheerder", conn);
List <Beheerder> beheerdersLijst = new List <Beheerder>();
conn.Open();
MySqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Beheerder beheerder = new Beheerder(
Convert.ToInt16(dataReader[0]),
dataReader[1].ToString(),
dataReader[2].ToString()
);
beheerdersLijst.Add(beheerder);
}
conn.Close();
return(beheerdersLijst);
}
/// <summary>
/// MySqlDataReader转换成DataTable
/// </summary>
/// <param name="dataReader"></param>
/// <returns></returns>
public static DataTable GetNewDataTable(MySqlDataReader dataReader)
{
DataTable datatable = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable();
//动态添加列
try
{
foreach (DataRow myRow in schemaTable.Rows)
{
DataColumn myDataColumn = new DataColumn();
myDataColumn.DataType = myRow.GetType();
myDataColumn.ColumnName = myRow[0].ToString();
datatable.Columns.Add(myDataColumn);
}
//添加数据
while (dataReader.Read())
{
DataRow myDataRow = datatable.NewRow();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
datatable.Rows.Add(myDataRow);
myDataRow = null;
}
schemaTable = null;
dataReader.Close();
return datatable;
}
catch (Exception ex)
{
throw new Exception("转换出错出错!", ex);
}
}
