SAP Business Objects great Admin tool to view and query the admin auditing tables

A little known and little discussed tool included in the SAP BusinessObjects suite is something called Query Builder.  This simple webapp can be used to query your BusinessObjects repository to get all kinds of information not readily available in other places such as the Central Management Console.

The BusinessObjects repository is a database that contains all the information about the reports, universes, and security that make up your deployment.  Unfortunately, the data contained in this repository is stored in a binary format, so you can’t query it with conventional SQL tools.  That’s where Query Builder comes in.  Using queries that are very similar to SQL, you can tap the information hidden away there.  Since the repository is what drives the entire BusinessObjects system, there is a lot to explore.

A Quick Tour of Query Builder

To access the Query Builder, point your web browser to your BusinessObjects server.  Query Builder can be found at the following URL:  http://[server]:[port]/AdminTools/.  Log on as an Administrator to get full access to all the repository objects.  From here you can begin to construct your query.  There are three “tables” that you can query:

• CI_INFOOBJECTS
  Contains objects that are often used to build the user desktop, such as favorites folders and reports.
• CI_SYSTEMOBJECTS
  Contains objects that are often used to build the admin desktop and internal system objects, such as servers, connections, users, and user groups.
• CI_APPOBJECTS
  Contains objects that represent BusinessObjects Enteprise applications. For example, the InfoView and Desktop Intelligence objects are stored in this table.

A query can be as simple as this:

SELECT * FROM CI_INFOOBJECTS

This will return the details for all of the “InfoObjects” in your repository — all documents, folders, and other content.  You can filter this list using a WHERE clause just like you would in SQL. Using some of the basic properties, you can refine your query.

SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’
returns all WebIntelligence documents

SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME LIKE ‘Monthly%’
returns all content starting with the word “Monthly”

SELECT SI_NAME FROM CI_INFOOBJECTS WHERE SI_KIND=’Webi’ AND SI_NAME LIKE ‘Monthly%’ AND SI_RUNNABLE_OBJECT=1
returns a list of WebIntelligence documents that have a name starting with the word “Monthly” and are scheduled

To get all the reports created date , modified date , size of the report for auditing purposes

select SI_ID, SI_NAME,SI_SIZE,SI_UPDATE_TS,SI_CREATION_TIME,SI_STARTTIME,SI_ENDTIME,SI_KIND  from CI_INFOOBJECTS where SI_KIND='Webi'  Order BY SI_SIZE DESC

--- To get the all events of the documents refreshed or reports refreshed 

SELECT
  DETAIL_TYPE.Detail_Type_Description,
  AUDIT_EVENT.Start_Timestamp,
  AUDIT_EVENT.Duration,
  AUDIT_EVENT.User_Name,
  DBMS_LOB.SUBSTR (AUDIT_DETAIL.Detail_Text, 1000, 1),
  AUDIT_EVENT.Event_ID
FROM
  DETAIL_TYPE INNER JOIN AUDIT_DETAIL ON (DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID)
   INNER JOIN AUDIT_EVENT ON (AUDIT_DETAIL.Event_ID=AUDIT_EVENT.EVENT_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.SERVER_CUID)
   INNER JOIN EVENT_TYPE ON (AUDIT_EVENT.Event_Type_ID=EVENT_TYPE.Event_Type_ID)
   INNER JOIN V_DATE_HIERARCHY_PLUS  AuditEventDate ON (trunc(AUDIT_EVENT.START_TIMESTAMP)=AuditEventDate.DATE_FROM)
WHERE
  (
  EVENT_TYPE.Event_Type_Description  IN  ('Document Refreshed')
  AND  AuditEventDate.DIFF_DAYS  BETWEEN  -7 AND -2
  )



SELECT SI_ID, SI_CUID, SI_NAME, SI_CREATION_TIME, SI_UPDATE_TS, SI_KIND, SI_AUTHOR, SI_OWNER
FROM CI_INFOOBJECTS
WHERE SI_KIND = 'Webi'
AND SI_AUTHOR='Administrator'


 SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME ='EFASHION'")

I think that this query is not for "to find the number of users in a group" but for
"all reports connected to a universe" as already mentioned few posts above.
- just typo

2.,
SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS
WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")



General Queries

To get BO Repository Information


SELECT * FROM CI_SYSTEMOBJECTS WHERE SI_ID=4


To get BO File Repository Server Information


SELECT * FROM CI_SYSTEMOBJECTS
WHERE SI_KIND = 'SERVER' AND SI_NAME LIKE '%FILEREPOSITORY%'


To get the all the public folders (Non System Folders)


SELECT * FROM CI_INFOOBJECTS
WHERE SI_PARENTID=23 AND SI_NAME!='REPORT CONVERSION TOOL'  AND
SI_NAME!= 'ADMINISTRATION TOOLS' AND SI_NAME!= 'AUDITOR'


Some More queries 


To list all the WebI reports with prompts


SELECT SI_ID, SI_KIND, SI_NAME, SI_PROCESSINFO.SI_HAS_PROMPTS,
SI_PROCESSINFO.SI_WEBI_PROMPTS, SI_PROCESSINFO.SI_FILES,
SI_PROCESSINFO.SI_PROMPTS  FROM CI_INFOOBJECTS
WHERE  SI_KIND = 'WEBI' and SI_INSTANCE = 0 and
              SI_PROCESSINFO.SI_HAS_PROMPTS=1


To extract all the report names from specific folder


SELECT SI_ID,SI_NAME,SI_PARENT_FOLDER,SI_FILES
FROM CI_INFOOBJECTS 
WHERE SI_KIND = 'WEBI' AND SI_INSTANCE = 0 AND SI_ANCESTOR = [SI_ID OF THE FOLDER]


