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; }