?
Solved

C# - Manipulating SqlDataReader Process

Posted on 2007-11-22
10
Medium Priority
?
1,949 Views
Last Modified: 2008-02-01
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
Comment
Question by:aciang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20337359
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 20337376
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
 

Author Comment

by:aciang
ID: 20337495
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 16

Expert Comment

by:gnoon
ID: 20337539
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
 
LVL 16

Expert Comment

by:gnoon
ID: 20337542
Sorry

t = n.GetType();

should be

t = myTable .GetType();
0
 

Author Comment

by:aciang
ID: 20337792
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
 
LVL 16

Accepted Solution

by:
gnoon earned 2000 total points
ID: 20337880
>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
 

Author Comment

by:aciang
ID: 20337961
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
 
LVL 16

Expert Comment

by:gnoon
ID: 20338045
Sorry.. I did copy/paste from my VB. Change it to row["ColumnName"] ;-)
0
 

Author Comment

by:aciang
ID: 20341527
Excellent I change it a little to:
p = GetPropertyInfo(t, (string) row["ColumnName"]);

Thank you so much gnoon :).
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question