Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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

Free Resource

Transform incident management with machine learning and analytics to help you maintain optimal performance and availability while keeping pace with the growing demands of digital business with this eBook, 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();
            }
        }
    }
}

 



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.

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.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}