！！！精简版三层架构
private void button1_Click(object sender, EventArgs e)
{
// 三层架构
//UI界面层 数据访问层 业务逻辑层
//UI界面层
//查询数据有多少条
int count =StudentDAL.GetCount();
//根据ID删除数据
StudentDAL.DeleteById(12);
// 往数据库添加数据
Student student=new Student();
student.Id=16;
student.Name="casc";
student.Age=18;
student.AiHao="cacs";
student.Height=180.2m;
student.Birthday=DateTime.Now;
student.InDate=DateTime.Parse("1993-08-12");
StudentDAL.Insert( student );
//很据ID查询数据
Student studnet=StudentDAL.GetById(16);
txt1=student.Id;
txt2=student.Name;
txt3=student.Age;
txt4=student.AiHao;
txt5=student.Height;
txt6=student.Birthday;
txt7=student.InDate;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace WindowsFormsApplication2
{//数据访问层（ StudentDAL类 Student类 SqlHelper）
//Student类
class Student
{
public long Id
{get;set;}
public string Name
{get;set;}
public int Age
{get;set;}
public string AiHao
{get;set;}
public decimal? Height{get;set;}
public DateTime? Birthday
{get;set;}
public DateTime? InDate
{get;set;}
}
}
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WpfApplication2;
namespace WindowsFormsApplication2
{
//StudentDAL类
class StudentDAL
{
//输入值为null判断
public static object Todbvalue(object value)
{
if(value==null)
{
return DBNull.Value;
}
else
{
return value;
}
}
//数据库输出值为null判断
public static object Fromdbvalue(object value)
{
if(value==DBNull.Value)
{
return null;
}
else
{
return value;
}
}
//获取数据库数量
public static int GetCount()
{
return (int)SqlHelper.ExecuteScalar("select count(*)from t_Student");
}
//根据id删数据
public static void DeleteById(long id)
{
SqlHelper.ExecuteNonQuery("delete from t_student where id=@id",new SqlParameter("@id",id));
}
//添加数据库
public static void Insert(Student student )
{
SqlHelper.ExecuteNonQuery("insert into T_student (name,age,aihao,height,birthday,indate)values(@name,@age,@aihao,@height,@birthday,@indate)",
new SqlParameter("@name",student.Name),
new SqlParameter("@age",student.Age),
new SqlParameter("@aihao",student.AiHao),
new SqlParameter("@height", Todbvalue(student.Height)),
new SqlParameter("@birthday",Todbvalue(student.Birthday)),
new SqlParameter("@indate",Todbvalue(student.InDate)));
}
//通过ID查询数据库
public static Student GetById(long id)
{
DataTable table=SqlHelper.ExecuteDataTable("select*from t_student where id=@id",new SqlParameter("@id",id));
if(table.Rows.Count<=0)
{
return null;
}
else if(table.Rows.Count>1)
{
throw new Exception("出异常啦");
}
else
{
DataRow row=table.Rows[0];
Student student=new Student();
student.Id=(long)row["id"];
student.Name=(string)row["Name "];
student.Age=(int)row[" Age"];
student.AiHao=(string)row["AiHao"];
student.Height=(decimal?)Fromdbvalue(row["height"]);
student.Birthday=(DateTime?)Fromdbvalue(row["birthday"]);
student.InDate = (DateTime?)Fromdbvalue(row["InDate"]);
return student;
}}}}
App config 应用程序配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="dbconnstr" connectionString="server=.;Database=test;user=sa;pwd=32"/>
</connectionStrings>
</configuration>
SqlHelper
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
namespace WpfApplication2
{
class SqlHelper
{//调用APP.config配置文件中客户需要连接的数据库字符串
private static string connStr = ConfigurationManager.ConnectionStrings["dbconnstr"].ConnectionString;
//封装数据的原则 把不变的写到方法里，把变化的放到参数中
// 增删改的操作
public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))//建立数据库联接 using释放空间
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())//创建操作数据库的命令
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteNonQuery();//执行操作的命令
}
}
}
// 查询返回一条结果的操作 如Count(*) ,sum()等..
public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters); // foreach(SqlParameter param in parameters)
//{
// cmd.Parameters.Add(param);
// }
return cmd.ExecuteScalar();
}
}
}
// DataSet离线查询结果返回表结构操作： 一般项目操作里面只有一个表所以直接返回一个表更方便
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
//DateSet 完整封装方式：
//public static DataSet ExecuteDataSet(string sql,params SqlParameter[] parameters)
//{using (SqlConnection conn=new SqlConnection(connStr))
// {
// conn.Open();
// using(SqlCommand cmd=conn.CreateCommand())
// {
// cmd.CommandText=sql;
// cmd.Parameters.AddRange(parameters);
// SqlDataAdapter adapter=new SqlDataAdapter(cmd);
// DataSet dataset=new DataSet();
// adapter.Fill(dataset);
// return dataset;
// }
// }
//}
////ExcuteReader 大数据联接数据库查询
//public static SqlDataReader ExcuteReader(string sql, params SqlParameter[] parameters)
//{
// using (SqlConnection conn = new SqlConnection(connStr))
// {
// conn.Open();
// using (SqlCommand cmd = conn.CreateCommand())
// {
// cmd.CommandText = sql;
// cmd.Parameters.AddRange(parameters);
// return cmd.ExecuteReader();
// }
// }
//}
}
}
