VS IDE Post-build Event: Copy Files

The following line (specified in the "Post-build event command line" under "Build Events" of the project property) will copies the dll generated in a Visual Studio project to a folder "Libs" in the solution directory:

xcopy $(TargetDir)*.dll "$(SolutionDir)Libs" /D /Y

Calculate Geo Distance in SQL query

The follow SQL query computes the geo distance between two points ([LAT_CENTER], [LON_CENTER]) and ([LAT1], [LON1]), where ([LAT1], [LON1]) is taken from a datatable zipcensus:

SELECT ACOS(COS(centerlatrad)*COS(latrad)*COS(centerlongrad - longrad) + SIN(centerlatrad)*SIN(latrad)) * radius as distcirc FROM (SELECT latitude-([LAT_CENTER]) as difflat, longitude - ([LON_CENTER]) as difflong, latitude*PI()/180 as latrad, [LAT_CENTER]*PI()/180 as centerlatrad, longitude*PI()/180 as longrad, [LON_CENTER]*PI()/180 as centerlongrad, 3949.9 as radius FROM zipcensus zc WHERE latitude = [LAT1] AND longitude = [LON1]) zc) zc 

The follow SQL query computes the top 10 nearby points to ([LAT_CENTER], [LON_CENTER])

SELECT TOP 10 zc.*, ACOS(COS(centerlatrad)*COS(latrad)*COS(centerlongrad - longrad) + SIN(centerlatrad)*SIN(latrad)) * radius as distcirc FROM (SELECT latitude-([LAT_CENTER]) as difflat, longitude - ([LON_CENTER]) as difflong, latitude*PI()/180 as latrad, [LAT_CENTER]*PI()/180 as centerlatrad, longitude*PI()/180 as longrad, [LON_CENTER]*PI()/180 as centerlongrad, 3949.9 as radius FROM zipcensus zc) zc) zc ORDER BY distcirc

The formula uses several trigonometric functions, so the innermost query converts all the latitudes and longitudes to radians. In addition, this method uses the radius of the earth, which is taken to be 3,949.9 miles, the unit of the distance returned (i.e. distcirc) is in miles

SQLCE query alternative for SQL Server SQL

In SQL Server, you can use convenient features such as COUNT(DISTINCT db_field) or YEAR(orderdate). However, SQL functions such as COUNT(DISTINCT ...) and YEAR are not available for SQL Server Compact Edition, therefore, one has to write alternatives to get them to work. below are the example.

SQLCE Alternative to COUNT(DISTINCT ..)

//In SQL Server, will not work in SQLCE 
string query_SQLServer="SELECT COUNT(DISTINCT householdid) FROM customer c JOIN order o ON c.customerid=o.customerid";

//In SQLCE, will work for both SQL Sever and SQLCE
string query_SQLCE="SELECT COUNT(*) FROM (SELECT DISTINCT householdid FROM customer c JOIN order o ON c.customerid=o.customerid) a";

SQLCE Alternative to YEAR()

//In SQL Server, will not work in SQLCE 
string query2_SQLServer="SELECT YEAR(orderdate) FROM customer c JOIN order o ON c.customerid=o.customerid";

//In SQLCE, will work for both SQL Sever and SQLCE
string query2_SQLCE="SELECT DATEPART(year, orderdate) FROM customer c JOIN order o ON c.customerid=o.customerid";

SQLCE Alternative to SQUARE()

//In SQL Server, will not work in SQLCE 
string query_SQLCE="SELECT SQUARE(amount) as amountsqr FROM orders";

//In SQLCE, will work for both SQL Sever and SQLCE
string query_SQLCE="SELECT POWER(amount, 2) as amountsqr FROM orders";

How to return the list of column names about a datatable from SQL Compact Edition Database file

Suppose you have a datatable called "calendar" inside your SQLCE database file, but you don't know about the column names of the database, the following code can be used to retrieve the column headers:

string query="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'calendar'";
string conn_string="[YOUR DB CONNECTION STRING]";
try
{
 using (SqlCeConnection conn = new SqlCeConnection(conn_string))
 {
  conn.Open();
  using (SqlCeCommand cmd = new SqlCeCommand(query, conn))
  {
   SqlCeDataReader reader=cmd.ExecuteReader();
   while (reader.Read())
   {
    int fc=reader.FieldCount;
    for (int i = 0; i < fc; ++i)
    {
     Console.WriteLine("DB Column Header: {0}", reader.GetValue(i));
    }
   }
  }
  conn.Close();
 }
}
catch (Exception ex)
{
 Console.WriteLine("Error executing: {0}", query);
 Console.WriteLine(ex.ToString());
}

One interesting observation is that if one has a column named "national" within the "calendar" table and tries to retrieve it using "SELECT national from calendar", the query will fail as it seems that "national" may be a keyword in the SQLCE (which i am not sure at this point).

Increase the SQL Compact Edition database file size

By default the size of the sqlce database file is only 2GB, which may not be enough for some application. the following shows the connection string which allows to increase the file size to 4GB.

using System.Data.SqlServerCe;
using System.Data;
using System.IO;

string db_file_path="[YOUR DB FILE LOCATION]"; 
string db_pass = "[YOUR DB PASSWORD]";
int db_size_in_MB = 4000; //The absolute maximum size of the database is 4gb.
string conn_string = string.Format("DataSource=\"{0}\";Password=\"{1}\";Max Database Size={2}", db_file_path, db_pass, db_size_in_MB);

if(!File.Exists(db_file_path))
{
 SqlCeEngine en = new SqlCeEngine(conn_string);
 en.CreateDatabase();
}

string query= @"CREATE TABLE [product](
         [PRODUCTID] [int] NULL,
         [PRODUCTNAME] [nvarchar](50) NULL,
         [PRODUCTGROUPCODE] [nvarchar](50) NULL,
         [PRODUCTGROUPNAME] [nvarchar](50) NULL,
         [INSTOCKFLAG] [nvarchar](50) NULL,
         [FULLPRICE] [money] NULL
        ) ";

try
{
 using (SqlCeConnection conn = new SqlCeConnection(conn_string))
 {
  conn.Open();
  using (SqlCeCommand cmd = new SqlCeCommand(query, conn))
  {
   cmd.ExecuteNonQuery();
  }
  conn.Close();
 }
}
catch (Exception ex)
{
 Console.WriteLine("Error executing: {0}", query);
 Console.WriteLine(ex.ToString());
}

C# to MS SQL Compact Edition Data Type Mapping

SQLCE DataType	C# DataType
numeric	decimal
money	decimal
smallint	Int16
int	Int32
bigint	Int64
float	float
double	double
nvarchar, nchar	string

Bulk Insert into MS SQL Compact Edition Database from CSV files using C#

In MS SQL Server, bulk insert into database from CSV files can be easily done by calling 'LOAD' command from SQL query. However, this command is not supported in MS SQL compact edition. By inserting line by line into sqlce database is obviously too slow for most application that consumes large amount of data. Below is a source code written in C# for sqlce which allows users to obtain performance similar to bulk insert in MS SQL Server

