Archive for the ‘ADO.Net’ Category

Select Distinct in a DataTable (The Whidbey Version)

In my last post I described a “Select Distinct“ function for a datatable. This is a whole lot easier using Whidbey. You can get a distinct table based on a column in the DefaultView of the datatable:

DataTable d = dataSetName.dataTableName.DefaultView.ToTable(true, new string[] { "ColumnName" });

Posted on:
Posted in .Net 2.0, ADO.Net | Comments Off on Select Distinct in a DataTable (The Whidbey Version)

Select Distinct in a DataTable

I needed to perform a “Select Distinct“ on a datatable, not in the database, for binding to a listbox. Found a good solution here. Modified it according to one of the comments to use a hash table so sort order won’t matter. Here is my version of the function:

private DataTable SelectDistinct(DataTable sourceTable, string sourceColumn)
{
    DataTable result = null;
    try
   
{
        result =
new DataTable();
        result.Columns.Add(sourceColumn, sourceTable.Columns[sourceColumn].DataType);
       
Hashtable ht = new Hashtable();
       
foreach (DataRow dr in sourceTable.Rows)
        {
           
if (!ht.ContainsKey(dr[sourceColumn]))
            {
                ht.Add(dr[sourceColumn],
null);
               
DataRow newRow = result.NewRow();
                newRow[sourceColumn] = dr[sourceColumn];
                result.Rows.Add(newRow);
            }
        }
       
return result;
    }
   
catch (System.Exception ex)
    {
       
ExceptionManager.Publish(ex);
       
return null;
    }
   
finally
   
{
       
if (result != null)
            result.Dispose();
    }
}

Posted on:
Posted in ADO.Net | Comments Off on Select Distinct in a DataTable

Data Access Application Block v2 Bug

Mark Brown posted his fix for a bug in the FillDataSet method of the SqlHelper in the Data Access Application Block. The problem occurs when trying to use the FillDataSet method to fill a typed dataset that contains more than two tables. It works great for the first two, not at all for the subsequent tables.

I gave Marks\’s fix a try, but it still didn’t fix the problem for me. I did more digging and found a slightly different fix straight from the authors of the DAAB on the GotDotNet workspace for the DAAB. The fix is in the bug section (direct links don’t seem to work) This cleared up my problem right away. Of course, the bug is fixed in version 3+ of the DAAB. If you are like me, the MSDN site has been my source for the code for the application blocks, and of course there is only 2.0 on the site.

I did not realize they were on version 3 of the DAAB. The authors have added support for an abastract factory to make the SqlHelper ADO.Net provider independant. I don’t know about the rest of you, but the only projects I have ever had to change the database provider on have been Access upgrades. I have never had to move a database back end, say from SqlServer to Oracle. Is it more common than I realize?

Posted on:
Posted in .Net 2.0, ADO.Net | Comments Off on Data Access Application Block v2 Bug

Query the Indexing Service with Ixsso and ASP.Net

As I am continuing to migrate our ASP app to ASP.Net, it has finally come time to address the Indexing Service search. It’s a big feature in the application, and the transition of this piece needs to be seamless. We want to stick with Ixsso for the Indexing Service as opposed to using the Oledb driver. Ixsso is considered to be the faster of the two technologies, even using COM interop (See various of Hilary Cotter’s comments in microsoft.public.inetserver.indexserver). Code snippets for using Ixsso with ASP.Net are pretty sparse compared to using Oledb, so I figured I should post mine.  First, I used the IDE to create a reference to the ixsso Control Library dll and let the IDE make the .Net wrapper for the COM object (christened Cisso by the IDE).

Imports Cisso
Imports System.Security.Principal
Imports System.Data.OleDb

 

Private Function GetIndexResults(ByVal Query As String) As DataTable
Dim Q As New CissoQueryClass
Dim util As CissoUtilClass
Dim da As New OleDbDataAdapter
Dim ds As New DataSet(“IndexServerResults”)

Q.Query = Query
Q.SortBy = “rank[d]”
Q.Columns = “filename, rank, write”
Q.Catalog = “query://DocumentServer/Resumes”
Q.MaxRecords = 1000
util.AddScopeToQuery(Q, “\”, “deep”)
Q.LocaleID = util.ISOToLocaleID(“EN-US”)

Dim impContext As WindowsImpersonationContext = impersonateAnonymous()
da.Fill(ds, Q.CreateRecordset(“nonsequential”), “IndexServerResults”)

Q = Nothing
util = Nothing
impContext.Undo()

Return myDS.Tables(“IndexServerResults”)

End Function


The impersonateAnonymous function is described in a previous post of mine. In our case the anonymous user on the machine has appropriate privledges to query the remote Indexing Service, but the ASP.Net worker process does not so impersonation is in order for the function. That part is probably optional depending on the situation. The rest of it is not very tricky. I tried to fill the DataTable directly without the DataAdapter, but that didn’t work. The CreateRecordset function of the CissoQueryClass returns an ADO recordset and I couldn’t find a cast that worked. The DataAdapter seems to be doing the casting work during the call to Fill.

Temporary Tables in Oracle8i

Our application uses some fairly complex PL/SQL procedures to build reports in temporary tables so that we can access the data as a ref cursor and bind to a grid. We built the tables using “on commit delete rows” when creating the Oracle table. See DBASupport.com for a quick explanation of Oracle’s temporary tables. But calling commit inside the PL/SQL package did not delete the rows as implied by the Oracle documentation. The new data on subsequent calls simply added to what already existed in the table. We are using an Oracle Provider version of the Data Access Application Block, and called the stored procedure like this:

DataResults = Daab.ExecuteDataTable(CommandType.StoredProcedure, ProcName, ProcParametersArray)

To solve the problem, we wrapped the call to the stored procedure in a transaction, even though the procedure only issued select sql statements.

Dim FakeTransaction As OracleTransaction

FakeTransaction = Conn.BeginTransaction(IsolationLevel.Serializable)
DataResults = Daab.ExecuteDataTable(FakeTransaction, CommandType.StoredProcedure, ProcName, ProcParameters)
FakeTransaction.Rollback()

Oddly enough, calling .Commit() did not work as implied by the “on commit delete rows“ command added when creating the table. The data persisted in the table. Calling .Rollback() worked though, as I would expect for any transaction. I was unable to find documentation to tell if there is any perfomance drawback to using IsolationLevel.Serializable versus IsolationLevel.ReadCommitted or IsolationLevel.Unspecified as all three give the desired effect on the temporary table.

Posted on:
Posted in .Net 1.1, ADO.Net | Comments Off on Temporary Tables in Oracle8i

Data Access Application Block and Oracle

I read about the Data Application Block yesterday and was intrigued, although for the life of me I cannot remember where I read about it originally to pass along some credit. So I checked it out and it is very nice and all, but alas my project uses Oracle not SQL Server. Initially I thought about porting it to Oracle, but I figured that it has already been done. So I did a little Googling and voila!, Microsoft themselves had already done the work in the Nile 3.0 demo application. Sadly, it was in C# and my current project is using Oracle and VB.Net. So now I am back to porting.

Posted on:
Posted in .Net 1.1, ADO.Net, Oracle | Comments Off on Data Access Application Block and Oracle