Introduction to the SqlNetFramework

Author: Luis Ramirez.
June 28, 2007.

Introduction

The SqlNetFramework is a software development tool that helps you to develop .NET data applications faster and easier. The SqlNetFramework has data access code that helps you to reduce the quantity of custom code that usually you need to create, test and maintain. With a single line of code you can create a dataset or execute a SQL statement.

Background

Most of the software applications require to store information in some kind of data repository. The most common data repository is a database. To connect and execute data operations in a database we need to create custom code. This code may became repetitive and costly to test and maintain. For this reason have been created development software tools that create code automatically. It is real that it saves you time but in long term it can became costly to maintain all the generated code. If your database changes then you need to reflect those changes in the generated code. Another alternative is to find the most common patterns used by developers when creating data applications and to put all this data access code in a library. It will reduce the quantity of code that you need to create. The maintaining in long term is less expensive because most of the data access code is located in the data access library. Code automation is good because it saves you time but we need to choose when to use it and when to create components/library that contains known patterns and reduce programming complexity.

.NET data applications development tools

To develop a data application for the .NET platform you have different options:

  • Develop it from scratch.
  • Use Microsoft data access objects for .NET 2.0.
  • Object relational mapping products (ORM).
  • Code generation tools.

You may decide to create your .NET data applications because none off-the-shelf software available on the market feet your needs or because you may want to have full control of your application code. It may take you more time and effort to develop your application but you won’t depend of any third party.

Microsoft has incorporated new data access components for .NET 2.0. They allow you to connect and execute data operations easier. The SqlDataSource control is an example of a easy to use data access component. One disadvantage with the SqlDataSource control is that is embeds the SQL code within the presentation layer. It doesn’t allows you to reuse your SQL code.

The object relational mapping development tools create objects based on your database schema. It allows you to have an object-oriented development.

The code generation development tools create code for you. Some tools create the data access layers to select, insert, update and delete data. It saves you time but in long term it can be expensive to maintain if there are changes in your backend (database schema changes, database server changes).

As you can see there are different options available to develop your .NET data application. The option that you choose depends on your needs.

SqlNetFramework development tool

What is the SqlNetFramework?

The SqlNetFramework is a software development tool that helps you to develop .NET data applications faster and easier.

How the SqlNetFramework helps me to develop .NET data applications faster?

The SqlNetFramework implements the most common data access code used by developers. You don’t need to create, test and maintain custom code. Most of the custom code that you usually write is already implemented by the SqlNetFramework.

//The 0 makes reference to the Select SQL statement identifier in the SQL data store.
//YourConnectionId makes references to the connection string and other settings.
gridView1.DataSource = DbManager.Instance.ExecuteReader(0, "YourConnectionId");
gridView1.DataBind();
You can use the SqlNetFramework and CodeSmith to create SQL code automatically for CRUD operations. Read more...

How the SqlNetFramework helps me to develop .NET data applications easier?

The SqlNetFramework is very easy to learn. To execute data operations you only need to use the DbManager class. It has the following methods: CreateDataSet, CreateDbDataAdapter, CreateTransaction, ExecuteNonQuery, ExecuteReader and ExecuteScalar. 

ListDictionary parameterNameValueList = new ListDictionary();

//Assigning the parameter values that will be used to execute the SQL statement.
parameterNameValueList.Add("CustomerId", 2007);

//Executing a delete SQL statement. 
//The 1 makes reference to a delete SQL statement in the SQL data store. 
DbManager.Instance.ExecuteNonQuery(1, "YourConnectionId", parameterNameValueList);

If you are developing a web site application you can use the SqlStoreDataSource control. It is as easy to use as the SqlDataSource control. The SqlStoreDataSource control doesn’t embeds the SQL code within your aspx file. 

<asp:GridView ID="GridView1" runat="server" DataSourceID="dsCustomers">
</asp:GridView>

<SqlNetFramework:SqlStoreDataSource ID="dsCustomers" 
  ConnectionID="YourConnectionId" runat="server" SelectStatementID="0">
</SqlNetFramework:SqlStoreDataSource>

