Code to create & connect to a SQL Server database: Whats wrong with it?

Question

I am new to C# & I am trying to programatically create & open a SQL Server database.

I have a ASP.NET webapp I am creating & on page load it should pull some data from the database (if the db doesn't exist, it should be created & populated with default data).

PS: does C#'s System.Data.SqlClient use MySQL or SQLite or something else?

Right now I am unsure if my code correctly creates a SQL Server database & if I connect to it correctly.

Can you tell me if my code is correct & how I could improve it?

UPDATE: Error is

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"}"

I have indicated where in the code below the error occurs.

Creating a SQL Server database:

    // When I run this function no file seems to be created in my project directory?
    // Although there is a ASPNETDB sql database file in my App_Data folder so this maybe it
    public static string DEF_DB_NAME = "mydb.db"; // is this the correct extension?
    private bool populateDbDefData()
    {
        bool res = false;
        SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
        string str = "CREATE DATABASE "+DEF_DB_NAME+" ON PRIMARY " +
            "(NAME = " + DEF_DB_NAME + "_Data, " +
            "FILENAME = " + DEF_DB_NAME + ".mdf', " +
            "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = " + DEF_DB_NAME + "_Log, " +
            "FILENAME = " + DEF_DB_NAME + "Log.ldf', " +
            "SIZE = 1MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)";


        SqlCommand myCommand = new SqlCommand(str, myConn);
        try
        {
            myConn.Open(); // ERROR OCCURS HERE
            myCommand.ExecuteNonQuery();
            insertDefData(myConn);
        }
        catch (System.Exception ex)
        {
            res = false;
        }
        finally
        {
            if (myConn.State == ConnectionState.Open)
                 myConn.Close();
            res = true;
        }

        return res;
    }

Here's my connection to the SQL Server database code: I am pretty sure it fails to connect - if I try to use the variable conn, it says the connection is not open. Which could mean that I either failed to connect or failed to even create the db in the 1st place:

    private bool connect()
    {
        bool res = false;
        try
        {
            conn = new SqlConnection("user id=username;" +
                                     "password=password;" +
                                     "Server=localhost;" +
                                     "Trusted_Connection=yes;" +
                                     "database="+DEF_DB_NAME+"; " +
                                     "connection timeout=30");
            conn.Open();
            return true;
        }
        catch (Exception e)
        {
        }

        return false;
    }

Answer 1

You have probably already got this figured out, but just in case people end up here with the same problem (like I did) here's how I got this working.

Your error is that the SqlConnection is not being opened, because it isn't finding an appropriate server. If you're using the SQL server express edition (as I am) you should set the SqlConnection object like this:

SqlConnection myConn = new SqlConnection("Server=localhost\\SQLEXPRESS;Integrated security=SSPI;database=master;");

Once you resolve that error though, you are going to fail on the next line when you try to execute the query. The "Filename" needs to be separated by single quotes, but you only have one on the end after the extension; you will also need one before.

Also, that is the full physical file path, and it won't use the current directory context, you have to specify a path. Make sure that the location is one which the db server will have access to when it's running, otherwise you will get a SqlException being thrown with an error message along the lines of:

Directory lookup for the file "...\filename.mdf" failed with the operating system error 5 (Access is denied). CREATE DATABASE failed. Some file names listed could not be created.

The code which I ended up using looks like this:

public static string DB_NAME = "mydb"; //you don't need an extension here, this is the db name not a filename
public static string DB_PATH = "C:\\data\\";

public bool CreateDatabase()
{
    bool stat=true;
    string sqlCreateDBQuery;
    SqlConnection myConn = new SqlConnection("Server=localhost\\SQLEXPRESS;Integrated security=SSPI;database=master;");

    sqlCreateDBQuery = " CREATE DATABASE "
                        + DB_NAME
                        + " ON PRIMARY "
                        + " (NAME = " + DB_NAME + "_Data, "
                        + " FILENAME = '" + DB_PATH + DB_NAME + ".mdf', "
                        + " SIZE = 2MB,"
                        + " FILEGROWTH = 10%) "
                        + " LOG ON (NAME =" + DB_NAME + "_Log, "
                        + " FILENAME = '" + DB_PATH + DB_NAME + "Log.ldf', "
                        + " SIZE = 1MB, "
                        + " FILEGROWTH = 10%) ";

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

Answer 2

Use this code,

internal class CommonData
  {
    private static SqlConnection conn;

    public static SqlConnection Connection
    {
        get { return conn; }
    }

    public static void ReadyConnection()
    {
        conn = new SqlConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
    }

    public static int ExecuteNonQuery(SqlCommand command)
    {
        try
        {
            ReadyConnection();
            command.Connection = conn;
            int result = command.ExecuteNonQuery();
            return result;
        }
        catch (Exception ex)
        {
            throw ex;
        }

        finally
        {
            command.Dispose();
            if (conn.State == ConnectionState.Open) conn.Close();
            conn.Dispose();
        }
    }

    public static SqlDataReader ExecuteReader(SqlCommand command)
    {
        try
        {
            ReadyConnection();
            command.Connection = conn;
            SqlDataReader result = command.ExecuteReader(CommandBehavior.CloseConnection);
            return result;
        }
        catch (Exception Ex)
        {

            throw Ex;
        }
    }

    public static object ExecuteScalar(SqlCommand command)
    {
        try
        {
            ReadyConnection();
            command.Connection = conn;

            object value = command.ExecuteScalar();
            if (value is DBNull)
            {
                return default(decimal);
            }
            else
            {
                return value;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    public static void ClearPool()
    {
        SqlConnection.ClearAllPools();
    }
}

Answer 3

PS: does C#'s System.Data.SqlClient use MySQL or SQLite or something else?

MySQL provides their own C# dll for connecting to their database, as do the majority of other database manufacturers. I recommend using theirs. I typically using the built-in SQL client for MS SQL (I am not aware if it can be used with other DBs).

As for this line: insertDefData(myConn) -> the method is not included in your code sample.

As for SQL debugging in general, use the GUI for debugging. I know a lot of people who grew up on MySQL do not want to, or do not understand why you should use one, but it's really a good idea. If you are connecting to MySQL, I recommend MySQL WorkBench CE; if you are connecting to a MS database, SQL Management Studio is what you want. For others, GUIs should be available. The idea here is that you can selectively highlight sections of your query and run it, something not available via command-line. You can have several queries, and only highlight the ones you want to run. Plus, exploring the RDBMS is easier via a GUI.

And if you want to prevent a SQL injection attack, just Base64 encode the string data going in.

As for the connection string itself, we will need some more data on what type of database, exactly, you are trying to connect to. Personally, I recommend just creating a separate SQL account to handle operations (easier to track, and you only need to give it permissions to what you want it to access; security and all that).