简单三层架构
Form
namespace 三层架构
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btExecutescalar_Click(object sender, EventArgs e)//查询一个值
{
users model = new users();
model.userid = "admin";
model.userpass = 123456;
PersonBLL person = new PersonBLL();
int ExecuteScalar= person.selectNew(model);
MessageBox.Show("查询到的值为"+ExecuteScalar.ToString());
}
private void btnInsert_Click(object sender, EventArgs e)//添加数据
{
users model = new users();
model.userid = "xiao";
model.userpass = 1234567;
PersonBLL person = new PersonBLL();
person.insert(model);
MessageBox.Show("添加成功");
}
private void btnDel_Click(object sender, EventArgs e)//删除数据
{
PersonBLL person = new PersonBLL();
users model = new users();
model.userid = "xiaomi";
person.delete(model);
MessageBox.Show("删除成功");
}
private void btnUpdate_Click(object sender, EventArgs e)//更新
{
PersonBLL person = new PersonBLL();
users model = new users();
model.userid = "zhouyong";
person.update(2,model);
MessageBox.Show("更新成功成功");
}
private void btnSelect_Click(object sender, EventArgs e)//查询数据
{
PersonBLL person = new PersonBLL();
users model = new users();
model=person.get("456");
MessageBox.Show("用户名"+model.userid+"密码"+model.userpass);
}
private void Form1_Load(object sender, EventArgs e)
{
dgvselect.DataSource = new PersonBLL().GetAll(); //显示数据
}
}
}
DAL
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using 三层架构.Model;
using System.Data.SqlClient;
using System.Data;
namespace 三层架构.DAL
{
class PersonDAL
{
public int selectNew(users model)//查询单个数据
{
object ExecuteScalar = sqlHelper.ExecuteScalar("select userpass from username where userid=@id and userpass=@pwd", new SqlParameter("@id", model.userid), new SqlParameter("@pwd", model.userpass));
return Convert.ToInt32(ExecuteScalar);
}
public int insert(users model) //增加数据
{
return sqlHelper.ExecNonQuery("insert into username(userid,userpass) values(@id,@pass)", new SqlParameter("@id", model.userid), new SqlParameter("@pass", model.userpass));
}
public int delete(users model) //删除数据
{
return sqlHelper.ExecNonQuery("delete from username where userid=@id",new SqlParameter("id",model.userid));
}
public int update(int id,users model) //更新数据
{
return sqlHelper.ExecNonQuery("update username set userid =@userid where ID=@id", new SqlParameter("@userid",model.userid), new SqlParameter("@id",id));
}
public users get(string id) //查询数据
{
DataTable dt = sqlHelper.ExecuteDataTable("select * from username where userid=@id", new SqlParameter("@id", id));
if (dt.Rows.Count <= 0)
{
return null;
}
else if (dt.Rows.Count >= 1)
{
users model = new users();
DataRow row = dt.Rows[0];
model.userid = row["userid"].ToString();
model.userpass = Convert.ToInt32(row["userpass"]);
return model;
}
else
{
throw new Exception("");
}
}
public IEnumerable<users> GetAll() //查询所有值用到了IEumerable接口
{
DataTable dt = sqlHelper.ExecuteDataTable("select * from username");
List<users> list=new List<users>();
foreach (DataRow row in dt.Rows)
{
users model = new users();
model.id = Convert.ToInt32(row["id"]);
model.userid = row["userid"].ToString();
model.userpass = Convert.ToInt32(row["userpass"]);
list.Add(model);
}
return list;
}
}
}
sqlHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace 三层架构.DAL
{
class sqlHelper
{
private static string constr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
//封装方法的原则：把不变大的放到方法里，把变化的放参数中到
private static SqlConnection conn; //连接
public static SqlConnection Conn
{
get
{
if (conn == null || conn.State == ConnectionState.Broken)
{
conn = new SqlConnection(constr);
}
return conn;
}
}
public static int ExecuteNonQuery(string sql)//参数执行的语句 执行增删改
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteNonQuery();
}
}
}
public static int ExecNonQuery(string sqlstr, params SqlParameter[] paras)
{
SqlCommand cmd = new SqlCommand(sqlstr, Conn);
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
Conn.Open();
int res = 0;
res = cmd.ExecuteNonQuery();
Conn.Close();
return res;
}
public static object ExecuteScalar(string sql) //查询单个值
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
public static object ExecuteScalar(string sql,params SqlParameter[] paras) //查询单个值
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddRange(paras);
cmd.CommandText = sql;
return cmd.ExecuteScalar();
}
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] paras) //查询结果集比较少的sql
{
SqlCommand cmd = new SqlCommand(sql, Conn);
if (paras != null && paras.Length > 0)
{
cmd.Parameters.AddRange(paras);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
adapter.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
return dt;
}
}
}
App.config
?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="conStr" connectionString="server=.;database=hushi;uid=sa;pwd=sa;"/>
</connectionStrings>
</configuration>
BLL
namespace 三层架构.BLL
{
class PersonBLL
{
public int selectNew(users model)//查询单个数据
{
return new PersonDAL().selectNew(model);
}
public int insert(users model) //增加数据
{
return new PersonDAL().insert(model);
}
public int delete(users model) //删除数据
{
return new PersonDAL().delete(model);
}
public int update(int id,users model) //删除数据
{
return new PersonDAL().update(id,model);
}
public users get(string id) //查询数据
{
return new PersonDAL().get(id);
}
public IEnumerable<users> GetAll() //查询所有数据
{
return new PersonDAL().GetAll();
}
}
}
github代码下载：https://github.com/zy20081/practice.git
