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

SQL .Net Consultation Session 6 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

Content of Presentation Creating web-applications with relational database system 6 • Creating forms of transaction tables • Representing 1:many MainForm:SubForm structures with Obout DetailGrid • Setting data sources • Formatting: scroll, column widths • Editing DetailGrid templates through fake Grid templates • Creating transaction forms with Obout Row Template • Setting data sources: base query of Basis- and Joined tables • Using Obout FolderStyles and DataFormatStrings • Setting all-in-one text in DropDownLists of foreign keys • Setting RowTemplates References

Category CatCode CatName SubCategory SubCatCode SubCatName CatCode PrivBrand SuperCatCode HierCatCode • CatSys • . • CatCode • CatName • SubCatCode • SubCatName • CatCode • PrivBrand. • SuperCatCode • HierCatCode Grid Main Categories GridMain base entity Categories GridDetail base entity Grid Detail Representing 1:many MainForm:SubForm structures with Obout DetailGrid 1 Click drag drop Usage of .DetailGrid pro-perty of Obout Grid enab-les us to create Form:Sub-form structures with 1:many related DataSource tables: • Related records of Detail- Grid can be opened from 1 record of main Grid • DetailGrid can also be pa-ged, scrolled, edited, can contain DropDownList for foreign keys and can group records dragging field name into grouping area • We use tables of database PopsCategorySys.bak as source (Category-basis, SubCategorysubform, see logical plan of GUI in ADD) First we create the aspx form PopsCategorySys.aspx with title text and add: • SqlDataSourceCategory using PopsCategorySys-ConnectionString and table Categories with auto-generated SELECT,INSERT UPDATE,DELETE queries Click Click Click Click Click Click Click Click Click Click Click Click SELECT * FROM Categories UPDATE Categories SET CatName=@CatName WHERE CatCode=@CatCode DELETE FROM Categories WHERE CatCode= @CatCode INSERT INTO Categories (CatCode, CatName ) VALUES ( @CatCode, @CatName)

SubCatego SubCatego Click Click Click Click Obout DetailGrid 2 Click SELECT [SubCatCode], [PrivBrand], [SubCatName], [SuperCatCode], [CatCode], [HierCatCode] FROM [SubCategories] WHERE [CatCode]= @CatCode INSERT INTO [SubCategories] ([SubCatCode], [PrivBrand], [SubCatName], [SuperCatCode], [CatCode], [HierCatCode] ) VALUES ( @SubCatCode, @PrivBrand, @SubCatName, @SuperCatCode, @CatCode, @HierCatCode) Click Click • We also add SqlData- SourceSubCategory using the same con- nection string • First, we auto-generate its queries from table SubCate-gories, but then at .SelectQuery property of datasour-ce we manually include a WHERE condition which filters subform sourcetable by primary key control of mainform • Please note that in case of AccessDataSource, you have to use „?” instead of „@CatCode” in WHERE! • Next,we add Obout Grid named GridCategory and set: • .DataSource=SqlDataDataSourceCalegory • .ScrollWidth×Height=964×432px:it defines a scroll window for grid filling most of the 1024×768 screen • .PageSize=-1:by default, paging disabled, it shows all records – page loads slower, but handling faster • .AllowPageSizeSelection=True:user can switch back to showing 5,10,15…records and page it • .Width=100%:use all of the scroll window • At .Columns: • Add new • DataField= write fieldname • .Width=128: col. width, px • .Wrap=True: wrap text in column in multiple lines DELETE FROM [SubCategories] WHERE [SubCatCode]= @SubCatCode UPDATE [SubCategories] SET [PrivBrand]=@PrivBrand, [SubCatName]=@SubCatName, [SuperCatCode]=@SuperCatCode, [CatCode]=@CatCode, [HierCatCode]=@HierCatCode WHERE [SubCatCode]=@SubCatCode .Header Text can be set some-what mo –re mea-ningful! Click Click Click Click

2 Obout DetailGrid 3 Click Click Click Click Click At GridCategory.DetailGrids: • Add new, don’t try to rename its ID!!! • .DataSourceID=SqlDataSourceSub-Category, .ForeignKeys=CatCode this connects Grid and DetailGrid • Please note that foreign- and primary key fields should have identical names • .AllowGrouping=True:DetailGrid can be grouped dragging any of its fields into its group area runtime • At .Columns: • Add new, set its DataField, Wrap, Header, Width:setting field widths consider that DetailGrid is 32px narrower than its Grid! • At .Templates:templates of DetailGrid should be defined here, instead of do-ing in GridCategory.Templates: • Add new, do not rename its ID!!! • .ControlID= planned control name • .ControlPropertyName=Value • Please note that template defined at DetailGrid cannot be edited in graphic template editor, therefore: At GridCategory.Templates: we define a fake template,just for edit it,not to use: • Add new, don’t try to rename its ID!!! • .ControlID= planned control name • .ControlPropertyName=Value At GridCategory|RightClick|EditTemp-late|GridCategory templates: • Create DropDownListCategory: it isnamed as planned control name! • .DataSource=SqlDataSourceCategory • .DataValueField=CatCode • .DataTextField=CatName Click Click drag drop Click Click Click Click Click right click Click drag drop Click GridCategory Templates

