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

· Performance Zone ·
Free Resource

xMatters delivers integration-driven collaboration that relays data between systems, while engaging the right people to proactively resolve issues. Read the Monitoring in a Connected Enterprise whitepaper and learn about 3 tools for resolving incidents quickly.

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

 



Discovering, responding to, and resolving incidents is a complex endeavor. Read this narrative to learn how you can do it quickly and effectively by connecting AppDynamics, Moogsoft and xMatters to create a monitoring toolchain.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}