ModuleMaster/createtable.aspx.vb - main - ivan/ModMas

ModuleMaster/createtable.aspx.vb
145 строк6 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
Imports System.Data.SqlClient
Imports System.Data

Partial Class createTable
    Inherits System.Web.UI.Page

    Private sqlf As New sharedClasses.dbFunctions.SqlFunctions

    Protected Sub btnSave_Click(sender As Object, e As System.EventArgs) Handles btnSave.Click
        CreateTable(ddlModule.SelectedValue, txtTableName.Text, txtTableDescription.Text)
        editableGrid2.RefreshData()
        ddlTablesList.DataBind()
    End Sub

    Private Sub CreateTable(moduleId As Integer, tableName As String, description As String)
        Dim sql As New StringBuilder
        sql.Append("INSERT ")
        sql.Append("INTO [UserModules].[DBO].[ModuleTables] ")
        sql.Append("  ( ")
        sql.Append("    [ModuleId] , ")
        sql.Append("    [TableName] , ")
        sql.Append("    [TableDescription] , ")
        sql.Append("    [DbTableName] , ")
        sql.Append("    [Creator] ")
        sql.Append("  ) ")
        sql.Append("  VALUES ")
        sql.Append("  ( ")
        sql.Append("    @ModuleId , ")
        sql.Append("    @TableName , ")
        sql.Append("    @TableDescription , ")
        sql.Append("    @DbTableName , ")
        sql.Append("    @Creator ")
        sql.Append("  )")
        Dim com As New SqlCommand
        With com.Parameters
            .AddWithValue("moduleid", moduleId)
            .AddWithValue("tablename", tableName)
            .AddWithValue("tabledescription", description)
            .AddWithValue("dbtablename", tableName.Replace(" ", "_").ToUpper)
            .AddWithValue("creator", "I.KRIVOKON")
        End With
        com.CommandText = sql.ToString
        sqlf.NonQuery(com)
    End Sub

    Protected Sub btnCreateTable_Click(sender As Object, e As System.EventArgs) Handles btnCreateTable.Click
        txtCreateTableScript.Text = CreateTable(ddlTablesList.SelectedValue, chkDropBEforeCreate.Checked)
        sqlf.NonQuery(txtCreateTableScript.Text)
        editableGrid4.tableName = "[UserModules].[dbo]." & ddlTablesList.SelectedItem.Text
        'editableGrid4.sqlQuery = "SELECT * FROM " & editableGrid4.tableName
        editableGrid4.RefreshData()
    End Sub

    Private Function CreateTable(tableId As Integer, Optional dropBeforeCreate As Boolean = False) As String

        Dim tableName As String = sqlF.GetScalar("SELECT DbTableName  from [UserModules].[dbo].[ModuleTables] where id = " & tableId)
        If Not dropBeforeCreate Then
            If sqlF.GetScalar("SELECT count(*) FROM UserModules.sys.tables where name ='" & tableName & "'") <> 0 Then
                Return "Table " & tableName & " already exist!"
            End If
        Else
            sqlF.NonQuery("drop table [UserModules].[dbo].[" & tableName & "]")
        End If

        Dim tableFields As DataTable
        Try
            tableFields = sqlF.GetData("SELECT * FROM [UserModules].[dbo].[TableFields] WHERE tableid = " & tableId)
        Catch ex As Exception
            Return "Error!"""
        End Try

        Dim sql As New StringBuilder
        sql.Append("USE [UserModules]")
        sql.Append(vbNewLine)
        'sql.Append("GO")
        'sql.Append(vbNewLine)
        sql.Append("SET ANSI_NULLS ON ")
        sql.Append(vbNewLine)
        'sql.Append("GO")
        'sql.Append(vbNewLine)
        sql.Append("SET QUOTED_IDENTIFIER ON ")
        sql.Append(vbNewLine)
        'sql.Append("GO")
        'sql.Append(vbNewLine)
        sql.Append("SET ANSI_PADDING ON")
        sql.Append(vbNewLine)
        'sql.Append("GO")
        'sql.Append(vbNewLine)
        sql.Append("CREATE TABLE [DBO].[" & tableName & "] ")
        sql.Append("  ( ")
        sql.Append("    [id] [INT] IDENTITY(1,1) NOT NULL ")
        For Each dr As DataRow In tableFields.Rows
            sql.Append(", [" & dr("dbfieldname") & "] [nvarchar](50) NOT NULL ")
        Next
        sql.Append(", CONSTRAINT [PK_" & tableName & "] PRIMARY KEY CLUSTERED ([id] ASC ) ")
        sql.Append("WITH ")
        sql.Append("  ( ")
        sql.Append("    PAD_INDEX              = OFF, ")
        sql.Append("    STATISTICS_NORECOMPUTE = OFF, ")
        sql.Append("    IGNORE_DUP_KEY         = OFF, ")
        sql.Append("    ALLOW_ROW_LOCKS        = ON, ")
        sql.Append("    ALLOW_PAGE_LOCKS       = ON ")
        sql.Append("  ) ")
        sql.Append("  ON [PRIMARY] ")
        sql.Append("  ) ")
        sql.Append("  ON [PRIMARY]")
        sql.Append(vbNewLine)
        'sql.Append("GO")
        'sql.Append(vbNewLine)
        sql.Append("  SET ANSI_PADDING OFF ")
        'sql.Append(vbNewLine)
        'sql.Append("GO")
        'sql.Append(vbNewLine)
        'sql.Append(" ALTER TABLE [DBO].[" & tableName & "] ")
        'sql.Append("WITH CHECK ADD CONSTRAINT [FK_TableFields_ModuleTables] FOREIGN KEY([TableId]) REFERENCES [DBO].[ModuleTables] ([id]) GO ALTER TABLE [DBO].[TableFields] CHECK CONSTRAINT [FK_TableFields_ModuleTables] GO ALTER TABLE [DBO].[TableFields] ADD CONSTRAINT [DF_TableFields_FieldType] DEFAULT ('TEXT') FOR [FieldType] GO ALTER TABLE [DBO].[TableFields] ADD CONSTRAINT [DF_TableFields_Enabled] DEFAULT ('Y') FOR [Enabled] GO ALTER TABLE [DBO].[Tablefields] ADD CONSTRAINT [Df_Tablefields_Visible] DEFAULT ('Y') FOR [Visible] GO")
        Return sql.ToString
    End Function


    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

    End Sub

    Protected Sub ddlTablesList_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles ddlTablesList.SelectedIndexChanged
        editableGrid4.tableName = "[UserModules].[dbo]." & ddlTablesList.SelectedItem.Text
        'editableGrid4.sqlQuery = "SELECT * FROM " & editableGrid4.tableName
        editableGrid4.RefreshFilters()
        editableGrid4.RefreshData()
    End Sub

    Protected Sub NewTableCreating(ByRef com As SqlCommand)
        Try
            com.Parameters("@DbTableName").Value = com.Parameters("@TableName").Value.ToString.ToUpper.Replace(" ", "_")
        Catch ex As Exception

        End Try
    End Sub

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        AddHandler editableGrid2.NewRecordSaving, AddressOf NewTableCreating
        If Not IsPostBack Then
            'editableGrid2.Filters.Add("moduleid", "1")
        End If
    End Sub
End Class