三层框架 手动设置SQLHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace MyCourseManageProject.Dal
{
public class SQLHelper
{
//定义数据库链接字符串(放到配置文件中)
//private static string ConnStrng = "service=.;uid=sa;pwd=ok;database=CourseManageDB";
//添加引用 Configuration;
private static string ConnString = ConfigurationManager.ConnectionStrings["SQLServerConnString"].ToString();

/// <summary>
/// 执行增删改操作的方法
/// </summary>
/// <param name="cmdText">SQL语句也可以是存储过程名称</param>
/// <param name="param">参数数组(可有可无</param>
/// <param name="isStoredProcedure">是否存储过程(默认false)</param>
/// <returns>返回受影响的行数</returns>
public static int Update(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)
{
//创建连接数据库对象
SqlConnection conn = new SqlConnection(ConnString);
//
SqlCommand cmd = new SqlCommand(cmdText, conn);
//参数不等于空
if (param != null)
{
cmd.Parameters.AddRange(param);
}
//是否存储过程
if (isStoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
try
{

conn.Open();
//读取数据库
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
//添加日志,抛出异常
throw new Exception("执行 public static int Update(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)方法时,出现异常:" + ex.Message);
}
finally
{
conn.Close();
}
}

/// <summary>
/// 执行返回一个只读结果集查询方法
/// </summary>
/// <param name="cmdText">SQL语句或存储过程</param>
/// <param name="param">SQL语句或存储过程中的参数数组</param>
/// <param name="isStoredProcedure">是否存在存储过程</param>
/// <returns>返回未读取数据的Reader对象</returns>
public static SqlDataReader GetReader(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)
{
//创建连接数据库对象
SqlConnection conn = new SqlConnection(ConnString);
//
SqlCommand cmd = new SqlCommand(cmdText, conn);
//参数不等于空
if (param != null)
{
cmd.Parameters.AddRange(param);
}
//是否存储过程
if (isStoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
try
{

conn.Open();
//读取数据库
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
//添加日志,抛出异常
throw new Exception("执行 public static SqlDataReader GetReader((string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)方法时,出现异常:" + ex.Message);
}

}

/// <summary>
/// 执行返回单一结果的方法;
/// </summary>
/// <param name="cmdText">SQL语句也可以是存储过程名称</param>
/// <param name="param">参数数组(可有可无</param>
/// <param name="isStoredProcedure">是否存储过程(默认false)</param>
/// <returns>返回受影响的数值</returns>
public static object GetScalar(string cmdText, SqlParameter[] param = null, bool isStoredProcedure = false)
{
//创建连接数据库对象
SqlConnection conn = new SqlConnection(ConnString);
//
SqlCommand cmd = new SqlCommand(cmdText, conn);
//参数不等于空
if (param != null)
{
cmd.Parameters.AddRange(param);
}
//是否存储过程
if (isStoredProcedure)
{
cmd.CommandType = CommandType.StoredProcedure;
}
try
{
conn.Open();
//读取数据库
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
//添加日志,抛出异常
throw new Exception("执行 public static object GetScalar(string cmdText,SqlParameter[] param=null,bool isStoredProcedure=false)方法时,出现异常:"+ex.Message);
}
finally
{
conn.Close();
}
}
}
}

 

 

App.config添加部分

<connectionStrings>
<add name="SQLServerConnString" connectionString= "server=.;uid=sa;pwd=ok;DataBase=CourseManageDB"></add>
</connectionStrings>