database and asp.net

My goal is to connect to my database either manually or using a开发者_如何学编程n sqladapater, and get information from two of my databases on sql server 2005. Then I want to take this information and on run-time begin to add/subtract/divide/multiply certain columns and place the information into other columns. I can do this in queries, however, I want to do it on run-time what is the best way to achieve this. I had some of this working, but I just want to start fresh and see how you would go about doing this.

Partial Class _Default
    Inherits System.Web.UI.Page
    Public cmd As New SqlCommand()
    Public da As New SqlDataAdapter(cmd)
  conn string here.....
    Dim ds As New DataSet

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cmd.Connection = conn
        conn.Open()

        ' Dim ds As New DataSet
        Dim x As New DataTable

        cmd.CommandText = "SELECT B603SalesAsOFMASTER.DIVISION, B603SalesAsOFMASTER.SDESCR, B603SalesAsOFMASTER.DYYYY, B603SalesAsOFMASTER.AsOFSales, B603SalesAsOFMASTER.ASOFPAX, B603SalesAsOFMASTER.YESales, B603SalesAsOFMASTER.YEPAX, B603SalesAsOFMASTER.PCTofSales, B603SalesAsOFMASTER.PCTofPAX, B601SalesAsOF.Sales AS [Current Sales], B601SalesAsOF.PAX AS [Current PAX], B601SalesAsOF.Sales / B601SalesAsOF.PAX AS [AVG PAX], B601SalesAsOF.Sales / B603SalesAsOFMASTER.PCTofSales AS [Projected Year End] FROM B603SalesAsOFMASTER INNER JOIN B601SalesAsOF ON B603SalesAsOFMASTER.SDESCR = B601SalesAsOF.SDESCR WHERE (B603SalesAsOFMASTER.DYYYY = '2008') AND (B601SalesAsOF.DYYYY = '2010')"
        'da.Fill(ds)
        da.Fill(x)


        GridView1.DataSource = x

        GridView1.DataBind()

        da.FillSchema(ds, SchemaType.Mapped)

        conn.Close()
    End Sub

End Class

That is what i am playing around with right now, some of my calculations are done directly in the sql command, but i want to change this.

---

It sounds like you're asking how to move a calculated field out of a SQL query and into code between the query and the datagrid binding. (If I'm incorrect in that interpretation, let me know.) If that's the case, this short tutorial should have exactly what you're looking for.

On a side note, those public data access members on your class make me uneasy. Those might be better as private. Or, better still, encapsulated within the single method that uses them. Throw in some try/catch/finally for good measure and resource cleanup of course :)

---

Create a Data Access Layer (DAL) 2 - Sqlconnections, one for each db Use a sqlreader if you are just reading data and an adapter if you need to make changes. Use either a sql string or stored procedures to retrieve your 'uncalculated' data, but make sure they are parametarized if you plan on allowing the user interface to do any filtering.

*Study up on avoiding SQL Injection

Create a Business Logic Layer This is where you can take the data from the DAL and do your calculations.

Presentation Layer Looks like this is going to be a web page(s) with a grid viewer or maybe a repeater control based on your needs. Here are some ideas to get you going: http://msdn.microsoft.com/en-us/library/bb498211.aspx