Here goes, this is basically everything you want to know in terms of handling executing SQL on SqlServer using C#. Read through the comments and try to understand.
This example only has a select SQL, for other SQL like "DELETE, INSERT, CREATE TABLE, UPDATE" all you have to do is change the CommandText to whatever the SQL you want to execute and choose the appropriate method accordingly. :)
using (SqlConnection connection = new SqlConnection())
{
connection.ConnectionString = "Put your connection string here";
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM TestTable WHERE TestId = @TestId";
command.CommandType = CommandType.Text;
SqlParameter testIdParameter = command.CreateParameter();
testIdParameter.ParameterName = "@TestId";
testIdParameter.Value = "Some Value";
command.Parameters.Add(testIdParameter);
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetInt32(0));
}
int testObjectCount = (int)command.ExecuteScalar();
int affectedRows = command.ExecuteNonQuery();
}
finally
{
try
{
if (connection.State == ConnectionState.Open)
connection.Close();
}
catch
{
}
}
}
}
I know I am spoiling you with just giving you the code, but I am way bored at work today, so consider this as a good gesture :D Just for this time. Don't bite your nails, won't happen again :D
Hope this helps. Regards :)