These examples show how Coconut Dal can be used

Tests for these use cases can be found in the unit tests project in the source code (CoconutDal.NUnit.csproj)

Given this table:

Id FirstName LastName
1 Sherlock Holmes
2 Dr Watson
3 Mycroft Holmes
4 James Moriarty

Here are examples of how each method of the Dal can be used. See section 3 for details on exception handling. For the sake of brevity, most examples do not handle exceptions unless it's relevant to the point being demonstrated.

1. Simple Data Operations

ExecuteNonQuery

dal.IsTextQuery = true;
SqlDalParameter[] para = new[] { 
    new SqlDalParameter("@LastName", "Watson"), 
    new SqlDalParameter("@NewFirstName", "John")
};
bool result = dal.ExecuteNonQuery("Update Person SET FirstName = @NewFirstName WHERE LastName = @LastName", para);
true

GetSingleValue

dal.IsTextQuery = true;
object r = dal.GetSingleValue("SELECT LastName FROM Person WHERE Id = 1");

Holmes

GetSingleValue<string>

dal.IsTextQuery = true;
string r = dal.GetSingleValue<string>("SELECT LastName FROM Person WHERE Id = 1");

Holmes

 GetSingleValue<DateTime>

dal.IsTextQuery = true;
DateTime r = dal.GetSingleValue<DateTime>("SELECT getdate()");

[Current DateTime]

 GetDataColumn (simple)

dal.IsTextQuery = true;
SqlDalParameter[] para = new[] { new SqlDalParameter("@LastName", "Holmes") };
Dictionary<int, object> result = dal.GetDataColumn("SELECT FirstName FROM Person WHERE LastName = @LastName", para);

1 Sherlock
2 Mycroft

GetDataColumn (for a set of identity values)

Dictionary<int, object> result = dal.GetDataColumn("SELECT Id, FirstName FROM Person",  "Id", new[] { 1, 3 });

1 Sherlock
3 Mycroft

GetDataRow

dal.IsTextQuery = true;
SqlDalParameter[] para = new[] { new SqlDalParameter("@LastName", "Holmes") };
Dictionary <string, object> result = dal.GetDataRow("SELECT top 1 Id, FirstName, LastName FROM Person WHERE LastName = @LastName", para);

Id 1
FirstName Sherlock
LastName Holmes

GetDataTable

dal.IsTextQuery = true;
DataTable result = dal.GetDataTable("SELECT Id, FirstName, LastName FROM Person");
 
Id FirstName LastName
1 Sherlock Holmes
2 Dr Watson
3 Mycroft Holmes
4 James Moriarty

2. Using Transaction Scope

These examples use the Implict Transaction Model. Transactions are completed by calling the Complete method. Otherwise, rollback occurs when TransactionScope is disposed.

RollBack

try
{
    using (TransactionScope transaction = new TransactionScope())
    {
        dal.IsTextQuery = true;
        dal.ExecuteNonQuery("DELETE FROM Person WHERE id = 1");
        throw new ArgumentException("Not dead, but merely sleeping.");
     }
}
catch { }

dal.IsTextQuery = true;
object r = dal.GetSingleValue("SELECT LastName FROM Person WHERE Id = 1");
Holmes

Commit

dal.IsAlwaysTextQuery = true;
using (TransactionScope transaction = new TransactionScope())
{
    SqlDalParameter[] para = new[] 
    { 
         new SqlDalParameter("@FirstName", "Irene"),
         new SqlDalParameter("@LastName", "Adler")
    };
    dal.ExecuteNonQuery("INSERT INTO Person(Id, FirstName, LastName) 
VALUES(999, @FirstName, @LastName)"
, para); transaction.Complete(); } object r = dal.GetSingleValue("SELECT LastName FROM Person WHERE id = 999");
Adler

Using Multiple Coconut Dals in one Transaction (Rollback)

dal.IsAlwaysTextQuery = true;
try
{
    using (TransactionScope transaction = new TransactionScope())
    {        
        dal.ExecuteNonQuery("DELETE FROM Person WHERE id = 1");
        ICoconutDal doubleDal = new SqlServerCoconutDal(SqlVariant.SqlServerCompact);        
        doubleDal.ExecuteNonQuery("DELETE FROM Person WHERE id = 4");

        throw new ArgumentException("They both survived!");
    }
}
catch { }

var result = dal.GetDataColumn("SELECT Id, LastName FROM Person",  "Id", new[] { 1, 4 });

 

1 Holmes
4 Moriarty

3. Exception Handling

By default, the dal will throw exceptions so they can be dealt with in user code.

 

    int result;
    dal.IsTextQuery = true;
    try
    {
        string badQuery = "SELECT nonsense FROM rubbish";
        result = dal.GetSingleValue<int>(badQuery)
    }
    catch(DbException ex)
    {
        // do something with exception
    }

 

To simplify user code, the dal can be configured to catch data exceptions (DbException, SqlException and SqlCeException.) Caught exceptions will be stored in the LastError property of the dal.

 

    dal.IsTextQuery = true;
    dal.CatchDbExceptions = true;
    string badQuery = "SELECT nonsense FROM rubbish";
int result = dal.GetSingleValue<int>(badQuery);
LogErrorIfNotNull(dal.LastError);

Last edited Jun 12, 2012 at 7:34 PM by CoconutDev, version 4

Comments

No comments yet.