How can I call an SQL function in C #?

Question

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

I tried using something like this, but it seems like I'm doing it wrong, as I get:

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 tested it in Visual Studio and it worked, but I could not get it to work in my application.

And this is the declaration of my function:

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

Answer 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; 

Answer 2

At first glance, the first thing I see is that you do not specify the owner of the object / scheme; which is required for functions, so it should be select dbo.Function1(...

Secondly: look at how your call to string.Format ; which generates @1 and @n for n another integer, but not a valid parameter name. This is convenient because

Third: you have not added any parameters

Fourth: for the UDF table (and not for scalar UDF) you must select * from dbo.Function1(... , not just select dbo.Function1(...

Answer 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(ax - " + double.Parse(x.ToString()) + ") + ABS(ay - " + 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(); 

Notice how in this example you set SqlCommand CommandType to CommandType.Text . Set your command parameters (for example, the selection function in the code snippet), and then fill the data set using the Fill method. Then you can rip out the values ​​from the strings, as usual, with the standard ado.net.

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

How can I call the TSQL function from ado.net

Answer 4

You need the full name of the function with the name of the owner / schema. A working sample is available at the following link: