Use of SQL CE in. NET Compact Framework

SQL ce is the most commonly used database in Windows Mobile and Windows. SQL ce is also called SQL Server for Windows CE and SQL Server Mobile Edition. The latest version is named SQL Server Compact 3.5 SP1. SQL Server Compact can not only run on Wince and Windows Mobile, but also on Windows PC. It is an effective alternative to Access. If you do not use stored procedures, programs developed under SQL Server Compact can be transplanted to other SQL Server versions without modification. See this article for SQL Server Express and SQL Server Compact applications. In this article, I will use the SQL CE name.

In. NET Compact Framework for SQL CE use and development, requires the application library System. data. sqlServerCe. dll, note that different SQL CE versions use unused DLL versions. the SQL ce 3.5 library generally corresponds to the following directory C: \ Program Files \ Microsoft SQL Server Compact Edition \ v3.5 ADO.net DLL, while the SQL CE 3.0 library generally corresponds to the following directory C: \ Program Files \ Microsoft Visual Studio 8 \ SmartDevices \ SDK \ SQL Server \ Mobile, incompatible with each other. Because the developed namespaces are consistent, the developed programs can be used in unused SQL CE versions.

 

Helper class

The following demonstrates a helper class of SQL ce. This class is only for SQL ce databases and is not considered to be transplanted to other databases. Therefore, all classes use SQL CE-related classes.

 

Class SqlCeHelper: IDisposable
{
Private SqlCeConnection connection;
Private SqlCeCommand command;
Private const string connectionString = "Data Source =/DB/db. sdf ";

# Region Open/Close
Public void Open ()
{
Try
{
Connection = new SqlCeConnection (connectionString );
Command = connection. CreateCommand ();
Command. Connection = connection;
Command. CommandType = CommandType. Text;

Connection. Open ();
}
Catch (DataException e)
{
Console. WriteLine (e. Message );
}
}

Public void Close ()
{
Connection. Close ();
Connection. Dispose ();
}

Public void Dispose ()
{
Connection. Close ();
Connection. Dispose ();
Command. Dispose ();
}
# Endregion

# Region Operatons
Public SqlCeDataReader ExecuteReader (string SQL)
{
Command. CommandText = SQL;
SqlCeDataReader reader = null;
Try
{
Reader = command. ExecuteReader ();
}
Catch (DataException e)
{
Console. WriteLine (e. Message );
}
Return reader;
}

Public DataSet ExecuteDataSet (string SQL)
{
Command. CommandText = SQL;
SqlCeDataAdapter adapter = new SqlCeDataAdapter (command );
DataSet ds = new DataSet ();;

Try
{
Adapter. Fill (ds );
}
Catch (DataException e)
{
Console. WriteLine (e. Message );
}
Return ds;
}

Public int ExecuteNonQuery (string SQL)
{
Command. CommandText = SQL;
Int result =-1;

Try
{
Result = command. ExecuteNonQuery ();
}
Catch (DataException e)
{
Console. WriteLine (e. Message );

}
Return result;
}

Public object ExecuteScalar (string SQL)
{
Command. CommandText = SQL;
Object o = null;
Try
{
O = command. ExecuteScalar ();
}
Catch (DataException e)
{
Console. WriteLine (e. Message );
}
Return o;
}
# Endregion

# Region Transaction
Public void BeginTransaction ()
{
Command. Transaction = connection. BeginTransaction ();
}

Public void CommitTransaction ()
{
Command. Transaction. Commit ();
}

Public void RollbackTransaction ()
{
Command. Transaction. Rollback ();
}
# Endregion
}

1. to establish an SQL ce connection, you only need to specify the database file path. In this class, I hardcode the file path. In actual applications, You can initialize the connection in the constructor, or pass the connection string parameter in the Open function.
2. Because SQL ce does not support stored procedures in the current version, the CommandType of SqlCeCommand is specified as CommandType. Text, and only SQL statements can be executed.
3. implemented the general operation methods ExecuteReader, ExecuteDataSet, ExecuteNonQuery and ExecuteScalar.
4. SQL CE supports transactions ).

Use

Is the table structure of the example.

SqlCeHelper sqlCe = new SqlCeHelper ();

SqlCe. Open ();

SqlCe. BeginTransaction ();
If (sqlCe. ExecuteNonQuery ("delete from t") <0)
{
SqlCe. RollbackTransaction ();
Return;
}

If (sqlCe. ExecuteNonQuery ("insert into t (f1, f2) values (1, 'abc')") <0)
{
SqlCe. RollbackTransaction ();
Return;
}

SqlCeDataReader reader = sqlCe. ExecuteReader ("select * from t where f1 = 1 ");
While (reader. Read ())
{
Console. WriteLine ("reader: f2: {0} \ n", reader ["f2"]);
}
If (! Reader. IsClosed)
{
Reader. Close ();
}

If (sqlCe. ExecuteNonQuery ("update t set f2 = 'xyz'") <0)
{
SqlCe. RollbackTransaction ();
Return;
}

DataSet ds = sqlCe. ExecuteDataSet ("select * from t ");
Foreach (DataRow dr in ds. Tables [0]. Rows)
{
Console. WriteLine ("dataset: f2: {0} \ n", dr ["f2"]);
}

SqlCe. CommitTransaction ();
SqlCe. Close ();

It is very easy to use this Helper class. You can create an instance of this class, open the database connection, use ExecuteReader and ExecuteDataSet for query operations, and use ExecuteNonQuery for addition, deletion, and modification operations. During the operation, you can also add transaction processing operations to close the database connection after use.

References

System. Data. SqlServerCe Namespace