fetching values returned by Stored Procedure by columnName and not Index...

  • Thread starter Thread starter Sugandh Jain
  • Start date Start date
S

Sugandh Jain

Hi,

We have a stored procedure, that returns the set of rows of companies...
Following is the query in it.

Select
PMCompanyID AS [COMPANYID]
, TC.NAME AS [COMPANYNAME]
, TC.ShortName AS [SHORTNAME]
From
PM_Company AS PMC
INNER JOIN T_Company TC ON PMC.NOMSCompanyID = TC.CompanyID
WHERE
PMC.IsActive = 1

And in our c#, code we use the following method to populate the corresponding CompanyNameID object.
In the method below, we are using the index of the column to set a particular property value of the company object.

company = new CompanyNameID();
int ID = offset + 0;
int fullName = offset + 1;
int ShortName = offset + 2;
company.ID = Convert.ToInt32(row[ID]);
company.FullName = Convert.ToString(row[fullName]);
company.ShortName = Convert.ToString(row[ShortName]);
return company;



So, now, if some one changes the order of columns in the select query, the code written above will go wrong.

So, I want to set the property values of company Object not by index of column in the query but by the column name of it.

How to do that? I u need more clarification on the question, please wirte to me at (e-mail address removed) or ping at jain_sugandh ( on yahoo messenger)

Regards,
Sugandh
 
I assume that you are looping throught the Rows in a DataTable. If tghis is
the case, use can just use Row["COMPANYNAME"] as it is accessable by index,
columnname, the column and various others.

Alternatively, if you use a DataReader, you can use the GetName up to the
fieldcount to make sure where your data is.
 
yes, I am looping through the rows, but we are using SqlDataReader
looping using a while loop ...

row used below is just an object array...

Regards,
Sugandh


FrancoisViljoen said:
I assume that you are looping throught the Rows in a DataTable. If tghis
is
the case, use can just use Row["COMPANYNAME"] as it is accessable by
index,
columnname, the column and various others.

Alternatively, if you use a DataReader, you can use the GetName up to the
fieldcount to make sure where your data is.

Sugandh Jain said:
Hi,

We have a stored procedure, that returns the set of rows of companies...
Following is the query in it.

Select
PMCompanyID AS [COMPANYID]
, TC.NAME AS [COMPANYNAME]
, TC.ShortName AS [SHORTNAME]
From
PM_Company AS PMC
INNER JOIN T_Company TC ON PMC.NOMSCompanyID = TC.CompanyID
WHERE
PMC.IsActive = 1

And in our c#, code we use the following method to populate the
corresponding CompanyNameID object.
In the method below, we are using the index of the column to set a
particular property value of the company object.

company = new CompanyNameID();
int ID = offset + 0;
int fullName = offset + 1;
int ShortName = offset + 2;
company.ID = Convert.ToInt32(row[ID]);
company.FullName = Convert.ToString(row[fullName]);
company.ShortName = Convert.ToString(row[ShortName]);
return company;



So, now, if some one changes the order of columns in the select query,
the code written above will go wrong.

So, I want to set the property values of company Object not by index of
column in the query but by the column name of it.

How to do that? I u need more clarification on the question, please wirte
to me at (e-mail address removed) or ping at jain_sugandh ( on
yahoo messenger)

Regards,
Sugandh
Click to expand...
Click to expand...
 
This is what is done to loop through the records,
FillCompanyNameID(object[] row, int offset)
is the method which returns the companyNameId object populated.
Here, can i directly create a company Object and use it the way you have
suggested?

while (reader.Read())
{
object[] row = new object[reader.FieldCount];
reader.GetValues(row);
companies.Add(FillCompanyNameID(row, 0));
}

Regards,
Sugandh

Sugandh Jain said:
yes, I am looping through the rows, but we are using SqlDataReader
looping using a while loop ...

row used below is just an object array...

Regards,
Sugandh


FrancoisViljoen said:
I assume that you are looping throught the Rows in a DataTable. If tghis
is
the case, use can just use Row["COMPANYNAME"] as it is accessable by
index,
columnname, the column and various others.

Alternatively, if you use a DataReader, you can use the GetName up to
the
fieldcount to make sure where your data is.

Sugandh Jain said:
Hi,

We have a stored procedure, that returns the set of rows of companies...
Following is the query in it.

Select
PMCompanyID AS [COMPANYID]
, TC.NAME AS [COMPANYNAME]
, TC.ShortName AS [SHORTNAME]
From
PM_Company AS PMC
INNER JOIN T_Company TC ON PMC.NOMSCompanyID = TC.CompanyID
WHERE
PMC.IsActive = 1

And in our c#, code we use the following method to populate the
corresponding CompanyNameID object.
In the method below, we are using the index of the column to set a
particular property value of the company object.

company = new CompanyNameID();
int ID = offset + 0;
int fullName = offset + 1;
int ShortName = offset + 2;
company.ID = Convert.ToInt32(row[ID]);
company.FullName = Convert.ToString(row[fullName]);
company.ShortName = Convert.ToString(row[ShortName]);
return company;



