一、连接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 = "";
}
}
}