How can I call a SQL function in C#?

Question

I have created a function in SQL, now I need to use that function in my C# application.

I tried using something like this, but it seems I'm doing it wrong since I'm getting:

Must declare the scalar value '@2064734117'

...when I give 2064734117 as the first parameter and 1 as the second parameter. Here is the code I'm talking about:

SqlConnection con = new SqlConnection(clsDb.connectionString);
string query = string.Format("select Function1(@{0},@{1}) ",
  int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString()),1);
con.Open();
SqlCommand cmd = new SqlCommand(query,con);
SqlDataAdapter READER = new SqlDataAdapter();
READER.SelectCommand = cmd;
DataTable table = new DataTable();
READER.Fill(table);
radGridView1.DataSource = table;
con.Close();

And my function takes two integer parameters and returns a table. I checked it in Visual Studio and it worked, but I couldn't get it to work in my application.

And this is my function declaration:

ALTER FUNCTION dbo.Function1
(
/*
@parameter1 int = 5,
@parameter2 datatype
*/
@ID int,
@clsTypeID int
)
    RETURNS TABLE/* @table_variable TABLE (column1 datatype, column2 datatype) */
    AS
         /*BEGIN */
    /* INSERT INTO @table_variable
       SELECT ... FROM ... */
RETURN SELECT  * FROM tblCLASS2 
        WHERE STNID = @ID AND CLASSTYPEID =  @clsTypeID  
/*END */
/*GO*/

Solution 1

Your SQL is a bit off, it should be:

  string query = string.Format("select * from dbo.Function1({0},{1});", int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString()),1);

You might want to use SqlParameter-objects to prevent sql injections:

  string query = "select * from dbo.Function1(@pa1,@par2);";
  cmd.Parameters.Add("@par1", SqlDbType.Int).Value = int.Parse(e.CurrentRow.Cells["CodeMeli"].Value.ToString());  
  cmd.Parameters.Add("@par2", SqlDbType.Int).Value = 1;

Solution 2

At a glance, the first thing I can see is that you aren't specifying the object owner / schema; that is required for functions, so it should be select dbo.Function1(...

Second: look at what your call to string.Format generates; that is generating @1 and @n for n another integer, but that is not a valid parameter name. Which is handy, because

Third: you didn't add any parameters

Fourth: for a table UDF (rather than a scalar UDF), you must select * from dbo.Function1(..., not just select dbo.Function1(...

Solution 3

You can do something like this:

        myConn.Open();

        //generating the new command for our database
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT OBJECTID_1, NDNT as theAddress, MIN(ABS(x - " + double.Parse(x.ToString()) + ") + ABS(y - " + double.Parse(y.ToString()) +")) from dbo.DWH_OUTPUT  GROUP BY OBJECTID_1,NDNT HAVING   (MIN(ABS(x - " + double.Parse(x.ToString()) + ") + ABS(y - " + double.Parse(y.ToString()) + ")) = (Select MIN(ABS(a.x - " + double.Parse(x.ToString()) + ") + ABS(a.y - " + double.Parse(y.ToString()) + ")) from dbo.DWH_OUTPUT a ) )";
        cmd.Connection = myConn;

        //getting some more ado.net objects
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = cmd;
        da.Fill(ds, @"Addresses");

        if (ds.Tables[0].Rows.Count > 0)
        {
            theAddress = ds.Tables[0].Rows[0][@"theAddress"] + @" (proximity address)";
        }

        myConn.Close();

Note how in this example, you set the SqlCommand's CommandType to CommandType.Text. Specify your command parameters (i.e. the select function in your code snippet), and then populate the dataset with the Fill method. Then you can pluck out the values from the rows as you ordinarily would with standard ado.net.

If you need to call a stored proc, have a look at this:

How do I call a TSQL function from ado.net

Solution 4

You need fully qualified name of function with owner/schema name A working sample available at following link:

View more solutions