• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1956
  • Last Modified:

C# - Manipulating SqlDataReader Process

Dear Experts,

Do anyone know how to manipulate the process so I don't have to write all the fields manually?
I wonder if I can use loop on the dataReader by using dataReader.VisibleFieldCount and get the field name by dataReader.GetName().

as you can see the object name is the same as the dataReader's field name, but the case is not same.
The myTable field object using proper case and the dataReader field's name using camel case.

I hope my description are clear enough. Sorry my english is not very good since english is not my first language :).

Thanks you.

Kind Regards,
Aciang
System.Data.SqlClient.SqlDataReader dataReader = null;
 
dataReader = getDate()
 
while (dataReader.Read())
{
	myTable = new MyTable()
	
	myTable.CustNo = dataReader["custNo"];
	myTable.CustName = dataReader["custName"];
	mytable.CustAddress = dataReader["custAddress"];
	myTable.PhoneNo = dataReader["phoneNo"];
	...
	...
}

Open in new window

0
aciang
Asked:
aciang
  • 4
  • 4
  • 2
1 Solution
 
Pratima PharandeCommented:
You can use like this if you know the correct indexes of fields

myTable.CustNo = dataReader[0];               //First column value
	myTable.CustName = dataReader[1];     //2nd
	mytable.CustAddress = dataReader[2];
	myTable.PhoneNo = dataReader[3];

Open in new window

0
 
Pratima PharandeCommented:
Or you can try like this

For string fields

dataReader.GetString(0).ToString
dataReader.GetString(1).ToString

For int

dataReader.GetInt32(0)

and so on


0
 
aciangAuthor Commented:
Thank you for the answer 'Pratima'
is there another way that I don't have to write down all the MyTable properties like CustName, CustAddress, etc..
If I have 50 properties on MyTable, it means I have to write 50 statements.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gnoonCommented:
Sound like you need some classes in System.Reflection. Try this

Type t;
FieldInfo[] f;
while (dataReader.Read())
{
	myTable = new MyTable();
 
	t = n.GetType();
	f = t.GetFields(BindingFlags.Public|BindingFlags.Instance);
	for(int i=0; i<f.Length; i++)
	{
		f[i].SetValue(myTable, dataReader[f[i].Name]);
	}
}

Open in new window

0
 
gnoonCommented:
Sorry

t = n.GetType();

should be

t = myTable .GetType();
0
 
aciangAuthor Commented:
Thank you 'gnoon'
it almost solve my problem. I change the code like below.
myObject has more properties than dataReader's fields,
and I don't know how to validate if myObject property's name match with dataReader's fieldname. so for temporary I put try and catch to ignore the error because of the property name and dataReader's field is not match.

is it possible not to loop the MyObject properties?
Can we trigger the DataReader's field Name?

Thank you.

Type t;
PropertyInfo[] p;
 
while (dataReader.Read())
{
	myObject = new MyObject();
	t = myObject.GetType();
	p = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);
	for (int i = 0; i < p.Length; i++)
	{
		try
		{
			p[i].SetValue(myObject, dataReader[p[i].Name], null);
		}
		catch
		{
		}
	}
}

Open in new window

0
 
gnoonCommented:
>Can we trigger the DataReader's field Name?
Yes, read the name from schema table
Type t;
PropertyInfo p;
DataTable schemaTb = dataReader.GetSchemaTable();
 
while (dataReader.Read())
{
	myObject = new MyObject();
	t = myObject.GetType();
	foreach(DataRow row in schemaTb.Rows)
	{
		p = GetPropertyInfo(t, row("ColumnName"));
		if(p != null)
			p.SetValue(myObject, dataReader[p.Name], null);
	}
}
 
PropertyInfo GetPropertyInfo(Type t, string dbFieldName)
{
	return t.GetProperty(dbFieldName);
	// GetProperty accepts case-sensitive field name.
	// It may be different from database field.
	// So, you may need to search from array manually (to ignore case)
	// PropertyInfo[] p = t.GetProperties(BindingFlags.Public | BindingFlags.Instance);
	// loop to search for dbFieldName with p[i].Name
}

Open in new window

0
 
aciangAuthor Commented:
Sorry 'gnoon',
on this statement:
p = GetPropertyInfo(t, row("ColumnName"));
it returns error:
'row' is a 'variable' but is used like a 'method'

please kindly advise :).
0
 
gnoonCommented:
Sorry.. I did copy/paste from my VB. Change it to row["ColumnName"] ;-)
0
 
aciangAuthor Commented:
Excellent I change it a little to:
p = GetPropertyInfo(t, (string) row["ColumnName"]);

Thank you so much gnoon :).
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now