Wednesday, August 11, 2010

How to set dynamic connection string to SQLDataSource

There are some occations where we cant have fixed connection string in the web.config but we have to utilize username and password from the current context to login to database server. So such a case having SqlDataSource declared declaratively in <%$ ... %>,  best option to attach an on-init event handler to SqlDataSource.
WebConfig.
<connectionStrings>
  <clear/>
  <add name="LocalTestSqlServer" 
        connectionString="Data Source=CHARITH; Initial Catalog=Test; User ID={0}; Password={1}"/>
</connectionStrings>

Consuming Page:
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
 <script runat="server">
        protected void SetCredentials(object sender, EventArgs e)
        {
            SqlDataSource sdt = sender as SqlDataSource;
            sdt.ConnectionString = string.Format(sdt.ConnectionString, "UserName""Password");
        }
 </script>
</head>
<body>
 <form id="form1" runat="server">                
        <asp:SqlDataSource 
            ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>" 
            ID="SqlDataSource2" 
            OnInit="SetCredentials"
            runat="server" />
 </form>
</body>
</html>

2 comments:

Anonymous said...

Thanks very much! I was looking for another way to do this and also found this solution:

http://deepinthecode.com/2012/05/24/dynamically-set-connectionstring-on-sqldatasource-asp-net-control/

Anonymous said...

Perfect !!!!!!!!

Thanks.

Evandro

iPhone Launch Screen Sizes

iPhone Portrait iOS 8 Retina HT 5.5 = 1242 X 2208 Retna HD 4.7 = 750 X 1134 iPhone Landscape iOS 8 Retina HD 5.5  2208 X 1242 iPho...