自定义嵌入连接字符串中的数据源变量

Question

如何自定义调用连接字符串的数据源，现有连接字符串存储在web.config文件中。我正在使用OLEDB连接到上传到服务器的Excel表。

我在web.config文件中有我的连接字符串。早些时候，我正在努力编码整个字符串，包括Web.config文件中的数据源位置。

//this one works
<add name="testexcel1" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:usethisWebApplication1Uploaded Filesweekly output target.xlsx;Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1;MAXSCANROWS=0'"/>
//I would like to specify the data source parameter in my code behind file.
   <add name="testexcel" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No'"/>
//this is what I am trying to do, but it just does not work, the string gets concatenated wrongly.
string fileLocation = Server.MapPath("~/Uploaded Files/" + filename);
builder.Provider = ConfigurationManager.ConnectionStrings["testexcel1"].ConnectionString;
        builder.DataSource = accept_filelocation;
//This is finally working
string connectionStringProvider =
ConfigurationManager.ConnectionStrings["testexcel1"].ConnectionString;
        string dataSource = accept_filelocation;
        string connectionString = String.Format(connectionStringProvider, dataSource);

        OleDbConnection oledbConn = new OleDbConnection(connectionString);

Answer 1

如果我正确理解您，则要读取配置中的连接字符串格式（包括{0}），然后将其应用于给定的数据源：

string connectionStringFormat = 
    ConfigurationManager.ConnectionStrings["testexcel1"].ConnectionString;
string dataSource = Server.MapPath("~/Uploaded Files/" + filename);
string connectionString = String.Format(connectionStringFormat, dataSource);
var builder = new OleDbConnectionStringBuilder(connectionString);