CODEWITHSUNDEEP
sql-serverdatabasec#-2.0
dado
dado

Reputation: 9

Using a SQL Server mdf file easy on any Windows without any authentication

I have a SQL Server .MDF database file that contains data and tables that I need to load into my project and add or update that data so when I attach the file and run my program on a second PC that has SQL Server already installed, I get error that database is not found!

Note1: database was created in SQL Server 2012 local host server and Windows authentication mode.

I am using this code for loading and using database :

SqlConnection c = new SqlConnection(@"Data Source=.;Initial Catalog=db1;Integrated Security=True");

private void Form1_Load(object sender, EventArgs e)
{
    String str;
    SqlConnection myConn = new SqlConnection(@"Data Source=.;Initial Catalog=db1;Integrated Security=True");

    str = "CREATE DATABASE db1";
    SqlCommand myCommand = new SqlCommand(str, myConn);

    try
    {
        myConn.Open();
        myCommand.ExecuteNonQuery();
        MessageBox.Show("First db is Created", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (System.Exception ex)
    {
        // MessageBox.Show("DB is exist");
    }
    finally
    {
        if (myConn.State == ConnectionState.Open)
        {
            myConn.Close();
        }
    }

    using (SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=db1;Integrated Security=True"))
    {
        try
        {
            //Open.the SqlConnection;
            con.Open();

            //The following code uses a SqlCommand based on the SqlConnection
            using (SqlCommand command = new SqlCommand("CREATE TABLE contents(id int IDENTITY(100, 1),Name char(50) NOT NULL,Lastname char(50) NOT NULL,Number char(50) ,Area nvarchar(50) ,Date nvarchar(50)NULL,Duration nvarchar(MAX),description nvarchar(MAX),gender nvarchar(50),number2 nvarchar(50),DT datetime NULL);", con))
                    command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //MessageBox.Show("Tables created");
        }
    }
}

Load table

private void button4_Click(object sender, EventArgs e)
{
    SqlDataAdapter a = new SqlDataAdapter("select * from contents", c);
    DataTable t = new DataTable();
    a.Fill(t);

    dataGridView1.DataSource = t;
    dataGridView1.FirstDisplayedScrollingRowIndex = dataGridView1.RowCount - 1;
    dataGridView1.AutoResizeColumns();
}

But it's not very unique and useful the db will be moved every day to another PC and it must load perfectly also I have some table in SQL file that are static and their is no need to code for them, I want to just use them as resource. Also I heard about some method that embedded or local db can be used as db in app data folder and can be moved with app wisely so I need some help here. Thanks

Upvotes: 0

Views: 412

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

instead of creating a raw db everytime, you can use your mdf file as source,like below

Create database dbname 
On 
(   
Filename= 'path where you copied mdf file' 

)
For attach;

Upvotes: 1

Related Questions