Solved

How to use View in MSSQL to get temp data?

Posted on 2009-04-14
22
683 Views
Last Modified: 2013-12-17
Hi

I have a table to store a opening balance of stock value.
e.g. WarehouseID = 1, ProductID=1, Amount=9880.5

and there is a transactions table to add and reduce the amount when buy or sell transactions make.

e.g. WarehouseID = 1, ProductID=1, Amount=300, Type=Sell, datetime = 2009=04-01'
e.g. WarehouseID = 1, ProductID=1, Amount=200, Type=Sell, datetime = 2009=04-03'
e.g. WarehouseID = 1, ProductID=1, Amount=50, Type=Buy, datetime = 2009=04-04'
.......

Then the update to date balance of amount is 9880.5 - 300 - 200 + 50 and it will keep on changing.

If I want to display the updated balance or in a particular date, I write a stored procedure to calculate and return the value. But, I do not know how can I display the data (over 100 products in 20 warehouses) in aspx with C# as the data is temporarily calculated and not stored in table. Should I build a view or how to build a temp table to let C# to do data bind for display in a listview?

Please advise.



 



 
0
Comment
Question by:techques
  • 11
  • 10
22 Comments
 
LVL 1

Expert Comment

by:Landho
ID: 24144721
You could create a calculated column (google search: C# expression field)
http://msdn.microsoft.com/en-us/library/0bdkcbb6.aspx
 
 
 
 
0
 

Author Comment

by:techques
ID: 24144786
I saw the code example, but how to bind the data into a table?

Do you have a complete code example?

private void AddDataColumn(DataTable table)

{

    System.Type decimalType;

    decimalType = System.Type.GetType("System.Decimal");
 

    // Create the column. The name is 'Tax,' with data type Decimal,and 

    // an expression ('UnitPrice * .0862) to calculate the tax.

    DataColumn column = new DataColumn("Tax", 

        decimalType, "UnitPrice * .0862");
 

    // Set various properties.

    column.AutoIncrement = false;

    column.ReadOnly = true;
 

    // Add to Columns collection.;

    table.Columns.Add(column);

}

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145159
Hi,

I'm little confused with your requirements. Please correct me if I am wrong.

You have transaction tables and based on the transactions you would like to show the updated balance in an ASP.Net Page for each product. Am I correct?
0
 

Author Comment

by:techques
ID: 24145212
There are 2 tables:
account
WarehouseID = 1, ProductID=1, Amount=9880.5

transactions
WarehouseID = 1, ProductID=1, Amount=300, Type=Sell, datetime = 2009=04-01'

account and transactions are the table names

There is only one opening balance in account table, but there are many rows of data in transactions table.

The updated amount balance = [account].[Amount] + (for buy) / - (for sell) of the [transactions].[Amount] where WarehouseID = 1 (or 2,3,4.....20), ProductID=1 (or 2,3,4,5,......200)
0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145269
Hi,

You can do it from Sql end.

Create a view

Create view dbo.vw_Transactions as
Begin
            Select A.WarehouseID, A.ProductID, Amount [Opening Balance], Buy, Sell, Amount+Buy-Sell [Closing Balance] from Account A
             Join
             (Select WareHouseID, ProductID, Sum(Amount) [Buy] from Transactions where Type='Buy' Group By WareHouseID, ProductID) B on A.WareHouseID=B.WareHouseID and A.ProductID = B.ProductID
             Join
             (Select WareHouseID, ProductID, Sum(Amount) [Sell] from Transactions where Type='Sell' Group By WareHouseID, ProductID) B on A.WareHouseID=B.WareHouseID and A.ProductID = B.ProductID

END
0
 

Author Comment

by:techques
ID: 24145310
Do you mean each time I load that aspx page and will create that view?

Do I trigger that view in SP?

How to execute that view command?

Or, once that view has created, then the data will be updated for every time of loading the aspx page?

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145420
No no. Create the view in Sql Server and refer the view in your SqlDataAdapter object by

SqlDataAdapter SqlAdp = new SqlDataAdapter("Select * from dbo.vw_Transactions", SqlCon);
DataTable dt = new DataTable();
SqlAdp.Fill(dt);

GridView1.DataSource=dt;
GridView1.DataBind();
0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145431
Just execute the code in Query Analyzer (Sql Server 2000) or query window (Sql Management Studio 2005 & 2008) to create a view

reate view dbo.vw_Transactions as

Begin

            Select A.WarehouseID, A.ProductID, Amount [Opening Balance], Buy, Sell, Amount+Buy-Sell [Closing Balance] from Account A

             Join

             (Select WareHouseID, ProductID, Sum(Amount) [Buy] from Transactions where Type='Buy' Group By WareHouseID, ProductID) B on A.WareHouseID=B.WareHouseID and A.ProductID = B.ProductID

             Join

             (Select WareHouseID, ProductID, Sum(Amount) [Sell] from Transactions where Type='Sell' Group By WareHouseID, ProductID) B on A.WareHouseID=B.WareHouseID and A.ProductID = B.ProductID

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145449
Answers for your questions.

1. It is not required to create view everytime.Create once and use anytime.
2. It is not required to trigger the view from SP. View is nothing but a window to a table. You can write a sql statement for view like how you write for a table.
3. Since view is a window for a table it will reflect the original data of the table every time the page is loaded.
0
 

Author Comment

by:techques
ID: 24145668
Hi, however, I need to pass parameters from aspx and C# to DB to generate the proper view. Here is the SP to calculate account balance of one accountid and in a specified datetime.

If I need to create that view, how should I write?



ALTER PROCEDURE [dbo].[SP_Cal_Account_Balance] 

@accountid int,

@uptodate datetime,

@return decimal(18,6) output

AS

BEGIN

Declare @bal int

Declare @sumamount decimal(18,6)

Declare @sumpayoutamount decimal(18,6)

select @bal=balance from account where id=@accountid

select @sumamount=sum(amount) from subtransaction where accountid=@accountid and subdatetime <= @uptodate

select @sumpayoutamount=sum(payoutamount) from subtransaction where payoutaccountid=@accountid and subdatetime <= @uptodate

set @return = @bal + @sumamount - @sumpayoutamount

if @return is null

set @return = 0

END

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145826
If you calculating only the Account Balance it is not required for you to create a stored proc. Creating a scalar value function will do.

Execute the attach code snippet in Query Window and you can retrieve the value by

Select dbo. ufn_Cal_Account_Balance(@accountid, @uptodate)

it will return the balance as scalar value.  You can rewrite as a single query as

Select AccountID, @uptodate [As of Date], dbo. ufn_Cal_Account_Balance(Accountid, @uptodate)  [Account Balance] from Accounts

You can directly bind the output to GridView.
Create Function [dbo].[ufn_Cal_Account_Balance]

@accountid int,

@uptodate datetime

Returns decimal(18, 6)

AS

BEGIN

Declare @return decimal(18,6)

Declare @bal int

Declare @sumamount decimal(18,6)

Declare @sumpayoutamount decimal(18,6)

select @bal=balance from account where id=@accountid

select @sumamount=sum(amount) from subtransaction where accountid=@accountid and subdatetime <= @uptodate

select @sumpayoutamount=sum(payoutamount) from subtransaction where payoutaccountid=@accountid and subdatetime <= @uptodate

set @return = @bal + @sumamount - @sumpayoutamount

if @return is null

set @return = 0
 

return @return

END

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:techques
ID: 24145854
Msg 102, Level 15, State 1, Procedure ufn_Cal_Account_Balance, Line 7
Incorrect syntax near '@accountid'.
Msg 137, Level 15, State 2, Procedure ufn_Cal_Account_Balance, Line 16
Must declare the scalar variable "@accountid".
Msg 137, Level 15, State 2, Procedure ufn_Cal_Account_Balance, Line 17
Must declare the scalar variable "@accountid".
Msg 137, Level 15, State 2, Procedure ufn_Cal_Account_Balance, Line 18
Must declare the scalar variable "@accountid".
Msg 178, Level 15, State 1, Procedure ufn_Cal_Account_Balance, Line 23
A RETURN statement with a return value cannot be used in this context.
0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145869
Sorry. please use the following script.


Create Function [dbo].[ufn_Cal_Account_Balance]

(

@accountid int,

@uptodate datetime

)

Returns decimal(18, 6)

AS

BEGIN

Declare @return decimal(18,6)

Declare @bal int

Declare @sumamount decimal(18,6)

Declare @sumpayoutamount decimal(18,6)

select @bal=balance from account where id=@accountid

select @sumamount=sum(amount) from subtransaction where accountid=@accountid and subdatetime <= @uptodate

select @sumpayoutamount=sum(payoutamount) from subtransaction where payoutaccountid=@accountid and subdatetime <= @uptodate

set @return = @bal + @sumamount - @sumpayoutamount

if @return is null

set @return = 0

 

return @return

END

Open in new window

0
 

Author Comment

by:techques
ID: 24145946
yes, it can get the correct single result from Select dbo. ufn_Cal_Account_Balance(@accountid, @uptodate)

However, if I use

Select id, '2009-04-15' [As of Date], dbo.ufn_Cal_Account_Balance(1, '2009-04-15')
[Account Balance] from account

It display:

1      2009-04-15      14355.000000
2      2009-04-15      14355.000000
3      2009-04-15      14355.000000
4      2009-04-15      14355.000000
5      2009-04-15      14355.000000
6      2009-04-15      14355.000000
7      2009-04-15      14355.000000
8      2009-04-15      14355.000000
9      2009-04-15      14355.000000
10      2009-04-15      14355.000000
11      2009-04-15      14355.000000
12      2009-04-15      14355.000000
13      2009-04-15      14355.000000
14      2009-04-15      14355.000000
15      2009-04-15      14355.000000
16      2009-04-15      14355.000000
17      2009-04-15      14355.000000
18      2009-04-15      14355.000000
19      2009-04-15      14355.000000
20      2009-04-15      14355.000000
21      2009-04-15      14355.000000
22      2009-04-15      14355.000000
23      2009-04-15      14355.000000
24      2009-04-15      14355.000000
25      2009-04-15      14355.000000
26      2009-04-15      14355.000000
27      2009-04-15      14355.000000

However, different id should have different account balance after calculation.

And, how should I use the following VC# code to bind it to a gridview and display on aspx page?

Or, how should I write the C# code to execute the db function?




private void Bind_Account_Balance(int id, Datetime date)

        {

            this.strSql = "Select " + id + "," + date + " [As of Date], dbo.ufn_Cal_Account_Balance(" + id + "," + "date) [Account Balance] from account ";

            DataSet ds = new DataSet();

            try

            {

                ds = SqlHelper.ExecuteDataset(DBConnection.ConnString, CommandType.Text, this.strSql);

            }

            catch (Exception)

            {

                Response.Redirect("../error.aspx", true);

            }

            this.account.DataSource = ds.Tables[0].DefaultView;

            this.account.DataTextField = "Account Balance";

            this.account.DataValueField = "id";

            this.account.DataBind();

        }

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24145991
You have made a small mistake

Select id, '2009-04-15' [As of Date], dbo.ufn_Cal_Account_Balance(id, '2009-04-15')
[Account Balance] from account

instead of id you have hardcoded as 1.

What is your expected output? I mean how you are expecting your output should be. I can guide you.
0
 

Author Comment

by:techques
ID: 24146078
Dear Sir,

you are correct, it should be id instead of '1'.

I have a aspx page which has dropdownlist and a js calendar. User will select the accountid and date which pass into C# code.

Then, I need to execute a VC# code to pass accountid and date to the function you provided. Then, the result will be displayed on aspx page.

I changed the SQL and here is the result:
id                   Account        Balance             Date
1      RMB-Saving      14355.000000      2009-04-15
2      HKD-Saving      15539.000000      2009-04-15
3      USD-Saving      14996.000000      2009-04-15
4      SGD-Saving      0.000000      2009-04-15
5      RMB-Saving      0.000000      2009-04-15
6      HKD-Saving      0.000000      2009-04-15
7      USD-Saving      0.000000      2009-04-15
8      SGD-Saving      0.000000      2009-04-15
9      RMB-Saving      0.000000      2009-04-15
10      HKD-Saving      0.000000      2009-04-15
11      USD-Saving      0.000000      2009-04-15
12      SGD-Saving      0.000000      2009-04-15
13      RMB-Saving      0.000000      2009-04-15
14      HKD-Saving      0.000000      2009-04-15
15      USD-Saving      0.000000      2009-04-15
16      SGD-Saving      0.000000      2009-04-15
17      RMB-Saving      0.000000      2009-04-15
18      HKD-Saving      0.000000      2009-04-15
19      USD-Saving      0.000000      2009-04-15
20      SGD-Saving      0.000000      2009-04-15
21      RMB-Saving      0.000000      2009-04-15
22      HKD-Saving      0.000000      2009-04-15
23      USD-Saving      0.000000      2009-04-15
24      SGD-Saving      0.000000      2009-04-15
25      RMB-Saving      0.000000      2009-04-15
26      HKD-Saving      0.000000      2009-04-15
27      USD-Saving      0.000000      2009-04-15

I exactly need to display
id                   Account        Balance             Date
on the aspx page after user select accountid, date and press the submit button.

I will add more point to this question as it involves several coding skills
Select a.id, (a.shortcode+ '-' + c.currency+ '-' +a.type) Account, '2009-04-15' [As of Date], dbo.ufn_Cal_Account_Balance(a.id, '2009-04-15') 

[Account Balance] from account a inner join currency c on a.currencyid = c.id

Open in new window

0
 
LVL 14

Accepted Solution

by:
GiftsonDJohn earned 200 total points
ID: 24146132
Okay it is pretty simple. just I have given the code snippet.

Please correct the sql statement wherever applicable
SqlConnection SqlCon = new SqlConnection(<sql connection string>);

SqlDataAdapter SqlAdp;

DataTable dt;
 

//Code to populate Dropdown with AccountID

SqlAdp = new SqlDataAdapter("Select CurrencyID, currency from currency", SqlCon);

dt = new DataTable();

SqlAdp.Fill(dt);
 

DropDownList1.DataSource=dt;

DropDownList1.DataTextField="Account";

DropDownList1.DataValueField="ID";

DropDownList1.DataBind();
 

//Code to bind the calculated value from database on click of Submit button
 

protected void btnSubmit_Click(object sender, EventArgs e)

{

    SqlCommand SqlCmd = new SqlCommand("Select a.id, (a.shortcode+ '-' + c.currency+ '-' +a.type) Account, @Date1 [As of Date], dbo.ufn_Cal_Account_Balance(a.id, @Date1) 

[Account Balance] from account a inner join currency c on c.currencyid = a.id Where a.id=@accountid", SqlCon);

    SqlCmd.Parameters.AddWithValue("@accountid", DropDownList1.SelectedValue);

    SqlCmd.Parameters.AddWithValue("@Date1", Calendar1.SelectedDate);

    SqlAdp = new SqlDataAdapter(SqlCmd);

    dt  = new DataTable();

    SqlAdp.Fill(dt);
 

    //Now Bind to GridView.

     GridView1.DataSource=dt;

     GridView1.DataBind();

}

Open in new window

0
 

Author Comment

by:techques
ID: 24146307
Hi

I use the following aspx code which I drag it from the tool. It throws exception.

How can I fix it?

I viewed that SqlCon is null. Is that connection problem?


<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">

    <RowStyle BackColor="#EFF3FB" />

    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

    <EditRowStyle BackColor="#2461BF" />

    <AlternatingRowStyle BackColor="White" />

</asp:GridView>
 

C#:

private void btnSubmit_Click(object sender, System.EventArgs e)

        {

            if (Page.IsValid)

            {

                try

                {

SqlConnection SqlCon = new SqlConnection(ConnectionString);

                    SqlDataAdapter SqlAdp;

                    DataTable dt;

                    SqlCommand SqlCmd = new SqlCommand("Select a.id, (a.shortcode+ '-' + c.currency+ '-' +a.type) Account, @Date1 [As of Date], dbo.ufn_Cal_Account_Balance(a.id, @Date1) [Account Balance] from account a inner join currency c on c.currencyid = a.id Where a.id=@accountid", SqlCon);

                    SqlCmd.Parameters.AddWithValue("@accountid", this.accountid.SelectedItem.Value);

                    SqlCmd.Parameters.AddWithValue("@Date1", this.txtEndDate.Text);

                    SqlAdp = new SqlDataAdapter(SqlCmd);

                    dt  = new DataTable();

                    SqlAdp.Fill(dt);

                    GridView1.DataSource=dt;

                    GridView1.DataBind();

                }

                catch

                {

                }

                finally

                {

                }

            }

        }

Open in new window

0
 

Author Comment

by:techques
ID: 24147431
It works now as i changed to use dataset
0
 

Author Closing Comment

by:techques
ID: 31570276
excellent solution
0
 

Author Comment

by:techques
ID: 24155044
The table now can display
id, account, date, balance

I need to add exchange rate and the countervalue:
id, account, date, balance, exchangerate, countervalue

The exchange rate is the referencerate in exchanges table. currency as unique id.

I edited the SQL:
select @bal=a.balance, @currency=a.currency, @excurrency=e.currency, @referencerate=e.referencerate from account a inner join exchanges e on a.currency = e.currency where a.id=@accountid

How should I edit the following SQL to display the result with exchange rate and countervalue?

Select a.id, (a.shortcode+ '-' + c.currency+ '-' +a.type) Account, '" + da + "' [As of Date], dbo.ufn_Cal_Account_Balance(a.id, '" + da + "') [Account Balance] from account a inner join currency c on c.id = a.currencyid

I can add more points to this new question.


ALTER Function [dbo].[ufn_Cal_Account_Balance]

(

@accountid int,

@uptodate datetime

)

Returns decimal(18, 6)

AS

BEGIN

Declare @return decimal(18,6)

Declare @bal int

Declare @currency nvarchar(50)

Declare @excurrency nvarchar(50)

Declare @referencerate decimal(18,6)

Declare @sumamount decimal(18,6)

Declare @sumpayoutamount decimal(18,6)

select @bal=a.balance, @currency=a.currency, @excurrency=e.currency, @referencerate=e.referencerate from account a 

inner join exchanges e on a.currency = e.currency where a.id=@accountid

select @sumamount=sum(amount) from subtransaction where accountid=@accountid and subdatetime <= @uptodate

select @sumpayoutamount=sum(payoutamount) from subtransaction where payoutaccountid=@accountid and subdatetime <= @uptodate

set @return = @bal + @sumamount - @sumpayoutamount

if @return is null

set @return = 0 

return @return

END

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24155077
Don't edit ufn_Cal_Account_Balance. It just return the calculated balance. Just add another join to your main query as like below.

Select a.id, (a.shortcode+ '-' + c.currency+ '-' +a.type) Account, '" + da + "' [As of Date], e.referencerate [Exhange Rage], dbo.ufn_Cal_Account_Balance(a.id, '" + da + "') [Account Balance] from account a inner join currency c on c.id = a.currencyid join exchanges e on a.currency = e.currency
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now