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

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 2

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.

Answer 3

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

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

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();
        }