.Net简单三层
模型是一个学生信息表
数据库连接配置
<appSettings>
<add key="ConnectionString" value="server=(local);database=StudentDB;uid=sa;pwd=''" />
</appSettings>
数据层DBLayer
数据库连接类DBConnection.cs
using System;
using System.Data.SqlClient;
namespace DBLayer
{
/// <summary>
/// DBConnection类
/// 本类为数据库连接类
/// </summary>
public class DBConnection
{
private DBConnection()
{
}
/// <summary>
/// GetConnection() 获得数据库连接
/// </summary>
/// <returns>SqlConnection</returns>
public static SqlConnection GetConnection()
{
string _conString=System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection con=new SqlConnection(_conString);
return con;
}
}
}
业务层BusinessLayer
实体类StudentInfo.cs
using System;
namespace BusinessLayer
{
/// <summary>
/// StudentInfo 的摘要说明。
/// 本类为实体类
/// </summary>
public class StudentInfo
{
private int _studentID;
private string _number;
private string _name;
private string _password;
private DateTime _birthday;
private double _eyesight;
public StudentInfo()
{
}
public int StudentID//自动编号
{
get
{
return _studentID;
}
set
{
_studentID=value;
}
}
public string Number//学号
{
get
{
return _number;
}
set
{
_number=value;
}
}
public string Name//姓名
{
get
{
return _name;
}
set
{
_name=value;
}
}
public string Password//密码
{
get
{
return _password;
}
set
{
_password=value;
}
}
public DateTime Birthday//生日
{
get
{
return _birthday;
}
set
{
_birthday=value;
}
}
public double Eyesight//视力
{
get
{
return _eyesight;
}
set
{
_eyesight=value;
}
}
}
}
控制类StudentInfoService.cs
实现对学生信息表的增删改查
using System;
using System.Data;
using System.Data.SqlClient;
using DBLayer;
namespace BusinessLayer
{
/// <summary>
/// StudentInfoService 的摘要说明。
/// 本类为控制类实现对StudentInfo实体的增删改查操作
/// </summary>
public class StudentInfoService
{
private StudentInfoService()
{
}
/// <summary>
/// 静态字段表示控制类对象
/// </summary>
private static StudentInfoService _instance;
/// <summary>
/// 初始化控制类
/// </summary>
/// <returns>StudentInfoService</returns>
public static StudentInfoService GetInstance()
{
if(_instance==null)
{
_instance=new StudentInfoService();
}
return _instance;
}
/// <summary>
/// 增加学生信息
/// </summary>
/// <param name="info"></param>
/// <returns>StudentInfo</returns>
public StudentInfo Insert(StudentInfo info)
{
if(info==null)
{
throw new Exception("未指定插入的学生信息");
}
string cmdText="insert into StudentInfo(number,name,password,birthday,eyesight)values(@number,@name,@password,@birthday,@eyesight)";
SqlCommand cmd=new SqlCommand(cmdText);
try
{
//创建数据库连接对象
SqlConnection con=DBConnection.GetConnection();
//设置连接
cmd.Connection=con;
//打开数据库连接
cmd.Connection.Open();
//添加参数并赋值
//@number
SqlParameter parm=new SqlParameter("@number",SqlDbType.VarChar,15);
parm.Value=info.Number;
cmd.Parameters.Add(parm);
//@name
parm=new SqlParameter("@name",SqlDbType.VarChar,20);
parm.Value=info.Name;
cmd.Parameters.Add(parm);
//@password
parm=new SqlParameter("@password",SqlDbType.VarChar,30);
parm.Value=info.Password;
cmd.Parameters.Add(parm);
//@birthday
parm=new SqlParameter("@birthday",SqlDbType.DateTime,8);
parm.Value=info.Birthday;
cmd.Parameters.Add(parm);
//@eyesight
parm=new SqlParameter("@eyesight",SqlDbType.Decimal,5);
parm.Value=info.Eyesight;
cmd.Parameters.Add(parm);
//执行SQL语句
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
if(cmd.Connection!=null)
{
cmd.Connection.Close();
}
}
return info;
}
/// <summary>
/// 删除指定ID的学生信息
/// </summary>
/// <param name="studentID"></param>
public void Delete(int studentID)
{
string cmdText="delete from StudentInfo where studentid=@studentid";
SqlCommand cmd=new SqlCommand(cmdText);
try
{
//连接设置
SqlConnection con=DBConnection.GetConnection();
cmd.Connection=con;
//打开连接
cmd.Connection.Open();
//添加参数并赋值
SqlParameter parm=new SqlParameter("@studentid",SqlDbType.Int,4);
parm.Value=studentID;
cmd.Parameters.Add(parm);
//执行SQL语句
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
//关闭连接
if(cmd.Connection!=null)
{
cmd.Connection.Close();
}
}
}
public StudentInfo Update(StudentInfo info)
{
if(info==null)
{
throw new Exception("未指定更新的学生信息");
}
string cmdText="update StudentInfo set number=@number,name=@name,password=@password,birthday=@birthday,eyesight=@eyesight where studentid=@studentid";
SqlCommand cmd=new SqlCommand(cmdText);
try
{
//连接设置
SqlConnection con=DBConnection.GetConnection();
cmd.Connection=con;
//打开连接
cmd.Connection.Open();
//添加参数并赋值
//@studentid
SqlParameter parm=new SqlParameter("@studentid",SqlDbType.Int,4);
parm.Value=info.StudentID;
cmd.Parameters.Add(parm);
//@number
parm=new SqlParameter("@number",SqlDbType.VarChar,15);
parm.Value=info.Number;
cmd.Parameters.Add(parm);
//@name
parm=new SqlParameter("@name",SqlDbType.VarChar,20);
parm.Value=info.Name;
cmd.Parameters.Add(parm);
//@password
parm=new SqlParameter("@password",SqlDbType.VarChar,30);
parm.Value=info.Password;
cmd.Parameters.Add(parm);
//@birthday
parm=new SqlParameter("@birthday",SqlDbType.DateTime,8);
parm.Value=info.Birthday;
cmd.Parameters.Add(parm);
//@eyesight
parm=new SqlParameter("@eyesight",SqlDbType.Decimal,5);
parm.Value=info.Eyesight;
cmd.Parameters.Add(parm);
//执行SQL语句
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
//关闭连接
if(cmd.Connection!=null)
{
cmd.Connection.Close();
}
}
return info;
}
/// <summary>
/// 查找指定ID的学生信息
/// </summary>
/// <param name="studentID"></param>
/// <returns>DataTable</returns>
public DataTable Select(int studentID)
{
string cmdText="select * from StudentInfo where studentid=@studentid";
SqlCommand cmd=new SqlCommand(cmdText);
//定义数据集
DataSet dst=new DataSet();
try
{
//设置连接
SqlConnection con=DBConnection.GetConnection();
cmd.Connection=con;
//打开连接
cmd.Connection.Open();
//添加参数并赋值
SqlParameter parm=new SqlParameter("@studentid",SqlDbType.Int,4);
parm.Value=studentID;
cmd.Parameters.Add(parm);
//创建数据适配器
SqlDataAdapter dad=new SqlDataAdapter(cmd);
//填充数据集
dad.Fill(dst,"StudentInfo");
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
//关闭连接
if(cmd.Connection!=null)
{
cmd.Connection.Close();
}
}
return dst.Tables["StudentInfo"];
}
/// <summary>
/// 查找所有学生信息
/// </summary>
/// <returns>DataTable</returns>
public DataTable Select()
{
string cmdText="select * from StudentInfo";
SqlCommand cmd=new SqlCommand(cmdText);
//定义数据集
DataSet dst=new DataSet();
try
{
//设置连接
SqlConnection con=DBConnection.GetConnection();
cmd.Connection=con;
//打开连接
cmd.Connection.Open();
//创建数据适配器
SqlDataAdapter dad=new SqlDataAdapter(cmd);
//填充数据集
dad.Fill(dst,"StudentInfo");
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
finally
{
//关闭连接
if(cmd.Connection!=null)
{
cmd.Connection.Close();
}
}
return dst.Tables["StudentInfo"];
}
}
}
表示层(略)
。。。。。。。
5282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
