SQL .Net Consultation Session 5 Dr. Gábor Pauler, Associate Professor, Private Entrepeneur

1. SQL .Net Consultation Session 5 Dr. Gábor Pauler, Associate Professor, Private Entrepeneur Tax Reg. No.: 63673852-3-22 Bank account: 50400113-11065546 Location: 1st Széchenyi str. 7666 Pogány, Hungary Tel: +36-309-015-488 E-mail: pauler@t-online.hu

2. Content of Presentation Creating web-applications with relational database system 5 • Creating main form: panels and link buttons • Creating forms for code tables: form with single table data source • Configuring SqlDataSource object • Usage of DataGridView and DetailsView objects • Creating forms for master tables: form with single table data source and foreign keys • Download, install and import Obout .Net component • Obout Grid, ComboBox • Manage and edit Obout Grid templates to create dropdown lists for foreign keys • Setting auto-query generation in .Net SqlDataSource • Manual query generation in query editor References

3. Creating main form Its time to build up web-based GUI in Aspx .Net for our sample database Invoicing created at Session4: • First we planned it in MS Visio as ERD Invoicing.vsd • Then we generated as SQL script Invoicing.DDL • Then we run the script in MS SQL to create database, imported data of code and master tables, and backed it up as Invoicing.bak • Then we created logical design of GUI in Lesson4 • In the followings we assume that MS SQL server process + Invoicing database is running In the first step we create a main/master page/form (Főűrlap) which usually contains: • Logo and graphics of designer • Username+Password entry • Linkbuttons to other thematic forms of GUI • Exit/logoff button Create Invoicing.aspx form: • Title = „Invoicing” • Add necessary comment text • Within Invoicing, create PanelCodeTables • BorderStyle = Solid • BorderWidth = 1px • Add necessary comment text • Within it, create LinkButtonStreetType • Text = „Street type” • PostBackURL = ~/StreetType.aspx • You may notice that naming of linkbuttons and referenced thematic forms strictly follows name of their base entity to keep form naming consis-tent. Never use „Page1, Page2”-style form names, because you will mess them up when you will have 80-100 forms! Practice 5-1: Create another 4 linkbuttons referencing to proposed forms of code tables (0.5pts) Click

4. Click Creating forms for code tables 1 Click Click Click Click Click Click Data Source=PG-LAPTOP; Initial Catalog=Invoicing; Integrated Security=True Click • Code tables (Kódtáblák) are small-sized tables containing a numeric artificial primary key and its text description and does not contain any foreign keys. As their content cahnges in time very seldom, they do not contain logging fields (Modifier, Modified, Status) They are usually filled by developer before publishing the appli-cation,and usually cannot be Updated/Deleted by normal users (but often Insert is allowed) • So their GUI is less important, but very simple (has single table data source, and form will contain only textbox controls) so we start here • Creation of simple code table maintenance (In-sert/Update/Delete) forms is very well suppor-ted in .Net, no need of manual coding here: Create StreetType.aspx form: • Title = „StreetType” • Add necessary comment text • Within it, create LinkButtonBckInvoicing • Text = „Back to main page” • PostBackURL = ~/Invoicing.aspx • Within it, create SQLDataSourceStreetType. It connects MS SQL database with data controls. Click on its intelligent label|Configure wizard: • Press New Connection button, on its panel • Select MSSQL,give Sever name, DBname • Press Test Connection to log into server • Save connection into system config • Select table StreetTypes and all of its fields Click Click Click Click Click Click Click Click Click Click SELECT [StreetTypeID], [StreetTypeDescr] FROM [StreetTypes] • Press Advanced and check generate Insert/Update/Delete statements Of course the SqlDataSource can be con-figured from VB code also. Please note that configuring MS Access, XML, ODBC and other data sources usually have more difficult syntax Imports System.Data.OleDb Dim SqDSrc1 As SqlDataSource = New SqlDataSource() SqDSrc1.ID="SqlDataSourceStreetType" SqDSrc1.SelectCommand="SELECT[StreetTypeID],[StreetTypeDescr] FROM [StreetTypes]" SqDSrc1.ConnectionString="DataSource=pg-laptop;Initial Catalog=Invoicing;Integrated Security=True" SqDSrc1.ProviderName="System.Data.OleDb"

5. Creating forms for code tables 2 Click Click Click Click Click After completing data source wizard, we create a GridView control, which shows content of data source in tabular format, and configure it from its intelligent label menu: • Choose data source = SqlDataSourceStreetType • Enable Paging, Sorting, Editing, Selection (we usually do not allow Deleting from code tables, because referential integrity closes it out) • At Edit columns: • Select all of BoundFields,click Add button • Select the extra CommandField added to existing fields. It will contain links for auto-generated java scripts manipulating the given record: • Show Insert/Edit/Delete/Select button • InsertVisible: inserted record is visible • At artificial primary keys(eg.StreetTypeID): • ReadOnly = True: cannot write because of identity setting of field • InsertVisible = False: field cannot be inserted being auto-generated number • At normal data fields(eg.StreetTypeDescr): • ReadOnly = False: if user can write • InsertVisible = True After setting these, GridView should work fine: Click Click Click Shift Click Click Click Click Click Click Click Click Click Click Click Click Click • With Auto-generate, you can do it 1 step • Refr.schema passes changes of database

