SqlNetFramework code samples
The following code samples will exemplify how the SqlNetFramework works.
Execute a SQL text-inline SQL with just a single line of .NET code
To execute your SQL text-inline SQL/Stored Procedure you only need to call the static methods of the DbManager class. With just a single line of .NET code you can execute a SQL statement if it doesn't expect parameters.
1
using SqlNetFramework.Management;
2//Selecting data using the DbManager class.
3IDataReader reader = DbManager.Instance.ExecuteReader(0, "DemoDb");
SqlNetFramework an alternative to MS DAAB
The following code snippet shows you how to insert data using the Microsoft Data Access Application Block. As you can see you need to create hard code SQL and the definition of the SQL parameters (e.g. type, size, etc)
1using Microsoft.Practices.EnterpriseLibrary.Data;
2using System.Data.Common;
3
4Database db = DatabaseFactory.CreateDatabase();
5//Hard coded SQL.
6DbCommand command = db.GetSqlStringCommand(
7 "INSERT INTO Customer (FirstName, LastName) VALUES (?, ?)");
8//I create the parameters using generic types.
9db.AddInParameter(command, "FirstName", DbType.String, "Bill");
10db.AddInParameter(command, "LastName", DbType.String, "Smith");
11db.ExecuteNonQuery(command);
The SqlNetFramework is an alternative for developers using the Microsoft Data Access Application Block. The SqlNetFramework is as easy to use as the MS DAAB and reduces more lines of .NET code than the MS DAAB. As you can see in the code snippet below to execute a SQL statement that expects parameters you only need to specify the parameter name and its value, no parameter definitions (e.g. type, size) are required. No hard code SQL is added to your application code.
1using SqlNetFramework.Management;
2using SqlNetFramework;
3
4CiOrderedDictionary parameterValues = new CiOrderedDictionary();
5//A list is created with the parameter names and their values.
6parameterValues.Add("FirstName", "Bill");
7parameterValues.Add("LastName", "Smith");
8DbManager.Instance.ExecuteNonQuery(4, "DemoDb", parameterValues);
SqlStoreDataSource control an alternative to the SqlDataSource
The SqlDataSource control is a very useful control if you want to create data-driven ASP.NET web applications very fast. However, the SqlDataSource control has some disadvantages and some developers prefer don't use the SqlDataSource control. The SqlDataSource control adds the connection string, data provider name and SQL code directly in the markup of the ASPX web form and as consequence the SqlDataSource control doesn't promote SQL reutilization.
1<asp:GridView ID="GridView1" runat="server" DataKeyNames="CustomerId"
2 DataSourceID="Sqldatasource1">
3</asp:GridView>
4<asp:sqldatasource ID="Sqldatasource1" runat="server"
5 ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\
6 DemoDb.mdb"
7 ProviderName="System.Data.OleDb"
8 SelectCommand="SELECT * FROM CUSTOMER ORDER BY CUSTOMERID">
9</asp:sqldatasource>
The SqlStoreDataSource control doesn't add the connection string, data provider name and SQL code directly in the markup of the ASPX web form. When you develop your data-driven ASP.NET web sites using the SqlStoreDataSource control you will be able to re-use your SQL code in other applications (e.g. WinForms)
1<asp:GridView ID="GridView1" runat="server" DataKeyNames="CustomerId"
2 DataSourceID="dsCustomers">
3</asp:GridView>
4<SqlNetFramework:SqlStoreDataSource runat="server" ID="dsCustomers"
5 ConnectionID="DemoDb" SelectStatementID="0">
6</SqlNetFramework:SqlStoreDataSource>
No hard code SQL in your application code
The following code snippet shows how to read data for displaying using the Microsoft Data Application Block (MS DAAB). As you can see there is hard code SQL code in your application code.
1using Microsoft.Practices.EnterpriseLibrary.Data;
2
3Database db = DatabaseFactory.CreateDatabase();
4this.GridView1.DataSource = db.ExecuteReader(CommandType.Text,
5 "SELECT * FROM CUSTOMER ORDER BY CUSTOMERID");
6this.GridView1.DataBind();
When reading data using the SqlNetFramework you don't need to hard code your SQL in your application code. You only need to specify a SQL statement identifier that make references to the SQL code in your external SQL repository.
1using SqlNetFramework.Management;
2
3this.GridView1.DataSource = DbManager.Instance.ExecuteReader(0, "DemoDb");
4this.GridView1.DataBind();
All the SQL code that you create using the GUI SqlEditor application is stored in an external SQL repository. You can store your SQL code in a XML file or in a database (e.g. Microsoft SQL Server, Oracle). Once you have created your SQL Code database you can re-use your SQL code in any application where you were using the SqlNetFramework. You will never need to add SQL code in your application code.
1<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
2 <SqlStatement>
3 <SqlStatementId>0</SqlStatementId>
4 <CommandText><![CDATA[SELECT * FROM CUSTOMER ORDER BY CUSTOMERID]]>
5 </CommandText>
6 </SqlStatement>
7</XmlSqlDataStore>
Write portable SQL code across multiple .NET applications
The following code snippet shows you how to select data using the SqlNetFramework in a WinForms application. The '0' identifier makes reference to a select SQL statement in the external SQL repository.
1//WinForms application
2this.dataGridView1.DataSource = this.bindingSource1;
3this.bindingSource1.DataSource = DbManager.Instance.ExecuteReader(0, "DemoDb");
The following code snippet shows you how to select data using the SqlNetFramework in a ASP.NET web site application. The '0' identifier makes reference to a select SQL statement in the external SQL repository. For this and the previous code snippet we use the same external SQL repository.
1//ASP.NET web application
2this.GridView1.DataSource = DbManager.Instance.ExecuteReader(0, "DemoDb");
3this.GridView1.DataBind();
Write generic ADO.NET code - data provider independent
When using the Microsoft Data Access Application Block if you want to execute a SQL statement that requires parameters you need to add specific-type references to the data provider that you are using. If you want to develop .NET code compatible with different database vendors or if you need to change from database vendor in the future you would have to change this specific-type references in your application code.
1using Microsoft.Practices.EnterpriseLibrary.Data;
2using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
3using System.Data.SqlClient;
4
5SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase("DemoDbSqlClient");
6
7SqlCommand command = (SqlCommand)db.GetSqlStringCommand(
8 "SELECT * FROM CUSTOMER WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME");
9
10db.AddInParameter(command, "@FirstName", SqlDbType.VarChar, "Luis");
11command.Parameters[0].Size = 50;
12db.AddInParameter(command, "@LastName", SqlDbType.VarChar, "Ramirez");
13command.Parameters[1].Size = 50;
14
15this.GridView1.DataSource = db.ExecuteReader(command);
16this.GridView1.DataBind();
When using the SqlNetFramework to execute a SQL statement that requires parameters you only need to specify the parameter names and their values, nothing else. The SqlNetFramework doesn't add specific-type references to your application code. It allows you to develop portable applications between different database vendors and minimize application code changes if you need to change from database vendor in the future.
1using SqlNetFramework.Management;
2using SqlNetFramework;
3
4CiOrderedDictionary parameterValues = new CiOrderedDictionary();
5
6parameterValues.Add("FirstName", "Luis");
7parameterValues.Add("LastName", "Ramirez");
8
9this.GridView1.DataSource = DbManager.Instance.ExecuteReader(0, "DemoDbSqlClient",
10 parameterValues);
11this.GridView1.DataBind();
The SQL code used by the SqlNetFramework is stored in an external SQL repository. All the database specific-type references are stored in the external SQL repository and not in your application code.
1<XmlSqlDataStore xmlns="http://tempuri.org/XmlSqlDataStore.xsd">
2 <SqlStatement>
3 <SqlStatementId>0</SqlStatementId>
4 <SqlStatementName>Customer by name</SqlStatementName>
5 <Description />
6 <CommandText><![CDATA[
7 SELECT * FROM CUSTOMER WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME]]>
8 </CommandText>
9 <SqlStatementType>Text</SqlStatementType>
10 <LastModification>2008-01-22T00:00:00-06:00</LastModification>
11 </SqlStatement>
12 <SqlParameter>
13 <SqlParameterId>0</SqlParameterId>
14 <SqlStatementId>0</SqlStatementId>
15 <SqlParameterName>@FIRSTNAME</SqlParameterName>
16 </SqlParameter>
17 <SqlParameter>
18 <SqlParameterId>1</SqlParameterId>
19 <SqlStatementId>0</SqlStatementId>
20 <SqlParameterName>@LASTNAME</SqlParameterName>
21 </SqlParameter>
22 <SqlParameterProperty>
23 <PropertyName>Size</PropertyName>
24 <PropertyValue>50</PropertyValue>
25 <SqlParameterID>0</SqlParameterID>
26 </SqlParameterProperty>
27 <SqlParameterProperty>
28 <PropertyName>SqlDbType</PropertyName>
29 <PropertyValue>VarChar</PropertyValue>
30 <SqlParameterID>0</SqlParameterID>
31 </SqlParameterProperty>
32 <SqlParameterProperty>
33 <PropertyName>Size</PropertyName>
34 <PropertyValue>50</PropertyValue>
35 <SqlParameterID>1</SqlParameterID>
36 </SqlParameterProperty>
37 <SqlParameterProperty>
38 <PropertyName>SqlDbType</PropertyName>
39 <PropertyValue>VarChar</PropertyValue>
40 <SqlParameterID>1</SqlParameterID>
41 </SqlParameterProperty>
42</XmlSqlDataStore>