You need to spend just a few time to learn to use the SqlNetFramework.

Where the SqlNetFramework stores the SQL code?

The SqlNetFramework uses an independent storage medium to store the SQL code. The SqlNetFramework stores the SQL code in a SQL data store. It is a repository of SQL statements. You can use a XML file, a Microsoft SQL Server database, an Oracle database or any custom repository to store your SQL code. It allows you to reuse your SQL code among multiple applications. 

<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
  <SqlStatement>    
    <SqlStatementId>0</SqlStatementId>
    <SqlStatementName>Customers</SqlStatementName>
    <Description />
    <CommandText><![CDATA[SELECT * FROM CUSTOMER]]></CommandText>
    <SqlStatementType>Text</SqlStatementType>
    <LastModification>2007-01-22T00:00:00-06:00</LastModification>
  </SqlStatement>
  <SqlStatement>
    <SqlStatementId>1</SqlStatementId>
    <SqlStatementName>Delete orders</SqlStatementName>
    <Description />
    <CommandText><![CDATA[DELETE FROM [ORDER] WHERE CustomerId = ?]]></CommandText>
    <SqlStatementType>Text</SqlStatementType>
    <LastModification>2007-01-22T00:00:00-06:00</LastModification>
  </SqlStatement>
  <SqlParameter>
    <SqlParameterId>14</SqlParameterId>
    <SqlStatementId>1</SqlStatementId>
    <SqlParameterName>CustomerId</SqlParameterName>
  </SqlParameter>
  <SqlParameterProperty>
    <PropertyName>OleDbType</PropertyName>
    <PropertyValue>Integer</PropertyValue>
    <SqlParameterID>14</SqlParameterID>
  </SqlParameterProperty>
</XmlSqlDataStore>

The SqlNetFramework code is independent from my database data provider?

Yes. The SqlNetFramework doesn’t have any reference to an specific database data provider. The SqlNetFramework uses the interfaces defined in the .NET framework. If you decide to change from database vendor your code will have minor changes. 

ListDictionary parameterNameValueList = new ListDictionary();

//Assigning the parameter values that will be used to execute the SQL statement.
parameterNameValueList.Add("CustomerId", 2007);

//Executing a delete SQL statement. 
//The 1 makes reference to a delete SQL statement in the SQL data store. 
DbManager.Instance.ExecuteNonQuery(1, "YourConnectionId", parameterNameValueList);

As you can see the SqlNetFramework code doesn't have any reference to an specific data provider (SqlClient, OleDb, etc). The reference to the database data provider is done in the web.config file not in your code. It helps you to write code independent from the database data provider. The database factory specified in the web.config is used to execute your SQL statements.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connections>
   <connection
    id="YourConnectionId" 
    connectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE='C:\Neptuno.mdb';" 
    dbFactoryId="OleDbFactory" 
    sqlDataStoreId="NeptunoXmlDataStore"/>
  </connections>
  <sqlDataStores>
    <sqlDataStore 
        id="NeptunoXmlDataStore" 
        sqlDataStoreManager="XmlSqlDataStoreManager" 
        connectionSettings="File=C:\NeptunoQuerys.xml|ResourceLocation=File" 
        sqlDataCacheMode="All"/>
  </sqlDataStores>
</configuration>

Can I extend the SqlNetFramework to feet my needs?

Yes. You can implement your own database factory for a custom data provider (Sybase, MySql, etc). You also can implement your own SQL data store. You can store your SQL statements in a custom repository (Sybase, MySql, etc)

Why to choose the SqlNetFramework?

The SqlNetFramework contains data access code that helps you to reduce the quantity of code that you need to write. There are other tools that generates a lot of code automatically but then you need to maintain all this code.

The SqlNetFramework is very easy to use. You only need to learn how to use the DbManager and the SqlStoreDataSource control.

The SqlNetFramework code is independent from your database data provider.

Your SQL code is stored in an independent storage medium. It allows you to reuse your SQL code in multiple applications. It also allows you to separate your data access layer from your presentation and bussiness layer.