6. Creating forms for code tables 3 Click Click • Please note that GridView control does not query the database itself. Instead of that, it calls SQL queries (SQL nyelvű lekérdezés) or stored procedures (Tárolt eljárás) – a set of SQL or procedural commands copiled together into one executable – of the SqlDataSource defined at its .Insert, .Update, .Delete, .Select properties in property editor. • The data source wizard auto-configures these queries if generate Insert/Update/Delete statements is checked, but they can be modified manually • While GridView could show many records (25-40) in one page, it can show only limited number of fields (5-10) without forcing the user to scroll screen constantly • Therefore we can use DetailsView control also, which shows only one record on one page but with many (70-80) fields without the need of scrolling. We show an example for its use creating Country.aspx: • We add necessary comment text • We create LinkButtonBckInvoice pointing back to main form • We create SqldataSourceCountry using the same connection we already defined previously (pg-laptop.invoicing.dbo) and representing Countries table of Invoicing • Then we create DetailsView1 using Sql-DataSourceCountry and enabling Paging, Inserting, Editing (but not Deleting) • Edit fields works pretty much the same as above, but DetailsView has more reliable inserting than GridView, which often stucks Click • After field definition, we can run the form and it should work fine both in update an insert mode: Click Click Click Click Click Click Click Practice 5-2:Create code table forms for: • Legal formats (in tabular format), • Measure units (in tabular format), • VATs (in details format), • Connected to corresponding code tables of Invoicing database • Referenced by LinkButtons on PanelCodeTables of Invoicing main form (1.5pts)

7. Content of Presentation Creating web-applications with relational database system 5 • Creating main form: panels and link buttons • Creating forms for code tables: form with single table data source • Configuring SqlDataSource object • Usage of DataGridView and DetailsView objects • Creating forms for master tables: form with single table data source and foreign keys • Download, install and import Obout .Net component • Obout Grid, ComboBox • Manage and edit Obout Grid templates to create dropdown lists for foreign keys • Setting auto-query generation in .Net SqlDataSource • Manual query generation in query editor References

8. About Obout: download, install and import its .Net components • Obout Inc. is a manufacturer of highly sophis-ticated .Net components downloadable from http://www.obout.com/ in the install package oboutSuite20081003.exereducing manual co-ding need compared to MS .Net components • Install package in a given directory (eg. C:\ Obout) and run Default.aspx to see working examples of Obout.Net controls grouped into programming tasks both in Asp,C#, and VB • To use examples, import controls into Visual Studio by Toolbox|Data tab|Rightclick| Choose item opening .Net components list • There click Browse and select component *.dll file from C:\Obout\bin directory,click OK • Imported controls can be seen in Toolbox, from where you have to Drag them once into any of the forms of your website and Save all • This will import control *.dll files into \bin di-rectory the site, check it in Solution explorer • Now you can add example form of Obout to your site with SiteName|Rightclick|Add ex-isting item|C:\Obout\Controlname\Exaple name.aspx As Obout controls have many properties with sometimes misleading names, copy examples carefully to get them working Click Click Click Click Click Add existing item… right Click right Click Click Click Click Drag Click

9. Creating forms for master tables 1 • Master tables (Törzstáblák) can be big sized, but they change slowly in time, so they do not contain logging fields (Modifier, Modified, Status). • However they contain foreign keys referencing to code/lookup (Kinézegető) tables: as referential integrity of these relations are usually checked, they wont accept values not represented in the referenced primary key of the one side table (eg. you cannot write a non-existing CountryID in Zips table) • Therefore foreign keys should not be simple TextBox controls, but they should be DropDownLists or ComboBoxes, where items of the list are automatically merged from one side table (eg. Countries), this way user cannot mistype the code • As primary keys are usually identity-generated numbers, it is hard to remember their meaning for the user. Therefore the list should show some text description for the items, not the code itself it writes back in foreign key field • Built-in MS .Net GridView and DetailsView controls do not support showing DropDownLists in record editing mode, therefore they require lot of additional manual coding So we will build the first master table form of our Invoicing web application using the imported Grid control of Obout: • First create PanelMasterTables on Invoicing.aspx form • BorderStyle = Solid • BorderWidth = 1px • Add necessary comment text • Within it, create LinkButtonITJ • Text = „ITJ” • PostBackURL = ~/ITJ.aspx • Create ITJ.aspx form. It is a master form which has a foreign key referencing to VATs table • Title = „ITJ” andaddcomment text • Add LinkButtonBckInvoice .Text=„Back to main” .PostBackURL = ~/Invoicing.aspx • Within it, create SQLDataSourceITJ. This will be the data source of the master form • Connection=InvoicingConnectionString • Select all fields of ITJs table • Press Advanced and check generate Insert/Update/Delete statements • Within it, create SQLDataSourceVAT. This will be the data source of lookup table • Connection=InvoicingConnectionString (we reuse the same connection!) • Select all fields of VATs table • Do NOT check generate Insert/Update/Delete here! Click Click

