How to Open a CSV or XLS with Jet OLEDB and attain Table Lock?

I am trying to figure out how to read/write lock a CSV or XLS file when I read it as a Database via Jet OLEDB.

The following code will open a CSV as a DB and load it into a DataTable object:

        private DataTable OpenCSVasDB(string fullFileName)
        {
           string file = Path.GetFileName(fullFileName);
           string dir = Path.GetDirectoryName(fullFileName);
           string cStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
               +开发者_运维知识库 "Data Source=\"" + dir + "\\\";"
               + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";
           string sqlStr = "SELECT * FROM [" + file + "]";
           OleDbDataAdapter da;
           DataTable dt = new DataTable();
           try
           {
               da = new OleDbDataAdapter(sqlStr, cStr);
               da.Fill(dt);
           }
           catch { dt = null; }
        }

What I want to make sure of is that while I have the CSV or XLS file open, that I have a Read/Write LOCK on the Table (aka. the file), so that any other application that comes along and tries to read/write to this file has to wait its turn.

Does this happen automatically? If not, what do I need to do to make sure this does happen?

Btw, I'm working in C#/.NET 2.0, if that makes any difference...

Update: So, I'm clarifying my requirements now:

• XLS file (because I need SELECT and UPDATE functionality) [CSV can only SELECT and INSERT]
• LOCK the XLS file while the DB is Open. (can't have multiple threads and/or processes stepping on each other's changes...)
• Read into DataTable object (for ease of working)

OLEDB's Jet driver locks flat files while there's an open OleDbDataReader to them. To verify this, look at the VerifyFileLockedByOleDB method in the code sample below. Note that having an open OleDbConnection is not enough-- you have to have an open Reader.

That said, your code posted above does not keep an open connection, since it uses OleDbDataAdapter.Fill() to quickly connect to the data source, suck out all the data, and then disconnect. The reader is never left open. The file is only locked for the (short) time that Fill() is running.

Furthermore, even if you open the reader yourself and pass it into DataTable.Load(), that method will close your DataReader for you once it's done, meaning that the file gets unlocked.

So if you really want to keep the file locked and still use a DataTable, you'll need to manually populate the datatable (schema and rows!) from an IDataReader, instead of relying on DataAdapter.Fill() or DataTable.Load().

Anyway, here's a code sample which shows:

• your original code
• an example which won't work because DataTable.Load() will close the DataReader and unlock the file
• an alternate approach which will keep the file locked while you're working with the data, via operating at the row level using DataReader rather than using a DataTable

