SqlNetFramework Overview

The SqlNetFramework is a software development tool that reduces the lines of code that you need to create to develop a SQL database application for the Microsoft .NET Framework. With just a single line of .NET code you can create a dataset or execute a SQL query/stored procedure. You only need to create your SQL query/stored procedure and then call the SqlNetFramework API to execute your SQL statements. You won't need to create anymore common ADO.NET data access code for tasks such as: create a SQL connection, create a SQL command, close a SQL connection, etc.

Who needs the SqlNetFramework?

Developers building a SQL database application for the Microsoft .NET Framework.

The SqlNetFramework is simple and powerful

The SqlNetFramework is a very simple but powerful development tool. To start developing a SQL database applications for the Microsoft .NET Framework using the SqlNetFramework you only need to do 3 simple steps:

  • Configure your web.config/winApp.exe.config file with the SqlNetFramework settings.
    <?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="connections" type="SqlNetFramework.Configuration..."/>    
    <section name="sqlDataStores" type="SqlNetFramework.Configuration..."/>    
  </configSections>
  <connections>
   <connection
    id="Northwind" 
    connectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;
                      DATA SOURCE='C:\Northwind.mdb';" 
    dbFactoryId="OleDbFactory" 
    sqlDataStoreId="NorthwindXmlDataStore"/>
  </connections>
  <sqlDataStores>
    <sqlDataStore 
        id="NorthwindXmlDataStore" 
        sqlDataStoreManager="XmlSqlDataStoreManager" 
        connectionSettings="File=C:\NorthwindSQL.xml|ResourceLocation=File|
                            ConfigurationMode=Debug" 
        sqlDataCacheMode="All"/>
  </sqlDataStores>
