current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.3 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

I have got a .MDF file at specific location on which I want to run .sql file. I am using following code but it doesn't do it very well.

it always gives me error that failed to connect to the server and cannot read the physical file!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace Testing
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    private void btnCreateDatabase_Click(object sender, EventArgs e)
    {
        string dir = GetParentPath() + "\\Database";  // folder location
        if (!Directory.Exists(dir))
            Directory.CreateDirectory(dir);

        File.Create(GetParentPath() + "\\Database\\test_db.mdf");

        try
        {
            string sqlConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + GetParentPath() + "\\Database\\test_db.mdf" + ";Integrated Security=SSPI;User Instance=true";

            FileInfo file = new FileInfo(GetParentPath() + "\\test_db.MDF.sql");

            string script = file.OpenText().ReadToEnd();

            SqlConnection conn = new SqlConnection(sqlConnectionString);

            Server server = new Server(new ServerConnection(conn));

            server.ConnectionContext.ExecuteNonQuery(script);
            file.OpenText().Close();
            MessageBox.Show("DataBase is Created Successfully", "Create a Database", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }
        catch (System.Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Create a Database", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
        finally
        {
            //if (myConn.State == ConnectionState.Open)
            //{
            //    myConn.Close();
            //}
        }

    }
    private string GetParentPath()
    {
        string DbPath = System.AppDomain.CurrentDomain.BaseDirectory;
        int Posn;
        for (int Counter = 0; Counter != 2; Counter++)
        {
            Posn = DbPath.LastIndexOf("\\");
            DbPath = DbPath.Substring(0, Posn);
        }
        return DbPath;
    }
}

}

share|improve this question

closed as off-topic by Steve, marc_s, Fiona - myaccessible.website, cadrell0, Renan Aug 9 '13 at 17:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist" – Steve, cadrell0, Renan
If this question can be reworded to fit the rules in the help center, please edit the question.
2  
Simply creating a file with MDF extension doesn't make that file a Sql Server Database file. – Steve Aug 8 '13 at 10:02
    
also check the directories you're creating the file in one directory and trying to attach it from another. – Preet Sangha Aug 8 '13 at 10:04
    
@Steve, I am creating the .mdf file before try catch block. And then I am picking up that file to have .sql file executed on it. – user1889838 Aug 8 '13 at 10:04
up vote 3 down vote

creating a file with MDF extension doesn't simply mean that this is a valid db file. And it is giving you error because it isn't a valid db file.

If you want to create database, use this code. Ofcourse change the parameters as you wish.

String str;
    SqlConnection myConn = new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");

    str = "CREATE DATABASE MyDatabase ON PRIMARY " +
        "(NAME = MyDatabase_Data, " +
        "FILENAME = 'C:\\MyDatabaseData.mdf', " +
        "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
        "LOG ON (NAME = MyDatabase_Log, " +
        "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
        "SIZE = 1MB, " +
        "MAXSIZE = 5MB, " +
        "FILEGROWTH = 10%)";

    SqlCommand myCommand = new SqlCommand(str, myConn);
    try 
    {
        myConn.Open();
    myCommand.ExecuteNonQuery();
    MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
    MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    finally
    {
    if (myConn.State == ConnectionState.Open)
    {
        myConn.Close();
    }
    }
share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.