Use Parameters in a SQL Command or Stored Procedure - CSharp Database

CSharp examples for Database:SQL Command

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

Description

Use Parameters in a SQL Command or Stored Procedure

Demo Code


using System;/*  w w  w  .j  a  v a  2  s . c  om*/
using System.Data;
using System.Data.SqlClient;

class MainClass
    {
        public static void ParameterizedCommandExample(SqlConnection con,
            string employeeID, string title)
        {
            using (SqlCommand com = con.CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = "UPDATE Employees SET Title = @title" +
                    " WHERE EmployeeId = @id";

                SqlParameter p1 = com.CreateParameter();
                p1.ParameterName = "@title";
                p1.SqlDbType = SqlDbType.VarChar;
                p1.Value = title;
                com.Parameters.Add(p1);

                com.Parameters.Add("@id", SqlDbType.Int).Value = employeeID;

                int result = com.ExecuteNonQuery();

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

        public static void StoredProcedureExample(SqlConnection con,
            string category, string year)
        {
            using (SqlCommand com = con.CreateCommand())
            {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "SalesByCategory";

                com.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value =
                    category;

                com.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = year;

                using (IDataReader reader = com.ExecuteReader())
                {
                    Console.WriteLine("Sales By Category ({0}).", year);

                    while (reader.Read())
                    {
                        Console.WriteLine("  {0} = {1}",
                            reader["ProductName"],
                            reader["TotalPurchase"]);
                    }
                }
            }
        }

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

                con.Open();

                ParameterizedCommandExample(con, "5", "Cleaner");
                Console.WriteLine(Environment.NewLine);

                StoredProcedureExample(con, "Seafood", "1999");
                Console.WriteLine(Environment.NewLine);
            }

        }
    }

Result


Related Tutorials