Solved

How to use View in MSSQL to get temp data?

Posted on 2009-04-14
22
697 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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
 

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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 54
Trying to understand why my Index is so large 12 52
asp.net mvc5 6 34
bulk load multiple users for aspnetusers table 2 16
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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