ADO to ADO.NET questions.

[Dinko Deranja]

I have a few questions regarding ADO to ADO.NET code conversion. I have
Win2000, SQL Server 2000 and ASP.NET Premium:

1. I am storing a few small and simple tables in an Application array
variable (ASP) in global.asa Application_OnStart using the getrows method of
ADO recordset:
Application("cities") = rsCities.GetRows
What is the appropriate way to do it in ADO.NET (I think I saw someting
about storing a DataView in an App.var)?

2. When I want to do the SELECT and get the recordcount, I use the
following:
strSQL = "SELECT COUNT(*) FROM tbl1 WHERE..."
strSQL = strSQL & ";" & Replace(strSQL, "COUNT(*)", "*")
Set rs1 = objConn.Execute (strSQL)
Response.Write rs1(0)
Set rs1 = rs1.NextRecordset
...
How to do it in ADO.NET.

3. How can I browse and get help/examples on all the methods and properties
of ADO.NET (like I did with ADO using the Object Browser in VS)?

Thanks in advance!
Dinko Deranja, B.Sc.

[David Sceppa]

Dinko,

There is no direct equivalent to the GetRows method in this
release of ADO.NET. There is a GetValues method on the
DataReader object that will store the contents of the current row
into an object array. You could make repeated calls to that
method to achieve similar functionality.

You can still use a batch query to issue the SELECT COUNT
query prior to the actual query. Use the DataReader's NextResult
method to move to the next result set.

You can still use the object browser in Visual Studio .NET.
I believe it's available on the Other Windows submenu off of View.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2001 Microsoft Corporation. All rights reserved.

[Dinko Deranja]

Thanks for your answers David, I included further comments bellow...

"David Sceppa" <sc...@nospam.com> wrote in message
news:HDX6#ZwlBHA.2048@cpmsftngxa07...

> Dinko,
>
> There is no direct equivalent to the GetRows method in this
> release of ADO.NET. There is a GetValues method on the
> DataReader object that will store the contents of the current row
> into an object array. You could make repeated calls to that
> method to achieve similar functionality.

OK, I'll look into it. What I need is the same functionality - to minimize
constant reading of the small, static tables from db server. GetRows method
was the excellent solution for that.

> You can still use a batch query to issue the SELECT COUNT
> query prior to the actual query. Use the DataReader's NextResult
> method to move to the next result set.
>
> You can still use the object browser in Visual Studio .NET.
> I believe it's available on the Other Windows submenu off of View.

I thought that maybe it can be seen from VS6.0 (with .NET framework
installed).

Dinko Deranja, B.Sc.

[Dinko Deranja]

David, can you please help me with the following code. In my global.asa i
have:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>

Sub Application_OnStart

Application("Telefoni_ConnectionString") = "Provider=SQLOLEDB.1;Persist
Security Info=True;Initial Catalog=glavna;Data Source=strah.ot.hr;Use
Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;General
Timeout=0;Workstation ID=trepet;User ID=citac"

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open Application("Telefoni_ConnectionString")

Set rsNomen = objConnection.Execute("SELECT Sifra, Naziv FROM Nom_jed")
If Not rsNomen.EOF Then Application("Jedinice") = rsNomen.GetRows()
rsNomen.Close
Set rsNomen.ActiveConnection = nothing
End Sub
</SCRIPT>

Then, I started to write the following global.asax:

<%@ Application Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="VB" runat="server">

Sub Application_Start(Sender As Object, E As EventArgs)
Application("Telefoni_ConnectionString") = "Data Source=strah.ot.hr;Initial
Catalog=glavna; UID=citac"

Dim objConn As New SqlConnection(Application("Telefoni_ConnectionString"))
objConn.Open()

Dim objCmd As New SqlCommand("SELECT Sifra, Naziv FROM Nom_jed", objConn)
Dim objDR As SqlDataReader = objCmd.ExecuteReader()

objConn.Close()
End Sub

