/// <summary>
/// Get a SELECT statement used to get the lasted generated identity value of the specified table in any session and any range.
/// </summary>
/// <param name="tableName">Table name.</param>
/// <returns>A SELECT statement used to get the lasted generated identity value</returns>
public static ISqlSelectStatement GetIdentityStatement(string tableName)
{
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(new SqlServerField(new SqlFunction("IDENT_CURRENT").AddArgument(new SqlServerExpressionFactory().ConstantFactory.Create(tableName)), "Identity"));
return(statement);
}
/// <inheritdoc />
public IEnumerable <IDbViewEntity> GetViews(IDbDatabaseEntity database)
{
ISqlObject table = new SqlServerSource(new SqlServerSource("INFORMATION_SCHEMA"), "VIEWS");
ISqlObject nameField = new SqlServerField(table, "TABLE_NAME", null);
ISqlObject schemaField = new SqlServerField(table, "TABLE_SCHEMA", null);
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.FromClause.Source = table;
statement.SelectClause.AddExpressions(nameField);
statement.SelectClause.AddExpressions(schemaField);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = database.Source.Name;
builder.InitialCatalog = database.Name;
if (!(builder.IntegratedSecurity = database.Source.DbCredential.IntegratedSecurity))
{
builder.UserID = database.Source.DbCredential.UserName;
builder.Password = database.Source.DbCredential.Password;
}
ICollection <IDbViewEntity> views = new List <IDbViewEntity>();
using (IDataReader reader = SqlHelper.ExecuteSelect(new SqlConnection(builder.ConnectionString), statement)) {
while (reader.Read())
{
views.Add(new SqlServerViewEntity(database, reader.GetString(0), reader.GetString(1), new SqlServerViewFieldProvider()));
}
}
return(views);
}
/// <inheritdoc />
public IEnumerable <IDbDatabaseEntity> GetDatabases(IDbSourceEntity source)
{
ISqlObject table = new SqlServerSource(new SqlServerSource("sys"), "databases");
ISqlObject nameField = new SqlServerField(table, "name", null);
ISqlObject ownerID = new SqlServerField(table, "owner_sid", null);
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.FromClause.Source = table;
statement.WhereClause.Condition = ownerID.Equal(new SqlServerConstantExpressionFactory().Create(new byte[] { 0x01 })).Not();
statement.SelectClause.AddExpressions(nameField);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = source.Name;
if (!(builder.IntegratedSecurity = source.DbCredential.IntegratedSecurity))
{
builder.UserID = source.DbCredential.UserName;
builder.Password = source.DbCredential.Password;
}
ICollection <IDbDatabaseEntity> databases = new List <IDbDatabaseEntity>();
using (IDataReader reader = SqlHelper.ExecuteSelect(new SqlConnection(builder.ConnectionString), statement)) {
while (reader.Read())
{
databases.Add(new DbDatabaseEntity(source, reader.GetString(statement.SelectClause.Expressions.IndexOf(nameField)), new SqlServerDataEntityProvider()));
}
}
return(databases);
}
/// <inheritdoc />
public IEnumerable <IDbViewFieldEntity> GetFields(IDbViewEntity view)
{
ISqlObject owner = new SqlServerSource("INFORMATION_SCHEMA");
ISqlExpression tableName = new SqlServerConstantExpressionFactory().Create(view.Name);
ISqlExpression tableSchema = new SqlServerConstantExpressionFactory().Create(view.Schema);
ISqlObject columnsView = new SqlServerSource(owner, "COLUMNS");
ISqlObject nameField = new SqlServerField(columnsView, "COLUMN_NAME", "Name");
ISqlObject indexField = new SqlServerField(columnsView, "ORDINAL_POSITION", "Index");
ISqlObject typeField = new SqlServerField(columnsView, "DATA_TYPE", "TypeName");
ISqlObject maxLengthField = new SqlServerField(columnsView, "CHARACTER_MAXIMUM_LENGTH", "MaxLength");
ISqlObject isNullableField = new SqlServerField(columnsView, "IS_NULLABLE", "IsNullable");
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(nameField, indexField, typeField, maxLengthField, isNullableField);
statement.FromClause.SetSource(columnsView);
statement.WhereClause.Condition = new SqlServerField(columnsView, "TABLE_NAME", null).Equal(tableName).And(new SqlServerField(columnsView, "TABLE_SCHEMA", null).Equal(tableSchema));
statement.OrderClause.AddExpression(indexField, SqlOrder.Asc);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = view.Database.Source.Name;
builder.InitialCatalog = view.Database.Name;
if (!(builder.IntegratedSecurity = view.Database.Source.DbCredential.IntegratedSecurity))
{
builder.UserID = view.Database.Source.DbCredential.UserName;
builder.Password = view.Database.Source.DbCredential.Password;
}
ICollection <IDbViewFieldEntity> fields = new List <IDbViewFieldEntity>();
IList <ISqlExpression> selectFields = new List <ISqlExpression>(statement.SelectClause.Expressions);
using (IDataReader reader = SqlHelper.ExecuteSelect(new SqlConnection(builder.ConnectionString), statement)) {
while (reader.Read())
{
fields.Add(new DbViewFieldEntity(reader.GetString(selectFields.IndexOf(nameField)),
reader.GetInt32(selectFields.IndexOf(indexField)),
SqlServerUtility.GetRuntimeType(reader.GetString(selectFields.IndexOf(typeField))),
SqlServerUtility.GetDbType(reader.GetString(selectFields.IndexOf(typeField))),
(int)SqlServerUtility.GetSqlDbType(reader.GetString(selectFields.IndexOf(typeField))),
reader.GetString(selectFields.IndexOf(typeField)),
reader.IsDBNull(selectFields.IndexOf(maxLengthField)) ? 0 : reader.GetInt32(selectFields.IndexOf(maxLengthField)),
reader.GetString(selectFields.IndexOf(isNullableField)).Equals("YES", StringComparison.OrdinalIgnoreCase),
view,
null,
null));
}
}
return(fields);
}
private IEnumerable <UniqueIndexColumnItem> GetUniqueIndexColumns(IDbTableEntity table)
{
//create SQL statement
IConstantSqlExpressionsFactory constantFactory = new SqlServerConstantExpressionFactory();
ISqlObject owner = new SqlServerSource("sys");
ISqlFunction tableID = new SqlFunction("OBJECT_ID").AddArgument(constantFactory.Create(table.SchemaQualifiedName));
ISqlObject indexexView = new SqlServerSource(owner, "indexes");
ISqlObject indexColumnsView = new SqlServerSource(owner, "index_columns");
ISqlObject columnsView = new SqlServerSource(owner, "columns");
ISqlObject indexNameField = new SqlServerField(indexexView, "name", "IndexName");
ISqlObject columnNameField = new SqlServerField(columnsView, "name", "ColumnName");
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(indexNameField, columnNameField);
statement.FromClause.SetSource(indexexView);
statement.FromClause.InnerJoin(indexColumnsView, new SqlServerField(indexexView, "index_id", null).Equal(new SqlServerField(indexColumnsView, "index_id", null)).And(new SqlServerField(indexColumnsView, "object_id", null).Equal(tableID)).And(new SqlServerField(indexColumnsView, "is_included_column", null).Equal(constantFactory.Create(0))));
statement.FromClause.InnerJoin(columnsView, new SqlServerField(indexColumnsView, "column_id", null).Equal(new SqlServerField(columnsView, "column_id", null)).And(new SqlServerField(columnsView, "object_id", null).Equal(tableID)));
statement.WhereClause.Condition = new SqlServerField(indexexView, "object_id", null).Equal(tableID).And(new SqlServerField(indexexView, "is_unique", null).Equal(constantFactory.Create(1)).And(new SqlServerField(indexexView, "is_primary_key", null).Equal(constantFactory.Create(0)).And(new SqlServerField(indexexView, "is_unique_constraint", null).Equal(constantFactory.Create(0)).And(new SqlServerField(indexexView, "is_disabled", null).Equal(constantFactory.Create(0))))));
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = table.Database.Source.Name;
builder.InitialCatalog = table.Database.Name;
if (!(builder.IntegratedSecurity = table.Database.Source.DbCredential.IntegratedSecurity))
{
builder.UserID = table.Database.Source.DbCredential.UserName;
builder.Password = table.Database.Source.DbCredential.Password;
}
//read all fields data
ICollection <UniqueIndexColumnItem> items = new List <UniqueIndexColumnItem>();
using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
using (IDataReader reader = SqlHelper.ExecuteSelect(connection, statement)) {
ReadOnlyCollection <ISqlExpression> selectFields = statement.SelectClause.Expressions;
int indexNameIndex = selectFields.IndexOf(indexNameField);
int columnNameIndex = selectFields.IndexOf(columnNameField);
while (reader.Read())
{
items.Add(new UniqueIndexColumnItem {
IndexName = reader.GetString(indexNameIndex),
ColumnName = reader.GetString(columnNameIndex),
});
}
}
}
return(items);
}
/// <summary>
/// Gets a SELECT statement used to get the lasted generated identity value of any table in current session.
/// </summary>
/// <param name="inCurrentRange">True: in current range; Flase: in any range.</param>
/// <returns>A SELECT statement used to get the lasted generated identity value</returns>
public static ISqlSelectStatement GetIdentityStatement(bool inCurrentRange)
{
ISqlSelectStatement statement = new SqlServerSelectStatement();
if (inCurrentRange)
{
statement.SelectClause.AddExpressions(new SqlServerField(new SqlFunction("SCOPE_IDENTITY"), "Identity"));
}
else
{
statement.SelectClause.AddExpressions(new SqlServerField("@@IDENTITY", "Identity"));
}
return(statement);
}
/// <inheritdoc />
public IEnumerable <IDbTableFieldEntity> GetFields(IDbTableEntity table)
{
//create SQL statement
IConstantSqlExpressionsFactory constantFactory = new SqlServerConstantExpressionFactory();
ISqlObject owner = new SqlServerSource("INFORMATION_SCHEMA");
ISqlExpression tableName = constantFactory.Create(table.Name);
ISqlExpression tableSchema = constantFactory.Create(table.Schema);
ISqlFunction tableID = new SqlFunction("OBJECT_ID").AddArgument(constantFactory.Create(table.SchemaQualifiedName));
ISqlObject columnsView = new SqlServerSource(owner, "COLUMNS");
ISqlObject extendedPropertiesView = new SqlServerSource(new SqlServerSource("sys"), "extended_properties");
ISqlObject keyColumnUsageView = new SqlServerSource(owner, "KEY_COLUMN_USAGE");
ISqlObject tableConstraintsView = new SqlServerSource(owner, "TABLE_CONSTRAINTS");
ISqlObject referentialConstraintsView = new SqlServerSource(owner, "REFERENTIAL_CONSTRAINTS");
ISqlObject keyColumnUsageView2 = new SqlServerSource(owner, "KEY_COLUMN_USAGE", "kcu");
ISqlObject nameField = new SqlServerField(columnsView, "COLUMN_NAME", "Name");
ISqlObject indexField = new SqlServerField(columnsView, "ORDINAL_POSITION", "Index");
ISqlObject typeField = new SqlServerField(columnsView, "DATA_TYPE", "TypeName");
ISqlObject descriptionField = new SqlServerField(extendedPropertiesView, "value", "Description");
ISqlObject maxLengthField = new SqlServerField(columnsView, "CHARACTER_MAXIMUM_LENGTH", "MaxLength");
ISqlObject defaultField = new SqlServerField(columnsView, "COLUMN_DEFAULT", "DefaultExpression");
ISqlObject isNullableField = new SqlServerField(columnsView, "IS_NULLABLE", "IsNullable");
ISqlObject isIdentityField = new SqlServerField(new SqlFunction("COLUMNPROPERTY").AddArgument(tableID).AddArgument(nameField, false).AddArgument(constantFactory.Create("IsIdentity")), "IsIdentity");
ISqlObject constraintNameField = new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", "ConstraintName");
ISqlObject constraintTypeField = new SqlServerField(tableConstraintsView, "CONSTRAINT_TYPE", "ConstraintType");
ISqlObject referenceTableField = new SqlServerField(keyColumnUsageView2, "TABLE_NAME", "ReferenceTable");
ISqlObject referenceFieldField = new SqlServerField(keyColumnUsageView2, "COLUMN_NAME", "ReferenceField");
ISqlSelectStatement statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(nameField, indexField, typeField, descriptionField, maxLengthField, defaultField, isNullableField, isIdentityField, constraintNameField, constraintTypeField, referenceTableField, referenceFieldField);
statement.FromClause.SetSource(columnsView);
statement.FromClause.LeftOuterJoin(extendedPropertiesView, new SqlServerField(extendedPropertiesView, "class", null).Equal(constantFactory.Create(1)).And(new SqlServerField(extendedPropertiesView, "major_id", null).Equal(tableID)).And(new SqlServerField(extendedPropertiesView, "minor_id", null).Equal(new SqlFunction("COLUMNPROPERTY").AddArgument(tableID).AddArgument(nameField, false).AddArgument(constantFactory.Create("ColumnId")))));
statement.FromClause.LeftOuterJoin(keyColumnUsageView, new SqlServerField(columnsView, "COLUMN_NAME", null).Equal(new SqlServerField(keyColumnUsageView, "COLUMN_NAME", null)).And(new SqlServerField(keyColumnUsageView, "TABLE_NAME", null).Equal(tableName)).And(new SqlServerField(keyColumnUsageView, "TABLE_SCHEMA", null).Equal(tableSchema)));
statement.FromClause.LeftOuterJoin(tableConstraintsView, new SqlServerField(keyColumnUsageView, "CONSTRAINT_NAME", null).Equal(new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", null)).And(new SqlServerField(tableConstraintsView, "TABLE_NAME", null).Equal(tableName)).And(new SqlServerField(tableConstraintsView, "TABLE_SCHEMA", null).Equal(tableSchema)));
statement.FromClause.LeftOuterJoin(referentialConstraintsView, new SqlServerField(tableConstraintsView, "CONSTRAINT_NAME", null).Equal(new SqlServerField(referentialConstraintsView, "CONSTRAINT_NAME", null)));
statement.FromClause.LeftOuterJoin(keyColumnUsageView2, new SqlServerField(referentialConstraintsView, "UNIQUE_CONSTRAINT_NAME", null).Equal(new SqlServerField(keyColumnUsageView2, "CONSTRAINT_NAME", null)).And(new SqlServerField(keyColumnUsageView, "ORDINAL_POSITION", null).Equal(new SqlServerField(keyColumnUsageView2, "ORDINAL_POSITION", null))));
statement.WhereClause.Condition = new SqlServerField(columnsView, "TABLE_NAME", null).Equal(tableName).And(new SqlServerField(columnsView, "TABLE_SCHEMA", null).Equal(tableSchema));
statement.OrderClause.AddExpression(indexField, SqlOrder.Asc);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = table.Database.Source.Name;
builder.InitialCatalog = table.Database.Name;
if (!(builder.IntegratedSecurity = table.Database.Source.DbCredential.IntegratedSecurity))
{
builder.UserID = table.Database.Source.DbCredential.UserName;
builder.Password = table.Database.Source.DbCredential.Password;
}
//read all fields data
ICollection <FieldItem> items = new List <FieldItem>();
IList <ISqlExpression> selectFields = new List <ISqlExpression>(statement.SelectClause.Expressions);
using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
using (IDataReader reader = SqlHelper.ExecuteSelect(connection, statement)) {
int nameIndex = selectFields.IndexOf(nameField);
int indexIndex = selectFields.IndexOf(indexField);
int typeIndex = selectFields.IndexOf(typeField);
int descriptionIndex = selectFields.IndexOf(descriptionField);
int maxLengthIndex = selectFields.IndexOf(maxLengthField);
int defaultIndex = selectFields.IndexOf(defaultField);
int isNullableIndex = selectFields.IndexOf(isNullableField);
int isIdentityIndex = selectFields.IndexOf(isIdentityField);
int constraintNameIndex = selectFields.IndexOf(constraintNameField);
int constraintTypeIndex = selectFields.IndexOf(constraintTypeField);
int referenceTableIndex = selectFields.IndexOf(referenceTableField);
int referenceFieldIndex = selectFields.IndexOf(referenceFieldField);
while (reader.Read())
{
items.Add(new FieldItem {
Name = reader.GetString(nameIndex),
Index = reader.GetInt32(indexIndex),
Type = reader.GetString(typeIndex),
Description = reader.IsDBNull(descriptionIndex) ? null : reader.GetString(descriptionIndex),
MaxLength = reader.IsDBNull(maxLengthIndex) ? 0 : reader.GetInt32(maxLengthIndex),
HasDefault = !reader.IsDBNull(defaultIndex),
IsNullable = reader.GetString(isNullableIndex).Equals("YES", StringComparison.OrdinalIgnoreCase),
IsIdentity = reader.GetInt32(isIdentityIndex) == 1,
ConstraintName = reader.IsDBNull(constraintNameIndex) ? null : reader.GetString(constraintNameIndex),
ConstraintTypeName = reader.IsDBNull(constraintTypeIndex) ? null : reader.GetString(constraintTypeIndex),
ReferenceTable = reader.IsDBNull(referenceTableIndex) ? null : reader.GetString(referenceTableIndex),
ReferenceField = reader.IsDBNull(referenceFieldIndex) ? null : reader.GetString(referenceFieldIndex),
});
}
}
}
//inject unique index data
FieldItem field = null;
foreach (UniqueIndexColumnItem item in this.GetUniqueIndexColumns(table))
{
field = items.Where((obj) => string.Equals(obj.Name, item.ColumnName, StringComparison.OrdinalIgnoreCase)).First();
if (field.ConstraintName == null)
{
field.ConstraintName = item.IndexName;
field.ConstraintTypeName = "UNIQUE";
}
else
{
field = items.AddItem((FieldItem)field.Clone());
field.ConstraintName = item.IndexName;
field.ConstraintTypeName = "UNIQUE";
}
}
//get read-only data
ISqlObject tableSource = new SqlServerSource(new SqlServerSource(table.Schema), table.Name);
statement = new SqlServerSelectStatement();
statement.SelectClause.AddExpressions(new SqlAllField(tableSource));
statement.FromClause.Source = tableSource;
using (IDbConnection connection = new SqlConnection(builder.ConnectionString)) {
using (DataTable schema = SqlHelper.ExecuteSchema(connection, statement)) {
using (DataColumn columnNameColumn = schema.Columns["ColumnName"]) {
using (DataColumn isReadOnlyColumn = schema.Columns["IsReadOnly"]) {
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(columnNameColumn))
{
continue;
}
this.m_isReadOnlyCache[((string)row[columnNameColumn]).ToLower()] = (bool)row[isReadOnlyColumn];
}
}
}
}
}
//analyze fields
IDictionary <string, DbConstraintEntity> constraints = new Dictionary <string, DbConstraintEntity>();
this.CreateFileds(table, constraints, items.Where((obj) => string.IsNullOrWhiteSpace(obj.ReferenceField)));
this.CreateFileds(table, constraints, items.Where((obj) => !string.IsNullOrWhiteSpace(obj.ReferenceField)));
return(new List <IDbTableFieldEntity>(g_fieldsCache[table].Values));
}