sirbounty
asked on
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....
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....
ASKER
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).
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).
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
if exists(select 1 from sys.databases where name = 'dbname')
begin
drop database dbname
end
else
begin
create database dbname...
end
ASKER
So the if exists is a sql string I pass to the connection?
I'll give it a shot...
I'll give it a shot...
ASKER
But if it doesn't exist - how do I add it?
I was adding it to the project as a data source before...
I was adding it to the project as a data source before...
ASKER
Would it help if I uploaded the entire project?
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?
ASKER
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\V isual Studio 2005\Projects\DEJ\DEJ\bin\ Debug\Data base.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share."
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\V
post the script you're using to create the db....odds are, your data file names are the same.
ASKER
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
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.
ASKER
Not sure I follow...?
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.
ASKER
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?
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?
I would.
ASKER
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\V isual 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."
An attempt to attach an auto-named database for file C:\Users\xxxxx\Documents\V
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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)
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
Microsoft SQL Server Management Studio Express
http://www.microsoft.com/Downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en
ASKER
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...
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...
ASKER
ASKER
Yep, that was it - back on track here...
I get an error at If Not m_server.Databases.Contain s(m_databa seName) Then
A first chance exception of type 'Microsoft.SqlServer.Manag ement.Comm on.Connect ionFailure Exception' occurred in Microsoft.SqlServer.Connec tionInfo.d ll
? 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! :^)
I get an error at If Not m_server.Databases.Contain
A first chance exception of type 'Microsoft.SqlServer.Manag
? 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! :^)
If you look at this line:
Dim express As New SqlServerExpress("windowsv ista\sqlex press", "MyDb", "MyDatabase", tableList)
The first argument is the instance name. That is my instance name, and should be changed to your instance name.
Dim express As New SqlServerExpress("windowsv
The first argument is the instance name. That is my instance name, and should be changed to your instance name.
ASKER
Thought I had - I'll double-check it...
If it is the only instance, and it is the default name, then I believe that you can use .\SQLEXPRESS for the server name.
ASKER
Ah, that was it...
I had used 'SQLEXPRESS' instead of '.\SQLEXPRESS'
Thank you very much - this is terrific!!! :^)
I had used 'SQLEXPRESS' instead of '.\SQLEXPRESS'
Thank you very much - this is terrific!!! :^)
'Twas my pleasure, for sure!!
ASKER
TLO - slight hiccup - I need the one table to have an identity/pk column for FoodID.
https://www.experts-exchange.com/questions/24400108/Creating-a-table-with-an-identity.html is my new question in regards to it (I tried to get it started...but got stuck :^)
https://www.experts-exchange.com/questions/24400108/Creating-a-table-with-an-identity.html is my new question in regards to it (I tried to get it started...but got stuck :^)
http://msdn.microsoft.com/en-us/library/bb264562.aspx