一.说明
这一篇简单的个人总结,方便自己的复制粘贴,软件是Visual Studio 2019,SQLyog
二.配置
一.在Web.config中的configuration标签下添加如下代码:
<connectionStrings>
<add name="Conn_example" connectionString="Database='example';Data Source='127.0.0.1';User Id='root';Password='xxxxx';charset='utf8';pooling=true"/>
</connectionStrings>
<!--
数据库位置:Data Source='127.0.0.1'//此处为本地数据库
用户名:User Id=''
密码:Password=''
字符集:charset=''
-->
二.创建一个名为MysqlData.cs的类,并写入如下代码:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
namespace MyTest03
{
public class MysqlData
{
public static string DataBase = ConfigurationManager.ConnectionStrings["Conn_example"].ToString();
//将Web.config文件里的配置取出
}
}
三.在官网下载MySql.Data.dll,并将其引用,大家请在网上查找
三.连接
创建一个asp.net web 窗口,输入如下代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="MyTest03.WebForm2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="增" Width="31px" />
<br />
<asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="删" />
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button4" runat="server" OnClick="Button4_Click" Text="改" />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="查" />
<br />
</div>
</form>
</body>
</html>
其界面效果是这样的:
而对应的cs文件中,则输入如下代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql.Data.MySqlClient;/*添加Mysql的引用*/
namespace MyTest03
{
public partial class WebForm2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
//查寻功能
protected void Button3_Click(object sender, EventArgs e)
{
try {
string sql = "select * from student";
MySqlDataReader DR = MySqlHelper.ExecuteReader(MysqlData.DataBase, sql);
string sex = "";
while (DR.Read())
{
sex = "";
if (DR.GetInt32(2) == 1)
{
sex = "男";
}
else
{
sex = "女";
}
Response.Write("编号:" + DR.GetString(0) + ",姓名:" + DR.GetString(1) + ",性别:" + sex + "<br/>");
}
DR.Close();//关闭
} catch(Exception ex) {
Response.Write(ex.Message);
}
}
//增
protected void Button1_Click(object sender, EventArgs e)
{
string sql = "insert into student value(4,'小绿',0)";
MySqlConnection conn = new MySqlConnection(MysqlData.DataBase);//这里需要穿之前配置好类的参数
conn.Open();//打开
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = sql;//指定cmd要执行的sql语句
cmd.Connection = conn;//命令对象和那个连接对象关联
/*
上面的三行代码也可以换为更为简约的代码:
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
这样就可以省略掉:'cmd.Connection = conn;//命令对象和那个连接对象关联'这一句代码了
*/
//执行并返回结果
int result =cmd.ExecuteNonQuery();
cmd.Dispose();//将命令对象关闭
conn.Close();//将连接对象关闭
//判断返回的结果,是否执行成功
if (result > 0)
{
Response.Write("增加成功!");
}
else
{
Response.Write("增加失败!");
}
}
//删,其余的操作变化都不是很大,无非是sql语句的变化
protected void Button2_Click(object sender, EventArgs e)
{
string te = TextBox1.Text;
string sql = "delete from student where name='" + te + "'";
MySqlConnection conn = new MySqlConnection(MysqlData.DataBase);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
if (result > 0)
{
Response.Write("删除成功!");
}
else
{
Response.Write("删除失败!");
}
}
//改
protected void Button4_Click(object sender, EventArgs e)
{
string te = TextBox2.Text;
string sql = "update student set id=6 where name='" + te + "'";
MySqlConnection conn = new MySqlConnection(MysqlData.DataBase);
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
int result =cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
if (result > 0)
{
Response.Write("修改成功!");
}
else {
Response.Write("修改失败!");
}
}
}
}
四.结尾
为了数据库的安全,一般都是在取得返回之后,就将命令对象,连接对象关闭.
此为简陋的测试,接下来的学习中,如果遇到其他值得记录的代码,也会给小伙伴们分享.谢谢大家!