ASP.NET操作MYSQL数据库，以及操作数据库方法的封装

一、连接MYSQL数据库

string conn = "Data Source=127.0.0.1;User ID=root;Password=wjx12345;DataBase=test;CharSet=utf8"; MySqlConnection con = new MySqlConnection(conn); con.Open(); Label1.Text = "SqlConnection连接成功!"; con.Close();

效果如图所示:

二、对MYSQL数据库的增删查改

首先，为了便于操作，在项目中新建文件夹把对数据库的操作封装成一个类（记得要对类属性要选择编译，不然无法在其他地方引用）

namespace class4.App_Code { public class DbUtils { private MySqlConnection conn = null; private MySqlCommand cmd = null; private MySqlDataReader reader = null; public DbUtils() { string connstr = "Data Source=127.0.0.1;User ID=root;Password=wjx12345;DataBase=test;CharSet=utf8"; conn = new MySqlConnection(connstr); } public void CreateCommand(string sql) { conn.Open(); cmd = new MySqlCommand(sql, conn); } //插入,修改，删除数据方法 public int getExecute() { int res = -1; try { res = cmd.ExecuteNonQuery(); }catch(MySqlException ex) { Console.WriteLine("操作失败!" + ex.Message); } conn.Close(); return res; } //查询数据 public DataTable getSelect() { DataTable dt = new DataTable(); using (reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(reader); } return dt; } } }

好了，有了之前的操作，可以方便的操作mysql数据库了。

例如实现:列出所有学号并可以查询，插入学生，更新学号，删除等一系列操作。

相关代码:

namespace class4 { public partial class test2 : System.Web.UI.Page { private DbUtils dbUtils = new DbUtils(); private string sql = ""; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { sql = "select * from students"; dbUtils.CreateCommand(sql); DataTable dt = dbUtils.getSelect(); for(int i = 0; i < dt.Rows.Count; i++) { ListItem li = new ListItem(); li.Text = "" + dt.Rows[i][0]; li.Value = "" + dt.Rows[i][0]; DropDownList1.Items.Add(li); } } } //查询数据逻辑 protected void Button1_Click(object sender, EventArgs e) { string sql = "select * from students where uid=" + DropDownList1.SelectedItem.Value; dbUtils.CreateCommand(sql); DataTable dt = dbUtils.getSelect(); if(dt != null) { Label3.Text = "学号:" + dt.Rows[0][0] + " 姓名:" + dt.Rows[0][1]; } else { Label3.Text = "未查询出结果"; } } //添加按钮逻辑 protected void Button2_Click(object sender, EventArgs e) { string uid = TextBox1.Text; string uname = TextBox2.Text; string sql = "insert into students(uid,uname) values(" + uid + ",'" + uname + "')"; dbUtils.CreateCommand(sql); int res = dbUtils.getExecute(); Label3.Text = "有" + res + "条记录受影响"; TextBox1.Text = ""; TextBox2.Text = ""; } //更新按钮逻辑 protected void Button3_Click(object sender, EventArgs e) { string OldId = TextBox3.Text; string NewId = TextBox4.Text; string sql = "update students set uid = " + NewId + " where uid = " + OldId; dbUtils.CreateCommand(sql); int res = dbUtils.getExecute(); Label3.Text = "有" + res + "条记录受影响"; TextBox3.Text = ""; TextBox4.Text = ""; } //删除按钮逻辑 protected void Button4_Click(object sender, EventArgs e) { string uid = TextBox5.Text; string sql = "delete from students where uid = " + uid; dbUtils.CreateCommand(sql); int res = dbUtils.getExecute(); Label3.Text = "有" + res + "条记录受影响"; TextBox5.Text = ""; } } }