Unable to populate DataGridView from solution explorer DataBase

Question

Question background:

I have developed a WinForm app and am now at the stage of wishing to publish it.

I have an SQL server database that I have added into a folder of the solution explorer as I want the database to be deployed within the app, as shown;

Within this DataSet I have a number of stored procedures added.

The issue:

When I debug the app, supplying the following connection string in the code snippet the DataGridView is not populated.

string connectionString = @"Data Source=.;Integrated Security=True;AttachDbFilename=|DataDirectory|\testDB.mdf";**

public override DataSet FillDataGrid()
{

    SqlConnection connection = new SqlConnection(connectionString);
    SqlDataAdapter dataadapter = new SqlDataAdapter("spListAll", connection);
    DataSet ds = new DataSet();
    connection.Open();
    dataadapter.Fill(ds, "results");
    connection.Close();

    int size = ds.Tables[0].Rows.Count;

    return ds;
}

If I supply the connection string from a Database stored locally on my machine with the connection string as follows, it works perfectly populating the grid.

string connectionString = @"Data Source=DAVE-PC\FINAWARE; Initial Catalog=testDB; Integrated Security=SSPI;";

Can anyone tell me how I should be correctly setting this up so I can use the testDB.mdf in the solution explorer folder?

Answer 1

Apparently your DataSource is not valid.

Others had the same problem as you and used .\SQLEXPRESS as a DataSource.

Seems you also need to add User Instance = true in your connection string more documentation

Here for a similar problem How do I connect to an MDF database file?

Answer 2

Of course I am assuming that, whatever the machine from which you run the application, SQL Server was previously installed. (Or at least you have installed the LOCALDB version of Sql Express 2012)

Said that, you should keep in mind that the DataDirectory substitution string is resolved in different ways depending where your app is running.

In a debug session under Visual Studio (Winforms app) the DataDirectory resolves to the PROJECTFOLDER\BIN\DEBUG and the database file should be there (To ensure this you could set the property of the MDF file Copy To The Output Directory to Copy Always or If Newer)

In a production machine, the DataDirectory (always for winforms app), is located in the same folder where you install your application and this is an obvious problem for a database app if you install your application in a special read only folder like C:\program files (x86)

Luckily, it is possible to change change the location of the DataDirectory substitution string. Before trying to use any data access related code, add these lines

string dataPath = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData);
dataPath = Path.Combiner(dataPath, "MyAppDataFolder");
AppDomain.CurrentDomain.SetData("DataDirectory", dataPath);

Usually the installation package should create the MyAppDataFolder in the CommonApplicationData folder and copy there the MDF file. This is recommended because you have there read/write permissions and other users of the same machine could use your application

As a side note, I see another problem in how you call the stored procedure (though I can't explain why it works if you change the connectionstring). You should tell the adapter that you are using a stored procedure setting the

SqlDataAdapter dataadapter = new SqlDataAdapter("spListAll", connection);
dataadapter.SelectCommand.CommandType = CommandType.StoredProcedure;