Gergely Kovásznai, Csaba Biró
Eszterházy Károly College
In the previous section, we introduced how to perform queries on data represented in XML. A demand arises for doing the same on SQL databases, either local or remote ones. In contrast to XML files, SQL databases offer a more sophisticated, robust and trusted alternative. State-of-the-art database management systems, such as MS-SQL, Oracle Database, PostgreSQL, or MySQL, provide numerous services, e.g., maintaining large amount of data in an effective, standardized and optimized way, and supporting transaction processing. From our perspective, it is indeed very useful to create and maintain databases easily (by using the appropriate GUI tools), and to access databases, to perform queries, and to manipulate (insert, delete, or modify) data in C# easily. In this section, we only give a practical introduction to this deep topic, and hope that developers, after picking up those introductory tricks, will start their journey along this direction, which offers lots of novelties and beneficial knowledge. For this sake, we are about to show a rapid development process by using modern .NET technologies such as MS-SQL and LINQ to Entities.
Data in SQL databases are stored in tables, tables consist of columns, each column has some kind of type (e.g., integer, string, date etc.), and, furthermore, certain (groups of) columns may have special roles (e.g., primary key, foreign key), which can be used to realize connections between certain tables. Imagine, for instance, a database in which we store data about chocolats, by using columns such as an identifier (integer), a name (string), and (the identifier of) a manufacturer (integer)! The latter column is a foreign key refers to another table in which manufacturers are stored, by using appropriate columns. For the sake of example, we are going to create the database by using Visual Studio’s (Section XVIII.1) corresponding tool. First, add a so-called local database to your project; this database is actually an MS-SQL CE database!
MS-SQL has a slimmed-down version for desktop and mobile applications, called MS-SQL Compact Edition (CE). The point is that the MS-SQL CE engine gets embedded directly in our application. Visual Studio supports MS-SQL CE by default, since it is essential to provide some basic developing tool for creating and managing simple, local databases.
When adding a new item, choose „Local Database” in the „Data” category, as can be seen in Figure 1. Do not forget to give a name to the file that contains the database. As can be seen, the file format for MS-SQL CE is SDF.
Next, create tables in your (empty) database! For this, it is worth to use a built-in tool in Visual Studio, the Server Explorer. As can be seen in Figures 2 and 3, one has to click on Server Explorer’s „Data Connections” item, and then to select „Add Connection”. Notice in the window pops up that the tool expects an MS-SQL CE database by default as „Data Source”. Click the „Browse” button in order to browse for your SDF file.
How to create a table in our database? In Server Explorer, right click on the „Tables” item of our database, and then select the „Create Table” menu item. After naming the table (Chocolat), fill out the form for columns. First, add a numerical primary key (Id) to your table, as can be seen in Figure 4. Pay attention to set the type („Data Type”) of the key to integer (int), to make it primary key („Primary Key”), and to declare the column as an identifier („Identity”).
Add more columns as seen in Figure 5: let the Name column be string (30-character-long nvarchar), ManufacturerId a foreign key (int), and both filled compulsorily („Allows Nulls”).
When done, click the „OK” button. In a similar manner, create a „Manufacturer” table as well, as can be seen in Figure 6.
Notice that, even though the Chocolat table has a ManufacturerId column, we have not yet declared the way for connecting the two tables (i.e., which columns to use). This can be done by adding a so-called „Relation” to the table that references another table: right click on the Chocolat table, select „Table Properties”, and then click on „Add Relation”. The subsequent steps are shown in Figure 7. First, one need to name the relation (let it be called Manufacturer this time). Next, select the table to be referenced („Primary Key Table”) and its primary key („Primary Key Table Column”). Then, in the table that references („Foreign Key Table”) the other, select the column in which the reference itself will be stored („Foreign Key Table Column”). Finally, click the „Add Columns” button and then „OK”.
For the sake of example, let us create a Material table as well, in which ingredients for chocolats (e.g. cacao mass, cacao buttes, sugar) will be stored. Note that we are about to realize many-to-many relationship between the Chocolat and Material tables! That is, we need to create a junction table (ChocolatMaterial) as well, which contains two „Relations”: one to the Chocolat table and one to the Material table.
 The maximum file size for SDF is 4 GB.
 If you do not see Server Explorer on the interface of Visual Studio (usually on the left), then you can open it on the „View” menu.
 As can be seen in Figure 4, it is practical to leave „Identity Increment” equal to 1. By doing so, one instructs the system to increment the value of the identifier by 1 whenever inserting a new record.