[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetFruitByKind(SqlByte kind_nbr)
{
SqlConnection connection = new SqlConnection("context connection=true");
connection.Open();
string commandText = "SELECT [Fruit_nbr],[FruitKind_nbr],[FruitName] FROM [dbo].[Fruit] WHERE [FruitKind_nbr] = @Kind_nbr ";
SqlCommand command = new SqlCommand(commandText, connection);
SqlParameter param = new SqlParameter("@Kind_nbr", SqlDbType.TinyInt);
param.Value = kind_nbr;
command.Parameters.Add(param);
SqlDataReader reader = command.ExecuteReader();
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Fruit_nbr", SqlDbType.TinyInt),
new SqlMetaData("FruitKind_nbr", SqlDbType.TinyInt),
new SqlMetaData("FruitName", SqlDbType.NVarChar, 100, 1033, SqlCompareOptions.None)
);
SqlContext.Pipe.SendResultsStart(record);
while (reader.Read())
{
record.SetByte(0, Convert.ToByte(reader["Fruit_nbr"]));
record.SetByte(1, Convert.ToByte(reader["FruitKind_nbr"]));
record.SetString(2, reader["FruitName"].ToString());
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}
View Code