Using SQL Server Express with Visual Basic.NET

1. Professor Ralph Westfall April 2011 Sample Code Using SQL Server Express with Visual Basic.NET

2. 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

3. 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

4. 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

5. 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

6. 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

7. Import Data • File for following instructions: olympics.csv • Opens in Excel, save from there>Yes (to keep CSV format, then No on save changes) • 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 >Next (Use Windows authentication)> Next> click Edit Mappings button

8. Modify Fields, View Data • Change field names and properties in Destination column: • 1st column = Country, Data Type = 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 with Precision = 7, Scale = 2>OK • Create destination table should be checked • Next>Next>Next>Finish> Close

9. Modify Database Structure • Expand database name in Object Explorer • Right-click Olympics>Refresh • 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 or 101 (makes all keys same length)

10. Saving New Database Table • File>Save Olympics>Yes (ignore warnings)‏ • If still can’t save: Tools>Options>Designers> uncheck Prevent saving changes … >OK and then try to save again • Right-click table name>Edit Top 200 Rows • Verify that proper data is in right-most fields (integers in Bronze, decimals in Pop • Edit data if necessary to fix problems

11. 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 • nchar is for international (Unicode) 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 allow ID field value to be missing!

12. 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

13. 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

14. 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

15. 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 Field Names 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>Exit Access

16. 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

17. 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

18. More Controls • Add a CheckBox to right of 2nd Label with (Name) = chkPop, Text = Populous? • Put * 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 * grammar error (superfluous ‘a’) spotted by Cameron Berlino

19. Output ListBox • Add another ListBox stretching across the bottom of the Form, (Name) lstOutputs • Add label over it: Text=Countries G S B Pop • Set ListBox Font to Courier New so that outputs will line up in columns • Align controls and Labels to look professional

20. Add a Module: Data Tier • Project>Add Module>Name: DataSS.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

21. 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

22. 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 Lopez]

23. More Code for Database con.Open() da = New SqlDataAdapter(queryStr, con) 'OleDbDataAdapter da.Fill(ds) 'da.Fill(ds, "Olympics")

24. 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 use PadLeft for left padding numbers

25. Finish Function and Module • Type: con.Close() da = Nothing ds = Nothing Return alsData

26. 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 = DataSS.AcquireData("select Country from Olympics order by Country", 80) For Each item In alsData cboCountries.Items.Add(Trim(item.ToString)) Next

27. Test the Data Tier and Form • Double click the Exit button on the Form and then put the word End in the generated Sub • Now run the code and make corrections if necessary • might be easier to just copy code from this PowerPoint over again where problems are found • and/or look at code/copy from Sample Code zip

28. 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

29. 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 • With cursor at end of above line, hit Enter to generate End Function

30. 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; "

31. 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

32. 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

33. 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 DataSS.AcquireData(sqlStr, dataSize) • Run the code and fix any compilation errors • Note: you won't get any output yet

34. 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 (starts with alsData =) with : alsData = BizTier.GetData("formLoad") • In the design window, double-click all of the buttons to generate Subs for their code

35. 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 'clears outputs

36. 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, "=") lstOutputs.DataSource = BizTier.GetData("country", selector, "=") cboCountries.SelectedItem = selector 'restore this End If

37. 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

38. 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

39. 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