public async void VerifyScriptAsCreateTable()
{
string query = @"CREATE TABLE testTable1 (c1 int)
GO
CREATE CLUSTERED INDEX [ClusteredIndex-1] ON [dbo].[testTable1]
(
[c1] ASC
)
GO
";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Create;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testTable1",
Schema = "dbo",
Type = "Table"
};
List <string> expectedScripts = new List <string> {
"CREATE TABLE [dbo].[testTable1]", "CREATE CLUSTERED INDEX [ClusteredIndex-1] ON [dbo].[testTable1]"
};
await VerifyScriptAsForMultipleObjects(query, new List <ScriptingObject> {
scriptingObject
}, scriptCreateDrop, expectedScripts);
}
public async void VerifyScriptAsDropStoredProcedure()
{
string query = "CREATE PROCEDURE testSp1 AS BEGIN Select * from sys.all_columns END";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Delete;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testSp1",
Schema = "dbo",
Type = "StoredProcedure"
};
string expectedScript = "DROP PROCEDURE [dbo].[testSp1]";
await VerifyScriptAs(query, scriptingObject, scriptCreateDrop, expectedScript);
}
public async void VerifyScriptAsDropView()
{
string query = "CREATE VIEW testView1 AS SELECT * from sys.all_columns";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Delete;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testView1",
Schema = "dbo",
Type = "View"
};
string expectedScript = "DROP VIEW [dbo].[testView1]";
await VerifyScriptAs(query, scriptingObject, scriptCreateDrop, expectedScript);
}
public async void VerifyScriptAsDropTable()
{
string query = "CREATE TABLE testTable1 (c1 int)";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Delete;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testTable1",
Schema = "dbo",
Type = "Table"
};
string expectedScript = "DROP TABLE [dbo].[testTable1]";
await VerifyScriptAs(query, scriptingObject, scriptCreateDrop, expectedScript);
}
public async void VerifyScriptAsSelectTable()
{
string query = "CREATE TABLE testTable1 (c1 int)";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Select;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testTable1",
Schema = "dbo",
Type = "Table"
};
string expectedScript = "SELECT TOP (1000) [c1]";
await VerifyScriptAs(query, scriptingObject, scriptCreateDrop, expectedScript);
}
public async void VerifyScriptAsExecuteTableFailes()
{
string query = "CREATE TABLE testTable1 (c1 int)";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Execute;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testTable1",
Schema = "dbo",
Type = "Table"
};
string expectedScript = null;
await VerifyScriptAs(query, scriptingObject, scriptCreateDrop, expectedScript);
}
public async void VerifyScriptAsAlter()
{
string query = @"CREATE PROCEDURE testSp1 @StartProductID [int] AS BEGIN Select * from sys.all_columns END
GO
CREATE VIEW testView1 AS SELECT * from sys.all_columns
GO
CREATE FUNCTION testFun1() RETURNS [int] AS BEGIN RETURN 1 END
GO";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Alter;
List <ScriptingObject> scriptingObjects = new List <ScriptingObject>
{
new ScriptingObject
{
Name = "testSp1",
Schema = "dbo",
Type = "StoredProcedure"
},
new ScriptingObject
{
Name = "testView1",
Schema = "dbo",
Type = "View"
},
new ScriptingObject
{
Name = "testFun1",
Schema = "dbo",
Type = "UserDefinedFunction"
}
};
List <string> expectedScripts = new List <string>
{
"ALTER PROCEDURE [dbo].[testSp1]",
"ALTER VIEW [dbo].[testView1]",
"ALTER FUNCTION [dbo].[testFun1]"
};
await VerifyScriptAsForMultipleObjects(query, scriptingObjects, scriptCreateDrop, expectedScripts);
}
public async void VerifyScriptAsExecuteStoredProcedure()
{
string query = @"CREATE PROCEDURE testSp1
@BusinessEntityID [int],
@JobTitle [nvarchar](50),
@HireDate [datetime],
@RateChangeDate [datetime],
@Rate [money],
@PayFrequency [tinyint]
AS
BEGIN Select * from sys.all_columns END";
ScriptingOperationType scriptCreateDrop = ScriptingOperationType.Execute;
ScriptingObject scriptingObject = new ScriptingObject
{
Name = "testSp1",
Schema = "dbo",
Type = "StoredProcedure"
};
string expectedScript = "EXECUTE @RC = [dbo].[testSp1]";
await VerifyScriptAs(query, scriptingObject, scriptCreateDrop, expectedScript);
}
private async Task VerifyScriptAsForMultipleObjects(string query, List <ScriptingObject> scriptingObjects, ScriptingOperationType operation, List <string> expectedScripts)
{
var testDb = await SqlTestDb.CreateNewAsync(TestServerType.OnPrem, false, null, query, "ScriptingTests");
try
{
var requestContext = new Mock <RequestContext <ScriptingResult> >();
requestContext.Setup(x => x.SendResult(It.IsAny <ScriptingResult>())).Returns(Task.FromResult(new object()));
ConnectionService connectionService = LiveConnectionHelper.GetLiveTestConnectionService();
using (SelfCleaningTempFile queryTempFile = new SelfCleaningTempFile())
{
//Opening a connection to db to lock the db
TestConnectionResult connectionResult = await LiveConnectionHelper.InitLiveConnectionInfoAsync(testDb.DatabaseName, queryTempFile.FilePath, ConnectionType.Default);
var scriptingParams = new ScriptingParams
{
OwnerUri = queryTempFile.FilePath,
ScriptDestination = "ToEditor",
Operation = operation
};
string scriptCreateOperation = "ScriptCreate";
if (operation == ScriptingOperationType.Delete)
{
scriptCreateOperation = "ScriptDrop";
}
else
{
scriptCreateOperation = $"Script{operation}";
}
scriptingParams.ScriptOptions = new ScriptOptions
{
ScriptCreateDrop = scriptCreateOperation,
};
scriptingParams.ScriptingObjects = scriptingObjects;
ScriptingService service = new ScriptingService();
await service.HandleScriptExecuteRequest(scriptingParams, requestContext.Object);
Thread.Sleep(2000);
await service.ScriptingTask;
requestContext.Verify(x => x.SendResult(It.Is <ScriptingResult>(r => VerifyScriptingResult(r, expectedScripts))));
connectionService.Disconnect(new ServiceLayer.Connection.Contracts.DisconnectParams
{
OwnerUri = queryTempFile.FilePath
});
}
}
catch
{
throw;
}
finally
{
await testDb.CleanupAsync();
}
}
private async Task VerifyScriptAs(string query, ScriptingObject scriptingObject, ScriptingOperationType operation, string expectedScript)
{
await VerifyScriptAsForMultipleObjects(query, new List <ScriptingObject> {
scriptingObject
}, operation, new List <string> {
expectedScript
});
}