Solved

Proper way to add a SQL Express user instance database

Posted on 2009-05-07
29
780 Views
Last Modified: 2012-05-06
I tried this once before, but apparently went about the wrong route, so I thought I'd ask the experts.

I am at the point where I want to add a database to my project.  I expect the project to verify SQL Express is installed (via prerequisites), but then I want to ensure that the user has a database created and build two tables inside it:  tblLog & tblFood

Can anyone that's got some experience with this help me out?
It doesn't have to be a single user instance if that makes it more difficult....
0
Comment
Question by:sirbounty
  • 16
  • 7
  • 6
29 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24326528
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24326890
Hmm - I'm not sure I'm comfortable 'adding a wrapper'...not sure I know what that is. :^)

I already have code to test that the service is running (thus 'installed').
I guess I just need to know how to properly create the database (my attempts before were generating a new copy each time the code ran, thus overwriting the tables).
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24326923
OK, I gotcha.  Are you just wanting the create database syntax?

if exists(select 1 from sys.databases where name = 'dbname')
begin
drop database dbname
end
else
begin
create database dbname...
end

0
 
LVL 67

Author Comment

by:sirbounty
ID: 24328424
So the if exists is a sql string I pass to the connection?
I'll give it a shot...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24328675
But if it doesn't exist - how do I add it?
I was adding it to the project as a data source before...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24329525
Would it help if I uploaded the entire project?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24329555
So, you're asking me what do you do if the database does not exist...or are you asking me if the sql express isntance has not been installed?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24329587
I've got the service test - so I know SE is installed.
I'm having a problem with the database creation...I've got that 'sort of' working, but I still don't think I did it right, cause when I try to add tables, I get an error:

"An attempt to attach an auto-named database for file C:\Users\xxxxx\Documents\Visual Studio 2005\Projects\DEJ\DEJ\bin\Debug\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24329619
post the script you're using to create the db....odds are, your data file names are the same.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24329744
I think this is all of the relevant stuff...
  Private _dbPath As String = Assembly.GetExecutingAssembly.Location

  Public conn As String
 
 

    Function VerifyDB() As Boolean

        If VerifyService() Then

            If VerifyDatabase() Then

                If VerifyTable("tblLog") And VerifyTable("tblFood") Then

                    Return True

                Else

                    'create tables?

                End If

            End If

        End If

        Return False

    End Function
 

    Function VerifyService() As Boolean

        Dim ServiceName As String = "MSSQL$SQLEXPRESS"

        Try

            Using sc As New ServiceProcess.ServiceController(ServiceName)

                Dim s As String = sc.DisplayName

                Return True

            End Using

        Catch ex As Exception

            Return False

        End Try

    End Function
 

    Function VerifyDatabase() As Boolean

        _dbPath = Path.GetDirectoryName(_dbPath)

        _dbPath = _dbPath & Path.DirectorySeparatorChar & "DEJ.mdf"
 

        'conn = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _dbPath & ";Integrated Security=True;" 'User Instance=True"

        conn = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"

        Dim fi As FileInfo = New FileInfo(_dbPath)
 

        If Not fi.Exists Then

            If CreateDatabase(fi) Then Return True

        Else

            Return True

        End If

    End Function
 

    Function VerifyTable(ByVal tblName As String) As Boolean

        Using con As New SqlConnection(conn)

            Dim checkTable As String = [String].Format("IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", tblName)

            Dim command As New SqlCommand(checkTable, con)

            command.CommandType = CommandType.Text

            Try

                con.Open()

                con.Close()

                Return True

            Catch ex As Exception

                Return False

            End Try

   

        End Using

    End Function
 

    Function CreateDatabase(ByVal fi As FileInfo) As Boolean

        Dim exe As Assembly = Assembly.GetExecutingAssembly

        Dim s As Stream = exe.GetManifestResourceStream("DEJ.Resources.DEJ.mdf")

  

        Try

            Dim fsDB As FileStream = fi.Create

            Return True

        Catch ex As Exception

            MessageBox.Show("Could not create database file" & vbNewLine & Err.Description, "Prerequisite Failure", MessageBoxButtons.OK)

            Return False

        End Try
 

    End Function
 

    Sub CreateTables()

        Using con As New SqlConnection(conn)

            con.Open()

            Dim TableConstructs As String() = {"tblLog (EntryDate datetime, FoodID varchar(20))", "tblFood (FoodID varChar(20), FoodDesc varchar(255), Calories bigint, Fat bigint, FatCalories bigint, Carbs bigint, Protein bigint)"}
 

            For Each TableCon As String In TableConstructs
 

                Using cmd As New SqlCommand("CREATE TABLE " & TableCon, con)

                    cmd.ExecuteNonQuery()

                End Using

            Next

            con.Close()

        End Using

    End Sub

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24329780
Looks like the db already exists.  In Express, you should be able to connect to the instance first, see if the db exists, and THEN try to connect to a certain db w/ the attachdbfilename thingy.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24329793
Not sure I follow...?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24329794
so, i would first connect to the master db, check to see if your user db exists, if it does, connect...if not, create it.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24329817
There's only going to be one database - thought it was a single user instance that would only get created if it didn't exist.
At this point, there's no data there - I just added the db to my project.
But I want to accommodate one other friend that may use my app as well.  We would need different data, but we don't share computers.

