vb.net how to write high-efficiency sqlhelper--(ii) Code combat

Previous blog: "VB.net how to write high-efficiency sqlhelper--(a) basic article"
We talked about a lot of the basics of the relationship, and those are the prerequisites to read this article. So children's shoes need to take a good look.
This article has selected the most commonly used methods, and as simple as possible, flexible (using the function overload and read the configuration file)
Well, nonsense not much to say, directly on the code.

' *************************************************' Yu' Group:' Description: Using function overloading, configuration file read makes the program more flexible' Date Created: 2015-4-27' version number: v1.4' *************************************************ImportsSystem.DataImportsSystem.Data.SqlClientImportsSystem.Configuration Public ClassSqlhelperdal' Global configuration: Connect characters, define a cnn,cmd    ' additions and deletions, parameters/No parameters    ' Check, parametric/no parameters    ' Global configuration    ' Connection string    ' Dim ConnectionString as String = ' server=bill\mysql;database=charge_system; User Id=sa; Password= "    ' Initialize the SqlConnection object    DimCnn as NewSqlConnection (Configuration.ConfigurationSettings.AppSettings ("ConnectionString"))' Read the configuration file to get the value of connectionstring    DimCmd as NewSqlCommand# Region "There are parameters and additions to Excutenoquery, do not return data rows, return the integer data indicates the number of rows affected. This function is overloaded "     " <summary>     "   The database operation (with parameter version)     " </summary>     "'   <param name=" Cmdtext "></param> Command text    "'   <param name=" Cmdtype "></param> type of command    "  ' <param name=" Sqlparams "></param> Set of SQL statements to execute, unable to determine specific number of bars     ' <returns></returns> integer value, indicating the number of rows affected     " <remarks></remarks>      Public FunctionExcutenoquery (ByValCmdtext as String,ByValCmdtype asCommandType,ByValSqlparams asSqlParameter ()) as Integer        ' Add the parameters in the array sqlparams to the parametersCmd.commandtype = Cmdtype' Pass the type of command to CommandTypeCmd. Connection = CNN' Assign a parameter connection to execute a connection commandCmd.commandtext = Cmdtext' assigns the SQL statement to the text that executes the command CommandText        TryCmd. Parameters.clear ()' Clear Parameters            ' If the Sqlparams parameter is present, it is added to the CMD object            If  notIsNothing (Sqlparams) ThenCmd. Parameters.addrange (Sqlparams)End If            ' If the connection is not open, open the connection            IfCnn. State = connectionstate.closed ThenCnn. Open ()End If            ReturnCmd. ExecuteNonQuery' Perform the action        CatchEx asExceptionReturn 0        FinallyConnectionclose (CNN) commandclose (CMD)End Try    End Function#End   Region# Region "No parameter additions or deletions, no data rows returned, return integer type data representing the number of affected rows overloaded Excutenoquery"     " <summary>      ' execution returns the number of rows affected, here is the overloaded Excutenoquery method.      " </summary>     "  ' <param name=" Cmdtext "></param> SQL text command to execute, or stored procedure name    "'   <param name=" Cmdtype "></param> type of command to execute, SQL text command or stored procedure name      ' <returns></returns>number of integer type, indicating the number of rows affected     " <remarks></remarks>      Public FunctionExutenoquery (ByValCmdtext as String,ByValCmdtype asCommandType) as Integer        ReturnExcutenoquery (Cmdtext, Cmdtype, Nothing)End Function#End   Region# Region "No version, returns the value of the first column in the first row"     Public FunctionExcutescalar (ByValCmdtext as String, Cmdtype asCommandType) as Object        ReturnExcutescalar (Cmdtext, Cmdtype, Nothing)End Function#End   Region# Region "Gets the value of the first column of the first row, there is a version, the method is overloaded"     Public FunctionExcutescalar (ByValCmdtext as String,ByValCmdtype asCommandType, Sqlparams asSqlParameter ()) as Object        DimResult as Object= NothingCmd. Connection = CNN Cmd.commandtype = Cmdtype Cmd.commandtext = CmdtextTry            ' If the status is not open, make a connection open operation            IfCnn. State = connectionstate.closed ThenCnn. Open ()End If            ' Add all parameters if not null            If  notIsNothing (Sqlparams) ThenCmd. Parameters.addrange (Sqlparams)End Ifresult = cmd. ExecuteScalarReturnResultCatchEx asExceptionThrow NewException ("Excutescalar Error:", ex)FinallyConnectionclose (CNN) commandclose (CMD)End Try    End Function#End   Region# Region "query operation, return datetable, overloaded function Excuteselect"     " <summary>      " ' operation to perform the query, there is a reference version     " </summary>     "  ' <param name=" Cmdtext "></param> SQL command text to execute    "'   <param name=" Cmdtype "></param> command Type    "'   <param name=" Sqlparams "></param> parameters in SQL command to perform database operations     ' <returns></returns>dataTable, querying data table entries in the database     " <remarks></remarks>      Public FunctionExcuteselect (ByValCmdtext as String,ByValCmdtype asCommandType, Sqlparams asSqlParameter ()) asDataTableDimSqladapter asSqlDataAdapterDimDataTable asDataTableDimDataSet as NewDataSet cmd. Connection = CNN Cmd.commandtype = Cmdtype Cmd.commandtext = Cmdtext' Why is it possible to connect to the database without Cnn.open connection here??? , is it a non-connected state operation? Sqladapter =NewSqlDataAdapter (CMD)TryCmd. Parameters.clear ()' Clear Parameters            ' If the Sqlparams parameter is passed, it is added to the CMD object            If  notIsNothing (Sqlparams) ThenCmd. Parameters.addrange (Sqlparams)End IfSqladapter.fill (DataSet)' Fill the dataset with SqladapterDataTable = Dataset.tables (0)' dataTable is the first table of a dataset            ReturnDataTable' return to the data obtained        CatchEx asExceptionThrow NewException ("The query failed!" "+ ex. Message)FinallyConnectionclose (CNN) commandclose (CMD)End Try    End Function#End   Region# Region "No parameter query, return datetable, here overload excuteselect function"     " <summary>     "  ' no parameter query, return datetable, here reload Excuteselect     " </summary>     "  ' <param name=" Cmdtext "></param> SQL text command to execute or stored procedure name    "'   <param name=" Cmdtype "></param> type of statement to execute, SQL statement or stored procedure     ' <returns></returns>DataTable Object Instance     " <remarks></remarks>      Public FunctionExcuteselect (ByValCmdtext as String,ByValCmdtype asCommandType) asDataTableReturnExcuteselect (Cmdtext, Cmdtype, Nothing)End Function#End   Region     " <summary>      " ' Close the Connection object and release the resource     " </summary>     "'   <param name=" CNN ></param>      " <remarks></remarks>      Public SubConnectionclose (ByValCnn asSqlConnection)IfCnn. State = ConnectionState.Open ThenCnn. Close () CNN = Nothing        End If    End Sub     " <summary>      " ' Close the CMD object and release the resource     " </summary>       "' <param name=" cmd "></param>      " <remarks></remarks>      Public SubCommandclose (ByValCmd asSqlCommand)If  notIsNothing (CMD) ThenCmd. Dispose () cmd = Nothing        End If    End SubEnd Class

Summary: Originally wrote two simple function, finally found not enough, had to continue to reconstruct SqlHelper class chant. Found himself the second time to write, for this database access helper class has been hand to, write code to such as flowing, this is probably the process of learning, repeat the number of times, do more will be. So, no you can not do, please believe that they will be able to!

vb.net how to write high-efficiency sqlhelper--(ii) Code combat