<Templates> <cc1:GridTemplate ID="GridTemplate1" runat="server" ControlID="DropDownListCatCode" ControlPropertyName="value"> </cc1:GridTemplate> </Templates> … <Templates> <cc1:GridTemplate ID="GridTemplate2" runat="server" ControlID="DropDownListCatCode" ControlPropertyName="value"> <Template> <asp:DropDownList ID="DropDownListCatCode" runat="server" BackColor="Blue" DataSourceID="SqlDataSourceCategory" DataTextField="CatName" DataValueField="CatCode" Font-Names="Arial Narrow" Font-Size="8pt" ForeColor="Yellow"> </asp:DropDownList> </Template> </cc1:GridTemplate> </Templates> COPY Obout DetailGrid 4 Then, open PopsCategorySys.aspx code • In the code, simply copy the content of the fake template of GridCategory („GridTemplate2”) into the empty template of the DetailGrid („Grid-Template1”), which cannot be gra-phically edited directly Then go back to GridCategry.DetailGrid • At .Columns.CatCode (this is the fo-reign key we want do define a Drop-DownList to show primary key values): • At .TemplateSettings: • Edit Mode=GridTemplate1 • We define all other DetailGrid temp-lates and lookup tables for other fore-ign keys the same way. Running the DetailGrid should show up clicking (+) buttons, pressing Edit link at its record will show DropDownLists • Please note that changing value of the foreign key linking sub- formtable to main formtable is possible for the user! It will switch the SubCategory into ano- ther Category. How- ever there is a small bug in Obout Grid, that it shows up in its new place only after pressing the Refresh button of the web-browser! • To block switching, set the key filed ReadOnly Click Click Click Click Click

Practice 6-1: Using DetailGrid of DetailGrid TASK: Create DetailGrid of DetailGrid showing content of Products table under SubCategories. • Please note that hierarchy of DetailGrids will collapse and gets destroyed if any of the 1:many relations linking data source tables in a chain does not comply referential integrity (has outlier records at many side) • In database PopsCategorySys.bak the 1:many relation SubCategory:Product does not comply referential integrity, because there are Null values in its foreign key. Resolve the situation creating SubCategory 0 Other (which should belong to Category 0 Other) in MS SQL and update all Null-valued Products.SubCatCode into 0 (zero) (1pts) • We call this trick fake-record referential integrity-handling (Hamis rekordos referenciális integritás zárás) • Build DetailGrid for Products with all necessary templates and DropDownLists (1pts)

Creating transaction forms with Obout Row Template 1 Click Click • An Obout Grid can show many records (25-30) in one screen without scrolling/paging, but only limited number of fields (too many fields will result in tiring horizontal scrolling) • Row template (Sor Minta) is a custom-lay-out form which pops up at editing a record and can show numerous fields without scrolling the screen. We will show its usage in Invoicing.bak data-base, where we would like to see all data from tables Buyers, Sellers, SalesPer-sons, and Invoices together in the section named„Head”of an invoice form(Számlafej): • Create PanelTransactionTables in Invoicing.aspx, where: • Create LinkButtonInvoice pointing to • Create Invoice.aspx with title text, where: • Create SqlDataSourceInvoicesFull: • Use InvoicingConnectionString • Auto-generate all queries using Invoices as base table Click Click Click Click Click Click Click Click • We will modify SELECT query manually from its .SelectQuery property to include Joined tables (see Lesson4) connected to base table with 1:many relationship Click

