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, String cmdtext, params sqlparameter[] cmdparms)
{
SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (conn_string);
Conn. Open ();
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
SqlDataReader rdr = cmd. ExecuteReader (commandbehavior.closeconnection);
Cmd. Parameters.clear ();
Rdr. Close ();
Conn. Close ();
return RDR;
}
public static Object ExecuteScalar (CommandType cmdtype, String cmdtext, params sqlparameter[] cmdparms)
{
SqlCommand cmd = new SqlCommand ();
using (SqlConnection conn = new SqlConnection (conn_string))
{
Conn. Open ();
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
Object val = cmd. ExecuteScalar ();
Cmd. Parameters.clear ();
Conn. Close ();
return Val;
}
}
public static Object ExecuteScalar (SqlConnection conn, CommandType Cmdtype, String cmdtext, params sqlparameter[] Cmdparm S
{
SqlCommand cmd = new SqlCommand ();
PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, cmdparms);
Object val = cmd. ExecuteScalar ();
Cmd. Parameters.clear ();
return Val;
}
private static void PrepareCommand (SqlCommand cmd, SqlConnection Conn, SqlTransaction Trans, CommandType cmdtype, string C Mdtext, 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 ();
}
<summary>
Incoming SQL statement, returning INT
</summary>
<param name= "SQL" ></param>
<returns></returns>
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;
}
<summary>
///
</summary>
<param name= "name" > need pagination to indicate </param>
<param name= "PK" > Primary Key Name </param>
<param name= "Fields" > fields to be taken out, leave blank for *</param>
<param name= "pagesize" > Number of records per page </param>
<param name= "currentpage" > Current page </param>
<param name= "Filter" > conditions, can be empty, do not fill where</param>
<param name= "group" > Group BY, you can be empty without filling group by</param>
<param name= "Order" > Sort, can be empty, default by primary key in ascending order, not fill order by</param>
<returns></returns>
public static DataTable Pagedlist (string Name, String pk, string fields, int pagesize, int currentpage, string Filter, str ing 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",
New SqlParameter ("@TableNames", Name),
New SqlParameter ("@Filter", Filter));
}
<summary>
///
</summary>
<param name= "name" > need pagination to indicate </param>
<param name= "PK" > Primary Key Name </param>
<param name= "Fields" > fields to be taken out, leave blank for *</param>
<param name= "pagesize" > Number of records per page </param>
<param name= "currentpage" > Current page </param>
<param name= "Filter" > conditions, can be empty, do not fill where</param>
<param name= "group" > Group BY, you can be empty without filling group by</param>
<param name= "Order" > Sort, can be empty, default by primary key in ascending order, not fill order by</param>
<param name= "OBJANP" > Pass Aspnetpager Controls </param>
<returns></returns>
public static DataTable Paged (string Name, String pk, string fields, int pagesize, int currentpage, string Filter, String Group, string order, Aspnetpager OBJANP)
{
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)
);
Objanp. RecordCount = Fenye_num (Name, Filter);
return DT;
}
}
}
This can be achieved (the following figure), in any project only need to COPY2 stored procedures a data manipulation class, or like to make data classes into a DLL or, in the page call to pass the parameter only one line of code.
