mirror of
https://github.com/ckaczor/sqltoolsservice.git
synced 2026-01-13 17:23:02 -05:00
6dd9a4b5f1
This changes adds the following two notifications from the results processing within a batch. These new notifications allows a consumer to stream results from a resultset instead of getting them all at once after the entire resultset has been fetched. ResultsAvailable This is issued after at least 1 row has been fetched for this resultset. ResultsUpdated This is issued periodically as more rows are available on this resultset. The final send of this notification when all rows have been fetched has the property 'Complete' set to true in the ResultSummary object. Detailed Change Log: * Initial completed implementation of QueryResults stream feature. 3 unittests still need fixing * Fix for the 3 failing test. I will look into making MockBehavior strict again for the three tests later * Making GetReader/GetWriter use filestream objects in FileShare.ReadWrite mode so the file can be concurrently read and written * Changing resultsAvailable also to fire off on a timer instead of after 1st row * adding a project for clr TableValuedFunction to produce result set with delays after each row. This is helpful in end to end testing. * Fixing up some tests and simplifying implementation of result update timer * Address review comments * Some test fixes * Disabled flaky test verification
91 lines
2.7 KiB
C#
91 lines
2.7 KiB
C#
using System;
|
|
using System.Collections;
|
|
using System.Data.SqlTypes;
|
|
using System.Text;
|
|
using Microsoft.SqlServer.Server;
|
|
using System.Threading;
|
|
/// <summary>
|
|
/// TVF for clr stored procedure with following definition:
|
|
///
|
|
/// use master;
|
|
/// -- Replace SQL_Server_logon with your SQL Server user credentials.
|
|
/// GRANT EXTERNAL ACCESS ASSEMBLY TO [redmond\arvran];
|
|
///-- Modify the following line to specify a different database.
|
|
///ALTER DATABASE master SET TRUSTWORTHY ON;
|
|
///--
|
|
///RECONFIGURE;
|
|
///GO
|
|
///sp_configure 'clr enabled', 1;
|
|
///GO
|
|
///RECONFIGURE;
|
|
///GO
|
|
///sp_configure 'network packet size', 512;
|
|
///GO
|
|
///RECONFIGURE;
|
|
///GO
|
|
|
|
///-- Modify the next line to use the appropriate database.
|
|
///CREATE ASSEMBLY MyTVfs
|
|
///FROM 'D:\src\sqltoolsservice\test\TVFSample\bin\Release\TVFSample.dll'
|
|
///WITH PERMISSION_SET = EXTERNAL_ACCESS;
|
|
///GO
|
|
///CREATE FUNCTION StreamingTvf(@numRows int , @delayInMs int, @messageSize int= 4000)
|
|
///RETURNS TABLE
|
|
///(rowNumber int, msg nvarchar(max))
|
|
///AS
|
|
///EXTERNAL NAME MyTVfs.TvfSample.TVF_Streaming;
|
|
///GO
|
|
/// </summary>
|
|
public class TvfSample
|
|
{
|
|
private struct ReturnValues
|
|
{
|
|
public int Value;
|
|
public string Message;
|
|
}
|
|
|
|
private static void FillValues(object obj, out SqlInt32 theValue, out SqlChars message)
|
|
{
|
|
ReturnValues returnValues = (ReturnValues)obj;
|
|
theValue = returnValues.Value;
|
|
message = new SqlChars(returnValues.Message);
|
|
}
|
|
|
|
private static string RandomString(int size)
|
|
{
|
|
StringBuilder builder = new StringBuilder();
|
|
Random random = new Random();
|
|
for (int i = 0; i < size; i++)
|
|
{
|
|
char ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
|
|
builder.Append(ch);
|
|
}
|
|
return builder.ToString();
|
|
}
|
|
|
|
[SqlFunction(DataAccess = DataAccessKind.None,
|
|
IsDeterministic = true, IsPrecise = true,
|
|
SystemDataAccess = SystemDataAccessKind.None,
|
|
FillRowMethodName = "FillValues", TableDefinition = "IntValue INT, Message nvarchar(max) ")]
|
|
public static IEnumerable TVF_Streaming(SqlInt32 maxValue, SqlInt32 delayInMilliseconds, SqlInt32 messageSize)
|
|
{
|
|
if (maxValue.IsNull)
|
|
{
|
|
yield break; // return no rows
|
|
}
|
|
|
|
// we do not need the Generic List of <ReturnValues>
|
|
ReturnValues values = new ReturnValues(); // each row
|
|
|
|
for (int index = 1; index <= maxValue.Value; index++)
|
|
{
|
|
values.Value = index;
|
|
values.Message = RandomString((int)messageSize);
|
|
yield return values; // return row per each iteration
|
|
Thread.Sleep((int)delayInMilliseconds);
|
|
}
|
|
|
|
// we do not need to return everything at once
|
|
}
|
|
}
|