module1.vb

Visual Basic 2005数据库通用模块开发与系统移植 物业管理信息系统

Imports System.Data.SqlClient

Imports System.IO
Module Module1
    Public User As DBUser

    Public FrmMainobj As New frmMain()
  
    Public Const SQL_CONNECTION_STRING As String = _
          "Server=localhost;" & _
          "DataBase=;" & _
          "Integrated Security=SSPI"
    Public Const MSDE_CONNECTION_STRING As String = _
        "Server=(local)\NetSDK;" & _
        "DataBase=;" & _
        "Integrated Security=SSPI"
    Public Const CONNECTION_ERROR_MSG As String = _
        "要运行这个实例，机器上必须安装有SQL Server " & _
        "或者 MSDE，并且带有Northwind数据库"
    Public bolDidPreviouslyConnect As Boolean = False
    Public bolDidCreateTable As Boolean = False
    Public connectionString As String = SQL_CONNECTION_STRING


    Public Sub CreateDataBase()
        Dim strSQL As String = _
            "IF NOT EXISTS (" & _
            "SELECT * " & _
            "FROM master..sysdatabases " & _
            "WHERE Name = 'Wuye')" & vbCrLf & _
            "DROP DATABASE Wuye" & vbCrLf & _
            "CREATE DATABASE Wuye"
        '如果不存在该数据库，进行初始化操作，创建数据库
        ' 准备连接到本地的SQL Server或者MSDE 
        Dim bolIsConnecting As Boolean = True
        While bolIsConnecting
            Try
                Dim northwindConnection As New SqlConnection(connectionString)
                Dim cmd As New SqlCommand(strSQL, northwindConnection)
                northwindConnection.Open()
                cmd.ExecuteNonQuery()
                northwindConnection.Close()
                ' 关闭状态窗口
                bolIsConnecting = False
                bolDidPreviouslyConnect = True
                bolDidCreateTable = True
                MessageBox.Show("成功创建""Wuye""数据库", _
                    "数据库创建状态", MessageBoxButtons.OK, _
                    MessageBoxIcon.Information)
            Catch sqlExc As SqlException
                MessageBox.Show(sqlExc.ToString, "SQL 异常错误!", _
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
                Exit While
            Catch exc As Exception
                If connectionString = SQL_CONNECTION_STRING Then
                    ' 如果不能连接到SQL Server，尝试连接 MSDE.
                    connectionString = MSDE_CONNECTION_STRING
                Else
                    'SQL Server和MSDE都不能连接
                    MessageBox.Show(CONNECTION_ERROR_MSG, _
                            "连接失败!", MessageBoxButtons.OK, _
                            MessageBoxIcon.Error)
                    End
                End If
            End Try
        End While
    End Sub

    Private Sub CreateTable()
        Dim strSQL As String = _
            "USE Wuye" & vbCrLf & _
            "IF EXISTS (" & _
            "SELECT * " & _
            "FROM Wuye.dbo.sysobjects " & _
            "WHERE Name = 'tbUser' " & _
            "AND TYPE = 'u')" & vbCrLf & _
            "BEGIN" & vbCrLf & _
            "DROP TABLE HowToDemo.dbo.tbUser" & vbCrLf & _
            "END" & vbCrLf & _
            "CREATE TABLE tbUser (" & _
            "UserName VarChar(10) NOT NULL," & _
            "Password VarChar(10) NOT NULL," & _
            "RealName NVarChar(10) NOT NULL," & _
            "ModHouse NVarChar(3) NOT NULL," & _
            "ModFee NVarChar(3) NOT NULL," & _
            "ModRepair NVarChar(3) NOT NULL," & _
            "Memo NVarChar(3) NOT NULL"


        Try
            Dim northwindConnection As New SqlConnection(connectionString)
            Dim cmd As New SqlCommand(strSQL, northwindConnection)
            '打开连接，执行SQL语句，然后关闭连接
            northwindConnection.Open()
            cmd.ExecuteNonQuery()
            northwindConnection.Close()
            MessageBox.Show("成功创建 ""NW_Seafood""表", _
                "创建表状态", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch sqlExc As SqlException
            MessageBox.Show(sqlExc.ToString, "SQL 异常错误!", _
                     MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub CreatePro()
        '创建存储过程 " GetProducts "
        Dim northwindConnection As New SqlConnection(connectionString)
        Dim scmd As SqlCommand
        scmd.Connection = northwindConnection
        scmd.CommandText = _
             "IF EXISTS (" & _
             "SELECT * " & _
             "FROM Wuye.dbo.sysobjects " & _
             "WHERE Name = 'DeleteRepair' " & _
             "AND TYPE = 'p')" & vbCrLf & _
             "DROP PROCEDURE DeleteRepair"
        Try
            scmd.ExecuteNonQuery()
        Catch expSql As SqlException
            MessageBox.Show(expSql.ToString, "物业管理系统", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
        scmd.CommandText = _
            "CREATE PROCEDURE DeleteRepair " & vbCrLf & _
            "@RepairNo Int " & vbCrLf & _
            "AS " & vbCrLf & _
            "DELETE FROM tbRepair WHERE RepairNo = @RepairNo"
        Try
            scmd.ExecuteNonQuery()
        Catch expSql As SqlException
            MessageBox.Show(expSql.ToString, "物业管理系统", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
            Exit Sub
        End Try
    End Sub

End Module