C#中使用MVC架构(二）
C#中使用MVC架构(二）
- 实现数据访问层DAL
定义一个基本数据接口IBaseDao,作各数据访问的总中介，也就是说业务层只通过IBaseDao的接口对象，访问数据层，具体访问哪个数据模型操作类，是通过注入IBaseDao的实现类来完成的，可根据需要生成具体数据模型操作类，实现接口定义的抽象方法，封装操作具体数据模型的SQL语句。还需要定义一个数据访问操作类，封装基础数据操作。
1.新建数据访问类，实现sql-server数据库的链接及数据CRUD操作
public class AdoConnector
{
//Provider=SQLNCLI10;Data Source=NAPMLGNQRNJXJOR;Persist Security Info=True;Password=sa;User ID=sa;Initial Catalog=SuperMarket
const String strConn = @"Data Source=127.0.0.1;Persist Security Info=True;Password=123456;User ID=sa;Initial Catalog=newsDB";
SqlConnection conn = null;
String errorMsg = "数据查找失败！";
public String ErrorMsg
{
get
{
return this.errorMsg;
}
}
public SqlConnection Conn
{
get
{
if (this.conn == null)
{
this.conn = new SqlConnection(strConn);
}
return this.conn;
}
set
{
this.conn = value;
}
}
public AdoConnector()
{
if (this.conn == null)
{
this.conn = new SqlConnection(strConn);
}
}
~AdoConnector()
{
if (this.conn != null)
{
// this.conn.Dispose();
}
}
/***
* 参数化查询
***/
public SqlDataReader QueryReader(String sql,params SqlParameter[] param)
{
OpenConnection();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = this.conn;
sqlCmd.CommandText = sql;
for(int i = 0; i < param.Length; i++)
{
sqlCmd.Parameters.Add(param[i]);
}
SqlDataReader sdr =sqlCmd.ExecuteReader();
if (sdr != null && sdr.Read())
{
return sdr;
}else
{
this.errorMsg = "数据查询失败";
return null;
}
}
public SqlDataReader QueryReader(String sql, SqlParameter param)
{
OpenConnection();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = this.conn;
sqlCmd.CommandText = sql;
sqlCmd.Parameters.Add(param);
SqlDataReader sdr = sqlCmd.ExecuteReader();
if (sdr != null && sdr.Read())
{
return sdr;
}
else
{
this.errorMsg = "数据查询失败";
return null;
}
}
public SqlDataReader QueryGet(String sql,SqlParameter param)
{
OpenConnection();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = this.conn;
sqlCmd.CommandText = sql;
sqlCmd.Parameters.Add(param);
SqlDataReader sdr = sqlCmd.ExecuteReader();
if (sdr != null && sdr.Read())
{
return sdr;
}
else
{
this.errorMsg = "数据查询失败";
return null;
}
}
public Object QueryScalar(String strSql)
{
try
{
OpenConnection();
SqlCommand sqlCmd = new SqlCommand(strSql, this.conn);
Object obj = sqlCmd.ExecuteScalar();
if (obj != null)
{
return obj;
}
else
{
return null;
}
}
catch (Exception ex)
{
errorMsg = "发生异常：" + ex.Message;
return null;
}
finally
{
this.conn.Close();
}
}
public int QueryDelete(String sql, SqlParameter id)
{
this.conn.Open();
SqlCommand cmd = new SqlCommand();
//填写SqlCommand对象的参数
cmd.Connection = this.conn;
cmd.CommandText = sql;
cmd.Parameters.Add(id);
return cmd.ExecuteNonQuery();
}
public int QueryInsert(String sql,params SqlParameter[] param)
{
this.conn.Open();
SqlCommand cmd = new SqlCommand();
//填写SqlCommand对象的参数
cmd.Connection = this.conn;
cmd.CommandText = sql;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
int num = cmd.ExecuteNonQuery();
return num;
}
public int QueryUpdate(String sql,params SqlParameter[] param)
{
this.conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.conn;
cmd.CommandText = sql;
for(int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
int num = cmd.ExecuteNonQuery();
return num;
}
public bool QueryUpdate(String strSql)
{//插入、修改记录
this.conn.Open();
int rows = 0;
try
{
SqlCommand sqlCmd = new SqlCommand(strSql, this.Conn);
rows = sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
errorMsg = "发生异常：" + ex.Message;
}
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
public SqlDataReader QueryReader(String strSql)
{
SqlDataReader sdr = null;
try
{
this.conn.Open();
SqlCommand sqlCmd = new SqlCommand(strSql, this.Conn);
sdr = sqlCmd.ExecuteReader();
}
catch (Exception ex)
{
errorMsg = "发生异常：" + ex.Message;
}
return sdr;
}
public DataSet QueryAdapter(String strSql)
{
DataSet ds = null;
SqlDataAdapter sda = null;
try
{
this.conn.Open();
SqlCommand sqlCmd = new SqlCommand(strSql, this.conn);
//sdr = sqlCmd.ExecuteReader();
sda = new SqlDataAdapter(sqlCmd);
ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch (Exception ex)
{
errorMsg = "发生异常：" + ex.Message;
}
//finally
//{
// this.conn.Close();
//}
return ds;
}
public void OpenConnection()
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
else if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
}
public void CloseConnection()
{
if (conn.State == ConnectionState.Open
|| conn.State == ConnectionState.Broken)
{
conn.Close();
}
}
}
2.创建基本数据访问接口，定义基本泛型CRUD操作
interface IBaseDao<T>
{
int Add(T obj);
int Delete(int id);
int Edit(T obj);
T Get(int id);
SqlDataReader Get(String name);
T GetByName(String name);
DataSet FindAll();
}
3.创建UserDao ，实现IBaseDao接口，操作User模型类，实现对users数据表的ORM访问。
public class UserDao : IBaseDao<User>
{
public AdoConnector ado;
public AdoConnector Ado
{
get
{
return ado;
}
set
{
ado = value;
}
}
public UserDao() {
if (ado == null)
{
ado = new AdoConnector();
}
}
~UserDao()
{
if (ado != null)
{
ado.CloseConnection();
}
}
public int Add(User obj)
{
String sql = "insert into users(uname,upass) values(@uname,@upass)";
int num=ado.QueryInsert(sql, new SqlParameter[] { new SqlParameter("@uname", obj.Uname), new SqlParameter("@upass", obj.Upass) });
ado.CloseConnection();
return num;
}
public int Delete(int id)
{
String sql = "delete from users where uid=@uid";
int num= ado.QueryDelete(sql, new System.Data.SqlClient.SqlParameter("@uid",id));
ado.CloseConnection();
return num;
}
public int Edit(User obj)
{
String sql = "update users set uname=@uname,upass=@upass where uid=@uid";
int num = ado.QueryUpdate(sql, new SqlParameter[]
{ new SqlParameter("@uname",obj.Uname),new SqlParameter("@upass",obj.Upass),new SqlParameter("uid",obj.Uid) });
ado.CloseConnection();
return num;
}
public DataSet FindAll()
{
String sql = "SELECT TOP 100 [uid] as 序号 ,[uname] as 登陆名称 ,[upass] as 密码 FROM [newsDB].[dbo].[users]";
DataSet ds =ado.QueryAdapter(sql);
ado.CloseConnection();
return ds;
}
public User Get(int id)
{
String sql = "select * from users where uid=@uid";
SqlDataReader ds = ado.QueryReader(sql, new SqlParameter("@uid", id));
User user = new User(Convert.ToInt32(ds["uid"]),(string)ds["uname"],(string)ds["upass"]);
ado.CloseConnection();
return user;
}
public User GetByName(string name)
{
throw new NotImplementedException();
}
public SqlDataReader Get(String name)
{
String sql = "select * from users where uname=@uname";
SqlDataReader sdr = ado.QueryReader(sql, new SqlParameter("@uname",name));
//ado.CloseConnection();
return sdr;
}
}
4.创建TopicDao ，实现IBaseDao接口，操作Topic模型类，实现对topics数据表的ORM访问。
public class TopicDao : IBaseDao<Topic>
{
AdoConnector ado;
public AdoConnector Ado
{
get
{
return ado;
}
set
{
ado = value;
}
}
public TopicDao() {
ado = new AdoConnector();
}
public int Add(Topic obj)
{
String sql = "insert into topic(tname) values(@tname)";
int num = ado.QueryInsert(sql, new System.Data.SqlClient.SqlParameter[] {new System.Data.SqlClient.SqlParameter("@tname",obj.Tname)});
ado.CloseConnection();
return num;
}
public int Delete(int id)
{
String sql = "delete from topic where tid=@tid";
int num=ado.QueryDelete(sql, new SqlParameter("@tid", id));
ado.CloseConnection();
return num;
}
public int Edit(Topic obj)
{
String sql = "update topic set tname=@tname where uid=@uid";
int num = ado.QueryUpdate(sql, new SqlParameter[] { new SqlParameter("@tname", obj.Tname), new SqlParameter("@tid", obj.Tid) });
ado.CloseConnection();
return num;
}
public DataSet FindAll()
{
String sql = "SELECT TOP 100 [tid] as 序号 ,[tname] as 栏目名称 FROM [newsDB].[dbo].[topic]";
DataSet ds = ado.QueryAdapter(sql);
ado.CloseConnection();
return ds;
}
public Topic Get(int id)
{
String sql = "select * from topic where tid=@tid";
SqlDataReader sdr=ado.QueryGet(sql, new SqlParameter("@tid", id));
Topic topic = new Topic((int)sdr["tid"],(string)sdr["tname"]);
return topic;
}
public Topic GetByName(string name)
{
throw new NotImplementedException();
}
public SqlDataReader Get(string name)
{
String sql = "select * from topic where tname=@tname";
SqlDataReader sdr = ado.QueryGet(sql, new SqlParameter("@tname", name));
ado.CloseConnection();
return sdr;
}
}
5.创建NewsDao ，实现IBaseDao接口，操作News模型类，实现对news数据表的ORM访问。
public class NewsDao : IBaseDao<News>
{
public AdoConnector ado;
public NewsDao()
{
this.ado = new AdoConnector();
}
public int Add(News obj)
{
String sql = "insert into news(ntid,ntitle,nauthor,ncreatedate,ncontent,nsumary,nmodifydate,npicpath) " +
"values(@ntid,@ntitle,@nauthor,GETDATE(),@ncontent,@nsumary,GETDATE(),@npicpath)";
int num = ado.QueryInsert(sql, new SqlParameter[] {new SqlParameter("@ntid",obj.Ntid),
new SqlParameter("@ntitle",obj.Ntitle),new SqlParameter("@nauthor",obj.Nauthor),
new SqlParameter("@ncontent",obj.Ncontent),
new SqlParameter("@nsumary",obj.Summary),
new SqlParameter("@npicpath",obj.Npicpath)});
ado.CloseConnection();
return num;
}
public int Delete(int id)
{
String sql = "delete from news where nid=@nid";
int num = ado.QueryDelete(sql, new System.Data.SqlClient.SqlParameter("@nid", id));
ado.CloseConnection();
return num;
}
public int Edit(News obj)
{
String sql = "update news set ntid=@ntid,ntitle=@ntitle,nauthor=@nauthor,nmodifydate=GETDATE(),ncontent=@ncontent,nsumary=@nsumary,npicpath=@npicpath where nid=@nid";
int num = ado.QueryUpdate(sql, new SqlParameter[] {new SqlParameter("@ntid",obj.Ntid),
new SqlParameter("@ntitle",obj.Ntitle),new SqlParameter("@nauthor",obj.Nauthor),
new SqlParameter("@ncontent",obj.Ncontent),
new SqlParameter("@nsumary",obj.Summary), new SqlParameter("@npicpath",obj.Npicpath),
new SqlParameter("@nid",obj.Nid)});
ado.CloseConnection();
return num;
}
public DataSet FindAll()
{
String sql = "SELECT TOP 100 [nid] as 序号 ,[ntitle] as 标题,[tname] as 栏目,[nauthor] as 新闻作者,[ncreatedate] as 创建时间,[nmodifydate] as 修改时间 ,[nsumary] as 简介 " +
"FROM [newsDB].[dbo].[news] as nw,[newsDB].[dbo].[topic] as tp where nw.ntid=tp.tid";
DataSet ds=ado.QueryAdapter(sql);
ado.CloseConnection();
return ds;
}
public News Get(int id)
{
String sql = "select * from news where nid=@nid";
SqlDataReader sdr = ado.QueryGet(sql, new SqlParameter("@nid",id));
News news = new News((int)sdr["nid"],(int)sdr["ntid"],(string)sdr["ntitle"],(string)sdr["nauthor"],
(DateTime)sdr["ncreatedate"],(string)sdr["npicpath"],(string)sdr["ncontent"],
(DateTime)sdr["nmodifydate"],(string)sdr["nsumary"]);
ado.CloseConnection();
return news;
}
public SqlDataReader Get(string name)
{
String sql = "select * from news where ntitle=@ntitle";
SqlDataReader sdr = ado.QueryGet(sql, new SqlParameter("@ntitle", name));
//ado.CloseConnection();
return sdr;
}
public News GetByName(string name)
{
throw new NotImplementedException();
}
}
本文地址：https://blog.csdn.net/yytrobot/article/details/107340643
下一篇： 学习python的第四天