Using SQL Server Express with Visual Basic NET

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

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 x 64 version for Vista, or the x 86 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 Local. System, right-click SQL Server (SQLEXPRESS)>Properties>Built-in account: >select Local. System>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 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

Modify Fields, View Data Change field names and properties in Destination column: 1 st 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>Finish> Close

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 (Is. Identity) as Yes, set Identity Seed as 100 or 101 (makes all keys same length)

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

Field Length Data Types int 4 bytes, values range from ± 2 billion char is number of bytes in parentheses varchar is for variable length text fields Reserves that much space for fields nchar is for international (Unicode) applications 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!

Detach Database Right-click Olympics (database name) in Object Explorer>Facets>select and copy Primary. File. Path>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 cis 338 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: cis 338 [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 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 Table 1>Exit Access

Restart Visual Basic Project Double click the Olympics-SQL. sln file in Olympics-SQL folder on the Desktop to restart Visual Studio 2008 Click Form 1 in Solution Explorer and then the icon to view it in the designer window Right-click Form 1 in designer window> Properties Change (Name) to frm. UI 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: Combo. Box, (Name) cbo. Countries, change Drop. Down. Style to Simple, Sorted to True, Font to Courier New To right of 1 st Label: Label, set Text as # of Golds Under 2 nd Label: List. Box, (Name) lst. Golds Click Items (Collection) in lst. Golds and type 1 through 10 on separate rows>OK

More Controls Add a Check. Box to right of 2 nd Label with (Name) = chk. Pop, Text = Populous? Put * separate Buttons below each control Name them btn. Country, btn. Golds, btn. Pop and set the Text for each as Select Add two more Buttons: btn. Clear and btn. Exit, with Text of Clear and Exit * grammar error (superfluous ‘a’) spotted by Cameron Berlino

Output List. Box Add another List. Box stretching across the bottom of the Form, (Name) lst. Outputs Add label over it: Text=Countries G S B Pop Set List. Box 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: Data. SS. vb>OK Type: Imports System. Data. Sql. Client above the Module statement Imports System. Data. Ole. Db 'Use for MS Access Below the Module statement, type: Private pads() As Integer = {19, 3, 3, 3, 7} Public Function Acquire. Data(By. Val query. Str As String, By. Val start. Size As Integer) As Array. List Then hit Enter to generate End Function

Add Variable Declarations Type the following in the new Function Dim als. Data As Array. List Dim da As Sql. Data. Adapter Dim ds As Data. Set Dim con As Sql. Connection Dim dr As Data. Row Dim dc As Data. Column Dim row. Data As String '* '* Ole. Db. Data. Adapter for Access '** Ole. Db. Connection for Access

Add Code for Database Below the declarations, type following lines: ds = New Data. Set con = New Sql. Connection 'Ole. Db. Connection con. Connection. String = "server=(local)SQLEXPRESS; Attach. Db. Filename=" & "C: cis 338Olympics. mdf; " & "Integrated Security=True; " 'or Olympics. dbo if that's the file type 'or con. Connection. String = "Provider=Microsoft. Jet. OLEDB. 4. 0; Data Source=" & "C: cis 338Olympics. accdb; " [thanks to Karla Lopez]

More Code for Database con. Open() da = New Sql. Data. Adapter(query. Str, con) 'Ole. Db. Data. Adapter da. Fill(ds) 'da. Fill(ds, "Olympics")

Add Code to Load Array. List Next, type: als. Data = New Array. List(start. Size) Dim i as Integer 'move up into other declations For Each dr In ds. Tables(0). Rows row. Data = "" For Each dc In ds. Tables(0). Columns row. Data += Trim(dr(dc). To. String()). Pad. Right(pads(i)) '* i += 1 Next i=0 als. Data. Add(row. Data) Next * will need to use Pad. Left for left padding numbers

Finish Function and Module Type: con. Close() da = Nothing ds = Nothing Return als. Data

Code the Form Double-click the Form in the designer Add the following declaration above the frm. UI_Load Sub: Private als. Data As Array. List Add the following code to the frm. UI_Load Sub als. Data = Data. SS. Acquire. Data("select Country from Olympics order by Country", 80) For Each item In als. Data cbo. Countries. Items. Add(Trim(item. To. String)) Next

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 Power. Point over again where problems are found and/or look at code/copy from Sample Code zip

Add a Module: Business Tier Project>Add Module>name Biz. Tier. 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 Array. List size Private Const SIZE_FACTOR As Integer = 3 'for reducing Array. List size for selections

Code a Function Type the following below the declarations shown on previous slide: Public Function Get. Data(By. Val what. Data As String, Optional By. Val what. Criteria As String = "", Optional By. Val what. Compare As String = "=") As Array. List With cursor at end of above line, hit Enter to generate End Function

Declare and Initialize Type the following code within the Function: Dim selectors As String Dim from. Arg As String Dim order. Arg As String Dim data. Size As Integer Dim data. Size. Smaller As Integer Dim sql. Str As String = "" selectors = " select Country " from. Arg = " from Olympics " order. Arg = " order by Country; "

Add Selection Code Type: data. Size. Smaller = CInt(COUNTRY_COUNT / SIZE_FACTOR) 'reduces default size If what. Data = "form. Load" Then sql. Str = selectors & from. Arg & order. Arg data. Size = COUNTRY_COUNT Else selectors += ", Gold, Silver, Bronze, Pop" data. Size = data. Size. Smaller

More Selection Code Type: If what. Data = "country" Then sql. Str = selectors & from. Arg & " where Country " & what. Compare & "'" & what. Criteria & "'" & order. Arg data. Size = data. Size. Smaller Else. If what. Data = "population" Then sql. Str = selectors & from. Arg & " where Pop " & what. Compare & what. Criteria & order. Arg

Finish Coding Biz. Tier Module Type: Else. If what. Data = "golds" Then sql. Str = selectors & from. Arg & " where Gold " & what. Compare & what. Criteria & order. Arg End If Type the following just above End Function: Console. Write. Line(sql. Str) 'for debugging Return Data. SS. Acquire. Data(sql. Str, data. Size) Run the code and fix any compilation errors Note: you won't get any output yet

Add Code for Form Selections In the frm. UI. 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 Array. List from the Data. SQL Module in the frm. UI_Load Sub (starts with als. Data =) with : als. Data = Biz. Tier. Get. Data("form. Load") 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 Clear. Selections() lst. Outputs. Selected. Item = Nothing lst. Golds. Selected. Item = Nothing cbo. Countries. Selected. Item = Nothing chk. Pop. Checked = False End Sub Put following into Sub for btn. Clear: Clear. Selections() lst. Outputs. Data. Source = Nothing 'clears outputs

Add Country Selection Code Put following into Sub to select countries: If cbo. Countries. Selected. Index = -1 Then Message. Box. Show("Please select a country”) Else selector = cbo. Countries. Selected. Item. To. String. Replace("'", "''") 'single quote in 1 st string, 2 single quotes in 2 nd lst. Outputs. Data. Source = Biz. Tier. Get. Data("country", selector, "=") cbo. Countries. Selected. Item = selector 'restore this End If

Add Gold Medal Selections Add the following code for btn. Golds: If lst. Golds. Selected. Index = -1 Then Message. Box. Show("Please select number of gold medals") Else selector = lst. Golds. Selected. Item. To. String comparer = "=" lst. Outputs. Data. Source = Biz. Tier. Get. Data("golds", selector, comparer) Clear. Selections() lst. Golds. Selected. Item = selector End If

Add Large Population Selection Add the following code for btn. Pop: Dim big. Pop As Boolean comparer = "" big. Pop = chk. Pop. Checked If big. Pop Then comparer = ">=" Else comparer = "<" End If lst. Outputs. Data. Source = Biz. Tier. Get. Data("population", "100", comparer) Clear. Selections() chk. Pop. Checked = big. Pop

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