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*/

What errors are you getting? Also one option is to write a stored procedure that calls your function and just call the proc via ado.net/c# — Ta01, Aug 03 '12 at 13:21
This code should work, what error is it giving? Note that this function does not *look like* a function, it would be better implemented in a `stored procedure`. However, it should work either way. — Andre Calil, Aug 03 '12 at 13:23
i get a "Must declare the scalar value '@2064734117'". when i give 2064734117 as the first parameter and 1 as the second parameter. — Hossein, Aug 03 '12 at 13:26
Since it's a table valued function, shouldn't it be `select * from dbo.Function1(({0},{1})` rather than just `select dbo.Function1(({0},{1})` ? — Bridge, Aug 03 '12 at 13:26

score 12 · Accepted Answer · edited Jul 26 '16 at 13:05

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;

score 4 · Answer 2 · answered Aug 03 '12 at 13:24

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(...

score 1 · Answer 3 · edited May 23 '17 at 12:18

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

score 1 · Answer 4 · edited May 23 '17 at 10:29

1

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

edited May 23 '17 at 10:29

Community

1
1

answered Aug 03 '12 at 13:24

Munawar

2,588
2
26
29

How can I call a SQL function in C#?

4 Answers4

Linked

Related