SqlHelper编写

现在用的EF比较多，渐渐的ADO.NET比较少用了。
今天就当做是SqlHelper的小小总结。

SqlHelper.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Sockets;

namespace Utility
{
///


/// SQL Server数据库访问助手类
/// 本类为静态类 不可以被实例化 需要使用时直接调用即可
/// Copyright © 2013 Wedn.Net
///
public static partial class SqlHelper
{
private static readonly string[] localhost = new[] { "localhost", ".", "(local)" };
///
/// 数据库连接字符串
///
private readonly static string connStr;
static SqlHelper()
{
var conn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
if (conn!=null)
{
connStr = conn.ConnectionString;
}
}

region 数据库检测

region 测试数据库服务器连接 +static bool TestConnection(string host, int port, int millisecondsTimeout)

///


/// 采用Socket方式，测试数据库服务器连接
///
/// 服务器主机名或IP
/// 端口号
/// 等待时间：毫秒
/// 链接异常
///
public static bool TestConnection(string host, int port, int millisecondsTimeout)
{
host = localhost.Contains(host) ? "127.0.0.1" : host;
using (var client = new TcpClient())
{
try
{
var ar = client.BeginConnect(host, port, null, null);
ar.AsyncWaitHandle.WaitOne(millisecondsTimeout);
return client.Connected;
}
catch (Exception)
{
throw;
}
}
}
#endregion

region 检测表是否存在 + static bool ExistsTable(string table)

///


/// 检测表是否存在
///
/// 要检测的表名
///
public static bool ExistsTable(string table)
{
string sql = "select count(1) from sysobjects where id = object_id(N'[" + table + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
object res = ExecuteScalar(sql);
return (Object.Equals(res, null)) || (Object.Equals(res, System.DBNull.Value));
}
#endregion

region 判断是否存在某张表的某个字段 +static bool ExistsColumn(string table, string column)

///


/// 判断是否存在某张表的某个字段
///
/// 表名称
/// 列名称
/// 是否存在
public static bool ExistsColumn(string table, string column)
{
string sql = "select count(1) from syscolumns where [id]=object_id('N[" + table + "]') and [name]='" + column + "'";
object res = ExecuteScalar(sql);
if (res == null)
return false;
return Convert.ToInt32(res) > 0;
}
#endregion

region 判断某张表的某个字段中是否存在某个值 +static bool ColumnExistsValue(string table, string column, string value)

///


/// 判断某张表的某个字段中是否存在某个值
///
/// 表名称
/// 列名称
/// 要判断的值
/// 是否存在
public static bool ColumnExistsValue(string table, string column, string value)
{
string sql = "SELECT count(1) FROM [" + table + "] WHERE [" + column + "]=@Value;";
object res = ExecuteScalar(sql, new SqlParameter("@Value", value));
if (res == null)
return false;
return Convert.ToInt32(res) > 0;
}
#endregion

endregion

region 公共方法

region 获取指定表中指定字段的最大值, 确保字段为INT类型

///


/// 获取指定表中指定字段的最大值, 确保字段为INT类型
///
/// 字段名
/// 表名
/// 最大值
public static int QueryMaxId(string fieldName, string tableName)
{
string sql = string.Format("select max([{0}]) from [{1}];", fieldName, tableName);
object res = ExecuteScalar(sql);
if (res == null)
return 0;
return Convert.ToInt32(res);
}
#endregion

region 生成查询语句

region 生成分页查询数据库语句 +static string GenerateQuerySql(string table, string[] columns, int index, int size, string wheres, string orderField, bool isDesc = true)

///


/// 生成分页查询数据库语句, 返回生成的T-SQL语句
///
/// 表名
/// 列集合, 多个列用英文逗号分割(colum1,colum2...)
/// 页码(即第几页)(传入-1则表示忽略分页取出全部)
/// 显示页面大小(即显示条数)
/// 条件语句(忽略则传入null)
/// 排序字段(即根据那个字段排序)(忽略则传入null)
/// 排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)
/// 生成的T-SQL语句
public static string GenerateQuerySql(string table, string[] columns, int index, int size, string where, string orderField, bool isDesc = true)
{
if (index == 1)
{
// 生成查询第一页SQL
return GenerateQuerySql(table, columns, size, where, orderField, isDesc);
}
if (index < 1)
{
// 取全部数据
return GenerateQuerySql(table, columns, where, orderField, isDesc);
}
if (string.IsNullOrEmpty(orderField))
{
throw new ArgumentNullException("orderField");
}
// 其他情况, 生成row_number分页查询语句
// SQL模版
const string format = @"select {0} from
(
select ROW_NUMBER() over(order by [{1}] {2}) as num, {0} from [{3}] {4}
)
as tbl
where
tbl.num between ({5}-1){6} + 1 and {5};";
// where语句组建
where = string.IsNullOrEmpty(where) ? string.Empty : "where " + where;
// 查询字段拼接
string column = columns != null && columns.Any() ? string.Join(" , ", columns) : "";
return string.Format(format, column, orderField, isDesc ? "desc" : string.Empty, table, where, index, size);
}
#endregion
#region 生成查询数据库语句查询全部 +static string GenerateQuerySql(string table, string[] columns, string wheres, string orderField, bool isDesc = true)
///
/// 生成查询数据库语句查询全部, 返回生成的T-SQL语句
///
/// 表名
/// 列集合
/// 条件语句(忽略则传入null)
/// 排序字段(即根据那个字段排序)(忽略则传入null)
/// 排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)
/// 生成的T-SQL语句
public static string GenerateQuerySql(string table, string[] columns, string where, string orderField, bool isDesc = true)
{
// where语句组建
where = string.IsNullOrEmpty(where) ? string.Empty : "where " + where;
// 查询字段拼接
string column = columns != null && columns.Any() ? string.Join(" , ", columns) : "";
const string format = "select {0} from [{1}] {2} {3} {4}";
return string.Format(format, column, table, where,
string.IsNullOrEmpty(orderField) ? string.Empty : "order by " + orderField,
isDesc && !string.IsNullOrEmpty(orderField) ? "desc" : string.Empty);
}
#endregion

region 生成分页查询数据库语句查询第一页 +static string GenerateQuerySql(string table, string[] columns, int size, string where, string orderField, bool isDesc = true)

///


/// 生成分页查询数据库语句查询第一页, 返回生成的T-SQL语句
///
/// 表名
/// 列集合
/// 显示页面大小(即显示条数)
/// 条件语句(忽略则传入null)
/// 排序字段(即根据那个字段排序)(忽略则传入null)
/// 排序方式(0:降序(desc)|1:升序(asc))(忽略则传入-1)
/// 生成的T-SQL语句
public static string GenerateQuerySql(string table, string[] columns, int size, string where, string orderField, bool isDesc = true)
{
// where语句组建
where = string.IsNullOrEmpty(where) ? string.Empty : "where " + where;
// 查询字段拼接
string column = columns != null && columns.Any() ? string.Join(" , ", columns) : "*";
const string format = "select top {0} {1} from [{2}] {3} {4} {5}";
return string.Format(format, size, column, table, where,
string.IsNullOrEmpty(orderField) ? string.Empty : "order by " + orderField,
isDesc && !string.IsNullOrEmpty(orderField) ? "desc" : string.Empty);
}
#endregion
#endregion

region 将一个SqlDataReader对象转换成一个实体类对象 +static TEntity MapEntity(SqlDataReader reader) where TEntity : class,new()

///


/// 将一个SqlDataReader对象转换成一个实体类对象
///
/// 实体类型
/// 当前指向的reader
/// 实体对象
public static TEntity MapEntity(SqlDataReader reader) where TEntity : class,new()
{
try
{
var props = typeof(TEntity).GetProperties();
var entity = new TEntity();
foreach (var prop in props)
{
if (prop.CanWrite)
{
try
{
var index = reader.GetOrdinal(prop.Name);
var data = reader.GetValue(index);
if (data != DBNull.Value)
{
prop.SetValue(entity, Convert.ChangeType(data, prop.PropertyType), null);
}
}
catch (IndexOutOfRangeException)
{
continue;
}
}
}
return entity;
}
catch
{
return null;
}
}
#endregion

endregion

region SQL执行方法

region ExecuteNonQuery +static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters)

///


/// 执行一个非查询的T-SQL语句，返回受影响行数，如果执行的是非增、删、改操作，返回-1
///
/// 要执行的T-SQL语句
/// 参数列表
///
/// 受影响的行数
public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters)
{
return ExecuteNonQuery(cmdText, CommandType.Text, parameters);
}
#endregion

