Creating CRUD operations using the SqlNetFramework and CodeSmith

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.

1. Open the CodeSmith Studio.
2. Open the CRUD template. It is located at [SqlNetFrameworkInstallDir]\SqlNetFramework 1.0\CodeSmith\Templates\CRUD.cst.
3. The CRUD template properties are displayed in the properties window. Three properties are mandatory and the rest are optional.
   
4. 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.
   
5. DbFactoryType property. You need to specify the database factory that will be used to create the SQL statements.
   
6. SqlDataStore property. You need to specify the SQL repository where the SQL code will be stored.
   
7. 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.
   
8. The InsertFields, SelectFields and UpdateFields are used to specify which fields will be used in the Insert, Select and Update SQL statement, respectively.
   
9. 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.

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.