How to tell if a column in DataTable (ADO.NET) is mapping to "image" type in SQL Server?

Hi, everyone.

I am using .net framework 1.1, VS studio 2003 and MSSQL Server 2000.

I tried to write codes to clear all the image data in several tables which are stored in a dataset named myDataSet. As I know, the image type in MS Sql Databse is maped to Byte[] in .Net. I used function GetTypeCode() to compare the column type with the standard Byte[], but it doesn't work.

Any idea thanks.

Following is my code:

byte[] bt = new byte[100]

for(int j=0; j<myDataSet.Tables.Count;j++)
{
       for(int k=0; k<myDataSet.Tables[j].Rows.Count; k++)
       {
              DataRow r = myDataSet.Tables[j].Rows[k];
              for(int m=0; m < r.ItemArray.Length; m++)
             {
                  if(System.Type.GetTypeCode(r.ItemArray[m].GetType())== System.Type.GetTypeCode(bt.GetType())
                   {
                        r.ItemArray[m] = null;
                   }
             }
       }
  }

Answer this question

How to tell if a column in DataTable (ADO.NET) is mapping to "image" type in SQL Server?

Ramon de Klein

System.Type.GetTypeCode(r.ItemArray[m].GetType()) retuns 'object'

you should use something like

if (r.ItemArray[m] is byte[]) {

...

}
Jhan Zaib

DBNull.Value doesn't work, too.

Following are my complete codes. When I debug, I did see the set value code (r.ItemArray[m]= System.DBNull.Value;) was executed. But "myDataSet.HasChanges()" returned false. Why Did I do something wrong Thanks.

DataSet myDataSet = new DataSet();

String cmd = string.Format("SELECT * FROM table1");
OleDbCommand myCommand = new OleDbCommand(cmd,oleDbConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myCommand);
myDataAdapter.Fill(myDataSet, "table1");

//add more tabes here into myDataSet

for(int j=0; j<myDataSet.Tables.Count;j++)
{
for(int k=0; k<myDataSet.Tables[j].Rows.Count; k++)
{
DataRow r = myDataSet.Tables[j].Rows[k];
for(int m=0;m < r.ItemArray.Length; m++)
{
if(r.ItemArray[m] is byte[])
{
r.ItemArray[m]= System.DBNull.Value;
}
}
}
}
// Export to xml file
if(myDataSet.HasChanges())
{
myDataSet.AcceptChanges();
}

myDataSet.WriteXml(fileName);
String[] s = fileName.Split('.');
string schemaFileName = s[0];
myDataSet.WriteXmlSchema(schemaFileName + ".xsd");
David Szarenski

Thank you, Bill.

Just found the getTypeCode worked OK. Of course, your code is very neat.

It seems the code "r.ItemArray[m] = null" doesn't work. The image data in the filed is not cleared. Any idea of this one thanks.
n3hgn
Try DBNull.Value
PGamblin
You are right. Now it works fine. Sorry for my stupid mistake. Since itemArray is only a "copy" , I do think compiler should give a warning or error for writing operation. Just my 2 cents. Thanks a lot, Bill.
Chikku

You're welcome, lulu2005.

The intent of exposing ItemArray property is for users to assign an array of objects to the whole row.

So dataRow.ItemArray = new Object[] {..., ..., ..} will update the dataset.
Eamon Nerbonne
GetTypeCode won't return byte[] because TypeCode Enumeration doesn't include byte[]. So TypeCode.Object is always returned if it's a byte array.
RCD

You have to use r[m] = DBNull.Value to set a field of a row.

ItemArray only gives you an array of objects, which is a copy of the values of a datarow.
How to tell if a column in DataTable (ADO.NET) is mapping to "image" type in SQL Server?

Answer this question