Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to use View in MSSQL to get temp data?

Posted on 2009-04-14
22
Medium Priority
?
701 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 800 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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