microsoft.public.sqlserver.ce
for this topic. I only find a post from Darren Shaffer and I requested a document.
Unfortunately, there is not a clear example of how to create a mobile database (sdf) on a desktop machine which contains VS 2005 and SQL server 2005 by using Visual Basic 2005.
I am sure a clear example in VB for this problem would help a lot of people. Hence anybody who knows how to perform this task, Please help these people.
Thanks in advance
TalatAfter a little bit of search, I succeeded! Here is the guidelines for using an SQL server mobile dataset residing in your desktop computer on your desjktop (populating a SQL Server mobile database on your desktop). At the end of the guidelines, I give a sample program to populate an sqlce database from a text file.
I hope you will find these guidelines and sample program usefull.
You may contact me at toncu@.nospam.e-kolay.net (remove nospam for actual address)
1. create a Windowsapplication project
File->New Project
Choose Windows in Project Types and Windows Apllication from Templates
2. Insert a reference to SQL Server Mobile
Project->Add Reference->Browse
c:\Program Files\Microsoft Visual Studio 8
\Common7\IDE\System.Data.SqlServerCE.DLL
3. Insert
imports system.data.sqlserverce
at the top of your form file
4. Write your application just as you would as if your project was a Smartdeviceapplication.
i.e. use sqlconnection and other constructs.
A sample application is as follows:
(dbtableexist is a subroutine which shows how to process the SCHEMA.
In this subroutine, change TABLES to INDEXES and TABLE_NAME to INDEX_NAME to search for an index)
Imports system.data.SqlServerCe
Imports System.IO
Public Sub createaccessfile(ByVal a$)
'curdbpath is the path of the database e.g. d:\my sql databases\
Dim connStr As String = "Data Source = " & curdbpath & a$ & ".sdf"
Dim conn As SqlCeConnection = Nothing
Dim command As SqlCeCommand
Try
conn = New SqlCeConnection(connStr)
conn.Open()
command = conn.CreateCommand()
If dbtableexist(conn, "ACCESSFILE") Then
command.CommandText = "DROP TABLE ACCESSFILE"
command.ExecuteNonQuery()
End If
command.CommandText = "CREATE TABLE ACCESSFILE ( code nchar(20),field nchar(3),totalrecs nvarchar(255),indexbits ntext)"
command.ExecuteNonQuery()
command.CommandText = "CREATE INDEX codeindex on ACCESSFILE (field,code)"
command.ExecuteNonQuery()
Catch ex As SqlCeException
Dim m1 = ex.Message
Dim m2 = ex.Source
Dim m3 = ex.NativeError
MsgBox(m1 & m2 & m3, , "open " & a$ & " for accessfile creation")
Exit Sub
End Try
' Create and prepare a SQL statement.
command.CommandText = " insert into accessfile values (?,?,?,?);"
' Read and insert the lines from the file until the end
' of the file is reached.
Dim accfname As String = curdbpath & a$ & "_accessfile.inp"
Dim fs As FileStream = New FileStream(accfname, FileMode.Open)
Dim sr As New StreamReader(fs)
Dim line As String, codestr As String, fieldstr As String, totalrecsstr As String, indexbitsstr As String
Dim line1 As String, flagstr As String, field As Integer
line = sr.ReadLine : line1 = line
Dim currec As Integer = 0
' Note: Even though named parameterized queries are not supported, we still need
' to provide unique parameter names so that we can manipulate parameter collection;
command.Parameters.Add("@.code", "a")
command.Parameters.Add("@.field", 1)
command.Parameters.Add("@.totrecs", "c")
command.Parameters.Add("@.indexbits", "d")
Do
currec = currec + 1
If currec Mod 10 = 0 Then dispcaption(Str(currec))
flagstr = before(line, Chr(9)) : line = after(line, Chr(9))
codestr = before(line, Chr(9)) : line = after(line, Chr(9))
field = Val(before(line, Chr(9))) : line = after(line, Chr(9))
totalrecsstr = before(line, Chr(9)) : line = after(line, Chr(9))
indexbitsstr = line
Try
command.Parameters(0).Value = codestr
command.Parameters(1).Value = Format(field, "000")
command.Parameters(2).Value = totalrecsstr
command.Parameters(3).Value = indexbitsstr
' Calling Prepare after having set the Commandtext and parameters.
command.Prepare()
command.ExecuteNonQuery()
Catch ex As SqlCeException
Dim m1 = ex.Message
Dim m2 = ex.Source
Dim m3 = ex.NativeError
MsgBox(m1 & m2 & m3, , "Add record to Acessfile")
conn.Close()
sr.Close()
Exit Sub
End Try
line = sr.ReadLine : line1 = line
Loop Until line Is Nothing
sr.Close()
conn.Close()
End Sub
Private Function dbtableexist(ByVal dbconnection As SqlCeConnection, ByVal tablename As String) As Boolean
Dim cmd As SqlCeCommand, rdr As SqlCeDataReader
Dim iname As String, tablename1 As String, i As Integer
tablename1 = UCase(tablename)
cmd = dbconnection.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from INFORMATION_SCHEMA.INDEXES"
Try
rdr = cmd.ExecuteReader
Catch ex As SqlCeException
Dim m1 = ex.Message
Dim m2 = ex.Source
Dim m3 = ex.NativeError
MsgBox(m1 & m2 & m3)
Stop
End Try
Do While rdr.Read
iname = rdr("TABLE_NAME")
If UCase(iname) = tablename1 Then dbtableexist = True : Exit Function
Loop
dbtableexist = False
End Function
