public ReturnValue Connect()
{
if (ConnectionString == null)
return ReturnValue.FailResult();
return ReturnValue.Wrap(() =>
{
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
_tableInfo =
conn.ExecQuery(
"SELECT t.NAME AS TableName, s.Name AS SchemaName,max( p.rows) AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB " +
"FROM sys.tables t " +
"INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id " +
"INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id " +
"INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id " +
"LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id " +
"WHERE (t.NAME NOT LIKE 'dt%') AND (t.is_ms_shipped = 0) AND (i.OBJECT_ID > 255 ) " +
"GROUP BY t.Name, s.Name " +
"ORDER BY t.Name");
DisplayTables();
}
}, "Connect() failed.");
}
public static void AssertConstraintsAreEnabledAndTrusted()
{
using (var conn = new SqlConnection(DataToolsConnectionString))
{
conn.Open();
var dt = conn.ExecQuery("select name, is_disabled, is_not_trusted from sys.foreign_keys");
var badones =
dt.Rows.Cast<DataRow>().Where(r => r.Field<bool>("is_disabled") || r.Field<bool>("is_not_trusted"));
CollectionAssert.IsEmpty(badones);
}
}
public void SpecialSchemaTest()
{
using (var conn = new SqlConnection(DataSerTestHelper.DataToolsConnectionString))
{
const string tableName = "BulkLoadTestTableSpecial";
conn.ExecSql(string.Format("DELETE FROM {0}", tableName));
var datSer = new DataTableSerializer(DataSerTestHelper.DataToolsConnectionString);
var result = datSer.ImportTable(string.Format(@"resources\dbo.{0}.xml", tableName));
DataSerTestHelper.AssertResult(result);
var tableData = conn.ExecQuery(string.Format("SELECT * FROM {0}", tableName));
Assert.AreEqual(3, tableData.Rows.Count);
}
}
public void DifferentSchemaTest()
{
using (var conn = new SqlConnection(DataSerTestHelper.DataToolsConnectionString))
{
conn.ExecSql(File.ReadAllText(@"SQL\reset_BulkLoadTestTable1.sql"));
var datSer = new DataTableSerializer(DataSerTestHelper.DataToolsConnectionString);
var result = datSer.ImportTable(@"resources\dbo.BulkLoadTestTable1.xml");
DataSerTestHelper.AssertResult(result);
var tableData = conn.ExecQuery("SELECT * FROM BulkLoadTestTable1");
Assert.AreEqual(3, tableData.Rows.Count);
var firstRowData = tableData.Rows[0].ItemArray;
CollectionAssert.AreEqual(new object[] {1, "Alpha", 11, DBNull.Value}, firstRowData);
}
}
public List<SearchResult> Search(string textToSearchFor)
{
var searchResults = new List<SearchResult>();
using (var conn = new SqlConnection(_databaseDataTableSet.ConnStr))
{
conn.Open();
foreach (var table in _databaseDataTableSet.TableList)
{
var fields = GetFieldsOfType(conn, table, SqlDbTypeGroup.String);
DataColumn[] keys;
using (var adapter = new SqlDataAdapter("select * from " + table, conn))
using (var dt = new DataTable(table))
{
adapter.FillSchema(dt, SchemaType.Mapped);
keys = dt.PrimaryKey;
}
foreach (DataRow fieldInfoRow in fields.Rows)
{
var fieldName = fieldInfoRow.Field<string>("COLUMN_NAME");
string fieldSearchSql = string.Format("select * from {0} where ([{1}] like '%{2}%')",
table, fieldName, textToSearchFor);
Log.Debug(string.Format("fieldSearchSql: {0}", fieldSearchSql));
var dataSearchResults = conn.ExecQuery(fieldSearchSql);
searchResults.AddRange(
dataSearchResults.Rows.Cast<DataRow>().Select(dataRow => new SearchResult()
{
TableName = table,
FieldName = fieldName,
FieldType = fieldInfoRow.Field<string>("DATA_TYPE"),
Sql = string.Format("{0} AND {1}", fieldSearchSql, MakeKeyFilter(keys,dataRow.KeyValues())),
Value = dataRow[fieldName],
KeyFields = keys,
KeyValue = dataRow.KeyValues()
}));
}
}
}
return searchResults;
}
private DataTable GetFieldsOfType(SqlConnection conn, string table, SqlDbTypeGroup dbTypeGroup)
{
var matchingTypes = SqlTypeUtil.SqlDbTypeGroupMap.Where(kv => kv.Value == dbTypeGroup).Select(kv => kv.Key);
var inClause = string.Join(",", matchingTypes.Select(t => string.Format("'{0}'", t)));
var sqlDbTypeSql =
string.Format(
"select * from INFORMATION_SCHEMA.COLUMNS where (TABLE_SCHEMA + '.' + TABLE_NAME = '{0}') " +
"AND (DATA_TYPE IN ({1}))", table, inClause);
Log.Debug(sqlDbTypeSql);
var result = conn.ExecQuery(sqlDbTypeSql);
return result;
}