region ExecuteNonQuery +static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] parameters)

///


/// 执行一个非查询的T-SQL语句，返回受影响行数，如果执行的是非增、删、改操作，返回-1
///
/// 要执行的T-SQL语句
/// 命令类型
/// 参数列表
///
/// 受影响的行数
public static int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
if (parameters != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
}
cmd.CommandType = type;
try
{
conn.Open();
int res = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return res;
}
catch (System.Data.SqlClient.SqlException e)
{
conn.Close();
throw e;
}
}
}
}
#endregion

region ExecuteScalar +static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)

///


/// 执行一个查询的T-SQL语句，返回第一行第一列的结果
///
/// 要执行的T-SQL语句
/// 参数列表
///
/// 返回第一行第一列的数据
public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)
{
return ExecuteScalar(cmdText, CommandType.Text, parameters);
}
#endregion

region ExecuteScalar +static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters)

///


/// 执行一个查询的T-SQL语句，返回第一行第一列的结果
///
/// 要执行的T-SQL语句
/// 命令类型
/// 参数列表
///
/// 返回第一行第一列的数据
public static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
if (parameters != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
}
cmd.CommandType = type;
try
{
conn.Open();
object res = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return res;
}
catch (System.Data.SqlClient.SqlException e)
{
conn.Close();
throw e;
}
}
}
}
#endregion

