How to attach database from winforms using C#

Question

I have written code to check the database is existed or not and If not existed then I am trying to attach the database from my local directory. My code is:

        SqlConnection con = new SqlConnection(@"Data Source=CENSYS08\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");
        con.Open();

        SqlDataAdapter da = new SqlDataAdapter("select name from sys.databases", con);
        DataTable dt = new DataTable();
        da.Fill(dt);

         string[] array = dt
             .AsEnumerable()
             .Select(row => row.Field<string>("Name"))
             .ToArray();

        if(!array.Contains("cstmrDB",StringComparer.OrdinalIgnoreCase))
        {
            SqlCommand cmd = new SqlCommand("sp_attach_db");
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@dbname", "cstmrDB");
            cmd.Parameters.AddWithValue("@filename1", @"C:\Naresh WORK AREA\My Projects\AttachDB\Data\cstmrDB.mdf");
            cmd.ExecuteNonQuery();

        }

And one more thing, I have gave the permission to my local file as this. Event it is throwing an exception as

Unable to open the physical file "C:\Naresh WORK AREA\My Projects\AttachDB\Data\cstmrDB.mdf". Operating system error 2: "2(The system cannot find the file specified.)".

How can I attach my database to sql server.

Answer 1

You should remove spaces in your path use '_' like- C:\Naresh_WORK_AREA\My_Projects\AttachDB\Data\cstmrDB.mdf
If still this problem not resolved try following code. Also you can remove extra step in your code for checking DB name in query.

string query = "select name from sys.databases where name='cstmrDB'";
SqlConnection con = new SqlConnection(@"Data Source=CENSYS08\SQLEXPRESS;Initial Catalog=master;Integrated Security=True");

    SqlDataAdapter da = new SqlDataAdapter(query , con);
    DataTable dt = new DataTable();

con.Open();
    da.Fill(dt);

if(dt.Rows.Count==0)
{
   query=" CREATE DATABASE AdventureWorks2008R2 ON"+ 
   " (FILENAME = 'C:\Naresh_WORK_AREA\My_Projects\AttachDB\Data\cstmrDB.mdf'), "+
   " (FILENAME = 'C:\Naresh_WORK_AREA\My_Projects\AttachDB\Data\cstmrDB.ldf'),"+
       " (FILENAME = 'c:\myFTCatalogs\cstmrDBCat')"+
   " FOR ATTACH;"

   SqlCommand cmd = new SqlCommand(query);
       cmd.Connection = con;
       cmd.ExecuteNonQuery();
}
con.Close();