数据访问层的第一种实现:Access SQL(2)_.Net教程

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using NGuestBook.IDAL;
using NGuestBook.Entity;

namespace NGuestBook.AccessDAL
{
public class AdminDAL : IAdminDAL
{
/**//// <summary>
/// 插入管理员
/// </summary>
/// <param name="admin">管理员实体类</param>
/// <returns>是否成功</returns>
public bool Insert(AdminInfo admin)
{
string SQLCommand = "insert into [TAdmin]([Name],[Password]) values(@name,@password)";
OleDbParameter[] parameters ={
new OleDbParameter("name",admin.Name),
new OleDbParameter("password",admin.Password)
};

try
{
AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
return true;
}
catch
{
return false;
}
}

/**//// <summary>
/// 删除管理员
/// </summary>
/// <param name="id">欲删除的管理员的ID</param>
/// <returns>是否成功</returns>
public bool Delete(int id)
{
string SQLCommand = "delete from [TAdmin] where [ID]=@id";
OleDbParameter[] parameters ={
new OleDbParameter("id",id)
};

try
{
AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
return true;
}
catch
{
return false;
}
}

/**//// <summary>
/// 更新管理员信息
/// </summary>
/// <param name="admin">管理员实体类</param>
/// <returns>是否成功</returns>
public bool Update(AdminInfo admin)
{
string SQLCommand = "update [TAdmin] set [Name]=@name,[Password]=@password where [ID]=@id";
OleDbParameter[] parameters ={
new OleDbParameter("id",admin.ID),
new OleDbParameter("name",admin.Name),
new OleDbParameter("password",admin.Password)
};

try
{
AccessDALHelper.ExecuteSQLNonQuery(SQLCommand, parameters);
return true;
}
catch
{
return false;
}
}

/**//// <summary>
/// 按ID取得管理员信息
/// </summary>
/// <param name="id">管理员ID</param>
/// <returns>管理员实体类</returns>
public AdminInfo GetByID(int id)
{
string SQLCommand = "select * from [TAdmin] where [ID]=@id";
OleDbParameter[] parameters ={
new OleDbParameter("id",id)
};

try
{
OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
if (!dataReader.HasRows)
{
throw new Exception();
}

AdminInfo admin = new AdminInfo();
dataReader.Read();
admin.ID=(int)dataReader["ID"];
admin.Name=(string)dataReader["Name"];
admin.Password=(string)dataReader["Password"];

return admin;
}
catch
{
return null;
}
}

/**//// <summary>
/// 按用户名及密码取得管理员信息
/// </summary>
/// <param name="name">用户名</param>
/// <param name="password">密码</param>
/// <returns>管理员实体类，不存在时返回null</returns>
public AdminInfo GetByNameAndPassword(string name, string password)
{
string SQLCommand = "select * from [TAdmin] where [Name]=@name and [Password]=@password";
OleDbParameter[] parameters ={
new OleDbParameter("name",name),
new OleDbParameter("password",password),
};

try
{
OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
if (!dataReader.HasRows)
{
throw new Exception();
}

AdminInfo admin = new AdminInfo();
dataReader.Read();
admin.ID = (int)dataReader["ID"];
admin.Name = (string)dataReader["Name"];
admin.Password = (string)dataReader["Password"];

return admin;
}
catch
{
return null;
}
}

/**//// <summary>
/// 按管理员名取得管理员信息
/// </summary>
/// <param name="name">管理员名</param>
/// <returns>管理员实体类</returns>
public AdminInfo GetByName(string name)
{
string SQLCommand = "select * from [TAdmin] where [Name]=@name";
OleDbParameter[] parameters ={
new OleDbParameter("name",name),
};

try
{
OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, parameters);
if (!dataReader.HasRows)
{
throw new Exception();
}

AdminInfo admin = new AdminInfo();
dataReader.Read();
admin.ID = (int)dataReader["ID"];
admin.Name = (string)dataReader["Name"];
admin.Password = (string)dataReader["Password"];

return admin;
}
catch
{
return null;
}
}

/**//// <summary>
/// 取得全部管理员信息
/// </summary>
/// <returns>管理员实体类集合</returns>
public IList<AdminInfo> GetAll()
{
string SQLCommand = "select * from [TAdmin]";
try
{
OleDbDataReader dataReader = AccessDALHelper.ExecuteSQLDataReader(SQLCommand, null);
if (!dataReader.HasRows)
{
throw new Exception();
}

IList<AdminInfo> adminCollection = new List<AdminInfo>();
int i = 0;
while (dataReader.Read())
{
AdminInfo admin = new AdminInfo();
admin.ID = (int)dataReader["ID"];
admin.Name = (string)dataReader["Name"];
admin.Password = (string)dataReader["Password"];

adminCollection.Add(admin);
i ;
}

return adminCollection;
}
catch
{
return null;
}
}
}
}