</configuration>
  • Create your SQL statements using the SqlEditor. Click on the image to enlarge it.
    Editor used by the SqlNetFramework to create SQL statements.
  • Use the SqlNetFramework API to execute your SQL statements.
    [C#]
SqlNetFramework.CiOrderedDictionary parameterValues;
IDataReader reader;

parameterValues.Add("CustomerId", 342);
reader = DbManager.Instance.ExecuteReader(1,"Northwind", parameterValues);

[VB.NET]
Dim parameterValues As SqlNetFramework.CiOrderedDictionary
Dim reader As IDataReader

parameterValues.Add("CustomerId", 342)
reader = DbManager.Instance.ExecuteReader(1,"Northwind", parameterValues)

Write portable SQL code across multiple .NET applications

Would you like that your SQL code could be used in multiple .NET application types, such as: ASP.NET or Windows Forms? With the SqlNetFramework it is possible because the SqlNetFramework stores your SQL code in an independent storage medium. As your SQL code is isolated from your .NET application code you can take your SQL code and use it in multiple .NET application types. For example, you can use the same SQL code and use it in both: an ASP.NET application and a Windows Forms application.

The SqlNetFramework has built-in SQL data stores for the following mediums: XML, Microsoft SQL Server 2000, Microsoft SQL Server 2005 and Oracle 10g.

[C#]
//ASP.NET application code
System.Web.UI.WebControls.GridView gridView1;

gridView1.DataSource = DbManager.Instance.ExecuteReader(0, "Northwind");
gridView1.DataBind();


//Windows Forms application code
System.Windows.Forms.DataGridView dataGridView1;
System.Windows.Forms.BindingSource bindingSource1;

dataGridView1.DataSource = this.bindingSource1;
bindingSource1.DataSource = DbManager.Instance.ExecuteReader(0, "Northwind");

[VB.NET]
//ASP.NET application code
Dim gridView1 As System.Web.UI.WebControls.GridView

gridView1.DataSource = DbManager.Instance.ExecuteReader(0, "Northwind")
gridView1.DataBind()


//Windows Forms application code
Dim dataGridView1 As System.Windows.Forms.DataGridView
Dim bindingSource1 As System.Windows.Forms.BindingSource

dataGridView1.DataSource = bindingSource1
bindingSource1.DataSource = DbManager.Instance.ExecuteReader(0, "Northwind")

Write portable .NET code across multiple SQL database servers

It is common to find in the ADO.NET data access code references to a particular database vendor such as: System.Data.SqlClient or System.Data.OracleClient. Have you ever thought what would happen if you need to change from database vendor in the future? Maybe you may need to change from a Microsoft SQL Server to an Oracle database server, what will happen to your .NET code? You will need to update all references to your new database vendor. When developing SQL database applications for the Microsoft .NET Framework using the SqlNetFramework you won't need to add references to your database vendor or the references will be minimun. It will give you the advantage that the same .NET application code can run for different database vendor without changes or with minimun changes.

[C#]
SqlNetFramework.CiOrderedDictionary parameterValues = new CiOrderedDictionary();
IDataReader reader;

//No references to a parameter format such as: @CustomerId or :CustomerId.
parameterValues.Add("CustomerId", 342);

reader = DbManager.Instance.ExecuteReader(1, "Northwind", parameterValues);

string firstName = reader["FIRSTNAME"].ToString();
string lastName = reader["LASTNAME"].ToString();

reader.Close();

[VB.NET]
Dim parameterValues As New SqlNetFramework.CiOrderedDictionary()
Dim reader As IDataReader

'No references to a parameter format such as: @CustomerId or :CustomerId.
parameterValues.Add("CustomerId", 342)

reader = DbManager.Instance.ExecuteReader(1, "Northwind", parameterValues)

Dim firstName As String = reader("FIRSTNAME").ToString()
Dim lastName As String = reader("LASTNAME").ToString()

reader.Close();

[Microsoft SQL Server]
<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
  <SqlStatement>    
    <CommandText><![CDATA[SELECT * FROM CUSTOMER WHERE CUSTOMERID = @CUSTOMERID]]>
    </CommandText>
  </SqlStatement>
</XmlSqlDataStore>

[Oracle]
<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
  <SqlStatement>    
    <CommandText><![CDATA[SELECT * FROM CUSTOMER WHERE CUSTOMERID = :CUSTOMERID]]>
    </CommandText>
  </SqlStatement>
</XmlSqlDataStore>

[OleDb]
<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
  <SqlStatement>    
    <CommandText><![CDATA[SELECT * FROM CUSTOMER WHERE CUSTOMERID = ?]]>
    </CommandText>
  </SqlStatement>
</XmlSqlDataStore>

SqlStoreDataSource: No more SQL code in your ASP.NET ASPX files

Have you ever used the SqlDataSource control shipped with ASP.NET 2.0 to work with a SQL database server? The SqlDataSource control is a very cool control, easy to use and quick. But have you ever seen where is created the SQL code that the SqlDataSource control uses? It is created in your ASP.NET ASPX file. Now the question is, what happen if you would want to use the SQL code that already exists in your ASP.NET ASPX files in a new project, for example, a Windows Forms project? You can't re-use your existing SQL code. The only thing that you can do is to copy and paste your SQL code. A solution for this issue is to use the SqlStoreDataSource control that comes with the SqlNetFramework. The SQL code used by the SqlStoreDataSource control is stored in an independent SQL repository. It allows you to use your SQL code not only in your ASP.NET application but in multiple applications (e.g. Windows Forms).

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

<SqlNetFramework:SqlStoreDataSource ID="SqlStoreDataSource1" runat="server" 
        ConnectionID="Neptuno" DataSourceMode="DataSet" SelectStatementID="1">
</SqlNetFramework:SqlStoreDataSource>

Create your own database factories and SQL data stores

Do you want to use a SQL database server for which there is not a built-in database factory? Do you want to connect to a SQL database server using a particular data provider? Do you want to store your SQL code in a particular SQL database server or another storage? The SqlNetFramework allows you to implement your own database factories and SQL data stores. You only need to implement the IDbFactory and the ISqlDataStoreManager interfaces to a create your own database factory and SQL data store, respectively.

[C#]
public class MyDbFactory : SqlNetFramework.Data.IDbFactory
{
}

public class MySqlDataStoreManager : SqlNetFramework.Sql.ISqlDataStoreManager 
{
}    

[VB.NET]
Public Class MyDbFactory 
  Implements SqlNetFramework.Data.IDbFactory
End Class

Public Class MySqlDataStoreManager 
  Implements SqlNetFramework.Sql.ISqlDataStoreManager 
End Class