Washington State University



Saving Rows of Transaction Data to SQL Server Databases (Simplified) and SQL UPDATE SET Commands - Featherman ?right12636500Imports System.DataImports System.Data.SqlClient'This program records a new sale into a sales table, then uses the sale transaction information to update some columns from the customers table. This is an example of a transaction where different data manipulations need to occur. 1) a record is saved into one table then 2) some running total values are updated in another table to reflect the transaction. Several columns in the customers (master data) table are updated to record the recnt and increased commercial activity. While updating a current value such as inventory or bank account balance would be performed similar as shown, to calculate aggregate values (e.g running totals for # transactions, # units, revenue, profitability) for one or more records (e.g. customer or employee) you would more commonly use a SQL GROUP BY() or PIVOT() query or similar aggregating mechanism compiling data from the transaction table (here the new sales).'Note: this methodology works only if you have set up the primary key of the SQL Server database table (here sales) set to be auto-incremented. So we do not need to capture data to insert a sale ID# (that value is calculated for us). Also be sure to set the Customer DDL to be autopostback !! This program also is clever in that functionality that is repetitive is broken out into its own general sub-procedure so that it can be called from different procedures. Partial Class InsertNewRecordAdvanced Inherits System.Web.UI.Page#Region "Featherman's Connectionstring: Create your own using the samples provided" Public Shared strCon As String = System.Configuration.ConfigurationManager.ConnectionStrings("conAnalyticsSecure").ConnectionString Public Shared con As New SqlConnection(strCon)#End Region 'This dataAdapter is multi-purpose a) it is used to retrieve and display the row of new data that is saved to the database, b) it serves as the link from the webpage to the database table, meaning that the page_init procedure includes functionality such that the dataAdapter retrieves the schema for the datatable that the new row will be added to, c) the dataAdapter also provides the functionality to take the row of data from the in-memory webpage and save it back to the SQL Server database table. The commandbuilder object writes the insert statement for us! Public Shared daSalesRecords As New SqlDataAdapter("Select TOP 1 * FROM featherman.Sales ORDER BY Sale_ID DESC", con) Public Shared cbSalesecords As New SqlCommandBuilder(daSalesRecords) 'these tables are created as global public shared objects so that different procedures can access and modify them. Public Shared CustomersDataTable As New DataTable Public Shared SalesDataTable As New DataTable#Region "Load DDL" Protected Sub Page_init(sender As Object, e As System.EventArgs) Handles Me.Init 'Next the data for the dropdownlist is retrieved. The datatextfield is the information that is displayed in the dropdownlist. The Datavaluefield is the # collected when a user makes a selection. The .datavaluefield is usually the primary key for the record (here customerID is the primary key for the customers table and a foerign key in the sales table. Another time we will use this foreign key to retrieve the sales for a specified customer. Dim CustomersAdapter As New SqlDataAdapter("SELECT CustomerID, CustomerName FROM Featherman.Customers ORDER BY CustomerName", con) Dim CustomerDDLTable As New DataTable Try 'the next line of code run the red SQL statement of the data Adapter, retrieving rows of data and entering the data into the datatable (so that it can be displayed in the dropdown list. CustomersAdapter.Fill(CustomerDDLTable) With DropDownList2 .DataSource = CustomerDDLTable .DataTextField = "CustomerName" 'this is the column to show .DataValueField = "CustomerID" 'this is the column to capture info to be used .DataBind() .Items.Insert(0, "Select a customer") End With Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region “Retrieving and Displaying the VIP status for the selected Employee”Protected Sub DropDownList2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles DropDownList2.SelectedIndexChanged 'This procedure shows how to use a SELECT statement that can pull one value calculated snd returned by a SQL SELECT query. When you have a sqlCommand that runs a SQL statement that returns one value (MSFT calls this a scalar) then you can run the SQLCommand using .executescalar If DropDownList2.SelectedIndex <= 0 Then Exit Sub 'In this case we want to return the VIP status of the selected customer. The value is stored in a bit field which can store 0 or 1, false or true. The value was captured from a checkbox, and here we retrieve the true or false value from teh SQL database and use that value to set the .checked status of the customer (which can have 2 options true or false), then Dim cmdCheckVIPStatus As New SqlCommand("SELECT VIPCustomer FROM featherman.Customers WHERE CustomerID = @p1", con) cmdCheckVIPStatus.Parameters.Clear() cmdCheckVIPStatus.Parameters.AddWithValue("@p1", DropDownList2.SelectedValue) Try If con.State = ConnectionState.Closed Then con.Open() chkVIP.Checked = cmdCheckVIPStatus.ExecuteScalar Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region#Region "Insert data row" Protected Sub lnkSaveRecord_Click(sender As Object, e As EventArgs) Handles lnkSaveRecord.Click 'We add the new row of data using the super-easy dataAdapter.Update approach to saving a new row of transaction data. We bring the SQL database schema into ou array to specify the columns, then we add a new row to the array and then proceed to fill in most of the columns for the new row. We add the new row to the dataTable and then use that datatable to push the row of data back to the SQL Server database using the dataAdapter.Update code. It is really easy to use and magic. 'Later we will create a new row of sales data. That row needs to know the schema of the columns (their datatypes, restrictions, and column names). This next line of code passes (maps) the column information into the datatable so when we create a new row for the new sale, that row knows the columns that need to be populated. If the datatable is made in the SQL Server database, then this next line creates the columns replacing the columns.add procedure we used previously. daSalesRecords.FillSchema(SalesDataTable, SchemaType.Mapped) If SalesDataTable.Rows.Count > 0 Then SalesDataTable.Rows.Clear()'Here a new blank row is created. The row knows what columns it has by name, because of the prior .fillschema code. Dim dr As DataRow = SalesDataTable.NewRow Dim decTATotal As Decimal Dim dtInvoiceDate As Date 'here we ensure the total sale value is a number If IsNumeric(txtTotalSale.Text) = False Then Response.Write("<script language = 'javascript' >alert('Use numbers please')</script>") txtTotalSale.BackColor = Drawing.Color.Coral Exit Sub End If 'here we ensure the value of the total sale is >0 If CDec(txtTotalSale.Text) < 0 Then Response.Write("<script language = 'javascript' >alert('Why does someone always try to crash your code?')</script>") txtTotalSale.BackColor = Drawing.Color.Coral Exit Sub End If 'here we ensure the totalsale <= 25000. Also FYI Visual studio provides some built-in error control. It is not a problem if a person types a $ or , in their number as in $24,000. These characters are trimmed off before the data is stored. Thank you Visual Studio! If CDec(txtTotalSale.Text) > 25000 Then decTATotal = 25000 Response.Write("<script language = 'javascript' >alert('Sales of $25k was recorded. This sample sales system has a max $amount of 25k')</script>") txtTotalSale.Text = 25000 txtTotalSale.BackColor = Drawing.Color.Coral Else decTATotal = CDec(txtTotalSale.Text) txtTotalSale.BackColor = Drawing.Color.White End If 'Select today in the calendar if no selection was made or a date in the future is selected dtInvoiceDate = DateTime.Parse(txtDate.Text) If dtInvoiceDate = Nothing OrElse dtInvoiceDate > Today() Then dtInvoiceDate = Today() End If 'here we ensure appropriate values are made, especially important is the calendar date. 'we don't want dates in the future If ddlRegion.SelectedIndex < 0 OrElse DropDownList2.SelectedIndex <= 0 OrElse txtTotalSale.Text = Nothing OrElse IsNumeric(txtTotalSale.Text) = False Then Response.Write("<script language = 'javascript' >alert('Please be sure to select a customer and type a numeric sales amount.')</script>") Exit Sub End If 'now take the values from the form and assign them to the columns of the new datarow. Note you do not need to provide a value for every single column of the new row. If the column in the database allows NULLS, then entering it is optional. Sometimes one procedure adds half of the columns of data and another procedure populates some of the other columns. dr.Item("CustomerID") = DropDownList2.SelectedValue dr.Item("Total_Sale") = decTATotal dr.Item("Invoice_Date") = dtInvoiceDate.Date dr.Item("Region") = ddlRegion.SelectedItem.Text dr.Item("Paid") = chkPaid.Checked dr.Item("Year") = dtInvoiceDate.Year 'this is a nice column that can be used later to pull analytics more quickly. 'Next use a try/catch code block to a) save the now populated row of data to the in-memory datatable and, b) use that datatable (with just the one row in it) as the datasource for the streamlined dataAdapter.update procedure. Because we passed the database table schema into the in-memory datatable, and the command builder constucted the INSERT SQL statment, we were able to create a row of the correct schema, and the dataAdapter figures out the INSERT statement for us. The code that updates the customer master record is segmented into another procedure (UpdateData) and is called (executed). Try SalesDataTable.Rows.Add(dr) daSalesRecords.Update(SalesDataTable) Call UpdateData() Catch ex As Exception Response.Write(ex.Message) End Try End SubProtected Sub UpdateData() 'Here we update three of the columns in the customers table. When running an UPDATE procedure you DO NOT need to update every column in the row jsut htose that would change as a result of the business transaction. For example the customer address did not need to change, so those columns were not included. Dim cmdUpdateCustomerInfo As New SqlCommand("UPDATE featherman.customers Set NumberSales += 1, TotalSales += @p2, LastPurchase= @p3 WHERE CustomerID = @p1", con) 'now set-up the update of the customer master record to show the increased business with the customer. With cmdUpdateCustomerInfo.Parameters .Clear()'make sure you enable postback on the customer dropdownlist. Here we CINT the selectedvalue just to be sure that a number is provided. .AddWithValue("@p1", CInt(DropDownList2.SelectedValue)) .AddWithValue("@p2", CDec(txtTotalSale.Text)) .AddWithValue("@p3", DateTime.Parse(txtDate.Text)) End With 'now the updating action occurs. The SQLCommand object (cmdUpdateCustomerInfo) runs the red SQL UPDATE SET statement at the top of this procedure. The executenonquery function means 'execute this SQL statement and do not return any data' (its not a SELECT statement rather the data needs to be modified and that's all (insert/updates/deletes). We still need to run the code within a try/catch block. Notice that when running a command you need to manually open and close the connection to the database. Try If con.State = ConnectionState.Closed Then con.Open() cmdUpdateCustomerInfo.ExecuteNonQuery() 'this line runs the red UPDATE SET statement to update the customer info'these next two helper procedures are discussed below. Notice how clean this procedure is when the code is segmented into procedure calls. The code in this procedure is long enough. It's a lot easier to think through and write code if you break it in to pieces. Call GetRecords() 'fetch the new row of data just added and display it Call RefreshDGV() 'fetch the updated customer data and display it Catch ex As Exception Response.Write(ex.Message) Finally con.Close() End Try End Sub#End Region#Region "Show data row just added" Protected Sub GetRecords() 'Here we just bring the new row of sales data back onto the webpage in a row format. Notice that the red query inside the dataAdapter at the top of the page includes the code SELECT TOP 1(Sale_ID). This means get the top row in the table since the ORDER BY sorts the orders highest to lowest (using the SaleID field), putting the newest record at the top. This code was separated into a 'called' procedure just to split things up. You never want to put all your code into one HUGE procedure, its too hard to envision, read, troubleshoot, and maintain. Also shorter procedures allow you to see the meaning and detail in the code, allowing you to find errors or find ways to tune-up or tight-up the code. 'recall the SalesDataTable is global so we need to clear out any rows before we reload it If SalesDataTable.Rows.Count > 0 Then SalesDataTable.Rows.Clear() 'same as always, retrieve the data specified in the red SQL statement above, placing the retrieved values into an in-memory datatable (an array), and then display the array in a gridview control. Try daSalesRecords.Fill(SalesDataTable) GridView1.DataSource = SalesDataTable GridView1.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub #End Region#Region "Show totals" Protected Sub RefreshDGV() 'this code was separated into a 'called procedure' because the code needs to be run after each new sale is made. Also the code could be run at page init or in other places as this program grows. Well any resorting of the customers is not automatically performed (shown elsewhere), but this procedure shows the updated customer information (increased # of transactions, increased sales volume, updated date field). While not necessary, the date field is converted to a varchar (text) datatype so that it can be formatted into a shortdate. Two columns of analytics are added to the table data, 1) average order $ and 2) # of days ago the most recent transaction was completed. Dim CustomersAdapter As New SqlDataAdapter("SELECT CustomerName As [Customer Name], NumberSales As [# Sales], FORMAT(TotalSales, 'N0') AS [Total $ales], FORMAT((TotalSales/NumberSales), 'N0') as [Average Order $], CONVERT(varchar(10),[LastPurchase], 101) AS [Last Sale Was], DATEDIFF(DAY, [LastPurchase], GETDATE()) AS [Days Ago] FROM Featherman.Customers ORDER BY CustomerName", con) Try 'clear the datatable if the old info exists (before the current transaction was posted). This is how we ensure the updated data is displayed and not duplicated. Theoretically the datatable can hold the totals after the last sale was made, so selecting more data would append (add more rows) and not replace any current data. We have to cehck for and remove any old data. If CustomersDataTable.Rows.Count > 0 Then CustomersDataTable.Rows.Clear() CustomersAdapter.Fill(CustomersDataTable) GridView2.DataSource = CustomersDataTable GridView2.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try End Sub Private Sub lnkClear2_Click(sender As Object, e As EventArgs) Handles lnkClear2.Click (compressed to save paper) txtDate.Text = Nothing, ddlRegion.SelectedIndex = -1, DropDownList2.SelectedIndex = -1, txtTotalSale.Text = Nothing chkPaid.Checked = False End Sub #End Region ................
................

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

Google Online Preview   Download