Should I remove the db and start over from there?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24329868
I would.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24332572
Deleted it from my project, used the other connection string, it created the database, but when it got to the VerifyTable, I get

An attempt to attach an auto-named database for file C:\Users\xxxxx\Documents\Visual Studio 2005\Projects\DEJ\DEJ\bin\Debug\DEJ.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."

0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24345984

I like to create generalized classes, so I took your idea, and ran with it.  I added references to SMO objects, in order to create databases and tables on SQL Server Express.


' Add a reference to the following Server Management Object (SMO) libraries:

'   Microsoft.SqlServer.ConnectionInfo 

'   Microsoft.SqlServer.Smo

 

Imports System.Configuration

Imports System.Data.SqlClient

Imports System.IO

Imports System.Reflection

Imports System.ServiceProcess

Imports Microsoft.SqlServer.Management.Smo

 

Public Class SqlServerExpress

 

    Private Const SERVICE_NAME As String = "MSSQL$SQLEXPRESS"

 

    Private m_serverName As String

    Private m_databaseName As String

    Private m_tableList As List(Of SqlTable)

 

    Private m_server As Server

    Private m_database As Database

 

    Public Sub New(ByVal serverName As String, ByVal databaseKey As String, ByVal databaseName As String, ByVal tableList As List(Of SqlTable))

 

        ' Validate arguments

 

        If String.IsNullOrEmpty(serverName) Then

            Throw New ArgumentNullException("Server")

        End If

 

        If String.IsNullOrEmpty(databaseKey) Then

            Throw New ArgumentNullException("Database key")

        End If

 

        If String.IsNullOrEmpty(databaseName) Then

            Throw New ArgumentNullException("Database name")

        End If

 

        If tableList Is Nothing OrElse tableList.Count = 0 Then

            Throw New ArgumentNullException("Table list")

        End If

 

        ' Verify that SQL Server Express is installed.

        If Not VerifyService() Then

            Throw New InvalidOperationException("Cannot verify that SQL Server Express is installed.")

        End If

 

        ' Store server and database metadata

        m_serverName = serverName

        m_databaseName = databaseName

        m_tableList = tableList

 

        ' Reference the SQL Server Express instance.

        m_server = New Server(m_serverName)

 

        ' Verify that the database was created, and create if it doesn't exist.

        Me.VerifyDatabase()

    End Sub

 

    Private Sub VerifyDatabase()

        If Not m_server.Databases.Contains(m_databaseName) Then

            Me.CreateDatabase()

        Else

            m_database = m_server.Databases(m_databaseName)

        End If

 

        For Each table As SqlTable In m_tableList

            If Not VerifyTable(table) Then

                Me.CreateTable(table)

            End If

        Next table

    End Sub

 

    Private Function VerifyService() As Boolean

        Dim serviceList As ServiceController() = ServiceController.GetServices()

        For Each service As ServiceController In serviceList

            If service.ServiceName = SERVICE_NAME Then

                Return True

            End If

        Next

        Return False

    End Function

 

    Private Function VerifyTable(ByVal table As SqlTable) As Boolean

        If table.ColumnList Is Nothing OrElse table.ColumnList.Count = 0 Then

            Throw New ArgumentNullException("Column list")

        End If

 

        Return m_database.Tables.Contains(table.Name)

    End Function

 

    Private Function CreateDatabase() As Boolean

        m_database = New Database(m_server, m_databaseName)

        m_database.Create()

 

        Return m_server.Databases.Contains(m_databaseName)

    End Function

 

    Private Sub CreateTable(ByVal table As SqlTable)

        Dim newTable As New Table(m_database, table.Name)

 

        For Each column As SqlColumn In table.ColumnList

            Dim newColumn As New Column(newTable, column.Name, column.DataType)

            newTable.Columns.Add(newColumn)

        Next column

        newTable.Create()

    End Sub

 

    Public Class SqlTable

        Private m_name As String

        Private m_columnList As List(Of SqlColumn)

 

        Public Sub New(ByVal name As String, ByVal ParamArray columnList As SqlColumn())

            m_name = name

            m_columnList = New List(Of SqlColumn)(columnList)

        End Sub

 

        Public ReadOnly Property Name() As String

            Get

                Return m_name

            End Get

        End Property

 

        Public ReadOnly Property ColumnList() As List(Of SqlColumn)

            Get

                Return m_columnList

            End Get

        End Property

    End Class

 

    Public Class SqlColumn

        Private m_name As String

        Private m_dataType As DataType

 

        Public Sub New(ByVal name As String, ByVal dataType As DataType)

            m_name = name

            m_dataType = dataType

        End Sub

 

        Public ReadOnly Property Name() As String

            Get

                Return m_name

            End Get

        End Property

 

        Public ReadOnly Property DataType() As DataType

            Get

                Return m_dataType

            End Get

        End Property

 

    End Class

 

