[Solved] CRUD operation in C#

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. :)

// This is your connection to your database
using (SqlConnection connection = new SqlConnection())
{
    connection.ConnectionString = "Put your connection string here";

    // This creates an object with which you can execute sql
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM TestTable WHERE TestId = @TestId";
        command.CommandType = CommandType.Text;
        // If the SQL you want to execute is a stored procedure, use the commented 2 lines below
        // instead of the 2 lines above.
        // command.CommandText = "SP_GetTestSubjectCount_Select";
        // command.CommandType = CommandType.StoredProcedure;

        // This is how you add a parameter to your sql command
        // This way you are protected against SQL injection attacks
        SqlParameter testIdParameter = command.CreateParameter();
        testIdParameter.ParameterName = "@TestId";
        testIdParameter.Value = "Some Value";
        command.Parameters.Add(testIdParameter);

        try
        {

            connection.Open();

            // This is the easiest way to iterate through the returned results with MULTIPLE rows
            // This way can ONLY be used for SELECT statements or SPs which returns results
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader.GetInt32(0));
            }

            // If your SQL returns just ONE VALUE you can use the code below
            int testObjectCount = (int)command.ExecuteScalar();

            // For DELETE, UPDATE, INSERT statements which does not return a resultset
            // instead they return the number of records affected
            // Use the code below for that instead of the above code
            int affectedRows = command.ExecuteNonQuery();

        }
        finally
        {
            try
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
            catch
            {
                // This catch block suppresses any errors occured while closing the connection
                // remove the try catch block if you want your application to know of this exception as well
            }
        }
    }
}

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 :)