这是一个mysql的数据库设计,其实MySQL和Server SQL的用户也是差不多一样,如果是使用Server SQL的话,也是可以这样子设计的。但是,今天我所写的案例,连接MySQL数据库。
(1)MySQL的设计
(2)三层架构的设计
(3)Model中的login实体类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Model
{
/// <summary>
/// login的实体类
/// </summary>
public class login
{
/// <summary>
/// 用户的ID
/// </summary>
public string UserId { get; set; }
/// <summary>
/// 用户名
/// </summary>
public string UserName { get; set; }
/// <summary>
/// 用户的密码
/// </summary>
public string Password { get; set; }
/// <summary>
/// 用于测试
/// </summary>
public string Msg { get; set; }
}
}
(4)UI界面的代码
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs" Inherits="Ajax.Login" %>
<!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:TextBox ID="txtUserName" runat="server"></asp:TextBox>
<br />
<br />
密码:<asp:TextBox ID="txtPassword" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="btnSubimt" runat="server" Text="注 册" />
<asp:Button ID="Close" runat="server" Text="取 消" />
</div>
</form>
</body>
</html>
(5)如果如果用的是MySQL数据库的话,那就引用MySql.Data.ddl
如果没有MySql.Data.ddl的话,就点击进:https://dl.pconline.com.cn/download/425471-1.html这个网址下载 MySql.Data.ddl,然后再把它引进项目中。
(6)写一个DBHelper类。如果你有了的话,你可以把它添加到DAL下面。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data; //先引用MySql.Data.dll
using MySql.Data.MySqlClient;
namespace WindowsFormsApp1
{
public static class DBHelper
{
#region [ Connection ]
public static string connectionString = "Database=test;DataSource=localhost;Port=3306;UserId=root;Password=;Charset=utf8;TreatTinyAsBoolean=false;Allow User Variables=True;";
public static MySqlConnection GetConnection
{
get
{
return new MySqlConnection(connectionString);
}
}
#endregion
#region [ ExecuteNonQuery ]
/// <summary>
/// 普通SQL语句执行增删改
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteNonQuery(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程执行增删改
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQueryByProc(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 执行增删改
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
int result = 0;
using (MySqlConnection conn = GetConnection)
{
try
{
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
/// <summary>
/// SQL语句得到 MySqlDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>MySqlDataReader 对象</returns>
public static MySqlDataReader ExecuteReader(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteReader(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程得到 MySqlDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>MySqlDataReader 对象</returns>
public static MySqlDataReader ExecuteReaderByProc(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteReader(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 得到 MySqlDataReader 对象
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>MySqlDataReader 对象</returns>
public static MySqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
MySqlDataReader result = null;
MySqlConnection conn = GetConnection;
conn.Open();
try
{
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
conn.Close();
throw new Exception(ex.Message);
}
//finally
//{
// if (conn != null && conn.State != ConnectionState.Closed)
// conn.Close();
//}
//}
return result;
}
/// <summary>
/// 执行SQL语句, 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteDataSet(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSetByProc(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteDataSet(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataSet </returns>
public static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
DataSet result = null;
using (MySqlConnection conn = GetConnection)
{
try
{
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
result = new DataSet();
adapter.Fill(result);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
/// <summary>
/// 执行SQL语句, 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTable(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteDataTable(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 执行存储过程, 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTableByProc(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteDataTable(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns> DataTable </returns>
public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
DataTable dtResult = null;
DataSet ds = ExecuteDataSet(cmdText, cmdType, commandParameters);
if (ds != null && ds.Tables.Count > 0)
{
dtResult = ds.Tables[0];
}
return dtResult;
}
/// <summary>
/// 普通SQL语句执行ExecuteScalar
/// </summary>
/// <param name="cmdText">SQL语句</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static object ExecuteScalar(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteScalar(cmdText, CommandType.Text, commandParameters);
}
/// <summary>
/// 存储过程执行ExecuteScalar
/// </summary>
/// <param name="cmdText">存储过程</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static object ExecuteScalarByProc(string cmdText, params MySqlParameter[] commandParameters)
{
return ExecuteScalar(cmdText, CommandType.StoredProcedure, commandParameters);
}
/// <summary>
/// 执行ExecuteScalar
/// </summary>
/// <param name="cmdText">命令字符串</param>
/// <param name="cmdType">命令类型</param>
/// <param name="commandParameters">可变参数</param>
/// <returns>受影响行数</returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, params MySqlParameter[] commandParameters)
{
object result = null;
using (MySqlConnection conn = GetConnection)
{
try
{
MySqlCommand command = new MySqlCommand();
PrepareCommand(command, conn, cmdType, cmdText, commandParameters);
result = command.ExecuteScalar();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
conn.Close();
}
}
return result;
}
/// <summary>
/// Command对象执行前预处理
/// </summary>
/// <param name="command"></param>
/// <param name="connection"></param>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="commandParameters"></param>
private static void PrepareCommand(MySqlCommand command, MySqlConnection connection, CommandType cmdType, string cmdText, MySqlParameter[] commandParameters)
{
try
{
if (connection.State != ConnectionState.Open) connection.Open();
command.Connection = connection;
command.CommandText = cmdText;
command.CommandType = cmdType;
//command.CommandTimeout = 3600; //此处请自定义
//if (trans != null) command.Transaction = trans;
if (commandParameters != null)
{
foreach (MySqlParameter parm in commandParameters)
command.Parameters.Add(parm);
}
}
catch
{
}
}
}
}
(7)在DAL中创建一个LoginDAL类,代码如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindowsFormsApp1;
using Model;
using MySql.Data.MySqlClient;
namespace DAL
{
public class LoginDAL
{
public int AddLogin(login login)
{
//定义参数
MySqlParameter[] parameters = new MySqlParameter[]
{
new MySqlParameter("UserName",login.UserName),
new MySqlParameter("Password",login.Password)
};
return DBHelper.ExecuteNonQuery("insert into login(UserId,UserName,Password) values(uuid(),@UserName,@Password)",parameters);
}
}
}
(8)uuid()的了解
我在return DBHelper.ExecuteNonQuery("insert into login(UserId,UserName,Password) values(uuid(),@UserName,@Password)",parameters);中用到了uuid(),如果有对uuid不了解的话,可以访问这个网址:https://baike.baidu.com/item/UUID/5921266?fr=aladdin
(9)BLL下面的LoginBLL代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Model;
using WindowsFormsApp1;
using DAL;
using Model;
using System.Security.Cryptography;
namespace BLL
{
public class LoginBLL
{
public login AddLogin(login login)
{
Model.login model = new login();
//调用DAL中LoginDAL
LoginDAL cd = new LoginDAL();
//对密码进行加密
login.Password = Encryption(login.Password);
if (cd.AddLogin(login) == 1)
{
model.Msg = "注册成功!";
return model;
}
else
{
model.Msg = "注册失败!";
return model;
}
}
/// <summary>
/// ND5加密
/// </summary>
/// <param name="UserPassword"></param>
/// <returns></returns>
public string Encryption(string password)
{
MD5CryptoServiceProvider mD5 = new MD5CryptoServiceProvider();
byte[] de = Encoding.UTF8.GetBytes(password);
de = mD5.ComputeHash(de);
return BitConverter.ToString(de);
}
}
}
(10)UI端的后台代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Model;
using BLL;
namespace Ajax
{
public partial class Login : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
LoginBLL cb = new LoginBLL();//把LoginBLL引用到这里来,准备调用它的方法
login login = new login();//实例化login实体类
/// <summary>
/// 注册功能代码块
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubimt_Click(object sender, EventArgs e)
{
login.UserName = txtUserName.Text.Trim();
login.Password = txtUserName.Text.Trim();
//弹出提示是否注册成功
Response.Write("<script>alert('"+cb.AddLogin(login).Msg+"')</script>");
//如果注册成功了,就调用清除文本框输入的内容方法把文本框清空
Clise();
}
/// <summary>
/// 取消注册功能代码块
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Close_Click(object sender, EventArgs e)
{
//调用清除文本框输入的内容方法
Clise();
}
/// <summary>
/// 清除文本框输入的内容方法
/// </summary>
public void Clise()
{
txtUserName.Text = "";
txtPassword.Text = "";
}
}
}
(11)注册成功效果界面
(12)查看数据库中是否存在刚才注册的数据
?,大功告成,希望这一篇小小的文章能帮助到有困惑的编程爱好者!