visual studio 2005 has a tool for connecting to a database called a "sqlDataSource". while this tool works, i find myself wanting to create the connection and build or execute stored procedures within my own C# code. this tutorial will show you how to connect to a sql database 3 different ways in asp.net

the first thing is creating a connection with your database. if you understand how connection strings are built, write your own other wise will can use the visual studio database tool to do that for us.

drag a new sqlDataSource onto the canvas, highlight it, then click the little arrow that appears to reveal configure datasource. in the windows that follow you can create a new database connection or use an existing one. refresh the server list and select your database server or use localhost. then choose if you want to use windows integrated security (where the server is the database user) or sql server security (where you supply the sql username and password). then select the database you want to connect to on the server, and test your connection.

press ok, and you will return to the previous window, click the little plus sign to expand and show the connection string that visual studio generated for you. copy it, and click cancel. now you can delete the sqlDataSource and switch to code view.

now the first thing we need to do is create a database connection in our code:

SqlConnection con = new SqlConnection("Data Source=DEVELOPMENT;Initial Catalog=rss;Integrated Security=True");

hardcoded procedure:

int id = 13;
con.Open();
//---create procedure
string select = "SELECT * FROM 'table' WHERE 'id' = @ID"; 
SqlCommand command = new SqlCommand(select, Con); 
//---paramerter adding
command.Parameters.Add("@ID", SqlDbType.Int).Value = id;
//---make a data reader
SqlDataReader reader = command.ExecuteReader(); 
while (reader.Read()) { 
   String data = reader["thing"].ToString();
}
//---close connection
con.Close();

using a stored procedure with one parameter
(e.g. "DELETE 'tabelname' WHERE 'id' = @ID; ")

int id = 42;
con.Open();
//---setup query
SqlCommand commando = new SqlCommand("deleteUser", con);
commando.CommandType = CommandType.StoredProcedure;
//---paramerter adding
commando.Parameters.Add("@ID", SqlDbType.Int).Value = id;
//---execute the query
commando.ExecuteNonQuery();
//---close connection
con.Close();

using a stored procedure without any parameters
(e.g. "SELECT * FROM 'tabelname'; ")

con.Open();
//---select a stored procedure
SqlCommand commander = new SqlCommand("getFilename", con);
commander.CommandType = CommandType.StoredProcedure;
//---make a data reader
SqlDataReader SQLreader;
SQLreader = commander.ExecuteReader();
while (SQLreader.Read()) {
   //---select data by array index
   String data  = SQLreader[0].ToString();
   //---select data by sql collumn name
   String data2 = SQLreader["name"].ToString();
}
//---close connections
SQLreader.Close();
con.Close();