//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); } }