380 likes | 677 Views
Professor Ralph Westfall October 2009 Sample Code. Using SQL Server Express with Visual Basic.NET. Create Visual Basic.NET Project. Start>All Programs>Microsoft Visual Studio 2008 Click Project link after Create: on left side
E N D
Professor Ralph Westfall October 2009 Sample Code Using SQL Server Express with Visual Basic.NET
Create Visual Basic.NET Project • Start>All Programs>Microsoft Visual Studio 2008 • Click Project link after Create: on left side • Expand Visual Basic>click Windows>Windows Forms Application>rename as Olympics-SQL> OK • File>Save All>[check Create Directory for solution if available>]browse to the Desktop>Save • File>Exit
SQL Server Express Versions • You may get SQL Server Express 2005 when you install Visual Studio Professional 2008 • If so, you should also install SQL Server Express 2008 to go with it (download the x64 version for Vista, or the x86 one for other Windows versions) • If you can avoid installing SQL Server Express 2005 when installing Visual Basic, it will make it easier to install SQL Server Express 2008
Set Up SQL Server Express • I was having trouble connecting with SQL Server 2008 until I did the following • See the following slide if these menu choices aren’t available • Start>All Programs>Microsoft SQL Server 2008>Configuration Tools>SQL Server [Surface Area] Configuration Manager • click lower link that says Surface Area Configuration for Services and Connections • Click Remote Connections>Local and remote connections>Using TCP/IP only>OK
Set Up SQL Server Express • I installed a later version and it was like this: • Start>All Programs>Microsoft SQL Server 2008>Configuration Tools>SQL Server Configuration Manager • Click SQL Server Services>if Log On As is not LocalSystem, right-click SQL Server (SQLEXPRESS)>Properties>Built-in account:>select LocalSystem>OK>Yes • Then close Tools>SQL Server Configuration Manager
Create a SQL Server Database • Start>All Programs>Microsoft SQL Server 2008>SQL Server Management Studio>OK • Be sure Windows Authentication is being used, write down the Server name:>click Connect • Expand server name and then Databases in Object Explorer window on lower left • if get error message, minimize, click refresh button at top of Object Explorer and try again • problem may be a database that wasn't detached before deletion • right-click Databases>New Database>type Olympics as Database name:>OK
Import Data • File for following instructions: olympics.csv • Save files as .csv>Yes (to keep format) • See Saving Changes Not Permitted if a problem • Right-click Olympics (database name)> Tasks>Import Data>Next> • select Flat File Source>Browse and select Files of type: CSV files (*.csv) to get olympics.csv>select Format: Delimited>Next> • Use Format: Ragged right for *.txt files • Next (Use Windows authentication)>Next>Next> Next>Finish> Close
Modify Fields • Expand Olympics in Object Explorer window • Expand Tables>right-click dbo.olympics>Design • Set up key field • Right-click first row>Insert Column>make name be ID>change Data Type to int>unclick Allow nulls (but leave checked for other fields) • Right-click ID>Set Primary Key • In Column properties below, expand Identity Specification, set (IsIdentity) as Yes, set Identity seed as 100
Modify Fields, View Data • Change field names and properties: • Next row after ID should be named Country with Data Type of nchar(20) • Next three rows should be Gold, Silver, Bronze, each with Data Type of int • Last row should be Pop, with Data Type of decimal(7, 2) • File>Save Olympics>Yes (ignore warnings) • Right-click table name>Edit Top 200 Rows • Verify that proper data is in right-most fields • Edit data if necessary to fix problems
Field Length Data Types • int 4 bytes, values range from ±2 billion • char is number of bytes in parentheses • Reserves that much space for fields • Use nchar for international applications • varchar is for variable length text fields • Only uses as much space as is needed for each field (saves space in database) • Use nvarchar for international applications • decimal (x, y) is more accurate than float for decimal values (x=total digits, y=decimals) • Can have missing values with Allow Nulls • Never let ID field value be missing!
Detach Database • Right-click Olympics (database name) in Object Explorer>Facets>select and copy PrimaryFilePath>OK • Open File Explorer and paste that path into address box • Close the Design and/or Edit windows on the right of Management studio>right-click Olympics>Tasks>Detach>check Drop>OK • Close Management Studio
Move SQL Server Database • Select and Cut Olympics.mdf and Olympics_log.ldf files in Windows Explorer • Create a new folder named cis338 on the C:\ drive • Then paste the two files into that directory
Microsoft Access 2007 • File for following instructions: olympics.csv • Start>All Programs>Microsoft Office> Microsoft Office Access 2007 • Click Blank database>name it olympics.accdb>browse to C:\cis338 [thanks to Rafael Robles] folder and Save the file in that directory>Click Create
Microsoft Access Data Fields • Click External Data tab>click Import text file icon>Import the source data into a new table>browse to olympics.csv file>OK>Delimited>Next>Comma>Next> • Change Fieldnames to Country, Gold, Silver, Bronze, Pop • Make Country be Text, the medals be Integer, and Pop be Double>Next • Let Access add primary key>Next • Leave Table name as Olympics>Finish>Close • Close Table1
Restart Visual Basic Project • Double click the Olympics-SQL.sln file in Olympics-SQL folder on the Desktop to restart Visual Studio 2008 • Click Form1 in Solution Explorer and then the icon to view it in the designer window • Right-click Form1 in designer window> Properties • Change (Name) to frmUI • Change Text to Olympics Data • Click and drag right side of form to make it wider
Add Controls • Go to form designer window and drag the following onto the following locations on form: • Upper left: Label, set Text as Country • Under Label: ComboBox, (Name) cboCountries, change DropDownStyle to Simple, Sorted to True, Font to Courier New • To right of 1st Label: Label, set Text as # of Golds • Under 2nd Label: ListBox, (Name) lstGolds • Click Items (Collection) in lstGolds and type 1 through 10 on separate rows>OK
More Controls • To right of 2nd Label: CheckBox, (Name) chkPop, set Text as Large Population • Click drop down on right of Text to make 2 lines • Put a separate Buttons below each control • Name them btnCountry, btnGolds, btnPop and set the Text for each as Select • Add two more Buttons: btnClear and btnExit, with Text of Clear and Exit
Output ListBox • Add another ListBox stretching across the bottom of the Form, (Name) lstOutputs • Add label over: Text=Countries G S B Pop • Set Font to Courier New so that outputs will line up in columns • Align controls and Labels to look professional
Add a Module: Data Tier • Project>Add Module>Name: DataSQLSS.vb>OK • Type: Imports System.Data.SqlClient above the Module statement • Imports System.Data.OleDb 'Use for MS Access • Below the Module statement, type: Private pads() As Integer = {19, 3, 3, 3, 7} Public Function AcquireData(ByVal queryStr As String, ByVal startSize As Integer) As ArrayList • Then hit Enter to generate End Function
Add Variable Declarations • Type the following in the new Function Dim alsData As ArrayList Dim da As SqlDataAdapter '* Dim ds As DataSet Dim con As SqlConnection '** Dim dr As DataRow Dim dc As DataColumn Dim rowData As String '* OleDbDataAdapter for Access '** OleDbConnection for Access
Add Code for Database • Below the declarations, type following lines: ds = New DataSet con = New SqlConnection 'OleDbConnection con.ConnectionString = "server=(local)\SQLEXPRESS; AttachDbFilename=" & "C:\cis338\Olympics.mdf;" & "Integrated Security=True;" 'or Olympics.dbo if that's the file type'or con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\cis338\Olympics.accdb;"[thanks to Karla Gutierrez Lopez]
More Code for Database con.Open() da = New SqlDataAdapter(queryStr, con) 'OleDbDataAdapter da.Fill(ds) 'da.Fill(ds, "Olympics")
Add Code to Load ArrayList • Next, type: alsData = New ArrayList(startSize) Dim i as Integer 'move up into other declations For Each dr In ds.Tables(0).Rows rowData = "" For Each dc In ds.Tables(0).Columns rowData += Trim(dr(dc).ToString()).PadRight(pads(i)) * i += 1 Next i = 0 alsData.Add(rowData) Next • will need to fix for left padding numbers
Finish Function and Module • Type: con.Close() da = Nothing ds = Nothing Return alsData
Code the Form • Double-click the form in the designer • Add the following declaration above the frmUI_Load Sub: Private alsData As ArrayList • Add the following code to the frmUI_Load Sub alsData = DataSQLSS.AcquireData("select Country from Olympics order by Country", 80) For Each item In alsData cboCountries.Items.Add(Trim(item.ToString)) Next
Test the Data Tier and Form • Double click the Exit button and then put the word End in the generated Sub • Now run the code and make corrections if necessary
Add a Module: Business Tier • Project>Add Module>name BizTier.vb>OK • Type code below the Module statement: Private Const COUNTRY_FIELD As String = " Country " Private Const ALL_FIELDS As String = " * " Private Const COUNTRY_COUNT As Integer = 80 'default ArrayList size Private Const SIZE_FACTOR As Integer = 3 'for reducing ArrayList size for selections
Code a Function • Type the following below the declarations shown on previous slide: Public Function GetData(ByVal whatData As String, Optional ByVal whatCriteria As String = "", Optional ByVal whatCompare As String = "=") As ArrayList • Then hit Enter to generate End Function
Declare and Initialize • Type the following code within the Function: Dim selectors As String Dim fromArg As String Dim orderArg As String Dim dataSize As Integer Dim dataSizeSmaller As Integer Dim sqlStr As String = "" selectors = " select Country " fromArg = " from Olympics " orderArg = " order by Country; "
Add Selection Code • Type: dataSizeSmaller = CInt(COUNTRY_COUNT / SIZE_FACTOR) 'reduces default size If whatData = "formLoad" Then sqlStr = selectors & fromArg & orderArg dataSize = COUNTRY_COUNT Else selectors += ", Gold, Silver, Bronze, Pop" dataSize = dataSizeSmaller
More Selection Code • Type: If whatData = "country" Then sqlStr = selectors & fromArg & " where Country " & whatCompare & "'" & whatCriteria & "'" & orderArg dataSize = dataSizeSmaller ElseIf whatData = "population" Then sqlStr = selectors & fromArg & " where Pop " & whatCompare & whatCriteria & orderArg
Finish Coding BizTier Module • Type: ElseIf whatData = "golds" Then sqlStr = selectors & fromArg & " where Gold " & whatCompare & whatCriteria & orderArg End If End If • Type the following just above End Function: Console.WriteLine(sqlStr) 'for debugging Return DataSQL.AcquireData(sqlStr, dataSize) • Run the code and fix any compilation errors • Note: you won't get any output yet
Add Code for Form Selections • In the frmUI.vb file, add the following lines just below the Class statement at top of file: Private selector As String Private comparer As String • Replace the line for loading the ArrayList from the DataSQL Module in the frmUI_Load Sub with: alsData = BizTier.GetData("formLoad") • In the design window, double-click all of the buttons to generate Subs for their code
Add Code to Clear Selections • Add a Sub to clear selections: Private Sub ClearSelections() lstOutputs.SelectedItem = Nothing lstGolds.SelectedItem = Nothing cboCountries.SelectedItem = Nothing chkPop.Checked = False End Sub • Put following into Sub for btnClear: ClearSelections() lstOutputs.DataSource = Nothing 'clear outputs
Add Country Selection Code • Put following into Sub to select countries: If cboCountries.SelectedIndex = -1 Then MessageBox.Show("Please select a country") Else selector = cboCountries.SelectedItem.ToString.Replace("'", "''") 'single quote in 1st string, 2 single quotes in 2nd lstOutputs.DataSource = BizTier.GetData("country", selector, "=") ClearSelections() 'undo all selections cboCountries.SelectedItem = selector 'restore this End If
Add Gold Medal Selections • Add the following code for btnGolds: If lstGolds.SelectedIndex = -1 Then MessageBox.Show("Please select number of gold medals") Else selector = lstGolds.SelectedItem.ToString comparer = "=" lstOutputs.DataSource = BizTier.GetData("golds", selector, comparer) ClearSelections() lstGolds.SelectedItem = selector End If
Add Large Population Selection • Add the following code for btnPop: Dim bigPop As Boolean comparer = "" bigPop = chkPop.Checked If bigPop Then comparer = ">=" Else comparer = "<" End If lstOutputs.DataSource = BizTier.GetData("population", "100", comparer) ClearSelections() chkPop.Checked = bigPop
Test and Fine Tune • Run the code and test all the buttons and selection possibilities • Be sure to verify that current selections stay visible but all others are cleared on each click • Verify that the outputs data is correct for each selection • Be sure that every Button gets clicked several times before you click the Exit button • Fine tune form appearance and usability • Appearance should be pleasing and professional • Form should be easy and “natural” to use • Form should be as compact as possible for user