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