region ExecuteReader +static void ExecuteReader(string cmdText, Action action, params SqlParameter[] parameters)

///


/// 利用委托 执行一个大数据查询的T-SQL语句
///
/// 要执行的T-SQL语句
/// 传入执行的委托对象
/// 参数列表
///
public static void ExecuteReader(string cmdText, Action action, params SqlParameter[] parameters)
{
ExecuteReader(cmdText, action, CommandType.Text, parameters);
}
#endregion

region ExecuteReader +static void ExecuteReader(string cmdText, Action action, CommandType type, params SqlParameter[] parameters)

///


/// 利用委托 执行一个大数据查询的T-SQL语句
///
/// 要执行的T-SQL语句
/// 传入执行的委托对象
/// 命令类型
/// 参数列表
///
public static void ExecuteReader(string cmdText, Action action, CommandType type, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
if (parameters != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
}
cmd.CommandType = type;
try
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
action(reader);
}
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException e)
{
conn.Close();
throw e;
}
}
}
}
#endregion

region ExecuteReader +static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] parameters)

///


/// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象, 如果出现SQL语句执行错误, 将会关闭连接通道抛出异常
/// ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
///
/// 要执行的T-SQL语句
/// 参数列表
///
/// SqlDataReader对象
public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] parameters)
{
return ExecuteReader(cmdText, CommandType.Text, parameters);
}
#endregion

region ExecuteReader +static SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters)

///


/// 执行一个查询的T-SQL语句, 返回一个SqlDataReader对象, 如果出现SQL语句执行错误, 将会关闭连接通道抛出异常
/// ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
///
/// 要执行的T-SQL语句
/// 命令类型
/// 参数列表
///
/// SqlDataReader对象
public static SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connStr);
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
if (parameters != null)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
}
cmd.CommandType = type;
conn.Open();
try
{
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return reader;
}
catch (System.Data.SqlClient.SqlException ex)
{
//出现异常关闭连接并且释放
conn.Close();
throw ex;
}
}
}
#endregion

region ExecuteDataSet +static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] parameters)

///


/// 执行一个查询的T-SQL语句, 返回一个离线数据集DataSet
///
/// 要执行的T-SQL语句
/// 参数列表
/// 离线数据集DataSet
public static DataSet ExecuteDataSet(string cmdText, params SqlParameter[] parameters)
{
return ExecuteDataSet(cmdText, CommandType.Text, parameters);
}
#endregion