End Class

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24345985
Sample usage:


   Private Sub CreateDatabase()
 

        Dim tableLog As New SqlServerExpress.SqlTable("tblLog", _

            New SqlServerExpress.SqlColumn("EntryDate", DataType.SmallDateTime), _

            New SqlServerExpress.SqlColumn("FoodID", DataType.VarChar(20)))
 

        Dim tableFood As New SqlServerExpress.SqlTable("tblFood", _

            New SqlServerExpress.SqlColumn("FoodID", DataType.VarChar(20)), _

            New SqlServerExpress.SqlColumn("FoodDesc", DataType.VarChar(255)), _

            New SqlServerExpress.SqlColumn("Calories", DataType.Int), _

            New SqlServerExpress.SqlColumn("Fat", DataType.Int), _

            New SqlServerExpress.SqlColumn("FatCalories", DataType.Int), _

            New SqlServerExpress.SqlColumn("Carbs", DataType.Int), _

            New SqlServerExpress.SqlColumn("Protein", DataType.Int))
 

        Dim tableList As New List(Of SqlServerExpress.SqlTable)(New SqlServerExpress.SqlTable() {tableLog, tableFood})
 

        Dim express As New SqlServerExpress("windowsvista\sqlexpress", "MyDb", "MyDatabase", tableList)

    End Sub

Open in new window

0
 
LVL 67

Author Comment

by:sirbounty
ID: 24353549
This looks great - unfortunately I can't seem to locate the SMO in project references.  Did I neglect to install something (just chose the default install of SE)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24353612
My friend, that is a great question.  I have always had it, so I don't think about how it gets installed.  It might come with the SQL Server Management Studio Express install:

Microsoft SQL Server Management Studio Express
http://www.microsoft.com/Downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24353691
Sadly no. : \
From what I've uncovered it should be installed in ProgFiles\MS Sql Server\90\SDK\Assembly
My SDK only has Include & Lib subfolders.

I'll keep digging...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24353827
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24353998
Yep, that was it - back on track here...
I get an error at     If Not m_server.Databases.Contains(m_databaseName) Then

A first chance exception of type 'Microsoft.SqlServer.Management.Common.ConnectionFailureException' occurred in Microsoft.SqlServer.ConnectionInfo.dll

? ex.Message
"Failed to connect to server sqlexpress."

I'm willing to start this over from scratch as well if it'll help...I've been circling too long on this one thing that I could have easily passed if I was the only one using this.  I'm trying to be helpful and share it with a fellow runner... ;^)

Unless you know of what I've missed/overlooked, or have an idea what to check.

Thanx for your time sir! :^)



0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24355331
If you look at this line:

  Dim express As New SqlServerExpress("windowsvista\sqlexpress", "MyDb", "MyDatabase", tableList)

The first argument is the instance name.  That is my instance name, and should be changed to your instance name.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24355367
Thought I had - I'll double-check it...
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24355375
If it is the only instance, and it is the default name, then I believe that you can use .\SQLEXPRESS for the server name.
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24355462
Ah, that was it...
I had used 'SQLEXPRESS' instead of '.\SQLEXPRESS'

Thank you very much - this is terrific!!! :^)
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 24357144
'Twas my pleasure, for sure!!
0
 
LVL 67

Author Comment

by:sirbounty
ID: 24360992
TLO - slight hiccup - I need the one table to have an identity/pk column for FoodID.

http://www.experts-exchange.com/Programming/Languages/.NET/Q_24400108.html is my new question in regards to it (I tried to get it started...but got stuck :^)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now