|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
fetching values returned by Stored Procedure by columnName and not Index...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 sugandh.j***@nirvana-sol.co.in 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. Show quote "Sugandh Jain" wrote: > 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 sugandh.j***@nirvana-sol.co.in or ping at jain_sugandh ( on yahoo messenger) > > Regards, > Sugandh > > > 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 Show quote "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote in message news:411F4529-7111-4D7C-A791-B8AB4316FBF8@microsoft.com... >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" wrote: > >> 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 sugandh.j***@nirvana-sol.co.in or ping at jain_sugandh ( on >> yahoo messenger) >> >> Regards, >> Sugandh >> >> >> 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 Show quote "Sugandh Jain" <sugandh.j***@nirvana-sol.com> wrote in message news:ubtkllPJHHA.4928@TK2MSFTNGP06.phx.gbl... > 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:411F4529-7111-4D7C-A791-B8AB4316FBF8@microsoft.com... >>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" wrote: >> >>> 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 sugandh.j***@nirvana-sol.co.in or ping at jain_sugandh >>> ( on yahoo messenger) >>> >>> Regards, >>> Sugandh >>> >>> >>> > > 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. Show quote "Sugandh Jain" wrote: > 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:411F4529-7111-4D7C-A791-B8AB4316FBF8@microsoft.com... > >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" wrote: > > > >> 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 sugandh.j***@nirvana-sol.co.in or ping at jain_sugandh ( on > >> yahoo messenger) > >> > >> Regards, > >> Sugandh > >> > >> > >> > > > 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 Show quote "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote in message news:721D96DE-81D9-48E9-950F-658D894C22AB@microsoft.com... > 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" wrote: > >> 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:411F4529-7111-4D7C-A791-B8AB4316FBF8@microsoft.com... >> >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" wrote: >> > >> >> 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 sugandh.j***@nirvana-sol.co.in or ping at jain_sugandh ( on >> >> yahoo messenger) >> >> >> >> Regards, >> >> Sugandh >> >> >> >> >> >> >> >> >> Thanks Francois I am doing it the way you suggested.
Regards, Sugandh Show quote "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote in message news:721D96DE-81D9-48E9-950F-658D894C22AB@microsoft.com... > 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" wrote: > >> 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:411F4529-7111-4D7C-A791-B8AB4316FBF8@microsoft.com... >> >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" wrote: >> > >> >> 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 sugandh.j***@nirvana-sol.co.in or ping at jain_sugandh ( on >> >> yahoo messenger) >> >> >> >> Regards, >> >> Sugandh >> >> >> >> >> >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics