Ad:

C# Discussion Thread
Marked Solved
Views: 1847

Nov 7th, 2009

Best way to handle column conversion for DataTable.Load

Expand Post »

Hello friends! I have some database tables that store Image/Bitmap as part of each record as a Byte[]. I also have method that loads all the tables records into a DataTable and I would like some suggestions on how I can get this Byte[] column in my DataTable to be of type
System.Drawing.Image after or during the load from the DbDataReader .

Thanks in advance!

C# Syntax (Toggle Plain Text)
        public DataTable SelectAll(string tableName, DbConnection conn)
        {
            DataTable dt = new DataTable();
 
            try
            {
                string query = "SELECT * FROM " + tableName;
 
                conn.Open();
 
                using (DbCommand cmd = GetDbCommand(query, conn))
                {
                    using (DbDataReader dr = cmd.ExecuteReader())
                    {
                        dt.Load(dr);
                    }
                }
            }
            catch (DbException ex)
            {
                Console.WriteLine("Exception: {0}\r\n   Stack Trace: {1}", ex.Message, ex.StackTrace);
                System.Diagnostics.Debugger.Break();
            }
            finally
            {
                conn.Close();
            }
 
            return dt;
        }
        public DataTable SelectAll(string tableName, DbConnection conn)
        {
            DataTable dt = new DataTable();

            try
            {
                string query = "SELECT * FROM " + tableName;

                conn.Open();

                using (DbCommand cmd = GetDbCommand(query, conn))
                {
                    using (DbDataReader dr = cmd.ExecuteReader())
                    {
                        dt.Load(dr);
                    }
                }
            }
            catch (DbException ex)
            {
                Console.WriteLine("Exception: {0}\r\n   Stack Trace: {1}", ex.Message, ex.StackTrace);
                System.Diagnostics.Debugger.Break();
            }
            finally
            {
                conn.Close();
            }

            return dt;
        }

Similar Threads

Visual C++ Debugging Problem in C++
How can I retrieve image from database? in C#
Reading Excel Document in C#
Problem with ComboBox DataGridView Column in VB.NET
Crystal Report shows only one column from the Data Table in C#

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Nov 7th, 2009

Re: Best way to handle column conversion for DataTable.Load

Just create a new column and move the data over to the desired data type.

I don't know of a one-liner to do this:

C# Syntax (Toggle Plain Text)
    private void button1_Click(object sender, EventArgs e)
    {
      DataTable dt = new DataTable();
      dt.Columns.Add(new DataColumn("RecordId", typeof(int)));
      dt.Columns.Add(new DataColumn("Picture", typeof(byte[])));
      {
        DataRow row = dt.NewRow();
        row["RecordId"] = 1;
        row["Picture"] = System.IO.File.ReadAllBytes(@"C:\picture.bmp");
        dt.Rows.Add(row);
        row = dt.NewRow();
        row["RecordId"] = 2;
        row["Picture"] = System.IO.File.ReadAllBytes(@"C:\picture2.bmp");
        dt.Rows.Add(row);
      }
 
      string colName = Guid.NewGuid().ToString();
      dt.Columns.Add(new DataColumn(colName, typeof(Image)));
      foreach (DataRow row in dt.Rows)
      {
        using (System.IO.MemoryStream ms = new System.IO.MemoryStream((byte[])row["Picture"]))
        {
          Image bmp = Bitmap.FromStream(ms);
          row.BeginEdit();
          row[colName] = bmp;
          row.EndEdit();
        }
      }
      dt.AcceptChanges();
      dt.Columns.Remove("Picture");
      dt.Columns[colName].ColumnName = "Picture";
      dt.AcceptChanges();
      System.Diagnostics.Debugger.Break();
    }
    private void button1_Click(object sender, EventArgs e)
    {
      DataTable dt = new DataTable();
      dt.Columns.Add(new DataColumn("RecordId", typeof(int)));
      dt.Columns.Add(new DataColumn("Picture", typeof(byte[])));
      {
        DataRow row = dt.NewRow();
        row["RecordId"] = 1;
        row["Picture"] = System.IO.File.ReadAllBytes(@"C:\picture.bmp");
        dt.Rows.Add(row);
        row = dt.NewRow();
        row["RecordId"] = 2;
        row["Picture"] = System.IO.File.ReadAllBytes(@"C:\picture2.bmp");
        dt.Rows.Add(row);
      }

      string colName = Guid.NewGuid().ToString();
      dt.Columns.Add(new DataColumn(colName, typeof(Image)));
      foreach (DataRow row in dt.Rows)
      {
        using (System.IO.MemoryStream ms = new System.IO.MemoryStream((byte[])row["Picture"]))
        {
          Image bmp = Bitmap.FromStream(ms);
          row.BeginEdit();
          row[colName] = bmp;
          row.EndEdit();
        }
      }
      dt.AcceptChanges();
      dt.Columns.Remove("Picture");
      dt.Columns[colName].ColumnName = "Picture";
      dt.AcceptChanges();
      System.Diagnostics.Debugger.Break();
    }

