How to Execute T-SQL from c#?

Question

Can anybody give an example for executing a T-SQL statement using C#?

Answer 1

I suggest that you start with an ADO.NET turorial like this one

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx

How to use SQLCommand

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson03.aspx

Answer 2

Do you mean something like this:

private static void ReadOrderData(string connectionString)
{
      string commandText = "SELECT OrderID, CustomerID FROM dbo.Orders;";
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                  connection.Open();
                  using (SqlDataReader reader = command.ExecuteReader())
                  {
                        while (reader.Read())
                        {
                              Console.WriteLine(String.Format("{0}, {1}", 
                                reader[0], reader[1]));
                        }
                  }
            }
      }
}

Or, perhaps something like:

static public int AddProductCategory(string newName, string connString)
{
    Int32 newProdID = 0;
    string sql =
        "INSERT INTO Production.ProductCategory (Name) VALUES (@Name); "
        + "SELECT CAST(scope_identity() AS int)";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@Name", SqlDbType.VarChar);
        cmd.Parameters["@name"].Value = newName;
        try
        {
            conn.Open();
            newProdID = (Int32)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
    return (int)newProdID;
}

Source: MSDN

Answer 3

Using a reader:

SqlConnection MSSQLConn = new SqlConnection("your connection string");
MSSQLConn.Open();
SqlCommand MSSQLSelectConsignment = new SqlCommand();
MSSQLSelectConsignment.CommandText = "select * from yourtable where blah = @blah";
MSSQLSelectConsignment.Parameters.AddWithValue("@blah", somestring);
MSSQLSelectConsignment.Connection = MSSQLConnOLD;
SqlDataReader reader = MSSQLSelectConsignment.ExecuteReader();

while (reader.Read())
{
...
}

To bring back a single value:

MSSQLSelectConsignment.CommandText = "select fieldname from yourtable where blah = @blah";
string yourstring = MSSQLSelectConsignment.ExecuteScalar().ToString();

or to bring back number of rows affected for updates etc:

MSSQLSelectConsignment.CommandText = "update yourtable set yourfield = 0 where blah = @blah";
int yourint = MSSQLSelectConsignment.ExecuteNonQuery();

Hope helps :)