环境:Visual Studio 2013 , Sql Server 2017
项目分为三层:UI , Dao, Model
UI 层:用于接受客户传递的信息,提交给Dao 层进行处理,显示返回处理的结果代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using Model;
using Dao;
namespace Login_Demo
{
public partial class Form1 : Form
{
private static List<User> list = null;
// private static User[] users = null;
public Form1()
{
InitializeComponent();
}
private void login(object sender, EventArgs e)
{
//由配置文件获取连接数据库的连接字符串
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ToString();
//接受界面输入的密码
string pwd=null;
//接受从数据库查询的密码
string real_pwd = null;
string username = null;
pwd = this.textBox1.Text;username = this.comboBox1.Text.Trim();
//SQL语句查询
//real_pwd = UserDao.findbyUseranme(connstr,username);
//存储过程查询
real_pwd = UserDao.findbyUser_proc(connstr, username);
Console.Write("{0},{1}", username, real_pwd);
if (pwd.Equals(real_pwd))
{
// 1.使用from2.showDialog();
// this.Hide();
// Form form2 = new Form();
// form2.ShowDialog();
// this.Close();
//2.然后给form2的关闭添加一个事件,手动编写关闭form1
this.Hide();
Form form2 = new Form();
form2.Closed += new EventHandler(this.form2_closed);
form2.Show();
}
else
{
MessageBox.Show("密码错误");
}
}
private void form2_closed(object sender, EventArgs e)
{
Application.Exit();
}
private void exit(object sender, EventArgs e)
{
Application.Exit();
}
private void Form1_Load(object sender, EventArgs e)
{
string connstr = System.Configuration.ConfigurationManager.ConnectionStrings["strCon"].ToString();
//
// string connstr = ConfigurationManager.AppSettings["ConnectionString"];
//SQL语句查询
//list=UserDao.findUser(connstr);
//存储过程查询
list=UserDao.findUser_proc(connstr);
for (int i = 0; i < list.Count;i++)
{
//Console.Write(list[i].Username);
this.comboBox1.Items.Add( list[i].Username);
}
}
}
}
Dao层:项目只涉及一个用户表,所以Dao就是对用户表的操作
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using Model;
using System.Data;
namespace Dao
{
/// <summary>
///用户操作类
/// </summary
public class UserDao
{
private static List<User> list= new List<User>();
public UserDao()
{
}
/// <summary>
/// SQL语句查询用户
/// </summary>
/// <param name="connstr"></param>
/// <returns></returns>
public static List<User> findUser(string connstr)
{
//声明连接对象
SqlConnection conn = null;
//sql语句
String sqlStr = "SELECT name,pwd FROM [dbo].[管理员]";
//声明数据读取器
SqlDataReader reader = null;
try
{
conn = new SqlConnection(connstr);
//连接
conn.Open();
if(conn.State == ConnectionState.Open){
Console.Write("连接成功");
}
//实例命令对象
SqlCommand cmd = new SqlCommand(sqlStr, conn);
//cmd.CommandType = System.Data.CommandType.Text;
reader = cmd.ExecuteReader();
while (reader.Read())
{
User user = new User();
user.Username = reader[0];
user.Password = reader[1];
list.Add(user);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
conn.Close();
}
return list;
}
/// <summary>
/// SQL语句通过用户名进行查找
/// </summary>
/// <param name="connstr"></param>
/// <param name="username"></param>
/// <returns></returns>
public static string findbyUseranme(string connstr,string username)
{
//声明连接对象
SqlConnection conn = null;
//用于接受数据库返回结果
string real_pwd = null;
//SQL语句
String sqlStr = "SELECT pwd FROM [dbo].[管理员] WHERE [dbo].[管理员].name = '" + username + "'";
//声明数据读取器
SqlDataReader reader = null;
try
{
//实例连接对象
conn = new SqlConnection(connstr);
//数据库连接
conn.Open();
//实例命令对象
SqlCommand cmd = new SqlCommand(sqlStr, conn);
//执行结果传给数据读取器
reader = cmd.ExecuteReader();
while (reader.Read())
{
real_pwd = reader.GetString(reader.GetOrdinal("pwd"));
}
return real_pwd;
}catch(Exception ex){
throw ex;
}
finally
{
//关闭连接
reader.Close();
conn.Close();
}
}
// <summary>
/// 调用存储过程查询用户
/// </summary>
/// <param name="connstr"></param>
/// <returns></returns>
public static List<User> findUser_proc(string connstr)
{
//存储过程名
string proc = "dbo.FindUser_PROC";
//声明数据读取器
SqlDataReader reader = null;
try
{
SqlConnection con = new SqlConnection(connstr);
SqlCommand cmd = new SqlCommand(proc, con);
con.Open();
//执行存储过程类型命令
cmd.CommandType = CommandType.StoredProcedure;
reader = cmd.ExecuteReader();
while (reader.Read())
{
User user = new User();
user.Username = reader[0];
user.Password = reader[1];
list.Add(user);
}
con.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
}
return list;
}
/// <summary>
/// 存储过程通过用户名进行查找
/// </summary>
/// <param name="connstr"></param>
/// <param name="username"></param>
/// <returns></returns>
public static string findbyUser_proc(string connstr, string username)
{
//声明连接对象
SqlConnection con = null;
//用于接受数据库返回结果
string real_pwd = null;
//存储过程名
string proc = "[dbo].[FindbyName_PROC]";
//声明数据读取器
SqlDataReader reader = null;
try
{
//实例连接对象
con = new SqlConnection(connstr);
//数据库连接
con.Open();
SqlCommand cmd = new SqlCommand(proc, con);
//执行存储过程类型命令
cmd.CommandType = CommandType.StoredProcedure;
//添加参数
cmd.Parameters.Add("@name",SqlDbType.VarChar,20).Value=username;
//执行结果传给数据读取器
reader = cmd.ExecuteReader();
while (reader.Read())
{
real_pwd = reader.GetString(reader.GetOrdinal("pwd"));
}
return real_pwd;
}
catch (Exception ex)
{
throw ex;
}
finally
{
//关闭连接
reader.Close();
con.Close();
}
}
}
}
Model层:与数据库表对应的实体类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Model
{
public class User
{
String username;
/// <summary>
/// 用户名
/// </summary>
public String Username
{
get { return username; }
set { username = value; }
}
String password;
/// <summary>
/// 密码
/// </summary>
public String Password
{
get { return password; }
set { password = value; }
}
}
}
数据库
dbo.管理员表存储过程
------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[FindbyName_PROC]
@name nvarchar(20)AS
BEGIN
Select pwd from [dbo].[管理员] where [dbo].[管理员].name= @name;
END
--------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[FindUser_PROC]
AS
begin
SELECT [name],[pwd] FROM [dbo].[管理员]
end