Since the connection cannot be used until a DataReader has been closed, it is quite often necessary to read the results of a DataReader into an array, then close the DataReader, THEN you can process the data in the array while you use the connection for something else. I could only get this example to work with integers but not varchars (?).
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat="server">
void Page_Load(Object s, EventArgs e) {
SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["con"]);
SqlCommand cmd = new SqlCommand("CustomerByCountry",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@publications",SqlDbType.Int)).Value = 3;
//cmd.Parameters.Add(new SqlParameter("@country",SqlDbType.VarChar)).Value = "USA";
con.Open();
ArrayList al = new ArrayList();
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read()) {
object[] values = new object[dr.FieldCount];
dr.GetValues(values);
al.Add(values);
}
dr.Close();
con.Close();
foreach(object[] row in al) {
foreach(object column in row) {
Response.Write(column.ToString() + "<br>");
}
}
}
</script>
Stored Procedure
------------------------------------
CREATE PROCEDURE CustomerByCountry @publications int
AS
SELECT *
FROM Customers
WHERE publications = @publications
GO