datacreate.vb

ASP,VB.NET写的,用的SQLSERVER数据库,影片超市系统,属于网上购物系统

Imports System.Data.SqlClient
Public Class DataCreate
    Private mycon As SqlConnection
    Private constr As String = "uid=sa;pwd=;database=01级电子商务;server=(local)"

    '建立检查库、表、存储过程是否存在的方法
    Public Function checkExists(ByVal who As String) As String
        'constr = "uid=sa;pwd=;database=01级1;server=(local)"

        If who = "库" Then constr = "uid=sa;pwd=;server=(local)"

        mycon = New SqlConnection(constr)

        Dim cdb As String
        If who = "库" Then cdb = "SELECT name FROM sysdatabases  WHERE name = '01级电子商务'"

        If who = "表" Then cdb = "select name from sysobjects where name='用户表'" & _
               "and type='u'"

        If who = "过程" Then cdb = "select name from sysobjects " & _
             "where name='inUserTable' and type='p'"

        Dim crecom As New SqlCommand(cdb, mycon)

        Try
            mycon.Open()
            Dim aa As String
            aa = crecom.ExecuteScalar
            mycon.Close()

            If aa = "" Then
                Return "没有"
            Else
                Return "存在"
            End If
        Catch
            Return "出错"
        End Try
    End Function

    '建库
    Public Function createDB() As Boolean
        Dim str As String = "uid=sa;pwd=;server=(local)"
        mycon = New SqlConnection(str)

        Dim sqlstr As String
        sqlstr = "CREATE DATABASE [01级电子商务]  " & _
        "ON (NAME = N'01级电子商务_Data', FILENAME = N'd:\01级电子商务_Data.MDF' , " & _
        "SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'01级电子商务_Log', " & _
        "FILENAME = N'd:\01级电子商务_Log.LDF' , SIZE = 1, FILEGROWTH = 10%) " & _
        "COLLATE Chinese_PRC_CI_AS "

        Dim mycom As New SqlCommand(sqlstr, mycon)

        mycon.Open()
        mycom.ExecuteNonQuery()
        mycon.Close()
        Return True
    End Function

    '建全部表
    Public Function CreateTable()
        Dim ctab As String

        '定义事务
        Dim myTrans As SqlTransaction

        Dim crecom As New SqlCommand()
        mycon = New SqlConnection(constr)

        ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_商品_分类]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[商品] DROP CONSTRAINT FK_商品_分类"
        mycon.Open()
        myTrans = mycon.BeginTransaction()
        crecom.Connection = mycon
        crecom.Transaction = myTrans
        Try
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_用户表_口令]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[用户表] DROP CONSTRAINT FK_用户表_口令"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[分类]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[分类]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[口令]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[口令]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[商品]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[商品]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用户表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[用户表]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "CREATE TABLE [dbo].[分类] (" & _
                "[分类名] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL , " & _
                "[说明] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL " & _
                ") ON [PRIMARY]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "CREATE TABLE [dbo].[口令] (" & _
                "[用户名] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[口令] [varchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[权限] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL " & _
                ") ON [PRIMARY]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "CREATE TABLE [dbo].[商品] ( " & _
                "[ID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ," & _
                "[分类] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[片名] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[国家] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[格式] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[单价] [decimal](9, 2) NOT NULL," & _
                "[数量] [integer] NOT NULL " & _
                ") ON [PRIMARY]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "CREATE TABLE [dbo].[用户表] (" & _
                "[用户名] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[姓名] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[年龄] [int] NOT NULL ," & _
                "[性别] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[学历] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[电话] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ," & _
                "[email] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ," & _
                "[图片] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL " & _
                ") ON [PRIMARY]"
            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            ctab = "ALTER TABLE [dbo].[分类] WITH NOCHECK ADD " & _
                "CONSTRAINT [PK_分类] PRIMARY KEY  CLUSTERED " & _
                "([分类名])  ON [PRIMARY] " & _
                "ALTER TABLE [dbo].[口令] WITH NOCHECK ADD " & _
                "CONSTRAINT [PK_口令] PRIMARY KEY  CLUSTERED " & _
                "([用户名])  ON [PRIMARY] " & _
                "ALTER TABLE [dbo].[商品] WITH NOCHECK ADD " & _
                "CONSTRAINT [PK_商品] PRIMARY KEY  CLUSTERED " & _
                "([ID])  ON [PRIMARY] " & _
                "ALTER TABLE [dbo].[用户表] WITH NOCHECK ADD " & _
                "CONSTRAINT [PK_用户表] PRIMARY KEY  CLUSTERED " & _
                "([用户名])  ON [PRIMARY] " & _
                "ALTER TABLE [dbo].[分类] WITH NOCHECK ADD " & _
                "CONSTRAINT [DF_分类_说明] DEFAULT ('') FOR [说明]" & _
                "ALTER TABLE [dbo].[商品] WITH NOCHECK ADD " & _
                "CONSTRAINT [DF_商品_国家] DEFAULT ('') FOR [国家]" & _
                "ALTER TABLE [dbo].[商品] ADD " & _
                "CONSTRAINT [FK_商品_分类] FOREIGN KEY " & _
                "([分类]) REFERENCES [dbo].[分类] ([分类名]) " & _
                "ON DELETE CASCADE  ON UPDATE CASCADE " & _
                "ALTER TABLE [dbo].[用户表] ADD " & _
                "CONSTRAINT [FK_用户表_口令] FOREIGN KEY " & _
                "([用户名]) REFERENCES [dbo].[口令] " & _
                "([用户名]) ON DELETE CASCADE  ON UPDATE CASCADE"

            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()

            myTrans.Commit()
            mycon.Close()
            Return True
        Catch
            myTrans.Rollback()
            Return False
        End Try

    End Function

    '建立存储过程的方法
    Public Function CreateProc() As Boolean
        Dim ctab As String
        Dim crecom As New SqlCommand()
        mycon = New SqlConnection(constr)

        ctab = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[inUserTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" & _
            "drop procedure [dbo].[inUserTable] " & _
            "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IN商品]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) " & _
            " drop procedure [dbo].[IN商品] " & _
            "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[inflb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) " & _
            " drop procedure [dbo].[inflb] "
        crecom.CommandText = ctab
        crecom.Connection = mycon

        Try
            mycon.Open()
            crecom.ExecuteNonQuery()

            ctab = "CREATE PROC inUserTable  " & _
                "@uid varchar(20)," & _
                "@xm varchar(10), " & _
                "@age int, " & _
                "@sex char(2)," & _
                "@xl varchar(30)," & _
                "@Tel varchar(30)," & _
                "@email varchar(30)," & _
                "@tp varchar(30) " & _
                "AS insert into 用户表 " & _
                "(用户名,姓名,年龄,性别,学历,电话,email,图片) values(@uid,@xm,@age,@sex,@xl,@tel,@email,@tp) "

            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()
            ctab = " CREATE PROCEDURE IN商品 " & _
                "@fl varchar(20)," & _
                "@pm varchar(20)," & _
                "@pp varchar(20)," & _
                "@xh varchar(20)," & _
                "@dj decimal(5,2), " & _
                "@sl integer " & _
                "AS " & _
                "insert into 商品(分类,片名,国家,格式,单价,数量) values(@fl,@pm,@pp,@xh,@dj,@sl) "

            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()
            ctab = " CREATE PROCEDURE inflb " & _
                "@flm varchar(20)," & _
                "@sm varchar(50) " & _
                "AS " & _
                "insert into 分类 values(@flm,@sm) "

            crecom.CommandText = ctab
            crecom.ExecuteNonQuery()
            mycon.Close()
            Return True
        Catch ex As Exception
            mycon.Close()
            Return False
        End Try
    End Function

End Class

#Region "老"
''建立存储过程的方法
'Public Function CreateProc() As Boolean
'    mycon = New SqlConnection(constr)
'    Dim crecom As New SqlCommand
'    Dim Str As String = _
'    "if exists(select [name] from sysobjects " & _
'    "where name='inUserTable' and type='p') " & _
'    "drop proc inUserTable"

'    crecom.CommandText = Str
'    mycon.Open()
'    crecom.Connection = mycon
'    crecom.ExecuteNonQuery()

'    Str = "CREATE PROC inUserTable  " & _
'          "@uid varchar(20), " & _
'          "@xm varchar(10), " & _
'          "@age int, " & _
'          "@sex char(2)," & _
'          "@xl varchar(30)," & _
'          "@Tel varchar(30)," & _
'          "@email varchar(30)," & _
'          "@tp varchar(30) " & _
'          "AS insert into 用户表" & _
'          "(用户名,姓名,年龄,性别,学历,电话,email,图片) " & _
'                "values(@uid,@xm,@age,@sex,@xl,@tel,@email,@tp)"
'    crecom.CommandText = Str
'    'Try
'    crecom.ExecuteNonQuery()
'    Return True
'    'Catch
'    '    Return False
'    'End Try
'End Function
#End Region