Data Access Application Block (EntLib)

Published 9/22/2005 by Henry in EntLib
Tags:

When I first started working with the Enterprise Library, I found it hard to find information about how to use it. I even heard a story about a developing-team that decided not to use it because they could not figure out fast enough how to use it.
So I will try to make some sort of Data Access Application Block walkthrough and hope this will make it easier for people to start using the Enterprise Library.

Consuming app
Suppose we got a webapplication with one page. We are going to use the Northwind database. In the Northwind database there is a stored procedure called:
CustOrdersOrders
which needs the parameter:
@CustomerID nvarchar(5).

On the page in our webapp we got a Datagrid. In this grid we want to show the data that the stored procedure returns.
We are going to use the Data Access Application Block for access to the database.

Web Application
First we create a web Application. The language I use is C#. I renamed the WebForm1.aspx to Default.aspx and put a datagrid on the page and named it dGrid, I left 'Auto Generate Columns' checked. Enterprise Library Configuration (Tool) Next we open the Enterprise Library Configuration Tool, we click on ‘File’ > ‘Open Application” or click the ‘Open’ icon.

Open application 
Open Existing Application

Browse to the directory where the web.config of the Web Application is located selected it and click on ‘Open’. Now we see the following:

New application 
New Application

You can rename the ‘Application’ node, I named it ‘WebAppEntLib’. You right-click the ‘Application’ node and choose ‘New’ > ‘Data Access Application Block’.

New dataaccesappblock 

As you see the DAAB (Data Access Application Block) is installed (with the Configuration Application Block).

New DAAB
New DAAB

Configuration Application Block
The ‘Configuration Application Block’ is installed with the Data Access Application Block and is used to store the information about which method is used to store the config information (XML File) and where this XML based dataacces config file can be found. By chancing the ‘FileName’ under ‘XML File Storage Provider’ under the ‘dataConfiguration’ node in the name with a directory name before the name (\dataConfiguration.config) you can control where the config file will be stored. If you don’t do this the file will be in the same directory as the web.config or app.config file.

Data Access Application Block
With the Configuration Tool we make the configuration settings needed to make the Data Acces Block work for us. Database Types With this node it is possible to define other database types (Oracle, MySql etc.). With the Enterprise Library the Oracle database type is included and can easily be chosen.

Oracle db
Oracle database

Sql Connection String
With the Sql Connection String node the connectionstring used to connect to the database can be defined.
Default the following nodes are provided, we use the following values:

  • Database - ‘Northwind’
  • Integrated Security - ‘False’
  • Server - ‘(local)’

We also need the nodes (with the following values):

  • Uid (userid) - ‘developer’
  • Pwd (Password) - ‘developer’

Through a right click and choosing the menu’s new > ‘parameter’ and supplying as name ‘uid’ with the Value the username we need to connect to our database (in our case ‘developer’).
And once more with New > Password parameter we supply ‘pwd’ as Name and in our case ‘developer’ as password (by clicking the ellipse).
We also change the default node name ‘SQL Connection String’ into ‘Northwind Connection String’ by right clicking the node name and choosing ‘Rename’.

Database Instances
With the Database Instances node we define database instances. A great benefit from the Data Access Application Block is that it is relatively easy to make an application communicate with multiple databases. We define which Connectionstring, Database Type and Name of the Database we are going to use through this node.

  • Connectionstring node - ‘Northwind Connection String’ (dropdown)
  • DatabaseTypeNode - ‘Sql Server’ (dropdown) Name - ‘Northwind’

choose databasetypenode 

We save this settings with File > Save Application or through the disk icon. All settings now are written in the dataconfiguration.config file <P align=left>We save this settings with File &gt; Save Application or through the disk icon.<BR>All settings now are written in the dataconfiguration.config file.<BR></P>

Example (NOT COMPLETE):

<dataConfiguration>
 <xmlSerializerSectiontype="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
