1. 开发目的:将数据库数据读取到ArrayList集合,实现在内存中查询数据库对象,将数据库和数据访问分离。
2. 开发环境:windows 7+Visual Studio2017+SQLService 2012
3. 开发过程:
1) 编写数据库访问类
2) 封装对象
3) 执行SQL语句,将数据对象返回到ArrayList集合,利用LINQ查询对象
4. 开发整体结构图
5. 开发源码
1) 数据库访问辅助SQLHelp
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace SQL
{
public class SqlHelp
{
public static SqlDataReader ExecuteReader(string cmdStr)
{
string conStr= "Data Source=VICTORY;Initial Catalog = test; Persist Security Info = True; User ID = sa; Password = 123456";
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand(cmdStr, con);
con.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}
2)对象封装UserModel
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace USER
{
class Userinfo
{
public Userinfo(string name, string pwd)
{
this.name = name;
this.pwd = pwd;
}
public string name;
public string pwd;
}
}
3)数据读取与访问 UserDAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using SQL;
using USER;
using System.Collections;
namespace UserDAL
{
class UserLoginDAL
{
public bool LoginDAL(string name, string pwd)
{
string cmd = "select * from users3_0";
SqlDataReader da = SqlHelp.ExecuteReader(cmd);
ArrayList list = new ArrayList();
while (da.Read())
{
string m = da["name"].ToString();
string n = da["pwd"].ToString();
Userinfo u = new Userinfo(m, n);
list.Add(u);
}
Userinfo c = new Userinfo(name, pwd);
var query = from Userinfo st in list
where st.name.Contains(c.name) && st.pwd.Contains(c.pwd)
select st;
if(query.Count()==1)
{
return true;
}
else
{
return false;
}
}
}
}
4)用户界面 Buttorn
private void button1_Click(object sender, EventArgs e)
{
string name = textBox1.Text.ToString().Trim();
string pwd = textBox2.Text.ToString().Trim();
UserDAL.UserLoginDAL a = new UserDAL.UserLoginDAL();
if(a.LoginDAL(name, pwd))
{
MessageBox.Show("1");
}
else
{
MessageBox.Show("0");
}
}
6.运行结果截图
附:数据库数据表图