<div class="codebody" id="code86067">
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.sqlClient ;
using System.Data;
using System.Configuration;
using Wuqi.Webdiyer;
using Models;
namespace DAL
{
public class DBHelper
{
public static readonly string CONN_STRING = ConfigurationManager.ConnectionStrings["RyMedicalConnectionString"].ConnectionString;
public static sqlDataReader GetReader(string safesql)
{
sqlConnection conn = new sqlConnection(CONN_STRING);
sqlCommand cmd = new sqlCommand(safesql,conn);
sqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();
return reader;
}
public static sqlDataReader GetReader(string sql,params sqlParameter[] values)
{
sqlConnection conn = new sqlConnection(CONN_STRING);
sqlCommand cmd = new sqlCommand(sql,conn);
conn.Open();
cmd.Parameters.AddRange(values);
sqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();
conn.Close();
return reader;
}
public static DataTable GetDataSet(string safesql)
{
sqlConnection conn = new sqlConnection(CONN_STRING);
DataSet ds = new DataSet();
sqlCommand cmd = new sqlCommand(safesql,conn);
conn.Open();
sqlDataAdapter da = new sqlDataAdapter(cmd);
da.Fill(ds);
conn.Close();
return ds.Tables[0];
}
public static DataTable GetDataSet(CommandType cmdType,string cmdText,params sqlParameter[] cmdParms)
{
sqlConnection conn = new sqlConnection(CONN_STRING);
DataSet ds = new DataSet();
sqlCommand cmd = new sqlCommand(cmdText,conn);
conn.Open();
PrepareCommand(cmd,conn,null,cmdType,cmdText,cmdParms);
sqlDataAdapter da = new sqlDataAdapter(cmd);
da.Fill(ds);
conn.Close();
return ds.Tables[0];
}
public static sqlDataReader ExecuteReader(CommandType cmdType,params sqlParameter[] cmdParms)
{
sqlCommand cmd = new sqlCommand();
sqlConnection conn = new sqlConnection(CONN_STRING);
conn.Open();
PrepareCommand(cmd,cmdParms);
sqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
rdr.Close();
conn.Close();
return rdr;
}
public static object ExecuteScalar(CommandType cmdType,params sqlParameter[] cmdParms)
{
sqlCommand cmd = new sqlCommand();
using (sqlConnection conn = new sqlConnection(CONN_STRING))
{
conn.Open();
PrepareCommand(cmd,cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
conn.Close();
return val;
}
}
public static object ExecuteScalar(sqlConnection conn,CommandType cmdType,params sqlParameter[] cmdParms)
{
sqlCommand cmd = new sqlCommand();
PrepareCommand(cmd,cmdParms);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
private static void PrepareCommand(sqlCommand cmd,sqlConnection conn,sqlTransaction trans,sqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (sqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
public static void ExecuteNonQuery(string sql)
{
sqlConnection conn = new sqlConnection(CONN_STRING);
sqlCommand cmd = new sqlCommand(sql,conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
///

/// 传入sql语句,返回int
///

/// <param name="sql">
///
public static int ExcuteCommand(string sql)
{
sqlConnection conn = new sqlConnection(CONN_STRING);
sqlCommand cmd = new sqlCommand(sql,conn);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
return result;
}
///

///
///

/// <param name="Name">需要分页的表明
/// <param name="pk">主键名
/// <param name="fields">需要取出的字段,留空则为
/// <param name="pagesize">每页的记录数
/// <param name="CurrentPage">当前页
/// <param name="Filter">条件，可以为空，不用填 where
/// <param name="Group">分组依据，可以为空，不用填 group by
/// <param name="order">排序，可以为空，为空默认按主键升序排列，不用填 order by
///
public static DataTable Pagedlist(string Name,string pk,string fields,int pagesize,int CurrentPage,string Filter,string Group,string order)
{
CurrentPage = CurrentPage - 1;
DataTable dt = GetDataSet(CommandType.StoredProcedure,"fenye",
new sqlParameter("@TableNames",Name),
new sqlParameter("@PrimaryKey",pk),
new sqlParameter("@Fields",fields),
new sqlParameter("@PageSize",pagesize),
new sqlParameter("@CurrentPage",CurrentPage),
new sqlParameter("@Filter",Filter),
new sqlParameter("@Group",Group),
new sqlParameter("@Order",order)
);
return dt;
}
public static int fenye_num(string Name,string Filter)
{
return (int)ExecuteScalar(CommandType.StoredProcedure,"fenye_num",Filter));
}
///

///
///

/// <param name="Name">需要分页的表明
/// <param name="pk">主键名
/// <param name="fields">需要取出的字段,留空则为
/// <param name="pagesize">每页的记录数
/// <param name="CurrentPage">当前页
/// <param name="Filter">条件，可以为空，不用填 where
/// <param name="Group">分组依据，可以为空，不用填 group by
/// <param name="order">排序，可以为空，为空默认按主键升序排列，不用填 order by
/// <param name="objanp">传递aspnetpager控件
///
public static DataTable Paged(string Name,string order,AspNetPager objanp)
{
CurrentPage = CurrentPage - 1;
DataTable dt = GetDataSet(CommandType.StoredProcedure,order)
);
objanp.RecordCount = fenye_num(Name,Filter);
return dt;
}
}
}