public static void CopyTableStructure(SqlTableReference sourceTable, SqlTableReference targetTable)
{
var sisTable = InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinition(sourceTable);
var createTableScript = SISToSqlDmlCreateStatementGenerator.GenerateCreateTableScript(sisTable);
targetTable.SqlConnectionProvider.Execute(createTableScript);
}
public static string GetInformationSchemaColumnsSqlQuery(SqlTableReference sqlTableReference)
{
var sql = string.Format(_sqlInformationSchemaColumn, sqlTableReference.TableName.Debracketize(), sqlTableReference.SqlConnectionProvider.DatabaseName);
sql = AddSchemaWhereClauseIfNecessary(sql, sqlTableReference);
return(sql);
}
public void EnsureCreateClass_FromSqlTableReference_AndFromQuery_ProduceEquivalentResults()
{
string classFromQuery = string.Empty, classFromTable = string.Empty;
"Given a C# class generated from a query"
._(() =>
{
var query = string.Format("select * from {0}", TableName);
classFromQuery = CSharpClassGeneratorFromQueryViaSqlDescribeResultSet
.GenerateClass(SqlConnectionProviders.AdventureWorksDb, query, "Employee");
var compileResults = RoslynHelper.TryCompile(classFromQuery);
compileResults.IsValid().Should().BeTrue();
});
"Given a C# class generated from SQL InformationSchema metadata"
._(() =>
{
var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, TableName);
classFromTable = CSharpClassGeneratorFromInformationSchema.CreateCSharpClass(sqlTableReference);
var compileResults = RoslynHelper.TryCompile(classFromTable);
compileResults.IsValid().Should().BeTrue();
});
"They should produce identical output"
._(() =>
{
Console.WriteLine("From Query:\r\n" + classFromQuery);
Console.WriteLine("From Table:\r\n" + classFromTable);
classFromTable.Should().BeEquivalentTo(classFromQuery);
});
}
public static string CreateCSharpClass(SqlTableReference sqlTableReference, string?className = default(string))
{
var tableName = className ?? sqlTableReference.TableName;
var schemaColumns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns(sqlTableReference);
return(CreateCSharpClass(schemaColumns, tableName, CSharpClassTextGeneratorOptions.Default));
}
public void CreateClassFromSqlTableReferenceForActualUseWhenNeedingToGenerateCSharpClasses()
{
var sqlTableReference = new SqlTableReference(SqlConnectionProviders.PbsiCopy, "PbsiWM.MTDINV_LINE");
var sqlTable = SqlTableFactory.Create(sqlTableReference);
var cSharpClass = CSharpClassGeneratorFromSqlTable.GenerateClass(sqlTable);
Console.WriteLine(cSharpClass);
}
public void CreateClassFromSqlTableReference()
{
var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, TableName);
var sqlTable = SqlTableFactory.Create(sqlTableReference);
var cSharpClass = CSharpClassGeneratorFromSqlTable.GenerateClass(sqlTable);
Console.WriteLine(cSharpClass);
}
public void RetrieveHumanResourcesEmployeeTableAsJson()
{
var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, "HumanResources.Employee");
var informationSchemaTableDefinition = InformationSchemaMetadataExplorer.GetInformationSchemaTableDefinition(sqlTableReference);
var tableDefinitionAsJson = JsonConvert.SerializeObject(informationSchemaTableDefinition);
Console.WriteLine(tableDefinitionAsJson);
}
public static SqlTableReference ToSqlTableReference(this SISTable sisTable,
ISqlConnectionProvider sqlConnectionProvider)
{
var sqlTableReference = new SqlTableReference(sqlConnectionProvider,
sisTable.TABLE_SCHEMA,
sisTable.TABLE_NAME);
return(sqlTableReference);
}
public void CreateClassFromSqlTableReference()
{
var sqlTableReference = new SqlTableReference(SqlConnectionProviders.AdventureWorksDb, TableName);
var cSharpClass = CSharpClassGeneratorFromInformationSchema.CreateCSharpClass(sqlTableReference);
var compileResult = RoslynHelper.TryCompile(cSharpClass);
compileResult.IsValid().Should().BeTrue();
Console.WriteLine(cSharpClass);
}
public static void EnsureTableExists <T>(this SqlTableReference tableReference)
{
var tableExists = CheckTableExistence(tableReference.SqlConnectionProvider, tableReference.SchemaName, tableReference.TableName);
if (!tableExists)
{
CreateTable <T>(tableReference.SqlConnectionProvider, tableReference.SchemaName, tableReference.TableName);
}
}
public static SISTable GetInformationSchemaTable(SqlTableReference sqlTableReference)
{
var sqlQuery =
InformationSchemaMetadataSqlQueryGenerator.GetInformationSchemaTableSqlQuery(sqlTableReference);
var tables = sqlTableReference.SqlConnectionProvider.Query <SISTable>(sqlQuery).ToList();
CheckOnlyOneTableWasReturned(tables);
return(tables.SingleOrDefault());
}
public static IList <SISColumn> GetInformationSchemaColumns(SqlTableReference sqlTableReference)
{
var sqlQuery =
InformationSchemaMetadataSqlQueryGenerator.GetInformationSchemaColumnsSqlQuery(sqlTableReference);
var tableColumns = sqlTableReference.SqlConnectionProvider.Query <SISColumn>(sqlQuery)
.ToList();
return(tableColumns);
}
public static InformationSchemaTableDefinition GetInformationSchemaTableDefinition(
SqlTableReference sqlTableReference)
{
var definition = new InformationSchemaTableDefinition();
definition.InformationSchemaTable = GetInformationSchemaTable(sqlTableReference);
definition.InformationSchemaColumns = GetInformationSchemaColumns(sqlTableReference);
definition.TableConstraints = GetTableConstraints(sqlTableReference);
return(definition);
}
/// <summary>Visits the <see cref="T:System.Linq.Expressions.ParameterExpression" />.</summary>
/// <returns>The modified expression, if it or any subexpression was modified; otherwise, returns the original expression.</returns>
/// <param name="node">The expression to visit.</param>
protected override Expression VisitParameter(ParameterExpression node)
{
var result = new SqlTableReference()
{
Table = _tables[node.Name]
};
Return(result);
return(node);
}
public static string GenerateClassFromTable(this ISqlConnectionProvider sqlConnectionProvider,
string schemaname, string tableName,
string className = null)
{
className = className ?? tableName;
var sqlTableReference = new SqlTableReference(sqlConnectionProvider, schemaname, tableName);
var sqlTable = SqlTableFactory.Create(sqlTableReference);
return(CSharpClassGeneratorFromSqlTable.GenerateClass(sqlTable, className));
}
private static string AddSchemaWhereClauseIfNecessary(string sql, SqlTableReference sqlTableReference)
{
var hasSchemaName = !string.IsNullOrWhiteSpace(sqlTableReference.SchemaName);
if (hasSchemaName)
{
sql += $" AND TABLE_SCHEMA = '{sqlTableReference.SchemaName}'";
}
return(sql);
}
private static List <TableConstraintInfoDto> GetTableConstraints(SqlTableReference sqlTableReference)
{
var whereclause =
$"WHERE tableConstraint.TABLE_SCHEMA = @schemaName AND tableConstraint.TABLE_NAME = @tableName ";
var query = TableConstraintInfoDto.SqlQuery.Replace("--WHERE", whereclause);
var queryParams = new { schemaName = sqlTableReference.SchemaName, tableName = sqlTableReference.TableName };
var result = sqlTableReference.SqlConnectionProvider.Query <TableConstraintInfoDto>(query, queryParams)
.ToList();
return(result);
}
public static SqlTable Create(SqlTableReference sqlTableReference)
{
var columns = InformationSchemaMetadataExplorer.GetInformationSchemaColumns(sqlTableReference);
var sqlColumns = columns.Select(CSharpClassGeneratorFromInformationSchema.InformationSchemaColumnToSqlColumn).ToList();
var sqlTable = new SqlTable()
{
Name = sqlTableReference.TableName,
Schema = sqlTableReference.SchemaName,
SqlColumns = sqlColumns
};
return(sqlTable);
}
public void CreateTableShouldWork()
{
ISqlConnectionProvider sourceSqlConnectionProvider = null;
UniqueDbConnectionProvider targetSqlConnectionProvider = null;
SqlTableReference sourceSqlTableReference = null;
SqlTableReference targetSqlTableReference = null;
"Given a blank target database"
._(() =>
{
targetSqlConnectionProvider =
new UniqueDbConnectionProvider(new UniqueDbConnectionProviderOptions(
"ws2012sqlexp1\\sqlexpress", "TableManipulationTests"));
targetSqlConnectionProvider.CreateDatabase();
});
using (targetSqlConnectionProvider.ToSelfDeletingDisposable())
{
"Given a source database and a new blank database"
._(() =>
{
sourceSqlConnectionProvider = SqlConnectionProviders.AdventureWorksDb;
});
"Given a source table to copy"
._(() =>
{
sourceSqlTableReference = new SqlTableReference(
sourceSqlConnectionProvider, "Person.Person");
});
"When copying the source table to the target db."
._(() =>
{
targetSqlTableReference = new SqlTableReference(
targetSqlConnectionProvider, "dbo.Person");
TableManipulation.CopyTableStructure(sourceSqlTableReference, targetSqlTableReference);
});
"Then there should be a copy of the table at the target DB"
._(() =>
{
var tableSchemas =
InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider);
tableSchemas.Count.Should().Be(1);
tableSchemas[0].TABLE_NAME.Should().Be("Person");
});
}
}
public void DropTableShouldWork()
{
ISqlConnectionProvider sourceSqlConnectionProvider = null;
UniqueDbConnectionProvider targetSqlConnectionProvider = null;
SqlTableReference sourceSqlTableReference = null;
SqlTableReference targetSqlTableReference = null;
"Given a table to drop in the database."
._(() =>
{
targetSqlConnectionProvider =
new UniqueDbConnectionProvider(new UniqueDbConnectionProviderOptions(
"ws2012sqlexp1\\sqlexpress", "TableManipulationTests"));
targetSqlConnectionProvider.CreateDatabase();
sourceSqlConnectionProvider = SqlConnectionProviders.AdventureWorksDb;
sourceSqlTableReference = new SqlTableReference(
sourceSqlConnectionProvider, "Person.Person");
targetSqlTableReference = new SqlTableReference(
targetSqlConnectionProvider, "dbo.Person");
TableManipulation.CopyTableStructure(sourceSqlTableReference, targetSqlTableReference);
var tableSchemas =
InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider);
tableSchemas.Count.Should().Be(1);
tableSchemas[0].TABLE_NAME.Should().Be("Person");
});
using (targetSqlConnectionProvider.ToSelfDeletingDisposable())
{
"When dropping the target table."
._(() =>
{
TableManipulation.DropTable(targetSqlTableReference);
});
"Then the table should be removed from the database."
._(() =>
{
var tableSchemas =
InformationSchemaMetadataExplorer.GetInformationSchemaTablesOnly(targetSqlConnectionProvider);
tableSchemas.Count.Should().Be(0);
});
}
}
public static string GenerateDropTableScript(SqlTableReference tableReference)
{
return(GenerateDropTableScript(tableReference.SchemaName, tableReference.TableName));
}
public static void TruncateTable(this SqlTableReference tableReference)
{
TruncateTable(tableReference.SqlConnectionProvider, tableReference.SchemaName, tableReference.TableName);
}
public static void DropTable(SqlTableReference table)
{
var dropTableScript = DropSqlTableReference.GenerateDropTableScript(table);
table.SqlConnectionProvider.Execute(dropTableScript);
}
private SqlQueryExpression VisitColumn(MemberExpression node)
{
// first we detect closures
if (!node.IsScopedParameterAccess())
{
return(new SqlObjectParameter()
{
Parameter = ObtainResult <object>(node)
});
}
// Get whether property points to an entity (case like x.User.Order)
if (_isEntity(node.Type))
{
// if so, we have nested table access
var nestedResult = new SqlTableReference()
{
Table = ObtainNestedTableReference(node, false)
};
return(nestedResult);
}
TableReference tableRef = null;
PropertyInfo colId = null;
var root = node.GetRootMember();
var nex = node.Expression.Unconvert();
if (nex.NodeType != ExpressionType.Parameter)
{
if (nex.NodeType == ExpressionType.MemberAccess && nex is MemberExpression mex) //nested table column
{
var derived = (node.Member.DeclaringType != mex.Type) && _isEntity(node.Member.DeclaringType);
tableRef = ObtainNestedTableReference(mex, derived);
colId = node.Member as PropertyInfo;
}
else
{
throw new Exception(string.Format("Please refer only top-level properties of {0}", root.Type));
}
}
if (tableRef == null && colId == null)
{
var parRef = root as ParameterExpression;
if (parRef == null)
{
throw new Exception("Unknown column reference: " + node.ToString());
}
tableRef = _tables[parRef.Name];
var derived = (node.Member.DeclaringType != root.Type) && _isEntity(node.Member.DeclaringType);
colId = node.Member as PropertyInfo;
}
var result = new SqlColumnReference()
{
Column = colId,
Table = tableRef
};
return(result);
}
private string VisitTableReference(SqlTableReference x9)
{
return(EmitParameter(x9));
}
protected virtual string VisitTableReference(SqlTableReference expr)
{
return(VisitTableReference(expr.Table, expr.ChildrenJoinedAs, expr.AsAlias));
}
