none
Database Storage For VB.Net Project RRS feed

  • Question

  • Hi,

    I am very new to vb.net. I am trying to make a shop management system. I am using MS SQL Server for the required database. Where should i save the database. They are currently in the default save folder for SQL Server. I want my project to run in any computer with vb.net and SQL Server installed, so I am confused whether the database will be included in the .exe file if I make it now.

    Thanks.

    Thursday, June 15, 2017 5:15 PM

Answers

  • Hi niloysd7,

    >>Where should i save the database?

    I suggest you to save your database in any place other than the C, Because when you reinstall the system or others, these databases may be damaged.

    >>so I am confused whether the database will be included in the .exe file if I make it now.

    According to your description, your client have installed the SQL, if you use attaching a database file, you just include .mdf database file when you package your project.

    Attach a database file

    Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;
    Trusted_Connection=Yes;

    If you want to connect your local SQL, you need to copy database a file to another computer SQL , and use App.config to connect the SQL. The link is the getting connection string from App.Config file for your reference.

    https://www.aspsnippets.com/Articles/Read-Get-Connection-String-from-AppConfig-file-using-C-and-VBNet.aspx

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by niloysd7 Saturday, June 17, 2017 7:59 AM
    Friday, June 16, 2017 3:12 AM
    Moderator
  • Okay, there is a good deal going on in the project to start off with.

    Took about an hour or so to write working off a C# code sample I did which made life easier. The MSDN code sample goes a tad farther and was to fully demo the dialog component.

    Let's look at the form code. I stuffed all code into a button. The first part of the 'if' statement for you would be a one time deal. You could place it in the form load which would be a better place so there is no button ever involved but it's easier to try the code out this way.

     

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New Operations
            ' if My.Settings.DatabaseConnectionString is not set the database has not been created
            ' so let's do it
            If String.IsNullOrWhiteSpace(My.Settings.DatabaseConnectionString) Then
                Dim dataSource = ""
                Dim ServerName As String = ""
    
                If ops.GetConnection(dataSource) Then
                    If ops.CreateDatabase() Then
                        If ops.CreateTablesAndPopulate Then
                            My.Settings.DatabaseConnectionString = ops.NewConnectionString
                            MessageBox.Show("Done")
                        Else
                            If ops.LastException IsNot Nothing Then
                                ' could be failure on create or populate
                                MessageBox.Show($"Failed populating table : {ops.LastException}")
                            End If
                        End If
                    Else
                        If ops.LastException IsNot Nothing Then
                            MessageBox.Show($"Failed to create database: {ops.LastException}")
                        End If
                    End If
                Else
                    ' failed to get connection
                End If
    
            Else
                ' database was created and populated
                DataGridView1.DataSource = ops.GetData(My.Settings.DatabaseConnectionString)
            End If
    
        End Sub
    End Class

    Caveats

    • I only tried with with SQL-Server with a named instance e.g. KARENS-PC and not with .\SQLEXPRESS as the database but should be fine, if not I don't have my SQL-Server setup for this.
    • When they first execute, the dialog appears as shown in my first reply. They need to enter the instance name of their SQL-Server, optionally press test connection button. If the test works they press OK.
    • The code executes and if all goes as expected they end up with a database with (in this demo two tables populated).
    • The else part of the 'if' would be be in the inner if success to then populate the data.
    • I included a property LastException which returns the last exception which if there is one they can report that back to you.
    • Include DataConnection.xml in your project (found in bin\Debug) which tells the dialog to default to SQL-Server.
    • The following references provide the functionality for the dialogs (source code is in my MSDN code sample)

    Full project and source.

    All the runtimes are in the folder Binaries

    Ready for it, here is the source code responsible for everything. Depending on your knowledge of VB.NET this may be a bit to take in so take your time and study it or simply use it.

    Imports Microsoft.Data.ConnectionUI
    Imports System.Data.Common
    Imports System.Data.SqlClient
    
    Public Class Operations
        Private mException As Exception
        Public ReadOnly Property LastException As Exception
            Get
                Return mException
            End Get
        End Property
        Private mBuilder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder()
        Public ReadOnly Property ConnectionString As String
            Get
                Return mBuilder.ConnectionString
            End Get
        End Property
        Private mNewConnectionString As String
        Public ReadOnly Property NewConnectionString As String
            Get
                Return mNewConnectionString
            End Get
        End Property
        ''' <summary>
        ''' SQL-Server name returned in GetConnection method
        ''' </summary>
        Public Property ServerName() As String
        ''' <summary>
        ''' SQL-Server database returned in GetConnection method
        ''' </summary>
        Public Property InitialCatalog() As String
        ''' <summary>
        ''' Table names in ServerName.InitialCatalog 
        ''' </summary>
        Public Property TableNames() As List(Of String)
        Public Sub New()
            InitialCatalog = "SampleDatabase"
        End Sub
        ''' <summary>
        ''' Create connection string using Microsoft's ConnectionUI class
        ''' </summary>
        ''' <param name="SaveConfiguration"></param>
        ''' <returns></returns>
        Public Function GetConnection(ByRef DataSource As String, Optional ByVal SaveConfiguration As Boolean = False) As Boolean
            Dim success = False
    
            Dim dcd As New DataConnectionDialog()
    
            Dim dcs As New DataConnectionConfiguration(Nothing)
    
            dcs.LoadConfiguration(dcd)
    
            If DataConnectionDialog.Show(dcd) = DialogResult.OK Then
                Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(dcd.SelectedDataProvider.Name)
                Using connection = factory.CreateConnection()
                    connection.ConnectionString = dcd.ConnectionString
    
                    DataSource = connection.DataSource
                    connection.Open()
                    Dim cmd = connection.CreateCommand()
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES"
    
                    Dim dt = New DataTable()
                    dt.Load(cmd.ExecuteReader())
    
                    TableNames = dt.AsEnumerable().Select(Function(row) row.Field(Of String)("table_name")).OrderBy(Function(field) field).ToList()
                End Using
    
                mBuilder = New SqlConnectionStringBuilder() With {.ConnectionString = dcd.ConnectionString}
    
                ServerName = mBuilder.DataSource
    
                If SaveConfiguration Then
                    dcs.SaveConfiguration(dcd)
                End If
    
                If TableNames.Count > 0 Then
                    success = True
                End If
            End If
    
            Return success
    
        End Function
        Public Function GetData(ByVal pConnectionString As String) As DataTable
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = pConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = PersonGenderSelect()
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
    
            Return dt
    
        End Function
        ''' <summary>
        ''' SELECT to get back our data
        ''' </summary>
        ''' <returns></returns>
        Private Function PersonGenderSelect() As String
            Return <SQL>
    SELECT 
    	P.id, 
    	P.FirstName, 
    	P.LastName, 
    	G.Gender
    FROM            
    	Persons1 AS P INNER JOIN
        GenderTypes AS G ON P.GenderIdentifier = G.GenderIdentifier
    ORDER BY P.LastName
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' Create our database
        ''' </summary>
        ''' <returns></returns>
        Public Function CreateDatabase() As Boolean
            Dim success As Boolean = True
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "CREATE DATABASE SampleDatabase"
                    cn.Open()
                    Try
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        mException = ex
                        success = False
                    End Try
                End Using
            End Using
            Return success
        End Function
        ''' <summary>
        ''' Create tables and populate
        ''' </summary>
        ''' <returns></returns>
        Public Function CreateTablesAndPopulate() As Boolean
            Dim success As Boolean = True
    
            mBuilder.InitialCatalog = InitialCatalog
            mBuilder.DataSource = ServerName
    
            Using cn As New SqlConnection With {.ConnectionString = mBuilder.ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = GenderTableScript()
                    cn.Open()
                    Try
                        ' create child table 
                        cmd.ExecuteNonQuery()
                        cmd.CommandText = PeopleTableScript()
                        ' create main table
                        cmd.ExecuteNonQuery()
    
                        'insert gender records
                        cmd.CommandText = InsertGenderScript()
                        cmd.ExecuteNonQuery()
    
                        ' insert people records
                        cmd.CommandText = InsertPeopleScript()
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        mException = ex
                        success = False
                    End Try
                End Using
            End Using
    
            If success Then
                mNewConnectionString = mBuilder.ConnectionString
            End If
            Return success
        End Function
        ''' <summary>
        ''' SQL Script to generate gender table
        ''' </summary>
        ''' <returns></returns>
        Private Function GenderTableScript() As String
            Return <SQL>
    CREATE TABLE [dbo].[GenderTypes](
    	[GenderIdentifier] [INT] IDENTITY(1,1) NOT NULL,
    	[Gender] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_GenderTypes] PRIMARY KEY CLUSTERED 
    (
    	[GenderIdentifier] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' SQl script to populate gender table
        ''' </summary>
        ''' <returns></returns>
        Private Function InsertGenderScript() As String
            Return <SQL>
    INSERT INTO [GenderTypes]
    VALUES
    ( N'Female' ), 
    ( N'Male' ), 
    ( N'Non binary' )
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' Sql script to create person table
        ''' </summary>
        ''' <returns></returns>
        Private Function PeopleTableScript() As String
            Return <SQL>
    CREATE TABLE [dbo].[Persons1](
    	[id] [INT] IDENTITY(1,1) NOT NULL,
    	[FirstName] [NVARCHAR](MAX) NULL,
    	[LastName] [NVARCHAR](MAX) NULL,
    	[GenderIdentifier] [INT] NULL,
    	[IsDeleted] [BIT] NULL,
     CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' Sql script to populate person table
        ''' </summary>
        ''' <returns></returns>
        Private Function InsertPeopleScript() As String
            Return <SQL>
    INSERT INTO [Persons1]
    VALUES
    ( N'Mary', N'Buckley', 1, 1 ), 
    ( N'Karen', N'Payne', 1, 1 ), 
    ( N'Lee', N'Warren', 1, 1 ), 
    ( N'Regina', N'Forbes', 1, 0 ), 
    ( N'Daniel', N'Kim', 2, 0 ), 
    ( N'Dennis', N'Nunez', 2, 0 ), 
    ( N'Myra', N'Zuniga', 1, 0 ), 
    ( N'Teddy', N'Ingram', 2, 0 ), 
    ( N'Annie', N'Larson', 1, 0 ), 
    ( N'Karen', N'Anderson', 1, 0 ), 
    ( N'Jenifer', N'Livingston', 1, 0 ), 
    ( N'Stefanie', N'Perez', 1, 0 ), 
    ( N'Chastity', N'Garcia', 1, 0 ), 
    ( N'Evelyn', N'Stokes', 1, 0 ), 
    ( N'Jeannie', N'Daniel', 1, 0 ), 
    ( N'Rickey', N'Santos', 2, 0 ), 
    ( N'Bobbie', N'Hurst', 2, 0 ), 
    ( N'Lesley', N'Lawson', 1, 0 ), 
    ( N'Shawna', N'Browning', 1, 0 ), 
    ( N'Theresa', N'Ross', 1, 0 ), 
    ( N'Tasha', N'Hughes', 3, 0 ), 
    ( N'Karla', N'Hale', 1, 0 ), 
    ( N'Otis', N'Holt', 2, 0 ), 
    ( N'Alisa', N'Browning', 3, 0 ), 
    ( N'Peggy', N'Donaldson', 1, 0 ), 
    ( N'Lisa', N'Bentley', 1, 0 ), 
    ( N'Vicky', N'Wiley', 1, 0 ), 
    ( N'Nicolas', N'Spence', 2, 0 ), 
    ( N'Miranda', N'Barnes', 1, 0 ), 
    ( N'Karen', N'Barry', 1, 1 ), 
    ( N'Rosemary', N'Levine', 3, 0 ), 
    ( N'Ernest', N'Gamble', 2, 0 ), 
    ( N'Lindsay', N'Henderson', 1, 0 ), 
    ( N'Lorenzo', N'Adams', 2, 0 ), 
    ( N'Tammie', N'Graves', 1, 0 ), 
    ( N'Kareem', N'Benton', 3, 0 ), 
    ( N'Cesar', N'Vance', 3, 0 ), 
    ( N'Charlene', N'Rocha', 1, 0 ), 
    ( N'Sonja', N'Mac Donald', 1, 0 ), 
    ( N'Gwendolyn', N'Russell', 1, 0 ), 
    ( N'Stephan', N'Hill', 2, 0 ), 
    ( N'Maggie', N'Day', 1, 0 ), 
    ( N'Earnest', N'Walters', 1, 0 ), 
    ( N'Zachary', N'Pratt', 1, 0 ), 
    ( N'Erin', N'Hinton', 3, 0 ), 
    ( N'Rodolfo', N'Collier', 2, 0 ), 
    ( N'Carla', N'Jackson', 1, 0 ), 
    ( N'Norma', N'Robles', 1, 0 ), 
    ( N'Jean', N'Haynes', 1, 0 ), 
    ( N'Tara', N'Pope', 3, 0 ), 
    ( N'Ann', N'Patterson', 1, 0 ), 
    ( N'Nancy', N'Lebow', 3, 0 ), 
    ( N'Joe', N'Hansen', 2, 0 ), 
    ( N'Joe', N'Hansen', 2, 0 ), 
    ( N'Jill', N'Gallagher', 1, 0 ), 
    ( N'Sunshine', N'Miller', 1, 0 ), 
    ( N'Annabelle', N'Huff', 1, 0 ), 
    ( N'Pam', N'Gallagher', 3, 0 )
                   </SQL>.Value
        End Function
    End Class

    The scripts were easy for me as I used a third party extension to SQL-Server Management Studio, took about 1 minute but you may need to do them by hand.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Friday, June 16, 2017 8:08 PM
    Moderator

All replies

  • Hi niloysd7,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about VB development. Since our forum is to discuss the VS IDE, I will help you move this thread to the appropriate forum: Visual Studio Languages  >  Visual Basic to seek for a more professional support, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Friday, June 16, 2017 2:31 AM
  • Hi niloysd7,

    >>Where should i save the database?

    I suggest you to save your database in any place other than the C, Because when you reinstall the system or others, these databases may be damaged.

    >>so I am confused whether the database will be included in the .exe file if I make it now.

    According to your description, your client have installed the SQL, if you use attaching a database file, you just include .mdf database file when you package your project.

    Attach a database file

    Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;
    Trusted_Connection=Yes;

    If you want to connect your local SQL, you need to copy database a file to another computer SQL , and use App.config to connect the SQL. The link is the getting connection string from App.Config file for your reference.

    https://www.aspsnippets.com/Articles/Read-Get-Connection-String-from-AppConfig-file-using-C-and-VBNet.aspx

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by niloysd7 Saturday, June 17, 2017 7:59 AM
    Friday, June 16, 2017 3:12 AM
    Moderator
  • From the Microsoft point of view and given and the information you supplied only one answer possible. 

    https://azure.microsoft.com/en-us/services/sql-database/?v=17.23h


    Success
    Cor


    Friday, June 16, 2017 7:52 AM
  • Then where you should be posting is to MS SQL Server forum.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Friday, June 16, 2017 10:52 AM
  • Hello,

    If you are sure SQL-Server is installed on the client computer(s) then you could create your database and tables, populate them rather than used a local copy of a database. If interested later today I can put an example together which a) prompts the user to login to SQL-Server, generate a connection string for you which can be stored under My.Settings.

    Here is a run-time screenshot for the prompt (same as used in Visual Studio for TableAdapters).

    Here I have connected to a specific database (this is optional which you don't need but shows it works)


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 16, 2017 12:49 PM
    Moderator
  • That would be very helpful Karen.

    Friday, June 16, 2017 2:47 PM
  • Okay, there is a good deal going on in the project to start off with.

    Took about an hour or so to write working off a C# code sample I did which made life easier. The MSDN code sample goes a tad farther and was to fully demo the dialog component.

    Let's look at the form code. I stuffed all code into a button. The first part of the 'if' statement for you would be a one time deal. You could place it in the form load which would be a better place so there is no button ever involved but it's easier to try the code out this way.

     

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New Operations
            ' if My.Settings.DatabaseConnectionString is not set the database has not been created
            ' so let's do it
            If String.IsNullOrWhiteSpace(My.Settings.DatabaseConnectionString) Then
                Dim dataSource = ""
                Dim ServerName As String = ""
    
                If ops.GetConnection(dataSource) Then
                    If ops.CreateDatabase() Then
                        If ops.CreateTablesAndPopulate Then
                            My.Settings.DatabaseConnectionString = ops.NewConnectionString
                            MessageBox.Show("Done")
                        Else
                            If ops.LastException IsNot Nothing Then
                                ' could be failure on create or populate
                                MessageBox.Show($"Failed populating table : {ops.LastException}")
                            End If
                        End If
                    Else
                        If ops.LastException IsNot Nothing Then
                            MessageBox.Show($"Failed to create database: {ops.LastException}")
                        End If
                    End If
                Else
                    ' failed to get connection
                End If
    
            Else
                ' database was created and populated
                DataGridView1.DataSource = ops.GetData(My.Settings.DatabaseConnectionString)
            End If
    
        End Sub
    End Class

    Caveats

    • I only tried with with SQL-Server with a named instance e.g. KARENS-PC and not with .\SQLEXPRESS as the database but should be fine, if not I don't have my SQL-Server setup for this.
    • When they first execute, the dialog appears as shown in my first reply. They need to enter the instance name of their SQL-Server, optionally press test connection button. If the test works they press OK.
    • The code executes and if all goes as expected they end up with a database with (in this demo two tables populated).
    • The else part of the 'if' would be be in the inner if success to then populate the data.
    • I included a property LastException which returns the last exception which if there is one they can report that back to you.
    • Include DataConnection.xml in your project (found in bin\Debug) which tells the dialog to default to SQL-Server.
    • The following references provide the functionality for the dialogs (source code is in my MSDN code sample)

    Full project and source.

    All the runtimes are in the folder Binaries

    Ready for it, here is the source code responsible for everything. Depending on your knowledge of VB.NET this may be a bit to take in so take your time and study it or simply use it.

    Imports Microsoft.Data.ConnectionUI
    Imports System.Data.Common
    Imports System.Data.SqlClient
    
    Public Class Operations
        Private mException As Exception
        Public ReadOnly Property LastException As Exception
            Get
                Return mException
            End Get
        End Property
        Private mBuilder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder()
        Public ReadOnly Property ConnectionString As String
            Get
                Return mBuilder.ConnectionString
            End Get
        End Property
        Private mNewConnectionString As String
        Public ReadOnly Property NewConnectionString As String
            Get
                Return mNewConnectionString
            End Get
        End Property
        ''' <summary>
        ''' SQL-Server name returned in GetConnection method
        ''' </summary>
        Public Property ServerName() As String
        ''' <summary>
        ''' SQL-Server database returned in GetConnection method
        ''' </summary>
        Public Property InitialCatalog() As String
        ''' <summary>
        ''' Table names in ServerName.InitialCatalog 
        ''' </summary>
        Public Property TableNames() As List(Of String)
        Public Sub New()
            InitialCatalog = "SampleDatabase"
        End Sub
        ''' <summary>
        ''' Create connection string using Microsoft's ConnectionUI class
        ''' </summary>
        ''' <param name="SaveConfiguration"></param>
        ''' <returns></returns>
        Public Function GetConnection(ByRef DataSource As String, Optional ByVal SaveConfiguration As Boolean = False) As Boolean
            Dim success = False
    
            Dim dcd As New DataConnectionDialog()
    
            Dim dcs As New DataConnectionConfiguration(Nothing)
    
            dcs.LoadConfiguration(dcd)
    
            If DataConnectionDialog.Show(dcd) = DialogResult.OK Then
                Dim factory As DbProviderFactory = DbProviderFactories.GetFactory(dcd.SelectedDataProvider.Name)
                Using connection = factory.CreateConnection()
                    connection.ConnectionString = dcd.ConnectionString
    
                    DataSource = connection.DataSource
                    connection.Open()
                    Dim cmd = connection.CreateCommand()
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES"
    
                    Dim dt = New DataTable()
                    dt.Load(cmd.ExecuteReader())
    
                    TableNames = dt.AsEnumerable().Select(Function(row) row.Field(Of String)("table_name")).OrderBy(Function(field) field).ToList()
                End Using
    
                mBuilder = New SqlConnectionStringBuilder() With {.ConnectionString = dcd.ConnectionString}
    
                ServerName = mBuilder.DataSource
    
                If SaveConfiguration Then
                    dcs.SaveConfiguration(dcd)
                End If
    
                If TableNames.Count > 0 Then
                    success = True
                End If
            End If
    
            Return success
    
        End Function
        Public Function GetData(ByVal pConnectionString As String) As DataTable
            Dim dt As New DataTable
            Using cn As New SqlConnection With {.ConnectionString = pConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = PersonGenderSelect()
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                End Using
            End Using
    
            Return dt
    
        End Function
        ''' <summary>
        ''' SELECT to get back our data
        ''' </summary>
        ''' <returns></returns>
        Private Function PersonGenderSelect() As String
            Return <SQL>
    SELECT 
    	P.id, 
    	P.FirstName, 
    	P.LastName, 
    	G.Gender
    FROM            
    	Persons1 AS P INNER JOIN
        GenderTypes AS G ON P.GenderIdentifier = G.GenderIdentifier
    ORDER BY P.LastName
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' Create our database
        ''' </summary>
        ''' <returns></returns>
        Public Function CreateDatabase() As Boolean
            Dim success As Boolean = True
            Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = "CREATE DATABASE SampleDatabase"
                    cn.Open()
                    Try
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        mException = ex
                        success = False
                    End Try
                End Using
            End Using
            Return success
        End Function
        ''' <summary>
        ''' Create tables and populate
        ''' </summary>
        ''' <returns></returns>
        Public Function CreateTablesAndPopulate() As Boolean
            Dim success As Boolean = True
    
            mBuilder.InitialCatalog = InitialCatalog
            mBuilder.DataSource = ServerName
    
            Using cn As New SqlConnection With {.ConnectionString = mBuilder.ConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
                    cmd.CommandText = GenderTableScript()
                    cn.Open()
                    Try
                        ' create child table 
                        cmd.ExecuteNonQuery()
                        cmd.CommandText = PeopleTableScript()
                        ' create main table
                        cmd.ExecuteNonQuery()
    
                        'insert gender records
                        cmd.CommandText = InsertGenderScript()
                        cmd.ExecuteNonQuery()
    
                        ' insert people records
                        cmd.CommandText = InsertPeopleScript()
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        mException = ex
                        success = False
                    End Try
                End Using
            End Using
    
            If success Then
                mNewConnectionString = mBuilder.ConnectionString
            End If
            Return success
        End Function
        ''' <summary>
        ''' SQL Script to generate gender table
        ''' </summary>
        ''' <returns></returns>
        Private Function GenderTableScript() As String
            Return <SQL>
    CREATE TABLE [dbo].[GenderTypes](
    	[GenderIdentifier] [INT] IDENTITY(1,1) NOT NULL,
    	[Gender] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_GenderTypes] PRIMARY KEY CLUSTERED 
    (
    	[GenderIdentifier] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' SQl script to populate gender table
        ''' </summary>
        ''' <returns></returns>
        Private Function InsertGenderScript() As String
            Return <SQL>
    INSERT INTO [GenderTypes]
    VALUES
    ( N'Female' ), 
    ( N'Male' ), 
    ( N'Non binary' )
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' Sql script to create person table
        ''' </summary>
        ''' <returns></returns>
        Private Function PeopleTableScript() As String
            Return <SQL>
    CREATE TABLE [dbo].[Persons1](
    	[id] [INT] IDENTITY(1,1) NOT NULL,
    	[FirstName] [NVARCHAR](MAX) NULL,
    	[LastName] [NVARCHAR](MAX) NULL,
    	[GenderIdentifier] [INT] NULL,
    	[IsDeleted] [BIT] NULL,
     CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
                   </SQL>.Value
        End Function
        ''' <summary>
        ''' Sql script to populate person table
        ''' </summary>
        ''' <returns></returns>
        Private Function InsertPeopleScript() As String
            Return <SQL>
    INSERT INTO [Persons1]
    VALUES
    ( N'Mary', N'Buckley', 1, 1 ), 
    ( N'Karen', N'Payne', 1, 1 ), 
    ( N'Lee', N'Warren', 1, 1 ), 
    ( N'Regina', N'Forbes', 1, 0 ), 
    ( N'Daniel', N'Kim', 2, 0 ), 
    ( N'Dennis', N'Nunez', 2, 0 ), 
    ( N'Myra', N'Zuniga', 1, 0 ), 
    ( N'Teddy', N'Ingram', 2, 0 ), 
    ( N'Annie', N'Larson', 1, 0 ), 
    ( N'Karen', N'Anderson', 1, 0 ), 
    ( N'Jenifer', N'Livingston', 1, 0 ), 
    ( N'Stefanie', N'Perez', 1, 0 ), 
    ( N'Chastity', N'Garcia', 1, 0 ), 
    ( N'Evelyn', N'Stokes', 1, 0 ), 
    ( N'Jeannie', N'Daniel', 1, 0 ), 
    ( N'Rickey', N'Santos', 2, 0 ), 
    ( N'Bobbie', N'Hurst', 2, 0 ), 
    ( N'Lesley', N'Lawson', 1, 0 ), 
    ( N'Shawna', N'Browning', 1, 0 ), 
    ( N'Theresa', N'Ross', 1, 0 ), 
    ( N'Tasha', N'Hughes', 3, 0 ), 
    ( N'Karla', N'Hale', 1, 0 ), 
    ( N'Otis', N'Holt', 2, 0 ), 
    ( N'Alisa', N'Browning', 3, 0 ), 
    ( N'Peggy', N'Donaldson', 1, 0 ), 
    ( N'Lisa', N'Bentley', 1, 0 ), 
    ( N'Vicky', N'Wiley', 1, 0 ), 
    ( N'Nicolas', N'Spence', 2, 0 ), 
    ( N'Miranda', N'Barnes', 1, 0 ), 
    ( N'Karen', N'Barry', 1, 1 ), 
    ( N'Rosemary', N'Levine', 3, 0 ), 
    ( N'Ernest', N'Gamble', 2, 0 ), 
    ( N'Lindsay', N'Henderson', 1, 0 ), 
    ( N'Lorenzo', N'Adams', 2, 0 ), 
    ( N'Tammie', N'Graves', 1, 0 ), 
    ( N'Kareem', N'Benton', 3, 0 ), 
    ( N'Cesar', N'Vance', 3, 0 ), 
    ( N'Charlene', N'Rocha', 1, 0 ), 
    ( N'Sonja', N'Mac Donald', 1, 0 ), 
    ( N'Gwendolyn', N'Russell', 1, 0 ), 
    ( N'Stephan', N'Hill', 2, 0 ), 
    ( N'Maggie', N'Day', 1, 0 ), 
    ( N'Earnest', N'Walters', 1, 0 ), 
    ( N'Zachary', N'Pratt', 1, 0 ), 
    ( N'Erin', N'Hinton', 3, 0 ), 
    ( N'Rodolfo', N'Collier', 2, 0 ), 
    ( N'Carla', N'Jackson', 1, 0 ), 
    ( N'Norma', N'Robles', 1, 0 ), 
    ( N'Jean', N'Haynes', 1, 0 ), 
    ( N'Tara', N'Pope', 3, 0 ), 
    ( N'Ann', N'Patterson', 1, 0 ), 
    ( N'Nancy', N'Lebow', 3, 0 ), 
    ( N'Joe', N'Hansen', 2, 0 ), 
    ( N'Joe', N'Hansen', 2, 0 ), 
    ( N'Jill', N'Gallagher', 1, 0 ), 
    ( N'Sunshine', N'Miller', 1, 0 ), 
    ( N'Annabelle', N'Huff', 1, 0 ), 
    ( N'Pam', N'Gallagher', 3, 0 )
                   </SQL>.Value
        End Function
    End Class

    The scripts were easy for me as I used a third party extension to SQL-Server Management Studio, took about 1 minute but you may need to do them by hand.


    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. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Friday, June 16, 2017 8:08 PM
    Moderator
  • Typical response.... The OP doesn't know how to design a solution,  and information is given about what to do. You intervene on the Goodship Lollypop, and when you don't like the response. So you pull the axe out and go to work chop chop.  
    Saturday, June 17, 2017 12:26 AM
  • What you should have done is minded your own business, which you are incapable of doing, because what I said was not that bad.
    Saturday, June 17, 2017 12:29 AM
  • Thanks a lot for the help everyone.
    Saturday, June 17, 2017 8:10 AM
  • Strange that if somebody asks: "Where should i save the database. "

    He/she marks all replies which tell "How to save data to a database" as answer.


    Success
    Cor

    Saturday, June 17, 2017 11:22 AM
  • Thanks a lot for the help everyone.

    Maybe, you should learn how to design a .NET solution. Anyone can write some code, but can you effectively design a solution from the front-end UI to the DB on the back-end, which you don't seem to know based on the questions about the DB 

    http://www.dofactory.com/products/net-design-pattern-framework

    You should get the VB version that gives you  the source code and understand what you are being shown as you execute the code and look at how things are designed.

    https://msdn.microsoft.com/en-us/library/bb384398.aspx?f=255&MSPPError=-2147217396

    https://www.codeproject.com/Articles/21115/Building-an-N-Tier-Application-in-VB-NET-in-Step

    Saturday, June 17, 2017 12:24 PM