Now that we've seen how to do data interaction for simple CRUD functions, let's examine more complex, custom situations. There will be times when you want to Select your data in a way that differs from the standard Speedy.Data object Fill, and for these situations you will need to create a DSO (Data Service Object) for the buisness object, and write your own query. However, Speedy.Data has several features that streamline this process greatly, and vastly improve maintainability. Simply write your business object's DSO class to inherit from Speedy.Data.SpeedyDSO to leverage these features:

Automatic select clause generation

Just because you're writing your own SQL doesn't mean you should write a select clause! If you write a select clause and hard-code the fields, you'll need to re-write it whenever your database table changes! If you write "Select *", you condemn your query to poor performance, as it pulls back every field, especially on queries with joins. Instead, let Speedy.Data auto-generate the select clause using the attributes on your business object. All you need to do is call this method in your DSO:

    
 GenerateSpeedyFieldsQueryString(typeof([your business object]), "[Query Alias]");

Automatic object population

Again, you don't want to write code to map your object to data returned from the database - that's code that can go stale fast. Let Speedy.Data auto-populate your business object based on its attributes. All you have to do is call this method in your DSO:

 GenericPopulateValues(object,DataRow);
    

Example DSO Method using Automatic select clause generation and object population:

    public void GetAllPeople(PersonCollection personCollection)
    {
        //Use the base Speedy.Data.SpeedyDSO class GenerateSpeedyFieldsQueryString() method to generate
        //a list of fields to select.  That way, adding a field to an object never means revising 
        //your custom queries.
        string queryString = @"SELECT " + GenerateSpeedyFieldsQueryString(typeof(Person), "PERS")
            + @" FROM [Person] PERS WITH (NOLOCK)
            ORDER BY ID DESC
            ";

        DataTable dt = new DataTable("Results");

        using (SqlCommand sqlCmd = new SqlCommand())
        {
            sqlCmd.CommandText = queryString;
            //Just use the connection of the ambient Service Scope.  No need to worry about
            //opening it, closing it, or managing it at all.
            SpeedyScope.CurrentConnection.FillDataTable(sqlCmd, dt);
        }
        foreach (DataRow dr in dt.Rows)
        {
            Person personObject = new Person();

            //Automatically populates the object with the values in this DataRow.
            //NOTE: If calling GenericPopulateValues on an inherited object, make sure to use
            // the overload which allows you to specify the type of the object.
            GenericPopulateValues(personObject, dr);

            //Adds the person to the collection.
            personCollection.Add(personObject);
        }
    }
    

Using stored procedures

To take the most advantage of development productivity, Speedy.Data generates parameterized SQL on-the-fly to perform database operations automatically. While this yields high-performance and is extremely productive, we understand that many development shops like to separate their SQL into stored procedures (especially if you have a database administrator on staff).

Calling a stored procedure works just like executing any other piece of custom SQL. You can also take a hybrid approach and use stored procedures only for highly complex SQL routines.

Here's the exact same example of code in a DSO from above, but this time, we'll use a stored procedure:

    public void GetAllPeopleByStoredProcedure(PersonCollection personCollection)
    {
        //Use the base Speedy.Data.SpeedyDSO class GenerateSpeedyFieldsQueryString() method to generate
        //a list of fields to select.  That way, adding a field to an object never means revising 
        //your custom queries.
        string queryString = @"GetAllPeopleByStoredProcedure";

        DataTable dt = new DataTable("Results");

        using (SqlCommand sqlCmd = new SqlCommand())
        {
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = queryString;

            //Example Parameter
            sqlCmd.Parameters.Add(new SqlParameter("@UselessParameter", "0"));

            //Just use the connection of the ambient Service Scope.  No need to worry about
            //opening it, closing it, or managing it at all.
            SpeedyScope.CurrentConnection.FillDataTable(sqlCmd, dt);
        }
        foreach (DataRow dr in dt.Rows)
        {
            Person personObject = new Person();

            //Automatically populates the object with the values in this DataRow.
            //NOTE: If calling GenericPopulateValues on an inherited object, make sure to use
            // the overload which allows you to specify the type of the object.
            GenericPopulateValues(personObject, dr);

            //Adds the person to the collection.
            personCollection.Add(personObject);
        }
    }
    

Using DataReaders as an alternative to DataAdapters / DataTables

