CLR stored procedure with parameters

Yesterday we learned how to create and http://www.cnblogs.com/insus/p/4371762.html the CLR stored procedure simply by creating and deployment to SQL.

The following example is the implementation of CLR stored procedures with parameters:

To copy code:

SqlConnection Connection=New SqlConnection ("Context connection=true "); Connection.Open(); SqlCommand Command=new SqlCommand (); Command. Connection=connection; String sql="SELECT [FRUIT_NBR],[FRUITKIND_NBR],[Fruitname]  from [dbo].[Fruit] WHERE [Fruitname]  like @FruitName"; Command.commandtext=SQL; SqlParameter param=New SqlParameter ("@FruitName", SqlDbType.NVarChar); Param. Value=name; Command. Parameters.ADD(param); try {SqlDataReader Reader=command.            ExecuteReader (); SqlContext.Pipe.        Send (reader); } catch (Exception ex) {throw new Exception (ex).        Message); } connection.Close();

View Code

Writing CLR stored procedures is a bit like writing SQL statements directly in a program, but there are fine specifications and details that are different.

Insus.net feels that CLR stored procedures are a good fit for some of the more stable and mature procedures, when the deployment is generally not necessary to modify. Because the CLR store has an update or upgrade, you need to delete the old stored procedures and assembly that have already been stored, before you can reconfigure the new version of the CLR stored procedures.

To delete FRUITCLR assembly related stored procedures:



Then delete the assembly:

After all the drop, refer to yesterday's CLR stored procedure SQL deployment method, re-deployment ...

The following shows the execution of the newly added CLR stored procedure:

CLR stored procedure with parameters