So, now, if some one changes the order of columns in the select query,
the code written above will go wrong.

So, I want to set the property values of company Object not by index of
column in the query but by the column name of it.

How to do that? I u need more clarification on the question, please
wirte to me at (e-mail address removed) or ping at jain_sugandh
( on yahoo messenger)

Regards,
Sugandh
Click to expand...
Click to expand...
Click to expand...
 
I found a reader.GetSchemaTable() just now in the list of reader properties,
i think this will solve the problem.
let me try that too..

Thanks a ton...

Regards,
Sugandh



FrancoisViljoen said:
OK, you can use a Dictionary<string, int> to store locations of fields.
Use
a for loop to go from 0 to reader.fieldcount-1 and add to the dictionary
the
result of getName(I), and then I.

Then access the values using the following: row[dictionary["COMPANYNAME"]]

If the order of the fields then changes, you'll be OK as it is not hard
coded, but derived from the reader.

A dictionary is also very fast, so no real speed penalty.

Sugandh Jain said:
yes, I am looping through the rows, but we are using SqlDataReader
looping using a while loop ...

row used below is just an object array...

Regards,
Sugandh


"FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote
in
message news:[email protected]...
I assume that you are looping throught the Rows in a DataTable. If
tghis
is
the case, use can just use Row["COMPANYNAME"] as it is accessable by
index,
columnname, the column and various others.

Alternatively, if you use a DataReader, you can use the GetName up to
the
fieldcount to make sure where your data is.

:

Hi,

We have a stored procedure, that returns the set of rows of
companies...
Following is the query in it.

Select
PMCompanyID AS [COMPANYID]
, TC.NAME AS [COMPANYNAME]
, TC.ShortName AS [SHORTNAME]
From
PM_Company AS PMC
INNER JOIN T_Company TC ON PMC.NOMSCompanyID = TC.CompanyID
WHERE
PMC.IsActive = 1

And in our c#, code we use the following method to populate the
corresponding CompanyNameID object.
In the method below, we are using the index of the column to set a
particular property value of the company object.

company = new CompanyNameID();
int ID = offset + 0;
int fullName = offset + 1;
int ShortName = offset + 2;
company.ID = Convert.ToInt32(row[ID]);
company.FullName = Convert.ToString(row[fullName]);
company.ShortName = Convert.ToString(row[ShortName]);
return company;



So, now, if some one changes the order of columns in the select query,
the code written above will go wrong.

So, I want to set the property values of company Object not by index
of
column in the query but by the column name of it.

How to do that? I u need more clarification on the question, please
wirte
to me at (e-mail address removed) or ping at jain_sugandh ( on
yahoo messenger)

Regards,
Sugandh
Click to expand...
Click to expand...
Click to expand...
 
Thanks Francois I am doing it the way you suggested.

Regards,
Sugandh

FrancoisViljoen said:
OK, you can use a Dictionary<string, int> to store locations of fields.
Use
a for loop to go from 0 to reader.fieldcount-1 and add to the dictionary
the
result of getName(I), and then I.

Then access the values using the following: row[dictionary["COMPANYNAME"]]

If the order of the fields then changes, you'll be OK as it is not hard
coded, but derived from the reader.

A dictionary is also very fast, so no real speed penalty.

Sugandh Jain said:
yes, I am looping through the rows, but we are using SqlDataReader
looping using a while loop ...

row used below is just an object array...

Regards,
Sugandh


"FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote
in
message news:[email protected]...
I assume that you are looping throught the Rows in a DataTable. If
tghis
is
the case, use can just use Row["COMPANYNAME"] as it is accessable by
index,
columnname, the column and various others.

Alternatively, if you use a DataReader, you can use the GetName up to
the
fieldcount to make sure where your data is.

:

Hi,

We have a stored procedure, that returns the set of rows of
companies...
Following is the query in it.

Select
PMCompanyID AS [COMPANYID]
, TC.NAME AS [COMPANYNAME]
, TC.ShortName AS [SHORTNAME]
From
PM_Company AS PMC
INNER JOIN T_Company TC ON PMC.NOMSCompanyID = TC.CompanyID
WHERE
PMC.IsActive = 1

And in our c#, code we use the following method to populate the
corresponding CompanyNameID object.
In the method below, we are using the index of the column to set a
particular property value of the company object.

company = new CompanyNameID();
int ID = offset + 0;
int fullName = offset + 1;
int ShortName = offset + 2;
company.ID = Convert.ToInt32(row[ID]);
company.FullName = Convert.ToString(row[fullName]);
company.ShortName = Convert.ToString(row[ShortName]);
return company;



So, now, if some one changes the order of columns in the select query,
the code written above will go wrong.

So, I want to set the property values of company Object not by index
of
column in the query but by the column name of it.

How to do that? I u need more clarification on the question, please
wirte
to me at (e-mail address removed) or ping at jain_sugandh ( on
yahoo messenger)

Regards,
Sugandh
Click to expand...
Click to expand...
Click to expand...
 
Back
Top