Creating DB for WinCE...

[gibookrishnan]

Hi,
I want to use database in my Project.I'm new to it too.
I'm doing project in C#,Visual Studio 2005,Compact Framework 2.0 and for WinCE.Plz. do help.I want an open source DB please.
Help Me.Tell me how to create a Database from the scratch or attach any link which describes that.Thanks in Advance.

[Ramy Mahrous]

You can use SQL Server Compact edition, but I don't know if there's open source RDBMS for smart phones or not.

[peter_budo]

These should help you, it worked for me ;)
Create a SQL Mobile database
Accessing SQL Server Express from the emulator

[gibookrishnan]

Hi,
Thanks peter_budo and RamyMahrous.But i would like to know one thing first.This should be free while development and while making the software as a product i.e while distributing the my software which will be using this Database.And i donot need much functionality in the DBMS i.e. only create database tables(no relation b/w those is reqd.) and access through the code and maintain it.And where can i get the materials for accessing the database tables only using the code and not any GUI as such.And what is SQL Compact Edition(Microsoft SQL Server 2005 Compact Edition : 3.1) and is it free.Thanks in Advance.

[Ancient Dragon]

First, there are no free databases for WinCE other than ADOCE and the CDatabase class that is supplied with the C++ compiler. I don't know if you can access that class with C# or not.

The next best thing for you to do is just write your own simple database, which is what I did a few years ago. Its not all that difficult to do.

[Ramy Mahrous]

I don't know about prices you can see EULA of each of which; SQL Server Compact edition shipped with SQL Server 2005 and you can install it alone. you can develop your application using VS and integrate with it.

[gibookrishnan]

Thanks RamyMahrous and Ancient Dragon.

To RamyMahrous :
You mean to say i can use it as a datasource??? I got that SDK from their site.But i cant find the SQL Compact Edition in the data sources list but i could find it in the namespace when i included that System.Data.SqlServerCe.dll.But before starting it i would like to know whether it is free to use or not...because my requirement says like that.Also where to find the material for the database operations in that DB.

To Ancient Dragon:
You mean to say the Embedded Database(EDB) and CE database(CEDB) support in the WinCE.Suggest me some materials where i can refer to create my own database in C#.

Thanks in Advance.

[peter_budo]

Win CE devices are already shipped with SQL Compact Edition already on them, plus they are easy to update as far I know. So there is not too much to device side. Question is, will your application on Win CE device retrieving data from external database? If so, it is not difficult to set up IIS server for testing purposes and there is also SQL Server 2005 free version, however I have no idea what will be cost of commercial setup...

[gibookrishnan]

Thanks peter_budo.
I donot want concepts such as accessing the external databases(external to devices).I want to create a database(some tables) only for my device and used only by my device for some calculation purpose and no networking concepts between devices through this database and this database will recide in my SD-Card or ROM thats it.And i have to use the database(tables) only through the code and the user of the device will not know the contents of the tables.

[peter_budo]

then there is nothing to worry about. Creating and maintaining database and tables through code is easy task

[gibookrishnan]

Thanks peter_budo.
But i am beginner to database creation(long back i studied in my college).Where can i get the materials and which Database.Help.
Thanks in Advance.

[peter_budo]

Here example which I did few months back. As you can see I do check if database exist to over-write to default values that can be removed or replaced by something more sensible. Also this show creation of only one database with one table but this can expand. Use your imagination to expand this example

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.IO;
using System.Windows.Forms;

namespace CW3_MobileDB
{
    class EmulatorDB
    {
        private string conStr = @"Data Source=.\\Program Files\\CW3_MobileDB\\emuDB.sdf";

        public void createNewDB()
        {
            if (System.IO.File.Exists(@".\\Program Files\\CW3_MobileDB\\emuDB.sdf"))
            {
                SqlCeConnection con = new SqlCeConnection();
                SqlCeCommand ceCmd = new SqlCeCommand();
                con.ConnectionString = conStr;

                con.Open();

                ceCmd.Connection = con;                
                ceCmd.CommandText = "DROP TABLE ComicsBook";
                ceCmd.ExecuteNonQuery();
                con.Close();
            }
            else
            {
                createDB();
            }
            
            createTableComicsBook();
            
            insertComicsBookData();
        }

