【ASP.Net】PetShop4.0学习笔记1（VB）

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› ASP.NET›ASP.NET编程经验

原作者: [db:作者] 来自: [db:来源] 收藏邀请

最近在学ASP.Net，在网上找了许久也没找到VB版的Pet Shop,只好下了C#的自己翻译，就当熟悉C#语法了。

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.ComponentModel

Namespace PetShop.DBUtility

    ''' <summary>
    ''' The SqlHelper class is intended to encapsulate high performance,
    ''' scalable best practices for common uses of SqlClient.
    ''' </summary>
    Public MustInherit Class SQLHelper

        'Database connection strings
        Public Shared ReadOnly ConnectionStringLocalTransaction As String = ConfigurationManager.ConnectionStrings("SQLConnString1").ConnectionString
        Public Shared ReadOnly ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings("SQLConnString2").ConnectionString
        Public Shared ReadOnly ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings("SQLConnString3").ConnectionString
        Public Shared ReadOnly ConnectionStringProfile = ConfigurationManager.ConnectionStrings("SQLProfileConnString").ConnectionString

        'Hashtable to store cached parameters
        Private Shared parmCache As Hashtable = Hashtable.Synchronized(New Hashtable)

        '''<summary>
        '''Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
        '''using the provided parameters.
        '''</summary>
        '''<remarks>
        '''e.g.:
        ''' int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        '''</remarks>
        '''<param name="connectionString">a valid connection string for a SqlConnection</param>
        '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        '''<param name="cmdText">the stored procedure name or T-SQL command</param>
        '''<param name="commandParameters">an array of SqlParamters used to execute the command</param>
        '''<returns>an int representing the number of rows affected by the command</returns>
        Public Shared Function ExecuteNonQuery(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Integer

            Dim cmd As SqlCommand = New SqlCommand

            Using conn As SqlConnection = New SqlConnection(connectionString)
                PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
                Dim val As Integer = cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                conn.Dispose()
                Return val
            End Using

        End Function

        '''<summary>
        '''Execute a SqlCommand (that returns no resultset) against an existing database connection
        '''using the provided parameters.
        '''</summary>
        '''<remarks>
        '''e.g.:
        ''' int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        '''</remarks>
        '''<param name="conection">an existing database connection</param>
        '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        '''<param name="cmdText">the stored procedure name or T-SQL command</param>
        '''<param name="commandParameters">an array of SqlParamters used to execute the command</param>
        '''<returns>an int representing the number of rows affected by the command</returns>
        Public Shared Function ExecuteNonQuery(ByVal conection As SqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter())

            Dim cmd As SqlCommand = New SqlCommand
            PrepareCommand(cmd, conection, Nothing, cmdType, cmdText, commandParameters)
            Dim val As Integer = cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
            Return val

        End Function

        '''<summary>
        '''Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction
        '''using the provided parameters.
        '''</summary>
        '''<remarks>
        '''e.g.:
        ''' int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        '''</remarks>
        '''<param name="trans">an existing sql transaction</param>
        '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        '''<param name="cmdText">the stored procedure name or T-SQL command</param>
        '''<param name="commandParameters">an array of SqlParamters used to execute the command</param>
        '''<returns>an int representing the number of rows affected by the command</returns>
        Public Shared Function ExecuteNonQuery(ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Integer

            Dim cmd As SqlCommand = New SqlCommand()
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters)
            Dim val As Integer = cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
            Return val

        End Function

        '''<summary>
        '''Execute a SqlCommand that returns a resultset against the database specified in the connection string
        '''using the provided parameters.
        '''</summary>
        '''<remarks>
        '''e.g.:
        ''' SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        '''</remarks>
        '''<param name="connectionString">a valid connection string for a SqlConnection</param>
        '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        '''<param name="cmdText">the stored procedure name or T-SQL command</param>
        '''<param name="commandParameters">an array of SqlParamters used to execute the command</param>
        '''<returns>A SqlDataReader containing the results</returns>
        Public Shared Function ExcuteReader(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As SqlDataReader
            Dim cmd As SqlCommand = New SqlCommand()
            Dim conn As SqlConnection = New SqlConnection(connectionString)

            Try
                PrepareCommand(cmd, conn, Nothing, cmdType, cmdText, commandParameters)
                Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                cmd.Parameters.Clear()
                Return rdr
            Catch ex As Exception
                conn.Close()
                ExceptionManagement.SystemError.SystemLog(ex.Message)
                Throw
            End Try

        End Function

        '''<summary>
        '''Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
        '''using the provided parameters.
        '''</summary>
        '''<remarks>
        '''e.g.:
        ''' Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        '''</remarks>
        '''<param name="connectionString">a valid connection string for a SqlConnection</param>
        '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        '''<param name="cmdText">the stored procedure name or T-SQL command</param>
        '''<param name="commandParameters">an array of SqlParamters used to execute the command</param>
        '''<returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        Public Shared Function ExcuteScalar(ByVal connectionString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Object

            Dim cmd As SqlCommand = New SqlCommand

            Using connection As SqlConnection = New SqlConnection(connectionString)
                Dim val As Object = cmd.ExecuteScalar
                cmd.Parameters.Clear()
                connection.Dispose()
                Return val
            End Using

        End Function

        '''<summary>
        ''' Execute a SqlCommand that returns the first column of the first record against an existing database connection
        '''using the provided parameters.
        '''</summary>
        '''<remarks>
        '''e.g.:
        ''' Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        '''</remarks>
        '''<param name="connection">an existing database connection</param>
        '''<param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
        '''<param name="cmdText">the stored procedure name or T-SQL command</param>
        '''<param name="commandParameters">an array of SqlParamters used to execute the command</param>
        '''<returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        Public Shared Function ExcuteScalar(ByVal connection As SqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal ParamArray commandParameters As SqlParameter()) As Object

            Dim cmd As SqlCommand = New SqlCommand

            PrepareCommand(cmd, connection, Nothing, cmdType, cmdText, commandParameters)
            Dim val As Object = cmd.ExecuteScalar
            cmd.Parameters.Clear()
            connection.Dispose()
            Return val

        End Function

        ''' <summary>
        ''' add parameter array to the cache
        ''' </summary>
        ''' <param name="cacheKey">Key to the parameter cache</param>
        ''' <param name="commandParameters">an array of SqlParamters to be cached</param>
        Public Shared Sub CacheParameters(ByVal cacheKey As String, ByVal ParamArray commandParameters As SqlParameter())
            parmCache(cacheKey) = commandParameters
        End Sub

        ''' <summary>
        ''' Retrieve cached parameters
        ''' </summary>
        ''' <param name="cacheKey">key used to lookup parameters</param>
        ''' <returns>Cached SqlParamters array</returns>
        Public Shared Function GetCachedParameters(ByVal cacheKey As String) As SqlParameter()
            Dim cachedParms() As SqlParameter = CType(parmCache(cacheKey), SqlParameter())
            If cachedParms Is Nothing Then
                Return Nothing
            End If
            Dim clonedParms As SqlParameter() = New SqlParameter(cachedParms.Length - 1) {}

            Dim i As Integer
            For i = 0 To cachedParms.Length - 1
                clonedParms(i) = DirectCast(DirectCast(cachedParms(i), ICloneable).Clone(), SqlParameter)
            Next

            Return clonedParms

        End Function

        '''<summary>
        '''Prepare a command for execution
        '''</summary>
        '''<param name="cmd">SqlCommand object</param>
        '''<param name="conn">SqlConnection object</param>
        '''<param name="trans">SqlTransaction object</param>
        '''<param name="cmdType">Cmd type e.g. stored procedure or text</param>
        '''<param name="cmdText">Command text, e.g. Select * from Products</param>
        '''<param name="cmdParms">SqlParameters to use in the command</param>
        Private Shared Sub PrepareCommand(ByRef cmd As SqlCommand, ByVal conn As SqlConnection, ByVal trans As SqlTransaction, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As SqlParameter())
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If

            cmd.Connection = conn
            cmd.CommandText = cmdText

            If trans IsNot Nothing Then
                cmd.Transaction = trans
            End If

            cmd.CommandType = cmdType

            If cmdParms IsNot Nothing Then
                For Each parm As SqlParameter In cmdParms
                    cmd.Parameters.Add(parm)
                Next
            End If

        End Sub

    End Class

End Namespace

MSDN

DirectCast 关键字：

引入类型转换操作。该关键字的使用方法与 CType 关键字相同，如下列所示：


Dim Q As Object  
                       
                    
                     
                      



鲜花




握手




雷人




路过




鸡蛋





 
                    
                    
                     
                    
                    
                    
                     
                    
                  
                   
                  
                   
                  
                
                 
                
                
                
                 
                 
                
   
    该文章已有0人参与评论
    请发表评论
    
  
   
    
     
    
        
    
    
    





                











                

    
    
    

   
    
         

    全部评论
     
    
        
    
              
  


 
                     
                
            
            
                    
    	
            
            
                
                    专题导读
                    More+
                    
                
                
                    
                    
                        

                        
                    
                
            

                        
                
                	上一篇：
                    『ExtJS』ExtJSGrid与Asp.NET通信发布时间：2022-07-10                    下一篇：
                    ASP.NETMVC异步上传图片和富文本编辑器的使用详解发布时间：2022-07-10                    
                
            
                        
            
                
                    热门推荐
                    More+
                    
                
                
                    

                    

                    
                
                
            
            
            
                
                    热门话题
                    More+
                    
                
                
                    
                        

                        
                    
                
            
            
            
                
                    

                    
                
            
            
            
                
                    阅读排行榜
                    
                    
                
                
                    
                        

                                                       
                    
                
                
            
            
            
                
                    

                    
                
            
        
    	







	
    
    
    	
        	
            	
                	
                    	
                        	关于我们
                            
                                创业团队
                                加入我们
                                媒体报道
                                合作伙伴
                                公益事业
                            
                        
                        
                        	产品与服务
                            
                            	寻求合作
                                项目投资
                                干货视频
                                经理人培训
                                招聘代理
                            
                        
                        
                        	解决方案
                            
                            	一站式
                                制造业
                                教育科研
                                行业案例
                            
                        
                        
                    
                
            	
                	
                    
                    	扫描微信二维码
                        查看手机版网站
                        随时了解更新最新资讯
                    
                    
                
            	
                	
                    	139-2527-9053
                        在线客服（服务时间 9:00～18:00）
                        在线QQ客服
                    
                    地址：深圳市南山区西丽大学城创智工业园
                    电邮：jeky_zhao#qq.com
                    移动电话：139-2527-9053
                
                
            
        
        
        
        	Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap



        
        
    
    



返回顶部

客服电话

电子邮件

【ASP.Net】PetShop4.0学习笔记1（VB）

请发表评论

全部评论

上一篇：

下一篇：

关于我们

产品与服务

解决方案

139-2527-9053