1 / 38

Using SQL Server Express with Visual Basic.NET

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

sherlock_clovis
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Professor Ralph Westfall October 2009 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 • 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

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

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

  10. 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!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related