SQLhelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Sql
{
public class SqlHelper:IDisposable
{
protected SqlConnection Con;
protected SqlCommand Cmd;
protected SqlTransaction Tran;
public SqlHelper(string ConStr)
{
Con = new SqlConnection(ConStr);
Cmd = new SqlCommand();
Cmd.Connection = Con;
}
public T GetList(string Sql, Func func)
{
return GetList(Sql, func, null, false);
}
public T GetList(string Sql, Func func,SqlParameter[] Par)
{
return GetList(Sql, func, Par, false);
}
public T GetListSp(string Sql, Func func, SqlParameter[] Par)
{
return GetList(Sql, func, Par, true);
}
///

/// 执行SQL或者存储过程来返回信息
///

/// 必须用类
/// SQL或者存储过程名称
/// 执行委托
/// 参数
/// 是否为存储过程
/// 返回数据信息
public virtual T GetList(string Txt, Func func, SqlParameter[] Par, bool IsSp)
{
try
{
if (func == null) { return default(T); }
Cmd.CommandType = (IsSp ? CommandType.StoredProcedure : CommandType.Text);
Cmd.CommandText = Txt;
if (Par != null)
{
Cmd.Parameters.Clear();
foreach (SqlParameter p in Par)
{
Cmd.Parameters.Add(p);
}
}
Con.Open();
SqlDataReader rd = Cmd.ExecuteReader();
return func(rd);
}
catch (Exception ex) { throw ex; }
finally { if (Con.State == ConnectionState.Open) { Con.Close(); } }
}
public int ExecuteSql(string Sql)
{
return Execute(Sql, null,false);
}
public int ExecuteSql(string Sql, SqlParameter[] Par)
{
return Execute(Sql, Par, false);
}
public int ExecuteSp(string Name, SqlParameter[] Par)
{
return Execute(Name, Par, true);
}
///

/// 执行SQL或者存储过程
///

/// SQL或存储过程名称
/// 参数
/// 存储过程为TRUE,SQL为FALSE
/// 返回受影响记录数
public virtual int Execute(string Txt, SqlParameter[] Par, bool IsSp)
{
try
{
Cmd.CommandType = (IsSp ? CommandType.StoredProcedure : CommandType.Text);
Cmd.CommandText = Txt;
if (Par != null)
{
Cmd.Parameters.Clear();
foreach (SqlParameter p in Par)
{
Cmd.Parameters.Add(p);
}
}
Con.Open();
Tran = Con.BeginTransaction();
Cmd.Transaction = Tran;
int i = Cmd.ExecuteNonQuery();
Tran.Commit();
return i;
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
finally { if (Con.State == ConnectionState.Open) { Con.Close(); } }
}
///

/// 执行SQL复制
///

/// 数据表
/// 更新的表名
/// 执行函数(列影射)
public virtual void SqlCopy(DataTable dt,string tbName, Action act)
{
try
{
using (SqlBulkCopy bc = new SqlBulkCopy(Con))
{
bc.DestinationTableName = tbName;
Con.Open();
if (act != null) { act(bc); }
Con.Close();
}
}
catch (Exception ex) { throw ex; }
finally { if (Con.State == ConnectionState.Open) { Con.Close(); } }
}
public void SqlCopy(DataTable dt, string tbName)
{
SqlCopy(dt, tbName, null);
}
public void Dispose()
{
Con.Dispose();
Cmd.Dispose();
}
///

/// 分页
///

/// 表名或表关联代码
/// 表主键
/// 要显示的页码
/// 每页页数,默认为15
/// 要显示的字段,以逗号分开
/// 排序字段,以逗号分开
/// 查询条件
/// 总页数
/// 返回数据表
public T PageView(string tbname, string fieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string Where, ref int PageConut,Func func)
{
try
{
Where = string.IsNullOrEmpty(Where) ? "" : " WHERE " + Where;
FieldOrder = (string.IsNullOrEmpty(FieldOrder) ? " ORDER BY " + fieldKey : " ORDER BY " + FieldOrder);
int count = GetList<int>("SELECT COUNT(*) FROM " + tbname + Where, rd => {
rd.Read();
return rd.GetInt32(0);
});
PageConut = count / PageSize + (count % PageSize == 0 ? 0 : 1);
if (PageCurrent == 1)
{
return GetList("SELECT TOP " + PageSize + " " + FieldShow + " FROM " + tbname + Where + FieldOrder, func);
}
T t = GetList("SELECT " + FieldShow + " FROM (SELECT ROW_NUMBER() OVER(" + FieldOrder + ") AS rowId," + FieldShow + " FROM " + tbname + Where + ") AS "
+ tbname + " WHERE rowId BETWEEN " + ((PageCurrent - 1) * PageSize + 1) + " AND " + PageSize * PageCurrent, func);
return t;
}
catch (Exception ex) { throw ex; }
}
}
}