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

Professor ralph westfall october 2009 sample code l.jpg
Download
1 / 38

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 l.jpg

Professor Ralph Westfall

October 2009

Sample Code

Using SQL Server Express with Visual Basic.NET


Create visual basic net project l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

More Code for Database

con.Open()‏

da = New SqlDataAdapter(queryStr, con)‏ 'OleDbDataAdapter

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


Add code to load arraylist l.jpg

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 l.jpg

Finish Function and Module

  • Type:

    con.Close()‏

    da = Nothing

    ds = Nothing

    Return alsData


Code the form l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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