none
How to create database in code Visual Basic 2010 Express

    Question

  • I would like to create an application which enables the user to create, edit and delete databases.

    I only find how to create it with Visual Basic manually, and not how to do it with code. Or I see code that is apparently unknown to VB 2010 Express.

    I'm a very beginner and apologise if that has been answered somewhere already.


    Wednesday, April 17, 2013 12:54 PM

Answers

  • Not knowing what type of database I will provide a thought for MS-Access.  In your application include a database without anything within, place the database in a folder below the application folder.

    Use SQL to manage tables, for instance use CREATE TABLE statement to create a table, DROP TABLE statement to remove a table etc.

    You would need to create a user interface that allows users to submit table name, column names and types for creating dynamic tables, for removing and altering them you would need to query an existing database for table and column information.

    How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET

    To give you a glimpse at creating the database (which I would not suggest but instead include a blank one as per above), create and populate data here is an example.

    • Under project properties, references, add a reference for Microsoft ADO Ext. under the COM tab.
    • Add a DataGridview1 and DataGridView2 to a form named form1
    • On the same form you need to buttons, cmdCreateUsingADOX and cmdFilter
    • Add the code below which was originally done in VS2008, updated to VS2010 via VB.NET, Option Strict On
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "AdoxDatabase.mdb")
        Private htTypes As Hashtable = GetOleDbTypeNamesValues(False)
        Private ConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", FileName)
        WithEvents bsDataSource As New BindingSource
        Sub CreateNewAccessDatabase(ByVal FileName As String)
            Dim dbCatalog As New ADOX.Catalog()
            dbCatalog.Create(ConnectionString)
        End Sub
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            If IO.File.Exists(FileName) Then
                IO.File.Delete(FileName)
            End If
            cmdFilter.Text = "Set Filter"
            cmdFilter.Enabled = False
        End Sub
        Private Sub cmdCreateUsingADOX_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateUsingADOX.Click
            If Not IO.File.Exists(FileName) Then
                CreateNewAccessDatabase(FileName)
            End If
            Using cn As New OleDb.OleDbConnection(ConnectionString)
                Dim cmd As New OleDb.OleDbCommand( _
                <text>
                    CREATE TABLE persons ([Identifier] int identity,
                                          UserName NVarchar(50), 
                                          [Password] NVarchar(50),
                                          CONSTRAINT [pk_AutoId] PRIMARY KEY ([Identifier])) 
                </text>.Value, cn)
                cn.Open()
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As OleDb.OleDbException
                    MessageBox.Show(ex.Message, "OleDbException")
                    Exit Sub
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "GeneralException")
                    Exit Sub
                End Try
                Application.DoEvents()
                cmd.CommandText =
                <Text>
                    INSERT INTO persons (UserName,[Password]) VALUES (@UserName,@UserPassword)
                </Text>.Value
                cmd.Parameters.Add(New OleDb.OleDbParameter With {.ParameterName = "@UserName", .DbType = DbType.String})
                cmd.Parameters.Add(New OleDb.OleDbParameter With {.ParameterName = "@UserPassword", .DbType = DbType.String})
                cmd.Parameters(0).Value = "Smith"
                cmd.Parameters(1).Value = "abv123w"
                cmd.ExecuteNonQuery()
                cmd.Parameters(0).Value = "Student1"
                cmd.Parameters(1).Value = "College"
                cmd.ExecuteNonQuery()
                cmd.Parameters(0).Value = "gallagher"
                cmd.Parameters(1).Value = "mypass"
                cmd.ExecuteNonQuery()
                cmd.Parameters(0).Value = "Simpson"
                cmd.Parameters(1).Value = "Homer1"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT * FROM Persons ORDER BY UserName"
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader())
                bsDataSource.DataSource = dt
                DataGridView1.DataSource = bsDataSource
                Console.WriteLine("There are [{0}] rows in person table", cn.RecordCount("Persons").ToString)
                cn.Close()
            End Using
            GetOledbSchema(ConnectionString, "Persons", Me.htTypes, DataGridView2)
            cmdCreateUsingADOX.Enabled = False
            cmdFilter.Enabled = True
        End Sub
        Function GetOleDbTypeNamesValues(ByVal DisplayInformation As Boolean) As Hashtable
            Static Names As String()
            Dim Values As Integer()
            Dim FieldTypes As New Hashtable
            If IsNothing(Names) Then
                Names = CType([Enum].GetNames(GetType(OleDb.OleDbType)), String())
                Values = CType([Enum].GetValues(GetType(OleDb.OleDbType)), Integer())
                For Row As Integer = 0 To Names.Count - 1
                    FieldTypes.Add(Values(Row), Names(Row))
                Next
                If DisplayInformation Then
                    Dim query = From x In Names.Select(Function(Name, index) String.Format("{0} {1}", index + 1, Name))
                    For Each item In query
                        Console.WriteLine(item)
                    Next
                End If
            End If
            Return FieldTypes
        End Function
        Public Function GetOledbSchema(ByVal ConnectionString As String, ByVal TableName As String, ByVal FieldTypes As Hashtable, ByVal GridView As DataGridView) As Boolean
            Try
                Using cn As New OleDb.OleDbConnection(ConnectionString)
                    Dim Table As New DataTable With {.TableName = String.Format("tbl{0}", TableName)}
                    cn.Open()
                    Table = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName, Nothing})
                    Dim dt As DataTable =
                        Table.DefaultView.ToTable(
                            String.Format("{0}_schema", TableName.SpacesToUnderScore), True,
                            New String() _
                                {
                                    "Column_Name",
                                    "Data_Type",
                                    "is_nullable",
                                    "Character_Maximum_Length",
                                    "Description"
                                }
                            )
                    Dim query =
                       (
                          From fd In dt.AsEnumerable
                          Select New With
                          {
                                .Column = fd.Field(Of String)("Column_Name"),
                                .DataType = FieldTypes(fd.Field(Of Integer)("Data_Type")),
                                .Size = fd.Field(Of Object)("Character_Maximum_Length"),
                                .Description = fd.Field(Of Object)("Description"),
                                .Nullable = fd.Field(Of Boolean)("is_nullable").ToString
                          }
                      ).ToList
                    cn.Close()
                    GridView.DataSource = query
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function
        Private Sub cmdFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFilter.Click
            If bsDataSource.DataSource IsNot Nothing Then
                If bsDataSource.Filter = "" Then
                    bsDataSource.Filter = "UserName like 'G%'"
                    cmdFilter.Text = "Remove Filter"
                Else
                    bsDataSource.Filter = ""
                    cmdFilter.Text = "Set Filter"
                End If
            End If
        End Sub

    Add a code module to your project and insert the code below

        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function SpacesToUnderScore(ByVal sender As String) As String
            Return sender.Replace(" ", "_")
        End Function
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function RecordCount(ByVal cn As OleDb.OleDbConnection, _
                                    ByVal TableName As String) As Long
            If cn Is Nothing Then
                Throw New Exception("Expected a properly prepared connection")
            End If
            If Not cn.State = ConnectionState.Open Then
                cn.Open()
            End If
            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = cn
            cmd.CommandText = <SQL>SELECT  COUNT(*) 
                                   FROM <%= TableName %></SQL>.Value
            Return CLng(cmd.ExecuteScalar())
        End Function
    Build, run, try out the operations. I should indicate the above is only for MS-Access, for other databases you need the same logic but done as per that database rules, permissions and available methods.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by .paul. _ Thursday, April 18, 2013 7:39 AM
    • Marked as answer by Shanks ZenModerator Tuesday, April 23, 2013 10:24 AM
    Wednesday, April 17, 2013 7:19 PM

