Manipulating data in .NET -- dotnet ado.net

beschler
4/19/2007 12:40:00 PM

I have an ODBC data source that has EXTREMELY limited SQL functionality that
I want to expose via a web service.

For example, I need to SELECT DISTINCT and teh ODBC client does not support
the DISTINCT keyword. So, in the web service I am forced to retrieve ALL
records that match the WHERE clause.

Here is the relevant code I am using to retrieve the ODBC data and create
the dataset within the web service.
OdbcConnection oConn = new OdbcConnection();
DataSet ds = new DataSet();
DataTable jobsTable = new DataTable();
OdbcCommand cmd = new OdbcCommand();

//Define JobNos table in Dataset
jobsTable.TableName = "JobNos";
ds.Tables.Add(jobsTable);
jobsTable.Columns.Add("JobNo", typeof(string));

string szSQL;
szSQL = "SELECT Job_No,Job_Cnct_No FROM JC_JOBM WHERE JOB_STATUS = 'O'
ORDER BY Job_No";

oConn.ConnectionString = "DSN=" + pszDSN;
cmd.Connection = oConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = szSQL;

oConn.Open();
OdbcDataReader dr = cmd.ExecuteReader();
string szJobNo;
string szCnctNo;
string szTemp;
while (dr.Read())
{
szJobNo = dr[0].ToString();
szTemp = szJobNo.Substring(2, szJobNo.Length - 2);
jobsTable.Rows.Add(new object[] { szTemp });
}

return ds;

Note that I have removed some code within the loop that formats the job
number before it is added to the DS.

The problem is, I do not want to add the job number to the DS if it is
already there. However, as the list of job numbers grows, the time it takes
to check for each job number in the DS would get to be too long.

Is there a way to filter or order or something this DS after it is created
and populated?
Something like:
SELECT DISTINCT JobNo FROM ds?

Any ideas?
Thanks,
John

beschler
4/19/2007 1:28:01 PM

That will not work. Here's why:
There will be multiple variations of a jobno that will only differ in the
first 4 characters, like this:
1001ABCDE071111A111
1003ABCDE071111A111
1008ABCDE071111A111

I only need to retrieve the string starting in position 5 and I only need it
one time no matter how many times it appears in the data.
The ODBC client does not support any kind of string manipulation
(LEFT,RIGHT,SUBSTRING, etc.).

I only need part of the jobno

[quoted text, click to view]

Jim Rand
4/19/2007 4:14:26 PM

SELECT Job_No,Job_Cnct_No
FROM JC_JOBM
WHERE JOB_STATUS = 'O'
GROUP BY Job_No, Job_Cnct_No
ORDER BY Job_No;

Rad [Visual C# MVP]
4/20/2007 12:00:00 AM

[quoted text, click to view]

Given the constraints of your ODBC drivers, wouldn't it be better to store
the bits you're looking for separately?
--
Bits.Bytes

beschler
4/20/2007 5:54:03 AM

Rad wrote: "Given the constraints of your ODBC drivers, wouldn't it be better
to store the bits you're looking for separately?"

I'm not sure what you mean.

Here's exactly what I am doing currently:
I retrieve the record set from the ODBC provider using a datareader.
I loop through the RS.
For each jobno that matches my criteria, I extract the part of the string
that I need and populate the ds with that string.
Unfortunately, with the example I gave, that returns three identical records
in the DS and I only need one.
I could alter the SELECT in the ODBC driver to only pull in one of those
three "prefixes" i.e. "1001" however, they change every year of the contract
and might change more frequently depending on contract requirements.

What I would like to do is be able to retrieve all the records from the ODBC
driver (which is slow even by ODBC standards) into a local ds (or whatever)
and then filter (OR SELECT DISTINCT) the local ds (or whatever) and use that
to populate the ds that is passed in the web service.

One other thought just came to mind. What if I create a collection for the
JobNos? A collection, by it's nature will not allow duplicates, correct? As
I populate the collection, I could set the error handling to ignore those
duplicate errors and then iterate the collection to populate the ds.

Any help is appreciated.
Thanks,

[quoted text, click to view]

beschler
4/20/2007 6:46:01 AM

I got it!

I found this article: http://support.microsoft.com/kb/326176 in the
knowledge base. It tells how to create a DataSetHelper class to select
DISTINCT from a column in a table within a dataset. I modified the code to
work in my project and it does exactly what I want.
Thanks guys.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : Manipulating data in .NET