Sunday, January 11, 2009

Call StoredProcedure with input and output parameters tutorial example

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

class MainClass {
static void Main() {
string cstr = "server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;";
using ( SqlConnection conn = new SqlConnection( cstr ) )
{
conn.Open();

SqlCommand cmd = new SqlCommand( "QueryVendor", conn );
cmd.CommandType = CommandType.StoredProcedure;

// input parm
SqlParameter name = cmd.Parameters.Add( "@name", SqlDbType.NVarChar, 15 );
name.Value = "Tom";

// output parm
SqlParameter vendor = cmd.Parameters.Add( "@vendor", SqlDbType.NVarChar, 15 );
vendor.Direction = ParameterDirection.Output;

// return value
SqlParameter rowCount = cmd.Parameters.Add( "@rowCount", SqlDbType.Int );
rowCount.Direction = ParameterDirection.ReturnValue;

cmd.ExecuteNonQuery();

if ( (int)rowCount.Value > 0 )
{
Console.WriteLine(" is available from " + vendor.Value );
}
else
{
Console.WriteLine(" not available from " + vendor.Value );
}
}

}
}

0 comments:

Followers

The Daily Puppy

 
Design by Wordpress Theme | Bloggerized by Free Blogger Templates | JCPenney Coupons