locked
single database for both windows and smartdecive applications RRS feed

  • Question

  • hello...

    i am developing mobile application for windows

    i have one database file (.sdf)

    and i want to use it from windows forms application  and also from smart device application

    ....

    please help me urgent...

    Tuesday, April 3, 2012 2:40 PM

Answers

All replies

  • Please describe in more detail what you would like to acheive - you can always copy the file to and from the device, but maybe it is Sync you need?

    Please mark as answer, if this was it. Visit my SQL Server Compact blog

    Tuesday, April 3, 2012 3:51 PM
  • Do you want centralized database
    Wednesday, April 4, 2012 7:14 AM
  • yes... i want to use centralized database either .mdf or .sdf no problem..

    but i need to use single database in both windows and smartdevice applications...

    Thursday, April 5, 2012 6:03 AM
  • Hi Chikkanti,

    Assuming you want to synchronize your SQL Compact SDF file to SQL Server MDF rather than synchronizing the files themselves.
    Please refer to
    1.  Tutorial: Synchronizing SQL Server and SQL Server Compact
     http://msdn.microsoft.com/en-us/library/ff928494%28v=sql.110%29.aspx
    2. Walkthrough: Creating an Occasionally Connected Application
     http://msdn.microsoft.com/en-us/library/bb384472.aspx

    Regards, Amber zhang

    • Marked as answer by amber zhang Wednesday, April 11, 2012 3:05 AM
    Friday, April 6, 2012 9:21 AM
  • There is always accessing the database file directly over activesync. Copying the database file is not required.

    As long as you have sqlce installed on the Windows Mobile device then your good to go. I haven't tried this with VS2010 as all my mobile development work (mainly barcode scanners) all run WinMo ~6.1, but it definitely works with 2008

    Basically, instead of referencing, and using System.Data.SqlServerCe like you do on the mobile device, on the desktop you reference Microsoft.SqlServerCe.Client.dll and import the namespace (Using Microsoft.SqlServerCe.Client;)

    This file, for me, was located at the following path c:\program files (x86)\Microsoft Visual Studio 9.0\Common7\IDE

    After that - you are basically good to go. 

    I've put together  bit of a messy class for you to see how to use it.

    using System;
    using Microsoft.SqlServerCe.Client;
    using System.IO;
    using System.Data;
    
    namespace WindowsClient.Classes
    {
        class DataSource
        {
            private SqlCeConnection _connection;
            private DataSet _dataset;
            private SqlCeDataAdapter _sqlDataAdapter;
    
            public DataSet ExecuteQuery(string database, string query,string tablename)
            {
                try
                {
                    using (_connection = new SqlCeConnection { ConnectionString = @"Data Source= Mobile Device\My Documents\" + database + ";" })
                    {
                        _connection.Open();
                    _dataset = new DataSet("MyDataSet");
                    var sql = query;
                    _sqlDataAdapter = new SqlCeDataAdapter(sql, _connection);
                    _sqlDataAdapter.FillSchema(_dataset, SchemaType.Source, tablename);
                    _sqlDataAdapter.Fill(_dataset, tablename);
                    _connection.Close();
                    return _dataset;
                }
                }
                catch (Exception ee)
                {
                    using (StreamWriter sw = new StreamWriter("LOG.TXT", true))
                    {
                        sw.WriteLine("New Error @ " + DateTime.Now);
                        sw.WriteLine(ee.Message);
                        sw.WriteLine(Environment.NewLine);
                    }
                    return null;
                }
            }
    
            public void ExecuteNonQuery(string database, string query)
            {
                try
                {
                    using (_connection = new SqlCeConnection { ConnectionString = @"Data Source= Mobile Device\My Documents\" + database + ";" })
                    {
                        var command = new SqlCeCommand(query, _connection);
                        command.Connection.Open();
                        command.ExecuteNonQuery();
                        command.Connection.Close();
                        return;
                    }
                }
                catch (Exception ee)
                {
                    using (StreamWriter sw = new StreamWriter("LOG.TXT", true))
                    {
                        sw.WriteLine("New Error @ " + DateTime.Now);
                        sw.WriteLine(ee.Message);
                        sw.WriteLine(Environment.NewLine);
                    }
                    return;
                }
            }
    
            public void UpdateDatabaseFromDataset(DataSet dataset,string database,string tablename, string query)
            {
                using (_connection = new SqlCeConnection { ConnectionString = @"Data Source= Mobile Device\My Documents\" + database + ";" })
                {
                    _connection.Open();
                    var sql = query;
                    _sqlDataAdapter = new SqlCeDataAdapter(sql, _connection);
                    var builder = new SqlCeCommandBuilder(_sqlDataAdapter);
                    builder.GetUpdateCommand();
                    _sqlDataAdapter.Update(dataset, tablename);
                    _connection.Close();
                }
                
            }
        }
    
    }

    It assumes that your database is located in the folder (DEVICE ROOT)\My Documents

    The database string is the filename of the database, i.e. mydata.sdf

    Used like so:

    // Load Data
    var data = new Classes.DataSource();
    dataset = data.ExecuteQuery("mydata.sdf", "Select * from myTable","myTable");
    dataGridView1.DataSource = dataset.Tables[0].DefaultView;
    
    
    // Execute a None query
    var data = new Classes.DataSource();
    data.ExecuteNoneQuery("mydata.sdf", "Delete From myTable Where ID='someID';","myTable");
    
    
    // Update database changes from dataset changes
     var data = new Classes.DataSource();
    data.UpdateDatabaseFromDataset(dataset, "mydata.sdf", "selected table", "Select * from " + selectedtable);

    Ignore the fact that it comments half of it in this preview here.

    Here you have  query to dataset, none query and update table changes based on dataset solutions.

    The initial load as it instances the database file takes a couple of seconds but after that - its "ok" speed wise



    • Edited by Prom3theu5 Wednesday, July 4, 2012 3:27 PM
    Wednesday, July 4, 2012 3:05 PM