Speedy.Data uses DataAdapters and DataTables to fill your objects. These work very well for most applications, and their connection management is bulletproof. However, if you want to squeeze a little extra performance out of your app, you may want to use a DataReader instead. You must execute this code inside a SpeedyScope using statement to take advantage of Speedy.Data's ability to automatically manage the connection for you. This ensures the DataReader is disposed of automatically. Here's an example of populating a collection of objects using a DataReader:

    PersonCollection personCollection = new PersonCollection();

    using (SpeedyScope scope = new SpeedyScope())
    {
        PersonDSO personDSO = new PersonDSO();
        //Call the DSO method to populate this SpeedyScope's connection's DataReader 
        personDSO.GetAllPeopleByDataReader();

        if (SpeedyScope.CurrentConnection.SqlDataReader != null)
        {
            //Loop through every Person
            while (SpeedyScope.CurrentConnection.SqlDataReader.Read())
            {
                //Now, we can either grab the data we want manually, 
                string result = Convert.ToString(SpeedyScope.CurrentConnection.SqlDataReader["FirstName"]);

                //OR, let Speedy.Data auto-populate the object for us!
                Person person = new Person();
                personDSO.GenericPopulateValues(person, SpeedyScope.CurrentConnection.SqlDataReader);
                personCollection.Add(person);
            }
        }

        scope.Complete();
    }
    

...and here's the GetAllPeopleByDataReader() method that lives in the DSO:

    public void GetAllPeopleByDataReader()
    {
        //Use the base Speedy.Data.SpeedyDSO class GenerateSpeedyFieldsQueryString() method to generate
        //a list of fields to select.  That way, adding a field to an object never means revising 
        //your custom queries.
        string queryString = @"SELECT " + GenerateSpeedyFieldsQueryString(typeof(Person), "PERS")
            + @" FROM [Person] PERS WITH (NOLOCK)
            ORDER BY ID DESC
            ";

        using (SqlCommand sqlCmd = new SqlCommand())
        {
            sqlCmd.CommandText = queryString;

            //Just use the connection of the ambient Service Scope.  No need to worry about
            //opening it, closing it, or managing it at all.
            SpeedyScope.CurrentConnection.GetDataReader(sqlCmd);
        }
    }

Now that we've seen how automatic CRUD works on simple objects, we'll examine how it works on objects that are inherited from each other (a Doctor IS a Person), and that are parallel to each other (a Doctor HAS DoctorDetails). There's really only one trick here, which is to use the overloads of Speedy.Data's Fill(), Update(), Insert(), and Delete() methods. These methods can accept a 'Type' as a parameter, and passing this in allows you to let Speedy.Data know what kind of complex object it is dealing with.

Automatic CRUD for inherited objects

In our sample application, our example of inherited objects are the Doctor (child) and Person (parent) objects. Speedy.Data needs to be explicitly told what object type it is operating on in order to properly work on inherited objects. This allows it to know, for example, that it should not try to operate on the "FirstName" field for a Doctor object, since that field really lives on the parent Person object.

In the example below, we'll do an Update of the Doctor object. We'll wrap everything inside a transaction. We first update the base Person object by simply calling base.Update(). We then update the Doctor object by calling Update(typeof(Doctor));

public override void Update()
{
    //Set the doctor ID to the person ID:
    mem_DoctorID = ID;

    //We'll use a transaction here, since we want everything to roll back
    //if anything fails.
    using (SpeedyScope scope = new SpeedyScope())
    {
        //First, we update the Person object by calling Update() on the base Person object.
        //For the update, an overridden method does exist on the Person object.
        base.Update();

        //Next, we'll update the actual Doctor object (this object)
        //by calling the Speedy.Data base SpeedyObject Update method.
        Update(typeof(Doctor));

        //Finally, we'll complete the transaction.
        scope.Complete();
    }
}

Automatic CRUD for parallel objects

In our sample application, our example of parallel objects are the Doctor (owner) and DoctorDetails (secondary) objects. The Doctor object has a Doctor Details object as one of its members. The Doctor and Doctor Details table share a single ID, and have a one-to-one relationship. Parallel objects are often used to encapsulate complex information, for performance reasons, and to reduce the number of columns in a single table.

In real world applications, you may have the main object/table act as a metadata table, storing important identifiers and information that can be fetched quickly, and store a parallel object/table with all the slower-to-fetch details. We'll refer to actions taken on these parallel or seconday objects as Deep. So, an update method on the Doctor object that performs an update on the parallel Doctor Details table will be called DeepUpdate().

In the example below, we'll do an update of the Doctor object. We'll wrap everything inside a transaction. We first update the main Doctor object by simply calling Update(). We then update the Doctor Details object by calling Update() on the DoctorDetails object owned by the Doctor object.

public void DeepUpdate()
{
    //We'll use a transaction here, since we want everything to roll back
    //if anything fails.
    using (SpeedyScope scope = new SpeedyScope())
    {
        //Update this object (the Doctor object)
        this.Update();

        //For simplicity's sake, we'll assume this DeepUpdate() wouldn't be called
        //unless the object had been DeepFilled().  So, we'll assume the DoctorDetails object
        //has been filled.  If it's not null, we'll update it.
        if (mem_DoctorDetails != null)
        {
            //Update the doctor details object.
            mem_DoctorDetails.Update();
        }

        //Finally, we'll complete the transaction.
        scope.Complete();
    }
}