Click here to Skip to main content
15,885,546 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to create a sql server database at runtime in my vb .net project. I know how to actually code the database but I am wondering where should I actually put the code? Should I be putting the code in the start up form or should it go into a class on it's own? Also, this project will be going on more than one pc at a particular site, so I only want the database to be created the first time the project is activated and then just be able query the database on different pcs after that. How do I do this? All help on this matter would be greatly appreciated.
Posted

First, check the SQL Server instance to see if the DB is there: that's pretty easy, you can get a list of all DB's from SQL like this:
VB
Using con As New SqlConnection("Data Source=GRIFFPC\SQLEXPRESS;Integrated Security=True")
	con.Open()
	Using cmd As New SqlCommand("sp_databases", con)
		cmd.CommandType = CommandType.StoredProcedure
		Dim read As SqlDataReader = cmd.ExecuteReader()
		While read.Read()
			Console.WriteLine(DirectCast(read("DATABASE_NAME"), String))
		End While
	End Using
End Using
If your DB is in the list, it exists. Sorted.

If it isn't, then you need to create it: and the easiest way is to put the SQL commands into a file, and send that to the new SQL instance as a command string - SSMS will generate the file for you (complete with a minimum or sample set of data if needed).

Where to put it? Well - that's a good question! Creating the DB could take some time, so you want it to happen when the user can see that something is going on. Myself, I'd create a small "Initializing" form which is run by the startup form as a modal dialog and which shows the user what is happening, and only returns when the DB is ready to rock'n'roll. (I'd also run the code in a background worker within that form so that the UI remains responsive)
 
Share this answer
 
Comments
Member 10804519 30-Jul-14 7:28am    
Where you have 'sp_databases' in the using cmd... statement, what should I be replacing that with?
OriginalGriff 30-Jul-14 7:50am    
Nothing - it's an SQL built in stored procedure which returns the DB details.
Member 10804519 30-Jul-14 9:34am    
OK thank you for your help.
OriginalGriff 30-Jul-14 9:42am    
You're welcome!
When building object oriented code I find it best to follow the SOLID principles.

The first of which is the Single Responsibility Principle.

This suggests that each of your programming objects should only have a single purpose.

Following this methodology then yes you should create separate classes for creating your database and let you form simply be a form.

In interface design there are a number of programming patterns which will allow you to separate your logic from your interface. One such pattern which works well is MVVM. Model View View-Model.

Model-View-ViewModel (MVVM) Explained[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900