Create Database in SQL Server with C# question

AuntPeggy

AuntPeggy

Final Approach
PoA Supporter
Joined
May 23, 2006
Messages
8,479
Location
Oklahoma
Display Name
Display name:
Namaste
I am trying to use C# to create a SQL Server database. I have not yet figured out what the connectionstring should be. Have failed using a variety of ideas.

Here is the code I'm trying:
Code: 
System.Data.SqlClient.SqlConnection tmpConn;
tmpConn = new SqlConnection();
tmpConn.ConnectionString
       = "SERVER = COMPAQ330; "
       + "DATABASE = SQLEXPRESS.master; "
       ;
string sqlCreateDBQuery;
sqlCreateDBQuery = " CREATE DATABASE"
       + " PeggyTest"
       ;
SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery,        tmpConn);
try
{
       [B]tmpConn.Open();[/B]
       MessageBox.Show(sqlCreateDBQuery);
       myCommand.ExecuteNonQuery();
       MessageBox.Show("Database has been created successfully!",
           "Create Database", MessageBoxButtons.OK,
           MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
       MessageBox.Show(ex.ToString(), "Create Database",
              MessageBoxButtons.OK,
              MessageBoxIcon.Information);
}
finally
{
       tmpConn.Close();
}

I have tried providing "localserver" in place of "Compaq330" and "master" in place of "SQLEXPRESS.master".
The database does not get created.
The line in bold throws this exception:
Code: 
System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at File_Browser.Class1.myDatabase() in C:\Documents and Settings\Owner\My Documents\Visual Studio 2008\Projects\File Browser\File Browser\Class1.cs:line 35

This connectionstring works fine for an already existing database:
Code: 
SqlConnection conn = new SqlConnection(
 "Data Source=.\\SQLEXPRESS;"
 + "AttachDbFilename=C:\\Northwind\\NORTHWND.MDF;"
 + "Integrated Security=True;"
 + "Connect Timeout=30;"
 + "User Instance=True");
Any assistance would be appreciated.
 
Peggy, I don't work in MSSQL, but doesn't the create string have to specify that it's a new server connection, something like

Code: 
 SqlConnection connection =
     new SqlConnection(connectionString);
Server server =
     new Server(new ServerConnection(connection));

or something along those lines?

Rich
 
RJM62 said:
Peggy, I don't work in MSSQL, but doesn't the create string have to specify that it's a new server connection, something like

Code: 
 SqlConnection connection =
     new SqlConnection(connectionString);
Server server =
     new Server(new ServerConnection(connection));

or something along those lines?

Rich
Click to expand...
Examples I've seen don't instantiate Server. Also, this code on an existing database works without Server as well.
Code: 
SqlConnection conn = new SqlConnection(
 "Data Source=.\\SQLEXPRESS;"
 + "AttachDbFilename=C:\\Northwind\\NORTHWND.MDF;"
 + "Integrated Security=True;"
 + "Connect Timeout=30;"
 + "User Instance=True");
SqlDataReader myReader = null;
try
{
 conn.Open();
 SqlCommand cmd = new SqlCommand("Select * from Customers", conn);
 myReader = cmd.ExecuteReader();
 while (myReader.Read())
 {
     Console.WriteLine(myReader[3]);
 }
}
finally
{
 if (myReader != null)
 {
     myReader.Close();
 }
 if (conn != null)
 {
     conn.Close();
 }
}
 
AuntPeggy said:
...

I have tried providing "localserver" in place of "Compaq330" and "master" in place of "SQLEXPRESS.master".
The database does not get created. ...
Click to expand...

From total ignorance here....

Wouldn't you want "localhost" or 127.0.0.1 ?

I'm going nuts trying to get basic stuff working in Excel VBA these days. What a mess the syntax and the IDE is.
 
may be a simple question...but are you sure that the user you're using has rights to create a database? That's a permission level that isn't always turned on by default.
 
mikea said:
From total ignorance here....

Wouldn't you want "localhost" or 127.0.0.1 ?

I'm going nuts trying to get basic stuff working in Excel VBA these days. What a mess the syntax and the IDE is.
Click to expand...
I meant "localhost". I sure hope that is what I put into the code.

Yes, the IDE gets really tricky when you want to go beyond "Hello, World!"
 
TangoWhiskey said:
And questions like this is why this is one of my favorite "cheat" sites:

http://www.connectionstrings.com
Click to expand...
Thanks! This works.
Code: 
[SIZE=2][SIZE=2]tmpConn.ConnectionString
    = [/SIZE][SIZE=2][COLOR=#a31515]"SERVER = .\\SQLExpress; "
[/COLOR][/SIZE][SIZE=2]    + [/SIZE][SIZE=2][COLOR=#a31515]"Database = master; "
[/COLOR][/SIZE][SIZE=2]    + [/SIZE][SIZE=2][COLOR=#a31515]"Trusted_Connection = yes; "
[/COLOR][/SIZE][SIZE=2]    ;
[/SIZE][/SIZE]
 
AuntPeggy said:
Thanks! This works.
Code: 
[SIZE=2][SIZE=2]tmpConn.ConnectionString
    = [/SIZE][SIZE=2][COLOR=#a31515]"SERVER = .\\SQLExpress; "
[/COLOR][/SIZE][SIZE=2]    + [/SIZE][SIZE=2][COLOR=#a31515]"Database = master; "
[/COLOR][/SIZE][SIZE=2]    + [/SIZE][SIZE=2][COLOR=#a31515]"Trusted_Connection = yes; "
[/COLOR][/SIZE][SIZE=2]    ;
[/SIZE][/SIZE]
Click to expand...

You're welcome!!

That ("Trusted_Connecton=yes") will work on YOUR machine because it's using your NT Login credentials to authenticate access to the database. If the code is only going to run on your machine that's fine. If you're going to let other people run the code, or push it to a website, you'll need to change the connection string to use a specific username/pw, or make sure the application runs in the context of a named user that also has permissions to the database.
 
TangoWhiskey said:
You're welcome!!

That ("Trusted_Connecton=yes") will work on YOUR machine because it's using your NT Login credentials to authenticate access to the database. If the code is only going to run on your machine that's fine. If you're going to let other people run the code, or push it to a website, you'll need to change the connection string to use a specific username/pw, or make sure the application runs in the context of a named user that also has permissions to the database.
Click to expand...
OK. Glad to get it working, but will be sure to add security before handing it over.

Now, any idea how I can specify the path to my database.
 
AuntPeggy said:
OK. Glad to get it working, but will be sure to add security before handing it over.

Now, any idea how I can specify the path to my database.
Click to expand...

Change "Database=master" to "Database=MyDBname" in that connection string, and you'll be referencing your db schema by default in the connection.
 
AuntPeggy said:
OK. Glad to get it working, but will be sure to add security before handing it over.

Now, any idea how I can specify the path to my database.
Click to expand...
I believe that will be part of the create database syntax, not the connection string.
 
Greebo said:
I believe that will be part of the create database syntax, not the connection string.
Click to expand...
Correct. Something like
Code: 
CREATE DATABASE Peggy
ON 
( NAME = Peggy_dat, FILENAME = 'c:\peggydat.mdf')
LOG ON
( NAME = 'Peggy_log', FILENAME = 'c:\peggylog.ldf')
The above may or may not work. But you'll want to do something like that. My .NET/SQL Server experience can be counted in hours not exceeding a single digit.
 
Ditto,

I think that PHP has the easiest to use SQL connector. That's why I hire slave labor -- I mean happy outsourced contract workers to handle DB stuff for windows programs.

~ Christopher
 
jesse said:
Correct. Something like
Code: 
CREATE DATABASE Peggy
ON 
( NAME = Peggy_dat, FILENAME = 'c:\peggydat.mdf')
LOG ON
( NAME = 'Peggy_log', FILENAME = 'c:\peggylog.ldf')
The above may or may not work. But you'll want to do something like that. My .NET/SQL Server experience can be counted in hours not exceeding a single digit.
Click to expand...
Thank you. I've been too happily humming away at code that works, to stop and express my gratitude. Hubby chided me for asking pilots this kind of question, but I just knew this is where the answer would come from.
 
You must log in or register to reply here.
Back
Top