Simple Package DBHelper

Object-oriented three major features: encapsulation, inheritance, polymorphism.

From the beginning to learn coding until now, although these features are used every day, but feel that their understanding is still very shallow, dbhelper generally we should write a DLL to call

The result of the final operation is:

The problems encountered:

(1). Code redundancy (more or less redundant parts of the code).

(2). Connection string problem (comment section in code).

Table structure:

Create Table T (    varchar(maxnull)

DBHelper:

usingSystem;usingSystem.Collections.Generic;usingSystem.Configuration;usingSystem.Data.Common;usingSystem.Data.SqlClient;usingSystem.Data;namespacedatabase{/// <summary>    ///DBHelper/// </summary>     Public classMssql {/// <summary>        ///here, how can you send this DLL directly to others let others directly to call and no longer need to know the name of the connection string/// </summary>        Private Static ReadOnlyString Constr = configurationmanager.connectionstrings["MSSQL"].        ConnectionString; PrivateDbConnection Getcon () {return NewSqlConnection (CONSTR); }        PrivateSqlConnection Connection () {return NewSqlConnection (CONSTR); }        /// <summary>        ///Execute SQL (insert,delete,update)/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "PMS" ></param>        /// <returns></returns>         PublicInt32 execnonquery (String sql,paramsdbparameter[] PMS)            {Int32 R; SqlConnection Con=Connection (); using(DbCommand cmd =NewSqlCommand (sql, con)) {                if(NULL!=PMS) {cmd.                Parameters.addrange (PMS); } con.                Open (); R=cmd.                ExecuteNonQuery (); Con.                Close (); Con.            Dispose (); }            returnR; }        /// <summary>        ///Query Statement (exclusive one connection)/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "PMS" ></param>        /// <returns></returns>         PublicSqlDataReader Execreader (String sql,paramsdbparameter[] PMS) {SqlConnection con=Connection (); using(DbCommand cmd =NewSqlCommand (sql, con)) {                if(NULL!=PMS) {cmd.                Parameters.addrange (PMS); } con.                Open (); returnCmd. ExecuteReader (commandbehavior.closeconnection) asSqlDataReader; }        }        /// <summary>        ///Break Type/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "PMS" ></param>        /// <returns></returns>         PublicDataTable exectable (String sql,paramsdbparameter[] PMS)            {DataTable dt; SqlConnection Con=Connection (); using(SqlDataAdapter SDA =NewSqlDataAdapter (sql, con)) {                if(NULL!=PMS) {SDA.                SelectCommand.Parameters.AddRange (PMS); } DT=NewDataTable (); Sda.                Fill (DT); Con.                Close (); Con.            Dispose (); }            returnDT; }        /// <summary>        ///return to First row column/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "PMS" ></param>        /// <returns></returns>         PublicObject execscalar (String sql,paramsdbparameter[] PMS)            {Object obj; SqlConnection Con=Connection (); using(DbCommand cmd =NewSqlCommand (sql, con)) {                if(NULL!=PMS) {cmd.                Parameters.addrange (PMS); } con.                Open (); Obj=cmd.                ExecuteScalar (); Con.                Close (); Con.            Dispose (); }            returnobj; }         PublicDataSet execdataset (String sql,paramsdbparameter[] PMS)            {DataSet ds; SqlConnection Con=Connection (); using(SqlDataAdapter SDA =NewSqlDataAdapter (sql, con)) {                if(NULL!=PMS) {SDA.                SelectCommand.Parameters.AddRange (PMS); } con.                Open (); DS=NewDataSet (); Sda.                Fill (DS); Con.                Close (); Con.            Dispose (); }            returnds; }        PrivateDataTable exectable (stringProcName, CommandType type,paramsdbparameter[] PMS)            {DataTable dt; SqlConnection Con=Connection (); using(SqlDataAdapter SDA =NewSqlDataAdapter (ProcName, con)) {                if(NULL!=PMS) {SDA.                SelectCommand.Parameters.AddRange (PMS); } Sda.SelectCommand.CommandType=type; DT=NewDataTable (); Sda.                Fill (DT); Con.                Open (); Con.                Close (); Con.            Dispose (); }            returnDT; }        /// <summary>        ///Executing stored procedures/// </summary>        /// <param name= "procname" ></param>        /// <param name= "PMS" ></param>        /// <returns></returns>         PublicDataTable Execproc (String procname,paramsdbparameter[] PMS) {            returnexectable (procname, CommandType.StoredProcedure, PMS); }    }}

Main program:

usingDataBase;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Data.SqlClient;namespacetestdll{/// <summary>    ///Main/// </summary>    classProgram {Static voidMain (string[] args) {String result=String.Empty; Mssql DB=NewMssql (); #regionNewString SQL="INSERT into T values (@args)"; Int32 R= db. Execnonquery (SQL,NewSqlParameter ("@args","say aloud: I love He Xiaodong")); if(R >0) {result="data insertion is successful, [Congratulations on your success in Falling in love with He Xiaodong],"; } SQL="select * FROM T"; #endregion            #regionInquireSqlDataReader Reader=db.            Execreader (SQL); if(reader. HasRows) { while(reader. Read ()) {result+="The data read is:"+ Reader. GetString (0);            }} Console.WriteLine (Result); #endregion            #regionStored ProceduresString procname="usp_t"; DataTable DT=db.            Execproc (procname); #endregionConsole.readkey (); }    }}

Simple Package DBHelper