Using System;
Using System.Data;
Using System.Data.SqlClient; Namespace SP. dbutility {///<summary>///SQL Server data-tier base class///</summary> public abstract class Sqlserverhel
Per {//sql Statement Execution timeout (sec) Private Const int command_timeout = 3600; #region preparecommand///<summary>///Command Pre-Setup///</summary>///<pa RAM Name= "conn" >mysqlconnection object </param>///<param name= "Trans" >mysqltransaction object, available for Null</par am>///<param name= "cmd" >mysqlcommand object </param>///<param name= "Cmdtype" >commandtyp E, stored procedure or command line </param>///<param name= "Cmdtext" >sql statement or stored procedure name </param>///<param name= "cmd Parms ">mysqlcommand parameter array, which can be null</param> private static void PrepareCommand (SqlConnection conn, sqltransact
Ion trans, SqlCommand cmd, commandtype cmdtype, String cmdtext, sqlparameter[] cmdparms) { IF (Conn. State!= ConnectionState.Open) Conn.
Open (); Cmd.
Connection = conn;
Cmd.commandtext = Cmdtext;
Cmd.commandtimeout = Command_timeout; if (trans!= null) cmd.
Transaction = trans;
Cmd.commandtype = Cmdtype; if (cmdparms!= null) {foreach (SqlParameter parm in cmdparms) cmd.
Parameters.Add (Parm); #endregion #region ExecuteNonQuery///<summary>///execute command/// </summary>///<param name= "connectionString" > Database connection string </param>///<param name= "Cmdty PE > Command type (stored procedure or SQL statement) </param>///<param name= "Cmdtext" >sql statement or stored procedure name </param>///<PA Ram Name= "cmdparms" >mysqlcommand parameter array </param>///<returns> returns the number of rows that are being cited </returns> public static int ExecuteNonQuery (string connectionString, commandtype cmdtype, String cmdtext, params sqlparameter[] cmdparms) {Sqlcomman
d cmd = new SqlCommand (); using (SqlConnection conn = new SqlConnection (connectionString)) {PrepareCommand (conn, NULL,
CMD, Cmdtype, Cmdtext, cmdparms); int val = cmd.
ExecuteNonQuery (); Cmd.
Parameters.clear ();
return Val; }///<summary>///execute command///</summary>///<param name= "Conn" &G T Connection object </param>///<param name= "cmdtype" > Command type (stored procedure or SQL statement) </param>///<param na
Me= "Cmdtext" >sql statement or stored procedure name </param>///<param name= "cmdparms" >mysqlcommand parameter array </param> <returns> returns the number of rows subject to the argument </returns> public static int ExecuteNonQuery (SqlConnection conn, CommandType cm Dtype, String cmdtext, params sqlparameter[] cmdparms) {
SqlCommand cmd = new SqlCommand ();
PrepareCommand (conn, null, CMD, Cmdtype, Cmdtext, cmdparms); int val = cmd.
ExecuteNonQuery (); Cmd.
Parameters.clear ();
return Val; ///<summary>///Execution Transaction///</summary>///<param name= "Trans" >mysqltr Ansaction Object </param>///<param name= "cmdtype" > Command type (stored procedure or SQL statement) </param>///<param Nam E= "Cmdtext" >sql statement or stored procedure name </param>///<param name= "cmdparms" >mysqlcommand parameter array </param>/ <returns> returns the number of rows subject to the argument </returns> public static int ExecuteNonQuery (SqlTransaction trans, CommandType C
Mdtype, String cmdtext, params sqlparameter[] cmdparms) {SqlCommand cmd = new SqlCommand (); PrepareCommand (trans.
Connection, trans, cmd, Cmdtype, Cmdtext, cmdparms); int val = cmd.
ExecuteNonQuery (); Cmd. Parameters.clear();
return Val; #endregion #region ExecuteScalar///<summary>///executes the command to return the value of the first column in the first row/// </summary>///<param name= "connectionString" > Database connection string </param>///<param name= "Cmdty PE > Command type (stored procedure or SQL statement) </param>///<param name= "Cmdtext" >sql statement or stored procedure name </param>///<PA Ram Name= "cmdparms" >mysqlcommand parameter array </param>///<returns> return Object Object </returns> public Static Object ExecuteScalar (String connectionString, CommandType cmdtype, String cmdtext, params sqlparameter[] cmdparms
) {SqlCommand cmd = new SqlCommand (); using (SqlConnection connection = new SqlConnection (connectionString)) {PrepareCommand (Connec
tion, null, cmd, Cmdtype, Cmdtext, cmdparms); Object val = cmd.
ExecuteScalar (); Cmd.
Parameters.clear (); Return VAL }///<summary>///executes a command that returns the value of the first column in the first row///</summary>///<param na
Me= "connectionString" > Database connection string </param>///<param name= "cmdtype" > Command type (stored procedure or SQL statement) </param> <param name= "Cmdtext" >sql statement or stored procedure name </param>///<param name= "cmdparms" parameters array >mysqlcommand T;/param>///<returns> Return Object object </returns> public static object ExecuteScalar (Sqlconnectio N Conn, CommandType Cmdtype, String cmdtext, params sqlparameter[] cmdparms) {SqlCommand cmd = new Sq
Lcommand ();
PrepareCommand (conn, null, CMD, Cmdtype, Cmdtext, cmdparms); Object val = cmd.
ExecuteScalar (); Cmd.
Parameters.clear ();
return Val; #endregion #region ExecuteReader///<summary>///execute a command or stored procedure, return Mysqldatareader to Like///note mysqldatareader objects must be close to release MYSQLC after useOnnection Resources///</summary>///<param name= "connectionString" > Database connection Strings </param>/ <param name= "cmdtype" > Command type (stored procedure or SQL statement) </param>///<param name= "Cmdtext" >sql statement or stored procedure name </para
m>///<param name= "cmdparms" >mysqlcommand parameter array </param>///<returns></returns> public static SqlDataReader ExecuteReader (String connectionString, CommandType cmdtype, String cmdtext, params Sql
Parameter[] cmdparms) {SqlCommand cmd = new SqlCommand ();
SqlConnection conn = new SqlConnection (connectionString);
try {preparecommand (conn, null, CMD, Cmdtype, Cmdtext, cmdparms); SqlDataReader dr = cmd.
ExecuteReader (commandbehavior.closeconnection); Cmd.
Parameters.clear ();
Return Dr; catch {Conn.
Close ();
Throw #endregion #region executedataset///<summary>///execute commands or storage
Procedure, returning the DataSet object///</summary>///<param name= "connectionString" > Database connection string </param> <param name= "cmdtype" > Command type (stored procedure or SQL statement) </param>///<param name= "Cmdtext" >sql statement or stored procedure name </p aram>///<param name= "cmdparms" >mysqlcommand parameter array (nullable value) </param>///<returns></ returns> public static DataSet ExecuteDataset (String connectionString, CommandType cmdtype, string cmdtext, par
AMS sqlparameter[] cmdparms {SqlCommand cmd = new SqlCommand (); using (SqlConnection conn = new SqlConnection (connectionString)) {PrepareCommand (conn, NULL,
CMD, Cmdtype, Cmdtext, cmdparms);
SqlDataAdapter da = new SqlDataAdapter (cmd);
DataSet ds = new DataSet (); Da.
Fill (DS); Conn.
Close (); Cmd.
Parameters.clear ();
return DS; }} #endregion}}
Using System;
Using System.Collections.Generic;
Using System.Linq;
Using System.Text;
Using System.Data.SqlClient;
Using System.Configuration;
Using System.Data;
Using System.Data.Common; namespace DAL {public static class Dbhelpher {public static string sql = String. empty;//here is a problem, when the concurrent access to the Web site, the SQL statement will have problems, you can use the session to resolve the public static string connectionString = Configurationmanager.con nectionstrings["Imitateconnstr"].
ConnectionString; Database Connection Properties, it ' s already open the ' Connection '/* private static SqlConnection conn
ection; public static SqlConnection Connection {get {string connectionString = C onfigurationmanager.connectionstrings["Imitateconnstr"].
ConnectionString;
if (connection = = null) {connection = new SqlConnection (connectionString); Connection.
Open ();
} else if (connection. state = = System.Data.ConnectionState.Closed) {connection.
Open (); else if (connection. state = = System.Data.ConnectionState.Broken) {connection.
Close (); Connection.
Open ();
return connection;
}/*///<summary>///Implementation of Non-parametric SQL statements </summary> public static int ExecuteCommand (string safesql) {using (SqlConnection C
onnection = new SqlConnection (connectionString)) {Connection.Open (); using (SqlCommand cmd = new SqlCommand (Safesql, Connection)) {int result = cmd.
ExecuteNonQuery ();
return result; }}///<summary>///ImPlementation parameters to SQL statements///</summary> public static int ExecuteCommand (String SQL
, params sqlparameter[] parameters) {//sqlcommand cmd = new SqlCommand (sql, Connection); Cmd.
Parameters.addrange (Parameters); return CMD.
ExecuteNonQuery ();
using (SqlConnection Connection = new SqlConnection (connectionString)) {Connection.Open (); using (SqlCommand cmd = Connection.createcommand ()) {cmd.
CommandText = SQL; Traverse the parameter array of the number of variables, and add to the SQL statement foreach (Sqlpa Rameter parameter in parameters) {cmd.
Parameters.Add (parameter); } return cmd.
ExecuteNonQuery (); }}///<summary>///ImplemEntation of Non-parametric SQL statements, and return to the implementation of the number of rows///</summary > public static Object Getscalar (string safesql) {using (SqlConnection Connection = new Sq
Lconnection (connectionString)) {Connection.Open (); using (SqlCommand cmd = new SqlCommand (Safesql, Connection)) {return cmd.
ExecuteScalar (); }}///<summary>///implementation parameters to SQL Statements,and return t o The implementation of the number of rows///</summary> public static Object Getscalar (String sql,
params sqlparameter[] parameters) {//using (SqlCommand cmd = new SqlCommand (SQL, Connection)) {//cmd.
Parameters.addrange (Parameters); int result = Convert.ToInt32 (cmd.
ExecuteScalar ()); // return result;
}//the same as below using (SqlConnection Connection = new SqlConnection (connectionString))
{Connection.Open (); using (SqlCommand cmd = Connection.createcommand ()) {cmd.
CommandText = SQL; Cmd. Parameters.addrange (Parameters);//addrange () is adding a parameter array return cmd.
ExecuteScalar (); }}///<summary>///Using storedprocedure for Scalar///&
lt;/summary>/* Public static Object Getscalar (string storeprocedure, params sqlparameter[] parameters) {using (SqlCommand cmd = connection.) CreateCommand ()) {cmd.
CommandType = CommandType.StoredProcedure; Cmd.
Parameters.addrange (Parameters); return CMD.
ExecuteScalar ();
} } *////<summary>///Implementation of Non-parametric SQL statements and return SqlData Reader///</summary> public static SqlDataReader Getreader (string safesql) {US
ing (SqlConnection Connection = new SqlConnection (connectionString)) {Connection.Open (); using (SqlCommand cmd = new SqlCommand (Safesql, Connection)) {Sqldatarea Der reader = cmd.
ExecuteReader ();
return reader; }}///<summary>///implementation parameters to SQL statements and return SqlDataReader///</summary> public static SqlDataReader Getreader (String sql,params sqlparameter[]
Parameters) {using (SqlConnection Connection = new SqlConnection (connectionString)) {
Connection.Open (); using (Sqlcommand cmd = new SqlCommand (SQL, Connection)) {cmd.
Parameters.addrange (Parameters); SqlDataReader reader = cmd.
ExecuteReader ();
return reader; }}///<summary>///U can use this function to get user ' s information. (The effect the same as SqlDataReader)///</summary> public static DataTable GetDataSet (String sql , params sqlparameter[] parameters) {using (SqlConnection Connection = new SqlConnection (Connectionst
Ring)) {Connection.Open (); using (SqlCommand cmd = new SqlCommand (SQL, Connection)) {cmd.
Parameters.addrange (Parameters);
DataSet DataSet = new DataSet ();
SqlDataAdapter adapter = new SqlDataAdapter (cmd); Adapter.
Fill (DataSet, "usertable"); return DataSet.
tables["Usertable"]; Adapter.
Fill (DataSet); return DataSet.
Tables[0];
}
}
}
}
}
Using System;
Using System.Data;
Using System.Configuration;
Using System.Linq;
Using System.Web;
Using System.Web.Security;
Using System.Web.UI;
Using System.Web.UI.HtmlControls;
Using System.Web.UI.WebControls;
Using System.Web.UI.WebControls.WebParts;
Using System.Xml.Linq;
Using System.Data.SqlClient; Namespace SRC {public class DBHelper {public static string Getsqlconn () {string sCo
nn sconn = configurationmanager.connectionstrings["ConnStr"].
ConnectionString;
return sconn;
The public static DataTable Getsqldatatable (String strSQL) {DataTable dt = new DataTable ();
String sconn = Getsqlconn ();
try {SqlDataAdapter da = new SqlDataAdapter (strSQL, sconn); Da.
Fill (DT);
catch {throw;
} return DT; } public static string GetsqldAtascalar (String strSQL) {string strresult = "";
SqlConnection conn = new SqlConnection (Getsqlconn ()); IF (Conn. state = = connectionstate.closed) {Conn.
Open ();
try {SqlCommand comm = new SqlCommand (strSQL, conn); strresult = Comm. ExecuteScalar ().
ToString ();
return strresult;
catch {return strresult = "0";
} public static int excutenonquery (String sql, params sqlparameter[] parameters) {
int result;
SqlConnection conn = new SqlConnection (Getsqlconn ());
SqlCommand comm = new SqlCommand (SQL, conn); foreach (SqlParameter p in Parameters) {Comm.
Parameters.Add (P); } conn.
Open (); try {result = Comm. ExeCutenonquery (); Conn.
Close ();
return result;
catch (Exception) {return 0;
} public static bool Chkexists (String strSQL) {DataTable dt = new DataTable ();
try {SqlDataAdapter da = new SqlDataAdapter (strSQL, Getsqlconn ()); Da.
Fill (DT);
catch {throw; } if (dt.
Rows.Count > 0) {return true;
else {return false;
}
}
}
}