I try to make a CLR in C# to use in SQLServer Management Studio 2014. Everything's working fine right now, unless I start to insert parameters.
How do you insert a parameter in the CLR and use it in a PROC
?
I tried to put the parameters in the function parentheses:
public static void InsertInto(SqlString street, SqlInt32 number)
{
using(SqlConnection conn = new SqlConnection("context connection = true"))
{
SqlCommand comm = new SqlCommand();
comm.CommandText = "INSERT INTO TABLE_CLRTEST (COLRW_STREET, COLRW_NUMBER) VALUES("+street+","+number+")";
I cannot seem to concatenate the SqlString, and it won't let me use a String either.
And in C# and I tried like this:
comm.CommandText = "INSERT INTO TABLE_CLRTEST (COLRW_STREET, COLRW_NUMBER) VALUES(@street,@number)";
comm.Parameters.Add("@street", SqlDbType.VarChar,50);
comm.Parameters.Add("@number", SqlDbType.Int);
When I use this is get the following error: CREATE PROCEDURE failed because parameter counts do not match.
So still nothing.
Do you guys/girls know what is going on? If you need clarification, just ask.
Thanks in advance
EDIT: The code snippet provided below is working. It seems that Strings just won't work.
public static void updateTest(SqlInt32 lo, SqlInt32 la)
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
conn.Open();
SqlCommand update = new SqlCommand();
update.CommandText = "UPDATE TABLE_CLRTEST SET COLRW_LONGITUDE = " + lo + ", COLRW_LATITUDE = " + la + "";
update.Connection = conn;
update.ExecuteNonQuery();
conn.Close();
}
}