数据访问基础类 SQLHelper
2013年08月11日
⁄ 综合
⁄ 共 6200字 ⁄ 字号
小 中 大
SQLHelper
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System.Xml;
using System.Reflection;
namespace DBUtility
...{
/**//// <summary>
/// 数据访问基础类(基于SQLServer)
/// </summary>
public abstract class SqlHelper
...{
//数据库连接字符串(web.config来配置)
public static readonly string LocalSqlServer = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
/**//// <summary>
/// 通用分页存储过程
/// </summary>
/// <param name="connectionString">连接</param>
/// <param name="tblName">要显示的表或多个表的连接</param>
/// <param name="fldName">要显示的字段列表,可为Null,表示*</param>
/// <param name="pageSize">每页显示的记录个数</param>
/// <param name="pageIndex">要显示那一页的记录</param>
/// <param name="fldSort">排序字段列表或条件</param>
/// <param name="Sort">排序方法，False为升序，True为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')</param>
/// <param name="strCondition">查询条件,不需where,以And开始,可为Null,表示""</param>
/// <param name="ID">主表的主键</param>
/// <param name="Disk">是否添加查询字段的 DISTINCT 默认False不添加/True添加</param>
/// <param name="pageCount">查询结果分页后的总页数</param>
/// <param name="Counts">查询到的记录数</param>
/// <param name="strSql">最后返回的SQL语句</param>
/// <returns>查询当前页的数据集</returns>
public static DataSet PageList(string connectionString, string tblName, string fldName, int pageSize, int pageIndex,
string fldSort, bool Sort, string strCondition, string ID, bool Dist,
out int pageCount, out int Counts, out string strSql)
...{
SqlParameter[] parameters =...{ new SqlParameter("@tblName",SqlDbType.NVarChar,200),
new SqlParameter("@fldName",SqlDbType.NVarChar,500),
new SqlParameter("@pageSize",SqlDbType.Int),
new SqlParameter("@page",SqlDbType.Int),
new SqlParameter("@fldSort",SqlDbType.NVarChar,200),
new SqlParameter("@Sort",SqlDbType.Bit),
new SqlParameter("@strCondition",SqlDbType.NVarChar,1000),
new SqlParameter("@ID",SqlDbType.NVarChar,150),
new SqlParameter("@Dist",SqlDbType.Bit),
new SqlParameter("@pageCount",SqlDbType.Int),
new SqlParameter("@Counts",SqlDbType.Int),
new SqlParameter("@strSql",SqlDbType.NVarChar,1000)};
parameters[0].Value = tblName;
parameters[1].Value = (fldName == null) ? "*" : fldName;
parameters[2].Value = (pageSize == 0) ? int.Parse(ConfigurationManager.AppSettings["PageSize"]) : pageSize;
parameters[3].Value = pageIndex;
parameters[4].Value = fldSort;
parameters[5].Value = Sort;
parameters[6].Value = strCondition == null ? "" : strCondition;
parameters[7].Value = ID;
parameters[8].Value = Dist;
parameters[9].Direction = ParameterDirection.Output;
parameters[10].Direction = ParameterDirection.Output;
parameters[11].Direction = ParameterDirection.Output;
DataSet ds = RunProcedure(connectionString, "PageList", parameters, "ds");
pageCount = (int)parameters[9].Value;
Counts = (int)parameters[10].Value;
strSql = parameters[11].Value.ToString();
return ds;
}
执行简单SQL语句#region 执行简单SQL语句
/**//// <summary>
/// 获取表某个字段的最大值
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public static int GetMaxID(string connectionString, string FieldName, string TableName)
...{
string strSql = "select max(" + FieldName + ") from " + TableName;
DataSet ds = Query(connectionString, strSql);
if (ds.Tables[0].Rows[0][0] != DBNull.Value)
return int.Parse(ds.Tables[0].Rows[0][0].ToString());
else
return 0;
}
/**//// <summary>
/// 检测一个记录是否存在(SqlParameter语句方式)
/// </summary>
/// <param name="strSql"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static bool Exists(string connectionString, string strSql, params SqlParameter[] cmdParms)
...{
DataSet ds = Query(connectionString, strSql, cmdParms);
return int.Parse(ds.Tables[0].Rows[0][0].ToString()) > 0;
}
/**//// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string connectionString, string SQLString)
...{
using (SqlConnection connection = new SqlConnection(connectionString))
...{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
...{
try
...{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException E)
...{
connection.Close();
throw new Exception(E.Message);
}
}
}
}
/**//// <summary>
/// 执行SQL语句，返回记录的个数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteCountSql(string connectionString, string SQLString)