• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 825
  • Last Modified:

Proper way to add a SQL Express user instance database

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
sirbounty
Asked:
sirbounty
  • 16
  • 7
  • 6
1 Solution
 
chapmandewCommented:
0
 
sirbountyAuthor Commented:
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
 
chapmandewCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sirbountyAuthor Commented:
So the if exists is a sql string I pass to the connection?
I'll give it a shot...
0
 
sirbountyAuthor Commented:
But if it doesn't exist - how do I add it?
I was adding it to the project as a data source before...
0
 
sirbountyAuthor Commented:
Would it help if I uploaded the entire project?
0
 
chapmandewCommented:
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
 
sirbountyAuthor Commented:
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
 
chapmandewCommented:
post the script you're using to create the db....odds are, your data file names are the same.
0
 
sirbountyAuthor Commented:
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
 
chapmandewCommented:
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
 
sirbountyAuthor Commented:
Not sure I follow...?
0
 
chapmandewCommented:
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
 
sirbountyAuthor Commented:
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
 
chapmandewCommented:
I would.
0
 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:

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
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
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
 
sirbountyAuthor Commented:
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
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
Thought I had - I'll double-check it...
0
 
Bob LearnedCommented:
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
 
sirbountyAuthor Commented:
Ah, that was it...
I had used 'SQLEXPRESS' instead of '.\SQLEXPRESS'

Thank you very much - this is terrific!!! :^)
0
 
Bob LearnedCommented:
'Twas my pleasure, for sure!!
0
 
sirbountyAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 16
  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now