Now, I'm stuck. What should I write next to achieve the same as in above
global.asa?

Thanks in advance!
Dinko Deranja, B.Sc.

"David Sceppa" <sc...@nospam.com> wrote in message
news:HDX6#ZwlBHA.2048@cpmsftngxa07...

[David Sceppa]

The following code snippets retrieve data into an ArrayList
object to provide functionality similar to GetRows. The code
reads the contents of the DataReader into an ArrayList and then
examines one of the values in the ArrayList.

C#:

string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT CustomerID, CompanyName FROM Customers";
OleDbConnection cn = new OleDbConnection(strConn);
cn.Open();
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbDataReader rdr = cmd.ExecuteReader();
ArrayList myResults = new ArrayList();
object[] objOneRow;
while (rdr.Read())
{
objOneRow = (object[]) Array.CreateInstance(typeof(object),
rdr.FieldCount);
rdr.GetValues(objOneRow);
myResults.Add(objOneRow);
}
rdr.Close();
cn.Close();
objOneRow = (object[]) myResults[7];
Console.WriteLine(objOneRow[1].ToString());

VB:

Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT CustomerID, CompanyName FROM Customers"
Dim cn As New OleDbConnection(strConn)
cn.Open()
Dim cmd As New OleDbCommand(strSQL, cn)
Dim rdr As OleDbDataReader = cmd.ExecuteReader()
Dim myResults As New ArrayList()
Dim objOneRow As Object()
Do While rdr.Read()
objOneRow = Array.CreateInstance(GetType(Object),
rdr.FieldCount)
rdr.GetValues(objOneRow)
myResults.Add(objOneRow)
Loop
rdr.Close()
cn.Close()
objOneRow = CType(myResults(7), Object())
Console.WriteLine(objOneRow(1).ToString())

As far as I know, you cannot use the Object Browser in
Visual Basic 6.0 to view the .NET object model.

[Andy Baron]

Dinko,

You may find it more efficient (and easier) to store a DataTable
directly in Application. The only negative consequence of this is that
it would occupy a bit more memory (and contain more information).

You should also take a look at ASP.NET's ability to store data in the
Cache object. This gives you a lot of good control over when the data
expires (and the ability to replace it as needed), instead of having
it sit there for the lifetime of your application.

-- Andy

[Dinko Deranja]

Thanks David, the informatin was very helpful. I didn't know that a simple
one-line statement has to be replaced with 8 of them (?!). The question is -
what am I storing in that array? It looks to me like an array of objects,
which, I'm affraid, is not very resource friendly, unlike the array of
strings which was a result of the old GetRows method.

Any comments?
Dinko Deranja, B.Sc.

"David Sceppa" <sc...@nospam.com> wrote in message

news:nOqvTW8lBHA.1752@cpmsftngxa07...

>
> The following code snippets retrieve data into an ArrayList
> object to provide functionality similar to GetRows. The code
> reads the contents of the DataReader into an ArrayList and then
> examines one of the values in the ArrayList.
>

[Dinko Deranja]

Thanks Andy!

I just started to write code in ASP.NET, so, for now, I don't know how to
reference a DataTable, is it possible to get it from the DataReader or
should I open a DataSet?

Regarding Cache object, I haven't come to it yet, but I know for sure that
the data I am saving in the application variables must be accessible for the
lifetime of the application. It is a static data that rarely or never
changes (e.g. a list of USA States).

Dinko Deranja, B.Sc.

"Andy Baron" <Andy_...@msn.com> wrote in message
news:2grk3ugocnks7iak7...@4ax.com...

[Andy Baron]

The easiest way to fill a DataTable is by using the Fill method of a
DataAdapter. DataSets can contain DataTable objects or you can work
with free-standing DataTable objects apart from any DataSet.

[David Sceppa]

Dinko,

GetRows returns a two-dimensional variant array. The
replacement for the variant data type in the .NET world is
object. You're still storing simple data types.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.

© 2002 Microsoft Corporation. All rights reserved.