There are lots of retail outlets like mine where one or two americans are accountable for every little thing and they are very busy making an attempt to learn new technologies and push the enterprise forward. My assignment is very large. it is going to eventually change 90 % of the green-reveal menu pushed device they at present use. i'm engaged on this venture on my own and constructing it slowly from the ground up. I want to have a well-built base. I need to be aware how .net and DB2 work together earlier than I delivery building an awful lot on right of the base program. i am working with just a few iSeries data to peer how issues work within the grids and forms. i am looking for some assist with a few issues:
1. physical and Logical files–Our iSeries data are developed above all from DDS. Some actual files don't also have a key, an awful lot much less a different key. VB.net is pretty a lot forcing me to have a different identifier on the file with the intention to enforce integrity through the information adapters. however their logical information don't seem to be always unique (like their heritage info). So, what do they do to get them into the VB application and perhaps replace them? can they rebuild them the use of SQL statements and drive a unique identifier at the conclusion of the file? I haven’t had much luck with that one in my testing. do they create new logicals built principally for VB.net? That’s a headache.
additionally, there’s the composite key situation. I haven’t had any luck constructing data adapters over information that have composite keys. I had one guy inform me to most effective use the actual info from the iSeries and not use any logicals. He also noted I vital to installation business good judgment to tackle all DB2 transactions. That is way beyond my scope.
2. Joined info–I actually have a necessity to join three info and reveal items of each and every in a grid for the person to come to a decision if commission funds might be issued on these income. The consumer will make a decision and mark a checkbox on the grid row. I desire clients to replace one of the crucial files (ARTRAN) that includes a flag to inform the system to pay commissions on the sale. I acquired to the checkbox half and all of it appeared extremely good until I found accessible was no solution to push the updates again through the dataset to DB2. I scrapped it and commenced over. (here's my lifestyles story for the past 12 months with VB and DB2.)
I should say that I accomplish that many different issues all over the day I actually have a hard time conserving my focus on VB.net. It takes loads of time to plow through the cyber web for extra information. Any perception you can provide might be advantageous.
For the checklist, we’re the use of visual Studio 2008 with the IBM .net information provider.
be aware: The code accompanying this article is available for down load here.
I feel your pain along with your struggles of keeping up with .net construction innovations! It’s challenging staying abreast of enterprise suggestions, present technology, and new expertise. So I’ll do my most desirable to answer your questions. For readers who're unfamiliar with the .web web atmosphere and the thought of using a grid control to replace a database desk, I imply reviewing my article device i developers and .internet 2.0: ASP.net and the Declarative Programming model. this text offers a popular introduction to Microsoft‘s visible Studio IDE (version 2005 or greater) for constructing internet functions. In specific, it illustrates a way to increase a web page that uses a different manage called the “GridView” to without difficulty update a DB2 database table from a web browser based software.
Now to the concerns! Having an precise interesting key described on a table is not required for doing database updates from a .web Grid manage. The entertaining key requirement that you frequently hear about actually refers to having a special identifier on one or more columns in the table. if your table has one or greater fields that may uniquely establish a row, then which you can safely replace it from a .internet Grid control although the database doesn’t enforce the forte. youngsters, if possible, i recommend constructing a special key on each table. Having a different index or primary key constraint will give you the advantages of more advantageous query optimization and may permit one of the crucial .net facts wizards to instantly establish the exciting key columns for you. (be aware that with legacy applications here is not always feasible.)
although a desk doesn’t have uniquely identifiable rows it might probably nevertheless be up to date from a .net Grid software, but you may additionally turn out to be inadvertently more rows than expected. As for updating tables with composite keys (keys containing a couple of field or column), .net can deal with them as i'll illustrate in my sample code.
With recognize to actual and logical files, when using SQL (inspite of the interface even if .net, JDBC, ODBC, OLE DB, green monitor, and so forth.) i would advocate using physical data each time feasible. if you write an SQL observation towards a logical file, the DB2 optimizer goes lower back to the physical file and analyzes it and all logical info and indexes in an attempt to find the premier approach to extract the requested facts. In different words, just since you request records from a logical file doesn’t always suggest DB2 is going to pull the facts at once from that file! additional, reckoning on your OS edition, queries that come to be using logical files within the query’s access plan are submitted to the older classic query engine (CQE), which doesn’t have all of the optimization and monitoring advantages of the newer SQL question Engine (SQE).
Now they need to cowl the way to be part of three tables and screen information from every desk in a grid manage on an internet page. here's effortless adequate to do except for the requirement that one of the crucial tables has an updateable column. this is where the .net wizards will fail you because no longer all database engines enable updates in opposition t queries with distinctive joins so the wizards don’t even bother providing replace capabilities. The first rate news is that here's nonetheless notably easy so long as you’re everyday with writing SQL.
for example a way to accomplish this query from three tables and replace one table, I’ve created three simplified tables representing precise tables in Duane’s software The table names are ARTRAN (A/R transactions), COSTTRAN (can charge transactions), and TERRITORY (containing territory codes and names). In my contrived sample, the ARTRAN and COSTTRAN tables are related with the aid of three columns: enterprise, client, and bill. The TERRITORY table is related to the COSTTRAN table by using a common column referred to as TERR. The three tables are joined to permit a person to examine the cost of an invoice vs. the bill quantity with a view to verify if a fee may still be paid. The updateable “pay commission” flag is known as PAYCOMM and is in desk ARTRAN. For comfort, the rows are sorted by way of territory name.
you can re-create these table definitions and pattern facts with this SQL Script. I placed my pattern information in a library referred to as i3.
To reveal how the Microsoft GridView control would tackle this circumstance, I created a sample ASP.internet page and positioned it in a observe doc that you should view here. The respectable news is that this scenario may also be carried out the usage of the .net declarative programming model, which requires no .internet code, no C#, no VB, no nothing!
The trick to this components is to make use of an SqlDataSource control in an effort to handle the conversation between .internet and DB2. recall that the SqlDataSource handle has 4 very vital houses:
every of the homes stores an SQL observation, which can also be a opt for, update, INSERT, DELETE, or a stored system call. each of these statements can have one or more parameters. during this instance, they only need to select facts to populate their grid and replace information to set the “pay commission” flag, so they can only fill in the SelectCommand and UpdateCommand residences. once the SQL statistics source is defined safely, the GridView control will have interaction (essentially immediately) with the SqlDataSource handle for inquiring for information and performing updates because the person alterations the data.
Let’s determine a few important facets of the SqlDataSource definition:<asp:SqlDataSource identity="SqlDataSource1" runat="server" ConnectionString="DataSource=AS400.i3.local;person identification=myuser; Password=mypassword;LibraryList=I3;Naming=system" ProviderName="IBM.facts.DB2.iSeries" SelectCommand=" opt for T.TERRNAME,AR.enterprise,AR.client, AR.bill,AR.INVDATE,AR.INVAMT, PAYAMT,INVCOST,(INVAMT-INVCOST)/INVAMT As Est_Profit, SLSREP,forged(Case When PayComm='0' Then 0 Else 1 end As SmallInt) As PayComm From ARTRAN AR be part of COSTTRAN cost On charge.company=AR.company and cost.client=AR.consumer and price.bill=AR.invoice be part of TERRITORY T On T.TERR=can charge.TERR where INVAMT>0 Order with the aid of TERRNAME,bill" UpdateCommand=" update ARTran Set PAYCOMM=Case When solid(? As VarChar(10))='authentic' Then '1' Else '0' end where business=? And customer=? And invoice=?"> <UpdateParameters> <asp:Parameter name="PAYCOMM" class="String" /> <asp:Parameter identify="company" category="Decimal" /> <asp:Parameter name="consumer" class="Decimal" /> <asp:Paramete identify="bill" classification="Decimal" /> </UpdateParameters> </asp:SqlDataSource>
First, be aware the ConnectionString and ProviderName residences are populated. I’m the usage of the DB2 information company for the iSeries and have populated the connection string to consult with their host (as400.i3.native). always these settings would be stored within the net.Config file, but for simplicity I defined them within the net page itself. Of course, change your own connection and library listing tips.
second, word the SelectCommand property is described with an easy query that joins the three tables collectively. A extra subtle example would probably limit the amount of facts returned using a parameterized standards limit.
additionally, the UpdateCommand property is populated with a parameterized update commentary. during this remark, we’re updating the PAYCOMM (pay commission) flag, which will be attached to a checkbox column within the GridView. This pattern assumes that a composite key along with enterprise, customer, and invoice might be in a position to uniquely determine a row during this table. Parameter markers are used within the SET and where clauses. further, an UPDATEPARAMETERS collection is also defined to tell .internet how to move these parameters to DB2 when the consumer in reality updates facts in the GridView manage. in the event you’re questioning, these observation parameters can be populated with suggestions presented from the GridView control from the particular row the consumer is enhancing. I’ll illustrate a way to connect the GridView to the SqlDataSource a bit later.
One special element to note during this example is the implementation of a boolean checkbox on the grid. customarily, legacy database tables define a boolean container as a CHAR(1) column that may hold cost pairs reminiscent of ‘Y’/’N’ or ‘1’/’0′. despite the fact, they don’t wish to demonstrate these values in the .web world. They simply need the consumer to verify or uncheck a checkbox. in order to do this, they deserve to put in force a special conversion in their SQL.
My sample assumes the PAYCOMM boolean column can hang a ‘0’ (no) or ‘1’ (yes). On the opt for remark, I coded an expression to come the statistics as a small integer, which .net can convert to its personal boolean layout:forged(Case When PayComm='0' Then 0 Else 1 conclusion As SmallInt) As PayComm
although, when the GridView handle offers statistics lower back to the SqlDataSource (which is responsible for sending the statistics to DB2), I’ve defined the PAYCOMM parameter within the update statement to represent the checkbox value from the DataGrid as a string. within the .internet world, boolean values are represented as strings with the values of ‘authentic’ or ‘False’. hence, within the update commentary, these string values from .web need to be converted returned to the usual legacy values of ‘0’ or ‘1’ as follows:Set PAYCOMM=Case When cast(? As VarChar(10))='authentic' Then '1' Else '0' conclusion
remember, the parameter marker in the above commentary might be offered its cost from a checkbox on the GridView. If the consumer assessments the field, this parameter will comprise the cost ‘real’. otherwise it's going to contain the value ‘False’.
The definition of the GridView handle is fairly self explanatory. here is the birth of the manage’s definition:<asp:GridView identification="GridView1" runat="server" AllowPaging="actual" DataKeyNames="company,customer,bill" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
as a way to edit or delete data from the GridView, the DatakeyNames property ought to contain a comma delimited list of columns that form the wonderful identifier for the desk. constantly, if you use a .internet wizard to populate the grid from an SQL information supply, this value should be stuffed in immediately if the database has a distinct index or fundamental key constraint defined. In their case, they need to fill in these names manually. The different important property is the DataSourceID, which is decided to SqlDataSource1 (our prior to now described SqlDataSource handle.) This property is important as it defines the linkage between the statistics entry and the person interface (Grid). all the inside communication between these two layers is handled by .internet for us.
When reviewing the GridView’s columns, I’ve described the columns to seem in the Grid and set all of them to ReadOnly (except for the PayComm field). The PayComm column makes use of a unique definition known as a TemplateField. at all times, a string or numeric database column will monitor a textual content container by means of default on the GridView. despite the fact, they need to exhibit this column as a checkbox. The TemplateField allows for us to override the default behavior by using specifying alternative HTML to render their column. As shown within the definition below, when a GridView row is in “edit” mode (EditItemTemplate), the user will see an editable checkbox that's bound to the database column called PayComm. Likewise, when a GridView row is within the typical “study most effective” mode (ItemTemplate), the person will see a locked checkbox bound to the PayComm table column.<asp:TemplateField HeaderText="Pay Comm?" SortExpression="paycomm"> <EditItemTemplate> <asp:CheckBox identity="CheckBox2" runat="server" Checked='' /> </EditItemTemplate> <ItemTemplate> <asp:CheckBox id="CheckBox1" runat="server" Enabled="false" Checked='' /> </ItemTemplate> </asp:TemplateField>
When this web page is displayed in a browser, it looks like this:
For the record, I created each and every of these tables and not using a keys at all, and the updates will nonetheless work appropriately as long as the statistics rows in the table can be uniquely identified. if you are missing a bound assortment of enjoyable row selecting columns, you possibility the SqlDataSource manage updating more rows than it'll when it executes its replace statement.
There are all kinds of tricks which you can play to get round this. as an example, you could have your question return a relative list number (using the RRN function) to the grid, that may then be handed again to an RPG stored system defined within the SqlDataSource’s UPDATECOMMAND. This RPG software can rapidly search for a unique desk row by relative record number and replace the existing row.
yet another probability to consider when doing an update in opposition t a question with numerous joins is to create an SQL View of the question along with an as an alternative OF set off. The in its place OF set off will allow a developer to outline code to make the at all times examine-most effective view updateable by specifying customized code. This method will permit the choose, update, INSERT, and DELETE homes to all reference the same View object. For greater tips on in its place OF Triggers, see function the unattainable: replace a Multi-desk View the use of as a substitute OF Triggers.
in view that Duane outlined he's using “statistics adapters,” I should word that my illustration instead uses the SQLDataSource and the .web 2.0 appropriate DB2 for iSeries .net Managed information issuer that includes iSeries access (a.okay.a. system i entry) edition V6R1. in case you’re nonetheless the usage of the older .net 1.0 compatible DB2 for iSeries .net managed provider that includes prior models of iSeries entry, you're going to deserve to do some manual .net coding with the facts adapters. here's since the older .internet 1.0 managed provider can't be used with the SqlDataSource manage. however, the SqlDataSource ideas proven here should be relevant as the data adapter also has similar opt for, replace, insert, and delete commands and parameter collections.
it is essential to observe that one way to replace statistics adapters with the SQLDataSource control could be to obtain a duplicate of the .internet 2.0 appropriate managed issuer via installing V6R1 system i entry–even if you don't seem to be in a position to update your i/OS to six.1. an additional alternative that could let you use the SqlDataSource control with DB2 with out upgrading iSeries access is to use one of the crucial IBM OLE DB providers (IBMDA400, IBMDASQL) or the IBM ODBC driver in its place of the usage of the .web managed company. All of those add-ons include the iSeries entry product.
In summary, the SqlDataSource handle provides the precise plumbing between .net and DB2 for i. given that the choose, update, INSERT, and DELETE instructions are consumer-described, they could make the database replace their records any means they want. they are able to select information from four tables, however insert into one (or many if they call a saved system). Or, they are able to choose facts from one desk, but call a stored method to alternate two tables; as an instance, update the base records table and insert a row into an audit desk.
Combining the SqlDataSource with a GridView (or different Grid control) gives an easy option to update records without needing a busy one-adult show coding all the DB2 business transactions manually!
Michael Sansoterra is a programmer/analyst for i3 company options, an IT services enterprise based in Grand Rapids, Michigan. ship your questions or feedback for Mike to Ted Holt by the use of the IT Jungle Contact page.
system i developers and .web 2.0: ASP.internet and the Declarative Programming mannequin
operate the impossible: replace a Multi-table View the use of as an alternative OF Triggers
publish this story to del.icio.us publish this story to Digg post this story to Slashdot