Microsoft has two very helpful articles on DAL (Creating a Data Access Layer) &
BLL (Creating a Business Logic Layer) but they are Incomplete as the does not cover all the Tables.
The most Important tables in Northwind db are Orders & OrderDetails. Both the articles have nothing about them.
TABLE 1
SELECT I.STUNA
,S.SNO
,SUM(B.AMT * Y.YEAR) AS AMT
FROM STUDENT S
LEFT JOIN INFO I ON S.SNO = I.SNO
LEFT JOIN BONUS B ON S.SNO = B.SSNO
LEFT JOIN YEAR Y ON S.SNO = Y.SSNO
STUNA SNO AMT
JOHN A 10
LISA B 20
ALLEN C 100
TABLE 2
SNO AMT
A 1
B 2
C 3
D 5
I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)
STUNA SNO AMT
JOHN A 11
LISA B 22
ALLEN C 103
EDDIE D 5
I TRIED USE THIS QEURY
SELECT I.STUNA
,S.SNO
,SUM(B.AMT) AS AMT
FROM STUDENT S
LEFT JOIN INFO I ON S.SNO = I.SNO
LEFT JOIN BONUS B ON S.SNO = B.SSNO
LEFT JOIN
(
SELECT SNO, SUM(AMT) AS AMT
FROM TABLE2
GROUP BY SNO
) T2 ON S.SNO = T2.SNO
GROUP BY I.STUNA,S.SNO
THE RESULT IS
STUNA SNO AMT
JOHN A 11
LISA B 22
ALLEN C 103
THE "EDDIE" WAS MISS. ALSO I TRY USE CROSS JOIN... THE RESULT IS STILL NOT CORRECT.
How do i implement cross backing in my asp.net projects.Also give examples and whats the use?
View 3 RepliesI am creating an ASP.NET application that allows users to edit and insert data that is persisted in a SQL Server database.I would like to implement a controller class that handles queries for certain entities, caches results and triggers events when data is inserted or updated.I need one of these controller classes for each entity in my data model.What I'm unsure of is how to create a singleton class of this sort that different user sessions can access.
For example,I have a Employee entity in my data model.I would like to have an EmployeeController class that handles retrieving the employees from the database, sends inserts and updates to the database,keeps a cache of all employees and triggers events when an employee is inserted or updated.Objects in other user sessions could then listen and respond to these events.
Am I on the right track?Should I be using the ASP.NET MVC framework to accomplish this?How do I instantiate these controller classes when the first user needs them and have subsequent concurrent users access the same class?
EDIT:I've been researching the idea of a controller class as an anti-pattern since @TomTom's answer.I'm not convinced one way or the other.I still need an answer to question #3 above.Let's just drop the specific idea of a controller class for now. How do I create any class that is:accessible by multiple user sessions,allows only one instance,is deleted if there are no user sessions,and triggers events that other classes can listen to.
I have this SP. When I run this one my calculations is only the first row in OrderDetails. I want it to calculate all rows
[Code]....
I'm trying to display the day most orders were placed, and how many orders were placed.
I use the following LINQ to get find what I'm looking for:
[Code]....
I want to display this as follow:
12 orders were placed 01.01.2010.
I get the ordercount using this:
[Code]....
But how can i get the day?
var topDay = from o in orders
I have followed a tutorial and got a shopping cart working with some products in my database.
It uses the querystring to add the items to the cart on a different page. What i would like to do is submit the items in the cart to the database, if anyone would be able to guide me into doing that it would be great.
Here is my code:
View Cart Page which outputs using a repeater:
[Code]....
I have a problem with my first ASP.NET webpage. Im using ASP.NET 3.5 with VisualStudio 2008 and LINQ to SQL.
To easier communication we take a example from northwind data base. I want to list all the Orders and for each order I want to list all its OrderDetails. I have created a listview (lvOrders) with a LinqDataSource (Order table). Now I have create a new nested listview, for the order details, but I dont know how to load the orderdetails in this nested listview. I have tried to load the orderdetails in the OnItemDataBound of the first listview (lvOrders), but I miss functionatilly like DELETE command on the nested listview (to delete a orderdetail) This is my code:
[Code]....
On code behind:
[Code]....
Im thinking on some like a unique linqdatasource, returning a new Order entity (modified) with one field that is a list of orderdetails... this would work?
Can You send me the code of mention website [URL]. open the website and put the 10 no's. with press enter one by one show the result in table format or gridview like this.
View 1 RepliesI am using a gridview on an OrderList page to present data from a database. Paging is enabled on the gridview.
Each row provides a link to an OrderDetails page which is used to display/edit data for the selected order.
I want to be able to return from OrderDetails page to the OrderList page and to set the gridview pageindex.
What is the best way to do this?
Can I have multiple paramaters with NavigateURL (i.e. the orderid and the pageindex)?
I am doing a tutorial where you use a templatefield in the gridview control to call a function. I don't understand the code for the function. What is the object Northwind.EmployeesRow? This is the tutorial I am doing. Tutorial 12: Using TemplateFields in the GridView Control And this is the code for the function.
Protected Function DisplayDaysOnJob(ByVal employee As Northwind.EmployeesRow) As String
If employee.IsHireDateNull() Then
Return "Unknown"
Else
' Returns the number of days between the current
' date/time and HireDate
Dim ts As TimeSpan = DateTime.Now.Subtract(employee.HireDate)
Return ts.Days.ToString("#,##0")
End If
End Function
I have an entity "Order", which contains a set of states. Every state has date and name.
I want show grid with all orders and in grid one column with last state of order. But I dont understand how to do it.
Which version of Northwind db can I install with VS 2010/.net 4? Can you pls give the link for downloading the same. Also can somebody tellme the steps to go about starting SQL service I have already installed VS 2010.
View 4 Repliesim creating a database that needs a unique constraint on columns. does anyone know how to add this within the northwind database?
View 4 Repliesi need the northwind databse. i dont have it in the database where i can find it and who to add it to my database in sql server so can i c it in the combo box off the database
View 1 RepliesWhere can you download Northwind for SQLServer 2005? I tried this but there is no Northwind after the install... [URL]
View 1 RepliesI am a .net developer.I am not that strong in SQL.I need a simple query.This is from Northwind Database.
select 'Select All' as CustomerID,'Select All' as City union(select CustomerID, City from Customers
order by City)
i need to get output like 1st row should be Select ALl Select All and next rows should be output of this stat..(select CustomerID, City from Customers order by City)
i am not getting this...i can do by keeping the second query result in temp table and then using union... but i am looking without temp tables or simple query.
i was attaching northwind database with sql server 2005 so while attaching it gives me an error an exception occured while executing a transct -sql statemnet or batch ,unable to eon physical file "C:sql server 2000 sample database orthwnd.mdf"
View 6 RepliesI am a complete newbie when it comes to ASP.NET and SQL database access. I'm trying to learn so that I might become gainfully employed one day soon and better my life. I'm running through the Visual Web Developer Express 2008 beginning walkthroughs right now and I'm already stuck on the third one -- Walkthrough: Basic Data Access in Web Pages. I've installed MS SQL Server 2008 and downloaded the sample Northwind and pubs databases, and even (I think) attached the pre-built files using the DOS command: sqlcmd -S .SQLEXPRESS -i instnwd.sql. (Output was: Changed database context to 'master'. Changed database context to 'Northwind'.)
But, now, when I try to Add Connection through the Database Explorer in VWDE2008, nothing comes up in the Connect to a Database field. Shouldn't it auto-populate? If I'm supposed to manually type a name in there, what should it be? I've tried "Northwind" and "SQLEXPRESS" with no luck (getting a generic Unable to Connect message). I've verified the database is running with SQL Server Configuration Manager and that all Client Protocols are enabled.
i have a first page and user selecting some products. Passind with session. Retrieving from first page. And in this point i want to connecting my northwind db and display selected products detail side by side in my second page. (listview or datalist) here is my second page codebehind.
[Code]....
I'm trying this example: [URL] I got it to work before, like 6-months ago. I'm coming back to this now and I can't seem to get the example working. Do I need to create a query in SSMS and then set up my Master/Detail views off of that? It seems like the GridView is NOT linked to the DropDownList and it seems like there is no way to link the two Objects.
View 2 RepliesI wan to retrieve spesific data from northwind and list in my compare page.
in the first page i used gridview with checkboxes. all selected values get with session and then
send to my second page. I can display this values in the page but how can i retrieve all product details from my northwind db i dont know.
here is my second page code behind.
[Code]....
and aspx page
[Code]....
I've been attempting to walk through the "Creating a Data Access Layer" tutorial found [URL]I create the DB connection, create the typed dataset and table adapter, specify the sql, etc.When I add the code to the presentation layer (in this case a page called AllProducts.aspx) I am unable to find the NorthwindTableAdapters.ProductsTableAdapter class. I tried to import the NorthwindTableAdapters namespace, but it is not showing up. Looking in the solution explorer Class View confirms that there is a Northwind class, but not the namespace I'm looking for.
I've tried several online tutorials that all have essentially the same steps, and I'm getting the same results.Can anyone give me a push in the right direction?I'm getting error: Namespace or type specified in the Imports 'NorthwindTableAdapters' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member.I think I might need to add a reference OR they may be creating a separate class and importing it into their main project. If that's the case, the tutorials do not mention this.
SuppliersTest2.aspx.vb:
Imports NorthwindTableAdapters
Partial Class SuppliersTest2
Inherits System.Web.UI.Page
[code]...
I've been attempting to walk through the "Creating a Data Access Layer" tutorial found [URL] I create the DB connection, create the typed dataset and table adapter, specify the sql, etc. When I add the code to the presentation layer (in this case a page called AllProducts.aspx) I am unable to find the NorthwindTableAdapters.ProductsTableAdapter class. I tried to import the NorthwindTableAdapters namespace, but it is not showing up. Looking in the solution explorer Class View confirms that there is a Northwind class, but not the namespace I'm l ooking for. I've tried several online tutorials that all have essentially the same steps, and I'm getting the same results.
View 1 RepliesI have an C# ASP application I am writing that needs to have the capability to import a generated excel or a comma delineated sheet each day. A clerk will have this job each morning so it doesn't need to be automated. My problem in trying to understand the solution to this is that the 1 sheet contains loan information, including customer information all in the same sheet. I would like to send certain columns to update information in the loan table and send other information to update the customer table. I need it to create relationships when new loans appear in the spreadsheet.
View 1 Replies
