1: Imports System
2: Imports System.Collections.Generic
3: Imports System.ComponentModel
4: Imports System.Data
5: Imports System.Drawing
6: Imports System.Text
7: Imports System.Windows.Forms
8: Imports System.Data.SqlServerCe
9: Imports System.IO
10:
11: Public Class Form1
12:
13: #Region "btnCreateDatabase_Click"
14: ‘ Create an empty SSCE Database with a password.
15: ‘ Note that when creating a db with code, adding a
16: ‘ password automatically encrypts the database
17: Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _
18: ByVal e As System.EventArgs) Handles btnCreateDatabase.Click
19:
20: Dim connectString As String = ""
21: Dim fileName As String = "ArcaneCode.sdf"
22: Dim password As String = "arcanecode"
23:
24: If File.Exists(fileName) Then
25: File.Delete(fileName)
26: End If
27:
28: connectString = String.Format( _
29: "DataSource=""{0}""; Password=’{1}’", fileName, password)
30:
31: Dim eng As SqlCeEngine = _
32: New SqlCeEngine(connectString)
33: eng.CreateDatabase()
34:
35: lblResults.Text = "Database Created"
36:
37: End Sub
38: #End Region
39:
40: #Region "btnCreateTable_Click"
41: ‘ Issue a SQL command to create a table
42: ‘ Note this only creates the table, it
43: ‘ does not put any rows in it.
44: Private Sub btnCreateTable_Click(ByVal sender As System.Object, _
45: ByVal e As System.EventArgs) Handles btnCreateTable.Click
46:
47: Dim cn As New SqlCeConnection(ConnectString())
48:
49: If cn.State = ConnectionState.Closed Then
50: cn.Open()
51: End If
52:
53: Dim cmd As SqlCeCommand
54:
55: Dim sql As String = "create table CoolPeople (" _
56: + "LastName nvarchar (40) not null, " _
57: + "FirstName nvarchar (40), " _
58: + "URL nvarchar (256) )"
59:
60: cmd = New SqlCeCommand(sql, cn)
61:
62: Try
63: cmd.ExecuteNonQuery()
64: lblResults.Text = "Table created."
65: Catch sqlexception As SqlCeException
66: MessageBox.Show(sqlexception.Message, "Oh Crap." _
67: , MessageBoxButtons.OK, MessageBoxIcon.Error)
68: Catch ex As Exception
69: MessageBox.Show(ex.Message, "Oh Crap." _
70: , MessageBoxButtons.OK, MessageBoxIcon.Error)
71: Finally
72: cn.Close()
73: End Try
74:
75: End Sub
76: #End Region
77:
78: #Region "btnLoadTable_Click"
79: ‘ This routine calls a subroutine that
80: ‘ does the real work of inserting rows
81: ‘ into the database.
82: Private Sub btnLoadTable_Click(ByVal sender As System.Object, _
83: ByVal e As System.EventArgs) Handles btnLoadTable.Click
84:
85: Try
86: LoadARow("Scott", "Hanselman", "http:\\www.hanselminutes.com")
87: LoadARow("Wally", "McClure", "http:\\aspnetpodcast.com/CS11/Default.aspx")
88: LoadARow("John", "Dvorak", "http:\\www.crankygeeks.com")
89: LoadARow("Arcane", "Code", "http:\\arcanecode.wordpress.com")
90: Catch ex As Exception
91: MessageBox.Show(ex.Message, "Oh Crap.", _
92: MessageBoxButtons.OK, MessageBoxIcon.Error)
93: End Try
94:
95: End Sub
96: #End Region
97:
98: #Region "ConnectString"
99: ‘ A central place to serve up the connection string
100: Private Function ConnectString() As String
101:
102: Dim connectionString As String
103: Dim fileName As String = "ArcaneCode.sdf"
104: Dim password As String = "arcanecode"
105:
106: connectionString = String.Format( _
107: "DataSource=""{0}""; Password=’{1}’", fileName, password)
108:
109: Return connectionString
110:
111: End Function
112: #End Region
113:
114: #Region "LoadARow"
115: ‘ Generates the SQL and issues the command to
116: ‘ insert a single row into the database
117: Private Sub LoadARow(ByVal first As String, _
118: ByVal last As String, ByVal url As String)
119:
120: Dim cn As New SqlCeConnection(ConnectString())
121:
122: If cn.State = ConnectionState.Closed Then
123: cn.Open()
124: End If
125:
126: Dim cmd As SqlCeCommand
127:
128: Dim sql As String = "insert into CoolPeople " _
129: + "(LastName, FirstName, URL) " _
130: + "values (@lastname, @firstname, @url)"
131:
132: Try
133: cmd = New SqlCeCommand(sql, cn)
134: cmd.Parameters.AddWithValue("@lastname", last)
135: cmd.Parameters.AddWithValue("@firstname", first)
136: cmd.Parameters.AddWithValue("@url", url)
137: cmd.ExecuteNonQuery()
138: lblResults.Text = "Row Added."
139: Catch sqlexception As SqlCeException
140: MessageBox.Show(sqlexception.Message, "Oh Crap.", _
141: MessageBoxButtons.OK, MessageBoxIcon.Error)
142: Catch ex As Exception
143: MessageBox.Show(ex.Message, "Oh Crap.", _
144: MessageBoxButtons.OK, MessageBoxIcon.Error)
145: Finally
146: cn.Close()
147: End Try
148:
149: End Sub
150: #End Region
151:
152: #Region "btnLoadGrid_Click"
153: Private Sub btnLoadGrid_Click(ByVal sender As System.Object _
154: , ByVal e As System.EventArgs) Handles btnLoadGrid.Click
155:
156: Dim cn As New SqlCeConnection(ConnectString())
157:
158: If cn.State = ConnectionState.Closed Then
159: cn.Open()
160: End If
161:
162: Try
163: ‘ Set the command to use the table, not a query
164: Dim cmd As SqlCeCommand = New SqlCeCommand("CoolPeople", cn)
165: cmd.CommandType = CommandType.TableDirect
166:
167: ‘ Get the Table
168: Dim rs As SqlCeResultSet = cmd.ExecuteResultSet( _
169: ResultSetOptions.Scrollable)
170:
171: ‘ Load the result set into the database
172: dgvCoolPeople.DataSource = rs
173:
174: Catch sqlexception As SqlCeException
175: MessageBox.Show(sqlexception.Message, "Oh Crap.", _
176: MessageBoxButtons.OK, MessageBoxIcon.Error)
177: Catch ex As Exception
178: MessageBox.Show(ex.Message, "Oh Crap.", _
179: MessageBoxButtons.OK, MessageBoxIcon.Error)
180: End Try
181: ‘ Note, do not close the connection,
182: ‘ if you do the grid won’t be able to display.
183: ‘ For production code you probably want to make
184: ‘ your result set (rs) a class level variable
185:
186: End Sub
187: #End Region
188:
189: #Region "btnReadRecords_Click"
190: Private Sub btnReadRecords_Click(ByVal sender As System.Object _
191: , ByVal e As System.EventArgs) Handles btnReadRecords.Click
192:
193: Dim cn As New SqlCeConnection(ConnectString())
194:
195: If cn.State = ConnectionState.Closed Then
196: cn.Open()
197: End If
198:
199: ‘ Build the sql query. If this was real life,
200: ‘ I’d use a parameter for the where bit
201: ‘ to avoid SQL Injection attacks.
202: Dim sql As String = "select LastName, FirstName from CoolPeople "
203: If txtName.Text.Length > 0 Then
204: sql += "where LastName like ‘" + txtName.Text + "%’ "
205: End If
206:
207: Try
208:
209: Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn)
210: cmd.CommandType = CommandType.Text
211:
212: ‘ if you don’t set the result set to
213: ‘ scrollable HasRows does not work
214: Dim rs As SqlCeResultSet = cmd.ExecuteResultSet( _
215: ResultSetOptions.Scrollable)
216:
217: If rs.HasRows Then
218:
219: ‘ Use the get ordinal function so you don’t
220: ‘ have to worry about remembering what
221: ‘ order your SQL put the field names in.
222: Dim ordLastName As Integer = rs.GetOrdinal("LastName")
223: Dim ordFirstName As Integer = rs.GetOrdinal("FirstName")
224:
225: ‘ Hold the output
226: Dim output As StringBuilder = New StringBuilder()
227:
228: ‘ Read the first record and get it’s data
229: rs.ReadFirst()
230: output.AppendLine(rs.GetString(ordFirstName) _
231: + " " + rs.GetString(ordLastName))
232:
233: ‘ Now read thru the rest of the records.
234: ‘ When there’s no more data, .Read returns false.
235: Do While rs.Read()
236: output.AppendLine(rs.GetString(ordFirstName) _
237: + " " + rs.GetString(ordLastName))
238: Loop
239:
240: ‘ Set the output in the label
241: lblResults.Text = output.ToString()
242: Else
243: lblResults.Text = "No Rows Found."
244: End If
245:
246: Catch sqlexception As SqlCeException
247: MessageBox.Show(sqlexception.Message, "Oh Crap.", _
248: MessageBoxButtons.OK, MessageBoxIcon.Error)
249: Catch ex As Exception
250: MessageBox.Show(ex.Message, "Oh Crap.", _
251: MessageBoxButtons.OK, MessageBoxIcon.Error)
252: Finally
253: ‘ Don’t need it anymore so we’ll be good and close it.
254: ‘ in a ‘real life’ situation
255: ‘ cn would likely be class level
256: cn.Close()
257: End Try
258:
259: End Sub
260: #End Region
261: End Class
Posts