All replies

  • Not knowing what type of database I will provide a thought for MS-Access.  In your application include a database without anything within, place the database in a folder below the application folder.

    Use SQL to manage tables, for instance use CREATE TABLE statement to create a table, DROP TABLE statement to remove a table etc.

    You would need to create a user interface that allows users to submit table name, column names and types for creating dynamic tables, for removing and altering them you would need to query an existing database for table and column information.

    How To Retrieve Schema Information by Using GetOleDbSchemaTable and Visual Basic .NET

    To give you a glimpse at creating the database (which I would not suggest but instead include a blank one as per above), create and populate data here is an example.

    • Under project properties, references, add a reference for Microsoft ADO Ext. under the COM tab.
    • Add a DataGridview1 and DataGridView2 to a form named form1
    • On the same form you need to buttons, cmdCreateUsingADOX and cmdFilter
    • Add the code below which was originally done in VS2008, updated to VS2010 via VB.NET, Option Strict On
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "AdoxDatabase.mdb")
        Private htTypes As Hashtable = GetOleDbTypeNamesValues(False)
        Private ConnectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", FileName)
        WithEvents bsDataSource As New BindingSource
        Sub CreateNewAccessDatabase(ByVal FileName As String)
            Dim dbCatalog As New ADOX.Catalog()
            dbCatalog.Create(ConnectionString)
        End Sub
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            If IO.File.Exists(FileName) Then
                IO.File.Delete(FileName)
            End If
            cmdFilter.Text = "Set Filter"
            cmdFilter.Enabled = False
        End Sub
        Private Sub cmdCreateUsingADOX_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateUsingADOX.Click
            If Not IO.File.Exists(FileName) Then
                CreateNewAccessDatabase(FileName)
            End If
            Using cn As New OleDb.OleDbConnection(ConnectionString)
                Dim cmd As New OleDb.OleDbCommand( _
                <text>
                    CREATE TABLE persons ([Identifier] int identity,
                                          UserName NVarchar(50), 
                                          [Password] NVarchar(50),
                                          CONSTRAINT [pk_AutoId] PRIMARY KEY ([Identifier])) 
                </text>.Value, cn)
                cn.Open()
                Try
                    cmd.ExecuteNonQuery()
                Catch ex As OleDb.OleDbException
                    MessageBox.Show(ex.Message, "OleDbException")
                    Exit Sub
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "GeneralException")
                    Exit Sub
                End Try
                Application.DoEvents()
                cmd.CommandText =
                <Text>
                    INSERT INTO persons (UserName,[Password]) VALUES (@UserName,@UserPassword)
                </Text>.Value
                cmd.Parameters.Add(New OleDb.OleDbParameter With {.ParameterName = "@UserName", .DbType = DbType.String})
                cmd.Parameters.Add(New OleDb.OleDbParameter With {.ParameterName = "@UserPassword", .DbType = DbType.String})
                cmd.Parameters(0).Value = "Smith"
                cmd.Parameters(1).Value = "abv123w"
                cmd.ExecuteNonQuery()
                cmd.Parameters(0).Value = "Student1"
                cmd.Parameters(1).Value = "College"
                cmd.ExecuteNonQuery()
                cmd.Parameters(0).Value = "gallagher"
                cmd.Parameters(1).Value = "mypass"
                cmd.ExecuteNonQuery()
                cmd.Parameters(0).Value = "Simpson"
                cmd.Parameters(1).Value = "Homer1"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "SELECT * FROM Persons ORDER BY UserName"
                Dim dt As New DataTable
                dt.Load(cmd.ExecuteReader())
                bsDataSource.DataSource = dt
                DataGridView1.DataSource = bsDataSource
                Console.WriteLine("There are [{0}] rows in person table", cn.RecordCount("Persons").ToString)
                cn.Close()
            End Using
            GetOledbSchema(ConnectionString, "Persons", Me.htTypes, DataGridView2)
            cmdCreateUsingADOX.Enabled = False
            cmdFilter.Enabled = True
        End Sub
        Function GetOleDbTypeNamesValues(ByVal DisplayInformation As Boolean) As Hashtable
            Static Names As String()
            Dim Values As Integer()
            Dim FieldTypes As New Hashtable
            If IsNothing(Names) Then
                Names = CType([Enum].GetNames(GetType(OleDb.OleDbType)), String())
                Values = CType([Enum].GetValues(GetType(OleDb.OleDbType)), Integer())
                For Row As Integer = 0 To Names.Count - 1
                    FieldTypes.Add(Values(Row), Names(Row))
                Next
                If DisplayInformation Then
                    Dim query = From x In Names.Select(Function(Name, index) String.Format("{0} {1}", index + 1, Name))
                    For Each item In query
                        Console.WriteLine(item)
                    Next
                End If
            End If
            Return FieldTypes
        End Function
        Public Function GetOledbSchema(ByVal ConnectionString As String, ByVal TableName As String, ByVal FieldTypes As Hashtable, ByVal GridView As DataGridView) As Boolean
            Try
                Using cn As New OleDb.OleDbConnection(ConnectionString)
                    Dim Table As New DataTable With {.TableName = String.Format("tbl{0}", TableName)}
                    cn.Open()
                    Table = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, TableName, Nothing})
                    Dim dt As DataTable =
                        Table.DefaultView.ToTable(
                            String.Format("{0}_schema", TableName.SpacesToUnderScore), True,
                            New String() _
                                {
                                    "Column_Name",
                                    "Data_Type",
                                    "is_nullable",
                                    "Character_Maximum_Length",
                                    "Description"
                                }
                            )
                    Dim query =
                       (
                          From fd In dt.AsEnumerable
                          Select New With
                          {
                                .Column = fd.Field(Of String)("Column_Name"),
                                .DataType = FieldTypes(fd.Field(Of Integer)("Data_Type")),
                                .Size = fd.Field(Of Object)("Character_Maximum_Length"),
                                .Description = fd.Field(Of Object)("Description"),
                                .Nullable = fd.Field(Of Boolean)("is_nullable").ToString
                          }
                      ).ToList
                    cn.Close()
                    GridView.DataSource = query
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function
        Private Sub cmdFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFilter.Click
            If bsDataSource.DataSource IsNot Nothing Then
                If bsDataSource.Filter = "" Then
                    bsDataSource.Filter = "UserName like 'G%'"
                    cmdFilter.Text = "Remove Filter"
                Else
                    bsDataSource.Filter = ""
                    cmdFilter.Text = "Set Filter"
                End If
            End If
        End Sub

    Add a code module to your project and insert the code below

        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function SpacesToUnderScore(ByVal sender As String) As String
            Return sender.Replace(" ", "_")
        End Function
        <System.Diagnostics.DebuggerStepThrough()> _
        <Runtime.CompilerServices.Extension()> _
        Public Function RecordCount(ByVal cn As OleDb.OleDbConnection, _
                                    ByVal TableName As String) As Long
            If cn Is Nothing Then
                Throw New Exception("Expected a properly prepared connection")
            End If
            If Not cn.State = ConnectionState.Open Then
                cn.Open()
            End If
            Dim cmd As New OleDb.OleDbCommand
            cmd.Connection = cn
            cmd.CommandText = <SQL>SELECT  COUNT(*) 
                                   FROM <%= TableName %></SQL>.Value
            Return CLng(cmd.ExecuteScalar())
        End Function
    Build, run, try out the operations. I should indicate the above is only for MS-Access, for other databases you need the same logic but done as per that database rules, permissions and available methods.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Proposed as answer by .paul. _ Thursday, April 18, 2013 7:39 AM
    • Marked as answer by Shanks ZenModerator Tuesday, April 23, 2013 10:24 AM
    Wednesday, April 17, 2013 7:19 PM
  • Hello,

    I have moved this thread to Visual Basic forum for better response.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, April 18, 2013 7:35 AM