10. Click Edit Templates Creating forms for master tables 2 Click Click memori-ze field names! Click Click Click Click Click • Add new Obout Grid to ITJ.aspx • In its intelligent label:set its da-ta source to SqlDataSourceITJ • Click Edit Columns panel: • Add all columns of ITJs • Fill their DataField • Allow Select,Add,Sort,Page • At Manage template panel: • Add new template • Set ID=EditVAT (it will be a template for editing VATID) • Set Control=ddlVAT(it will use a control with this name) • Set Property=Value (it will be the input) • At Edit templates panel: • Add a DropDownList • In intelligent label: • Edit databind: Select.value, Custom=No • Data source: =SqlDataSour- ceVAT Display= VATPercent Value=VATID • In Properties set its ID=ddlVAT • At Edit columns panel: • VATID|Col.template: • Edit mode=EditVAT Run and enjoy VATID lookup table! sometimes it cannot be renamed,do not force it, use original! Click Click you have to find out name in advance Click Click Click Drag Template controls cannot have fix data binding! Click Click Click Click Click Click Field to show in droplist Click Field to put in foreign key Click Click Match up with name given in EditVAT. ControlID Click Click Click Click Click

11. Creating forms for master tables 3 Click Click One can see that lookup table for VATID works fine, but it is somewhat confusing that the field in view mode (when not edited) will contain numeric codes undezipherable for the user: • We could solve this problem assigning EditVAT template as View template of VATID field in its Column template property. However it will case type mismatch runtime error in Grid • An alternative solution is that description of VATID should be added to the Grid as a new column. However VATPercentdescriptor field (Leíró mező) can be found in different table (VATs) than data source table of the master table form (ITJs). Therefore we have to learn about how to use multiple tables as data source of a form queried together in a viewtable (Nézet tábla). We will see it creating form Zip.aspx for master table ZIPs, which has a lookup table Countries: • On PanelMasterTables of Invoicing.aspx form • Create LinkButtonZIP • Text = „ZIPs” • PostBackURL = ~/ZIP.aspx • Create Zip.aspx form: • Title = „ZIP” andaddcomment text • Add LinkButtonBckInvoice • Text=„Back to main” • PostBackURL = ~/Invoicing.aspx Click Click

12. Click Click Creating forms for master tables 4 Click Click Click • Create SQLDataSourceZIP. This will be the data source of the master form • Connection=InvoicingConnectionString • Select Specify SQL to give query of viewtable: • Click Select/Update/Insert/Delete to specify queries of datasource called at different data control operations: • Click Query Builder to open graphic query designer of MS SQL server: • Add tables with Rightclick|Add table • Tables will inherit joins from defined relations, but extra joins can be defined Drag&Drop join fields • Fields of tables can be Pulled into query assembly grid’s Column part • Set Alias for rename fields, check Output • Prepared query can be seen and edited in SQL panel • Press Execute query to check resulting viewtable • Important: defining manual queries one should not use autogenerate Insert/ Update/ Delete statements and Optimistic concurrency from Advanced panel of SqlDataSource wizard!!! • Within Zip.aspx form, create SQLDataSourceCountries. This will be the data source of lookup table • Connection=InvoicingConnectionString (we reuse the same connection!) • Select all fields of Countries table • Do NOT check generate Insert/Update/Delete here! right Click Add table… Drag Drag Click Click UPDATE [Zips] SET [City]=@City, [CountryID]=@CountryID WHERE [ZipID]=@ZipID INSERT INTO [Zips] ([ZipID],[City],[CountryID]) VALUES(@ZipID,@City,@CountryID) DELETE FROM [Zips] WHERE [ZipID]=@ZipID

13. Creating forms for master tables 5 Click Click Click • Then we apply Obout Grid control and its edit template in the same way as before • Running the form one can see that if value of the foreign key (CountryID) is updated with the lookup table, the Grid requeries its content from view table and foreign key description field (CountryDescr) will be refreshed • Changing the Select query of basis data source including some computed fields there, we can perform computations with Grid reacting on user input instantly • Even better solution is to define the view table in MS SQL already and assign it to the data source SqlDataSourceZip. This way, computed fields can be reached from database also • The disadvantage of this solution is that in this case Update/ Insert/ Delete queries cannot be generated automatically, their definition will be totally manual • Moreover, if we would like to modify more source tables of the view table, we have to write separate Update/ Insert/ Delete queries for each of them. • But coding in SQL is still 20 times faster than manual coding in VB. Therefore we will broaden our SQL knowledge in the next part. Practice 5-3: Create master table form for Products table in Invoicing database with all its necessary lookup tables, and descriptor fields! (2pts)

14. References .Net Visual Basic reference: http://msdn.microsoft.com/en-us/library/sh9ywfdk.aspx