Press Edit Query button to open graphic editor, RightClick on Links Pane to add joined tables • Relations will appear as joins between tables, set them to INNER JOIN Clicking the ( ) button • Add necessary extra relations with Drag&Drop joinfields in each other • Check fields in tables to enter them into SELECT part of query • As fields from several tables/alias tables can have very similar names (eg. .HouseNum), rename them with AS to more specific name (eg. .BuyerHouseNum) Basis queries in SQL 1 SELECT Invoices.InvoiceID, Invoices.InvoiceNum, Invoices.ItemCount, Invoices.NetTotalVal, Invoices.GrossTotal, Invoices.Paid, Invoices.IssueDate, Invoices.BuyerID, Invoices.SalesPersID, Invoices.SellerID, Invoices.Modifier, Invoices.Modified, Invoices.Status, Buyers.FirstName, Buyers.LastName, Buyers.AddressID AS BuyerAddressID, Addresses.Floor AS BuyerFloor, Addresses.Building AS BuyerBuilding, Addresses.HouseNum AS BuyerHouseNum, Addresses.Street AS BuyerStreet, Addresses.StreetTypeID AS BuyerStreetTypeID, StreetTypes.StreetTypeDescr AS BuyerStreetTypeDescr, Addresses.ZipID AS BuyerZipID, Zips.City AS BuyerCity, Zips.CountryID AS BuyerCountryID, Countries.CountryDescr AS BuyerCountryDescr, Sellers.SellerName, Sellers.SellerTaxReg, Sellers.LegalFormatID AS SellerLegalFormatID, LegalFormats.LegalFormatDescr AS SellerLegalFormatDescr, Sellers.AddressID AS SellerAddressID, Addresses_1.Door AS SellerDoor, Addresses_1.Floor AS SellerFloor, Addresses_1.Building AS SellerBuliding, Addresses_1.HouseNum AS SellerHouseNum, Addresses_1.Street AS SellerStreet, Addresses_1.StreetTypeID AS SellerStreetTypeID, Addresses_1.ZipID AS SellerZipID, Zips_1.City AS SellerCity, Zips_1.CountryID AS SellerCountryID, StreetTypes_1.StreetTypeDescr AS SellerStreetTypeDescr, Countries_1.CountryDescr AS SellerCountryDescr Click Click drag drop

INSERT INTO [Invoices] ( [InvoiceNum], [ItemCount], [NetTotalVal], [GrossTotal], [Paid], [IssueDate], [BuyerID], [SalesPersID], [SellerID], [Modifier], [Modified], [Status] ) VALUES ( @InvoiceNum, @ItemCount, @NetTotalVal, @GrossTotal, @Paid, @IssueDate, @BuyerID, @SalesPersID, @SellerID, @Modifier, @Modified, @Status ) • Never modify FROM part of base query manually as multiple, nested INNER JOIN syntax can be quite a complicated • Instead of that, let the graphic designer to write • As the form usually just reads joined tables to merge descriptions of foreign keys in the base table (eg. BuyerID, SellerID, SalesPersID), and it modifies only the base table, auto-generated INSERT, UPDATE, DELETE queries will be fine Basis queries in SQL 2 FROM Invoices INNER JOIN Buyers ON Invoices.BuyerID = Buyers.BuyerID INNER JOIN Addresses ON Buyers.AddressID = Addresses.AddressID INNER JOIN StreetTypes ON Addresses.StreetTypeID = StreetTypes.StreetTypeID INNER JOIN Zips ON Addresses.ZipID=Zips.ZipID INNER JOIN Countries ON Zips.CountryID = Countries.CountryID INNER JOIN Sellers ON Invoices.SellerID = Sellers.SellerID INNER JOIN Addresses AS Addresses_1 ON Sellers.AddressID = Addresses_1.AddressID INNER JOIN StreetTypes AS StreetTypes_1 ON Addresses_1.StreetTypeID = StreetTypes_1.StreetTypeID INNER JOIN Zips AS Zips_1 ON Addresses_1.ZipID = Zips_1.ZipID INNER JOIN Countries AS Countries_1 ON Zips_1.CountryID = Countries_1.CountryID INNER JOIN LegalFormats ON Sellers.LegalFormatID = LegalFormats.LegalFormatID UPDATE [Invoices] SET [BuyerID] = @BuyerID, [SalesPersID]= @SalesPersID, [SellerID] = @SellerID WHERE [InvoiceID] = @InvoiceID DELETE FROM [Invoices] WHERE [InvoiceID] = @InvoiceID

