professor ralph westfall april 2011 sample code l.
Skip this Video
Loading SlideShow in 5 Seconds..
Using SQL Server Express with Visual Basic.NET PowerPoint Presentation
Download Presentation
Using SQL Server Express with Visual Basic.NET

Loading in 2 Seconds...

play fullscreen
1 / 39

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

  • Uploaded on

Professor Ralph Westfall April 2011 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

PowerPoint Slideshow about 'Using SQL Server Express with Visual Basic.NET' - mare

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
create visual basic net project
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
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
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 express5
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
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
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
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
modify database structure
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)
saving new database table
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
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!
detach database
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
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
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
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
restart visual basic project
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
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
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

output listbox
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
add a module data tier
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
add variable declarations
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
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]

more code for database
More Code for Database


da = New SqlDataAdapter(queryStr, con) 'OleDbDataAdapter

da.Fill(ds) 'da.Fill(ds, "Olympics")

add code to load arraylist
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 use PadLeft for left padding numbers

finish function and module
Finish Function and Module
  • Type:


da = Nothing

ds = Nothing

Return alsData

code the form
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



test the data tier and form
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
add a module business tier
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
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
declare and initialize
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
Add Selection Code
  • Type:

dataSizeSmaller = CInt(COUNTRY_COUNT / SIZE_FACTOR) 'reduces default size

If whatData = "formLoad" Then

sqlStr = selectors & fromArg & orderArg



selectors += ", Gold, Silver, Bronze, Pop"

dataSize = dataSizeSmaller

more selection code
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
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
add code for form selections
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
add code to clear selections
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 'clears outputs

add country selection code
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, "=")

lstOutputs.DataSource =

BizTier.GetData("country", selector, "=")

cboCountries.SelectedItem = selector 'restore this

End If

add gold medal selections
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 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
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