Dynamic Datasource Path C#

Question

I created an app where a combobox is bound with an Access Database. The application was working fine on my computer because the source path in the connectionstring I defined was related to my computer. I copied the project folder to another computer which gives error of not finding the database at the specified location.

Can I dynamically set the path from a textbox or some other input? Can I call a database from the application where the source path doesn't matter. Even when I refer to Resources.Database1 it still gives full path to application folder for my computer which doesn't work on another computer. Any idea would be appreciated. Thanks!

My code is the following:

private void button1_Click(object sender, EventArgs e)
{
    OleDbCommand command = new OleDbCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "INSERT INTO SubrubDatabaseT(SuburbName,DeliveryTime) values('" + textBox1.Text + "','" + textBox2.Text + "')";
    OleDbConnection connect = new OleDbConnection();
    connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\WoolsValley\Desktop\Database1.accdb ;
    Persist Security Info = False; ";
    connect.Open();
    command.Connection = connect;
    command.ExecuteNonQuery();
    MessageBox.Show("Data Saved Successfully");
    connect.Close();   
}

Specify a relative path to the database instead of an absolute path. — Bradley Uffner, Oct 24 '16 at 17:44
Of course you could specify it with some sort of input (like a `TextBox`). What has stopped you from trying that? But you should probably use a relative path like @BradleyUffner suggested above, possible combined with doing this in the app.config so you don't need to recompile if the string needs to change. — Broots Waymb, Oct 24 '16 at 17:53

score 0 · Answer 1 · edited May 23 '17 at 10:27

For your case, the solution is to use an App.config (or Web.config depending on the type of the project you are developing) file and put all the settings there. Then if the path doesn't exist, you can still change it to an existing one in this file, and it will not be necessary to recompile the application.

This is the main use of these lind of files, to add there any settings that could change on the users machines or when the application is published and it may need little adjustments, as in this case, and it may not be needed to recompile for every computer the application runs.

score 0 · Answer 2 · answered Oct 24 '16 at 17:51

As you mentioned yourself, you can simply have a text box for the file path or maybe an OpenFileDialog component to select the file. You then pass that in the connection string:

//GetFileSource() a method that gets the source from somewhere, like a textbox or a configuration entry in app.config.
var fileSource = GetFileSource(); 
connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileSource + ";Persist Security Info = False;";

What's equally important is that you make sure the file actually exists at the destination.

score 0 · Answer 3 · edited Sep 22 '17 at 02:27

You could create a method that searches the users folder for data files or let the user specify a data file on startup and save that to the users profile directory. There are many ways to do this and which way you choose depends on factors like how is the data file copied to the users PC, are there many data files possible, etc.

If the data file only exists with the app, there is only one data file possible, and the data file is copied/created at deployment time (ie. when you run the MSI) then put it in the app.config instead as a connection element. See Connection Strings and Configuration Files.

First thing is to always use parameterized queries. See https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters(v=vs.110).aspx

Remarks

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

Your code refactored with using statements and parameterized inputs.

// get file from the drop down
var filePath = getSelectedDataFile();

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath))
using (OleDbCommand cmd = new OleDbCommand())
{
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "INSERT INTO SubrubDatabaseT(SuburbName,DeliveryTime) values(?,?)"; 

    cmd.Parameters.Add(new OleDbParameter("@suburbName", OleDbType.VarChar)).Value = textBox1.Text;
    cmd.Parameters.Add(new OleDbParameter("@deliveryTime", OleDbType.VarChar)).Value = textBox2.Text;

    con.Open();
    cmd.ExecuteNonQuery();
}

Note that:

OleConnection and OleDbCommand are wrapped in using blocks so they are disposed/cleaned up even when an exception occurs.
Parameters are now used instead of hard coding the string values
Parameters use the correct data types

Dynamic Datasource Path C#

3 Answers3

Remarks