Last edited by sknake; Nov 7th, 2009 at 4:45 pm.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 8th, 2009

Re: Best way to handle column conversion for DataTable.Load

Thanks Scott, that works fine for what I'm doing.

Out of curiosity, do you know of a way or example of overriding the default behavior of the datatable's load from the db reader? I was thinking if I had a large table schema with several column types that cannot be directly translated to system types that I would want to consider a more efficient way of performing a load and conversion all-in-one.

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Nov 8th, 2009

Re: Best way to handle column conversion for DataTable.Load

You can use the .NET reflector to dig in to the sql classes to see how it works. I'm sure it parses the rows one by one so you could manually read the rows and convert the columns as you import the data instead of using datable.load(); . I personally try to do everything at the data access layer after the .NET framework loads it in to a datatable (like the earlier example). In the old days of ado data access did not work so hot -- and i'm reluctant to try expanding their current data access and cause problems for myself.

As far as efficiency goes it would probably be the same. If you're pulling a PDF or an image down from the SQL Server it is stored as a byte[] array on the server. On the client end you have to instantiate the container class and load the buffer. No matter where you perform that task you're going to take a performance hit.

What you could do that my code didn't do is set the picture byte[] column to null after the image is loaded. The way I wrote the code means you will use double the memory until the all of the conversion is done, then it will drop the byte buffers.

C# Syntax (Toggle Plain Text)
      foreach (DataRow row in dt.Rows)
      {
        using (System.IO.MemoryStream ms = new System.IO.MemoryStream((byte[])row["Picture"]))
        {
          Image bmp = Bitmap.FromStream(ms);
          row.BeginEdit();
          row[colName] = bmp;
          row["Picture"] = DBNull.Value; //new line, reclaim memory
          row.EndEdit();
        }
      }
      foreach (DataRow row in dt.Rows)
      {
        using (System.IO.MemoryStream ms = new System.IO.MemoryStream((byte[])row["Picture"]))
        {
          Image bmp = Bitmap.FromStream(ms);
          row.BeginEdit();
          row[colName] = bmp;
          row["Picture"] = DBNull.Value; //new line, reclaim memory
          row.EndEdit();
        }
      }

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 8th, 2009

Re: Best way to handle column conversion for DataTable.Load

You cannot change DataType of column if it has data already. Why not just add a column and remove a column than in this case?

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

Permalink

Nov 9th, 2009

Re: Best way to handle column conversion for DataTable.Load

Click to Expand / Collapse Quote originally posted by sknake ...

... I'm sure it parses the rows one by one so you could manually read the rows and convert the columns as you import the data instead of using datable.load(); .

...On the client end you have to instantiate the container class and load the buffer.

Thanks for the follow-up sknake. I'm going to mark this as solved, but was wondering if you could refer any examples related to the excerpted quotes above?

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

Permalink

Nov 10th, 2009

Re: Best way to handle column conversion for DataTable.Load

DdoubleD,

Watching the behavior of DTS in MSSQL I noticed that it submitted two queries -- the first fetches the schema only, the second starts fetching the data. It appears what they did is this:

C# Syntax (Toggle Plain Text)
    public DataTable QueryDataTableSchemaOnly(string query, List<DataParameter> lst)
    {
      DataTable result = null;
      using (DbConnection conn = GetDbConnection())
      {
        conn.Open();
        using (DbCommand cmd = GetDbCommand(query, lst, conn))
        {
          LogQuery(query, lst);
          using (DbDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }
    public DataTable QueryDataTableSchemaOnly(string query, List<DataParameter> lst)
    {
      DataTable result = null;
      using (DbConnection conn = GetDbConnection())
      {
        conn.Open();
        using (DbCommand cmd = GetDbCommand(query, lst, conn))
        {
          LogQuery(query, lst);
          using (DbDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
          {
            result = new DataTable();
            result.Load(dr);
          }
        }
        conn.Close();
      }
      return result;
    }

Now that you have the schema information you could re-run your query with executing a reader but instead of loading the reader directly in to a datatable you could read row-by-row and convert all of the values to the desired data type as you imported the rows. I have long suspected this is how the .NET framework works under the hood but I have never dug in to it.

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

Permalink

Nov 10th, 2009

Re: Best way to handle column conversion for DataTable.Load

Thanks--I'll look into this.

DdoubleD

Reputation Points: 341

Solved Threads: 233

Posting Shark

Offline

984 posts
since Jul 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in C# Forum Timeline: Urgent Help For handling File in C#

Next Thread in C# Forum Timeline: GIS

DaniWeb Message

Cancel Changes

User Name
Password