Use OLEDB to read AccessFile from Stream to DataSet

Question

I Use Oledb to read an AccessFile(.accdb) to DataSet, I don't know about table names or columns, The regular implementation is:

public void GetAccessDB(string filepath){

this.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + filepath;
// get Table Names
this.TableNames = new List<string>();
using (System.Data.OleDb.OleDbConnection oledbConnection = new System.Data.OleDb.OleDbConnection(this.ConnectionString))
{
oledbConnection.Open();
System.Data.DataTable dt = null;
dt = oledbConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
foreach (System.Data.DataRow row in dt.Rows)
{
  string strSheetTableName = row["TABLE_NAME"].ToString();
  if (row["TABLE_TYPE"].ToString() == "TABLE")
     this.TableNames.Add(strSheetTableName);
}
oledbConnection.Close();
}


this.Dataset = new System.Data.DataSet();
using (System.Data.OleDb.OleDbConnection oledbConnection = new System.Data.OleDb.OleDbConnection(this.ConnectionString))
{
  foreach (string table in this.TableNames)
  {
    string command = string.Format("SELECT * FROM {0};", table);
    using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(command, oledbConnection))
    {
      cmd.CommandType = System.Data.CommandType.Text;
      oledbConnection.Open();
      System.Data.OleDb.OleDbDataReader dr = cmd.ExecuteReader();
      this.Dataset.Load(dr, System.Data.LoadOption.OverwriteChanges, table);
      oledbConnection.Close();
    }
  }
}
}

But I need to get Access File from Stream, And I can't Write it on the Disk temporary, so what is your suggestion?

I need This overload of GetAccessDB(Stream AccessFile)? I search and find This, but that's not clear for me, I need finally get DataSet by all Tables in Access File.

Does any one know about this?

Cordula

Why would you want to do data operations in an Access file opened as a stream? Just curious. – Hara

@AlexFilipovici The Clients Upload Access File And i need Read them and Find Target Datas and stored on SQL and Show a Table of Target Datas to Client, but I Can't(some limitation) Write the Files to Disk! – Cordula

how come you cannot do the work on local disk..? please explain your reasoning better so that others can understand and not lead you down the golden path as they say – Til

Is the database stored on MS SQL Server? If yes, which version and do you have any control over it? – Hara

@AlexFilipovici SQL Server 2008, And yes I have any control. – Cordula

Answer 1

If you have control over the MS SQL Server, that's good news. I currently see 2 alternatives:

1. Create a CLR asssembly that will process (asynchronously is a good idea) the file once the insert is made in the uploaded files table. It would create a temporary MS Access file on the server by using the content of the uploaded file. Then, open it with OleDB, parse it and insert the information from it in a SQL table which maps the extracted information with the uploaded file record in the first table. Then, you could go and look for the data in this second table.

2. Another option would be to send to the SQL a command which will do the following:

   1. Use the uploaded file bytes to create a file on the filesystem.
   2. Then, use the file as a linked server
   3. Use SELECT to query the Access database

You may have noticed that both options involve creating a (at least temporary) file on the SQL Server.

Hara

Answer 2

I don't know any api function in OleDb for work with in-memory databases. Maybe, could you install a RAMDisk?

Trella