Page Bound to DB HTML??? 1

[tqeonline]

Good Afternoon!

Picture a page that is formatted off a Database Cell that has HTML. Is this possible?

My situation, We are creating automation to run through 40,000 rows of unique data. the Primary Key is 1 to 40,000. My automation will be creating a Log in HTML that I will store in a column on the rows. Now I want to create a page that will pull that HTML and create the page.

So say it runs off the ID passed into the page

[URL unfurl="true"]www.google.com/id=21[/URL]

Then the page takes 21 and does a query to return the HTML and format the page.

Is their a control out there that can be bound to a Database and allow HTML formatting?

I believe that a GridView does... but making a gridview just 1 cell is kind of a crappy way to do this. or is it?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[jmeckley]

html is just text so you could store the html in a nvarchar(max) column. query the database and return the value to httpresponse. in fact, you wouldn't need webforms at all. all you really need is a handler. the generic one would work fine.

class RenderedCachedHtml : IHttpHandler
{
   public void IsReusable { get{ return true;}}

   public void Process(HttpContext context)
   {
       var id = context.Params["id"];
       var text = GetTextFromDatabaseUsing(id);

       context.Response.WriteString(text);
       context.Response.ContentType = "text/html";
   }

   private string GetTextFromDatabaseUsing(string id)
   {
       //your code to query the database
   }
}

then you can access the handler like this

RenderedCachedHtml.ashx?id=101

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[tqeonline]

Ok let me pick your brain a little more. I program in VB, i'm currently in VSTS 2010 building this page and then I get your response. What I have working is a work around but I really like the way you are doing it.

I have an .ASPX page that has a gridview with 1 column that allows HTML. When you visit the page

[URL unfurl="true"]http://...com/edit/ACP/Claims_Log.aspx?id=1[/URL]

It uses the ID and in the page load I have it calling an event

Partial Class ACP_Claims_Log
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim intID As Integer = Request.QueryString("id")
        GetData(intID)
        lblID.text = intID
    End Sub
    Public Sub GetData(ByVal intID As Integer)
        Dim con As New SqlConnection(WebConfigurationManager.ConnectionStrings("QualityCenterConnectionString").ConnectionString)
        Dim da As New SqlDataAdapter("Select [ACP_Claim_Log] From ACP_Temp WHERE [ID] = '" & intID & "'", con)

        Dim dt As New DataTable
        da.Fill(dt)
        dt.TableName = "tblACP"

        Dim dv As New DataView
        dv.Table = dt

        gvClaim_Log.DataSource = dv
        gvClaim_Log.DataBind()
    End Sub

End Class

So on page load it grabs the ID and then querys the db and the Gridview is bound to the result (assumption there will only be 1 result returned).

All I have to do is store HTML in the Column. But I feel that using a gridview this way is not the correct use. Could you give me more detail around your way?

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[jmeckley]

you are correct in that a gridview is not the way to go about this. the gridview is meant to display editable, tabular data. if that's not what you data is, then a gridview is not correct control to bind to.

webforms is a html engine. it takes data (typically from a database) and puts the data (the dynamic part) into the html template (the static part). once this completes webforms writes the result to the response stream. that steam is sent to the client and the client can render the html in the browser.

something else to note. webforms is an html rendering engine. asp.net is an http framework. they are 2 different frameworks with 2 different purposes. webforms is built on top of asp.net, but asp.net does not require webforms. I say this because it's a common misconception that
1. you must use webforms to develop webpage with .net
2. devs often think they are the same thing.

on to your situation...
you already have the html, it's stored in the database. so, for this portion of the application you don't need webforms, because there is nothing dynamic. you are simply returning text in the database to the client.

an IHttpHandler is part of the asp.net framework. a generic httphander (ashx) will automatically be handled by asp.net without any configuration.

you could also build your own handler and then map an extension to the handler in the web.config and IIS, but that is more complicated than it needs to be and I digress...

for this situation I recommend a generic http handler (ashx). add a new generic handler to your web project and name it [tt]RenderedCachedHtml[/tt]. then copy the members from above into the class. here are the key features.

1. the handler expects the key [tt]id[/tt] in the params collection. it can be in the querystring, the form, a cookie the items collection. You could be specific about pulling the value from the querystring, but prefer to abstract where the value comes from. Params is the next best thing.

you will want to add error handling in case the request is missing the id property. you may also want to name the property something a bit more descriptive.

2. Reuse return true. the handler is stateless, therefore asp.net can cache the handler which is a small preformance boost. no single user would notice a difference, but a high traffic site would capitalize on this from an overall preformance stand point.

3. once the text is retrieved from the database write the text to the response stream. the code may not be 100%, but it's the general idea. I also set the content type explicitly. this will tell the client what type of data it is. other options are xml, pain text, json, image, a specific application like word, excel, pdf)

comments on your code. there are number of problems with your data access code.
objects are not properly disposed.
there is no excuse for sql injection. a parameterized query solves many problems.
since the query is just returning a single string you could execute a scalar query.
at a minimum I would change the code to this

var connectionsettings = ConfigurationManager.ConnectionStrings["key in web.config"];
var factory = DbProviderFactories.GetFactory(connectionsettings .ProviderName);
using(var connection = factory.CreateConnection())
{
   connection.ConnectionString = connectionsettings .ConnectionString;
   using(var command = connection.CreateCommand())
   {
       command.CommandType = CommandType.Text;
       command.CommandText = "select [html] from [table] where [id] = @id";
       var parameter = command.CreateParameter();
       parameter.ParameterName = "id";
       parameter.Value = id; //retrieved from the querystring
       command.Parameters.Add(parameter);

       connection.Open();
       return command.ExecuteScalar();
   }
}

