Working with SQL Records

Introduction

This section describes how to work with data through SQL Records from Business Foundation (BF).

All classes referred to are available in the Mediachase.BusinessFoundation.Data.Sql and Mediachase.BusinessFoundation.Data.Sql.Management namespaces.

CustomTableRow Class

A CustomTableRow object represents an SQL user row. Use the CustomTableRow object and its properties and methods to retrieve, evaluate, insert, delete, and update the values in the table.

Create a new CustomTableRow object, passing the Table object to create a new record. After creating a new CustomTableRow, use the index property to set the column values. Finally, call the Update method of the CustomTableRow object to confirm the addition.

You can delete a row from the table by calling the Delete method of the CustomTableRow object. The actual removal occurs when you call the Delete method.

Creating a New Record

Create a new CustomTableRow object, passing the Table object to create a new record. After creating a new CustomTableRow, use the index property to set the column values. Finally, call the Update method of the CustomTableRow object to confirm the addition. Use the PrimaryKeyId property to get the primary key for the record.

Example: Create a record

// Create a new book{
        CustomTableRow newBook = CustomTableRow (bookTable);
        newBook\["Title"] = "Programming Windows Phone 7";
        newBook.Update();
        PrimaryKeyId newBookPk = newBook.PrimaryKeyId.Value;

Loading a Record from a Table

A record can be loaded from a table by creating a new CustomTableRow object with primary key id. If the record is not found, an ObjectNotFoundException exception will be thrown.

Example: Load a record

// Load book
        PrimaryKeyId bookPk = 1;
        CustomTableRow book = new CustomTableRow (bookTable, bookPk);
        String title = (String)book\["Title"];

Selecting Records from a Table

Call the CustomTableRow.List statement to return an array of CustomTableRow objects from the table. The CustomTableRow class has several List methods.

Refer to Filtering and Sorting for more information on filters and sorting.

Getting the Total Records Count

Call the CustomTableRow.GetTotalCount statement to return the total record count.

Extended Methods

You can use the extended methods CustomTableRow.GetReader and CustomTableRow.GetTable to get records in the inner .NET formats.

Deleting a Row

You can delete a row from a table by calling the Delete method of the CustomTableRow object. The actual removal occurs when you call the Delete method.

Example: Deleting a row

// Step 1. Load book{color}
        PrimaryKeyId bookPk = 1;
        CustomTableRow book = new CustomTableRow(bookTable, bookPk);

        // Step 2. Delete book{color}
        book.Delete();

Use the CustomTableRow.Delete static method to delete rows without row loading.

Example: Deleting rows without reloading

CustomTableRow.Delete(new TableConfig(bookTable), bookPk);

Transaction

Upon instantiating a SqlTransactionScope by calling SqlContext.BeginTransaction statement, the transaction manager determines which transaction to participate in. Once determined, the scope always participates in that transaction. You can obtain a reference to the ambient transaction by calling the TransactionScope property of the SqlContext class.

If no exception occurs within the transaction scope (that is, between the initialization of the SqlTransactionScope object and the calling of its Dispose method), then the transaction in which the scope participates is allowed to proceed. If an exception does occur within the transaction scope, the transaction in which it participates will be rolled back.

When your application completes all work it wants to perform in a transaction, you should call the Commit method only once to inform that transaction manager that it is acceptable to commit the transaction. Failing to call this method aborts the transaction.

A call to the Dispose method marks the end of the transaction scope. Exceptions that occur after calling this method may not affect the transaction.

A call to the AddCallback method registers custom callback. You can use transaction callback to handle final transaction commit or rollback.

Example: Create a new record in the transaction scope

// Step 1. Initiaze Sql Metamodel
        using (SqlContext.Current = new SqlContext(connectionString))
        {
            using (SqlTransactionScope tran = SqlContext.Current.BeginTransaction())
            {
            // Step 1. Get Book table
                Table bookTable = SqlContext.Current.Database.Tables["Book"];


            // Step 2. Create a new book
            CustomTableRow newBook = new CustomTableRow(bookTable);
            newBook["Title"] = "Programming Windows Phone 7";
            newBook.Update();

            PrimaryKeyId newBookPk = newBook.PrimaryKeyId.Value;

            // Step N. Call Commit to commit transaction
            tran.Commit();
            }
        }

Typed Table Row

