C # Implementation of a more practical sqlhelper_c# tutorial

Write a blog for the first time, think of what to write good B (￣▽￣) d, consider a half-day decision from SqlHelper start, sqlhelper for programmers like HelloWorld, very simple but very important, HelloWorld represents the first time the programmer Meng New write code, And SqlHelper is the first contact with the database (do not know if this is right).

Okay, no nonsense, the following directly on the code (speechless):

public class SQLHelper {//timeout time private static int Timeout = 1000;
    Database name Public Const String bestnet = "Bestnet";
    Stored procedure name Public Const String USERINFOCURD = "Userinfocurd";

    Database connection string private static dictionary<string, string> connstrs = new dictionary<string, string> (); <summary>///SQL Server Operations Class (static constructor)///</summary> static SQLHelper () {ConnectionStr
      Ingsettingscollection configs = webconfigurationmanager.connectionstrings; foreach (connectionstringsettings config in configs) {connstrs.add (config). Name, CONFIG.
      ConnectionString); }///<summary>///obtain database connection///</summary>///<param name= "Database" > Database (Co in configuration file) Nnectionstrings name) </param>///<returns> database connection </returns> private static SqlConnection Getconne Ction (String database) {if (string). IsNullOrEmpty (database)) {throw newException ("not set Parameter: Database"); } if (!
      Connstrs.containskey (database) {throw new Exception ("Not Found:" + db);
    Return to New SqlConnection (Connstrs[database]); ///<summary>///Get SqlCommand///</summary>///<param name= "Conn" >sqlconnection&lt ;/param>///<param name= "Transaction" >SqlTransaction</param>///<param name= "Cmdtype" >comma ndtype</param>///<param name= "sql" >SQL</param>///<param name= "parms" >sqlparameter array ;/param>///<returns></returns> private static SqlCommand GetCommand (SqlConnection conn, Sqltransa  ction transaction, CommandType Cmdtype, String sql, sqlparameter[] parms) {SqlCommand cmd = new SqlCommand (SQL,
      conn);
      Cmd.commandtype = Cmdtype;
      Cmd.commandtimeout = Timeout; if (transaction!= null) cmd.
      Transaction = Transaction; if (parms!= null && parms. Length!= 0) cmd.
      Parameters.addrange (parms);
    return cmd; ///<summary>///query data, back to DataTable///</summary>///<param name= "Database" > Database </
    param>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "parms" > Parameters </param> <param name= "Cmdtype" > Query type (SQL statement/stored procedure name) </param>///<returns>DataTable</returns> Publ IC Static DataTable querydatatable (string database, String sql, sqlparameter[] parms, commandtype cmdtype) {if (String.
      IsNullOrEmpty (database) {throw new Exception ("not set Parameter: Database"); } if (string.
      IsNullOrEmpty (SQL)) {throw new Exception ("Not Set parameters: SQL"); The try {using (SqlConnection conn = getconnection (database)) {Conn.

          Open (); using (SqlCommand cmd = GetCommand (conn, NULL, Cmdtype, SQL, parms)) {using (SqlDataAdapter da = new SqlDataAdapter (CMD)) {DataTable dt = new DataTable (); Da.
              Fill (DT);
            return DT; catch (SqlException ex) {System.Text.StringBuilder log = new system.t Ext.
        StringBuilder (); Log.
        Append ("Query data error:"); Log.
        Append (ex); throw new Exception (log.
      ToString ()); }///<summary>///query data, back to DataSet///</summary>///<param name= "Database" > Database & 
    lt;/param>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "parms" > Parameters </param> <param name= "Cmdtype" > Query type (SQL statement/stored procedure name) </param>///<returns>DataSet</returns> PU Blic static DataSet Querydataset (string database, String sql, sqlparameter[] parms, commandtype cmdtype) {if (s) Tring.
      IsNullOrEmpty (database) {throw new Exception ("not set Parameter: Database"); } if (string. IsnuLlorempty (SQL)) {throw new Exception ("Not Set parameters: SQL"); The try {using (SqlConnection conn = getconnection (database)) {Conn.

          Open (); using (SqlCommand cmd = GetCommand (conn, NULL, Cmdtype, SQL, parms)) {using (SqlDataAdapter da = ne
              W SqlDataAdapter (cmd)) {DataSet ds = new DataSet (); Da.
              Fill (DS);
            return DS; catch (SqlException ex) {System.Text.StringBuilder log = new system.t Ext.
        StringBuilder (); Log.
        Append ("Query data error:"); Log.
        Append (ex); throw new Exception (log.
      ToString ()); ///<summary>///Execute command get unique value (first row first column)///</summary>///<param name= "Database" > number According to the library </param>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "parms" > Parameters </param&
    Gt <param name= "Cmdtype" > Query type(SQL statement/stored procedure name) </param>///<returns> Get value </returns> public static object Queryscalar (string databas E, string sql, sqlparameter[] parms, commandtype cmdtype) {if (string).
      IsNullOrEmpty (database) {throw new Exception ("not set Parameter: Database"); } if (string.
      IsNullOrEmpty (SQL)) {throw new Exception ("Not Set parameters: SQL"); The try {using (SqlConnection conn = getconnection (database)) {Conn.

          Open (); using (SqlCommand cmd = GetCommand (conn, NULL, Cmdtype, SQL, parms)) {return cmd.
          ExecuteScalar (); {System.Text.StringBuilder log = new System.Text.StringBu}} catch (SqlException ex)
        Ilder (); Log.
        Append ("Processing error:"); Log.
        Append (ex); throw new Exception (log.
      ToString ()); }///<summary>///execute command Update data///</summary>///<param name= "Database" > Databases </pa Ram>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "parms" > Parameters </param>///  <param name= "Cmdtype" > Query type (SQL statement/stored procedure name) </param>///<returns> updated number of rows </returns> public static int Execute (string database, String sql, sqlparameter[] parms, commandtype cmdtype) {if (string).
      IsNullOrEmpty (database) {throw new Exception ("not set Parameter: Database"); } if (string.
      IsNullOrEmpty (SQL)) {throw new Exception ("Not Set parameters: SQL");

      //Returns the number of update rows (add) int count = 0; try {using (SqlConnection conn = getconnection (database)) {Conn.

          Open (); using (SqlCommand cmd = GetCommand (conn, NULL, Cmdtype, SQL, parms)) {if (Cmdtype = = Commandtype.st oredprocedure) cmd. Parameters.addwithvalue ("@RETURN_VALUE", "").

            Direction = ParameterDirection.ReturnValue; Count = cmd.

         ExecuteNonQuery ();   if (count <= 0) if (cmdtype = = CommandType.StoredProcedure) count = (int) cmd. parameters["@RETURN_VALUE"].
          Value; {System.Text.StringBuilder log = new System.Text.StringBu}} catch (SqlException ex)
        Ilder (); Log.
        Append ("Processing error:"); Log.
        Append (ex); throw new Exception (log.
      ToString ());
    return count; ///<summary>///query data, back to DataTable///</summary>///<param name= "Database" > Database </ param>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "cmdtype" > Query type (SQL statement/stored procedure name) & lt;/param>///<param name= "values" > Parameters </param>///<returns>DataTable</returns> Pub Lic static DataTable querydatatable (string database, String sql, CommandType Cmdtype, idictionary<string, Object> v
      alues) {sqlparameter[] parms = Dictoparams (values); return querydatatable (DATAbase, SQL, parms, cmdtype); ///<summary>///Execute Stored procedure query data, return DataSet///</summary>///<param name= "Database" > Database & lt;/param>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "cmdtype" > Query type (SQL statement/stored over Process name) </param>///<param name= "values" > Parameters///<returns>DataSet</returns> public static D
      Ataset querydataset (String database, String sql, CommandType Cmdtype, idictionary<string, object> values) {
      sqlparameter[] parms = Dictoparams (values);
    return querydataset (Database, SQL, parms, cmdtype); ///<summary>///Execute command get unique value (first row first column)///</summary>///<param name= "Database" > DB < /param>///<param name= "SQL" >sql statement or stored procedure name </param>///<param name= "cmdtype" > Query type (SQL statement/stored procedure name) </param>///<param name= "values" > Parameters </param>///<returns> unique value </returns> public s Tatic Object Queryscalar (String database, String sql, CommandType Cmdtype, idictionary<string, object> values) {
      sqlparameter[] parms = Dictoparams (values);
    return queryscalar (Database, SQL, parms, cmdtype); ///<summary>///Execute command Update data///</summary>///<param name= "Database" > Databases </param&gt
    ; <param name= "SQL" >sql statement or stored procedure name </param>///<param name= "cmdtype" > Query type (SQL statement/stored procedure name) </param >///<param name= "values" > Parameters </param>///<returns> updated number of rows </returns> public static in T Execute (string database, String sql, CommandType Cmdtype, idictionary<string, object> values) {Sqlparam
      eter[] parms = Dictoparams (values);
    return Execute (Database, SQL, parms, cmdtype); ///<summary>///Create parameters///</summary>///<param name= "name" > Parameter name </param>// /<param name= "type" > Parameter type </param>///<param name= "Size" > Parameter size </param>///<param name= "direction" > Parameter direction (input/output) </param>///<param name= "Val UE "> Parameter value </param>///<returns> New Parameter object </returns> public static sqlparameter[] Dictoparams (idictio

      nary<string, object> values) {if (values = = null) return null; sqlparameter[] parms = new sqlparameter[values.
      Count];
      int index = 0;
        foreach (keyvaluepair<string, object> kv in values) {SqlParameter parm = null; if (kv. Value = = null) {parm = new SqlParameter (kv.
        Key, DBNull.Value); else {Type T = kv.
          Value.gettype (); Parm = new SqlParameter (kv. Key, Nettosql (kv.
          Value.gettype ())); Parm. Value = kv.
        Value;
      } parms[index++] = Parm;
    return parms; ///<summary>///. NET type converted to SQL type///</summary>///<param name= "T" >.net type </param >///; returns>sql type </returns> public static SqlDbType nettosql (type T) {SqlDbType DbType = Sqldbtype.va
      Riant;
          Switch (t.name) {case "Int16": DbType = Sqldbtype.smallint;
        Break
          Case "Int32": DbType = SqlDbType.Int;
        Break
          Case "Int64": DbType = Sqldbtype.bigint;
        Break
          Case "single": DbType = sqldbtype.real;
        Break
          Case "Decimal": DbType = Sqldbtype.decimal;

        Break
          Case "byte[]": DbType = sqldbtype.varbinary;
        Break
          Case "Boolean": DbType = sqldbtype.bit;
        Break
          Case "String": DbType = SqlDbType.NVarChar;
        Break
          Case "char[]": DbType = SqlDbType.Char;
        Break
          Case "DateTime": DbType = sqldbtype.datetime;
        Break
   Case "DateTime2": DbType = sqldbtype.datetime2;       Break
          Case "DateTimeOffset": DbType = Sqldbtype.datetimeoffset;
        Break
          Case "TimeSpan": DbType = sqldbtype.time;
        Break
          Case "Guid": DbType = Sqldbtype.uniqueidentifier;
        Break
          Case "XML": DbType = Sqldbtype.xml;
        Break
          Case "Object": DbType = sqldbtype.variant;
      Break
    return dbType;

 }

  }

You can call this directly:

idictionary<string, object> values = new dictionary<string, object> ();
 Values. ADD ("@UserName", UserName);      
 Values. ADD ("@PassWord", PassWord);
 Object Scalar = Sqlhelper.queryscalar (sqlhelper.bestnet, Sqlhelper.userinfocurd, CommandType.StoredProcedure, values );  

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.