The ADOXCE object model allows the objects in a database (such as tables and columns) to be added, modified, and deleted. You can also use SQL statements to do the same, which can often be more convenient and quicker. These SQL statements are called "Data Definition Language," or DDL, statements. SQL statements that manipulate data are called "Data Manipulate Language," or DML, statements. The following DDL statements are supported:
Using CREATE TABLEThe CREATE TABLE statement allows a table to be created by giving a name for the new table and the list of columns (fields) and their data types. The allowable data types for Microsoft SQL Server for Windows CE are shown in Table 16.6. For example, the following SQL statement creates a new table called Orders with four fields: CustNum (integer), OrderNum (integer), Description (Unicode string up to 100 characters long), and DateAdded (DateTime). CREATE TABLE Orders (CustNum INT, OrderNum INT, Description NCHAR VARYING(100), DateAdded DATETIME);
The code in Listing 16.8 creates the table called Orders. The function ExecuteSQL is passed a connection pointer and executes the SQL code. Listing 16.8 The CREATE TABLE statementvoid ExecuteSQL(AdoNS::_ConnectionPtr& pConnection, _bstr_t& bstrSQL) { _variant_t varRowsAffected; pConnection->Execute(bstrSQL, &varRowsAffected, AdoNS::adCmdText); } void Listing16_8() { AdoNS::_ConnectionPtr pConnection; if(!GetConnection(pConnection)) return; _bstr_t bStrSQL(_T("CREATE TABLE Orders \ (CustNum INT, OrderNum INT, \ Description NCHAR VARYING(100), \ DateAdded DATETIME)")); ExecuteSQL(pConnection, bStrSQL); cout _T("Table created") endl; pConnection->Close(); } Using DROP TABLEListing 16.4 showed how to drop a table using ADOXCE and the tables connection. An alternative method is to use the DROP TABLE statement, which is passed the name of the table to drop. Listing 16.9 shows code to drop the "Orders" table. Listing 16.9 The DROP TABLE statementvoid Listing16_9() { AdoNS::_ConnectionPtr pConnection; if(!GetConnection(pConnection)) return; _bstr_t bStrSQL(_T("DROP TABLE Orders ")); ExecuteSQL(pConnection, bStrSQL); pConnection->Close(); } Using Identities and Primary KeysMany tables require a unique integer identifier for each record in the database. Chapter 4 showed how to do this for property databases using the registry. In SQL Server for Windows CE you can use the IDENTITY key word when creating a table to create an auto-increment field. The PRIMARY KEY modifier can be used to specify that a field is the uniquely identifying field within the table. Identifying the primary key in a table is important, since applications inspecting the database design can use this to optimize data access. For example, to create a new table called "OrderDetails" with an auto-incrementing field called OrderDetailNum, you can execute the following DDL code: CREATE TABLE OrderDetails (OrderDetailNum INT IDENTITY PRIMARY KEY, OrderNum INT, Product NCHAR VARYING(100), Quantity INT) In almost all cases an IDENTITY field will also be the primary key. Listing 16.10 shows how this DDL code can be executed through ADOCE. Listing 16.10 The CREATE TABLE with identity columnvoid Listing16_10() { AdoNS::_ConnectionPtr pConnection; if(!GetConnection(pConnection)) return; _bstr_t bStrSQL(_T("CREATE TABLE OrderDetails \ (OrderDetailNum INT IDENTITY PRIMARY KEY, \ OrderNum INT, \ Product NCHAR VARYING(100), \ Quantity INT)")); ExecuteSQL(pConnection, bStrSQL); cout _T("Table created") endl; pConnection->Close(); } IndexesIndexes are used to improve performance when accessing records in a table, when joining tables together (using primary and foreign keys), and for ensuring uniqueness. When the PRIMARY KEY modifier is used for a field, a unique index is used to ensure uniqueness. Other indexes should be added to the "Customers," "Orders," and "OrderDetails" tables to cater for the most frequent ways the data will be accessed. Figure 16.1 shows the relationships between these three tables, and this helps to identify where indexes should be placed. Figure 16.1. Relationships between Customers, Orders, and OrderDetailsThe following DDL code can be used to create a unique index on the "Orders" table for the CustNum field (a foreign key used in the relationship with the Customers database): CREATE UNIQUE INDEX OrdersInd1 ON Orders (CustNum) Listing 16.11 shows code to create a number of additional indexes on the three tables created in previous sections. Listing 16.11 The CREATE INDEX statementvoid Listing16_11() { AdoNS::_ConnectionPtr pConnection; if(!GetConnection(pConnection)) return; _bstr_t bStrSQL(_T("CREATE UNIQUE INDEX OrdersInd1 \ ON Orders (OrderNum)")); ExecuteSQL(pConnection, bStrSQL); bStrSQL = (_T("CREATE INDEX OrdersInd2 \ ON Orders (CustNum)")); ExecuteSQL(pConnection, bStrSQL); bStrSQL = (_T("CREATE UNIQUE INDEX Customers1 \ ON Customers (CustNum)")); ExecuteSQL(pConnection, bStrSQL); bStrSQL = (_T("CREATE UNIQUE INDEX Customers2 \ ON Customers (CustName)")); ExecuteSQL(pConnection, bStrSQL); cout _T("Indexes created") endl; pConnection->Close(); }
|