To get Reports those are spanning multiple universes


SELECT SI_ID, SI_KIND, SI_NAME FROM CI_INFOOBJECTS  WHERE SI_UNIVERSE.SI_TOTAL>1


Scheduled reports queries


To list all the events and corresponding event file location


SELECT SI_ID, SI_NAME, SI_FEATURES FROM CI_SYSTEMOBJECTS WHERE SI_KIND= 'Event'


To list all Scheduled reports based on event


SELECT SI_NAME, SI_SCHEDULEINFO  FROM CI_INFOOBJECTS
WHERE SI_RUNNABLE_OBJECT = 1 AND SI_SCHEDULEINFO.SI_DEPENDENCIES.SI_TOTAL > 0


To list reports those are not scheduled


SELECT SI_NAME, SI_OWNER, SI_AUTHOR, SI_SCHEDULEINFO, SI_PARENT_FOLDER 
FROM CI_INFOOBJECTS 
WHERE SI_KIND = 'WEBI' AND SI_CHILDREN = 0 AND SI_SCHEDULEINFO.SI_SCHED_NOW = 0


To get the list of all reports scheduled daily excluding Paused


SELECT SI_ID, SI_NAME, SI_SCHEDULEINFO.SI_SCHEDULE_TYPE,
SI_SCHEDULEINFO.SI_SCHEDULE_INTERVAL_NDAYS, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_NTHDAY, SI_SCHEDULEINFO. SI_SCHEDULE_INTERVAL_MONTHS
FROM CI_INFOOBJECTS
WHERE SI_SCHEDULE_STATUS !=8  AND SI_RECURRING = 1


To get the list of reports scheduled by a particular user


SELECT * FROM CI_INFOOBJECTS
WHERE SI_OWNER = '<USER NAME>' AND SI_RECURRING = 1


Universe queries
To Show count of reports per Universe
SELECT SI_NAME, SI_WEBI FROM CI_APPOBJECTS
WHERE SI_KIND='Universe' AND SI_WEBI.SI_TOTAL > 0
To retrieve all Web Intelligence reports connected to a Universe
SELECT * FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS 
WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'","SI_NAME =’EFASHION’")


To Show all universes using a specific connection


SELECT SI_ID, SI_NAME, SI_OWNER FROM CI_APPOBJECTS
WHERE CHILDREN("SI_NAME='DATACONNECTION-UNIVERSE' ", "SI_NAME='TEST'")


To list all Webi reports that uses the connection (multiple universes)


SELECT * FROM CI_APPOBJECTS, CI_INFOOBJECTS WHERE PARENTS("SI_NAME='WEBI-UNIVERSE'", "CHILDREN('SI_NAME=''DATACONNECTION-UNIVERSE'' ', 'SI_NAME=''TEST'' ')")  AND SI_KIND='WEBI'

User/UserGroups queries

To find the number of users in a group

SELECT SI_NAME,SI_GROUP_MEMBERS FROM CI_SYSTEMOBJECTS
WHERE SI_KIND = 'USERGROUP' AND SI_NAME='ADMINISTRATORS' 

To extract all the users from specific user group

SELECT SI_ID, SI_NAME, SI_KIND, SI_USERGROUPS FROM CI_SYSTEMOBJECTS 
WHERE DESCENDANTS("SI_NAME='USERGROUP-USER'", "SI_NAME='ADMINISTRATORS'")

SQL server 2014 Installation and Configuration on Oracle VM or Virtual Machine or Stand alone

https://www.microsoft.com/en-gb/evalcenter/evaluate-sql-server-2014?i=1

Download the Software and copy to some local folder which has full permissions on the folder on your logged or installing account

Make sure Firewall Is Off

Make sure you have enough space

Installing HP ALM 12.50 in Windows Environment

FormatingTextcontent

Private Function RaviformatTextcontent(ByVal Body As String) As String
Dim bodyText As String

