Smart Device Part 3

During the last weeks we made our GUI we create our form to insert records in our database now we must make our program to check if the database exist and if no to create it in the same directory.
First of all add reference System.Data.SqlServerCe 

 
To do this double click my project in solution explorer and go to references add and find out the above reference
Now create a module and name it functions or what ever you like.
Paste the code
What this code does
Declare the variables for connection to the database
And some functions to get the path of the application, query the database for insert update etc and create database function.

Imports System.Data.SqlServerCe
Module Functions
Public dbname As String = "database.sdf"
Public connstr As String = "Data Source = " & getpath() & dbname & ";"
Dim cn As New SqlCeConnection(connstr)
Function getpath()
Dim temp As String
temp = System.IO.Path.GetDirectoryName _
(System.Reflection.Assembly. _
GetExecutingAssembly.GetName().CodeBase)
Return temp + "\"
End Function
Public Sub esql(ByVal sql As String)
Dim cn As New SqlCeConnection(connstr)
Dim cmd As New SqlCeCommand(sql, cn)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Dispose()
cn.Dispose()
End Sub
Public Sub createDatabase()
Dim engine As SqlCeEngine
engine = New SqlCeEngine(connstr)
engine.CreateDatabase()
engine.Dispose()
End Sub
End Module

Now to make the application to check if the database exist we must say to or main form to check each time that loads to check and to create the database if it does not already exist
Double click on the main form and paste this code

If IO.File.Exists(getpath() & dbname) = False Then
Try
MessageBox.Show("Database Problem program will create new one")
createDatabase()
Dim sql As String
sql = "CREATE TABLE customers"
sql += " (id INT IDENTITY PRIMARY KEY, name nvarchar(50),"
sql += "surname nvarchar(50), address nvarchar(50), telephone INT)"
esql(sql)
MessageBox.Show("Successfully created")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End If

This code checks if the database exist by combining the path and the database name then shows a message that the database does not exist and that it will create a new one then uses the function createDatabase and the SQL command with the function esql finally informs the user with a success message the try finishes with a catch for all errors and if any kind of error happen the program informs the user...........
Now we will make the program to insert a record in the database!
To do this we do something simple
Paste this code at the end of the btnInsert

Dim sql As String
sql = "INSERT INTO customers (name,surname,address,telephone) "
sql += "VALUES ('" & name & "','"
sql += surname & "','" & address & "',"
sql += telephone & ")"
Try
esql(sql)
MessageBox.Show("Insert was successful");
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

In the next tutorial we will make a form to show all the data ......