Hello there!



I'm having my first go with displaying some data from a table (using SQL

Server 7.0) in a command line interface. However, having spent about 2 hours

reading about datasets, datareaders and so forth (which is pretty complex,

imho) I have to realize there's no solution in sight for today.



Therefore, could you please help me to understand how the correct

data-retrieval operation is supposed to work. Basically, I'm looking for

some commands which will allow me to do the same as the original older ADO

COM objects did (which, in ASP was pretty easy to use).



Like the ADO Recordset object for instance. Which class in the ADO+/SQL part

is giving these functionalities?! I would really like to know how to do the

following in the code:



1. open a connection to a SQL Server (not necessarily using the dedicated

System.Data.SQL namespace)

2. select some records using a SQL string

3. build a dataset with the currently selected records in a table

4. somehow loop through the dataset (for all available records) and display

the recordcount and display the selected number of records (user input) if

applicaple. otherwise, just display the available number of records.



I think my problem lies in the beta documentation and the fact that there's

actually so many ways to do this.



Is it possible to loop through a dataset (which, if I'm correct is a

disconnected recordset) similar to an old-school ADO recordset and checking

for the end of file in a loop?!



Like this (VBScript):

Do While Not oRs.Eof

    oRs.MoveNext

Loop



Here's my code:



---------- [snip] ------------



using System;

using System.Data;

using System.Data.SQL;



class myClass

{

 public static void Main()

 {

  int intNumRows = 10;

  string strConsoleInput;



  Console.Write("Number of rows to display (" + intNumRows.ToString() + "):

");



  strConsoleInput = Console.ReadLine();

  intNumRows = strConsoleInput.ToInt16();



  SQLConnection myConnection = new

SQLConnection("server=localhost;uid=sa;pwd=;database=sphereworx");

  SQLDataSetCommand myCommand = new SQLDataSetCommand("SELECT * FROM

sw_gallery_image ORDER BY id", myConnection);



  DataSet ds = new DataSet();

  myCommand.FillDataSet(ds, "Images");



    // display number of records here



  for (int i = 1; i <= intNumRows; i++)

  {

    // add code here to display all availble or selected number of records

    // add move-next like code for iterating through the dataset

   Console.WriteLine("Field value for row " + i.ToString() + " is: ");

  }

 }

}



---------- [snip] ------------



Thanks in advance guys! I do a lot of ADO programming every day so

recordset-handling isn't new to me. But, this C#/ADO stuff is just not

sticking to my brain yet :)



with regards

anders lundholm · lundholm@s...

the sphereworx / monoliner experience

--

Below is a little code snipet that you can use in C#.  The SQLDataReader

will function similarly to what you are used to in ADO with the Recordset.

It is built for speed and is Forward Only and Read Only.  The

SQLDataReader.Read() member will return false after the last record is read.



As I said, this is strictly for reading data.  If you need to edit and do

anything fancy, you'll probably need to use the DataSet class.



Hope this helps.



try

{

	//Construct a SQLConnection object

	conn = new SQLConnection({your connection string});

	conn.Open();



	//Construct a SQLCommand object using our connection

	SQLCommand cmd = new SQLCommand("{Your select statement}", conn);



	SQLDataReader reader;

	//Execute the Stored Procedure

	cmd.Execute(out reader);



	//Loop through the records

	while (reader.Read())

	{

		//You can get field values of this recordset, like this

		string strStringField = reader["{field name}"].ToString();

//string type field

		

		int intIntegerField = reader["{field

name}"].ToString().ToInt32(); //integer type field

		

	}



	//Close the reader

	reader.Close();



	}

	catch(Exception e){	

		//handle exception

	}

	finally{

		//clean up

	}

}





Scott Davis

Software Engineer

MCSD, MCP

International Parts

Decision Consultants Inc. 



-----Original Message-----

From: Anders Lundholm [mailto:lundholm@s...]

Sent: Wednesday, November 01, 2000 11:49 AM

To: ASP+

Subject: [aspx] Using datasets as recordsets!





Hello there!



I'm having my first go with displaying some data from a table (using SQL

Server 7.0) in a command line interface. However, having spent about 2 hours

reading about datasets, datareaders and so forth (which is pretty complex,

imho) I have to realize there's no solution in sight for today.



Therefore, could you please help me to understand how the correct

data-retrieval operation is supposed to work. Basically, I'm looking for

some commands which will allow me to do the same as the original older ADO

COM objects did (which, in ASP was pretty easy to use).



Like the ADO Recordset object for instance. Which class in the ADO+/SQL part

is giving these functionalities?! I would really like to know how to do the

following in the code:



1. open a connection to a SQL Server (not necessarily using the dedicated

System.Data.SQL namespace)

2. select some records using a SQL string

3. build a dataset with the currently selected records in a table

4. somehow loop through the dataset (for all available records) and display

the recordcount and display the selected number of records (user input) if

applicaple. otherwise, just display the available number of records.



I think my problem lies in the beta documentation and the fact that there's

actually so many ways to do this.



Is it possible to loop through a dataset (which, if I'm correct is a

disconnected recordset) similar to an old-school ADO recordset and checking

for the end of file in a loop?!



Like this (VBScript):

Do While Not oRs.Eof

    oRs.MoveNext

Loop



Here's my code:



---------- [snip] ------------



using System;

using System.Data;

using System.Data.SQL;



class myClass

{

 public static void Main()

 {

  int intNumRows = 10;

  string strConsoleInput;



  Console.Write("Number of rows to display (" + intNumRows.ToString() + "):

");



  strConsoleInput = Console.ReadLine();

  intNumRows = strConsoleInput.ToInt16();



  SQLConnection myConnection = new

SQLConnection("server=localhost;uid=sa;pwd=;database=sphereworx");

  SQLDataSetCommand myCommand = new SQLDataSetCommand("SELECT * FROM

sw_gallery_image ORDER BY id", myConnection);



  DataSet ds = new DataSet();

  myCommand.FillDataSet(ds, "Images");



    // display number of records here



  for (int i = 1; i <= intNumRows; i++)

  {

    // add code here to display all availble or selected number of records

    // add move-next like code for iterating through the dataset

   Console.WriteLine("Field value for row " + i.ToString() + " is: ");

  }

 }

}



---------- [snip] ------------



Thanks in advance guys! I do a lot of ADO programming every day so

recordset-handling isn't new to me. But, this C#/ADO stuff is just not

sticking to my brain yet :)



with regards

anders lundholm · lundholm@s...

the sphereworx / monoliner experience

--

Hi, 

I have a number of code snippets and references on my site that you may

find helpful.  Also, I have a searchable code library so you can search on

things like SQLConnection and view code snippets to see how they are used.



Here are a few links: 

Code Library 

http://www.123aspx.com/directory.asp?dir=2 



Database Tutorials 

http://www.123aspx.com/directory.asp?dir=7 



A Search on SQLConnection 

http://www.123aspx.com/search.asp?lookfor=SQLConnection&submit=search&wording=4





A Search on SQLCommand 

http://www.123aspx.com/search.asp?lookfor=SQLCommand&submit=search&wording=4





Hope this helps, 

Dave 

123aspx.com 

Thanks Dave and Scott!



It's pretty strange now having 3 different kinds of ADO access to keep track

of. The COM ADO objects, and the respectively ADO+ (and the subset for SQL

Server too).



I'll give it another big go today and I'm sure it'll work!

Thanks a lot!



with regards

anders lundholm · lundholm@s...

the sphereworx / monoliner experience

--