Manage a local sql express database using a visual studio 2012 windows application

The plan:

I have inspection records that are on a server that an application manages.  When the user goes out into the field (loses connection to the server), they will:

1.  Sync their local database with the server prior to leaving
2.  Use an application on their notebook to fill out the inspection records
3.  Sync their local db with the server again to transfer the information when they return

(I have all this code written and working on my own machine.  My end users live far away from me.)

I need to know how to:
1.  Connect to their version of SQLExpress or load SQL Express onto their machine
2.  Create a database with a specific name
3.  Load specific tables
4.  Make sure I can update table changes, if applicable

I have been searching the internet but I just confuse myself.  I need an expert to put me in the right direction.

Thanks for any help, code etc.!
Karen WilsonProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
1. You will need to have access to their machines through any hardware/software firewalls.  You can connect via TCP/IP using their IP address assuming it is turned on on their machine and is a public, static IP.

2. ?? what does this mean?  This perhaps???
http://technet.microsoft.com/en-us/library/ms176061.aspx

3. http://technet.microsoft.com/en-us/library/ms188365.aspx

4. Has to do with user logins, roles and permissions. If you give yourself those or know the sa password you can do it.
0
adriankohwsCommented:
Either static IP or using like forwarding, like old DDNS.
2.  Create a database with a specific name
3.  Load specific tables
4.  Make sure I can update table changes, if applicable

Point 2 and 3 you can use teamviewer to do it if it's for once and for all, or you can auto execute a script file for the purpose.

