Working with SQL records

// 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

You can load a record from a table by creating a new CustomTableRow  bject with primary key id. If the record is not found, an ObjectNotFoundException exception is thrown.

// 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.

See Filtering and Sorting for information about 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 CustomTableRow.GetReader and CustomTableRow.GetTable methods to get records in the inner .NET formats.

Deleting a row

To delete a row from a table, call the Delete method of the CustomTableRow object. The removal occurs when you call the Delete method.

// 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), the transaction in which the scope participates is allowed to proceed. If an exception occurs within the transaction scope, the transaction in which it participates is rolled back.

When your application completes work it wants to perform in a transaction, you should call the Commit method once to inform that transaction manager that it can 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.

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();
      }
  }

SqlBlobStream class

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

To reduce the amount of memory used when writing a BLOB value, a common practice 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, and 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.

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;
  }

Last updated: Jul 02, 2021