Professor ralph westfall october 2009 sample code
1 / 38

Using SQL Server Express with Visual Basic.NET - PowerPoint PPT Presentation

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Download Presentation

Using SQL Server Express with Visual Basic.NET

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

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


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


    i = 0



    • will need to fix for left padding numbers

Finish Function and Module

  • Type:


    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



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


    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:


    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")‏


    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")‏


    selector = lstGolds.SelectedItem.ToString

    comparer = "="

    lstOutputs.DataSource = BizTier.GetData("golds", selector, comparer)‏


    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 = ">="


    comparer = "<"

    End If

    lstOutputs.DataSource = BizTier.GetData("population", "100", comparer)‏


    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

  • Login