Is there any tutorial to do this in SQL Server 2008? Do you have an example?.
Is it possible to execute a stored procedure and get result in C# or something?
-
msdn.microsoft.com/en-us/sqlserver/ff681103John Saunders– John Saunders2011-03-15 02:08:30 +00:00Commented Mar 15, 2011 at 2:08
-
I would recommend understanding the concepts first. There is no difference in how ADO.NET code works with SQL Server 2005/2008.. It all lies in what objects the System.Data.SQLClient namespace provides and how they are related/different and what methods those objects have.sajoshi– sajoshi2011-03-15 03:00:35 +00:00Commented Mar 15, 2011 at 3:00
Add a comment
|
1 Answer
A good place to start is the SqlDataReader class:
private static void ReadOrderData(string connectionString)
{
string queryString =
"SELECT OrderID, CustomerID FROM dbo.Orders;";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// Call Read before accessing data.
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
}
// Call Close when done reading.
reader.Close();
}
}
More specifically: Using parameters with a SqlCommand and a Stored Procedure
static void GetSalesByCategory(string connectionString,
string categoryName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SalesByCategory";
command.CommandType = CommandType.StoredProcedure;
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CategoryName";
parameter.SqlDbType = SqlDbType.NVarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = categoryName;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
}
}
else
{
Console.WriteLine("No rows found.");
}
reader.Close();
}
}
2 Comments
Ashish Gupta
Just one thing, you can have the SqlDataReader in the using clause for a better way. if (reader.HasRows) { while (reader.Read()) { Console.WriteLine("{0}: {1:C}", reader[0], reader[1]); } } else { Console.WriteLine("No rows found."); }
Jeff Swensen
@ydobonmai Getting ahead of ourselves a bit here... but if you want to nitpick, the SqlCommand is disposable as well. Might as well add exception handling while we're at it. Heck, lets just write the Asker's code for him.