Introduction
The SqlNetFramework helps you to reduce the quantity of code that you need to connect and to execute operations in a relational database. In the following article we will show you how you can reduce the development time of your SQL statements. To automate the creation of the SQL code we will use the CodeSmith tool. We will focus in the automation of the Create, Read, Update and Delete operations used to maintain a database table.
Installing software
Before you can use the new tool to create the CRUD operations you need to install the required software.
Install the SqlNetFramework. Download it from www.sqlnetframework.com
Install the CodeSmith code generation tool. Download it from www.codesmithtools.com.
Register the SqlNetFramework add-in for CodeSmith. Copy the [SqlNetFrameworkInstallDir]\SqlNetFramework 1.0\CodeSmith\AddIn\SqlNetFrameworkCSAddIn.dll and SqlNetFrameworkCodeHelper.dll files to the [CodeSmithInstallDir]\currentVersion\AddIns directory. Repeat the same for the file located at [SqlNetFrameworkInstallDir]\SqlNetFramework 1.0\bin\SqlNetFramework.Core.dll and SqlNetFramework.Shared.dll.
Examining CRUD template
Now you are ready to examine the CRUD template. It is assumed that you already have a basic understanding about how CodeSmith works. CodeSmith has very easy tutorials for beginners, they are included in the CodeSmith installation.
- Open the CodeSmith Studio.
- Open the CRUD template. It is located at [SqlNetFrameworkInstallDir]\SqlNetFramework 1.0\CodeSmith\Templates\CRUD.cst.
- The CRUD template properties are displayed in the properties window. Three properties are mandatory and the rest are optional.
- Table property. Your need to specify the database table for which the CRUD operations will be created. Use the "Table Picker" editor included with CodeSmith to choose the database table.
- DbFactoryType property. You need to specify the database factory that will be used to create the SQL statements.
- SqlDataStore property. You need to specify the SQL repository where the SQL code will be stored.
- The CreateDelete, CreateInsert, CreateSelect and CreateUpdate properties are used to specify which SQL statements will be created. By default all the SQL statements are created.
- The InsertFields, SelectFields and UpdateFields are used to specify which fields will be used in the Insert, Select and Update SQL statement, respectively.
- The DeleteStatementId, InsertStatementId, SelectStatementId and UpdateStatementId properties are used to assign the identifier in the SQL data store. Their values are assigned automatically the first time that the template is executed.
Generating SQL code
Now you will use the CRUD template to generate SQL code automatically. You will use the DemoDb.mdb Access database deployed with the SqlNetFramework C# demo.
- Open CodeSmith Studio.
- Open the CRUD template.
- Select the Table property and then click the � button. The "Table Picker" editor will be displayed.
- Add a new data source. Click the ... button at the right of the data source dropdown list.
- The "Data Source Manager" dialog will be displayed. Click the Add button.
- The "Data Source" dialog is displayed. Assign the values as shown in the table below.
| Name |
AccessDemoDb |
| Provider type |
ADOXSchemaProvider |
| Connection string |
PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE='[YourDirectory]\DemoDb.mdb' |
- Click on Test to test the connection and then click OK.
- Select the AccessDemoDb data source and click on Close.
- The DemoDb database tables must be displayed in the "Table Picker" editor.
- Select the Customer database table. Click on Select.
- You will use the OleDb database factory to create the SQL statements. Assign OleDbFactory to the DbFactoryType property.
- You will use a XML repository to store the SQL code. Select the SqlDataStore property and then click the � button. The "SqlDataSource Manager" editor will be displayed.
- Click the Add� button to add a new SqlDataStore. The "Add/Edit SqlDataStore" dialog is displayed. Assign the values as shown in the table below.
| Name |
DemoDbXml |
| Manager type |
XmlSqlDataStoreManager |
| Connection settings |
[SqlNetFrameworkInstallDir]\SqlNetFramework 1.0\Demos\SqlNetFrameworkEvaluationCS\App_Data\DemoDb.xml |
- Click Ok to close the "Add/Edit SqlDataStore" dialog.
- Select the DemoDbXml and click on "Select" button.
- Now you are ready to run the CRUD template. Click F5 to run it.
- As you can see the properties window has been refreshed. The InsertFields, SelectFields and UpdateFields properties contain new values. These values are inferred from the database table schema.
- The DeleteStatementId, InsertStatementId, SelectStatementId and UpdateStatementId properties contain the identifier assigned by the XML SqlDataStore.
Now you will use a DetailsView control and a SqlStoreDataSource control display data and execute operations in the Customer database table. I have created the webform into the SqlNetFramework C# demo included with the SqlNetFramework installation.
<SqlNetFramework:SqlStoreDataSource ID="dsCustomers" SelectStatementID="12" InsertStatementID="13"
UpdateStatementID="14" DeleteStatementID="15" ConnectionID="DemoDb" runat="server"
DataSourceMode="DataSet"></SqlNetFramework:SqlStoreDataSource>
<asp:DetailsView ID="dvCustomers" DataSourceID="dsCustomers" runat="server" AllowPaging="True"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" AutoGenerateInsertButton="True"
AutoGenerateRows="False" DataKeyNames="CustomerId">
<Fields>
<asp:BoundField DataField="CustomerId" HeaderText="CustomerId" InsertVisible="False"
ReadOnly="True" SortExpression="CustomerId" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
</Fields>
</asp:DetailsView>
Conclusion
As you can see the SqlNetFramework reduce the quantity of code that you need to program. With the use of the SqlNetFramework and CodeSmith you can automate the creation of SQL code.