A table row object can be typed or untyped, and you can use either one (CustomTableRow) in your applications. However, BF has more tool support for typed rows, and programming with them is easier and less error-prone. Typed table row uses a pre-created storage procedure for command optimization.

Do the following to use the typed table row:

1. Create an SQL table and add columns.
2. Run McCodeGen to create the storage procedure.
3. Execute the script and add storage procedure to your database.
4. Run McCodeGen to create a typed row C# class.
5. Add the typed row to your project.

McCodeGen.exe

The McCodeGen application reads the mcgen file, extracts the meta model, and generates a template and an output text file.

McCodeGen.exe-mcgen:mcgenFile-out:outputFile

Using Mcgen File to Create a Typed Row

The McCodeGen application can generate C# classes from the mcgen file. A typed row class is very similar to the untyped class CustomTableRow, but includes properties mapped to column values and the column name constant string.

The Mcgen file should include this:

<?xml version="1.0" encoding="utf-8" ?>
            <mcgen xmlns="mediachase.codegen.config" language="C#" template="Templates\BusinessFoundationRowTemplate.aspx">
            <BfTableObject>
            <ConnectionString>*Data Source=(local);Initial Catalog=TestDB;User ID=sa;Password=;* </ConnectionString>
            <Table>*Book*</Table>
            <Params>
            <add key="Namespace" value="*Test.Database*" />
            <add key="ClassName" value="*BookRow*" />
            </Params>
            </BfTableObject>
            </mcgen>

PPaste your connection string and table name, declare an output class namespace and name, then execute McCodeGen from the command line.

cCodeGen.exe* -mcgen:BookRow.mcgen -out:BookRow.cs

Then you can add a typed row class to your .NET project.

Using Mcgen File to Create SQL Storage Procedure Script

The McCodeGen application can also generate SQL Storage procedure scripts from the mcgen file.

The mcgen file should include this:

<?xml version="1.0" encoding="utf-8" ?>
        <mcgen xmlns="mediachase.codegen.config" template="Templates\BusinessFoundationRowSpTemplate.aspx" extension=".sql">
        <BfTableObject>
        <ConnectionString> *Data Source=(local);Initial Catalog=TestDB;User ID=sa;Password=;* </ConnectionString>
        <Table>*tom_Category*</Table>
        </BfTableObject>
        </mcgen>

Paste your connection string, table name, then execute McCodeGen from the command line.

McCodeGen.exe -mcgen:BookRow.SP.mcgen -out:BookRow.sql

Visual Studio Integration

The McCodeGen application can be integrated with Visual Studio by adding themcgen file to aVisual Studio Project.

Check the properties for your mcgen file (in this case a CategoryRow.mcgen), the "Custom Tool" field should be blank. Enter the name "McCodeGenerator".

The tool should then run automatically. Check to make sure that the output was created by opening the Solution Explorer.

SqlBlobStream Class

An SqlBlobStream object exposes a Stream around a text, ntext, or image column, supporting both synchronous and asynchronous read and write operations.

A common practice to reduce the amount of memory used when writing a BLOB value is to write the BLOB to the database in "chunks". The process of writing a BLOB to a database in this way depends on the capabilities of your database.

Use the SqlBlobStream class to read, write, create or close text, ntext, or image values from a text, ntext, or image column, as well as to manipulate other file related operating system handles such as file.

SqlBlobStream objects support random access to files using the Seek method. Seek allows the read/write position to be moved to any position within the file. This is done with byte offset reference point parameters. The byte offset is relative to the seek reference point, which can be the beginning, the current position, or the end of the underlying file, as represented by the three properties of the SeekOrigin class.

Example: Using the SqlBlobStream class

strConnectionString = "Data source=(local);Initial catalog=TestDB;User Id=sa;Password=";
            string strSqlTable = "Files";
            string strBLOBColumn = "Data";

            try
            {

            using(SqlBlobStream stream = new SqlBlobStream(strConnectionString,
            strSqlTable,
            strBLOBColumn,
            SqlBlobAccess.Read, new SqlParameter("@FileId", 12345)))
            {

            byte[] tmpBuffer = new byte[1024];
            int Length = 0;
            while ((Length = stream.Read(buffer, 0, 1024)) == 1024)
            {
            //Save tmpBuffer
            }
            }
            }
            catch (Exception ex)
            {

            System.Diagnostics.Trace.WriteLine(ex);
            throw;
            }