Friday, February 27, 2015

Executing Stored Procedure using Command Object

The Stored Procedure was verified that has no problem by SSMS.


I made client code that call Stored Procedure like following.



class CustomerOrder
{
string _connString = "Data Source=MyPC\\SQLTEST;Initial Catalog=TSQL2012;User ID=sa;Password=1234567";
public List<string> GetCustomerOrderList(int custid)
{
List<string> orderList = new List<string>();
using (SqlConnection customerOrderConnection = new SqlConnection(_connString))
{
using (SqlCommand customerOrderCommand = new SqlCommand())
{
try
{
customerOrderConnection.Open();
customerOrderCommand.Connection = customerOrderConnection;
customerOrderCommand.CommandText = "GetCustomerOrders";

SqlParameter custidParameter = new SqlParameter();
custidParameter.ParameterName = "@custid";
custidParameter.Direction = ParameterDirection.Input;
custidParameter.SqlDbType = SqlDbType.Int;
custidParameter.Value = custid;
customerOrderCommand.Parameters.Add(custidParameter);

SqlParameter fromdateParameter = new SqlParameter();
fromdateParameter.ParameterName = "@fromdate";
fromdateParameter.Direction = ParameterDirection.Input;
fromdateParameter.SqlDbType = SqlDbType.DateTime;
DateTime dt1 = new DateTime(2007, 01, 01);
fromdateParameter.Value = dt1;
customerOrderCommand.Parameters.Add(fromdateParameter);

SqlParameter todateParameter = new SqlParameter();
todateParameter.ParameterName = "@todate";
todateParameter.Direction = ParameterDirection.Input;
todateParameter.SqlDbType = SqlDbType.DateTime;
DateTime dt2 = new DateTime(2008, 01, 01);
todateParameter.Value = dt2;
customerOrderCommand.Parameters.Add(todateParameter);

SqlParameter outputParameter = customerOrderCommand.Parameters.Add("@numrows", SqlDbType.Int);
outputParameter.Direction = ParameterDirection.Output;
customerOrderCommand.CommandType = CommandType.StoredProcedure;
using (SqlDataReader customerOrderDataReader = customerOrderCommand.ExecuteReader())
{
while (customerOrderDataReader.Read())
{
orderList.Add(customerOrderDataReader.GetString(0));
}
}
return orderList;
}
catch (SqlException ex)
{
throw ex;
}
}
}
}
}
class Program
{
static void Main(string[] args)
{
CustomerOrder co = new CustomerOrder();
foreach (string order in co.GetCustomerOrderList(1))
{
Console.WriteLine("{0}", order);
}
}
}

If run, following error occurs.


Could not find Stored Procedure "GetCustomerOrders"....


Can you help me to fix this code?



No comments:

Post a Comment