private string[] GetWhereClauses(string query)
{
SearchConditionNormalizer cn = new SearchConditionNormalizer();
var select = CreateSelect(query);
var res = new List <string>();
foreach (var qs in select.EnumerateQuerySpecifications())
{
cn.NormalizeQuerySpecification(qs);
// TODO use qs.SourceTableReferences ???
foreach (var tr in qs.EnumerateSourceTableReferences(true))
{
WhereClause where = cn.GenerateWhereClauseSpecificToTable(tr);
if (where != null)
{
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = true;
var sw = new StringWriter();
cg.Execute(sw, where);
res.Add(sw.ToString());
}
else
{
res.Add("");
}
}
}
return(res.ToArray());
}
private void RenderResults(JobInstance ji)
{
var q = (QueryBase)ji.Parameters["Query"].Value;
var codegen = new SqlServerCodeGenerator();
string sql = codegen.GenerateSelectStarQuery(q.Destination.GetTable(), 100);
using (var cn = new SqlConnection())
{
cn.ConnectionString = MyDBDataset.ConnectionString;
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
using (var dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
RenderTable(dr);
}
}
}
}
/// <summary>
/// Executest bulk copy to ingest data from the DataReader
/// </summary>
/// <param name="dr"></param>
protected void ExecuteBulkCopy(IDataReader dr, Table destination)
{
// TODO: it can only import the first resultset from dr
var cg = new SqlServerCodeGenerator();
isBulkCopyCanceled = false;
bulkCopyFinishedEvent = new AutoResetEvent(false);
// Initialize bulk copy
var sbc = new System.Data.SqlClient.SqlBulkCopy(destination.Dataset.ConnectionString);
sbc.DestinationTableName = cg.GetResolvedTableName(destination);
sbc.BatchSize = batchSize;
sbc.BulkCopyTimeout = timeout;
// Initialize events
sbc.NotifyAfter = batchSize;
sbc.SqlRowsCopied += delegate(object sender, SqlRowsCopiedEventArgs e)
{
//RowsAffected = e.RowsCopied; // TODO: delete if not used
e.Abort = isBulkCopyCanceled;
};
try
{
sbc.WriteToServer(dr);
}
finally
{
bulkCopyFinishedEvent.Set();
}
}
public virtual void Use_provider_method_is_generated_correctly()
{
var codeGenerator = new SqlServerCodeGenerator(new ProviderCodeGeneratorDependencies());
var result = codeGenerator.GenerateUseProvider("Data Source=Test");
Assert.Equal("UseSqlServer", result.Method);
Assert.Collection(
result.Arguments,
a => Assert.Equal("Data Source=Test", a));
}
private string GenerateCode(string query, bool resolveAliases, bool resolveNames, bool substituteStars)
{
var ss = CreateSelect(query);
var w = new StringWriter();
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = resolveNames;
cg.Execute(w, ss);
return(w.ToString());
}
public virtual void Use_provider_method_is_generated_correctly()
{
var codeGenerator = new SqlServerCodeGenerator(
new ProviderCodeGeneratorDependencies(
Enumerable.Empty <IProviderCodeGeneratorPlugin>()));
var result = codeGenerator.GenerateUseProvider("Data Source=Test", providerOptions: null);
Assert.Equal("UseSqlServer", result.Method);
Assert.Collection(
result.Arguments,
a => Assert.Equal("Data Source=Test", a));
Assert.Null(result.ChainedCall);
}
private void AppendPartitioningConditions(QuerySpecification qs, SimpleTableSource ts)
{
if (!double.IsInfinity(PartitioningKeyFrom) || !double.IsInfinity(PartitioningKeyTo))
{
var cg = new SqlServerCodeGenerator();
string format;
if (double.IsInfinity(PartitioningKeyFrom) && double.IsInfinity(PartitioningKeyTo))
{
format = "{1} <= {0} AND {0} < {2}";
}
else if (double.IsInfinity(PartitioningKeyFrom))
{
format = "{0} < {2}";
}
else
{
format = "{1} <= {0}";
}
string sql = String.Format(format,
cg.GetResolvedColumnName(ts.PartitioningColumnReference),
PartitioningKeyFrom.ToString(System.Globalization.CultureInfo.InvariantCulture),
PartitioningKeyTo.ToString(System.Globalization.CultureInfo.InvariantCulture));
var parser = new Jhu.Graywulf.SqlParser.SqlParser();
var sc = (SearchCondition)parser.Execute(new SearchCondition(), sql);
var where = qs.FindDescendant <WhereClause>();
if (where == null)
{
where = WhereClause.Create(sc);
var ws = Whitespace.Create();
var wsn = qs.Stack.AddAfter(qs.Stack.Find(qs.FindDescendant <FromClause>()), ws);
qs.Stack.AddAfter(wsn, where);
}
else
{
where.AppendCondition(sc, "AND");
}
}
// --- remove partition clause
ts.Stack.Remove(ts.FindDescendant <TablePartitionClause>());
}
protected override string GetExecuteQueryText()
{
// strip off order by
var orderby = SelectStatement.FindDescendant <OrderByClause>();
if (orderby != null)
{
SelectStatement.Stack.Remove(orderby);
}
// strip off partition on
foreach (var qs in SelectStatement.EnumerateQuerySpecifications())
{
// strip off select into
var into = qs.FindDescendant <IntoClause>();
if (into != null)
{
qs.Stack.Remove(into);
}
foreach (var ts in qs.EnumerateDescendantsRecursive <SimpleTableSource>())
{
var pc = ts.FindDescendant <TablePartitionClause>();
if (pc != null)
{
pc.Parent.Stack.Remove(pc);
}
}
}
// Generate code
var sw = new StringWriter();
var cg = new SqlServerCodeGenerator()
{
ResolveNames = true
};
cg.Execute(sw, SelectStatement);
return(sw.ToString());
}
public void GenerateCreateDestinationTableQueryTest()
{
DataTable schema;
using (var cn = new SqlConnection(Jhu.Graywulf.Test.AppSettings.IOTestConnectionString))
{
cn.Open();
var sql = "SELECT * FROM SampleData";
using (var cmd = new SqlCommand(sql, cn))
{
using (var dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
{
schema = dr.GetSchemaTable();
}
}
}
var dest = new Table()
{
SchemaName = "dbo",
TableName = "destination"
};
var cg = new SqlServerCodeGenerator();
var res = cg.GenerateCreateDestinationTableQuery(schema, dest);
Assert.AreEqual(@"CREATE TABLE [dbo].[destination] ([float] real NULL,
[double] float NULL,
[decimal] money NULL,
[nvarchar(50)] nvarchar(50) NULL,
[bigint] bigint NULL,
[int] int NOT NULL,
[tinyint] tinyint NULL,
[smallint] smallint NULL,
[bit] bit NULL,
[ntext] nvarchar(max) NULL,
[char] char(1) NULL,
[datetime] datetime NULL,
[guid] uniqueidentifier NULL)", res);
}
public virtual void Use_provider_method_is_generated_correctly_with_NetTopologySuite()
{
var codeGenerator = new SqlServerCodeGenerator(
new ProviderCodeGeneratorDependencies(
new[] { new SqlServerNetTopologySuiteCodeGeneratorPlugin() }));
var result = ((IProviderConfigurationCodeGenerator)codeGenerator).GenerateUseProvider("Data Source=Test");
Assert.Equal("UseSqlServer", result.Method);
Assert.Collection(
result.Arguments,
a => Assert.Equal("Data Source=Test", a),
a =>
{
var nestedClosure = Assert.IsType <NestedClosureCodeFragment>(a);
Assert.Equal("x", nestedClosure.Parameter);
Assert.Equal("UseNetTopologySuite", nestedClosure.MethodCalls[0].Method);
});
Assert.Null(result.ChainedCall);
}
private string[] GenerateMostRestrictiveTableQueryTestHelper(string sql, bool includePrimaryKey, int top)
{
var cg = new SqlServerCodeGenerator();
cg.ResolveNames = true;
var ss = CreateSelect(sql);
var res = new List <string>();
foreach (var qs in ss.EnumerateQuerySpecifications())
{
// TODO: use qs.SourceTableReferences
foreach (var tr in qs.EnumerateSourceTableReferences(true))
{
res.Add(cg.GenerateMostRestrictiveTableQuery(tr, includePrimaryKey, top));
}
}
return(res.ToArray());
}
public virtual void Use_provider_method_is_generated_correctly_with_options()
{
var codeGenerator = new SqlServerCodeGenerator(
new ProviderCodeGeneratorDependencies(
Enumerable.Empty <IProviderCodeGeneratorPlugin>()));
var providerOptions = new MethodCallCodeFragment(_setProviderOptionMethodInfo);
var result = codeGenerator.GenerateUseProvider("Data Source=Test", providerOptions);
Assert.Equal("UseSqlServer", result.Method);
Assert.Collection(
result.Arguments,
a => Assert.Equal("Data Source=Test", a),
a =>
{
var nestedClosure = Assert.IsType <NestedClosureCodeFragment>(a);
Assert.Equal("x", nestedClosure.Parameter);
Assert.Same(providerOptions, nestedClosure.MethodCalls[0]);
});
Assert.Null(result.ChainedCall);
}
public virtual void Use_provider_method_is_generated_correctly()
{
var codeGenerator = new SqlServerCodeGenerator(new ProviderCodeGeneratorDependencies());
Assert.Equal("UseSqlServer", codeGenerator.UseProviderMethod);
}