        /// <summary>
        /// Create emulator database
        /// </summary>
        private void createDB()
        {
            SqlCeEngine engine = new SqlCeEngine();
            engine.LocalConnectionString = conStr;
            
            try
            {
                engine.CreateDatabase();
            }
            catch (SqlCeException sqlEx)
            {
                MessageBox.Show(sqlEx.ToString());
            }
            System.Diagnostics.Debug.WriteLine("emuDB created");
        }

        private void createTableComicsBook()
        {
            SqlCeConnection con = new SqlCeConnection();
            SqlCeCommand ceCmd = new SqlCeCommand();
            con.ConnectionString = conStr;

            con.Open();
            ceCmd.Connection = con;

            string sqlCmd = "CREATE TABLE ComicsBook"
                + "(bookID nvarchar(1000) not null, gender nvarchar (30) not null, title nvarchar (50) not null,"
                +"volume int, issueNum int, grade nvarchar (5) not null, secondGrade nvarchar (5), pricePaid float,"
                +" guideValue float, action nvarchar (3))";
            ceCmd.CommandText = sqlCmd;
            ceCmd.ExecuteNonQuery();
            con.Close();
            System.Diagnostics.Debug.WriteLine("Table ComicsBook created");
        }

        private void insertComicsBookData()
        {
            SqlCeConnection con = new SqlCeConnection();
            SqlCeCommand ceCmd = new SqlCeCommand();
            con.ConnectionString = conStr;

            con.Open();
            ceCmd.Connection = con;

            ceCmd.CommandText = "INSERT INTO ComicsBook"
                + "(bookID, gender, title, volume, issueNum, grade, secondGrade, pricePaid, guideValue)"
                + " VALUES('X-MEN-1963_105','Super-hero','X-MEN-1963','1','105','NM','None','1.1','200')";
            try
            {
                ceCmd.ExecuteNonQuery();
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.ToString());
            }

            ceCmd.CommandText = "INSERT INTO ComicsBook" 
                + "(bookID, gender, title, volume, issueNum, grade, secondGrade, pricePaid, guideValue)"
                + " VALUES('AMAZING SPIDERMAN_3','Super-hero','AMAZING SPIDERMAN','1','3','G','VG','7.5','25')";
            try
            {
                ceCmd.ExecuteNonQuery();
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.ToString());
            }            

            con.Close();
            System.Diagnostics.Debug.WriteLine("Value add to ComicsBook");
        }
    }
}

Also you can find some code examples here

[gibookrishnan]

Thanks peter_budo.Now only i saw your reply.Thanks a lot.I'll try the code.

[gibookrishnan]

Hello,

1) I tried the code but it showed "Can't find PInvoke DLL 'sqlceme30.dll'" in the line " SqlCeEngine engine = new SqlCeEngine(); " and "MissingMethodException was unhandled".And then i tried to include that dll in the References section but i guess it is unmanaged dll and could not be added in the references section.Then i tried include that dll using "[DllImport("sqlceme30")]" code but i couldn't find the function to P/Invoke with that dll.Then i referred to some forums and found some thread with the same problem occurance.But they say the cab files in the SDK should be included in the Windows folder.But I do not know how to install the cab files in the Windows folder.And where the cab files will be present for the SQL CE 3.1 version.
I do the project in VS2005,CF2.0,WinCE,for ARM Processor and for now for testing it I am testing in Pocket PC 2003 Emulator.
I also tried copying the dll in the shared folder for the device which is in my computer.

2) Is this SQL Compact Edition 3.1 free to Develop,use and distribute it with my application?

I have two SQL Compact Edition:

One from :

http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en

And another from :

http://www.microsoft.com/downloads/details.aspx?FamilyId=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en

Help me which one to use and which is free as mentioned above as per my requirements.
Thanks in Advance.