<enterpriseLibrary.databaseSettingsxmlns:xsd=http://www.w3.org/2001/XMLSchemaxmlns:xsi=http://www.w3.org/2001/XMLSchema-instancedefaultInstance="Northwind" xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data">
  <databaseTypes>
    <databaseTypename="Sql Server"type="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0,   Culture=neutral, PublicKeyToken=null"/>
  </databaseTypes>
  <instances>
    <instancename="Northwind"type="Sql Server"connectionString="Northwind Connection String"/>
  </instances>
  <connectionStrings>
    <connectionStringname="Northwind Connection String">
    <parameters>
      <parametername="database"value="Northwind"isSensitive="false"/>
      <parametername="Integrated Security"value="False"isSensitive="false"/>
      <parametername="uid"value="developer"isSensitive="false"/>
      <parametername="pwd"value="developer"isSensitive="true"/> <parametername="server"value="(local)"isSensitive="false"/>
    </parameters>
    </connectionString>
  </connectionStrings>
  </enterpriseLibrary.databaseSettings>
 </xmlSerializerSection>
</dataConfiguration>



In Visual Studio we highlight our project and select Project > Add Reference. On the .NET tab, we use "Browse..." to locate and set references to the following dll's:

 

  • Microsoft.Practices.EnterpriseLibrary.Configuration
  • Microsoft.Practices.EnterpriseLibrary.Data

Now we open the code-behind file for our startingpage: default.aspx.cs and add the following directives:

using Microsoft.Practices.EnterpriseLibrary.Data;using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

In default.aspx code-behind we create the method BindDataFromDAAB(), which we call in the Page_Load() 

   1:  private void BindDataFromDAAB()
   2:  { 
   3:      DataRow dataRow; 
   4:      
   5:      DataTable dataTable = new DataTable(); 
   6:      dataTable.Columns.Add(new DataColumn("OrderID", typeof(Int32))); 
   7:      dataTable.Columns.Add(new DataColumn("OrderDate", typeof(DateTime))); 
   8:      dataTable.Columns.Add(new DataColumn("RequiredDate", typeof(DateTime))); 
   9:      dataTable.Columns.Add(new DataColumn("ShippedDate", typeof(DateTime))); 
  10:      
  11:      //Create a database object 
  12:      Database dataBase = DatabaseFactory.CreateDatabase(); 
  13:      //In case of multiple databases 
  14:      //Database dataBase=DatabaseFactory.CreateDatabase("Northwind"); 
  15:      
  16:      //Create command wrappers to specify parameters 
  17:      DBCommandWrapper dbCommandWrapper = dataBase.GetStoredProcCommandWrapper("CustOrdersOrders"); 
  18:      //Fill parameter 
  19:      dbCommandWrapper.AddInParameter("@CustomerID", DbType.String,"BOTTM"); 
  20:      
  21:      using (IDataReader dataReader = dataBase.ExecuteReader(dbCommandWrapper))
  22:      { 
  23:          while (dataReader.Read())
  24:          { 
  25:              dataRow = dataTable.NewRow(); 
  26:              // 0 
  27:              OrderID, string dataRow[0] = dataReader.Int32(0); 
  28:              // 1 
  29:              OrderDate, datetime dataRow[1] = dataReader.GetDateTime(1); 
  30:              // 2 
  31:              RequiredDate, datetime dataRow[2] = dataReader.GetDateTime(2); 
  32:              // 3 
  33:              ShippedDate, datetime dataRow[3] = dataReader.GetDateTime(3); 
  34:              dataTable.Rows.Add(dataRow); 
  35:          } 
  36:          dataReader.Close(); 
  37:      } 
  38:      dGrid.DataSource = dataTable; 
  39:      dGrid.DataBind();
  40:  }


This code is pretty unreusable, but I think it gets the point across.
In the real world you could make a dataacces class to do all the database related stuff, a class Orders in which all the Orders related stuff would be. So you would not have all the data and business logic in the webpage code-behind. 


