what is the best way to connect to a sql database?

[msn]

What is the best way to connect to a sql database in a asp.net page with
Visual studio. An example would be great. Thanks

Dave

 

[Chip]

There are so many ways to interface to SQL, it's tough to give an example.
Here is one, using a storedproc to update a value. No data is returned.

Dim con As SqlConnection

Dim cmd As SqlCommand

con = New SqlConnection(ConnectARListOps)

con.Open()

cmd = New SqlCommand("UpdateDiscountGroup", con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@orgID", SqlDbType.Int).Value =
Request.QueryString("id")

cmd.Parameters.Add("@groupID", SqlDbType.Int).Value = txtGroupID.Text

cmd.Parameters.Add("@invoiceID", SqlDbType.Int).Value =
lblInvoiceIDHidden.Text

cmd.ExecuteNonQuery()

con.Close()

 

[Martha[MSFT]]

The most common way to connect to a database is to use the "SqlConnection",
"SqlCommand" and "SqlDataReader" classes. The SqlConnection class represents
an open connection to an SQL database. The "SqlCommand" class represents an
sql statement or stored procedure. The "SqlDataReader" class represents
results from a db query.

The following example should be a good starting point:

string connectionString =
"Server=ServerName;database=DataBaseName;uid=username;pwd=password";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string commandString = "Select Title from Table;
SqlCommand command = new SqlCommand(commandString, conn);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
Response.Write(reader["Title"]);
Response.Write("<br>");
}
reader.Close();
conn.Close();

One more thing, you might want to look at "DataSet" class if you don't want
to keep the connection to the database open the whole time. You can retrieve
the records to memory and work on them offline. This is advantageous if you
are using the same set of data records from multiple pages. You don't want
to connect to the db from every page.

Hope this helps.