Washington State University



Featherman’s Coding Adventures: Overview of usage and SQL Server IntegrationThis document will make sense if you study the related glossary. is Visual ’s set of objects and functionality to connect webpages to databases (we use SQL Server as the DB). We leverage the objects (tables, commands, data adapters, parameters, rows, columns, command builders, etc.) to perform only a few tasks in this class, much more is possible; INSERT records of data from a business transaction to an array and database tablesSELECT a few columns from a dimension table that are used in a radiobutton, checkbox or dropdown listSELECT one row of data or from the database and display it on the webpage, often to update itAfter the one row of data is SELECTED and retrieved from the database is displayed on the webpage, if the program user UPDATES the data the changes need to be saved in the databaseIn code we often UPDATE running totals in a table to keep them accurate after a transaction.After the one row of data is SELECTED and retrieved from the database is displayed on the webpage, the program user can choose to DELETE the row of data SELECT a set of filtered rows of data from one or more database tables and display them on the webpage, such as records related to one dimension such as product, model, employee, customer, vendor, etc. The data is displayed in a tabular shaped gridview or chart control.The above functionality is packaged into ‘multi-step transactions’ such as SELECT/UPDATE/Refresh displayChoose an item from a radiobutton list and retrieve the data onto screen using a SQL SELECT statement inside a dataAdaper.FILL() Then update some columns of the record, saving the data back to the database using the easy dataAdapter.Update (as long as you have created a sqlcommandbuilder. Finish by retrieving the data onto the webpage again to display the updated changes. If the program user is manually making changes to the record using a webpage, then use the DataAdater.UPDATE syntax. This is really easy because the sqlcommandbuilder is writing the SQL for you.If you are using code to go update system database values such as update running totals, and the program user is NOT manually making the changes then use an UPDATE SET SQL statement inside a dqlcommand.SELECT/RETRIEVE/DISPLAYWhen you make a selection in a list controls (e.g., choosing a state or customer) you may want to view summary or detail transaction data for that selection (called an attribute of a dimension field). In this case you use a parameterized SQL SELECT statement inside a dataAdapter.FILL() to retrieve the subset of records and display them in a gridview. If you are retrieving only values for a list control then the process is the same but you retrieve only the two columns.The INSERT/UPDATE/SELECT/Refresh displayYou can save a row of transaction data to a transactions (fact) table, then go to each relevant dimension table and update running totals. You would use a dataAdapter.UPDATE to save the row of transaction data. You would then use a parameterized sqlcommand to execute a SQL UPDATE SET code to change numeric values in the dimension table. You would then use a dataAdapter.FILL() to retrieve the updated data tables into gridviews.You have to carefully think through the steps needed to pull off the changes. There are often many steps in the data management process that have to be accomplished to complete a transaction. For example, the number of sales to one customer and the update the columns that track customers. We used the RFM approach to monitor customer engagement (recency – last transaction date, frequency – number of transactions, monetary – total revenue or profit).A common scenario is the sale of an item to a customer. Many tables should be editeda) the transaction is saved into a transactions (fact table) such as invoice.b) One row in the inventory table should be edited to reflect the reduction in inventory. Depending on inventory reorder points, the inventory level may drop low enough to kick off a replenishment process.c) The accounts receivable table is updated if money is owed.d) the Customer record can be updated to reflect the increased business with the customere) analytics charts can be updated to reflect the increased businessSELECT/RETRIEVE/ARCHIVE/DELETE/Refresh displayThe professional way to delete data records, is to a) Create the archives database tables each with the same schema as the table that you want to delete rows from. Do not use auto-numbering feature, but do use a primary key. SSMS has a copy table wizard to facilitate this table duplicationb) identify the rows to be deletedc) copy the rows to be deleted into the archives tabled) verify to the program user that the rows are archived, either by a message or displaye) delete the data records f) then refresh the gridviews and list controlsThis procedure is slightly complicated if you are deleting rows of transaction data that have foreign key relationships to other dimension tables. For example if you had a Customers table (that has summary running totals) and each customer could be related (associated) to many sales transactions. In this scenario sales can only be made to pre-existing customers (enforced by the foreign key).In this case you have to delete the rows from the child transaction table first (here the Sales table), then delete the customer record from the parent (here Customers table). If there is no parent-child primary key/foreign key relationship then this step is not needed. To specify the row of data to delete, make a selection in a list control (e.g., choosing a state or customer), then you use an INSERT INTO Archives table SELECT * FROM datasource table WHERE primary key = @p1 SQL statement. This code copies the rows (to be deleted) into archives. Finally you use a DELETE FROM tablename WHERE primarykey = @p1 statement, inside a sqlcommand to delete the rows of data.Which code and objects are used to perform each task?GeneralWe use a SQLdataAdapter to SELECT and retrieve rows of data from the SQL database into our datatable which resides in the memory of the webserver. This is accomplished using the MyDataAdapter.Fill codeIf you have added a SQLCommandBuilder to help the dataAdapter, you can easily INSERT rows of data from the in-memory datatable back to the SQL Server database using the MyDataAdapter.Update code. Use this approach when the program user is manually editing columns values.If you want to UPDATE some values in a database table in code (not manual data editing) you use a different object the SQLCommand using the MySQLCommand.ExecuteNonQuery code.If you want to retrieve one value (calculated such as a count, sum, average, minimum or maximum, or not calculated) from a database table you can use the MySQLCommand.ExecuteScalar code.IF you need to delete a row or set of rows from a database table ,you can use a MySQLCommand.ExecuteNonQuery to execute a DELETE FROM tablename where primarykey = @p1 code. The @p1 is a variable in the SQL statement. When the program is run, the value provided by the program user, such as a selection in a list control, is passed into the variable and used in the SQL statement to specify which set of records are to be deleted.Retrieving data from the databaseYou can retrieve one row, many rows, or all the rows. You can retrieve one column, several columns or all the columns. Only retrieve the columns you need. This is how you retrieve all the rows and columns, the * means all the columns:Dim MyDataAdapter as New SQLDataAdapter(“SELECT * from Mytable”, con)To retrieve just some of the columns specify their names:Dim MyDataAdapter as New SQLDataAdapter(“SELECT column1, column2 FROM Mytable”, con)To retrieve some of the rows use a WHERE statement:Dim MyDataAdapter as New SQLDataAdapter(“SELECT * FROM Mytable WHERE column1 = @p1”, con)You can expand filtering of some of the columns and some of the rows using: Dim MyDataAdapter as New SQLDataAdapter(“SELECT column1, column2 FROM Mytable WHERE column1 = @p1”, con)You have to add the parameters to the dataAdapter’s SELECT command if you want to filter the rows retrieved for example:MyDataAdapter.selectcommand.parameters.add(“@p1”, textbox1.text)To run the SQL statement inside the dataAdapter use the following inside a TRY/CATCH block:MyDataAdapter.Fill(MyDatatable)Whereas the DataAdapter.fillschema(Mydatatable) code (dicussed below) copies in only the column schema information to the datatable. You do this when you want to add new rows of data to the database and you want to use the Dim dr as new datarow = tablename.newrow, datatable.rows.add(dr), and finally the dataAdapter.Update(datatable) code to save the new row of data to the database.The .fill code copies in the column information and rows of data.Insert a new row of data to a databaseCreate a new row in a datatable, transfer the values from the web page controls into the datarow. Save the datarow to the datatable. Start by making a dataAdapter which specifies which table is being connected to and over what connection: Dim MyDataAdapter as New SQLDataAdapter(“SELECT * from Mytable”, con)This is extremely simple code If you have previously loaded the schema (the column names, datatypes, etc.) into the in-memory datatable. Use the code:DataAdapter.FillSchema(Mydatatable). Then the in-memory datatable knows what columns it has.Inserting rows can be made easy by using the commandbuilder which writes the SQL code for you. You careate one as follows: Dim MyCommandBuilder as New SQLCommandBuilder(dataAdapter). Next use the DataAdapter.Update(Mydatatable) code in a TRY/CATCH block to transfer the new row of data in your datatable back to the SQL Server database.Update a few columns of data for one row This is such useful code. This is a SQL statement that can update a few columns in a database table, WITHOUT human manipulation of the column values (can update database columns automatically. As you can see, you can increment the value in the column using the += code (-= would also work, etc.). But be sure the starting value for the row of data is zero as you cannot add a value to NULL.Dim UpdateCommand as new SQLCommand(“UPDATE tablename SET Column1 += @p1, column2 += @p2 WHERE primarykeycolumn = @p3”, con)You have to add parameters (variables) to the command that allow you to pass the values from the webpage into the SQL UPDATE SET statement (so the values used to update the database can vary and are not ‘hard-coded’. For exampleUpdateCommand.Parameters.AddwithValue(“@p1”, CDEC(textbox1.text))The actual updating of the database occurs when you run the command (in a TRY/CATCH) which has specified the SQL UPDATE SET code within it, for example:UpdateCommand.ExecuteNonQuerywhich means execute the SQL in my command, but the SQL code is not a query so do not return any data.Delete rows for a selected valueExplained in-depth in a different document.Final note: At first this functionality is complex, however the .NET/SQL Server combination was selected for its ease of use. Database integration and data management is much harder in other programming paradigms. Sure there are a lot of new concepts here, but with some repetition, you can master them in a few weeks. The functionality is very gratifying and can be used to create very useful applications that together are a HUGE improvement over running a business using Excel.Think about business transactions in groups of data maniulations. You may need to update columns in several tables to complete the transaction, so think this through and plan you code well. These data manipulations are at the heart of data processing, and you are following a well-traveled path that many progammers have travelled before you. With this functionality, now you are building useful data-driven websites! ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download