Henry Cordes 
My thoughts exactly... 

Using subreports in a Crystal Reports XI report in a ASP.NET webapplication, with the datasource dynamically set.
While using a Crystal Reports report which contains one or more subreports, the following syntax (the most logical) does not give the expected behaviour:

What happens is that the main report is shown with data as expected, but the sub report(s) are always empty.
When I googled, I found some people had the same experience. Nobody came up with an answer that solved this problem.
A developer on my team asked Business Objects by mail and at first got some links that were not even related to the problem. After a few weeks he recieved the following code snippet, which after we changed it to fit our purpose did the trick.
Code snippet

The solution is to loop through all sections in the main report and than loop through all objects per section.
In the 'objects' loop we check to find if the ObjectKind is of ObjectKind.SubreportObject.
If this is the case we have to check if the name of this subreport matches the subreport we need.
If this is the case the datasource is set with the following syntax:
crSubReportDoc.Database.Tables[0].SetDataSource(ds);

I suspect that this is the trick.
The looping to find the right subreport object might not have any use.

Henry Cordes
My thoughts exactly...

SQL Server and Dynamic SQL

Published 8/20/2005 by Henry in SQL
Tags:
HTML Source EditorWord wrap

'EXEC()', 'sp_executesql' and 'EXEC('EXEC sp_executesql ...')'

There are two ways to invoke dynamic SQL in MS SQL Server, 'EXEC()' and 'sp_executesql'. And you can use a combination of the two 'EXEC('EXEC sp_executesql ...')'.
EXEC() is the most simple, as illustrated by this example:

   1:  SELECT @table = 'sales' EXEC('SELECT * FROM ' + @table) 

The permissions of the current user always apply, even if the statement appears in a stored procedure. 'EXEC(''')' is similar to the EXEC of a stored procedure. But rather than calling a stored procedure, you invoke a batch of single SQL statements.

sp_executesql has the advantage that it allows you to pass parameters to the dynamic query, both for input and output.
Here is an example with an output parameter:

   1:  DECLARE @sql nvarchar(4000) -- nvarchar(MAX) on SQL 2005. DECLARE @col sysname 
   2:  DECLARE @min varchar(20) 
   3:  SELECT @col = N'au_fname' 
   4:  SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col + N')) FROM authors' 
   5:  EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT SELECT @min 

Thus, you can much easier get a value from your dynamic SQL statement into a local variable than with EXEC().

These points apply to EXEC() and sp_executesql as well:

  • The SQL code is it in own scope, and you have no access to the variables in the calling stored procedure;
  • The permissions of the actual user apply;
  • Any USE statement will not affect the calling stored procedure;
  • Temp tables created in the SQL batch will not be available to the calling procedure;
  • SET statements only affects the SQL batch, not the caller;
  • Termination of the batch executed by sp_executesql terminates the calling procedure too;
  • @@error reports the status of the last statement in the dynamic SQL code;
  • According to Books Online the return value from sp_executesql is either 0 for success or 1 for failure. However it appears that the return value is the final value of @@error, at least in SQL 2000.

