Error creating a SQL database in visual studio

Question

I'm trying to programatically create in C# a db that can create itself. I'm usin a windows form application in visual studio as a base.

The problem when using this code is that it always return false. Do you see any error on this code that can cause the problem?

 public static String DB_NAME = "database name";
 public static string DB_PATH = "database path"; //this path does not have " " in the string.
 public bool CreateDatabase()
    {
        bool stat = true;
        string sqlCreateDBQuery;
        SqlConnection myConn = new SqlConnection("Server=localhost\\(localdb)\\v11.0;Integrated security=SSPI;database=master;");

        sqlCreateDBQuery = " CREATE DATABASE "
                            + DB_NAME
                            + " ON PRIMARY "
                            + " (NAME = " + DB_NAME + "_Data, "
                            + " FILENAME = '" + DB_PATH + DB_NAME + ".mdf', "
                            + " LOG ON (NAME =" + DB_NAME + "_Log, "
                            + " FILENAME = '" + DB_PATH + DB_NAME + "Log.ldf', ";

        SqlCommand myCommand = new SqlCommand(sqlCreateDBQuery, myConn);
        try
        {
            myConn.Open();
            myCommand.ExecuteNonQuery();
        }
        catch (System.Exception e)
        {
            Debug.WriteLine(e.ToString());
            stat =  false;
        }
        finally
        {
            if (myConn.State == ConnectionState.Open)
            {
                myConn.Close();
            }
            myConn.Dispose();
        }
        return stat;
    }

EDIT: reading the comments i have changed the code a bit not swallowing the exception.

the result of getting the Exception is:

 System.Data.SqlClient.SqlException (0x80131904): Error related with the network or specific from the instance  while the server established a connection with the SQL Server. The server was not found or not is not accesible. Check that the name of the instance is correct and the SQL server is configured to admit remote conections. (provider: SQL Network Interfaces, error: 26 - Error searching for server o specified instance)
   in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   in System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
   in System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   in System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   in System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   in System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
   in System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   in System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   in System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   in System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   in System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   in System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   in System.Data.SqlClient.SqlConnection.Open()
   in Contabilidad.Form1.CreateDatabase() en c:\*****\Form1.cs:línea 46
ClientConnectionId:00000000-0000-0000-0000-000000000000

Why don't you come up with the exception's error message. It will help you to find out the cause easily. — Sachin, Oct 12 '14 at 11:37
I have seen a lot of problems in this code. First of them you must not swallow the exception. — Hamlet Hakobyan, Oct 12 '14 at 11:37
It is really bad practice - when you catch exception and return `bool` instead. Exception always carry more information than `bool`. — Diligent Key Presser, Oct 12 '14 at 11:39
What is (LocalDb)? Is this your SQL Server name? it seems that yor code can't find you SQL Server. And this V11.0 which seems to be a path? Please try to connect via SQL Server Management Studio and post a picture. — user3514987, Oct 12 '14 at 12:05
Check this answer http://stackoverflow.com/a/15031548/2224701 — Vojtěch Dohnal, Oct 12 '14 at 13:09

Restro Fad · Accepted Answer · 2014-10-16T12:06:42.253

Thanks to the link VDohnal have posted i found the solution.

Like said in link fist is need to check the .NET Framework version (the version can be check following the instruction of this link http://msdn.microsoft.com/en-us/library/hh925568(v=vs.110).aspx).

One of the problems of my code was the definition of the the files path. The ExecuteQuerycommand() cannot execute the query properly because it cannot read the path correctly (the good version of doing this is the code).

Finnaly reading a comment from user3514987, the ((localdb)\v11.0) is the local databse I have installed, be careful if you installed the express version, the SQL Express (SQLEXPRESS) you have to change a part of the code (specifically myConn) to make this work.

    // your db name
    static string dbName = "myDbName";

    // path to your db files:
    // ensure that the directory exists and you have read write permission.
    string[] files = { Path.Combine(Application.StartupPath, dbName + ".mdf"), 
               Path.Combine(Application.StartupPath, dbName + ".ldf") };
public bool CreateDatabase()
    {
        bool stat = true;
        string sqlCreateDBQuery;
        SqlConnection myConn = new SqlConnection("Server=(localdb)\\v11.0;Integrated Security=true;");


        var query = "CREATE DATABASE " + dbName +
            " ON PRIMARY" +
            " (NAME = " + dbName + "_data," +
            " FILENAME = '" + files[0] + "'," +
            " SIZE = 10MB," +
            " MAXSIZE = 100MB," +
            " FILEGROWTH = 10%)" +

            " LOG ON" +
            " (NAME = " + dbName + "_log," +
            " FILENAME = '" + files[1] + "'," +
            " SIZE = 1MB," +
            " MAXSIZE = 5MB," +
            " FILEGROWTH = 10%)" +
            ";";

        SqlCommand myCommand = new SqlCommand(query, myConn);
        try
        {
            myConn.Open();
            myCommand.ExecuteNonQuery();
        }
        catch (System.Exception e)
        {
            Debug.WriteLine(e.ToString());
            stat =  false;
        }
        finally
        {
            if (myConn.State == ConnectionState.Open)
            {
                myConn.Close();
            }
            myConn.Dispose();
        }
        return stat;
    }

Error creating a SQL database in visual studio

1 Answers1