locked
How to get the connection string in aspx page RRS feed

  • Question

  • Hi all

    I managed to successfully deploy the beta version of my LS application on Azure and everything seems to work fine except one feature.

    The application stores documents (Excel, pdf, word) in the database on different tables. The application has Upload, Save and View command in various screen. The UPLOAD takes a file specified by the user from his computer and loads it into the database. The SAVE asks the user for a location and will save the file on the user computer.

    The VIEW button should open the file directly in the browser. it works fine on my web server, but reports an error when executed from Azure (same data).

    This feature uses a custom aspx page without any visual elements (code below). Azure reports an error but says it can't show the error for security reason. It suggest that I add a tag in web.config, which I did, but no change.

    <configuration>
        <system.web>
            <customErrors mode="Off"/>
        </system.web>
    </configuration>
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
            ' This is actually to VIEW document in Browser.
    
            If Request.QueryString("id") IsNot Nothing And Request.QueryString("Type") IsNot Nothing _
               AndAlso (Request.QueryString("Type") = "REQ" Or (Request.QueryString("Type") = "FORM" And Request.QueryString("LN") IsNot Nothing)) Then
    
                Dim wSql As String
    
                Using connection As New SqlConnection()
                    connection.ConnectionString = "Data Source=(local)\sqlexpress;Initial Catalog=HRFlow_Dev2;User ID=HRFlow_user;Password=Passw0rd!"
                    connection.Open()
    
                    If Request.QueryString("Type") = "REQ" Then
                        wSql = "select  RQF_NAME,RQF_DOC from tbd_RQF_RequestFile where REQ_ID = " & Request.QueryString("id")
                    ElseIf Request.QueryString("ln") = "EN" Then
                        wSql = "Select FRM_FILE_EN, FRF_DOC_EN from tbd_FRM_Form FRM inner join tbd_FRF_FormFile FRF on FRF.FRM_ID = FRM.FRM_ID where FRM.FRM_ID = " & Request.QueryString("id")
                    Else
                        wSql = "Select FRM_FILE_FR, FRF_DOC_FR from tbd_FRM_Form FRM inner join tbd_FRF_FormFile FRF on FRF.FRM_ID = FRM.FRM_ID where FRM.FRM_ID = " & Request.QueryString("id")
                    End If
    
                    Dim command As New SqlCommand(wSql, connection)
                    Dim reader As SqlClient.SqlDataReader = command.ExecuteReader()
    
                    If reader.HasRows() Then
                        reader.Read()
    
                        If Not reader.IsDBNull(0) AndAlso Not reader.IsDBNull(1) Then
                            Dim wSqlbuffer As SqlBytes
                            wSqlbuffer = reader.GetSqlBytes(1)
    
                            Dim wBuffer() As Byte
                            wBuffer = wSqlbuffer.Value
    
                            Dim wFilename = reader.GetSqlString(0).ToString()
                            Dim wExtension = wFilename.Substring(wFilename.LastIndexOf(".") + 1).ToLower()
    
    
                            Dim response = HttpContext.Current.Response
                            response.Clear()
                            response.ClearContent()
                            response.ClearHeaders()
                            response.Buffer = True
                            Select Case wExtension
                                Case "pdf"
                                    response.ContentType = "application/pdf"
    
                                Case "xls", "xlsx", "xlsm"
                                    response.ContentType = "application/vnd.ms-excel"
    
                                Case "doc", "docx", "dot"
                                    response.ContentType = "application/msword"
    
                                Case Else
                                    response.ContentType = "application/octet-stream"
    
                            End Select
    
    
                            response.AddHeader("Content-Disposition", "inline;filename=""" + wFilename + """")
                            response.BinaryWrite(wBuffer)
    
                            response.End()
    
                        End If
                        reader.Close()
                        connection.Close()
                    End If
    
                End Using
            End If
        End Sub

    As you can see, my connection string is hardcoded (not good).  I could change the value, but then the application will not work properly in debug mode...

    I see two alternatives:

    1- Get the current connection string from where it is store (I could not find where it is)

    2- have some test (?)  to detect if I am running on Azure or in local server and set the connection string accordingly.

    Side question, how can I get the actual error message from Azure?

    Thanks

    PS: I am not an aspx expert, any suggestions to improve this code is also welcome.

    Thursday, February 21, 2013 4:35 PM

Answers

  • Connection strings are stored in the web.config file. You can get the connection string by calling:

    WebConfigurationManager.ConnectionStrings(dataService).ConnectionString
    

    where dataService is:

    • If it's the intrinsic data source (the one that LightSwitch created for you): "_IntrinsicData"
    • If it's an attached data source, is that name of the data source under the DataSources node in Solution Explorer.

    This should work regardless of you debugging the project at design time or if the application is deployed.


    Justin Anderson, LightSwitch Development Team

    • Marked as answer by Ragoran Tuesday, February 26, 2013 2:04 AM
    Friday, February 22, 2013 1:18 AM
    Moderator

All replies

  • Hi Ragoran,

    The only thing I can think of is that Azure mime types are not set on its instance of IIS, which is not serving the correct content types. Are you seeing the correct content type headers coming through? Are any of them working? Can you try and serve a .txt file and see if that is coming up?

    -Pierson

    Thursday, February 21, 2013 9:08 PM
    Moderator
  • Thanks for the reply.

    I may not have been clear enough in my question. The problem is that the page is trying to connect to my local database instead of the Azure one that is currently used by the application. It does so because the connection string is hardcoded in the logic.

    To retest the page, I change the connection string to the correct one and the VIEW features works fine. However, I don't ewant to harcode the connection string in the program.

    So my question is how can I get the connection string from a config file from within the aspx page?

    Thursday, February 21, 2013 9:53 PM
  • Hi Ragoran,

    On the Publish Wizard's Data Connections step, there's a second tab "Attached Data Sources" that you can update with your Azure info so that when it publishes, it updates the connection string for you.

    Hope that fixes the problem.

    -Pierson

    Thursday, February 21, 2013 10:06 PM
    Moderator
  • Connection strings are stored in the web.config file. You can get the connection string by calling:

    WebConfigurationManager.ConnectionStrings(dataService).ConnectionString
    

    where dataService is:

    • If it's the intrinsic data source (the one that LightSwitch created for you): "_IntrinsicData"
    • If it's an attached data source, is that name of the data source under the DataSources node in Solution Explorer.

    This should work regardless of you debugging the project at design time or if the application is deployed.


    Justin Anderson, LightSwitch Development Team

    • Marked as answer by Ragoran Tuesday, February 26, 2013 2:04 AM
    Friday, February 22, 2013 1:18 AM
    Moderator
  • Justin

    Thanks, It works fine after I imported system.web.configuration.

    (VS2012 is cool, it suggests which asembly is missing! yeah!)

    Tuesday, February 26, 2013 2:05 AM