C#: Sql Stored Procedure Source Code Example

Posted by on Saturday, December 5, 2009 · Leave a Comment 

C#: Sql Stored Procedure

Purpose: – Illustrates using Sql Stored Procedure with SqlDataReader.
Prerequistes:

  1. Install C# (Express or Standard Edition)
  2. Install SQL Server Express
  3. Download Northwind Database
  4. Attach Northwind Database to Databases in Sql Express

Notes:

  • Console Application is used to simplify things, but Windows Forms or Web Forms could also be used
  • You can build a library of syntax examples by using same project over and over and just commenting out what you do not want to execute in Program.cs

Instructions:

  1. Use C# 2008 (Express or Standard) Edition
  2. Create new project; select Console Application; name of Project could be CSharp_Syntax.
  3. Right-click project name in solution explorer; add new folder; name of folder could be DatabaseADONET
  4. Right-click folder; add class; class name could be clsSqCalllStoredProcedure.cs
  5. Copy code into clsSqlCallStoredProcedure.cs
  6. Copy code into Program.cs
  7. Click green arrow to start with debugging

Step 1: Use View Plain to Cut-n-paste code into clsSqlCallStoredProcedure.cs

using System;
using System.Data;
using System.Data.SqlClient;
 
 
public class clsSqlCallStoredProcedure
{
static SqlConnection con;
 
public void Main()
{
con = new SqlConnection("Server=(local)\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI");
 
SqlCommand cmd = new SqlCommand("Sales By Year", con);
cmd.CommandType = CommandType.StoredProcedure;
 
// Add Parameters to Command Parameters collection
cmd.Parameters.Add("@Beginning_Date", SqlDbType.DateTime);
cmd.Parameters.Add("@Ending_Date", SqlDbType.DateTime);
cmd.Parameters["@Beginning_Date"].Value = "1/1/1990";
cmd.Parameters["@Ending_Date"].Value = "1/1/2000";
 
try
{
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0} - {1:c}", reader.GetDateTime(0), reader.GetDecimal(2));
}
reader.Close();
}
finally
{
con.Close();
}
Console.ReadLine();
 
}
}

Step 2: Use View Plain to Cut-n-paste code into Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace CSharp_Syntax
{
class Program
{
static void Main(string[] args)
{
//DatabaseADONET
 
//clsSqlConnectionConnectionString mySqlConnectionConnectionString = new clsSqlConnectionConnectionString();
//mySqlConnectionConnectionString.Main();
 
//clsSqlSelect mySqlSelect = new clsSqlSelect();
//mySqlSelect.Main();
 
//clsSqlParametersInsert mySqlParametersInsert = new clsSqlParametersInsert();
//mySqlParametersInsert.Main();
 
clsSqlCallStoredProcedure mySqlCallStoredProcedure = new clsSqlCallStoredProcedure();
mySqlCallStoredProcedure.Main();
 
}
}
}

Filed under Code Samples · Tagged with , ,