public void BulkInsertFromCSV(string file_path, string table_name, string connection_string, Dictionary<string, Type> data_types)
{
 string line;
 List<string> column_names = new List<string>();
 using (StreamReader reader = new StreamReader(file_path))
 {
  line = reader.ReadLine();
  string[] texts = line.Split('\t');
  foreach (string txt in texts)
  {
   column_names.Add(txt);
  }

   using (SqlCeConnection conn = new SqlCeConnection(connection_string))
   {
    SqlCeCommand cmd = new SqlCeCommand();
    SqlCeResultSet rs;
    SqlCeUpdatableRecord rec;
    conn.Open();
    cmd.Connection = conn;
    cmd.CommandText = table_name;
    cmd.CommandType = CommandType.TableDirect;

    rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
    

    while ((line = reader.ReadLine()) != null)
    {
     texts = line.Split('\t');

     rec = rs.CreateRecord();

     for (int j = 0; j < column_names.Count; ++j)
     {
      string columnName = column_names[j];
      
      int ordinal = rec.GetOrdinal(columnName);
      string param_value = "";
      if (j < texts.Length)
      {
       param_value = texts[j];
      }

      Type data_type=data_types[columnName];
      if (data_type == typeof(Int32))
      {
       Int32 value=0;
       int.TryParse(param_value, out value);
       rec.SetInt32(ordinal, value);
      }
      else if (data_type == typeof(Int64))
      {
       Int64 value = 0;
       Int64.TryParse(param_value, out value);
       rec.SetInt64(ordinal, value);
      }
      else if (data_type == typeof(Int16))
      {
       Int16 value = 0;
       Int16.TryParse(param_value, out value);
       rec.SetInt16(ordinal, value);
      }
      else if (data_type == typeof(string))
      {
       rec.SetString(ordinal, param_value);
      }
      else if (data_type == typeof(double))
      {
       double value=0;
       double.TryParse(param_value, out value);
       rec.SetDouble(ordinal, value);
      }
      else if (data_type == typeof(float))
      {
       float value = 0;
       float.TryParse(param_value, out value);
       rec.SetFloat(ordinal, value);
      }
      else if (data_type == typeof(DateTime))
      {
       DateTime value;
       if(DateTime.TryParse(param_value, out value))
           rec.SetDateTime(ordinal, value);
      }

      else if (data_type == typeof(decimal))
      {
         decimal value;
         decimal.TryParse(param_value, out value);
         rec.SetDecimal(ordinal, value);
      }
     }

     rs.Insert(rec);
    }

    rs.Close();
    rs.Dispose();
    cmd.Dispose();
   
  }
 }
}

As can seen in the code above, the parameters for the method include:

file_path: full path to the CSV file, which is assumed to be tab-separated and has a header column which has the same names as the database column names
table_name: name of the database table into which the CSV file data will be populated
connection_string: the database connection string
data_types: a data type holder of Dictionary<string, Type>, for which the key specifies the name of the datatable column while the type specifies the type of data held by the column

The usage of the method is very simple, support the datatable schema looks like the following:

CREATE TABLE [product](
 [PRODUCTID] [int] NULL,
 [PRODUCTNAME] [nvarchar](50) NULL,
 [PRODUCTGROUPCODE] [nvarchar](50) NULL,
 [PRODUCTGROUPNAME] [nvarchar](50) NULL,
 [INSTOCKFLAG] [nvarchar](50) NULL,
 [FULLPRICE] [money] NULL
) 

And the CSV file C:\temp.csv looks like the following

PRODUCTID PRODUCTNAME PRODUCTGROUPCODE PRODUCTGROUPNAME INSTOCKFLAG FULLPRICE
10001  CA CALENDAR N 15
10002  CA CALENDAR N 10
10003  CA CALENDAR N 10
10004  BK BOOK Y 90
10005  BK BOOK Y 16
10006  BK BOOK N 10
10007  BK BOOK N 7
10008  BK BOOK N 11

The invoking of the method will be something like:

string connection_string="[YOUR DB CONNECTION STRING]";
Dictionary<string, Type> data_types=new Dictionary<string, Type>();
data_types["PRODUCTID"]=typeof(Int32);
data_types["PRODUCTNAME"]=typeof(string);
data_types["PRODUCTGROUPCODE"]=typeof(string);
data_types["INSTOCKFLAG"]=typeof(string);
data_types["FULLPRICE"]=typeof(float);
BulkInsertFromCSV(@"C:\temp.csv", "product", connection_string, data_types);

One more thing, i found that there is a bug that prevents insertion of float value to the datatable. In my implementation, the .net complains that "cannot insert Single to column (xxx). The column type is Float". Although i already specify using "SetFloat" but the errors simply do not go away, therefore, currently I am using "SetValue(ordinal, value)" in place of "SetFloat(ordinal, value)" which kind of solving my problem.