Who should read this article?
This article is meant for developers that need to persist information for their applications, or in other words if your application is backed by a database and you are planning on using SaaSGrid's database capabilities. It will explain the different database models that SaaSGrid supports and provide samples on how to program your application’s to make them compatible with SaaSGrid.

Introduction
One of SaaSGrid's greatest features is the way it simplifies application development by providing a top/down SaaS support for all levels of an application's lifecycle, including database usage. SaaSGrid focuses on allowing application developers to write applications without a multitenant notion in mind and it worries about enhancing your database schemas to enable multi-tenancy at deployment time.

Currently SaaSGrid supports MSSQL 2000 and MSSQL2005 and it allows developers to choose between 3 different deployment models for the persistence layer depending on your needs and preferences.

The three deployment models currently supported are:

Model Description
Commingled This model mixes the data of all of your customers in a single set of tables. It is the most optimal and efficient way to store data for a SaaS application but due to certain regulations and laws in certain industries, it is not always possible as an option.
Isolated Schema This model stores the data of each customer in a schema of their own; it is not as optimal and efficient as the commingled model but it gives a better degree of separation when it comes to the data of each customer. Note that this model will not deploy a new database for each customer but only a new schema and they will still share the same database.
Isolated Database This model provides the cleanest data separation possible creating an entire new database for each one of your customers. It is the most inefficient of the supported models by SaaSGrid but depending on your industry it might be required by law that the data is stored in this fashion.

For a more in depth description of each model look for more articles in the near future.

Using SaaSGrid’s Capabilities for Database Access
SaaSGrid uses a SQL Provider that should be used to take advantage of SaaSGrid’s database capabilities; however it is only available when the applications are running on a live SaaSGrid environment and when it is being referenced from the web services layer. You should develop your applications against a local database server using the ‘System.Data.SqlClient’ provider and use SaaSGrid’s conditional configuration system to switch to the ‘SaaSGrid.Data.SqlClient’ provider for deployment to SaaSGrid. 

SaaSGrid requires that connections be defined in a configurable, non-database specific fashion. For example, one should use the App.config ‘connectionStrings’ section to store database connection information, or use a configuration system of their own creation.  The reason why this is left as an explicit task is so that developers can use external databases not controlled by SaaSGrid. Provider defined connection strings have no effect at runtime; SaaSGrid ignores anything defined in a ‘SaaSGrid.Data.SqlClient’ connection string, using its own connection information instead to route information to the appropriate location.

Samples
The following example shows how database access should be implemented:

ConnectionStringSettings settings = GetSettingsFromAppConfig();
DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName);

DbConnection conn = factory.CreateConnection();
conn.ConnectionString = settings.ConnectionString;

DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from task";

conn.Open();
DbDataReader reader = cmd.ExecuteReader();
conn.Close();

Notice how the example never has any explicit usage of any specific database provider and it simply uses the common abstract data classes with factory style instantiation. It is important that your application's are designed with this pattern in mind which allows SaaSGrid to switch the database provider at runtime and do the appropriate database enhancements otherwise SaaSGrid will not be able to intercept your database calls and direct them to the appropriate location.

The next example shows how NOT to design your applications:

SqlConnection conn = new SqlConnection("my connection string");
SqlCommand cmd = new SqlCommand("select * from task", conn);

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
conn.Close();

Notice how the above example is explicitly using the SQL specific classes. Although it is not recommended by standard best practices, you can use this style if you are connecting to a database that is not managed by SaaSGrid and your are not EVER planning on switching database servers to use something other than SQL Server but generally it is recommended to use the abstract classes and don't bind your code to any particular database provider.

The next block of code shows how your 'App.config' would look like if you were planning on using SaaSGrid's database capabilities.

<connectionStrings>
     <!--SaaSGrid Local Configuration--> 
     <add name="Taskr"
          providerName="System.Data.SqlClient"
          connectionString="Server=atlas\devsql01; Database=Taskr; Trusted_Connection=True;"/>
     <!--End SaaSGrid Local Configuration-->

     <!--SaaSGrid Live Configuration
     <add name="Taskr"
          providerName="SaaSGrid.Data.SqlClient"
          connectionString="Server=atlas\devsql01; Database=Taskr; Trusted_Connection=True;"/>
     End SaaSGrid Live Configuration-->
</connectionStrings>

Notice how the only difference between the local configuration and the live configuration is that the local configuration uses the 'System.Data.SqlClient' and the live configuration uses the 'SaaSGrid.Data.SqlClient'.

Using NHibernate
SaaSGrid can work equally as well if you are using NHibernate and theoretically any other Object-Relational Mapper. In the case of NHibernate all you need to do is change the 'connection.provider' property to a provider that is compatible with SaaSGrid at the time of deployment to a live SaaSGrid instance but other than that there isn't anything else that you need to do. The connection provider that should be used is 'Apprenda.SaaSGrid.NHibernate.SaaSGridConnectionProvider, SaaSGrid.Data.NHibernate' and similarly to the regular method, you can use SaaSGrid's conditional configuration system to switch out the properties at deployment time.

The example below shows a sample hibernate configuration file for a supposed Taskr application.

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
     <session-factory>
          <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
          <!--SaaSGrid Live Configuration
          <property name="connection.provider">Apprenda.SaaSGrid.NHibernate.SaaSGridConnectionProvider, SaaSGrid.Data.NHibernate</property>
          End SaaSGrid Live Configuration--> 

          <!--SaaSGrid Local Configuration-->
          <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
          <!--End SaaSGrid Local Configuration--> 

          <property name="connection.connection_string">Server=atlas\devsql01; Database=Taskr; Trusted_Connection=True;</property>
          <property name="show_sql">true</property>

          <!--SaaSGrid Live Configuration
          <mapping file="$#BASEPATH#$\Tag.hbm.xml"/> 
          <mapping file="$#BASEPATH#$\Task.hbm.xml"/>
          End SaaSGrid Live Configuration-->

          <!--SaaSGrid Local Configuration-->
          <mapping file="Tag.hbm.xml"/>
          <mapping file="Task.hbm.xml"/>
          <!--End SaaSGrid Local Configuration-->
     </session-factory>
</hibernate-configuration>

Notice how the sample above uses both the SaaSGrid conditional configuration system as well as SaaSGrid tokens.