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?

Domenico Barile

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

you should use something like

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

...

}
thePrisoner06
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.
Hi Its Me

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");
Paul Nolan
Try DBNull.Value
Divyachandra
GetTypeCode won't return byte[] because TypeCode Enumeration doesn't include byte[]. So TypeCode.Object is always returned if it's a byte array.
Mark Marsh

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.
jgrant

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.
AshishKSrivastava

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.
How to tell if a column in DataTable (ADO.NET) is mapping to "image" type in SQL Server?

Answer this question