Over a million developers have joined DZone.

SQL Server Compact Code Snippet of the Week #15 : flush data to disk immediately

Evolve your approach to Application Performance Monitoring by adopting five best practices that are outlined and explored in this e-book, brought to you in partnership with BMC.

Under normal operation, SQL Server Compact keeps all pending disk writes in memory, and flushes them to disk at least every 10 seconds. The connection string property to control this is called “Flush Interval”, and valid values are between 1 and 1000 seconds. But you may want to flush to disk immediately under certain circumstances, and this weeks code snippet demonstrates how to do just that. This is possible via the CommitMode property on the SqlCeTransaction object Commit method, as demonstrated below:

using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\data\AdventureWorks.sdf;"))
{
    conn.Open();
    // Start a local transaction; SQL Server Compact supports the following
    // isolation levels: ReadCommitted, RepeatableRead, Serializable
    using (SqlCeTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))
    {
        using (SqlCeCommand cmd1 = conn.CreateCommand())
        {
            // To enlist a command in a transaction, set the Transaction property
            cmd1.Transaction = tx;
            try
            {
                cmd1.CommandText = "INSERT INTO FactSalesQuota " +
                    "(EmployeeKey, TimeKey, SalesAmountQuota) " +
                    "VALUES (2, 1158, 150000.00)";
                cmd1.ExecuteNonQuery();

                // Commit the changes to disk immediately, if everything above succeeded;
                // Use Deferred mode for optimal performance; the changes will
                // be flashed to disk within the timespan specified in the
                // ConnectionString 'FLUSH INTERVAL' property (default 10 seconds);
                //
                tx.Commit(CommitMode.Immediate);
            }

            catch (Exception)
            {
                tx.Rollback();
            }
        }
    }
}

 



Learn tips and best practices for optimizing your capacity management strategy with the Market Guide for Capacity Management, brought to you in partnership with BMC.

Topics:

Published at DZone with permission of Erik Ejlskov Jensen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}