| Walkthrough: Use the SQL Server ce Database This section describes the main tasks required to use the Microsoft SQL Server 2000 Windows CE edition (SQL Server CE) database. If you do not have any SQL Server ce database, you must create a new database in the new Microsoft Visual Studio. NET project. This drill includes the following tasks:
- Create a new SQL Server ce Database
- Read SQL Server ce database data
- Change Data in the SQL Server ce Database
The code for each of the preceding task steps is intended to run together. You cannot run code only for specific steps in a task. For the complete code list, see code list: using the SQL Server ce database. Create a new databaseThe database in SQL Server CE is a set of tables that store structured data. Before you can store a database, you must create a database. After creating a database, you can create a table that saves data. In this section, you will create a new database, create a table, and add data to the table. To perform these steps, use SQL Server Ce (System. Data. sqlserverce). Net Framework Lite version of the data providerSqlceconnection,SqlceengineAndSqlcecommandClass.
Note:You cannot run code only for specific steps in this task. For the complete code list, see code list: using the SQL Server ce database.
Create a new SQL Server ce Database
- Start Visual Studio. NET and open a new project.
- Create a reference to the namespace used.
using System; using System.IO; using System.Text; using System.Data; using System.Data.SqlServerCe; using System.Collections; using System.Windows.Forms; using System.Data.Common;
- CreateWalkthroughClass.
public class WalkThrough { static void Main() { SqlCeConnection conn = null; try {
- Verify that the database with the name you intend to use already exists.
if (File.Exists (Test.sdf) ) File.Delete (Test.sdf);
- UseSystem. Data. sqlceengineObject To create an empty database named test. SDF.
Note:In SQL Server ce, the file extension of the database name is. SDF.
SqlCeEngine engine = new SqlCeEngine (Data Source = Test.sdf); engine.CreateDatabase ();
- Connect to the new database.
conn = new SqlCeConnection (Data Source = Test.sdf); conn.Open();
Create a new table
- UseSystem. Data. sqlcecommandCreate an instance of the command class.
SqlCeCommand cmd = conn.CreateCommand();
- Run commandsCMDCreate a table. The command used to create a table must be inCmd. commandtextIncluded SQL code.
To create a table using the SQL programming language of SQL Server ce, use the CREATE TABLE syntax. For more information, see SQL reference for SQL Server ce in SQL Server ce online manual. cmd.CommandText = CREATE TABLE TestTbl(col1 int PRIMARY KEY, col2 ntext, col3 money); cmd.ExecuteNonQuery();
Add data to a new table
- Run the command to add data rows. The Command Used To Add rows must also be inCmd. commandtextIncluded SQL code.
To Add rows to a table using the SQL programming language of SQL Server ce, use the insert syntax. For more information, see SQL reference for SQL Server ce in SQL Server ce online manual. cmd.CommandText = INSERT INTO TestTbl(col1, col2, col3) VALUES (0, 'abc', 15.66); cmd.ExecuteNonQuery();
- Create a command to insert data into a table multiple times using parameters.
Parameters can be used to query databases more efficiently, because you can use a set of SQL statements containing parameters to insert multiple values. For more information, see SQL Server ce online manual to use parameters in queries. cmd.CommandText = INSERT INTO TestTbl(col1, col2, col3) VALUES (?, ?, ?); cmd.Parameters.Add(new SqlCeParameter(p1, SqlDbType.Int)); cmd.Parameters.Add(new SqlCeParameter(p2, SqlDbType.NText)); cmd.Parameters.Add(new SqlCeParameter(p3, SqlDbType.Money)); cmd.Parameters[p2].Size = 50; cmd.Prepare();
- Run the parameterized command to insert data into the table.
cmd.Parameters[p1].Value = 1; cmd.Parameters[p2].Value = abc; cmd.Parameters[p3].Value = 15.66; cmd.ExecuteNonQuery();
- Clear parameters and check the data in the inserted Table.
To use the SQL programming language of SQL Server ce to read existing data, use the select syntax. For more information, see SQL reference for SQL Server ce in SQL Server ce online manual. cmd.Parameters.Clear(); //Set the command text to a SELECT query. // cmd.CommandText = SELECT * FROM TestTbl;
Read SQL Server ce database dataReading database data is a common task that usually involves accessing table row information. For useSystem. Data. sqlserverceTo execute this task, you needSqlcedatareaderAndSqlcecommandObject.
Note:For the complete code list, see code list: using the SQL Server ce database.
- CallSqlcecommandObjectExecutereaderMethod to createSqlcedatareader.
SqlCeDataReader rdr = cmd.ExecuteReader();
- Indicates the data reader.RDRWhen a row exists, data is displayed in the columns of each row.
while (rdr.Read()) {MessageBox.Show(col1 = + rdr.GetInt32(0) + col2 = + rdr.GetString(1) + col3 = + rdr.GetSqlMoney(2)); }
Change Data in the SQL Server ce DatabaseAfter creating a table, you can modify the data in the table in multiple ways: Change the attributes of specific data, add and delete data rows, you can even change the way data is stored by modifying the columns in the table. In this section, you will change the table item value, find the changed table data, and handle all the errors. To perform these steps, you will use the class used in the previous task:SqlcecommandAndSqlcedatareader. In addition, you will useSqlceexceptionHandle errors.
Note:You cannot run code only for specific steps in this task. For the complete code list, see code list: using the SQL Server ce database.
Update Data in the SQL Server ce table
- Set the command object to use the update statement.
To use the SQL programming language of SQL Server ce to change the value of a row or column, use the update syntax. For more information, see SQL reference for SQL Server ce in SQL Server ce online manual. cmd.CommandText = UPDATE TestTbl SET col2 = 'some new value' WHERE col1 = 0; cmd.ExecuteNonQuery();
Read data from the SQL Server ce table
- Set the command object to use the SELECT statement, and then runSqlcecommand. executereaderCreate an instance of the data reader.
cmd.CommandText = SELECT * FROM TestTbl; rdr = cmd.ExecuteReader(); while (rdr.Read()) { MessageBox.Show( col1 = + rdr.GetInt32(0) + col2 = + rdr.GetString(1) + col3 = + rdr.GetSqlMoney(2)); } }
- UseSqlceexceptionCapture any errors and close the connection to the database.
catch (SqlCeException e) { ShowErrors(e); } finally { if(conn.State == ConnectionState.Open) conn.Close(); } } public static void ShowErrors(SqlCeException e) { SqlCeErrorCollection errorCollection = e.Errors; StringBuilder bld = new StringBuilder(); foreach (SqlCeError err in errorCollection) { bld.Append(\n Error Code: + err.HResult.ToString(X)); bld.Append(\n Message : + err.Message); bld.Append(\n Minor Err.: + err.NativeError); bld.Append(\n Source : + err.Source); foreach (int numPar in err.NumericErrorParameters) { if (0 != numPar) bld.Append(\n Num. Par. : + numPar); } foreach (string errPar in err.ErrorParameters) { if (String.Empty != errPar) bld.Append(\n Err. Par. : + errPar); } MessageBox.Show(bld.ToString()); bld.Remove(0, bld.Length); } } }
For more information about error handling in SQL Server ce, see SQL Server ce online manual. |
