Data Layer - netTiers/netTiers GitHub Wiki

Using a layered approach when building out a framework gives you lots of flexibility especially when it comes to your data layer.

There are many valid reasons why you would want to separate out the data access layer from other layers. Keeping the data access layers separate give you the ability to create a data abstraction API. This means that you create an API for your data, which actually knows nothing about how to retrieve data from your database, web service, xml store, etc. Microsoft has really pushed the provider pattern, leading to excellent flexibility in your applications. Think of the provider pattern as a reverse plug-in pattern. A plug-in exists in only certain directories and the discovery is done by the client and is typically loaded in at application start. Whereas, a provider is entered through the configuration and loaded in at runtime. The benefit is that you can write several providers depending on your needs. Using a provider allows you to switch out your entire implementation just by changing the default provider name in your configured implementation. These layers, the abstraction data layer and the provider specific implementation layer, reference the Entities project as a dependancy. When working with data retrieved from the database, the entities and collections are actually created from these layers and returned to the consuming methods.

Take for example, you are wanting to create a new project to revolutionize the CRM application marketplace. You have the idea of building this application as a smart client, and server application. So you sit requirement is to create an excellent client server application. This means, that you can generate a single codebase, and have access to your data API out of the box through web services for your client application. On the client you would use the WsClientProvider, while the server application hosting the WebService endpoint would use the SqlClientProvider.

Currently .netTiers supports Sql Server 2000, Sql Server 2005, & Sql Server Express. Several other database providers like SqlLite, Firebird, and Oracle have limited support with the available Generic Client.

The DataRepository is the entry point into your data access API using the default configured data provider. It is the central mechanism for instantiating and loading the individual providers at runtime, and more importantly, retrieving and saving data for your data API. The DataRepository itself is essentially a singleton facade object into your API by using the Decorator Pattern of the individual TableModule Entity Providers. This class lives in the abstract Data Access Layer as mentioned earlier so that it does not reference any particular provider implementation, which only knows about all of your entities, their access methods.

Some typical method call looks like:

Example:

    DataRepository.OrdersProvider.GetAll();
    DataRepository.OrdersProvider.GetByOrderDate(DateTime.Today);
    DataRepository.OrdersProvider.Insert(transactionManager, order);

When the DataRepository is first called, it will check the current configuration in your app/web.config in the netTiersService configSection to determine the current default provider and possibly any other provider configurations that have been configured. Configuration was covered in the Getting Started Portion of the documentation. During this process, the data repository will load the default NetTiersProvider, by default the .netTiers provider will be the SqlNetTiersProvider. This class lives in the DataAccessLayer.SqlClient layer of the default generation process.

Access Methods are the methods that comprise the Data API. Depending on how .netTiers is configured it will identify and create a logical set of data access methods utilizing the data model's relationships and how their behavior affects your normalized relationships. That statement is a bit deep, so let's clarify. During the .netTiers generation process, the .netTiers templates will identify and create your initial API methods through based on indexes, keys, stored procedures, and a few other mechanisms all discussed below.

• Get All
• Get By Primary Key Id
• Get By composite foreign key columns
• Get By Composite index column
• Get By Dynamic WhereClause (Paged Result)
• Get By Custom Stored Procedure

• Insert
• Update
• Delete
• Save
• Custom - using Custom Stored Procedure

For extended circumstances where you want to roll your own data access calls inside the API, you can simply use the same methods .netTiers auto-creates for you, or you can even tap all of the DataAccess methods that the Enterprise Library Data Application Block provides. It will automatically obtain the current connection information and you can pass it existing open transactions using the TransactionManager.

Generic Data Access:

• ExecuteReader
• ExecuteScalar
• ExecuteDataSet
• ExecuteNonQuery

Example:

    string sqlCommand = "GetEmployeeName";
    // Retrieve EmployeeName ExecuteScalar returns an object, so 
    // we cast to the correct type (string). 
    string employeeName = (string)DataRepository.Provider.ExecuteScalar(CommandType.StoredProcedure, sqlCommand);

How can I use Dynamic where clauses in my API?

There are several classes to assist in building dynamic search conditions in a parameterized and secure fashion.

• SqlExpressionParser - a search term parser
• SqlStringBuilder - a filter expression builder (uses SqlExpressionParser internally)
• SqlFilterBuilder - a generic filter expression builder (uses entity column enumerations)
• EntityFilterBuilder - a strongly typed filter expression builder
• ParameterizedSqlExpressionParser - parses search terms into parameterized expressions
• ParameterizedSqlFilterBuilder - a generic parameterized expression builder (uses entity column enumerations)
• EntityParameterBuilder - a strongly typed parameterized expression builder
• SqlFilterParameter - represents information needed for a database command parameter
• SqlFilterParameterCollection - a parameterized query string and a collection of SqlFilterParameter objects
• SqlParameter - a subclass of System.Web.UI.WebControls.Parameter that will allow an ASP.NET developer to use the ParameterizedSqlFilterBuilder (default) or SqlFilterBuilder along with any data source control.
• EntityFilter - used along with the SqlParameter to bind filter input controls to a data source control.)))

