4

How can I run a scalar valued function from c# and get its value? For a table-based function, I use a select command and get the resulting DataTable, but I am clueless on how to do it with scalar valued functions.

halfer
  • 19,824
  • 17
  • 99
  • 186
Hossein
  • 24,202
  • 35
  • 119
  • 224
  • Are you using ADO.NET? See similar question/answers here: http://stackoverflow.com/questions/12364891/call-sql-function-using-ado-net – Ocelot20 Feb 18 '13 at 18:41
  • You still use a select command, select the scalar value, i.e. "select 1", and you get back a DataTable with 1 row & 1 column. It would be better to use ExecuteReader () if you just iterate over the results. Also, for scalars you can use ExecuteScalar () on your SqlCommand object. – muhmud Feb 18 '13 at 18:54

1 Answers1

6

You can pass a string to SQL and have it do anything that SQL can do using SqlConneciton and 'SqlCommand'. 

string sqlcheck = "SELECT * FROM " + table + " WHERE CUST_NO = @customerNumber";

using (SqlConnection connect = new SqlConnection(DBConnection String))
{
   using (SqlCommand command = new SqlCommand(sqlcheck, connect))
   {
     command.Parameters.AddWithValue("@customerNumber", customerNumber);
     connect.Open();
     response = (string)(command.ExecuteScalar());
   }
}

You can use ExecuteScalar or one of the other execution methods. http://msdn.microsoft.com/en-us/library/182ax5k8.aspx

You can make your SQL string do any SQL, and return/not return using the different execution methods.

Ian Best
  • 510
  • 1
  • 11
  • 23