Connecting to and reading data from a SQL database

Hi Everyone,

I am looking for some help, as i am pulling my hair out looking for information.

I have been using asp for many years and am now starting to learn .net. so far so good...

I am now wanted to connect to a database, execute a simple select statement and then read/write the information out. I can't help but think in old asp code and i am having a hard time finding what i need to perfom this simple task.

I have used the grid controls etc, and these are very good - however, i need to connect to a database in the code-behind file and perfom various functions in the background.

If any of you could be so kind as to perhaps show me some demo code i would be grateful.

I would like to do:

A) Connect to a database (sql server 2000)
B) Execute a simple SQL select statement
C) Read the returned information
D) put this information into variables used elsewhere
E) how do you check if no records are returned? such as the .EOF in asp?

Many thanks

Darren

Well, System.Data.SqlClient assembly provides some pretty ways: SqlDataReader, SqlDataApapter, SqlCommand, etc. I recommend using SqlCommand+SqlDataAdapter +DataSet. A quich example:

string connectionString = @."Data Source=(local);Integrated Security=SSPI;Database=tempdb";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();

// A table mapping names the DataTable.
adapter.TableMappings.Add("Table", "Suppliers");

// Open the connection.
connection.Open();

// Create a SqlCommand to retrieve Suppliers data.
SqlCommand command = new SqlCommand(
"select * from sysobjects ",
connection);
command.CommandType = CommandType.Text;

// Set the SqlDataAdapter's SelectCommand.
adapter.SelectCommand = command;
DataSet ds = new DataSet();
adapter.Fill(ds);

//check whether anything is returned, if 0 then no result returned

int i= ds.Tables.Count;

string s = ds.Tables[0].Rows[0][0];

}

And here is an example for SqlDataReader:

SqlConnection conn = new SqlConnection(@."Data Source=.;Database=tempdb;Integrated Security=SSPI;");
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from sysobjects", conn);
Console.WriteLine(conn.State);

SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.HasRows)
{
sdr.Read();
Console.WriteLine("Count of objects in current database:{0}", sdr.GetInt32(0));
}
conn.Close();

MSDN/VS2005 Documentation is your good friend