sqldatabase.vb

vb2005数据库完整代码

Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls


Public Class SqlDataBase
    Private sqlConn As SqlConnection
    Private sqlDa As SqlDataAdapter
    Private sqlDr As SqlDataReader
    Private cmd As SqlCommand
    Private ds As DataSet
    Private dv As DataView

    Public Function SqlDataBase() As VariantType

    End Function
#Region "打开数据库连接"
    '
    ' 打开数据库连接
    '
    Private Sub Open()

        Dim sqlStr As ConnectionStringSettings
        sqlStr = ConfigurationManager.ConnectionStrings("jdflow")
        sqlConn = New SqlConnection()
        sqlConn.ConnectionString = sqlStr.ConnectionString
        sqlConn.Open()
    End Sub

#End Region


#Region "关闭数据库连接"
    '
    ' 关闭数据库连接
    '
    Private Sub Close()
        If Not (sqlConn Is Nothing) Then
            sqlConn.Close()
            sqlConn.Dispose()
        End If
    End Sub

#End Region


#Region "释放资源"
    ' <summary>
    ' 释放资源
    '</summary>
    Public Sub Dispose()

        ' 确认连接是否已经关闭
        If Not (sqlConn Is Nothing) Then
            sqlConn.Dispose()
            sqlConn = Nothing
        End If
    End Sub


#End Region

#Region "根据DATATABLE在数据库中创建表"
    ''' <summary>
    ''' 根据DATATABLE在数据库中创建表
    ''' </summary>
    ''' <param name="dt">给出的DATATABLE</param>
    ''' <param name="tableName">要创建的表名</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CreateTable(ByVal dt As DataTable, ByVal tableName As String) As Boolean
        Dim bool As Boolean = False
        Dim sqlDbType As New SqlDbType
        Try
            RunSql("DROP TABLE " & tableName)
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
        Dim sqlstr As String = "CREATE TABLE " & tableName & " ("
        For Each dc As DataColumn In dt.Columns
            Dim type As String = [Enum].GetName(sqlDbType.GetType(), StringToSqlDbType(dc.DataType.ToString.Split(".")(1)))
            If type = "NVarChar" Or type = "VarChar" Or type = "Binary" Or type = "VarBinary" Or type = "Char" Or type = "NChar" Then
                sqlstr = sqlstr & "[" & dc.ColumnName & "] " & type & "(255),"
            Else
                sqlstr = sqlstr & "[" & dc.ColumnName & "] " & type & ","
            End If
        Next
        sqlstr = sqlstr.TrimEnd(",") & " )"
        Try
            RunSql(sqlstr)
            bool = True
        Catch ex As Exception
            bool = False
            'MsgBox(ex.Message)
        End Try
        If bool = True Then
            If dt.Rows.Count > 0 Then
                bool = False
                For Each dr As DataRow In dt.Rows
                    sqlstr = "INSERT INTO " & tableName & "("
                    For Each dc As DataColumn In dt.Columns
                        sqlstr = sqlstr & "[" & dc.ColumnName & "],"
                    Next
                    sqlstr = sqlstr.TrimEnd(",") & ") VALUES ("
                    For Each dc As DataColumn In dt.Columns
                        Dim str As String = dr(dc.ColumnName).ToString.Trim.Replace("'", """")
                        If String.IsNullOrEmpty(str) Then
                            str = "NULL"
                        Else
                            str = "'" & str & "'"
                        End If
                        sqlstr = sqlstr & str & ","
                    Next
                    sqlstr = sqlstr.TrimEnd(",") & ")"
                    Try
                        RunSql(sqlstr)
                        bool = True
                    Catch ex As Exception
                        bool = False
                        MsgBox(ex.Message)
                    End Try
                Next
            End If
        End If
        Return bool
    End Function
#End Region

#Region "读取EXCEL表格数据"
    ''' <summary>
    ''' 读取EXCEL表格数据
    ''' </summary>
    ''' <param name="fileName">要读取的EXCEL文件完整路径</param>
    ''' <param name="sheetName">EXCEL的工作表名,默认Sheel1$</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetExcelData(ByVal fileName As String, ByVal sheetName As String) As DataSet
        If File.Exists(fileName) = False Then
            Return Nothing
        End If
        If String.IsNullOrEmpty(sheetName) Then
            sheetName = "Sheet1$"
        End If
        Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName & ";Extended Properties=Excel 8.0;"
        Dim oleDa As OleDbDataAdapter = New OleDbDataAdapter(String.Format("select * from [{0}]", sheetName), strCon)
        Dim oleDs As New DataSet
        Try
            oleDa.Fill(oleDs, "excelData")
        Catch ex As Exception
            Throw ex
        End Try
        Return oleDs
    End Function

