Calling a Stored Procedure

This C# program demonstrates how to call an SQL stored procedure in C#.

Program.cs

using System;
using System.Security;
using System.Data;
using System.Data.SqlClient;

namespace XoaX {
  class Program {
    static void Main(string[] args) {
      // Create the credentials object.
      SqlCredential qSqlCredential = null;
      string sPassword = "MyPassword";
      SecureString qSecurePassword = new SecureString();
      // Copy the password string
      foreach (char cChar in sPassword) {
        qSecurePassword.AppendChar(cChar);
      }
      qSecurePassword.MakeReadOnly();
      qSqlCredential = new SqlCredential("MyUser", qSecurePassword);

      SqlConnection qSqlConnection = null;
      qSqlConnection = new SqlConnection("Server=MyServer;DataBase=MyDB;Integrated Security=false",
        qSqlCredential);
      qSqlConnection.Open();

      // Create the command for the stored procedure
      SqlCommand qSqlCommand = new SqlCommand("MyStoredProc", qSqlConnection);

      // Set the command type to a stored procedure
      qSqlCommand.CommandType = CommandType.StoredProcedure;

      // Add the parameters that are sent to the stored procedure
      qSqlCommand.Parameters.Add(new SqlParameter("@ClientID", 242552));
      qSqlCommand.Parameters.Add(new SqlParameter("@InventoryID", 24242));
      qSqlCommand.Parameters.Add(new SqlParameter("@Quantity", 34));

      // Execute the command
      SqlDataReader qSqlReader = qSqlCommand.ExecuteReader();

      // Iterate through the results. Print the key values for each.
      while (qSqlReader.Read()) {
        for (int i = 0; i < qSqlReader.FieldCount; ++i) {
          Console.WriteLine("{0}. Ordinal: {1}     Type: {2}\n   Name: {3}     Value: {4}",
            i, qSqlReader.GetOrdinal(qSqlReader.GetName(i)),
            qSqlReader.GetProviderSpecificFieldType(i), qSqlReader.GetName(i),
            qSqlReader.GetProviderSpecificValue(i));
        }
        Console.WriteLine();
      }
    }
  }
}

Output

0. Ordinal: 0     Type: System.Data.SqlTypes.SqlString
   Name: SKU     Value: 15493
1. Ordinal: 1     Type: System.Data.SqlTypes.SqlDecimal
   Name: Price     Value: 67.791900000
2. Ordinal: 2     Type: System.Data.SqlTypes.SqlInt32
   Name: InventoryID     Value: 24242
3. Ordinal: 3     Type: System.Data.SqlTypes.SqlString
   Name: ItemID     Value: 7346337
4. Ordinal: 4     Type: System.Data.SqlTypes.SqlDecimal
   Name: LocationID     Value: 425
5. Ordinal: 5     Type: System.Data.SqlTypes.SqlDecimal
   Name: InStock     Value: 34.000000000
6. Ordinal: 6     Type: System.Data.SqlTypes.SqlString
   Name: Location     Value: Wausau, WI

0. Ordinal: 0     Type: System.Data.SqlTypes.SqlString
   Name: SKU     Value: 15493
1. Ordinal: 1     Type: System.Data.SqlTypes.SqlDecimal
   Name: Price     Value: 67.791900000
2. Ordinal: 2     Type: System.Data.SqlTypes.SqlInt32
   Name: InventoryID     Value: 24242
3. Ordinal: 3     Type: System.Data.SqlTypes.SqlString
   Name: ItemID     Value: 7346337
4. Ordinal: 4     Type: System.Data.SqlTypes.SqlDecimal
   Name: LocationID     Value: 646
5. Ordinal: 5     Type: System.Data.SqlTypes.SqlDecimal
   Name: InStock     Value: 8.000000000
6. Ordinal: 6     Type: System.Data.SqlTypes.SqlString
   Name: Location     Value: Salt Lake City, UT

0. Ordinal: 0     Type: System.Data.SqlTypes.SqlString
   Name: SKU     Value: 15493
1. Ordinal: 1     Type: System.Data.SqlTypes.SqlDecimal
   Name: Price     Value: 67.791900000
2. Ordinal: 2     Type: System.Data.SqlTypes.SqlInt32
   Name: InventoryID     Value: 24242
3. Ordinal: 3     Type: System.Data.SqlTypes.SqlString
   Name: ItemID     Value: 7346337
4. Ordinal: 4     Type: System.Data.SqlTypes.SqlDecimal
   Name: LocationID     Value: 372
5. Ordinal: 5     Type: System.Data.SqlTypes.SqlDecimal
   Name: InStock     Value: 6.000000000
6. Ordinal: 6     Type: System.Data.SqlTypes.SqlString
   Name: Location     Value: Oklahoma City, OK

0. Ordinal: 0     Type: System.Data.SqlTypes.SqlString
   Name: SKU     Value: 15493
1. Ordinal: 1     Type: System.Data.SqlTypes.SqlDecimal
   Name: Price     Value: 67.791900000
2. Ordinal: 2     Type: System.Data.SqlTypes.SqlInt32
   Name: InventoryID     Value: 24242
3. Ordinal: 3     Type: System.Data.SqlTypes.SqlString
   Name: ItemID     Value: 7346337
4. Ordinal: 4     Type: System.Data.SqlTypes.SqlDecimal
   Name: LocationID     Value: 843
5. Ordinal: 5     Type: System.Data.SqlTypes.SqlDecimal
   Name: InStock     Value: 2.000000000
6. Ordinal: 6     Type: System.Data.SqlTypes.SqlString
   Name: Location     Value: Plano, TX

0. Ordinal: 0     Type: System.Data.SqlTypes.SqlString
   Name: SKU     Value: 15493
1. Ordinal: 1     Type: System.Data.SqlTypes.SqlDecimal
   Name: Price     Value: 67.791900000
2. Ordinal: 2     Type: System.Data.SqlTypes.SqlInt32
   Name: InventoryID     Value: 24242
3. Ordinal: 3     Type: System.Data.SqlTypes.SqlString
   Name: ItemID     Value: 7346337
4. Ordinal: 4     Type: System.Data.SqlTypes.SqlDecimal
   Name: LocationID     Value: 479
5. Ordinal: 5     Type: System.Data.SqlTypes.SqlDecimal
   Name: InStock     Value: 13.000000000
6. Ordinal: 6     Type: System.Data.SqlTypes.SqlString
   Name: Location     Value: Wichita, KS

Press any key to continue . . .