I was pleasantly surprised to find how many people actually read my posting Installing MSDE with your .NET application, in that posting I talked about how to install MSDE with your Setup and Deployment created MSI. How to edit it with Ocra so it will actually work.
In this posting I will talk about how to configure an initial database for your MSDE app.
I wrote this code in VB.NET and used Carlosag.net to translate the code from VB.net to C#, this tool does a great job but I can not guarantee the accuracy of C#.
One last disclaimer: My goal here is to post useful information for other developers, so please forgive the spelling and gramatical errors.
To the main application I add a class ClsInstallation which has three public Methods:
- IsEngineInstalled - Checks to see if the database engine is installed and returns a boolean value.
- IsDBInstalled - Checks to see if the database is installed and if its not it will install it.
- StartupSvc - Starts up the sqlengine service.
In my application startup I have:
VB
Dim cinstall As ClsInstallation = New ClsInstallation
If Not cinstall.ISEngineInstalled Then
MsgBox("Database Engine could not be found please install the HRAA Database.")
END
End If
If Not cinstall.IsDBInstalled Then
MsgBox("Could not Restore Databse.")
end
EndIf
C#
ClsInstallation cinstall = new ClsInstallation();
if (!cinstall.ISEngineInstalled) {
MsgBox("Database Engine could not be found please install the HRAA Database.");
}
If!cinstall.IsDBInstalled;
ThenMsgBox("Could not Restore Databse.");
End
EndIf
VB
Imports System.Threading
Imports System.Data.SqlClient
Imports System.IO
Imports System.ServiceProcess
C#
using System.Threading;
using System.Data.SqlClient;
using System.IO;
using System.ServiceProcess;
I am using MyMSDE as the name of the MSDE instance that was created during setup process. Make sure you modify the following two lines
VB
Dim Cnstr As String = "Initial Catalog=master;Data Source=(local)\\HRAA;Trusted_Connection=true"
Dim sMSDEsvc As String = "MSSQL$HRAA"
C#
string Cnstr = "Initial Catalog=master;Data Source=(local)\\MYMSDE;Trusted_Connection=true";
string sMSDEsvc = "MSSQL$MYMSDE";
IsEngineInstalled calls a private function that loops through installed services trying to locate sMSDEsvc (SQL service for your instance of MSDE)
VB
Public Sub ISEngineInstalled()
If ServiceInstalled(sMSDEsvc) Then
Return true
Else
Return false
End If
End Sub
Private Function ServiceInstalled(ByVal s As String) As Boolean
Dim controllers() As ServiceController = ServiceController.GetServices
Dim nNum As Integer = controllers.Length
Dim i As Integer
i = 0
Do While (i <= (nNum - 1))
Application.DoEvents
If (controllers(i).ServiceName.ToUpper = s.ToUpper) Then
Return true
End If
i +=1
Loop
Return false
End Function
C#
public void ISEngineInstalled() {
if (ServiceInstalled(sMSDEsvc)) {
return true;
}
else {
return false;
}
}
private bool ServiceInstalled(string s) {
ServiceController[] controllers = ServiceController.GetServices;
int nNum = controllers.Length;
int i;
for (i = 0; (i
<= (nNum - 1)); i++) {
Application.DoEvents();
if ((controllers[i].ServiceName.ToUpper == s.ToUpper)) {
return true;
}
}
return false;
}
Next function IsDBInstalled makes sure:
- The engine is started.
- If the database exits, if not Create it
- then restore from a Backup ( I also have added code to show you how you can attach to data and log files)
You do need to include either a backup of the startup database or detach the database and include the .MDF and .LDF files.
VB
Public Function IsDBInstalled()
Try
Dim controllers As ServiceController() = ServiceController.GetServices
If Not StartUPSVC(sMSDEsvc) Then
MsgBox("Could not start service, Please restart your computer and rerun application")
Return False
End If
Dim cn As SqlConnection = New SqlConnection(Cnstr)
cn.Open()
Dim cmd As SqlCommand = New SqlCommand(" exec sp_databases", cn)
Dim dr As SqlDataReader
cmd.CommandTimeout *= 600
cmd.Connection = cn
dr = cmd.ExecuteReader
bfound = False
Do While dr.Read
Application.DoEvents()
If dr(0).ToString.ToUpper = "MYMSDE".ToUpper Then
bfound = True
Return True
End If
Loop
dr.Close()
Dim ssql As String
Dim Brestore As Boolean = True
Dim spath As String
spath = Directory.GetCurrentDirectory.ToLower
If Not bfound Then
Try
If File.Exists(spath & "\data\MYMSDE_Data.MDF") Then
File.Delete(spath & "\data\MYMSDE_Data.MDF")
End If
If File.Exists(spath & "\data\MYMSDE_LOG.LDF") Then
File.Delete(spath & "\data\MYMSDE_LOG.LDF")
End If
ssql = " CREATE DATABASE [MYMSDE] ON (NAME = 'MYMSDE_Data', "
ssql &= " FILENAME = '" & spath & "\data\MYMSDE_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) "
ssql &= " LOG ON (NAME = 'MYMSDE_Log', FILENAME = '" & spath & "\data\MYMSDE_Log.LDF' , "
ssql &= " SIZE = 2, FILEGROWTH = 10%) "
cmd.CommandText = ssql
cmd.ExecuteScalar()
Catch exc As SqlClient.SqlException
MsgBox(exc.ToString)
Return False
End Try
Else
Dim tmpstr As String = "Would you like to save the data from previous Installation?"
tmpstr &= vbCrLf & vbCrLf & "Press ''NO'' to overwrite your data with a blank database."
tmpstr &= vbCrLf & "Press ''YES'' to keep your data from previous versions."
If MsgBox(tmpstr, MsgBoxStyle.Information + MsgBoxStyle.YesNo, "Database exists.") = MsgBoxResult.Yes Then
Brestore = False
Else
Brestore = True
End If
''Brestore = False
End If
If Brestore Then
MsgBox("Populating Database will take an additional 5-10 minutes")
ssql = "RESTORE DATABASE MYMSDE FROM DISK = '" & spath & "\data\MYMSDE.bup'"
cmd.CommandText = ssql
cmd.ExecuteScalar()
Else
End If
Return True
Catch exc As Exception
MsgBox("Could not configure the Datbase, please restart your computer and try again." & vbCrLf & exc.Message)
End Try
End Function
C#
public void IsDBInstalled() {
try {
ServiceController[] controllers = ServiceController.GetServices;
if (!StartUPSVC(sMSDEsvc)) {
MsgBox("Could not start service, Please restart your computer and rerun application");
return false;
}
SqlConnection cn = new SqlConnection(Cnstr);
cn.Open();
SqlCommand cmd = new SqlCommand(" exec sp_databases", cn);
SqlDataReader dr;
600;
cmd.Connection = cn;
dr = cmd.ExecuteReader;
bfound = false;
while (dr.Read) {
Application.DoEvents();
if ((dr[0].ToString.ToUpper == "MYMSDE".ToUpper)) {
bfound = true;
return true;
}
}
dr.Close();
string ssql;
bool Brestore = true;
string spath;
spath = Directory.GetCurrentDirectory.ToLower;
if (!bfound) {
try {
if (File.Exists((spath + "\\data\\MYMSDE_Data.MDF"))) {
File.Delete((spath + "\\data\\MYMSDE_Data.MDF"));
}
if (File.Exists((spath + "\\data\\MYMSDE_LOG.LDF"))) {
File.Delete((spath + "\\data\\MYMSDE_LOG.LDF"));
}
ssql = " CREATE DATABASE [MYMSDE] ON (NAME = \'MYMSDE_Data\', ";
(" FILENAME = \'"
+ (spath + "\\data\\MYMSDE_Data.MDF\' , SIZE = 2, FILEGROWTH = 10%) "));
(" LOG ON (NAME = \'MYMSDE_Log\', FILENAME = \'"
+ (spath + "\\data\\MYMSDE_Log.LDF\' , "));
" SIZE = 2, FILEGROWTH = 10%) ";
cmd.CommandText = ssql;
cmd.ExecuteScalar();
}
catch (SqlClient.SqlException exc) {
MsgBox(exc.ToString);
return false;
}
}
else {
string tmpstr = "Would you like to save the data from previous Installation?";
("\r\n" + ("\r\n" + "Press \'\'NO\'\' to overwrite your data with a blank database."));
("\r\n" + "Press \'\'YES\'\' to keep your data from previous versions.");
if ((MsgBox(tmpstr, (MsgBoxStyle.Information + MsgBoxStyle.YesNo), "Database exists.") == MsgBoxResult.Yes)) {
Brestore = false;
}
else {
Brestore = true;
}
// 'Brestore = False
}
if (Brestore) {
MsgBox("Populating Database will take an additional 5-10 minutes");
ssql = ("RESTORE DATABASE MYMSDE FROM DISK = \'"
+ (spath + "\\data\\MYMSDE.bup\'"));
cmd.CommandText = ssql;
cmd.ExecuteScalar();
}
else {
}
return true;
}
catch (Exception exc) {
MsgBox(("Could not configure the Datbase, please restart your computer and try again." + ("\r\n" + exc.Message)));
}
}
Here is code to attach to a .MDF and .LDF file
VB
ssql = " Use Master "
cmd.CommandText = ssql
cmd.ExecuteScalar()
ssql = " sp_attach_db 'MYMSDE','" & mpath & "\data\MYMSDE_Data.MDF' ,'" & mpath & "\data\MYMSDE_Log.LDF'"
cmd.CommandText = ssql
cmd.ExecuteScalar()
C#
ssql = " Use Master ";
cmd.CommandText = ssql;
cmd.ExecuteScalar();
ssql = (" sp_attach_db \'MYMSDE\',\'"
+ (mpath + ("\\data\\MYMSDE_Data.MDF\' ,\'"
+ (mpath + "\\data\\MYMSDE_Log.LDF\'"))));
cmd.CommandText = ssql;
cmd.ExecuteScalar();
Finally here is the code to start the SQL Database Engine
VB
Public Function StartUPSVC(ByVal s As String) As Boolean
Dim controllers As ServiceController() = ServiceController.GetServices
Dim nNum As Integer = controllers.Length
Dim i As Integer
Try
For i = 0 To nNum - 1
If controllers(i).ServiceName.ToUpper = s.ToUpper Then
If controllers(i).Status <> ServiceControllerStatus.Running Then
If controllers(i).Status <> ServiceControllerStatus.Running Then
Application.DoEvents()
controllers(i).Start()
End If
controllers(i).WaitForStatus(ServiceControllerStatus.Running)
Application.DoEvents()
End If
Return True
End If
Application.DoEvents()
Next
Catch exc As Exception
MsgBox("Microsoft Database Engine could not start, please contact your software vendor")
Return False
End Try
End Function
C#
public bool StartUPSVC(string s) {
ServiceController[] controllers = ServiceController.GetServices;
int nNum = controllers.Length;
int i;
try {
for (i = 0; (i
<= (nNum - 1)); i++) {
if ((controllers[i].ServiceName.ToUpper == s.ToUpper)) {
if ((controllers[i].Status != ServiceControllerStatus.Running)) {
if ((controllers[i].Status != ServiceControllerStatus.Running)) {
Application.DoEvents();
controllers[i].Start();
}
controllers[i].WaitForStatus(ServiceControllerStatus.Running);
Application.DoEvents();
}
return true;
}
Application.DoEvents();
}
}
catch (Exception exc) {
MsgBox("Microsoft Database Engine could not start, please contact your software vendor");
return false;
}
}