If you have their SA credential and having the ability to connect to their database, this is not an issue.
0
SStoryCommented:
If you don't have SA, you will need to create a user with permissions to the DB.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Karen WilsonProgrammerAuthor Commented:
Okay, not what I'm looking for....  here is the code that I have working.  There has to be an easier way for me to populate the columns etc. in the tables.  Look for the <==========

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports System.Data.SqlClient.SqlConnection
Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim isItSQL As Boolean = False
        Dim isitDatabase As Boolean = False
        Dim isitTable1 As Boolean = False

        Dim machine As String = My.Computer.Name
        Dim inst As String = "SQLEXPRESS"
        Dim dbName As String = "localWasteDatabase"
        Dim user As String = Environ("username")


        '============does the server exist???

        Dim curfile As String = "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\master.mdf"

        If (System.IO.File.Exists(curfile)) = True Then
            isItSQL = True
        Else
            '==========have the user load SQL Express
            Dim pr As Process = New Process
            pr.StartInfo.FileName = "IEXPLORE"
            pr.StartInfo.Arguments = "http://www.microsoft.com/en-us/download/details.aspx?id=29062"
            pr.Start()
            MsgBox("ENU\x64\SQLEXPR_x64_ENU.exe")
        End If

        If isItSQL = True Then

            '==============check to see if the database exists
            Dim dbfile As String = "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\localWasteDatabase.mdf"

            If (System.IO.File.Exists(dbfile)) = False Then
                '==== create the database and tables

                Dim str As String
                Dim srv As Server
                srv = New Server(machine & "\" & inst)

                Dim myConn As SqlConnection = New SqlConnection("Server=" & machine & "\" & inst & ";Trusted_Connection=True;")

                str = "CREATE DATABASE localWasteDatabase ON PRIMARY " & _
                        "(NAME = localWasteDatabase_Data, " & _
                        " FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\localWasteDatabase.mdf', " & _
                        " SIZE = 3MB, " & _
                        " MAXSIZE = 10MB, " & _
                        " FILEGROWTH = 10%) " & _
                        " LOG ON " & _
                        "(NAME = localWasteDatabase_Log, " & _
                        " FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\localWasteDatabase.ldf', " & _
                        " SIZE = 3MB, " & _
                        " MAXSIZE = 5MB, " & _
                        " FILEGROWTH = 10%) "

                Dim myCommand As SqlCommand = New SqlCommand(str, myConn)

                Try
                    myConn.Open()
                    myCommand.ExecuteNonQuery()
                    MessageBox.Show("Database is created successfully", _
                                    "MyProgram", MessageBoxButtons.OK, _
                                     MessageBoxIcon.Information)
                Catch ex As Exception
                    MessageBox.Show(ex.ToString())
                Finally
                    If (myConn.State = ConnectionState.Open) Then
                        myConn.Close()
                    End If
                End Try

               '<======start setting up the tables

                Dim db As Database
                db = srv.Databases("localWasteDatabase")

                Dim tb As Table
                tb = New Table(db, "tblContainerMasterloc")

                Dim col1 As Column
                col1 = New Column(tb, "containerIDloc", DataType.Int)
                col1.Collation = "Latin1_General_CI_AS"
                col1.Nullable = False
                tb.Columns.Add(col1)

                Dim col2 As Column
                col2 = New Column(tb, "contentsloc", DataType.NVarChar(150))
                col2.Collation = "Latin1_General_CI_AS"
                col2.Nullable = True
                tb.Columns.Add(col2)

                tb.Create()

            End If
        End If
    End Sub
End Class


Everything is working for the new set up but I'd like an easier way to create the tables like using SQL stored procedures???
0
SStoryCommented:
If this is a fresh install, the easiest way to create everything is to have a standard .mdf .ldf pair of files just like you want it. Copy those two files to the appropriate location. Then
create the database on the SQL Server. Attach those two files to the database.

http://technet.microsoft.com/en-us/library/ms187858.aspx

In that case you should get them all without creating anything new because those two files should be shipped by you with all that is needed.
0
Karen WilsonProgrammerAuthor Commented:
What I ended up doing is crude but effective.  I have SQL Express as a prereq when they load the application.  Then I look for it and make the database and tables.  If it didn't load, I give them the location to load it.

  'set up SQL
        Dim machine As String = My.Computer.Name
        Dim inst As String = "SQLEXPRESS"
        Dim dbName As String = "localWasteDatabase"
        Dim user As String = Environ("username")

        '============does the server exist???
        Dim doWeHaveExpress As Boolean = False
        Dim fileFolder As New List(Of String)

        Dim directoryList As New List(Of String)
        '----------make a list of all the directories and sub-directories
        Dim path As String = "C:\Program Files\Microsoft SQL Server"
        Dim di As New DirectoryInfo(path)

        For Each d As DirectoryInfo In di.GetDirectories("*", SearchOption.TopDirectoryOnly)
            directoryList.Add(d.FullName)
        Next

     
        For Each r In directoryList
            Dim p As String = r
            If p.EndsWith(".SQLEXPRESS") Then
                doWeHaveExpress = True
                fileFolder.Add(p.Trim)
                fileLocation = p.Trim
            End If
        Next

        If doWeHaveExpress = False Then

            MsgBox("You will need to load SQL Express onto your computer.")

            '==========have the user load SQL Express
            Dim pr As Process = New Process
            pr.StartInfo.FileName = "IEXPLORE"
            pr.StartInfo.Arguments = "http://www.microsoft.com/en-us/download/details.aspx?id=29062"
            pr.Start()
            MsgBox("Load the first box to check - (ENU\x64\SQLEXPR_x64_ENU.exe)")

            Me.Close()
        Else

            '==============check to see if the database exists
            Dim dbfile As String = fileLocation & "\MSSQL\DATA\localWasteDatabase.mdf"
            Dim logFile As String = fileLocation & "\MSSQL\DATA\localWasteDatabase.ldf"

            If (System.IO.File.Exists(dbfile)) = False Then
                '==== create the database and tables

                Dim str As String
                Dim srv As Server
                srv = New Server(machine & "\" & inst)

                Dim myConn As SqlConnection = New SqlConnection("Server=" & machine & "\" & inst & ";Trusted_Connection=True;")

                str = "CREATE DATABASE localWasteDatabase ON PRIMARY " & _
                        "(NAME = localWasteDatabase_Data, " & _
                        " FILENAME = '" & dbfile & "', " & _
                        " SIZE = 3MB, " & _
                        " MAXSIZE = 10MB, " & _
                        " FILEGROWTH = 10%) " & _
                        " LOG ON " & _
                        "(NAME = localWasteDatabase_Log, " & _
                        " FILENAME = '" & logFile & "', " & _
                        " SIZE = 3MB, " & _
                        " MAXSIZE = 5MB, " & _
                        " FILEGROWTH = 10%) "

                Dim myCommand As SqlCommand = New SqlCommand(str, myConn)

                Try
                    myConn.Open()
                    myCommand.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show(ex.ToString())
                Finally
                    If (myConn.State = ConnectionState.Open) Then
                        myConn.Close()
                    End If
                End Try

'THIS SECTION LOADS THE TABLES IN THE DATABASE
                Me.ContMasterToolStripMenuItem.PerformClick()
                Me.ContTransToolStripMenuItem.PerformClick()
                Me.SiteLocToolStripMenuItem.PerformClick()
                Me.SiteTransToolStripMenuItem.PerformClick()
                Me.CoordinatorsToolStripMenuItem.PerformClick()
                Me.InspectionsToolStripMenuItem.PerformClick()
            Else
                'do nothing
            End If
        End If
EndSub

To create the tables, I used this code:
'load the inspection table

Dim machine As String = My.Computer.Name
        Dim inst As String = "SQLEXPRESS"
        Dim dbName As String = "localWasteDatabase"
        Dim user As String = Environ("username")

        Dim strTable1 As String

        Dim myConnT1 As SqlConnection = New SqlConnection("Server=" & machine & "\" & inst & ";Trusted_Connection=True;")

        strTable1 = "USE [localWasteDatabase]" & _
                         "CREATE TABLE [dbo].[tblWasteSiteInspectionloc](" & _
    "[siteIDloc] [decimal](18, 0) NOT NULL," & _
    "[weekEndingloc] [datetime] NOT NULL," & _
    "[wasteCoordinatorloc] [nvarchar](50) NULL," & _
    "[arealoc] [nvarchar](25) NULL," & _
    "[locationloc] [nvarchar](255) NULL," & _
    "[containerIDloc] [nvarchar](50) NULL," & _
    "[inspDateloc] [date] NULL," & _
    "[inspTimeloc] [nvarchar](25) NULL," & _
    "[labelsloc] [nvarchar](5) NULL," & _
    "[labelARloc] [nvarchar](5) NULL," & _
    "[labelsCmntloc] [nvarchar](max) NULL," & _
    "[signsloc] [nvarchar](5) NULL," & _
    "[signsARloc] [nvarchar](5) NULL," & _
    "[signsCmntloc] [nvarchar](max) NULL," & _
    "[logbookloc] [nvarchar](5) NULL," & _
    "[logbookARloc] [nvarchar](5) NULL," & _
    "[logbookCmntloc] [nvarchar](max) NULL," & _
    "[containersloc] [nvarchar](5) NULL," & _
    "[containersARloc] [nvarchar](5) NULL," & _
    "[containersCmntloc] [nvarchar](max) NULL," & _
    "[spillsloc] [nvarchar](5) NULL," & _
    "[spillsARloc] [nvarchar](5) NULL," & _
    "[spillsCmntloc] [nvarchar](max) NULL," & _
    "[userNameloc] [nvarchar](30) NULL," & _
    "[modUserloc] [nvarchar](20) NULL," & _
    "[modDateloc] [datetime] NULL," & _
 "CONSTRAINT [PK_tblWasteSiteInspectionloc] PRIMARY KEY CLUSTERED" & _
"(" & _
    "[siteIDloc] ASC," & _
    "[weekEndingloc] 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]"

        Dim myCommandT As SqlCommand = New SqlCommand(strTable1, myConnT1)

        Try
            myConnT1.Open()
            myCommandT.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)

        Finally
            If (myConnT1.State = ConnectionState.Open) Then
                myConnT1.Close()
            End If
        End Try
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SStoryCommented:
Shipping the precreated db and reattaching is the simplest way.
0
Karen WilsonProgrammerAuthor Commented:
I needed an answer quickly so I went crude and effective.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.