Washington State University



0000Retrieving lists of data from SQL Server databases – Featherman ?'This program shows several common scenarios of retrieving data based on ONE criteria. We retrieve and summarize data for one customer, one region, one state, and for different time periods such as last 90 days. technically you can say that this sample program is a demonstration of parameterized SQL queries. We use the SQLDataAdapter object to run SQL SELECT statements WHERE some condition is met (e.g. State = 'WA') 'From a macro level you need to learn how to use Visual Studio to interact with SQL Server in the following ways:' 1. Insert new rows of transaction data' 2. Retrieve lists of raw data based on one or more criteria (this class does not retrieve summarized, compiled data)' 3. Retrieve one row of data into a webform, update that one row of data, and push the updated data back to the database' 4. Update records of data during transactions (e.g. reducing inventory when a sale is made)'First we need add easy access to these namespaces (directories of classes) so we can later reduce the amount of code we need to write. For example by using these Imports statements do not need to refer to the SqlConnection by its full name System.Data.Sqlclient.SqlConnection.Imports System.DataImports System.Data.SqlClientPartial Class NewFor2018_RetrievingData Inherits System.Web.UI.Page 'the next line is what a connection string looks like. For this introductory webpage, we use Featherman's public connectionstring to connect and retrieve data from Featherman's public Featherman_analytics database (which you can access read-only). Private Shared con As New SqlConnection("Data Source = cb-ot-devst03.ad.wsu.edu; initial catalog = Featherman_analytics; Persist Security Info = True;User ID = mfstudent;Password=BIanalyst") 'In this project we use the dataadapter to run a sql select statement and to retrieve data into a datatable (which is an array). Then we set the datasource property of a gridview to that datatable. We could have started with Select * to retrieve all the columns from the specified table and not included a WHERE statement to retrieve all the rows. Here we retrieve the last 25 rows of data since we select the top 100 rows but sort them in DESC (decending order) by invoice #.#Region "Get last 25 sales" Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click Dim decTotalSales, decAverageSale As Decimal Dim intCountSales As Integer 'this is the dataAdapter object that knows how to run SQL statements against databases. It has two properties, a SQL statement in red and the name of the connection upon which to run the SQL statement. The conenction specifies which database on what server to run the SQL against. It also specifies the userID and password to get thru the firewall. Dim AllSalesDataAdapter As New SqlDataAdapter("SELECT TOP 25 * FROM featherman.Sales ORDER BY Sale_ID DESC", con) 'the data that is returned is placed into an in-memory datatable (which is a strongly typed array, meaning each column has a name, data type and perhaps restrictions) Dim SalesDataTable As New DataTable 'this next line runs the red select statement from a prior line. Next we can get and show a row count as follows. If any errors are found they will be shown in at the top of the page. We use a try/catch block to run fragile code that might fail (here since the server is down). Try AllSalesDataAdapter.Fill(SalesDataTable) intCountSales = SalesDataTable.Rows.Count 'the control on the webpage needs to be databound (glued) to the data so that it can be displayed. We use a gridview control to show a table of data. GridView1.DataSource = SalesDataTable GridView1.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try 'this pute feature is an easy way to total a column. More commonly you would use SQL GROUP BY query to pull some quick analytics from the table (sum, counts, averages). SQL GROUP BY queries are not covered in this class, we will rather use embedded PowerBI reports inside the ASPX page to compile the data. decTotalSales = pute("Sum(Total_Sale)", Nothing) 'total up the values in the Total_Sale column decAverageSale = decTotalSales / intCountSales 'Good old concatenation txtOutput.Text = "Last " & intCountSales.ToString & " sales for all regions: " & vbNewLine & vbNewLine & "Total Revenue: " & vbTab & FormatCurrency(decTotalSales, 0) & vbNewLine & "Avg Sale: " & vbTab & FormatCurrency(decAverageSale, 0) End Sub#End Region#Region "Sales by Region" Protected Sub ddlRegions_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlRegions.SelectedIndexChanged 'The prior query was hard-coded to always retrieve the last 25 sales. You rarely want all the records. Here is a first look at a parameterized SQL SELECT statement. The parameter (here @Region) is used to retrieve the value selected in a dropdownlist and pass that value into the SQL Select statement. The effect is that the program user can filter the data retrieved from the database. Because businesspeople rarely want to see all the data, they need to filter the data, the use of parameters is very useful and common. Dim decRegionSales As Decimal Dim SalesDataTable As New DataTable Dim SalesDataAdapter As New SqlDataAdapter("SELECT * FROM featherman.Sales WHERE Region = @Region", con) 'only run the procedure if a region is selected. The code will crash if a paramter is not provided, so we must ensure something was selected, and stop the program if no region was selected. MAKE SURE THE AUTOPOSTBACK is set to true If ddlRegions.SelectedIndex <= 0 Then Response.Write("Select a Region") Exit Sub End If 'just in case there is any data in the data table from last time you ran the procedure then clear out the data. This is very useful if you are using a global page level table If SalesDataTable.Rows.Count > 0 Then SalesDataTable.Rows.Clear() End If 'ok. here we dig into the parameters collection of the select command that sits inside the dataadapter. Better read up on these objects ok? So we add a parameter that will get its value from the dropdownist. In this way the user selection is dynamically sent to the SQL command. A DataAdapter is the easiest way to retrieve data, here we use the select command inside the data adapter which is tuned for speed to run select statements. With SalesDataAdapter.SelectCommand.Parameters .Clear() .AddWithValue("@Region", ddlRegions.SelectedItem.Text) End With 'everything above was preparatory, here we run the red sql statement. The dataAdapter opens a connection to the server, runs the sql statement and places the retreived data into a datatable for later display in the gridview. Try SalesDataAdapter.Fill(SalesDataTable) 'now that we have a filtered dataset, don't forget to bind the control to its datasource GridView1.DataSource = SalesDataTable GridView1.DataBind() Catch ex As Exception Response.Write(ex.Message) End Try 'we can run the same pute method (with no filter) since the retrieved data is already for one region decRegionSales = pute("Sum(Total_Sale)", Nothing) txtOutput.Text = ddlRegions.SelectedItem.Text & " region had " & SalesDataTable.Rows.Count & " sales for a total of " & FormatCurrency(decRegionSales, 0) & ". Average sale: " & FormatCurrency((decRegionSales / SalesDataTable.Rows.Count), 0) End Sub#End Region#Region "Selecting data by date range" Protected Sub ddlDateRange_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlDateRange.SelectedIndexChanged 'Here we make a leap forward and let the program user select what time period (from a list control) they would like to use as the filter for the rows of data. Here we feed the SQL SELECT statement in from the code in the SELECT CASE statement. We could also have placed two calendar controls on the form, and selected the data that is between them (using two parameters) Dim strSQLStatement As String = "SELECT * FROM featherman.Sales WHERE " Dim SalesbyTimeRangeDataTable As New DataTable Dim SalesDateRangeDataAdapter As New SqlDataAdapter(strSQLStatement, con) 'Here we choose what SQL statement will be run, absed on what selection is made in the control Select Case ddlDateRange.SelectedIndex Case 0 Exit Sub Case 1 strSQLStatement &= " DateDiff(Day, [Invoice_Date], GETDATE()) BETWEEN 0 And 30" Case 2 strSQLStatement &= " DateDiff(Day, [Invoice_Date], GETDATE()) BETWEEN 30 And 60" Case 3 strSQLStatement &= " DateDiff(Day, [Invoice_Date], GETDATE()) BETWEEN 60 And 120" Case 4 strSQLStatement &= " DateDiff(Day, [Invoice_Date], GETDATE()) BETWEEN 120 And 365" Case 5 strSQLStatement &= " DateDiff(Day, [Invoice_Date], GETDATE()) > 365" End Select 'First we need to remind the dataAdapter that it will be running the SQL statement that is inside a variable that we created. We run the typical procedure, fetch the data, placing it into a datatable, then telling the gridview to display the retrieved data. You will write this type of code, over and over. Luckily it is very few line of code and a lot of functionality. Try 'since the SQL statement was built dynamically you need to add this next line to refresh the dataAdapter's knowledge of its SELECT statement SalesDateRangeDataAdapter.mandText = strSQLStatement 'here is the moment of action, the red SQL statement is run over the connection returning the dataset that is used to fill the in-memory data table (the array) that gets next displayed in the gridview control. SalesDateRangeDataAdapter.Fill(SalesbyTimeRangeDataTable) GridView1.DataSource = SalesbyTimeRangeDataTable GridView1.DataBind() txtOutput.Text = "Time period analysis:" & vbNewLine & vbNewLine & "# Sales: " & vbTab & SalesbyTimeRangeDataTable.Rows.Count & vbNewLine & "Total Sales: " & vbTab & FormatCurrency(pute("Sum(Total_Sale)", Nothing), 0) & vbNewLine & "Avg. Sale: " & vbTab & FormatCurrency(pute("Sum(Total_Sale)", Nothing) / SalesbyTimeRangeDataTable.Rows.Count) Catch ex As Exception Response.Write(ex.Message) End Try End Sub#End Region Protected Sub LinkButton1_Click(sender As Object, e As EventArgs) Handles LinkButton1.Click MultiView1.ActiveViewIndex = 0 End Sub Protected Sub LinkButton2_Click(sender As Object, e As EventArgs) Handles LinkButton2.Click MultiView1.ActiveViewIndex = 1 End Sub Private Sub NewFor2018_RetrievingData_Init(sender As Object, e As EventArgs) Handles Me.Init 'Notice this is the .init procedure so this is how you fill a dropdownlist with names and a gridview with a whole table of data (not filtered becuase the SQL statement reads SELECT *) Dim dtCustomers As New DataTable Dim daGetCustomers As New SqlDataAdapter("SELECT * FROM featherman.Customers ", con) Try daGetCustomers.Fill(dtCustomers) 'the gridview control on the webpage again needs to be databound (glued) to the data so that it can be displayed. We use a gridview control to show a table of data. You could also use the Microsoft chart GridView2.DataSource = dtCustomers GridView2.DataBind() With ddlCustomers .DataSource = dtCustomers .DataTextField = "CustomerName" .DataValueField = "CustomerID" 'this line of code is very important it assigns the primary key for the record to the value field for the DDL .DataBind() End With Catch ex As Exception Response.Write(ex.Message) End Try End Sub Protected Sub ddlRegion_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlRegion.SelectedIndexChanged 'Here we pull the sales records for the region selected by the program user Dim dtSalesFor1State As New DataTable Dim daGetSales As New SqlDataAdapter("SELECT * FROM featherman.Sales WHERE Region = @p1", con) 'again a parameterized SQL SELECT statement. The region selected by the program user is passed to the parameter, which passes the value to the DataAdapter when the SQL statement is run. Use of parameterized SQL statements protects webpages from a serious security problem called SQL injection attacks With daGetSales.SelectCommand.Parameters .Clear() .AddWithValue("@p1", ddlRegion.SelectedValue) End With Try daGetSales.Fill(dtSalesFor1State) GridView2.DataSource = dtSalesFor1State GridView2.DataBind() 'table.rows.count is very helpful, so is the pute function that has been discussed elsewhere txtOutput2.Text = ddlRegion.SelectedItem.Text & ":" & vbNewLine & vbNewLine & dtSalesFor1State.Rows.Count & " sales " & vbNewLine & FormatCurrency(pute("Sum(Total_Sale)", Nothing)) Catch ex As Exception txtOutput2.Text = ex.Message End Try End Sub Protected Sub ddlCustomers_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlCustomers.SelectedIndexChanged 'here we pull the sales for any one customer Dim dtSalesFor1Customer As New DataTable Dim daGetSales As New SqlDataAdapter("SELECT * FROM featherman.Sales WHERE CustomerID = @p1", con) With daGetSales.SelectCommand.Parameters .Clear() .AddWithValue("@p1", ddlCustomers.SelectedValue) End With Try daGetSales.Fill(dtSalesFor1Customer) GridView2.DataSource = dtSalesFor1Customer GridView2.DataBind() txtOutput2.Text = "Customer # " & ddlCustomers.SelectedValue & " " & ddlCustomers.SelectedItem.Text & ": " & vbNewLine & vbNewLine & dtSalesFor1Customer.Rows.Count & " sales " & vbNewLine & FormatCurrency(pute("Sum(Total_Sale)", Nothing)) Catch ex As Exception txtOutput2.text = ex.Message End Try End SubProtected Sub btn2Filters_Click(sender As Object, e As EventArgs) Handles btn2Filters.Click 'Here we pull the sales records for the region selected by the program user Dim strPaid As String Dim dtSalesFor1State As New DataTable Dim daGetSales As New SqlDataAdapter("SELECT * FROM featherman.Sales WHERE Region = @p1 AND Paid = @p2", con) 'again a parameterized SQL SELECT statement. The region selected by the program user is passed to the parameter, which passes the value to the DataAdapter when the SQL statement is run. Use of parameterized SQL statements protects webpages from a serious security problem called SQL injection attacks If ddlRegion.SelectedIndex <= 0 Then Exit Sub With daGetSales.SelectCommand.Parameters .Clear() .AddWithValue("@p1", ddlRegion.SelectedValue) .AddWithValue("@p2", CheckBox1.Checked) End With Try daGetSales.Fill(dtSalesFor1State) GridView2.DataSource = dtSalesFor1State GridView2.DataBind() If CheckBox1.Checked = True Then strPaid = " paid invoices " Else strPaid = " unpaid invoices " End If 'table.rows.count is very helpful, so is the pute function that has been discussed elsewhere txtOutput2.Text = ddlRegion.SelectedItem.Text & ":" & vbNewLine & vbNewLine & dtSalesFor1State.Rows.Count & strPaid & vbNewLine & FormatCurrency(pute("Sum(Total_Sale)", Nothing)) Catch ex As Exception txtOutput2.Text = ex.Message End Try End SubEnd Class ................
................

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

Google Online Preview   Download