Utilizing SQL Server Express with Visual Basic.NET .

Uploaded on:
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
Slide 1

Teacher Ralph Westfall October 2009 Sample Code Using SQL Server Express with Visual Basic.NET

Slide 2

Create Visual Basic.NET Project Start>All Programs>Microsoft Visual Studio 2008 Click Project join 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 arrangement if available>]browse to the Desktop>Save File>Exit

Slide 3

SQL Server Express Versions You may get SQL Server Express 2005 when you introduce Visual Studio Professional 2008 If along these lines, you ought to likewise introduce SQL Server Express 2008 to run with it ( download the x64 rendition for Vista, or the x86 one for different Windows adaptations) If you can abstain from introducing SQL Server Express 2005 when introducing Visual Basic, it will make it less demanding to introduce SQL Server Express 2008

Slide 4

Set Up SQL Server Express I was experiencing difficulty interfacing with SQL Server 2008 until I did the accompanying See the accompanying slide if these menu decisions aren\'t accessible Start>All Programs>Microsoft SQL Server 2008>Configuration Tools>SQL Server [Surface Area] Configuration Manager click lower interface that says Surface Area Configuration for Services and Connections Click Remote Connections>Local and remote connections>Using TCP/IP only>OK

Slide 5

Set Up SQL Server Express I introduced a later form and it was this way: 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

Slide 6

Create a SQL Server Database Start>All Programs>Microsoft SQL Server 2008>SQL Server Management Studio>OK Be certain Windows Authentication is being utilized, record the Server name:>click Connect Expand server name and after that Databases in Object Explorer window on lower left if get mistake message, minimize, click revive catch at top of Object Explorer and attempt again issue might be a database that wasn\'t isolates before cancellation right-click Databases>New Database>type Olympics as Database name:>OK

Slide 7

Import Data File for taking after guidelines: olympics.csv Save documents as .csv>Yes (to keep position) See Saving Changes Not Permitted if an issue Right-click Olympics (database name)> Tasks>Import Data>Next> select Flat File Source>Browse and select Files of sort: CSV documents (*.csv) to get olympics.csv>select Format: Delimited>Next> Use Format: Ragged a good fit for *.txt documents Next (Use Windows authentication)>Next>Next> Next>Finish> Close

Slide 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 (however leave checked for different fields) ‏ Right-click ID>Set Primary Key In Column properties beneath, grow Identity Specification, set (IsIdentity) as Yes, set Identity seed as 100

Slide 9

Modify Fields, View Data Change field names and properties: Next line after ID ought to be named Country with Data Type of nchar(20) ‏ Next three lines ought to be Gold, Silver, Bronze, each with Data Type of int Last line ought to be Pop, with Data Type of decimal(7, 2) ‏ File>Save Olympics>Yes (overlook notices) ‏ Right-click table name>Edit Top 200 Rows Verify that legitimate information is in right-most fields Edit information if important to settle issues

Slide 10

Field Length Data Types int 4 bytes, values range from ± 2 billion scorch is number of bytes in brackets Reserves that much space for fields Use nchar for worldwide applications varchar is for variable length content fields Only uses as much space as is required for every field (spares space in database) ‏ Use nvarchar for global applications decimal (x, y) is more exact than buoy for decimal qualities (x=total digits, y=decimals) ‏ Can have missing qualities with Allow Nulls Never let ID field worth be absent!

Slide 11

Detach Database Right-click Olympics (database name) in Object Explorer>Facets>select and duplicate PrimaryFilePath>OK Open File Explorer and glue that way into location box Close the Design and/or Edit windows on the privilege of Management studio>right-click Olympics>Tasks>Detach>check Drop>OK Close Management Studio

Slide 12

Move SQL Server Database Select and Cut Olympics.mdf and Olympics_log.ldf documents in Windows Explorer Create another organizer named cis338 on the C:\ drive Then glue the two records into that registry

Slide 13

Microsoft Access 2007 File for taking after guidelines: 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] envelope and Save the document in that directory>Click Create

Slide 14

Microsoft Access Data Fields Click External Data tab>click Import content document icon>Import the source information into another table>browse to olympics.csv file>OK>Delimited>Next>Comma>Next> Change Fieldnames to Country, Gold, Silver, Bronze, Pop Make Country be Text, the awards be Integer, and Pop be Double>Next Let Access include essential key>Next Leave Table name as Olympics>Finish>Close Close Table1

Slide 15

Restart Visual Basic Project Double tap the Olympics-SQL.sln document in Olympics-SQL organizer on the Desktop to restart Visual Studio 2008 Click Form1 in Solution Explorer and after that the symbol to view it in the creator window Right-click Form1 in originator window> Properties Change (Name) to frmUI Change Text to Olympics Data Click and drag right half of structure to make it more extensive

Slide 16

Add Controls Go to shape fashioner window and drag the accompanying onto the accompanying areas on structure: 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 1 st Label: Label, set Text as # of Golds Under 2 nd Label: ListBox, (Name) lstGolds Click Items (Collection) in lstGolds and sort 1 through 10 on independent rows>OK

Slide 17

More Controls To right of 2 nd Label: CheckBox, (Name) chkPop, set Text as Large Population Click drop down on right of Text to make 2 lines Put a different Buttons beneath every 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

Slide 18

Output ListBox Add another ListBox extending over the base of the Form, (Name) lstOutputs Add mark over: Text=Countries G S B Pop Set Font to Courier New so yields will arrange in sections Align controls and Labels to look proficient

Slide 19

Add a Module: Data Tier Project>Add Module>Name: DataSQLSS.vb>OK Type: Imports System.Data.SqlClient over the Module proclamation Imports System.Data.OleDb \'Use for MS Access Below the Module explanation, sort: Private cushions() As Integer = {19, 3, 3, 3, 7} Public Function AcquireData(ByVal queryStr As String, ByVal startSize As Integer) As ArrayList Then hit Enter to produce End Function

Slide 20

Add Variable Declarations Type the accompanying 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

Slide 21

Add Code for Database Below the presentations, sort taking after 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 is the record sort \'or con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & " C:\ cis338 \ Olympics.accdb;" [thanks to Karla Gutierrez Lopez]

Slide 22

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

Slide 23

Add Code to Load ArrayList Next, sort: alsData = New ArrayList(startSize) ‏ Dim i as Integer \'climb into different 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 should settle for left cushioning numbers

Slide 24

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

Slide 25

Code the Form Double-tap the structure in the architect Add the accompanying assertion over the frmUI_Load Sub: Private alsData As ArrayList Add the accompanying code to the frmUI_Load Sub alsData = DataSQLSS.AcquireData("select Country from Olympics request by Country", 80) For Each thing In alsData cboCountries.Items.Add(Trim(item.ToString)) Next

Slide 26

Test the Data Tier and Form Double tap the Exit catch and afterward put the word End in the created Sub Now run the code and make adjustments if fundamental

Slide 27

Add a Module: Business Tier Project>Add Module>name BizTier.vb>OK Type code underneath the Module articulation: 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 decreasing ArrayList size for choices

Slide 28


View more...