Creating a SQL Server Database Programmatically (ADO.NET)
Hi,
My question is related to Mahesh Chang's example on; http://www.c-sharpcorner.com/Code/2002/Mar/CreatingDBProgrammaticallyMCB.asp
Basically what I have been trying in vain is to connect to my SQL Server and create a database. I have followed Mahesh's example in any detail but somehow I keep on getting an error when trying to open the connection:
Listing 2. Creating a SQL Server database.
// This method creates a new SQL Server database
private void CreateDBBtn_Click(object sender, System.EventArgs e)
{
// Create a connection
conn = new SqlConnection(ConnectionString);
// Open the connection
if( conn.State != ConnectionState.Open)
conn.Open(); // !!!!!! ERROR !!!!!!
string sql = "CREATE DATABASE mydb ON PRIMARY"
+"(Name=test_data, filename = 'C:\\mysql\\mydb_data.mdf', size=3,"
+"maxsize=5, filegrowth=10%)log on"
+"(name=mydbb_log, filename='C:\\mysql\\mydb_log.ldf',size=3,"
+"maxsize=20,filegrowth=1)" ;
ExecuteSQLStmt(sql);
}
The error that I get is:
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at SQL_Setup.Module1.createDB() in C:\Documents and Settings\Christian Baliko
\Desktop\SQL_Setup\SQL_Setup\Module1.vb:line 36
at SQL_Setup.Module1.Main() in C:\Documents and Settings\Christian Baliko\Des
ktop\SQL_Setup\SQL_Setup\Module1.vb:line 22
The server does certainly exist and access is also definitely not denied.
Now the big question is WHY do I get this error?
Will I probably have to specify my own connection string instead of
Private string ConnectionString ="Integrated Security=SSPI;" +
"Initial Catalog=; Data Source=localhost;"; ?
Well, I tried this as well, with still no luck...
Changed Mahesh's connection string to
Private string connectionString =
"workstation id=GERICOM;packet size=4096;integrated security=SSPI;data source=GERICOM;persist security info=False;initial catalog=;"
(I retrieved this connection string by using the wizard)
Then if I run the program again it now crashes at;
Listing 1. The ExecuteSQLStmt method.
private void ExecuteSQLStmt(string sql)
{
// Open the connection
if( conn.State == ConnectionState.Open)
conn.Close();
ConnectionString ="Integrated Security=SSPI;" +
"Initial Catalog=mydb;" +
"Data Source=localhost;";
conn.ConnectionString = ConnectionString;
conn.Open(); // !!!!!! ERROR !!!!!!!
cmd = new SqlCommand(sql, conn);
try
{
cmd.ExecuteNonQuery();
}
catch(SqlException ae)
{
MessageBox.Show(ae.Message.ToString());
}
}
And the error is;
System.Data.SqlClient.SqlException: Cannot open database requested in login 'myd
b'. Login fails.
Login failed for user 'GERICOM\Christian Baliko'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at SQL_Setup.Module1.ExecuteSQLStmt(String sql) in C:\Documents and Settings\
Christian Baliko\Desktop\SQL_Setup\SQL_Setup\Module1.vb:line 53
at SQL_Setup.Module1.createDB() in C:\Documents and Settings\Christian Baliko
\Desktop\SQL_Setup\SQL_Setup\Module1.vb:line 40
at SQL_Setup.Module1.Main() in C:\Documents and Settings\Christian Baliko\Des
ktop\SQL_Setup\SQL_Setup\Module1.vb:line 22
It seems like the database that was suppose to be created in CreateDB() cannot be opened. But if the connection string is faulty why didnt it crash already in CreateDB() instead of ExecuteSQLStmt() ?
Any advice, hints or tips regarding how to solve this issue would be
much appeciated!!!
Thanks,
Chris