Creating CRUD operations using the SqlNetFramework and CodeSmith

Luis Ramirez

Rate me:

2.60/5 (2 votes)

20 Apr 20074 min read

31.3K

This article shows you how to generate SQL code for CRUD operations using the SqlNetFramework and CodeSmith.

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.

Name	AccessDemoDb
Provider type	ADOXSchemaProvider
Connection string	PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE='[YourDirectory]\DemoDb.mdb'

Name	DemoDbXml
Manager type	XmlSqlDataStoreManager
Connection settings	[SqlNetFrameworkInstallDir]\SqlNetFramework 1.0\Demos\SqlNetFrameworkEvaluationCS\App_Data\DemoDb.xml

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

Luis Ramirez

Software Developer (Senior) www.sqlnetframework.com

Mexico

Luis Ramirez is creator and owner of ADO.NET Accelerator. You can use the FREE ADO.NET Accelerator version to reduce more than 50% ADO.NET code from your data access layer. Luis Ramirez is a Microsoft Certified Professional specialized in .NET development. If you want to contact him to work in your projects or for any other inquiry that you have write him to lramirez [at] sqlnetframework [dot] com.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.