private static void _transferTable(DbConnector dbConn, string tableName, string whereClause) {
var q = "";
q += "IF EXISTS( ";
q += " SELECT sc.column_id ";
q += " FROM sys.columns sc, ";
q += " sys.tables st ";
q += " WHERE sc.object_id = st.object_id AND ";
q += " st.name = '{0}' AND ";
q += " sc.is_identity = 1) ";
q += " select 1 AS BPL_RETURNED_SCALAR ";
q += "else ";
q += " select 0 AS BPL_RETURNED_SCALAR";
var isIdentity = dbConn.ExecuteQueryScalar<bool>(string.Format(q, tableName));
if (isIdentity) {
dbConn.ExecuteQuery(string.Format("SET IDENTITY_INSERT [{0}].[dbo].[{1}] ON", AnonDBName, tableName));
}
q = "";
q += "SELECT sc.name AS ColumnName, ";
q += " CAST(ISNULL(ep.value, '0') AS bit) AS IsAnonimize ";
q += "FROM syscolumns sc ";
q += "INNER JOIN sysobjects so ON sc.id = so.id ";
q += "LEFT JOIN sys.extended_properties ep ON so.id = ep.major_id AND sc.colorder = ep.minor_id AND ep.name = 'Anon' ";
q += "WHERE so.type = 'U' AND ";
q += " so.name = '{0}'";
q = string.Format(q, tableName);
var columns = dbConn.ExecuteQueryResultSet<TableColumn>(q);
q = string.Format("INSERT INTO [{0}].[dbo].[{1}] (", AnonDBName, tableName);
var comma = "";
foreach (var column in columns) {
q += comma;
q += string.Format("[{0}]", column.ColumnName);
comma = ",";
}
q += ") SELECT ";
comma = "";
foreach (var column in columns) {
q += comma;
if (column.IsAnonimize) {
q += string.Format("CASE WHEN {0} IS NULL THEN NULL ELSE dbo.F_BIN_TO_HEXSTR(CONVERT(VARBINARY(4), CHECKSUM(HASHBYTES('SHA1',[{1}])))) END AS {2}",
column.ColumnName, column.ColumnName, column.ColumnName);
} else {
q += string.Format("[{0}]", column.ColumnName);
}
comma = ",";
}
q += string.Format(" FROM [{0}] {1}", tableName, (whereClause != null) ? whereClause : "");
dbConn.ExecuteQuery(q);
if (isIdentity) {
dbConn.ExecuteQuery(string.Format("SET IDENTITY_INSERT [{0}].[dbo].[{1}] OFF", AnonDBName, tableName));
}
}
private static bool _isTableExists(DbConnector dbConn, string dbName, string tableName) {
var q = "";
q += "IF EXISTS( ";
q += " SELECT name ";
q += " FROM {0}.sys.sysobjects ";
q += " WHERE type = N'U' AND name = N'{1}') ";
q += " select 1 AS BPL_RETURNED_SCALAR ";
q += "else ";
q += " select 0 AS BPL_RETURNED_SCALAR";
return dbConn.ExecuteQueryScalar<bool>(string.Format(q, dbName, tableName));
}