bodyText = Body
bodyText = Replace(bodyText, "." & Chr(13) & Chr(10), ".

")
bodyText = Replace(bodyText, "." & Chr(13) & Chr(10) & Chr(32) & Chr(32), ".

")
bodyText = Replace(bodyText, ":" & Chr(13) & Chr(10) & Chr(32) & Chr(32), ":

")
bodyText = Replace(bodyText, "?" & Chr(13) & Chr(10) & Chr(32) & Chr(32), "?

")
bodyText = Replace(bodyText, """" & Chr(13) & Chr(10) & Chr(32) & Chr(32), """

")
'bodyText = Replace(bodyText, Chr(13) & Chr(10) & Chr(32) & Chr(32) & """", "

""")
bodyText = Replace(bodyText, "." & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(32), ".

")
bodyText = Replace(bodyText, ":" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(32), ":

")
bodyText = Replace(bodyText, "?" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(32), "?

")
bodyText = Replace(bodyText, "-" & Chr(13) & Chr(10), "")

FormatArticleBody = bodyText
End Function

The King of POP Michael Jackson is no more!

The King of POP Michael Jackson is no more!

LOS ANGELES – Michael Jackson, the sensationally gifted child star who rose to become the "King of Pop" and the biggest celebrity in the world only to fall from his throne in a freakish series of scandals, died Thursday. He was 50.

Jackson died at UCLA Medical Center in Los Angeles. Ed Winter, the assistant chief coroner for Los Angeles County, confirmed his office had been notified of the death and would handle the investigation.

The circumstances of Jackson's death were not immediately clear. Jackson was not breathing when Los Angeles Fire Department paramedics responded to a call at his Los Angeles home about 12:30 p.m., Capt. Steve Ruda told the Los Angeles Times. The paramedics performed CPR and took him to the hospital, Ruda told the newspaper.

Jackson's death brought a tragic end to a long, bizarre, sometimes farcical decline from his peak in the 1980s, when he was popular music's premier all-around performer, a uniter of black and white music who shattered the race barrier on MTV, dominated the charts and dazzled even more on stage.

His 1982 album "Thriller" — which included the blockbuster hits "Beat It," "Billie Jean" and "Thriller" — is the best-selling album of all time, with an estimated 50 million copies sold worldwide.

The public first knew him in the late 1960s, when as a boy he was the precocious, spinning lead singer of the Jackson 5, the music group he formed with his four older brothers. Among their No. 1 hits were "I Want You Back," "ABC," and "I'll Be There."

He was perhaps the most exciting performer of his generation, known for his feverish, crotch-grabbing dance moves and his high-pitched voice punctuated with squeals and titters. His single sequined glove, tight, military-style jacket and aviator sunglasses were trademarks second only to his ever-changing, surgically altered appearance.

"For Michael to be taken away from us so suddenly at such a young age, I just don't have the words," said Quincy Jones, who produced "Thriller." "He was the consummate entertainer and his contributions and legacy will be felt upon the world forever. I've lost my little brother today, and part of my soul has gone with him."

Jackson ranked alongside Elvis Presley and the Beatles as the biggest pop sensations of all time. He united two of music's biggest names when he was briefly married to Presley's daughter, Lisa Marie, and Jackson's death immediately evoked that of Presley himself, who died at age 42 in 1977.

As years went by, Jackson became an increasingly freakish figure — a middle-aged man-child weirdly out of touch with grown-up life. His skin became lighter, his nose narrower, and he spoke in a breathy, girlish voice. He surrounded himself with children at his Neverland ranch, often wore a germ mask while traveling and kept a pet chimpanzee named Bubbles as one of his closest companions.

"It seemed to me that his internal essence was at war with the norms of the world. It's as if he was trying to defy gravity," said Michael Levine, a Hollywood publicist who represented Jackson in the early 1990s. He called Jackson a "disciple of P.T. Barnum" and said the star appeared fragile at the time but was "much more cunning and shrewd about the industry than anyone knew."

Jackson caused a furor in 2002 when he playfully dangled his infant son, Prince Michael II, over a hotel balcony in Berlin while a throng of fans watched from below.

In 2005, he was cleared of charges he molested a 13-year-old cancer survivor at Neverland in 2003. He had been accused of plying the boy with alcohol and groping him, and of engaging in strange and inappropriate behavior with other children.

The case followed years of rumors about Jackson and young boys. In a TV documentary, he had acknowledged sharing his bed with children, a practice he described as sweet and not at all sexual.

Despite the acquittal, the lurid allegations that came out in court took a fearsome toll on his career and image, and he fell into serious financial trouble.

Jackson was preparing for what was to be his greatest comeback: He was scheduled for an unprecedented 50 shows at a London arena, with the first set for July 13. He was in rehearsals in Los Angeles for the concert, an extravaganza that was to capture the classic Jackson magic: showstopping dance moves, elaborate staging and throbbing dance beats.

Singer Dionne Warwick said: "Michael was a friend and undoubtedly one of the world's greatest entertainers that I fortunately had the pleasure of working with. ... We have lost an icon in our industry."

Hundreds of people gathered outside the hospital as word of his death spread. The emergency entrance at the UCLA Medical Center, which is near Jackson's rented home, was roped off with police tape.

"Ladies and gentlemen, Michael Jackson has just died," a woman boarding a Manhattan bus called out, shortly after the news was announced. Immediately many riders reached for their cell phones.

So many people wanted to verify the early reports of Jackson's death that the computers running Google's news section interpreted the fusillade of "Michael Jackson" requests as an automated attack for about half an hour Thursday evening.

In New York's Times Square, a low groan went up in the crowd when a screen flashed that Jackson had died, and people began relaying the news to friends by cell phone.

"No joke. King of Pop is no more. Wow," Michael Harris, 36, of New York City, read from a text message a friend sent to his telephone. "It's like when Kennedy was assassinated. I will always remember being in Times Square when Michael Jackson died."

DAL

ScottGu's Blog
Scott Guthrie lives in Seattle and builds a few products for Microsoft
Sign in | Join Home About RSS Atom Comments RSS
Search

Tags
.NET ASP.NET Atlas Commerce Server Community News Data IIS7 Link Listing LINQ MVC Security Silverlight SQL Server Talks Tips and Tricks Visual Studio WPF WPF/E
News

My ASP.NET 2.0 Tips, Tricks, Recipes and Gotchas Series


My Silverlight Tutorials and Links






Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
June 22nd 2006 Update: We've now published a whole series of new data tutorials based on this origional post. You can read all about it here.



One of my goals over the next few weeks is to publish a number of quick tutorial postings that walkthrough implementing common data binding design patterns using ASP.NET 2.0 (master details, filtering, sorting, paging, 2-way data-binding, editing, insertion, deletion, hierarchical data browsing, hierarchical drill-down, optimistic concurrency, etc, etc).



To help keep these samples shorter, and to help link them together, I’m going to use a common DAL (data access layer) implementation across the samples that is based on SQL Server’s Northwind sample database.



To build the DAL layer I decided to use the new DataSet Designer that is built-into Visual Web Developer (which you can download for free) as well as VS 2005, and which provides an easy way to create and encapsulate data access components within an application. I’ve used it for a few samples lately, and have found it pretty useful and flexible. I really like the fact that it enables me to avoid having to write tedious data access code, while still preserving full control over my SQL data logic (I also like the fact that it enables me to use both standard SQL statements as well as SPROCs).



One of the things that I’ve noticed is that there aren’t a lot of end-to-end tutorials that show off how to build and use a DAL with the designer (one exception is the great write-up by Brian Noyes – who includes samples of how to use it with SPROCs). What I’ve tried to-do with the below set of tutorials is provide an end-to-end, step-by-step, walkthrough that covers all of the core concepts involved in creating and consuming a DAL built with the data designer.



Using the data designer and ASP.NET 2.0 together, you should be able to create a core DAL implementation and build from scratch a data-driven UI web app on top of an existing database very quickly (~10-15 minutes to build an application from scratch that supports master/details filtering along with data paging, sorting, insertion, and editing).



The final result of the below walkthroughs can be downloaded here. This download includes all of the code samples as well as the DAL built up below.



I’ll then be posting lots of (much shorter! ) blog posting over the next few weeks that show off common data-UI patterns that use it.



Tutorial 1: Creating a Web Project and Connecting To the Database



To begin with, create an empty new ASP.NET Web Site Project (File->New Web Site) within Visual Web Developer. We can then connect and load our Northwinds database in the IDE. To-do this you can follow one of two approaches:



Option 1: Connect to a local or remote SQL 2000 or SQL 2005 database that has the Northwinds sample installed. To-do this, go to the “Server Explorer” property window in Visual Web Developer (choose View->Server Explorer if it isn’t currently visible), right-click on the “Data Connections” node and choose to add a new database connection. You can then walkthrough a wizard to pick your SQL database and load the Northwinds database in the IDE:







Option 2: If you don’t have a SQL 2000 or SQL 2005, you can alternatively use the free SQL 2005 Express Edition. Download this .zip file containing the Northwinds.mdf database file, and copy it to the “App_Data” directory underneath your web site’s root directory. Click the “Refresh” button in the solution explorer, and it will appear in the Solution Explorer:







SQL Express databases within the App_Data directory will automatically show-up as a listed item in the Server Explorer property window (note: I sometimes get a timeout error the very first time I expand a newly copied sql express database here – I think because it is generating the log file the first time it is accessed. If this happens just click it again and it seems to immediately expands):







After the Northwinds database has been loaded using either Option #1 or Option #2 above, you can expand, view, edit, and add to any of the tables, stored procedures, views, triggers, etc with the database. You can also use the query builder to test out and run queries against the database (or right-click on a table and choose “Show Table Data” to see all of it).



Tutorial 2: Launching the DataSet Designer



To create our Northwind DAL, right-click on the project node in the solution explorer, and select “Add New Item”. Then select the “DataSet” option and name it “Northwind.xsd”:







This will prompt me as to whether I want to add it under the “App_Code” directory. When I click “yes” it will bring up a data-design surface, and (if in a web project) automatically launch the “Create TableAdapter” wizard (if you are in a class library project you need to right click and choose “Add->Table Adapter to launch this wizard):







Tutorial 3: Creating our First Table Adapter



The “Create TableAdapter” wizard will first prompt me for the database to use, and provide a list of all database connections currently registered in Visual Web Developer’s server-explorer window (note: you can also create new connections from this wizard if you want).



After I choose the Northwind database I want to use, it will prompt me for where to store the database connection-string. By default it will avoid hard-coding it within your code, and will instead save it within the new section of your web.config file (or app.config file if you are in a Class Library Project). Note that with .NET 2.0 you can now optionally encrypt configuration file values to keep them secure, as well as use the new ASP.NET 2.0 Property Page within the IIS GUI Admin Tool to change it later (ideal for administrators). Pick a name for what you want to call it:







You can then choose how you want to configure the TableAdapter – you can use either SQL Statements that you embed within your DAL layer, or stored procedures (SPROCs) that you call from it:







For our first table adapter we’ll use a SQL Statement. You can either type this in directly within the next window:







Or alternatively launch the query-builder from that wizard step to graphically construct it (one handy feature with it is the ability to execute test queries to validate results):







Once we’ve finished building and testing our query, we will return back to the “Enter a SQL Statement” page. Before moving to the next page, we’ll want to click the “Advanced Options” button to verify what type of operations we want the designer to generate:







The above dialog shows the default settings that are set when you run the “Create TableAdapter” wizard from within a VS 2005 Web Site Project (these are the most common data patterns we typically see developer’s use with stateless web applications). When you run the wizard from within a Class Library Project or from a Windows Client Project, the “Use Optimistic Concurrency” checkbox will be selected by default as well.



For the purposes of this DAL walkthrough, we want to turn off optimistic concurrency (note: one of my later blog postings on optimistic concurrency will change that setting – but I will be showing straight updates first).



When we click next on the wizard again, we’ll be prompted for the method names we want to create in our DAL to run our Category SQL query. There are two patterns of data usage that this data wizard can generate – one is the “Fill” pattern used with DataSets, that will generate a method that accepts a DataSet or DataTable parameter that we want to add category information to. The second pattern of data usage is one where we will generate a method that will simply return a new DataTable containing our category information. This later approach is the one we’ll be primarily using in all of our later tutorials, and is more typical with stateless web-applications.



In the wizard I’m going to name this DataTable method “GetAllSuppliers”. I will also keep the “GenerateDBDirectMethods” checkbox to automatically create default Insert, Update and Delete methods based off of my Select query on the component:







The wizard will then list all of the DAL methods it will create as part of this wizard process for, and when I hit finish add them to our data designer:







What I now have is a strongly-typed DataAdapter class (by default named “NorthwindTableAdapters.SuppliersTableAdapter”) with a “GetAllSuppliers” method that I can use to execute a SQL query and obtain back a strongly-typed DataTable results class (by default named “Northwind.SuppliersDataTable”).



I could use these objects to easily obtain all of the Suppliers information from the Northwind database and output it within a page like so:

Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim suppliers As Northwind.SuppliersDataTable

Dim supplier As Northwind.SuppliersRow



suppliers = suppliersAdapter.GetAllSuppliers()



For Each supplier In suppliers

Response.Write("Supplier: " & supplier.CompanyName & "
")

Next

Notice that I don’t need to write any manual ADO.NET code, construct a SQL query, or manage connections at all in the above code – all of that is encapsulated by the SuppliersDataTable and SuppliersTableAdapter that we now have in our project. Notice also how the SuppliersTableAdapter class is strongly typed – meaning I get intellisense and compilation checking on the “suppliersAdapter.GetSuppliers()” method. I can also access each property returned in the Suppliers result in a strongly typed fashion (for example: supplier.CompanyName or supplier.SupplierID).



I could alternatively write the below .aspx page and associated .aspx.vb code-behind file to easily databind and output the results in a grid:



SuppliersTest2.aspx:



<%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false" CodeFile="SupplierTest2.aspx.vb" Inherits="SuppliersTest2" %>

Simple Category Listing

SuppliersTest2.aspx.vb:

Imports NorthwindTableAdapters



Partial Class SuppliersTest2

Inherits System.Web.UI.Page



Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load



Dim suppliersAdapter As New SuppliersTableAdapter



GridView1.DataSource = suppliersAdapter.GetAllSuppliers()

GridView1.DataBind()



End Sub



End Class

Which then generates the below output at runtime:







Note that when using the new ASP.NET ObjectDataSource control we will be able to eliminate the need to write any code for the scenario above (which even now only has three lines), as well as to automatically enable paging, sorting and editing on the GridView without any page code needed. I’ll cover how to-do this in a later blog posting on using this DAL. My goal with the sample above was just to show how you could procedurally databind the GridView using techniques you might already be familiar with in ASP.NET 1.1.



Tutorial 4: Adding Parameterized Methods within Table Adapters



It is usually pretty rare in web applications to want to request all of the data within a database table. More typically you’ll want to use SQL queries to retrieve only the subset of data that you need. The data designer makes it super easy to create multiple strongly-typed data-methods within a DAL to help with the parameterized SQL operations.



To add one to our Suppliers object, right-click on it and choose “Add Query”:







Once again we can choose either a stored procedure, or a SQL statement. For the SQL statement you can choose to return multiple rows, or just a single value (useful for things like SELECT Count(*) commands, or to retrieve a single value):







For this parameterized query I’m going to return multiple rows, and will be filtering the data by country value. I can express a parameter value using a “@parametername” syntax within my SQL statement like so (note: there can be any number of parameters in the SQL statement):







I can then name this parameterized data method “GetSuppliersByCountry”:







And now I have two methods I can use within my SuppliersTableAdapter to get Supplier data (GetAllSuppliers and GetSuppliersByCountry). Note that the designer is fully re-entrant, meaning you can at any point re-configure a data method (for example: to change the SQL statement, add/remove parameters, etc). Just right click and select the “configure” method to-do so:







You can also use the designer to run any of the data methods. Just right-click and select “Preview Data” to execute them and see the results (note how any parameters can be specified as part of this operation):







And in code I can now easily build a page that uses this new method like so:



SuppliersByCountry.aspx:



<%@ Page Language="VB" AutoEventWireup="false" EnableViewState="false" CodeFile="SuppliersByCountry.aspx.vb" Inherits="SuppliersByCountry" %>

Suppliers By Country:

Select a country:

SuppliersByCountry.aspx.vb:

Imports NorthwindTableAdapters



Partial Class SuppliersByCountry

Inherits System.Web.UI.Page



Protected Sub SearchBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SearchBtn.Click



Dim suppliersAdapter As New SuppliersTableAdapter



GridView1.DataSource = suppliersAdapter.GetSuppliersByCountry(CountryTxt.Text)

GridView1.DataBind()



End Sub



End Class

Which will generate a Suppliers search page:







Note that I did not have to write any ADO.NET code, manually create parameter collections, manage connection objects etc. I only had to write 3 lines of code total to build the above sample from scratch using the Visual Web Developer data and page designers.



Tutorial 5: Using Insert, Update, and Delete DBDirect Commands on TableAdapters



Because we kept the default “GeneratedDBDirect Methods” checkbox selected, the Create Table Adapter wizard automatically added default Insert, Update and Delete methods to the SuppliersTableAdapter. You can see these, as well as edit/customize them further, by selecting the SuppliersTableAdapter object within the DataSet Designer and then looking at the property-grid (note: you must select the SuppliersTableAdapter heading to have these methods show up in the property grid – they won’t show up if you just select the Suppliers heading):







Using the “CommandText” property in the property-grid, you can pull up a query designer for each of the default statements and customize them:







You can also optionally add your own custom Insert/Update/Delete methods to each TableAdapter. For example, if I wanted to add a custom Insert method that had the additional behavior of returning the new identity column value of a newly created Supplier row (which has an auto-increment property set for the primary key), I could do so by right-clicking on the SuppliersTableAdapter and choosing “New Query”:







I’ll then pick doing the INSERT command with a SQL statement, and choose to create an Insert:







The DataSet designer will then automatically suggest the below SQL statement for me (it looks at the SELECT statement you entered earlier to suggest a default INSERT statement):







Note that the “SELECT @@Identity” statement at the end will return the newly created primary key from the insert operation. I could further customize the INSERT operation however I want.



I can then use the wizard to name the method “InsertSupplier” (or any other name I want). The wizard will then add this method to the SuppliersAdapter. The last step I’ll do (since I want to return the @@Identity value from the insert), is to change the type of the InsertSupplier method from “NonQuery” to “Scalar”:







You could then write the code below to add a new supplier, and then update the supplier values, and then delete the supplier within the Suppliers table:

Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter



Dim supplierId As Integer



supplierId = supplierAdapter.InsertSupplier("Microsoft" _

, "ScottGu" _

, "General Manager" _

, "One Microsoft Way" _

, "Redmond" _

, "USA" _

, "98004" _

, "425-555-1212")



supplierAdapter.Update("Microsoft" _

, "Someone Else" _

, "New title" _

, "New Address" _

, "New City" _

, "UK" _

, "New Zip" _

, "New Number" _

, supplierId)



supplierAdapter.Delete(supplierId)

Note that I did not have to write any ADO.NET code, manually create any parameter collections, or manage connection objects etc. Because the data designer generates typed methods and DataTables, I’ll get both intellisense/compilation-checking as well as type validation within my DAL (so for example: if I try to pass an integer instead of a DateTime it would give me a compile error).



Tutorial 6: Using DataTables to Insert/Update/Delete Suppliers



Tutorial 5 above showed how to use data methods directly on the SuppliersTableAdapter to manipulate individual rows within our database. Alternatively, developers can also work to perform these operations using the SuppliersDataTable and SuppliersDataRow objects. These are particularly useful when adding/updating/deleting multiple rows at a time (these updates can optionally be batched to the database in one database call using this approach).



The below example demonstrates how to retrieve all of the suppliers in the US with a single database call, then conditionally update some of the supplier’s zip-codes, and then add a new supplier. We’ll then update the database with all of the additions/changes:

Dim supplierAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim suppliers As Northwind.SuppliersDataTable

Dim supplier As Northwind.SuppliersRow



' Obtain all Suppliers in the US

suppliers = supplierAdapter.GetSuppliersByCountry("USA")



' Loop through all suppliers and update any 98042 postcodes to 98004

For Each supplier In suppliers



If supplier.PostalCode = "98052" Then

supplier.PostalCode = "98004"

End If



Next



' Create a New Supplier Just for Fun

supplier = suppliers.NewSuppliersRow()



' Set new data properties on supplier2 row

With supplier



.CompanyName = "Microsoft"

.ContactName = "ScottGu"

.Address = "One Microsoft Way"

.ContactTitle = "General Manager"

.City = "Redmond"

.PostalCode = "98052"

.Country = "USA"

.Phone = "425-555-1212"



End With



' Add New Supplier to SuppliersDataTable

suppliers.AddSuppliersRow(supplier)



' Update Database with all changes (updates + additions)

supplierAdapter.Update(suppliers)

Note that there are fancier ways to-do filtering (and sorting) operations that I’m not using above, but I wanted to keep this tutorial simple as opposed to overload it with too many concepts.



Tutorial 7: Putting the Northwinds DAL Together



The above tutorials hopefully provide the basic background needed to create DAL’s using the DataSet designer and then use them from code.



Using the above knowledge, you should be able to go off and quickly create the following strongly-typed TableAdapters with associated data methods pretty easily (note that the designer and query builder can be used to avoid having to manually write any of the SQL queries below – and it will auto-suggest subsequent queries for each adapter after you add the first one).



SuppliersTableAdapter:



GetAllSuppliers:



SELECT SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM Suppliers



GetSuppliersByCountry:



SELECT SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM Suppliers

WHERE Country=@Country



GetSupplierBySupplierId



SELECT SupplierID, CompanyName, ContactName, ContactTitle, Address, City, Country, PostalCode, Phone

FROM Suppliers

WHERE SupplierId=@SupplierID



GetUniqueSupplierCountries



SELECT DISTINCT Country

FROM Suppliers



CategoriesTableAdapter:



GetAllCategories:



SELECT CategoryID, CategoryName, Description

FROM Categories



GetCategoryByCategoryId



SELECT CategoryID, CategoryName, Description

FROM Categories

WHERE CategoryId=@CategoryId



EmployeesTableAdapter:



GetAllEmployees:



SELECT EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM Employees



GetEmployeeByEmployeeID



SELECT EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM Employees

WHERE EmployeeID=@EmployeeID



GetEmployeesByManager:



SELECT EmployeeID, LastName, FirstName, Title, HireDate, Country, ReportsTo

FROM Employees

WHERE ReportsTo = @ReportsTo



ProductsTableAdapter:



GetAllProducts:



SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

(SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM Products




GetProductsBySupplierId:



SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

(SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM Products

WHERE SupplierID=@SupplierID



GetProductsByCategoryId:



SELECT ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, Discontinued,

(SELECT Suppliers.CompanyName from Suppliers where Suppliers.SupplierId=Products.ProductId) as SupplierName

FROM Products

WHERE CategoryID=@ CategoryID



Most of the above TableAdapters are pretty straight-forward and simple. The one that is a little more advanced is the ProductsTableAdapter. Specifically, there in addition to retrieving the Products table columns I’m also retrieving the CompanyName from the Suppliers table that maps to the Product’s SupplierId column (it has a foreign key relationship to the Suppliers table). The above SQL statement will add this as a read-only “SupplierName” column on our ProductsDataTable.



This will allow me to be more efficient from a database perspective when I want to build a list UI on top of my Products table, and save me from having to hit the database an extra time per-row to retrieve this value when displaying a product list on the site.



The good news is that the data designer can still infer enough from the product adapter’s SQL statements to still automatically generate the correct INSERT, UPDATE and DELETE commands for the ProductsTableAdapter. In cases where you are doing more advanced JOINS across multiple tables and merging results from multiple places, the data designer might not be able to generate these automatically. The good news is that you can still define these manually within the designer (just click on the table-adapter, and then within its property grid choose to create a new “Insert” (or update or delete) command and define your own logic).



When finished, my Northwinds data design-surface looks like this (note how the designer will automatically map and detail foreign-key relationships):







When I hit save on the Northwinds.xsd file (where all of the adapters, definitions and relations are declaratively stored in an XML file), I’ll be able to program and data-bind against any of the objects defined within it.



Tutorial 8: Adding Custom Code to the DAL



One of the nice things about the data designer is that the .xsd file that declaratively stores all of the relevant definitions gets translated into either VB or C# code at compile and runtime. This means that you can easily step through it within the debugger, and always understand “what is it doing for me under the covers”.



To easily see what code has been generated by the data designer, just open the “Class View” property window in Visual Web Developer or VS 2005 (if it isn’t visible select the View->Class View menu item), and navigate to the respective namespaces and types to see the object model of them. You can then right-click and choose “Browse Definition” to jump to the generated code for any method:







In addition to viewing the generated code, you can also add to it and extend it in many ways. You can do this either by sub-classing the generated objects, or alternatively by using the new “Partial” keyword feature now supported by the .NET 2.0 language compilers. The partial keyword enables you to add any method, property or events you want to the classes generated within the DAL, and they will be compiled together into a single generated type. Because the partial class code you write lives in a separate file from the files generated by the designer, you do not have to worry about the designer ever clobbering or overwriting your code.



To see a simple example of how you could use this feature, I could easily add an additional “GetProducts()” method to each row of Suppliers within my Northwind DAL (allowing me to easily obtain the ProductDataTable for the specific Supplier I have a reference to). Enabling this is as easy as adding a “SuppliersRow.vb” file in my app_code directory and adding this code to it:

Partial Public Class NorthWind



Partial Public Class SuppliersRow



Public Function GetProducts() As ProductsDataTable



Dim productsAdapter As New NorthwindTableAdapters.ProductsTableAdapter

Return productsAdapter.GetProductsBySupplier(Me.SupplierID)



End Function



End Class



End Class

This tells the VB compiler that I want to add a “GetProducts()” method to the SuppliersRow class (I’m using the “SupplierID” property on the supplier’s row to automatically figure out the subset of products by Supplier – note that I can write “Me.SupplierId” – since this method is part of the SuppliersRow class).



Once I add this I can then write this code to easily hierarchically list each product produced by each Supplier:

Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter

Dim suppliers As Northwind.SuppliersDataTable

Dim supplier As Northwind.SuppliersRow



suppliers = suppliersAdapter.GetAllSuppliers()



For Each supplier In suppliers



Response.Write("Supplier: " & supplier.CompanyName & "
")



Dim products As Northwind.ProductsDataTable

Dim product As Northwind.ProductsRow



products = supplier.GetProducts()



For Each product In products

Response.Write("------- Product: " & product.ProductName & "
")

Next



Next

And I could easily build this page using a and control to databind a list of suppliers, and then allow a user to click any of the suppliers to list a hierarchical drill-down of their products:







I’d do this by databinding the suppliers against the DataList:



Hierarchy.aspx.vb:

Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender



Dim suppliersAdapter As New NorthwindTableAdapters.SuppliersTableAdapter



DataList1.DataSource = suppliersAdapter.GetAllSuppliers()

DataList1.DataBind()



End Sub

And then using the “Select” feature of DataList to have the selected item template hierarchically retrieve and display the products (this way only the selected supplier’s products are retrieved):



Hierarchy.aspx:

Supplier/Product Drilldown:

Click a Supplier to List Its Products

<%#Eval("CompanyName") %>

<%#Eval("CompanyName") %>

• <%#Eval("ProductName")%>

Summary



Hopefully the above set of tutorials provides a useful (and somewhat exhaustive -- sorry for the length) walkthrough of the core features and capabilities you can easily take advantage of with the DataSet designer in Visual Web Developer and VS 2005 to build easy data access layers.



Over the next few weeks I’m going to be posting many (short) blog posting that walkthrough using the DAL I built above to implement common web data patterns using ASP.NET 2.0.



Hope this helps,



Scott



Published Sunday, January 15, 2006 4:27 PM by ScottGu
Filed under: ASP.NET, Data, SQL Server

Comments
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Sunday, January 15, 2006 8:42 PM by Henrik Sørensen (OPTION APS Denmark)
Hi Scott

Great sample. Something to the wish-list for your upcomming samples:

The same DAL exposed by WebServices including reallife errorhandling.... and an ASP.NET application consuming it.... would that be something???
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 12:08 AM by John Walker
Scott,

Excellent as usual!
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 1:06 AM by scottgu
Hi Henrik,

I'll add that one to the list! :-)

Thanks,

Scott
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 4:00 AM by Tom
another great tutorial thanks Scott, your tutorial are never long enough ;).

I was wondering something, generating DAL methods inside the designer and inside the Dataset is convinient to build quick application, however I would love to see something where you could also choose to generate a separate DAL that takes in as a parameter the dataset structure instead of n parameters and where you could just map the different parameters to the dataset columns. I believe that this would generate a much more versatile architecture with passing "messages" to the DAL instead of params. What do you think ?
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 4:36 AM by Duncan Smart
I'd recommend SCOPE_IDENTITY() rather than @@IDENTITY, unless something has changed with @@IDENTITY in SQL 2005.
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 7:00 AM by cowgaR
I don't know if you're on drugs(good one:) or you've on some dopping Scott? Excellent, now more than ever!

It looked good when you began rolling this articles but now you are Rock&Rolling man, and according to last statements there is more Heavy Metal on the way =)

wohoooo!
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 8:26 AM by Mick
Hi Scott great article.
It would be nice to have the same example using ObjectDataSource. I know you showed it in other posts, but having a step-by-step article is really useful.
A question, I tried the procedure above, but the dataset wizard generates only Insert ans Select Commands and not the Delete and Update. Any idea why ?
Thanks
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 9:32 AM by Florian Krüsch
Thanks for this one!

Still, I get to wonder what's the recommended way to deal
with table joins. Always create SQL views? Use 'virtual'
datatables that map to the query result instead of an underlying
table?



# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 9:41 AM by Mike
Nevermind, I found the problem with Update and Delete. It was because I forgot to add a primary key to the table I'm using. The wizard didn't give any warning though. I think it is worth mentioning it in the article.
Thanks again.
Mike
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 10:24 AM by scottgu
Hi Tom,

The TableAdapters generated actually generate 5 Update methods by default -- one that takes the single parameters (like the DBDirect approach I showed first), and then one that takes a DataSet, one that takes the DataTable, and then two that tae DataRows. So in terms of your question about passing DataSets as parameter types back to the DAL, the good news is that you are all set on that regard (my sample showing the second round of updates is actually using this approach -- where it is passing back the SupplierDataTable as an argument to the SupplierTableAdapter's Update() method).

One nice thing is that because the TableAdapters are maked as partial classes as well, you can add additional methods to them if you want. For example, you could define your own class "Supplier" that was completely independent of any Datable/DataSet/DataRow classes (and just used fields or properties to store supplier data) and then define an Update method on the TableAdapter that took it as an argument.

Hope this helps,

Scott
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 10:30 AM by scottgu
Hi Mike,

Pretty much all of the data samples I'll be doing next will show using the above DAL with the ObjectDataSource. I didn't want to introduce it in this article primarily to make clear that the above approach is not only appropriate for UI specific databinding scenarios.

I think sometimes people assume when there are lots of designers and wizards that you can't write code too. I wanted to show some procedural examples so people felt comfortable that you can use all of these features in standalone code scenarios as well.

Thanks,

Scott
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 10:39 AM by Steve
Great stuff - thanks Scott

I will need to archive this one - very valuable.

I didn't realize the typed dataset created a TableAdapters


# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 2:06 PM by karl
Scott, I'm a fan, but I continue to think the web team outputs too much of this stuff versus more difficult material on n-tier and domain driven design (granted, you didn't say "SqlDataSource" anywhere).

I'm not saying one is better than the other, and certainly in this simplified example this approach has a lot going for it, but overall the education hasn't been very balanced.
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 2:42 PM by Dan
Great article! I have one issue when converting example to c#.

in Hierarchy.aspx:
Container.DataItem.Row.GetProducts()

I get error saying object does not contain a definition for 'Row'

any idea? thanks!
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 3:03 PM by Christian
Hi Scott!

Great content in your blog!!

There are two things I'd really like to have as features for data in VS.Net:

1. Is it possible to drag&drop an existing stored procedure to the designer, so that it creates a strongly typed method? I mean a SP that does not SELECT a table, but just takes some nvarchars and ints and counts something and writes that back?

This functionality seems to be much more basic than what the designer provides, yet it is missing!

2. The datarows would be great business objects, e.g. if I want to edit a customer I could take the strongly typed data row and pass it around through remoting.
But unfortunately, I don't think that this works: Datarows can only live withhin Datatables.

I always wondered why this functionality is not existing in .NET! (Or is it?)
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 3:38 PM by Patrick S.
hello scott,

isn't it just bad design to have typed datacontainer (dataset) and dataaccess (tableadapters) glued together?

what about complex query-parameters instead of simple types?

it seems, this designer-stuff is limited, or do i miss something?

why there is no datasetdatasource anymore?

regards
pat
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 5:00 PM by Logic
Worked like a charm !
After reading this, I re-wrote an app I did a few weeks ago.
This is an excellent tutorial. Please keep it coming.
Thanks
Logic
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 6:39 PM by dotnetkicks.com trackback
www.dotnetkicks.com trackback
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 7:55 PM by Tyrone
Scott,

Not putting you on the spot or anything, but how about doing at least one of these walkthroughs via screencast?

By the way, great job on the walkthrough. The new dataset designer makes things alot cleaner and elegant than using previous versions. I'm probably going to start using them a little now.
# re: Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
Monday, January 16, 2006 8:03 PM by scottgu
Hi Karl,

My goal with the upcoming data tutorials is going to be to show how to-do things using the ObjectDataSource and binding to a middle-tier layer. Although I'm going to use the DAL I created in this tutorial as the one I interact with, the concepts I'll walkthrough are pretty applicable to any data object model.

In terms of what the best data model is to use, that is a bit of a hard one. My personal belief is that there isn't one "perfect" data story out there for every application. Instead, I think there *is* a perfect data story for each particular project and the development team building it -- and these vary depending on the exact scenario. Some projects/teams have very complex requirements, and so need a lot of depth and abstraction. Some projects/teams are smaller/simpler and a simpler/smaller abstraction might be better.

What we are trying to-do in ASP.NET is make sure that we have a story that works for all data access models and implementations -- and let teams choose amongst themselves as to what exact implementation works best for their needs.

Sealed Methods

Sealed Methods
You can also use the sealed keyword to declare that an individual method in an unsealed
class is sealed. This means that a derived class cannot then override the sealed method. You
can seal only an override method. (You declare the method as sealed override.) You can think
of the interface, virtual, override, and sealed keywords as follows:
􀁑 An interface introduces the name of a method.
􀁑 A virtual method is the fi rst implementation of a method.
􀁑 An override method is another implementation of a method.
􀁑 A sealed method is the last implementation of a method.