region ExecuteDataSet +static DataSet ExecuteDataSet(string cmdText, CommandType type, params SqlParameter[] parameters)

///


/// 执行一个查询的T-SQL语句, 返回一个离线数据集DataSet
///
/// 要执行的T-SQL语句
/// 命令类型
/// 参数列表
/// 离线数据集DataSet
public static DataSet ExecuteDataSet(string cmdText, CommandType type, params SqlParameter[] parameters)
{
using (SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connStr))
{
using (DataSet ds = new DataSet())
{
if (parameters != null)
{
adapter.SelectCommand.Parameters.Clear();
adapter.SelectCommand.Parameters.AddRange(parameters);
}
adapter.SelectCommand.CommandType = type;
adapter.Fill(ds);
adapter.SelectCommand.Parameters.Clear();
return ds;
}
}
}
#endregion

region ExecuteDataTable +static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)

///


/// 执行一个数据表查询的T-SQL语句, 返回一个DataTable
///
/// 要执行的T-SQL语句
/// 参数列表
/// 查询到的数据表
public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
{
return ExecuteDataTable(cmdText, CommandType.Text, parameters);
}
#endregion

region ExecuteDataTable +static DataTable ExecuteDataTable(string cmdText, CommandType type, params SqlParameter[] parameters)

///


/// 执行一个数据表查询的T-SQL语句, 返回一个DataTable
///
/// 要执行的T-SQL语句
/// 命令类型
/// 参数列表
/// 查询到的数据表
public static DataTable ExecuteDataTable(string cmdText, CommandType type, params SqlParameter[] parameters)
{
return ExecuteDataSet(cmdText, type, parameters).Tables[0];
}
#endregion
#endregion
}
}

=====================================================

DAL调用

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Itcast.Mall.Model;
using Itcast.Mall.Utility;

namespace DAL
{
public partial class BookDAO
{
#region 向数据库中添加一条记录 +int Insert(Book model)
///


/// 向数据库中添加一条记录
///
/// 要添加的实体
/// 插入数据的ID
public int Insert(Book model)
{
#region SQL语句
const string sql = @"
INSERT INTO
目录

目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

)
VALUES (
@CategoryId
,@Title
,@Author
,@PublisherId
,@PublishDate
,@ISBN
,@WordsCount
,@UnitPrice
,@ContentDescription
,@AurhorDescription
,@EditorComment
,@TOC
);select @@IDENTITY";
#endregion
var res = SqlHelper.ExecuteScalar(sql,
new SqlParameter("@CategoryId", model.CategoryId),
new SqlParameter("@Title", model.Title),
new SqlParameter("@Author", model.Author),
new SqlParameter("@PublisherId", model.PublisherId),
new SqlParameter("@PublishDate", model.PublishDate),
new SqlParameter("@ISBN", model.ISBN),
new SqlParameter("@WordsCount", model.WordsCount),
new SqlParameter("@UnitPrice", model.UnitPrice),
new SqlParameter("@ContentDescription", model.ContentDescription),
new SqlParameter("@AurhorDescription", model.AurhorDescription),
new SqlParameter("@EditorComment", model.EditorComment),
new SqlParameter("@TOC", model.TOC)
);
return res == null ? 0 : Convert.ToInt32(res);
}
#endregion

region 删除一条记录 +int Delete(int id)

public int Delete(int id)
{
const string sql = "DELETE FROM [dbo].[Book] WHERE [Id] = @Id";
return SqlHelper.ExecuteNonQuery(sql, new SqlParameter("@Id", id));
}
#endregion

region 根据主键ID更新一条记录 +int Update(Book model)

///


/// 根据主键ID更新一条记录
///
/// 更新后的实体
/// 执行结果受影响行数
public int Update(Book model)
{
#region SQL语句
const string sql = @"
UPDATE
目录

SET
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

,
目录

WHERE [Id] = @Id";
#endregion
return SqlHelper.ExecuteNonQuery(sql,
new SqlParameter("@Id", model.Id),
new SqlParameter("@CategoryId", model.CategoryId),
new SqlParameter("@Title", model.Title),
new SqlParameter("@Author", model.Author),
new SqlParameter("@PublisherId", model.PublisherId),
new SqlParameter("@PublishDate", model.PublishDate),
new SqlParameter("@ISBN", model.ISBN),
new SqlParameter("@WordsCount", model.WordsCount),
new SqlParameter("@UnitPrice", model.UnitPrice),
new SqlParameter("@ContentDescription", model.ContentDescription),
new SqlParameter("@AurhorDescription", model.AurhorDescription),
new SqlParameter("@EditorComment", model.EditorComment),
new SqlParameter("@TOC", model.TOC)
);
}
#endregion

region 分页查询一个集合 +IEnumerable QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)

