SQL Server Using old school ASP.NET / C# backend with web.config - I need a dynamic connection string

pgpifvop 于 2023-10-15 发布在 .NET

关注(0)|答案(3)|浏览(126)

I have the following connection string in my web.config :

<add name="ConvMapping.Properties.Settings.ifl" 
     connectionString="Data Source=serverInfo;Initial Catalog=database;Persist Security Info=True;User ID=userid;Password=password" 
     providerName="System.Data.SqlClient" />`

The issue is now we have to use the database based on a selection. So, example I want to do something like:

Data Source=serverInfo;Initial Catalog=Grant_database;Persist Security Info=True;User ID=userid;Password=password

Data Source=serverInfo;Initial Catalog=Wilson_database;Persist Security Info=True;User ID=userid;Password=password

where all databases will be named in the format County_database .

In the ASP.NET, in order to get this string now, I do the following:

ConnectionString="<%$ ConnectionStrings:ConvMapping.Properties.Settings.codes %>"

Another option is to add my connection strings in web.config for all possible instances, but then I'm not sure how to do the logic in the inline or code-behind areas to call the appropriate connection string, and initialize it as such.

One way I was thinking is to do a code behind switch-a-roo...

In the web config do:

Data Source=serverInfo;Initial Catalog={0}_database;Persist Security Info=True;User ID=userid;Password=password

Then in the .aspx do something like:

ConnectionString="<%# GetConnectionString() %>"

Then finally in the code behind do something like:

protected string GetConnectionString()
{
    var databaseName = string.Format(Properties.Settings.Default.codes, Request.QueryString["County"]);
    
    return databaseName ;
}

The issue is that I get the following error:

The ConnectionString property has not been initialized

Any help on this is greatly appreciated.

I'm using the following technologies: C#, ASP.NET, SQL Server and the program is about 13 years old so a bit antiquated.

sql-server

来源：https://stackoverflow.com/questions/77098541/using-old-school-asp-net-c-sharp-backend-with-web-config-i-need-a-dynamic-co

3条答案

按热度按时间

xesrikrc1#

The string.Format() plan is good, as long as you're not allowing users to type in arbitrary text for this. The problem is the so-called "bee-sting" :

<%#

That means to use Data-Binding, similar to an Eval() expression, and like the other bee stings happens far too late in the page life cycle.

Instead, you will want to omit the ConnectionString attribute from the .aspx file completely, and set the connection string for the data source as part of the code behind.

So if you currently have a datasource like this:

<asp:sqldatasource id="SqlDataSource1"
  SelectCommand="SELECT ... FROM ..."
  ConnectionString="<%$ ConnectionStrings:ConvMapping.Properties.Settings.codes %>" 
  runat="server"/>

You would instead remove the ConnectionString attribute completely:

<asp:sqldatasource id="SqlDataSource1"
  SelectCommand="SELECT ... FROM ..."
  runat="server"/>

And then in the code-behind your page_load would include this:

SqlDataSource1.ConnectionString =  string.Format(Properties.Settings.Default.codes, Request.QueryString["County"]);

You might also be able to use an expression tag ( <%$ ) to call the GetConnectionString() method, but it's been too long since I've used the old web forms for me to know for sure.

赞(0）回复(0）举报 2023-10-15

5w9g7ksd2#

if you have many connectionstrings I recommend you use this template in web.config.

<configuration>
  <startup>
    ...
  </startup>
  <appSettings>
   ...
  </appSettings>
  
  <connectionStrings>
    <add name="conn1" connectionString="Data Source=...;Initial Catalog=...;Uid=...;Pwd=...;..." />
    <add name="conn2" connectionString="Data Source=...;Initial Catalog=...;Uid=...;Pwd=...;..." />
  </connectionStrings>
...
</configuration>

And you can get them like this

using System.Configuration;
var conn1 = ConfigurationManager.ConnectionStrings["conn1"].ConnectionString;
var conn2 = ConfigurationManager.ConnectionStrings["conn2"].ConnectionString;

赞(0）回复(0）举报 2023-10-15

tuwxkamq3#

This is how I ended up fixing the problem.

I put the DDL into the OnRowDataBound. From there I was able to load up the DropDownList.

if (e.Row.RowType == DataControlRowType.DataRow && GridView1.EditIndex == e.Row.RowIndex)
        {
            string query = "select a.code as NewActivityTypeID, a.name as NewActivityTypeDesc from code.activitytype a where deletedon is null";

            DropDownList DropDownList1 = (DropDownList)e.Row.FindControl("DropDownList1");
            DropDownList1.DataSource = GetDropDownInfo(query);
            DropDownList1.DataValueField = "NewActivityTypeID";
            DropDownList1.DataTextField = "NewActivityTypeDesc";
            DropDownList1.DataBind();
            DropDownList1.Items.Insert(0, new ListItem(string.Empty, string.Empty));
}

My *.aspx page looks like this:

<asp:TemplateField HeaderText="NewActivityTypeID" SortExpression="NewActivityTypeID">
                    <EditItemTemplate>
                        <asp:DropDownList ID="DropDownList1" runat="server" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged" AutoPostBack="true">
                        </asp:DropDownList>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblNewActivityTypeID" runat="server" SortExpression="Label1" Text='<%# Bind("NewActivityTypeID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Using old school ASP.NET / C# backend with web.config - I need a dynamic connection string

3条答案

相关问题

热门标签

最新问答