EXEC('EXEC sp_executesql ....
By using 'EXEC('EXEC sp_executesql ....' it is possible to use statement parameters with the dynamic query string. The sql query must be in unicode string , in nvarchar the MAX characters is 4000. By using EXEC('EXEC sp_executesql ...., multiple parameters can be concatenated, which results in the possibility to use more than 4000 characters in the dynamic query (the point with dynamic ofcourse is that you can not be sure what length the query will be).

Example:

   1:  DECLARE @query nvarchar(4000)
   2:  DECLARE @@queryDynamic nvarchar(4000)
   3:  DECLARE @l_Startdate datetimeDECLARE @l_Enddate datetimeDECLARE @dbName varchar(50)
   4:  DECLARE @dbName2 varchar(50)
   5:   
   6:  SET @dbName = '[SQLServer_instance].[Databasename]'
   7:  SET @dbName2 = '[SQLServer_instance2].[Databasename2]' 
   8:  SET @query = 'SELECT * FROM ' + @dbName + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate '
   9:  SET @@queryDynamic = 'UNION ' + 'SELECT * FROM ' + @dbName2 + '.dbo.tblTable ' + 'WHERE startDate >= @pl_Startdate AND Enddate < @pl_Enddate ' 
  10:   
  11:  EXEC('EXEC sp_executesql N''' + @query + @queryDynamic + ''', N''@pl_Startdate datetime ,
 12:                              @pl_Enddate datetime'', @pl_Startdate = ''' + @l_Startdate + ''', @pl_Enddate = ''' + @l_Enddate + '''')


Henry Cordes
My thoughts exactly...

Microsoft Enterprise Library 2005

Published 8/16/2005 by Henry in EntLib
Tags:

A sort off add-on for Visual Studio, this Application Blocks that are in the Library are released by the Patterns and Practices Group within Microsoft. They are reusable components that encapsulate Microsoft best practices in each area.

The Application blocks:

  • Caching
  • Configuration
  • Cryptography
  • Data Access
  • Exception Handling
  • Logging and Instrumentation
  • Security

The overall goals of the Enterprise Library are the following:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks include defined extensibility points that allow developers to customize the behavior of the application blocks by adding in their own code.
  • Ease of use. Enterprise Library offers numerous usability improvements, including a graphical configuration tool, a simpler installation procedure, and clearer and more complete documentation and samples.
  • Integration. Enterprise Library application blocks are designed to work well together and are tested to make sure that they do. It is also possible to use the application blocks individually (except in cases where the blocks depend on each other, such as on the Configuration Application Block).

A graphical configuration tool (Enterprise Library Configuration Console), provides a way to change and validate application block settings without having to manually edit the XML configuration files where they are stored. The Configuration Console displays the settings and also supplies default values that you can change. Because the configuration settings are presented in a graphical tool, along with default values and information about what each setting means, the task of configuring the application blocks can be handled by developers and system administrators.

The Configuration Console 
The Configuration Console

Each block is well-documented and includes complete source code. In fact, when you download the library you will find that no compiled assemblies are included. In order to implement these products, you must compile each one from the included source code. Fortunately, there is an included batch file that will do all the work for you. In addition, the documentation clearly covers installation, implementation and deployment for all blocks.

 

Henry Cordes
My thoughts exactly...


Crystal Reports XI Datasource

Published 8/15/2005 by hendaman

In Crystal Reports Server XI it is possible to schedule reports. Also users can be given rights to browse reports and run them ad hoc. The problem is that datasources are defined in the binary .rpt file. If you don's use Business Views it is not possible to define a datasource at a higher level. In the Management Console it is possible to change the datasource for a report, but in reality this does not always work and a nasty 'dataconnector error' is the result, instead of a report.

The workaround we found is to change the 'hosts file' on the developing machine. The 'hosts file' can be found in '<WINDIR>\System32\etc', (<WINDIR> stands for the directory where Windows is installed "C:\WINDOWS"). In the 'hosts file' IP-Adresses are being pointed to hostnames, so the (local) machine knows which IP-Adress to look for if we use the hostname.

Example:
102.54.94.97     rhino.acme.com          # sourceserver
38.25.63.10      x.acme.com              # x clienthost
127.0.0.1        localhost

In the hosts file we changed the developing SQL Server instance IP- adres in the production SQL Server instance name.
If on the development network the SQL Server instance name is "Develop" with ip adress 192.168.0.10 and in production the SQL Server instance name is "Production\Instance" than we would add this in the hosts file:

192.168.0.10        Production\Instance

This way the names are the same as on the production machine and the reports work without having to change the datasource.
I realize this is a work around, but we were happy we found it, because the only other way we could think of was installing a SQL Server instance with the production names.

Henry Cordes
My thoughts exactly...