C#.net database access and its operation classes

Using System;

Using System.Collections.Generic;

Using System.Linq;

Using System.Web;

Using System.Data;

Using System.Data.SqlClient;

Using System.Configuration;

Using System.Text;

Using System.Text.RegularExpressions;

Using System.Security;

<summary>

SqlHelper is a database operation class

</summary>

public class SqlHelper

{

This is all access to a database

Public SqlHelper ()

{

//

TODO: Add constructor logic here

//

}

/* Get the Connection field for the database string */

public static string ConnectionString = configurationmanager.connectionstrings["ConnectionString"]. ToString ();

public static SqlConnection conn = Getconn ();

Initializes the number of the last data on the current page and the number of the first record

private static int lowerrecord = 0;

private static int upperrecord = 0;

/* Get connection to database SqlConnection object conn*/

#region

<summary>

Gets the connection object for the database

</summary>

<returns> returns a SqlConnection object conn</returns>

private static SqlConnection Getconn ()

{

conn = new SqlConnection (ConnectionString);

Return conn;

}

#endregion

<summary>

Open a database connection

</summary>

private static void Open ()

{

IF (Conn. state = = connectionstate.closed)

Conn. Open ();

}

<summary>

To close a database connection

</summary>

private static void Close ()

{

IF (Conn. state = = connectionstate.closed)

Conn. Close ();

}

/* Destroy a SqlConnection object conn*/

#region

<summary>

Destroying Conn Objects

</summary>

///

private void Dispose ()

{

Try

{

Conn. Close ();

Conn. Dispose ();

}

catch {throw;}

}

#endregion

/* Execute SQL statement to return the number of rows affected */

#region # #执行sql语句, returns the number of rows affected

<summary>

Returns the number of rows affected by the strSQL execution.

</summary>

<param name= "strSQL" > stored procedure name or SQL command </param>

<param name= parameter collection for "Cmdparameter" >sql statement </param>

<returns></returns>

public static int ExecuteNonQuery (string strSQL, params sqlparameter[] cmdparameter)

{

int effectnum = 0;

Close ();

Open ();

SqlCommand cmd = new SqlCommand ();

Cmd.commandtimeout = 60;

PrepareCommand (CMD, conn, null, strSQL, cmdparameter);

Effectnum = cmd. ExecuteNonQuery ();

Cmd. Parameters.clear ();

return effectnum;

}

#endregion

/* Execute the SQL statement to return the data for the first column in the first row */

#region # #执行sql语句, returns the data for the first column of the first row

<summary>

Returns the object of the first column in the first row: the number of records that are generally used for statistics

</summary>

<param name= "strSQL" > SQL statement to execute, this statement typically contains: "SELECT COUNT (*) from table [where]" </param>

<param name= "Cmdparameter" > Parameters of the SQL statement executed </param>

<returns> returns the object of the first column in the first row </returns>

public static Object ExecuteScalar (String strsql,params sqlparameter[] cmdparameter)

{

Try

{

Close ();

Open ();

Object Object1;

SqlCommand cmd = new SqlCommand ();

PrepareCommand (CMD, conn, null, strSQL, cmdparameter);

Object1 = cmd. ExecuteScalar ();

Cmd. Parameters.clear ();

return object1;

}

catch {

Close ();

Throw

}

}

#endregion

/* Execute SQL statement to get DataSet SqlDataReader Object */

#region # #执行sql语句得到数据集sqlDataReader对象

<summary>

Gets the data result set returned by SQL after execution

</summary>

<param name= "strSQL" > SQL statement to execute </param>

<param name= parameters of the "Cmdparameter" >sql statement </param>

<returns> a SqlDataReader object </returns>

public static SqlDataReader ExecuteReader (string strSQL, params sqlparameter[] cmdparameter)

{

Try

{

Close ();

Open ();

SqlCommand cmd = new SqlCommand ();

PrepareCommand (CMD, conn, null, strSQL, cmdparameter);

SqlDataReader SDR = cmd. ExecuteReader (commandbehavior.closeconnection);

Cmd. Parameters.clear ();

return SDR;

}

catch (Exception e)

{

Close ();

Throw e;

}

}

#endregion

/* Returns a data set after execution dataset*/

#region # #执行后返回一个数据集dataset

<summary>

Returns a dataset result dataset after execution

</summary>

<param name= "strSQL" > SQL statement to execute </param>

<param name= "Cmdparameter" > Parameters of the SQL statement to execute </param>

<returns> returns a dataset result dataset </returns>

public static DataSet ExecuteDataset (string strSQL, params sqlparameter[] cmdparameter)

{

Close ();

Open ();

DataSet ds = new DataSet ();

SqlCommand cmd = new SqlCommand ();

PrepareCommand (cmd, getconn (), NULL, strSQL, cmdparameter);

SqlDataAdapter SDA = new SqlDataAdapter (cmd);

try {

Sda. Fill (DS);

Cmd. Parameters.clear ();

return DS;

}

catch (Exception e)

{

Close ();

Throw e;

}

}

#endregion

/* Prepare the SQL statement to execute */

#region # #准备要执行的sql语句

<summary>

Ready to execute a command, stitching parameters into cmd.

</summary>

<param name= "cmd" >sql command </param>

<param name= "conn" >sql connection </param>

<param name= "Trans" >sql Affairs </param>

<param name= "cmdtext" > Command text, for example: SELECT * FROM products</param>

<param name= "cmdparms" > Parameters of Execution Command </param>

///

private static void PrepareCommand (SqlCommand cmd, SqlConnection conn, sqltransaction trans, string cmdtext, SqlParameter [] cmdparms)

{

Determine the status of the connection. If it is off, open the

IF (Conn. State = ConnectionState.Open)

Conn. Open ();

Cmd Property Assignment value

Cmd. Connection = conn;

Cmd.commandtext = Cmdtext;

Whether transaction processing is required

if (trans! = null)

Cmd. Transaction = trans;

Cmd.commandtype = CommandType.Text;

Add cmd required Stored procedure parameters

if (cmdparms! = null)

{

foreach (SqlParameter parm in cmdparms)

Cmd. Parameters.Add (Parm);

}

}

#endregion

/* Get a non-read PRIMARY key collection */

#region # #获得未阅主键集合

<summary>

Paging using: Getting the primary key

</summary>

<param name= "Query" > SQL statements for Queries </param>

<param name= "Passcount" > Number of records read </param>

<returns> a primary key collection </returns>

private static string RecordID (string query, int passcount)

{

String Strid = String. Empty;

Close ();

Open ();

SqlCommand cmd = new SqlCommand (query, conn);

SqlDataReader SDR = cmd. ExecuteReader ();

while (SDR. Read ())

{

if (Passcount < 1)

{

Strid + = "," + "'" +SDR. GetString (0) + "'";

}

passcount--;

}

Sdr. Close ();

if (Strid.trim ()! = "")

return strid.substring (1);

Else

Return "'";

}

#endregion

/* Data Paging */

#region # #数据分页

<summary>

Data paging

</summary>

<param name= "PageIndex" > Current page index</param>

<param name= "pageSize" > Page size </param>

<param name= "strkey" > Primary key </param>

<param name= "showstring" > Display fields </param>

<param name= "queryString" > Querying SQL statements </param>

<param name= "wherestring" > Conditional statements </param>

<param name= "orderstring" > Sort statements </param>

<returns></returns>

public static DataTable Executepager (int pageindex,int pageSize, String strkey, String showstring,string queryString, String wherestring, String orderstring)

{

Close ();

Open ();

DataTable dt = new DataTable ();

Determines whether some of the important attributes of a page are empty or illegal, and corrects

if (PageIndex < 1) PageIndex = 1;

if (PageSize < 3) pageSize = 10;

if (Showstring.equals (string. Empty)) showstring = "*";

if (Orderstring.equals (string. Empty)) orderstring = strkey + "ASC";

if (Wherestring.equals (string. Empty)) wherestring = "1=1";

Get the database Connection object and open the database to prepare for the future

IF (Conn. state = = connectionstate.closed)

//{

Conn. Open ();

//}

Get Data Sheet

string table = String. Format ("{0} tempvw", queryString);

int recordCount = datacount (table);

Define a SqlCommand object

SqlCommand Cmdrecord;

Determines whether the total number of records is less than the number of displayed records on the page

if (RecordCount <= pageSize) {

Cmdrecord = new SqlCommand (string. Format ("Select top {0} {1} from {2} {3}", recordcount,showstring,table,wherestring), conn);

}

else {

Calculates the first and last record number of the current page

Lowerrecord = PageIndex * pageSize;

Upperrecord = lowerrecord-pagesize;

Get a primary key that has not yet been viewed

String recordids = RecordID (string. Format ("Select top {0} {1} from {2} {3}", Lowerrecord, Strkey, table,wherestring), Upperrecord);

String STR = String. Format ("Select {0} from {1} ' where {2} in ({3}) ' ORDER by {4}", showstring, table, strkey, Recordids, orderstring);

Cmdrecord = new SqlCommand (string. Format ("Select {0} from {1} ' where {2} in ({3}) ' ORDER by {4}", Showstring, Table,strkey,recordids, orderstring), conn);

}

SqlDataAdapter SDA = new SqlDataAdapter (Cmdrecord);

Sda. Fill (DT);

return DT;

}

#endregion

/* Calculate total number of records */

#region # #计算总记录数

<summary>

Calculate the total number of records

</summary>

<param name= "TABLE" > the table to query, or the statement to query </param>

<returns> total number of records </returns>

public static int Datacount (string table)

{

Close ();

Open ();

SqlCommand cmdcount = new SqlCommand (string. Format ("SELECT count (*) from {0}", table), conn);

Get Total Record Count

int recordCount = Convert.ToInt32 (Cmdcount.executescalar ());

Close ();

return recordCount;

}

#endregion

/* Total pages Calculated */

#region # #计算总页数

<summary>

Calculate Total Pages

</summary>

<param name= "queryString" > Querying SQL statements </param>

<param name= "pageSize" > Page size </param>

<returns> Total Pages </returns>

public static int PageCount (string queryString, int pageSize)

{

string table = String. Format ("({0}) tempvw", queryString);

int recordCount = datacount (table);

int PageCount;

if (recordCount% pageSize = = 0)

PageCount = recordcount/pagesize;

Else

PageCount = recordcount/pagesize + 1;

return PageCount;

}

#endregion

}

This article is from the "Trials and hardships" blog, please be sure to keep this source http://carben.blog.51cto.com/8690350/1654979

C#.net database access and its operation classes