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 databaseAfter 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 ‘transactions’ such as SELECT/UPDATE/SELECT Choosing an item from a radiobutton list and retrieving the data onto screen, then updating some columns of the record, saving the data back to the database, then retrieving the data onto the webpage to display the updated changes. SELECT/RETRIEVE/DISPLAYYou may select a region, product category or target market and want to view summary or detail transaction data for that set of records. The INSERT/UPDATE/SELECT/RETRIEVE/DISPLAY You can save a row of transaction data to a transactions (fact) table, then go to each relevant dimension table and update running totals for example the number of sales to one customer and the lifetime revenue of the customer.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 businessWhich 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 codeIf you want to UPDATE some values in a database you use a different object the SQLCommand using the MyCommand.ExecuteNonQuery codeRetrieving 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 combine the retrieving 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 information to the datatable the .fill code copies in the column information and the row information.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 our most advanced use of SQL in this introductory class. This is a SQL statement that can update a few columns in a database table. 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 to the command that allow you to pass the values from the webpage into the SQL UPDATE SET statement 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.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. ................
................

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

Google Online Preview   Download