///


/// 分页查询一个集合
///
/// 页码
/// 页大小
/// 条件匿名类
/// 排序字段
/// 是否降序排序
/// 实体集合
public IEnumerable QueryList(int index, int size, object wheres, string orderField, bool isDesc = true)
{
var parameters = new List();
var whereBuilder = new System.Text.StringBuilder();
if (wheres != null)
{
var props = wheres.GetType().GetProperties();
foreach (var prop in props)
{
if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
{
// 操作符
whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
}
else
{
var val = prop.GetValue(wheres, null).ToString();
whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
parameters.Add(new SqlParameter("@" + prop.Name, val));
}
}
}
var sql = SqlHelper.GenerateQuerySql("Book", new[] { "Id", "CategoryId", "Title", "Author", "PublisherId", "PublishDate", "ISBN", "WordsCount", "UnitPrice", "ContentDescription", "AurhorDescription", "EditorComment", "TOC" }, index, size, whereBuilder.ToString(), orderField, isDesc);
using (var reader = SqlHelper.ExecuteReader(sql, parameters.ToArray()))
{
if (reader.HasRows)
{
while (reader.Read())
{
yield return SqlHelper.MapEntity(reader);
}
}
}
}
#endregion

region 查询单个模型实体 +Book QuerySingle(object wheres)

///


/// 查询单个模型实体
///
/// 条件匿名类
/// 实体
public Book QuerySingle(object wheres)
{
var list = QueryList(1, 1, wheres, null);
return list != null && list.Any() ? list.FirstOrDefault() : null;
}
#endregion

region 查询单个模型实体 +Book QuerySingle(int id)

///


/// 查询单个模型实体
///
/// key
/// 实体
public Book QuerySingle(int id)
{
const string sql = "SELECT TOP 1
目录

using (var reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
{
if (reader.HasRows)
{
reader.Read();
return SqlHelper.MapEntity(reader);
}
else
{
return null;
}
}
}
#endregion

region 查询条数 +int QueryCount(object wheres)

///


/// 查询条数
///
/// 查询条件
/// new { CategoryId = cat } =》 where category = @category and
///
/// 条数
public int QueryCount(object wheres)
{
// 定义参数列表和
var parameters = new List();
var whereBuilder = new System.Text.StringBuilder();
if (wheres != null)
{
// 如果传递了where条件 如果传递了where 条件
var props = wheres.GetType().GetProperties();
foreach (var prop in props)
{
if (prop.Name.Equals("__o", StringComparison.InvariantCultureIgnoreCase))
{
// 操作符
whereBuilder.AppendFormat(" {0} ", prop.GetValue(wheres, null).ToString());
}
else
{
// 当前是一个条件
var val = prop.GetValue(wheres, null).ToString();
whereBuilder.AppendFormat(" [{0}] = @{0} ", prop.Name);
parameters.Add(new SqlParameter("@" + prop.Name, val));
}
}
}
var sql = SqlHelper.GenerateQuerySql("Book", new[] { "COUNT(1)" }, whereBuilder.ToString(), string.Empty);
var res = SqlHelper.ExecuteScalar(sql, parameters.ToArray());
return res == null ? 0 : Convert.ToInt32(res);
}
#endregion
}
}

=====================================================
posted @ 2015-08-05 12:57  johnsondiao  阅读(292)  评论(0)    收藏  举报