#End Region


#Region "返回DataSet数据集"
    '
    ' 返回DataSet数据集
    '
    Public Function GetDs(ByVal strSql As String) As DataSet
        Open()
        sqlDa = New SqlDataAdapter(strSql, sqlConn)
        ds = New DataSet
        sqlDa.Fill(ds)
        Close()
        Return ds
    End Function

#End Region


#Region "返回DataView数据视图"
    '
    '返回DataView数据视图
    '
    Public Function GetDv(ByVal strsql As String) As DataView
        ds = GetDs(strsql)
        dv = ds.Tables(0).DefaultView
        Return dv
    End Function

#End Region


#Region "获得DataTable对象"
    '
    ' 获得DataTable对象
    '
    Public Function GetTable(ByVal strSql As String) As DataTable
        Return GetDs(strSql).Tables(0)
    End Function

#End Region


#Region "获得SqlDataReader对象,使用完须关闭DataReader,关闭数据库连接"
    '
    ' 获得SqlDataReader对象
    '
    Public Function GetDataReader(ByVal strSql As String) As SqlDataReader
        Open()
        cmd = New SqlCommand(strSql, sqlConn)
        sqlDr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
        Return sqlDr
    End Function

#End Region

#Region "公用查询"
    ''' <summary>
    '''  公用查询
    ''' </summary>
    ''' <param name="tableName">查询的表名称</param>
    ''' <param name="fields">查询的字段</param>
    ''' <param name="filter">查询的条件</param>
    ''' <param name="orderby">排序的字段</param>
    ''' <param name="order">排序的顺序(ASC/DESC)</param>
    ''' <returns>返回datatable对象</returns>
    Public Function TSelect(ByVal tableName As String, ByVal fields As String, ByVal filter As String, ByVal orderby As String, ByVal order As String) As DataTable
        Dim sqlstr As String
        If filter = "" And orderby = "" And order = "" Then
            sqlstr = "select " & fields.Trim() & " from " & tableName.Trim()
        ElseIf orderby = "" And order = "" Then
            sqlstr = "select " & fields.Trim() & " from " & tableName.Trim() & " where " & filter.Trim()
        Else
            sqlstr = "select " & fields & " from " & tableName & " where " & filter & " order by " & orderby.Trim() & " " & order.Trim()
        End If
        Dim dt As DataTable = New DataTable
        dt = GetTable(sqlstr)
        Return dt
    End Function

    Public Function TSelect(ByVal tableName As String, ByVal fields As String) As DataTable
        Return TSelect(tableName, fields, "", "", "")
    End Function


    Public Function TSelect(ByVal tableName As String, ByVal fields As String, ByVal filter As String) As DataTable
        Return TSelect(tableName, fields, filter, "", "")
    End Function

#End Region


#Region "简单执行SQL语句"
    '
    ' 简单执行SQL语句
    '
    Public Sub RunSql(ByVal strSql As String)
        Open()
        cmd = New SqlCommand(strSql, sqlConn)
        cmd.ExecuteNonQuery()
        Close()
    End Sub

#End Region

#Region "执行SQL语句，返回第一行第一列结果"
    '
    ' 执行SQL语句，返回第一行第一列结果
    '
    Public Function RunSqlReturn(ByVal strSql As String) As String
        Dim strReturn As String = ""
        Open()
        Try
            cmd = New SqlCommand(strSql, sqlConn)
            strReturn = cmd.ExecuteScalar.ToString()
        Catch

        End Try
        Close()
        Return strReturn
    End Function

#End Region


#Region "创建一个SqlCommand对象以此来执行存储过程"
    ' <summary>
    ' 创建一个SqlCommand对象以此来执行存储过程
    ' </summary>
    ' <param name="procName">存储过程的名称</param>
    ' <param name="prams">存储过程所需参数</param>
    ' <returns>返回SqlCommand对象</returns>
    Public Function CreateCommand(ByVal procName As String, ByVal params() As SqlParameter) As SqlCommand

        '确认打开连接
        Open()
        Dim cmd As SqlCommand = New SqlCommand(procName, sqlConn)
        cmd.CommandType = CommandType.StoredProcedure

        '依次把参数传入存储过程
        If Not (params Is Nothing) Then
            For Each parameter As SqlParameter In params
                cmd.Parameters.Add(parameter)
            Next
        End If

        '加入返回参数
        cmd.Parameters.Add(New SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, False, 0, 0, String.Empty, DataRowVersion.Default, Nothing))
        Return cmd
    End Function