系统分析与设计
using System.Collections.Generic; using System.Linq; using System.Text; using System.Data;
using System.Data.SqlClient; using System.Configuration;
namespace DAL {
public class SQLHelper {
private SqlConnection conn = null; private SqlCommand cmd = null; private SqlDataReader sdr = null;
public SQLHelper() {
string connStr = ConfigurationManager.ConnectionStrings
[\].ConnectionString;
conn = new SqlConnection(connStr); }
private SqlConnection GetConn() {
if (conn.State == ConnectionState.Closed) {
conn.Open(); }
return conn; }
///
/// 执行不带参数的增删改SQL语句或存储过程 ///
/// /// ///
public int ExecuteNonQuery(string cmdText, CommandType ct) {
int res; try {
cmd = new SqlCommand(cmdText, GetConn());
6
系统分析与设计
cmd.CommandType = ct;
res = cmd.ExecuteNonQuery(); }
catch (Exception ex) {
throw ex; }
finally {
if (conn.State == ConnectionState.Open) {
conn.Close(); } }
return res; }
///
/// 执行带参数的SQL增删改语句或存储过程 ///
/// /// ///
public int ExecuteNonQuery(string sql, SqlParameter[] paras,
CommandType ct)
{
int res;
using (cmd = new SqlCommand(sql, GetConn())) {
cmd.CommandType = ct;
cmd.Parameters.AddRange(paras); res = cmd.ExecuteNonQuery(); }
return res; }
///
/// 该方法执行传入的SQL查询语句或存储过程 ///
///
/// ///
7
系统分析与设计
public DataTable ExecuteQuery(string cmdText,CommandType ct) {
DataTable dt = new DataTable();
cmd = new SqlCommand(cmdText, GetConn()); cmd.CommandType = ct; using (sdr = cmd.ExecuteReader
(CommandBehavior.CloseConnection))
{
dt.Load(sdr); }
return dt; }
///
/// 执行带参数的查询SQL语句或存储过程 ///
/// /// /// ///
paras, CommandType ct)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(cmdText, GetConn()); cmd.CommandType = ct;
cmd.Parameters.AddRange(paras); using (sdr = cmd.ExecuteReader
(CommandBehavior.CloseConnection))
{
dt.Load(sdr); }
return dt; } }
} 2.2.2、 DAL层实现数据库对表(CategoryDAO、CommentDAO、CompanyManageDAO、ManageDAO、UserManageDAO)的基本操作 /*
*招聘信息类别表的操作类 * 王溢文 */
using System;
using System.Collections.Generic;
8
系统分析与设计
using System.Linq; using System.Text; using System.Data;
using System.Data.SqlClient; using Model;
namespace DAL {
///
/// 招聘信息类别表的操作类 ///
public class CategoryDAO {
private SQLHelper sqlhelper = null; public CategoryDAO() {
sqlhelper = new SQLHelper(); }
///
/// 取出当前所有行业分类 ///
///
public DataTable SelectAll() {
DataTable dt = new DataTable();
string sql = \;
dt = sqlhelper.ExecuteQuery(sql,CommandType.Text); return dt; }
///
/// ///
public bool Insert(string caName) {
bool flag = false;
string sql = \into category(name) values(@caName)\;
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter(\,caName) }; int res =
9
系统分析与设计
sqlhelper.ExecuteNonQuery(sql,paras,CommandType.Text); if (res > 0) {
flag = true; }
return flag; }
///
/// 删除分类（连同其下招聘信息及询问一起删除） ///
///
public bool Delete(string id) {
bool flag = false;
string sql = \; SqlParameter[] paras = new SqlParameter[] {
new SqlParameter(\,id) };
int res = sqlhelper.ExecuteNonQuery(sql, paras,
CommandType.Text);
if (res > 0) {
flag = true; }
return flag; }
///
///
/// ///
public bool Update(Category ca) {
bool flag = false;
string sql = \category set [name]=@caName where id
= @id\;
SqlParameter[] paras = new SqlParameter[]{ new SqlParameter(\,ca.Id),
new SqlParameter(\,ca.Name) };
int res = sqlhelper.ExecuteNonQuery(sql, paras,
10