DBHelper Class code (for personal reference)

Source: Internet
Author: User
Tags object object static class first row connectionstrings
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>///&LT;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>///&LT;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;
 }
        }
    }
}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.