Examples:

    CustomersParameterBuilder query1 = new CustomersParameterBuilder();
    query1.Append(CustomersColumn.CustomerID, "A%");
    query1.Append(CustomersColumn.City, "London, Berlin");
 
    TList<Customers> list1 = DataRepository.CustomersProvider.Find(query1.GetParameters());
    Console.WriteLine("Query1 = {0}", query1);
    Console.WriteLine("Count1 = {0}", list1.Count);
``

**Results**

Query1 
```sql
(CustomerID LIKE @Param0) AND (City = @Param1 OR City = @Param2)
-- Count1 = 2

Supposing you had more advanced requirements though. For instance, suppose you wanted to find all customers who's CustomerID began with "A" and lived in London OR who's CustomerID began with "B" and lived in Berlin. In this case you would need to apply a little more elbow grease:

More Advanced Example

    CustomersParameterBuilder query1 = new CustomersParameterBuilder();
    query1.Clear();
    query1.Junction = string.Empty; // This prevents the ParameterBuilder from throwing an "AND" before next line's output
    query1.BeginGroup();
    query1.Append(string.Empty, CustomersColumn.CustomerID, "A%", true);
    query1.Append("AND", CustomersColumn.City, "London", true);
    query1.EndGroup();
    query1.BeginGroup("OR");
    query1.Append(string.Empty, CustomersColumn.CustomerID, "B%", true);
    query1.Append("AND", CustomersColumn.City, "Berlin", true);
    query1.EndGroup();
 
    TList<Customers> list1 = DataRepository.CustomersProvider.Find(query1.GetParameters());
    Console.WriteLine("Query1 = {0}", query1);

Results

Query1

(CustomerID LIKE @Param0 AND City = @Param1) OR (CustomerID LIKE @Param2 AND City = @Param3)

**Please note **that the current version as of writing this (2.2.0.603) as well as some previous versions exhibit some rather odd behavior when using grouping (the BeginGroup and EndGroup). Basically, the bug creates a situation whereby the BeginGroup and EndGroup methods do not act as expected and instead just create empty "()" blocks within your sql statement. Hopefully this will be fixed in the next build. However, for the time being you can get around this bug by modifying your NetTiers templates. All you'll need to do is a minor edit in the SqlStringBuilder.cst file (found under \DataAccessLayer\Query from your template directory). Search this file for the phrase "ensuregroups" and you'll find the following code:

      internal virtual void EnsureGroups()
      {
         while (_groupCount > 0)
         {
            EndGroup();
         }
      }

If you comment out the logic in that function, like so:

      internal virtual void EnsureGroups()
      {
       //  while (_groupCount > 0)
       //  {
       //     EndGroup();
       //  }
      }

you will find that the BeginGroup and EndGroup methods work as expected. Please be advised that since any grouping you create via the BeginGroup method will have to be manually ended using the EndGroup method. If you do not properly end all groups you begin the generated code will not do this for you and your sql statement will end up having too many open parentheses and not enough close parentheses and will almost certainly not work as expected.)))

Along with the filter expression, query1 also has a collection of SqlFilterParameter objects that hold the name, type and value of each named parameter. This collection, which is returned by calling query1.GetParameters(), is passed into a new Find method overload that dynamically generates a paramaterized SQL statement, applies the necessary command parameters, then executes the query.

The SqlStringBuilder class, along with all of its sub-classes, contain several variations to the Append method to allow you to define simple or complex queries. Also, notice that the Append method used in this example accepts the use of wild card characters.

Create a non-parameterized query for those times when one is not necessary. Example:

    CustomersFilterBuilder query2 = new CustomersFilterBuilder();
    query2.Append(CustomersColumn.CustomerID, "A*");
    query2.Append(CustomersColumn.City, "London, Berlin");
    int count = 0;
    TList<Customers> list2 = DataRepository.CustomersProvider.GetPaged(
        query2.ToString(), null, 0, 100, out count);
 
    Console.WriteLine("Query2 = {0}", query2);
    Console.WriteLine("Count2 = {0}", list2.Count);    

Results:

Query2 = (CustomerID LIKE 'A%') AND (City = 'London' OR City = 'Berlin')
--Count2 = 2

By default netTiers pre-populates the CustomProcedureStartsWith property with a formattable string “{0}”, where {0}= Current Table Name, meaning it will look through all of the stored procedures in the database, and if your procedure starts with that string it will be eligible for inclusion as a custom stored procedure.

An example would be: CustomProcedureStartsWith = '{1}cust_{0}' ProcedurePrefix = "usp" This would match any procedures that begin with usp_cust_TableName_GetByAnyMethod;

{1}cust_{0}_GetByAnyMethod The appropriate methods will be generated for your stored procedure.

Example of a matching procedure:

create procedure _Employee_GetByBirthdate @birthDate dateTime As 
Select * from Employee where birthDate = @birthdate GO

So when you start you generation you will get the appropriate methods created for you in your DAL’s EmployeeProvider.

    DateTime today = DateTime.Today; 
    TList<Employee> todaysBirthdayList = DataRepository.EmployeeProvider.GetByBirthdate(today);

When a custom stored procedure is found for inclusion, we will check the command and see what type of result sets you’re bringing back with you. You have the option of returning one of the following 3 types, a TList of the Entity, a DataSet, or an IDataReader.

Rules for checking Data Returned.

• If the custom procedure returns all the same columns that the table you are wanting to include it has, then a collection of entities is returned.

NOTE: Every column of the entity must be included in the returned result set. -Should you return back only a few columns, or possibly join with a few other tables, then the configured CustomNonMatchingReturnType type will be returned. So this will either be a Dataset or IDataReader.

-When using an IDataReader, the consumer is responsible for closing the reader. The IDataReader is useful when returning multiple result sets in a custom stored procedure.

I have a database for every client or user with the same table structure, etc. How can I change my connection string at runtime in the DataRepository?

Example:

DataRepository.AddConnection("Vendor1DynamicCS", "Data Source=(local);Initial Catalog=Vendor1Northwind;Integrated Security=true;");

TList<Info> list = DataRepository.Connections["Vendor1DynamicCS"].Provider.InfoProvider.GetAll() 

How can I configure multiple netTiersService configSections for multiple databases within the sameapp/web.config?{BR} You must set the name of the configSections to the name of the assembly involved in section.

Example:

  <configSections>
    <section name="Orders.Data"
             type="Orders.Data.Bases.NetTiersServiceSection, Orders.Data"
             allowDefinition="MachineToApplication"
             restartOnExternalChanges="true" />

    <section name="Inventory.Data"
             type="Inventory.Data.Bases.NetTiersServiceSection, Inventory.Data"
             allowDefinition="MachineToApplication"
             restartOnExternalChanges="true" />

  </configSections>

  <connectionStrings>
    <add name="connectionStringOrders"
         connectionString="database=Orders;Integrated Security=true;Connection Timeout=1;server=.;" />

    <add name="connectionStringInventory"
         connectionString="database=Inventory;Integrated Security=true;Connection Timeout=1;server=.;" />

  </connectionStrings>

  <Orders.Data defaultProvider="SqlNetTiersProvider">
    <providers>
      <add name="SqlNetTiersProvider"
           type="Orders.Data.SqlClient.SqlNetTiersProvider, Orders.Data.SqlClient"
           connectionStringName="connectionStringOrders"
           useStoredProcedure="false"
           providerInvariantName="System.Data.SqlClient" />
    </providers>
  </Orders.Data>
 
  <Inventory.Data defaultProvider="SqlNetTiersProvider2">
    <providers>
      <add name="SqlNetTiersProvider2"
           type="Inventory.Data.SqlClient.SqlNetTiersProvider, Inventory.Data.SqlClient"
           connectionStringName="connectionStringInventory"
           useStoredProcedure="false"
           providerInvariantName="System.Data.SqlClient" />
    </providers>
  </Inventory.Data>

In cases where you can't access the configuration you can use the public LoadProvider method of the DataRepository. Here's an example of how you would load the provider at runtime.

Example:

    SqlNetTiersProvider provider = new SqlNetTiersProvider();
    NameValueCollection collection = new NameValueCollection();
    collection.Add("UseStoredProcedure", "false");
    collection.Add("EnableEntityTracking", "true");
    collection.Add("EntityCreationalFactoryType", "Northwind.Entities.EntityFactory");
    collection.Add("EnableMethodAuthorization", "false");
    collection.Add("ConnectionString", "server=.\\Sql2000;database=Northwind;Integrated Security=true;");
    collection.Add("ConnectionStringName", "MyDynamicConnectionString");
    collection.Add("ProviderInvariantName", "System.Data.SqlClient");

    provider.Initialize("DynamicSqlNetTiersProvider", collection);

    DataRepository.LoadProvider(provider, true);

    TList<Orders> list = DataRepository.OrdersProvider.GetAll();
    Response.Write(list.Count.ToString());

    NetTiersProvider provider = DataRepository.Providers["MyDynamicProvider"];
    DataRepository.LoadProvider(provider, true);