detecting that a sql server database exists.

[Joesoft11a]

PART 2

I had someone help me with a way to detect is a sql server database exists. If it doesn't then create it and some tables.

How ever after running it after completing some parts of my app. I have learned that it does some what work. When the database hasn't been created it works just fine. So when the app starts for the 1st time it creates the databases and tables. So here's the issue. Every time I run the app when the database and tables have all ready been created. It seems to want to create them again. Here's my code below.

private void MapBase_Load(object sender, EventArgs e)
{
//Loads all the bms Files from the game directory
//ChangedIt = false;
btn_MapLocation.Enabled = false;
catLabel.Text = catName;

// this is for server based, not SQLEXPRESS,
// try this for EXPRESS -> MSSQL$SQLEXPRESS
var serviceName = "MSSQL$SQLEXPRESS";
ServiceController controller = new ServiceController(serviceName);

try
{

if (controller.Status == ServiceControllerStatus.Running)
{
// Check to see if database exists
if (makeSQLdb())
{
MessageBox.Show("Database and tables created.", Application.ProductName);
}
else
this.Close();
}
}
catch (Exception exc)
{
// not found
MessageBox.Show(exc.Message + " Error SQL Server must be ruuning.", Application.ProductName);
this.Close(); return;
}

loadDefaults();
}

public bool makeSQLdb()
{

bool result = false;

try
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS; Initial Catalog=master;Trusted_Connection = true;"))
{
conn.Open();

string cmddb = "SELECT * FROM master.dbo.sysdatabases where name = 'mlhelper'";

using (SqlCommand sqlCmd = new SqlCommand(cmddb, conn))
{
//Create the database 1st.
int nRet = sqlCmd.ExecuteNonQuery();

if (nRet <= 0)
{

using (SqlCommand command = new SqlCommand("CREATE DATABASE mlhelper", conn))
command.ExecuteNonQuery();

conn.ChangeDatabase("mlhelper");

using (SqlCommand comm1 = new SqlCommand("CREATE TABLE games(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,gamename char(50), category char(10))", conn))
{ comm1.ExecuteNonQuery(); }

using (SqlCommand command2 = new SqlCommand("CREATE TABLE bases(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,basename char(50),category char(10));", conn))
{ command2.ExecuteNonQuery(); }

using (SqlCommand command3 = new SqlCommand("CREATE TABLE maps(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,mapname char(50),description char(500),filename char(13),filedate char(13),datedtime timestamp,baseid int,category char(50));", conn))
{ command3.ExecuteNonQuery(); }

using (SqlCommand command4 = new SqlCommand("CREATE TABLE category(id int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,cname char(50));", conn))
{ command4.ExecuteNonQuery(); }

using (SqlCommand command5 = new SqlCommand("INSERT INTO category(cname) VALUES('jointops')", conn))
{ command5.ExecuteNonQuery(); }
conn.Close();
result = true;

}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Creating Error :" + ex.Message + "" + ex.StackTrace.ToString(), Application.ProductName);
this.Close();
}

return result;
}

I just wish I had a way to test this code when I updated it. I couldn't until other parts of the code were updated. So anyone have an idea why the "nRet" doesn't seem to work.







http://www.df-barracks.com Delta Force Barracks
http://www.starfiresoft.com Starfire Software

Continue reading...