.ob_gCP /*.ob_gridCellPadding*/ { width: 1px; float:left; font-family: Verdana; font-size: 10px; } Obout FolderStyles, DataFormatStrings .ob_gRC /*.ob_gridResizableContent*/ { overflow: hidden; white-space: nowrap; padding-left: 1px; /*text-overflow: ellipsis;*/ } .ob_gRCW /*.ob_gridResizableContentWithWrap*/ { overflow: hidden; white-space: normal; padding-left: 1px; } Create an Obout Grid named GridInvoice on Invoice.aspx form: • .DataSource =SqlDataSourceInvoicesFull • Do the usual scrolling, paging, width settings • .Columns =Add all fileds of the datasource, but: • Fields from joined tables are .ReadOnly=True • At less important fields we do not want to show in Grid,only in row template,set .Visible=False • Set .DataFormatString to format appearance of fraction number, currency and date fields: • Syntax: {LocalizationCode:Formatmask}LocalizationCode=0 for default locali-zation defined by international settings of OS (WinXP|Control panel|International) • {0:00.00}:fraction number with fixed digits • {0:C}:currency format of given localization • {0:yyyy.MM.dd}:Hungarian date format (See references for further format settings) • Set .Width and .Wrap. Please consider that if you have lot of columns to show on Grid, try to be as compact as possible. However left/right padding (Behúzás) of fields cannot be set from their properties, therefore: • We select an Grid style description directory from C:\Obout\Grid\Styles and copy it into the root directory of our website as OboutGridStyle • We change left/right padding of CSS styles ob_gCP, ob_gRC, ob_gRCW in its style.css file with any word processor (Szövegszerk.) • If necessary, we edit *.gif images of style building the grid with any image editor, then: • .FolderStyle=OboutGridStyle: the style changes will show up in the Grid • Please note that you should avoid deleting a field from .Columns collection of the Grid: it destabilizes and slows down Visual Studio incredibly because of Obout Grid bugs. Select fields carefully! Click Click Click

SELECT Buyers.BuyerID, Buyers.FirstName + ' ' + Buyers.LastName + ' ' + Addresses.HouseNum + 'th ' + Addresses.Street + ' ' + StreetTypes.StreetTypeDescr+' ' + Zips.City + ' ' + Countries.CountryDescr AS BuyerString FROM Addresses INNER JOIN Buyers ON Addresses.AddressID = Buyers.AddressID INNER JOIN StreetTypes ON Addresses.StreetTypeID = StreetTypes.StreetTypeID INNER JOIN Zips ON Addresses.ZipID = Zips.ZipID INNER JOIN Countries ON Zips.CountryID = Countries.CountryID SELECT Sellers.SellerID, Sellers.SellerName + ' ' + LegalFormats.LegalFormatDescr+' ' + Addresses.HouseNum + 'th ' + Addresses.Street + ' ' + StreetTypes.StreetTypeDescr + ' ' + Zips.City + ' ' + Countries.CountryDescr AS SellerString FROM Addresses INNER JOIN Sellers ON Addresses.AddressID = Sellers.AddressID INNER JOIN StreetTypes ON Addresses.StreetTypeID = StreetTypes.StreetTypeID INNER JOIN Zips ON Addresses.ZipID = Zips.ZipID INNER JOIN Countries ON Zips.CountryID = Countries.CountryID INNER JOIN LegalFormats ON Sellers.LegalFormatID = LegalFormats.LegalFormatID SELECT SalesPersID, FirstName + ' ' + LastName AS SalesPersString FROM SalesPersons All-in-one texts in DropDownLists We create SqlDataSources with special queries for DropDownLists used on RowTemplatelater: DataSourcewill contain primary key of 1 side tab-le and their description with all des-cription fields of all joined tables added together to create more informative DropDowns

Defining RowTemplate Click Click Click At GridInvoices: • At .Templates, Add new template, but don’t try to rename it and do not set anything at its .ControlID and .ControlPropertyName property! • At .TemplateSettings, set RowEditTemplate=GridTemplate1 This defines the template for the whole record,not just 1column Then, we have to edit the template: • With RightClick|Edit templates|GridInvoices Templates: • Add necessary form title and field prompt text • Create DropDownListBuyerID with the following settings: • .DataSourceID=SqlDataSourceBuyersLookup • .DataTextField=BuyerString (with full description) • .DataValueField=BuyerID (primary key field) • Create DropDownListSellerID with the following settings: • .DataSourceID=SqlDataSourceSellersLookup • .DataTextField=SellerString (with full description) • .DataValueField=SellerID (primary key field) • Create DropDownListSalesPersID with settings: • .DataSourceID=SqlDataSourceSalesPersLookup • .DataTextField=SalesPersString (full description) • .DataValueField=SalesPersID (primary key field) • At .Columns.BuyerID (a foreign key field in Grid): • .TemplateSettings|RowEditTemplate: • ControlID=DropDownListBuyerID (Should match!!!) • PropertName=Value We connect the field to a RowTemplate control here.Do the sa-me for SellerID and SalesPersID Click Click Click right click Click Click Click drag drop Click Click

Practice 6-1: Using DetailGrid with RowTemplate TASK: Create a DetailGrid of GridInvoice showing content of Items base table and all of its joined tables (Products, ITJs, VATs, MeasUnits) (1pts) • Fields of DetailGrid should be edited by user in a RowTemplate form. Be careful about that row template of DetailGrid cannot be edited directly, use the fake template trick! (1pts)

References • Obout Grid Control reference: http://www.obout.com/grid/

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