简单数据库连接（SQL Server2008）

简单数据库连接（SQL Server2008）

1. 执行

    Public Shared Function ExcuteNonQuery(ByVal strSql As String, ByVal Parameters() As SqlParameter)
        Dim cmd As New SqlCommand
        Dim connectionString As String = Common.fncGetConString("ConnectionString")
        Dim cmdText As String = strSql
        Dim conn As New SqlConnection(connectionString)
        Dim intRest As Integer
        conn.Open()
        cmd = New SqlCommand(cmdText, conn)
        cmd.CommandTimeout = 150
        If Not Parameters Is Nothing Then
            For Each parm As SqlParameter In Parameters
                cmd.Parameters.Add(parm)
            Next
        End If
        intRest = cmd.ExecuteNonQuery()
        cmd.Parameters.Clear()
        conn.Close()
        Return intRest
    End Function

 2.获取    //同Dataset

    Public Shared Function GetDataTable(ByVal strSql As String) As DataTable
        Dim cmd As New SqlCommand
        Dim connectionString As String = Common.fncGetConString("ConnectionString")
        Dim cmdText As String = strSql
        Dim conn As New SqlConnection(connectionString)
        Dim dt As New DataTable
        Dim adp As New SqlDataAdapter
        conn.Open()
        cmd = New SqlCommand(cmdText, conn)
        cmd.CommandTimeout = 150
        adp = New SqlDataAdapter(cmd)
        adp.Fill(dt)
        cmd.Parameters.Clear()
        conn.Close()
        Return dt
    End Function

3.连接字

Public Shared Function fncGetConString(ByVal Getstr As String) As String
        fncGetConString = ConfigurationManager.ConnectionStrings(Getstr).ConnectionString
    End Function

4.配置文件

    <add name="ConnectionString" connectionString="Data Source="服务器";Initial Catalog="数据库";User ID=sa;Password=123456"
      providerName="System.Data.SqlClient" />

5.PS

Dim objParameter() As SqlParameter = _
            { _
                New SqlParameter("@TempPar1", SqlDbType.VarChar, 50), _
                New SqlParameter("@TempPar2", SqlDbType.NVarChar, 50) _
            }