UPDATE: looks like keeping a DataReader open will prevent the same process from opening the file, but another process (e.g. Excel) can open (and write to!) the file. Go figure. Anyway, at this point I'd suggest, if you really want to keep the file locked, consider using something else besides OLEDB where you have more fine-grained control over how (adn when!) the file is opened and closed. I'd suggest the CSV reader fromhttp://www.codeproject.com/KB/database/CsvReader.aspx, which is well-tested and fast, but will give you the source code so if you need to change file-locking/opening/closing, you can do so.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace TextFileLocking
{
    class Program
    {
        private static DataTable OpenCSVasDB(string fullFileName)
        {
            string file = Path.GetFileName(fullFileName);
            string dir = Path.GetDirectoryName(fullFileName);
            string cStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=\"" + dir + "\\\";"
                + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";
            string sqlStr = "SELECT * FROM [" + file + "]";
            OleDbDataAdapter da;
            DataTable dt = new DataTable();
            try
            {
                da = new OleDbDataAdapter(sqlStr, cStr);
                da.Fill(dt);
            }
            catch { dt = null; }

            return dt;
        }
        private static DataTable OpenCSVasDBWithLockWontWork(string fullFileName, out OleDbDataReader reader)
        {
            string file = Path.GetFileName(fullFileName);
            string dir = Path.GetDirectoryName(fullFileName);
            string cStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=\"" + dir + "\\\";"
                + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";
            string sqlStr = "SELECT * FROM [" + file + "]";
            OleDbConnection openConnection = new OleDbConnection(cStr);
            reader = null;
            DataTable dt = new DataTable();
            try
            {
                openConnection.Open();
                OleDbCommand cmd = new OleDbCommand(sqlStr, openConnection);
                reader = cmd.ExecuteReader();
                dt.Load (reader);       // this will close the reader and unlock the file!
                return dt;  
            }
            catch 
            { 
                return null; 
            }
        }
        private static void OpenCSVasDBWithLock(string fullFileName, Action<IDataReader> dataRowProcessor)
        {
            string file = Path.GetFileName(fullFileName);
            string dir = Path.GetDirectoryName(fullFileName);
            string cStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=\"" + dir + "\\\";"
                + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";
            string sqlStr = "SELECT * FROM [" + file + "]";
            using (OleDbConnection conn = new OleDbConnection(cStr))
            {
                OleDbCommand cmd = new OleDbCommand(sqlStr, conn);
                conn.Open();
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        dataRowProcessor(reader);
                    }
                }
            }
        }
        private static void VerifyFileLockedByOleDB(string fullFileName)
        {
            string file = Path.GetFileName(fullFileName);
            string dir = Path.GetDirectoryName(fullFileName);
            string cStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=\"" + dir + "\\\";"
                + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";";
            string sqlStr = "SELECT * FROM [" + file + "]";
            using (OleDbConnection conn = new OleDbConnection(cStr))
            {
                OleDbCommand cmd = new OleDbCommand(sqlStr, conn);
                conn.Open();
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    File.OpenRead(fullFileName);   // should throw an exception

                    while (reader.Read())
                    {
                        File.OpenRead(fullFileName);   // should throw an exception

                        StringBuilder b = new StringBuilder();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            b.Append(reader.GetValue(i));
                            b.Append(",");
                        }
                        string line = b.ToString().Substring(0, b.Length - 1);
                        Console.WriteLine(line);
                    }
                }
            }
        }

        static void Main(string[] args)
        {
            string filename = Directory.GetCurrentDirectory() + "\\SomeText.CSV";
            try
            {
                VerifyFileLockedByOleDB(filename);
            }
            catch { }   // ignore exception due to locked file

            OpenCSVasDBWithLock(filename, delegate(IDataReader row)
            {
                StringBuilder b = new StringBuilder();
                for (int i = 0; i <row.FieldCount; i++)
                {
                    b.Append(row[i].ToString());
                    b.Append(",");
                }
                string line = b.ToString().Substring(0, b.Length - 1);
                Console.WriteLine(line);
            });

        }
    }
}

UPDATE: The following does not appear to lock my DB as I had hoped...

After much more digging, I found this page:

ADO Provider Properties and Settings

It says:

Jet OLEDB:Database Locking Mode

A Long value (read/write) that specifies the mode used when locking the database to read or modify records.

The Jet OLEDB:Database Locking Mode property can be set to any of the following values:

Page-level Locking 0

Row-level Locking 1

Note A database can only be open in one mode at a time. The first user to open the database determines the locking mode to be used while the database is open.

So I assume that my code would get changed to:

       string cStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
           + "Data Source=\"" + dir + "\\\";"
           + "Extended Properties=\"text;HDR=Yes;FMT=Delimited\";"
           + "Jet OLEDB:Database Locking Mode=0";

Which should give me Page-level locking. If I wanted Row-level locking, I'd switch the value to 1.

Unfortunately, this doesn't actually appear to do any table/row/page locking when opening a CSV file as a DB.

Ok, so that new function you wrote kinda works, but I still end up with a "Race condition" which then causes an exception to be thrown. So in this section of the code:

        using (OleDbConnection conn = new OleDbConnection(cStr))
        {
            OleDbCommand cmd = new OleDbCommand(sqlStr, conn);
            conn.Open();
            using (OleDbDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    reader.GetString(0);  // breakpoint here
                }
            }
        }

I put a breakpoint on the line with the comment "breakpoint here" and then ran the program. I then located the CSV file in File Explorer and tried to open it with Excel. It causes Excel to wait for the file to be unlocked, which is good.

But here's the bad part. When I clear the breakpoint and then tell it to continue debugging, Excel sneaks in, grabs a lock on the file and causes an exception in my running code.

(The exception is: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.)

I guess I can always wrap that code in a try-catch block, but when the exception occurs, I won't know if it is a legitimate exception or one caused by this weird condition.

The exception seems to occur when the Reader is finished reading. (after it reads the last row, but still is in the "using (OleDbDataReader reader = cmd.ExecuteReader())" loop.