Execute a SQL Command or Stored Procedure - CSharp Database

CSharp examples for Database:SQL Command

  1. HOME
  2. CSharp
  3. Database
  4. SQL Command

Description

Execute a SQL Command or Stored Procedure

Demo Code


using System;//from w  ww . j ava  2  s. c  o m
using System.Data;
using System.Data.SqlClient;

class MainClass
    {
        public static void ExecuteNonQueryExample(IDbConnection con)
        {
            IDbCommand com = con.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "UPDATE Employees SET Title = 'Sales Director'" +
                " WHERE EmployeeId = '5'";

            int result = com.ExecuteNonQuery();

            if (result == 1)
            {
                Console.WriteLine("Employee title updated.");
            }
            else
            {
                Console.WriteLine("Employee title not updated.");
            }
        }

        public static void ExecuteReaderExample(IDbConnection con)
        {
            IDbCommand com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "Ten Most Expensive Products";

            using (IDataReader reader = com.ExecuteReader())
            {
                Console.WriteLine("Price of the Ten Most Expensive Products.");

                while (reader.Read())
                {
                    // Display the product details.
                    Console.WriteLine("  {0} = {1}",
                        reader["TenMostExpensiveProducts"],
                        reader["UnitPrice"]);
                }
            }
        }

        public static void ExecuteScalarExample(IDbConnection con)
        {
            IDbCommand com = con.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "SELECT COUNT(*) FROM Employees";

            // Execute the command and cast the result.
            int result = (int)com.ExecuteScalar();

            Console.WriteLine("Employee count = " + result);
        }

        public static void Main()
        {
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString = @"Data Source = .\sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                con.Open();

                ExecuteNonQueryExample(con);
                Console.WriteLine(Environment.NewLine);

                ExecuteReaderExample(con);
                Console.WriteLine(Environment.NewLine);

                ExecuteScalarExample(con);
            }
        }
    }

Result


Related Tutorials