Invoking CLR stored procedures

Question

In short, where can I find C#/VB client side sample code that calls CLR stored procedure with some argumnet [like a sqlxml data] and receives a datareader or other form of result ?

Also how do I periodically receive information from the running CLR stored proc sent through SQlContext.Pipe.Send() method ?

Answer 1

  // Create a record object that represents an individual row, including it's metadata.
      SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));

      // Populate the record.
      record.SetSqlString(0,( "Hello World!" + System.DateTime.Now));

      // Send the record to the client.
      SqlContext.Pipe.Send(record);

Answer 2

// run a stored procedure that takes a parameter
    public void RunStoredProcParams()
    {
        SqlConnection conn = null;
        SqlDataReader rdr  = null;

        // typically obtained from user
        // input, but we take a short cut
        string custId = "FURIB";

        Console.WriteLine("\nCustomer Order History:\n");

        try
        {
            // create and open a connection object
            conn = new 
                SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
            conn.Open();

            // 1.  create a command object identifying
            //     the stored procedure
            SqlCommand cmd  = new SqlCommand(
                "dbo.CustOrderHist", conn);

            // 2. set the command object so it knows
            //    to execute a stored procedure
            cmd.CommandType = CommandType.StoredProcedure;

            // 3. add parameter to command, which
            //    will be passed to the stored procedure
            cmd.Parameters.Add(
                new SqlParameter("@CustomerID", custId));

            // execute the command
            rdr = cmd.ExecuteReader();

            // iterate through results, printing each to console
            while (rdr.Read())
            {
                Console.WriteLine(
                    "Product: {0,-35} Total: {1,2}",
                    rdr["ProductName"],
                    rdr["Total"]);
            }
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
            if (rdr != null)
            {
                rdr.Close();
            }
        }   
    }
}

    enter code here

Answer 3

Just the same way you call ordinary stroed procedures. May be...

EXEC StoredProcedure1

I wrote a blog long back - Write your first SQL Server CLR Stored Procedure

Answer 4

string connectionString = ConfigurationManager.AppSettings["ConnectDB"];
        SqlConnection sn = new SqlConnection(connectionString);
        SqlParameter[] sqlParameters = new SqlParameter[1];
        sn.Open();
        SqlCommand dCmd = new SqlCommand("dbo.HelloWorld", sn);
        dCmd.CommandType = CommandType.StoredProcedure;
        SqlDataReader rdr = null;
        rdr = dCmd.ExecuteReader();
        while (rdr.Read())
            {
            for (int i = 0; i < rdr.FieldCount; i++)
                Response.Write(rdr[i]);
            }
        sn.Close();
        }

Answer 5

I know that I have usually created a regular T-SQL stored procedure which calls my CLR functions or stored procs. Then they can be treated just like all other stored procs.