for more information on what's happening, and why, read my FAQ on database connection management. the link is in my signature.

...
additional information. not directly related, but may help you understand what a webform is:
webforms (aspx) is another type of http handler, albeit much more complicated. from the [tt]Process(HttpContext)[/tt] member all the objects required for webforms to work. it's at this point a webform http hanlder will parse the Params collection and create the page life cycle. this allows the developer to work with a web page in a similar manner to how a winform works.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[tqeonline]

Awesome. I'm close I can feel it. This is what I have

<%@ WebHandler Language="VB" Class="Claims_Log" %>

Imports System
Imports System.Web
Imports System.Data.Common
Imports System.Data

Public Class Claims_Log : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim id As String = context.Items("id")
        If id = "" Then
            context.Response.Write("<h4>ERROR</h4>Please Provide MDN as ID String When Visiting page<br/><i>ex. [URL unfurl="true"]http://sqadashboard.int.asurion.com/edit/ACP/Claims_Log.ashx?id=1</i>")[/URL]
            context.Response.ContentType = "text/html"
        ElseIf id <> "" Then
            Dim text As String = GetTextFromDatabaseUsing(id)
            context.Response.Write(text)
            context.Response.ContentType = "text/html"
        End If
    End Sub
    
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return True
        End Get
    End Property
    
    Public Sub GetTextFromDatabaseUsing(ByVal ID As String)
        Dim connectionsettings = ConfigurationManager.ConnectionStrings("key in web.config")
        Dim factory = DbProviderFactories.GetFactory(connectionsettings.ProviderName)
        Using connection = factory.CreateConnection()
            connection.ConnectionString = connectionsettings.ConnectionString
            Using command = connection.CreateCommand()
                command.CommandType = CommandType.Text
                command.CommandText = "select [ACP_Claim_Log] from [ACP_Temp] where [id] = @id"
                Dim parameter = command.CreateParameter()
                parameter.ParameterName = "id"
                parameter.Value = ID
                command.Parameters.Add(parameter)
                connection.Open()
                Return command.ExecuteScalar()
            End Using
        End Using
    End Sub

End Class

It has two errors.
1. GetTextFromDatabaseUsing(id) says "Expression does not produce a value"

2. Return command.ExecuteScalar() says "'Return' statement in a Sub or a Set cannot return a value"

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[jmeckley]

1. [tt]GetTextFromDatabaseUsing[/tt] should return a string, it's not a void method.
2. [tt]key in web.config[/tt] should be [tt]QualityCenterConnectionString[/tt] according to your last post. You will also need to set the provider attribute of the connectionstring node "System.Data.SqlClient" IIRC.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[tqeonline]

Ok I fixed the issues by making the Public Sub like this

Public Function GetTextFromDatabaseUsing(ByVal ID As String) As String

Also I had to update the ID to be like this:

Dim id As String = context.Request.QueryString("id")

Now I have an error that says

Object reference not set to an instance of an object.

Talking about the

Dim factory = DbProviderFactories.GetFactory(connectionsettings.ProviderName)

Here is my connection string in the web.config

<add name="QualityCenterConnectionString" connectionString="Data Source=NDCVCSSQL015SG1;Initial Catalog=SQA_bsd_qa_db;Integrated Security=True"
   providerName="System.Data.SqlClient" />

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[tqeonline]

Your response to the Connection string is correct.

Here is the working code:

<%@ WebHandler Language="VB" Class="Claims_Log" %>

Imports System
Imports System.Web
Imports System.Data.Common
Imports System.Data

Public Class Claims_Log : Implements IHttpHandler
    
    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim id As String = context.Request.QueryString("id")
        If id = "" Then
            context.Response.Write("<h4>ERROR</h4>Please Provide MDN as ID String When Visiting page<br/><i>ex. [URL unfurl="true"]http://sqadashboard.int.asurion.com/edit/ACP/Claims_Log.ashx?id=1</i>")[/URL]
            context.Response.ContentType = "text/html"
        ElseIf id <> "" Then
            Dim text As String = GetTextFromDatabaseUsing(id)
            context.Response.Write(text)
            context.Response.ContentType = "text/html"
        End If
    End Sub
    
    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return True
        End Get
    End Property
    
    Public Function GetTextFromDatabaseUsing(ByVal ID As String) As String
        Dim connectionsettings = ConfigurationManager.ConnectionStrings("QualityCenterConnectionString")
        Dim factory = DbProviderFactories.GetFactory(connectionsettings.ProviderName)
        Using connection = factory.CreateConnection()
            connection.ConnectionString = connectionsettings.ConnectionString
            Using command = connection.CreateCommand()
                command.CommandType = CommandType.Text
                command.CommandText = "select [ACP_Claim_Log] from [ACP_Temp] where [id] = @id"
                Dim parameter = command.CreateParameter()
                parameter.ParameterName = "id"
                parameter.Value = ID
                command.Parameters.Add(parameter)
                connection.Open()
                Return command.ExecuteScalar()
            End Using
        End Using
    End Function

End Class

This is awesome. Thanks for the help!

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008