I am trying to add an SQL database to my project. I have been successful when using an explicit path, but if I use a relative path |DataDirectory|\Data\Database.mdf the console appears to be be writing data to the database, but looking in the server explorer at the database afterwards shows not data. There are a couple of other questions that get close to answering, but scouring Stack, I have been unable to locate an answer that works or comes close to addressing.
EDIT Per the suggestions below I have attempted the fixes, however, just to clarify. The program appears to connect and write to the database as expected, but when I look at the database structure, no table is created, no data is inserted, even though the program runs with no errors
<connectionStrings>
<add name="rData"providerName="System.Data.SqlClient"connectionString="Data Source=(LocalDB)\MSSQLLocalDB; Initial Catalog=TestRecipeDatabase;AttachDbFilename=|DataDirectory|\Data\TestRecipeDatabase.mdf;Integrated Security=True" />
</connectionStrings>
public void connectToDB(string action)
{
var connectionString = ConfigurationManager.ConnectionStrings["rData"].ConnectionString;
Console.WriteLine(connectionString);
SqlConnection conn = new SqlConnection(connectionString);
if (action == "firstRun")
{
conn.Open();
// Creates the Database
try
{
using (SqlCommand createDBTable = new SqlCommand("" +
"CREATE TABLE RecipeStorage" +
"(" +
"rname char(50) NOT NULL, " +
"ringredients text, " +
"rdirections text" +
");", conn))
createDBTable.ExecuteNonQuery();
Console.WriteLine("Created Database");
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
System.Diagnostics.Trace.WriteLine(logTime + " " + e.Message);
}
// Sets the primary key to rname
try
{
using (SqlCommand setPrimaryKey = new SqlCommand("ALTER TABLE RecipeStorage ADD PRIMARY KEY (rname);", conn))
setPrimaryKey.ExecuteNonQuery();
Console.WriteLine("Set Primary Key to Recipe Name");
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
System.Diagnostics.Trace.WriteLine(logTime + " " +e.Message);
}
// Adds an initial recipe
try
{
using (SqlCommand firstLine = new SqlCommand("INSERT INTO RecipeStorage (rname, ringredients, rdirections) VALUES ('espresso', 'Hot water, coffee'," +
"'put coffee through espresso machine with hot water into cup, enjoy');", conn))
firstLine.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
System.Diagnostics.Trace.WriteLine(logTime + " " + e.Message);
}
conn.Close();
}
}
As mentioned above, this totally works when using an explicit path for the data connection C:\User\User\Projects\CSharp\RecipeHandler\Data\TestRecipeDatabase.mdf
But does not work using the relative path. Ideally I would want to be able for this to run on any computer which obviously would not have the database in same place
