Esempio n. 1
0
        /// <summary>
        /// Bulk insert node from data file into node table. 
        /// </summary>
        /// <param name="dataFileName"> The name of data file.</param>
        /// <param name="tableName"> The table name of node table.</param>
        /// <param name="tableSchema"> The Schema name of node table. Default by "dbo".</param>
        /// <param name="dataColumnName"> User-supplied column(s) in data file in order.
        /// By default(null or empty), data file should exactly contain all the columns of property and nodeid in creating order.</param>
        /// <param name="fieldTerminator"> The field terminator of data file. Default by "\t".</param>
        /// <param name="rowTerminator"> The row terminator of data file. Default by "\r\n".</param>
        public void BulkInsertNode(string dataFileName, string tableName, string tableSchema = "dbo",
            IList<string> dataColumnName = null, string fieldTerminator = "\t", string rowTerminator = "\r\n", bool skipHeader = false)
        {
            var command = Conn.CreateCommand();
            command.CommandTimeout = 0;
            
            var transaction = Conn.BeginTransaction();
            command.Transaction = transaction;
            try
            {
                if (tableName == null)
                {
                    throw new BulkInsertNodeException("The string of table name is null.");
                }
                if (dataFileName == null)
                {
                    throw new BulkInsertNodeException("The string of data file name is null.");
                }
                if (!File.Exists(dataFileName))
                {
                    throw new BulkInsertNodeException(string.Format("Data file {0} doesn't exist.", dataFileName));
                }
                if (string.IsNullOrEmpty(tableSchema))
                {
                    tableSchema = "dbo";
                }
                if (string.IsNullOrEmpty(fieldTerminator))
                {
                    fieldTerminator = "\t";
                }
                if (string.IsNullOrEmpty(rowTerminator))
                {
                    rowTerminator = "\r\n";
                }

                command.Parameters.Clear();
                command.Parameters.Add("name", SqlDbType.NVarChar, 128);
                command.Parameters["name"].Value = tableName;
                command.Parameters.Add("schema", SqlDbType.NVarChar, 128);
                command.Parameters["schema"].Value = tableSchema;
                const string checkTableExist = @"
                select *
                from {0}
                where TableName  = @name and TableSchema = @schema";
                command.CommandText = string.Format(checkTableExist, MetadataTables[0]); //_NodeTableCollection
                using (var reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new BulkInsertNodeException(
                            string.Format(
                                "There doesn't exist the table with tableSchema(\"{0}\") and tableName(\"{1}\")",
                                tableSchema, tableName));
                    }
                }

                //Get the column name and datatype in the node table for bulk insert.
                var columnDataType = new List<string>();
                const string findColumn = @"
                select GC.columnName, ISC.DATA_TYPE
                from {0} GC 
                join sys.columns C
                on GC.TableName = @name and GC.TableSchema = @schema and c.name = GC.ColumnName 
                join sys.tables T
                on C.object_id = T.object_id and GC.ColumnRole != 1 and T.name = GC.TableName
                join sys.schemas SC
                on SC.name = GC.TableSchema and SC.schema_id = T.schema_id
                join INFORMATION_SCHEMA.COLUMNS ISC
                on ISC.TABLE_SCHEMA = GC.TableSchema and ISC.TABLE_NAME = GC.TableName and ISC.COLUMN_NAME = GC.ColumnName
                order by C.column_id";
                command.CommandText = string.Format(findColumn, MetadataTables[1]); //_NodeTableColumnCollection

                if (dataColumnName == null || !dataColumnName.Any())
                {
                    if (dataColumnName == null)
                    {
                        dataColumnName = new List<string>();
                    }
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            dataColumnName.Add(reader["columnName"].ToString());
                            columnDataType.Add(reader["DATA_TYPE"].ToString());
                        }
                    }
                }
                else
                {
                    var columnNameMapping = new Dictionary<string, Tuple<string, string>>();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            columnNameMapping[reader["columnName"].ToString().ToLower()] =
                                Tuple.Create(reader["columnName"].ToString(), reader["DATA_TYPE"].ToString());
                        }
                    }
                    for (int i = 0; i < dataColumnName.Count; i++)
                    {
                        var it = dataColumnName[i];
                        if (columnNameMapping.ContainsKey(it.ToLower()))
                        {
                            columnDataType.Add(columnNameMapping[it.ToLower()].Item2);
                            dataColumnName[i] = columnNameMapping[it.ToLower()].Item1;
                        }
                        else
                        {
                            throw new BulkInsertNodeException(
                                string.Format("There doesn't exist a legal column \"{0}\" in the table \"{1}\".", it,
                                    tableName));
                        }
                    }
                }

                //Record information of indexes.
                var indexInfo = new List<IndexOrConstraintInformationRecord>();
                command.CommandText = @"
                select K.name indexname, K.index_id, K.type_desc, K.is_unique, K.is_primary_key, C.name referencename
                from sys.tables T
				join sys.schemas SC
				on SC.name = @schema and SC.schema_id = T.schema_id
				join sys.indexes K
                on T.object_id = K.object_id and T.name = @name 
				join sys.index_columns I
				on I.object_id = K.object_id and I.index_id = K.index_id
				join sys.columns C
				on I.object_id = C.object_id and I.column_id = C.column_id
				order by K.index_id, I.index_id";

                using (var reader = command.ExecuteReader())
                {
                    int indexId = -1;
                    while (reader.Read())
                    {
                        if (reader["indexname"].ToString() != "")
                        {
                            var columnIndexId = Convert.ToInt32(reader["index_id"].ToString());
                            if (indexId != columnIndexId)
                            {
                                indexInfo.Add(
                                    new IndexOrConstraintInformationRecord
                                    {
                                        name = reader["indexname"].ToString(),
                                        cluseterType = reader["type_desc"].ToString(),
                                        isUnique = (reader["is_unique"].ToString() == "True"),
                                        isPrimaryKey = (reader["is_primary_key"].ToString() == "True"),
                                        isConstraint = 0,
                                        indexColumns = new List<string>()
                                    });
                                indexId = columnIndexId;
                            }
                            indexInfo.Last().indexColumns.Add(reader["referencename"].ToString());
                        }
                    }
                    reader.Close();
                }

                command.CommandText = @"
                select KC.name 
                from sys.key_constraints KC
				join sys.schemas SC
				on SC.schema_id  = KC.schema_id and SC.name = @schema 
			    join sys.tables T
				on T.schema_id = SC.schema_id and T.name = @name and T.object_id  = KC.parent_object_id";

                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        foreach (var it in indexInfo)
                        {
                            var str = reader["name"].ToString();
                            if (str.ToLower() == it.name.ToLower())
                            {
                                it.isConstraint = 1;
                            }
                        }
                    }
                    reader.Close();
                }

                var tableNameWithSchema = string.Format("{0}.{1}", tableSchema, tableName);

                //Drop indexes and constraint.
                command.Parameters.Clear();
                const string dropIndexCommand = @"
                drop index {0} on {1} ";
                const string dropConstraintCommand = @"
                ALTER TABLE {0}
                DROP CONSTRAINT {1}";
                foreach (var it in indexInfo)
                {
                    if (it.isPrimaryKey || it.isConstraint != 0) //drop constraint
                    {
                        command.CommandText = string.Format(dropConstraintCommand, tableNameWithSchema, it.name);
                    }
                    else //drop index
                    {
                        command.CommandText = string.Format(dropIndexCommand, it.name, tableNameWithSchema);
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        it.isExecuteSuccess = true;
                    }
                    catch
                    {
                        it.isExecuteSuccess = false;
                    }
                }

                //Bulk insert
                using (var sqlBulkCopy = new SqlBulkCopy(Conn, SqlBulkCopyOptions.TableLock, transaction))
                {
                    sqlBulkCopy.BulkCopyTimeout = 0;
                    using (var reader = new BulkInsertFileDataReader(dataFileName, fieldTerminator, rowTerminator, dataColumnName,
                        columnDataType, skipHeader))
                    {
                        foreach (var it in dataColumnName)
                        {
                            sqlBulkCopy.ColumnMappings.Add(it, it);
                        }
                        sqlBulkCopy.DestinationTableName = tableNameWithSchema;
                        sqlBulkCopy.WriteToServer(reader);
                    }
                }

                //Rebuild indexes or constraint.
                command.Parameters.Clear();
                const string buildUnique = @"
                ALTER TABLE {0}
                ADD constraint {1}
                {2}({3})";
                const string buildIndex = @"
                Create {0} {1} index {2}
                on {3}({4})";

                foreach (var it in indexInfo)
                {
                    if (it.isExecuteSuccess)
                    {
                        if (it.isPrimaryKey || it.isConstraint == 1) //primary key or unique constraint
                        {
                            var type = it.isPrimaryKey ? "Primary key" : "Unique";
                            command.CommandText = string.Format(buildUnique, tableNameWithSchema,
                                it.name, type, string.Join(",", it.indexColumns));
                        }
                        else if (it.isConstraint == 0) //index
                        {
                            var unique = it.isUnique ? "unique" : "";
                            command.CommandText = string.Format(buildIndex, unique, it.cluseterType,
                                it.name, tableNameWithSchema, string.Join(",", it.indexColumns));
                        }
                        command.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
            catch (Exception error)
            {
                transaction.Rollback();
                throw new BulkInsertNodeException(error.Message);
            }
        }
Esempio n. 2
0
        /// <summary>
        /// Bulk insert edge from data file
        /// Data file should contain source node id and Sink node id on the first and second columns,
        /// then followed by the columns of user-supplied edge attribute.
        /// </summary>
        /// <param name="dataFileName"> The name of data file.</param>
        /// <param name="tableSchema"> The Schema name of node table. Default by "dbo".</param>
        /// <param name="sourceTableName"> The source node table name of node table.</param>
        /// <param name="sourceNodeIdName"> The node id name of source node table.</param>
        /// <param name="sinkTableName"> The Sink node table name of node table.</param>
        /// <param name="sinkNodeIdName"> The node id name of Sink node table.</param>
        /// <param name="edgeColumnName"> The edge column name in source node table.</param>
        /// <param name="dataEdgeAttributeName"> User-supplied edge attribute name in data file in order.
        /// By default(null), data file should exactly contain all the Edge Attribute in creating order.
        /// Empty stands for that data file doesn't provide edge attribute.</param>
        /// <param name="fieldTerminator"> The field terminator of data file. Default by "\t".</param>
        /// <param name="rowTerminator"> The row terminator of data file. Default by "\r\n".</param>
        /// <param name="updateMethod"> Choose update method or rebuild table method to implement bulk insert edge.
        /// <param name="Header"> True, the data file contains Header</param>
        public void BulkInsertEdge(string dataFileName, string tableSchema, string sourceTableName,
            string sourceNodeIdName, string sinkTableName, string sinkNodeIdName, string edgeColumnName,
            IList<string> dataEdgeAttributeName = null, string fieldTerminator = "\t", string rowTerminator = "\r\n",
            bool updateMethod = true, bool Header = false)
        {
            //Data types mapping from C# into sql and .Net.
            var typeDictionary = new Dictionary<string, Tuple<string, string>>
            {
                {"int", new Tuple<string, string>("int", "int")},
                {"long", new Tuple<string, string>("bigint", "bigint")},
                {"double", new Tuple<string, string>("float", "float")},
                {"string", new Tuple<string, string>("nvarchar(4000)", "nvarchar")},
                {"bool", new Tuple<string, string>("bit", "bit")}
            };
            
            //Check validity of input
            if (string.IsNullOrEmpty(sourceTableName))
            {
                throw new BulkInsertEdgeException("The string of source table name is null or empty.");
            }
            if (string.IsNullOrEmpty(sinkTableName))
            {
                throw new BulkInsertEdgeException("The string of Sink table name is null or empty.");
            }
            if (string.IsNullOrEmpty(dataFileName))
            {
                throw new BulkInsertEdgeException("The string of data file name is null or empty.");
            }
            if (string.IsNullOrEmpty(sourceNodeIdName))
            {
                throw new BulkInsertEdgeException("The string of source node Id name is null or empty.");
            }
            if (string.IsNullOrEmpty(sinkNodeIdName))
            {
                throw new BulkInsertEdgeException("The string of Sink node Id name is null or empty.");
            }
            if (!File.Exists(dataFileName))
            {
                throw new BulkInsertEdgeException(string.Format("Data file {0} doesn't exist.", dataFileName));
            }
            if (string.IsNullOrEmpty(tableSchema))
            {
                tableSchema = "dbo";
            }
            if (string.IsNullOrEmpty(fieldTerminator))
            {
                fieldTerminator = "\t";
            }
            if (string.IsNullOrEmpty(rowTerminator))
            {
                rowTerminator = "\r\n";
            }
            
            var transaction = Conn.BeginTransaction();
            var command = Conn.CreateCommand();
            command.CommandTimeout = 0;
            command.Transaction = transaction;
            try
            {
                //Check validity of table name in GraphView(source and Sink node table)
                command.Parameters.Clear();
                const string checkNodeTableCollection = @"
                Select *
                From {0}
                Where (TableName = @tablename and TableSchema = @tableschema)";
                command.CommandText = string.Format(checkNodeTableCollection, MetadataTables[0]); //_NodeTableCollection
                command.Parameters.Add("tableschema", SqlDbType.NVarChar, 128);
                command.Parameters["tableschema"].Value = tableSchema;
                command.Parameters.Add("tablename", SqlDbType.NVarChar, 128);
                command.Parameters["tablename"].Value = sourceTableName;
                using (var reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new BulkInsertNodeException(
                            string.Format(
                                "There doesn't exist the table with tableSchema(\"{0}\") and tableName(\"{1}\")",
                                tableSchema, sourceTableName));
                    }
                }
                command.Parameters["tablename"].Value = sinkTableName;
                using (var reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new BulkInsertNodeException(
                            string.Format(
                                "There doesn't exist the table with tableSchema(\"{0}\") and tableName(\"{1}\")",
                                tableSchema, sinkTableName));
                    }
                }

                //Check validity of edge column name in GraphView
                command.Parameters.Clear();
                const string checkEdgeColumn = @"
                select *
                from {0}
                where (TableName = @tablename and TableSchema = @tableschema and ColumnName = @columnname and Reference = @reference)";
                command.CommandText = string.Format(checkEdgeColumn, MetadataTables[1]); //_NodeTableColumnCollection
                command.Parameters.Add("tableschema", SqlDbType.NVarChar, 128);
                command.Parameters["tableschema"].Value = tableSchema;
                command.Parameters.Add("tablename", SqlDbType.NVarChar, 128);
                command.Parameters["tablename"].Value = sourceTableName;
                command.Parameters.Add("columnname", SqlDbType.NVarChar, 128);
                command.Parameters["columnname"].Value = edgeColumnName;
                command.Parameters.Add("reference", SqlDbType.NVarChar, 128);
                command.Parameters["reference"].Value = sinkTableName;
                using (var reader = command.ExecuteReader())
                {
                    if (!reader.Read())
                    {
                        throw new BulkInsertEdgeException(
                            string.Format("There doesn't exist edge column \"{0}\" or the edge is not from {1} to {2}",
                                edgeColumnName, sourceTableName, sinkTableName));
                    }
                }

                //Record edges' name and datatype in sql
                var userSuppliedEdgeAttributeInfo = new List<Tuple<string, string>>();
                var allEdgeAttributeNameInOrder = new List<string>(); //For UDF's variable reference

                //Get edges' name and datatype in sql
                command.Parameters.Clear();
                const string getEdgeAttribute = @"
                select AttributeName, AttributeType
                from {0} EGA
                where EGA.TableSchema = @tableschema and EGA.TableName = @tablename and EGA.ColumnName = @columnname
                order by EGA.AttributeEdgeId";

                command.Parameters.Add("tableschema", SqlDbType.NVarChar, 128);
                command.Parameters["tableschema"].Value = tableSchema;
                command.Parameters.Add("tablename", SqlDbType.NVarChar, 128);
                command.Parameters["tablename"].Value = sourceTableName;
                command.Parameters.Add("columnname", SqlDbType.NVarChar, 128);
                command.Parameters["columnname"].Value = edgeColumnName;
                command.CommandText = String.Format(getEdgeAttribute, MetadataTables[2]); //_EdgeAttributeCollection 
                if (dataEdgeAttributeName == null)
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var attributeName = reader["AttributeName"].ToString();
                            var attributeDatatype = reader["AttributeType"].ToString();
                            userSuppliedEdgeAttributeInfo.Add(Tuple.Create(attributeName, attributeDatatype.ToLower()));
                            allEdgeAttributeNameInOrder.Add(attributeName);
                        }
                    }
                }
                else
                {
                    var attributeMapping = new Dictionary<string, string>();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var attributeName = reader["AttributeName"].ToString().ToLower();
                            var propetyDatatype = reader["AttributeType"].ToString();
                            attributeMapping[attributeName] = propetyDatatype;
                            allEdgeAttributeNameInOrder.Add(attributeName);
                        }
                    }
                    foreach (var it in dataEdgeAttributeName)
                    {
                        if (attributeMapping.ContainsKey(it.ToLower()))
                        {
                            userSuppliedEdgeAttributeInfo.Add(Tuple.Create(it, attributeMapping[it.ToLower()].ToLower()));
                        }
                        else
                        {
                            throw new BulkInsertEdgeException(
                                string.Format("There doesn't exist edge attribute name \"{0}\"", it));
                        }
                    }
                }

                //Get the name and datatype of user-defined node id of source and Sink table
                command.Parameters.Clear();
                const string getNodeIdDataType = @"
                Select GC.ColumnName, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
                From {0} GC
                Join sys.columns C
                On C.name = GC.ColumnName
                Join sys.tables T
                On C.object_id = T.object_id and T.name = GC.TableName
                Join sys.schemas SC
                On SC.name = GC.TableSchema and SC.schema_id = T.schema_id
                Join INFORMATION_SCHEMA.COLUMNS ISC
                On ISC.TABLE_SCHEMA = GC.TableSchema and ISC.TABLE_NAME = GC.TableName and ISC.COLUMN_NAME = GC.ColumnName
                Where GC.TableSchema = @tableschema and GC.TableName = @tablename and GC.ColumnRole = 2";

                Tuple<string, string, string, string> sourceNodeId; //Name,DataType,NameInTempTable,DataTypeLength
                command.Parameters.Add("tableschema", SqlDbType.NVarChar, 128);
                command.Parameters["tableschema"].Value = tableSchema;
                command.Parameters.Add("tablename", SqlDbType.NVarChar, 128);
                command.Parameters["tablename"].Value = sourceTableName;
                command.CommandText = String.Format(getNodeIdDataType, MetadataTables[1]);
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["ColumnName"].ToString().ToLower() != sourceNodeIdName.ToLower())
                        {
                            throw new BulkInsertEdgeException(
                                string.Format("\"{0}\" is not a node id name of source node table\"{1}\"",
                                    sourceNodeIdName, sourceTableName));
                        }
                        sourceNodeId = Tuple.Create(reader["ColumnName"].ToString(), reader["DATA_TYPE"].ToString(),
                            reader["ColumnName"] + "_src_" + RandomString(), reader["CHARACTER_MAXIMUM_LENGTH"].ToString());
                    }
                    else
                    {
                        throw new BulkInsertEdgeException(
                            string.Format("There doesn't exist a node id in source note table \"{0}\".", sourceTableName));
                    }
                }

                Tuple<string, string, string, string> sinkNodeId; //Name,DataType,NameInTempTable,DataTypeLength
                command.Parameters["tableschema"].Value = tableSchema;
                command.Parameters["tablename"].Value = sinkTableName;
                command.CommandText = String.Format(getNodeIdDataType, MetadataTables[1]);
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (reader["ColumnName"].ToString().ToLower() != sinkNodeIdName.ToLower())
                        {
                            throw new BulkInsertEdgeException(
                                string.Format("\"{0}\" is not a node id name of Sink node table\"{1}\"", sinkNodeIdName,
                                    sinkTableName));
                        }
                        sinkNodeId = Tuple.Create(reader["ColumnName"].ToString(), reader["DATA_TYPE"].ToString(),
                            reader["ColumnName"] + "_sink_" + RandomString(), reader["CHARACTER_MAXIMUM_LENGTH"].ToString());
                    }
                    else
                    {
                        throw new BulkInsertEdgeException(
                            string.Format("There doesn't exist a node id in Sink note table \"{0}\".", sinkTableName));
                    }
                }

                //Create temp table for bulk inserting edge data
                var randomTempTableName = tableSchema + "." + sourceTableName + edgeColumnName + sinkTableName + "_" +
                                          RandomString();
                var attributeNameArray =
                    userSuppliedEdgeAttributeInfo.Select(x => x.Item1.ToString() + " " + typeDictionary[x.Item2].Item1);
                const string createTempTable = @"
                Create table {0}
                (
                    {1},
                    {2},
                    {3}
                )";
                var maxLength = string.IsNullOrEmpty(sourceNodeId.Item4)
                    ? ""
                    : ("(" + (sourceNodeId.Item4 == "-1" ? "max" : sourceNodeId.Item4) + ")");
                var sourceNodeIdColumnInfo = sourceNodeId.Item3 + ' ' + sourceNodeId.Item2 + maxLength;
                maxLength = string.IsNullOrEmpty(sinkNodeId.Item4)
                    ? ""
                    : ("(" + (sinkNodeId.Item4 == "-1" ? "max" : sinkNodeId.Item4) + ")");
                var sinkNodeIdColumnInfo = sinkNodeId.Item3 + ' ' + sinkNodeId.Item2 + maxLength;
                command.CommandText = string.Format(createTempTable, randomTempTableName, sourceNodeIdColumnInfo,
                    sinkNodeIdColumnInfo, string.Join(",\n", attributeNameArray));
                command.ExecuteNonQuery();

                //Bulk insert
                var dataColumnName = new List<string>();
                var columnDataType = new List<string>();
                dataColumnName.Add(sourceNodeId.Item3);
                columnDataType.Add(sourceNodeId.Item2);

                dataColumnName.Add(sinkNodeId.Item3);
                columnDataType.Add(sinkNodeId.Item2);

                foreach (var it in userSuppliedEdgeAttributeInfo)
                {
                   dataColumnName.Add(it.Item1); 
                    columnDataType.Add(typeDictionary[it.Item2.ToLower()].Item2);
                }
                

                using (var sqlBulkCopy = new SqlBulkCopy(Conn, SqlBulkCopyOptions.TableLock, transaction))
                {
                    sqlBulkCopy.BulkCopyTimeout = 0;
                    using (var reader = new BulkInsertFileDataReader(dataFileName, fieldTerminator, rowTerminator,
                            dataColumnName,
                            columnDataType, Header))
                    {
                        foreach (var it in dataColumnName)
                        {
                            sqlBulkCopy.ColumnMappings.Add(it, it);
                        }
                        sqlBulkCopy.DestinationTableName = randomTempTableName;
                        sqlBulkCopy.WriteToServer(reader);
                    }
                }

                //Create clustered index on Sink node in temp table
                string clusteredIndexName = "sinkIndex_" + RandomString(); 
                const string createClusteredIndex = @"
                create clustered index [{0}] on {1}([{2}])";
                command.Parameters.Clear();
                command.CommandText = string.Format(createClusteredIndex, clusteredIndexName, randomTempTableName, sinkNodeId.Item3);
                command.ExecuteNonQuery();

                if (updateMethod)
                {
                    //Update database
                    // ToLower due to case senstivity of Contains
                    var hashSetOfUserSuppliedEdgeAttribute =
                        new HashSet<string>(userSuppliedEdgeAttributeInfo.Select(x => x.Item1.ToLower()));
                    string aggregeteFunctionName = tableSchema + '_' + sourceTableName + '_' + edgeColumnName + '_' + "Encoder";
                    var tempTableForVariable =
                        allEdgeAttributeNameInOrder.Select(
                            x =>
                                string.Format(", {0}",
                                    (hashSetOfUserSuppliedEdgeAttribute.Contains(x.ToLower()) ? ("tempTable.[" + x + "]") : "null")));
                    var tempStringForVariable = string.Join("", tempTableForVariable);
                    string aggregateFunction = aggregeteFunctionName + "([sinkTable].[GlobalNodeId]" +
                                               tempStringForVariable +
                                               ")";
                    const string updateEdgeData = @"
                    Select [{0}].globalnodeid, [GraphView_InsertEdgeInternalTable].binary, [GraphView_InsertEdgeInternalTable].sinkCount into #ParallelOptimalTempTable
					From 
					(
					Select tempTable.[{2}] source, [{3}].{4} as binary, count([sinkTable].[GlobalNodeId]) as sinkCount
                    From {5} tempTable
                    Join [{3}].[{6}] sinkTable
                    On sinkTable.[{7}] = tempTable.[{8}]
                    Group by tempTable.[{2}]
					)
                    as [GraphView_InsertEdgeInternalTable],
					[{3}].[{0}]
                    Where [{0}].[{9}] = [GraphView_InsertEdgeInternalTable].source;
                    UPDATE [{3}].[{0}] SET {1} .WRITE(temp.[binary], null, null), {1}OutDegree += sinkCount
					from #ParallelOptimalTempTable temp
					where temp.globalnodeid = [{0}].globalnodeid;
                    drop table #ParallelOptimalTempTable;";
                    command.Parameters.Clear();
                    command.CommandText = string.Format(updateEdgeData, sourceTableName, edgeColumnName,
                        sourceNodeId.Item3,
                        tableSchema, aggregateFunction, randomTempTableName, sinkTableName, sinkNodeId.Item1,
                        sinkNodeId.Item3, sourceNodeId.Item1);
                    command.ExecuteNonQuery();

                    const string updateReversedEdgeData = @"
                        UPDATE [{3}].[{0}] SET [InDegree] += sourceCount
                        From (
                            Select tempTable.[{1}] as Sink, count(*) as sourceCount
                            From {2} tempTable
                            Join [{5}]
                            On [{5}].[{6}] = tempTable.[{7}]
                            Group by tempTable.[{1}]
                        ) as [GraphView_InsertEdgeInternalTable]
                        Where [GraphView_InsertEdgeInternalTable].Sink = [{0}].[{4}]";
                    command.CommandText = string.Format(updateReversedEdgeData, sinkTableName, sinkNodeId.Item3,
                        randomTempTableName, tableSchema, sinkNodeId.Item1, sourceTableName, sourceNodeId.Item1,
                        sourceNodeId.Item3);
                    command.ExecuteNonQuery();
                }
                else
                {
                    // Rebuild the table instead of the old table.
                    command.Parameters.Clear();
                    command.Parameters.Add("name", SqlDbType.NVarChar, 128);
                    command.Parameters["name"].Value = sourceTableName;
                    command.Parameters.Add("schema", SqlDbType.NVarChar, 128);
                    command.Parameters["schema"].Value = tableSchema;

                    //Record default value constrain of source table
                    var defaultConstrainInfo = new List<Tuple<string, string, string>>();
                    //list<DefaultConstrainName, ColumnName, DefaultValue> 
                    command.CommandText = @"
                    select DC.name as DC, C.name, definition
                    from sys.tables T
                    join sys.schemas SC
                    on SC.name = @schema and SC.schema_id = T.schema_id
                    join sys.default_constraints DC
                    on DC.parent_object_id = T.object_id and T.name = @name
                    join sys.columns C
                    on C.column_id = DC.parent_column_id and C.object_id = T.object_id";
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            defaultConstrainInfo.Add(Tuple.Create(reader["DC"].ToString(), reader["name"].ToString(),
                                reader["definition"].ToString()));
                        }
                        reader.Close();
                    }

                    //Record information of indexes of source table.
                    var indexInfo = new List<IndexOrConstraintInformationRecord>();
                    command.CommandText = @"
                    select K.name indexname, K.index_id, K.type_desc, K.is_unique, K.is_primary_key, C.name referencename
                    from sys.tables T
                    join sys.schemas SC
                    on SC.name = @schema and SC.schema_id = T.schema_id
                    join sys.indexes K
                    on T.object_id = K.object_id and T.name = @name 
                    join sys.index_columns I
                    on I.object_id = K.object_id and I.index_id = K.index_id
                    join sys.columns C
                    on I.object_id = C.object_id and I.column_id = C.column_id
                    order by K.index_id, I.index_id";

                    using (var reader = command.ExecuteReader())
                    {
                        int indexId = -1;
                        while (reader.Read())
                        {
                            if (reader["indexname"].ToString() != "")
                            {
                                var columnIndexId = Convert.ToInt32(reader["index_id"].ToString());
                                if (indexId != columnIndexId)
                                {
                                    indexInfo.Add(
                                        new IndexOrConstraintInformationRecord
                                        {
                                            name = reader["indexname"].ToString(),
                                            cluseterType = reader["type_desc"].ToString(),
                                            isUnique = (reader["is_unique"].ToString() == "True"),
                                            isPrimaryKey = (reader["is_primary_key"].ToString() == "True"),
                                            isConstraint = 0,
                                            indexColumns = new List<string>()
                                        });
                                    indexId = columnIndexId;
                                }
                                indexInfo.Last().indexColumns.Add(reader["referencename"].ToString());
                            }
                        }
                        reader.Close();
                    }

                    command.CommandText = @"
                    select KC.name 
                    from sys.key_constraints KC
                    join sys.schemas SC
                    on SC.schema_id  = KC.schema_id and SC.name = @schema 
                    join sys.tables T
                    on T.schema_id = SC.schema_id and T.name = @name and T.object_id  = KC.parent_object_id";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            foreach (var it in indexInfo)
                            {
                                var str = reader["name"].ToString();
                                if (str.ToLower() == it.name.ToLower())
                                {
                                    it.isConstraint = 1;
                                }
                            }
                        }
                        reader.Close();
                    }

                    //Get the column name of the source table.
                    var allColumnsInNewTable = new List<string>();
                    var allColumnsInNewTable2 = new List<string>();
                    const string getColumnNameInTable = @"
                    select C.name
                    from sys.schemas SC
                    join sys.tables T
                    on SC.schema_id = T.schema_id
                    join sys.columns C
                    on C.object_id = T.object_id
                    where SC.name = @tableschema and T.name = @tablename 
                    order by C.column_id ";
                    command.Parameters.Clear();
                    command.Parameters.Add("tableschema", SqlDbType.NVarChar, 128);
                    command.Parameters["tableschema"].Value = tableSchema;
                    command.Parameters.Add("tablename", SqlDbType.NVarChar, 128);
                    command.Parameters["tablename"].Value = sourceTableName;
                    command.CommandText = getColumnNameInTable;
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var column = reader["name"].ToString();
                            if (column.ToLower() == edgeColumnName.ToLower())
                            {
                                allColumnsInNewTable2.Add("[" + column + "]");
                                column =
                                    string.Format(
                                        "ISNULL(ISNULL([{0}], 0x) + [GraphView_InsertEdgeInternalTable].[binary], 0x) as [{0}]",
                                        edgeColumnName);
                                allColumnsInNewTable.Add(column);
                            }
                            else if (column.ToLower() == edgeColumnName.ToLower() + "outdegree")
                            {
                                allColumnsInNewTable2.Add("[" + column + "]");
                                column =
                                    string.Format(
                                        "ISNULL(([{0}] + [GraphView_InsertEdgeInternalTable].sinkCount), 0) as [{0}]",
                                        column);
                                allColumnsInNewTable.Add(column);
                            }
                            else
                            {
                                allColumnsInNewTable.Add("[" + sourceTableName + "].[" + column + "]");
                                allColumnsInNewTable2.Add("[" + column + "]");
                            }
                        }
                    }
                    var hashSetOfUserSuppliedEdgeAttribute =
                        new HashSet<string>(userSuppliedEdgeAttributeInfo.Select(x => x.Item1));
                    string aggregeteFunctionName = tableSchema + '_' + sourceTableName + '_' + edgeColumnName + '_' + "Encoder";
                    var tempTableForVariable =
                        allEdgeAttributeNameInOrder.Select(
                            x =>
                                string.Format(", {0}",
                                    (hashSetOfUserSuppliedEdgeAttribute.Contains(x) ? ("tempTable.[" + x + "]") : "null")));
                    var tempStringForVariable = string.Join("", tempTableForVariable);
                    string aggregateFunction = aggregeteFunctionName + "(sinkTable.[GlobalNodeId]" +
                                               tempStringForVariable +
                                               ")";
                    string tempSourceTableName = "tempSourceTable_" + RandomString();

                    //Build new source node table. 
                    const string createTempSourceTable = @"
                    select *
                    into [{0}].[{1}]
                    from [{0}].[{2}]
                    where 1 != 1;
                    SET IDENTITY_INSERT [{0}].[{1}] ON";
                    command.Parameters.Clear();
                    command.CommandText = string.Format(createTempSourceTable, tableSchema, tempSourceTableName,
                        sourceTableName);
                    command.ExecuteNonQuery();

                    const string insertTempSourceTable = @"
                    Select tempTable.[{2}] source, [{3}].{4} as binary, Count([sinkTable].[GlobalNodeId]) as sinkCount into #ParallelOptimalTempTable
                    From {5} tempTable
                    Join [{3}].[{6}] sinkTable
                    On sinkTable.[{7}] = tempTable.[{8}]
                    Group by tempTable.[{2}];

                    insert into [{3}].[{10}] ({11})
                    select {1}
                    From #ParallelOptimalTempTable
                    as [GraphView_InsertEdgeInternalTable]
                    right join [{3}].[{0}]
                    on [{0}].[{9}] = [GraphView_InsertEdgeInternalTable].source;
                    drop table #ParallelOptimalTempTable";
                    command.Parameters.Clear();
                    command.CommandText = string.Format(insertTempSourceTable, sourceTableName,
                        string.Join(",", allColumnsInNewTable), sourceNodeId.Item3, tableSchema, aggregateFunction,
                        randomTempTableName, sinkTableName, sinkNodeId.Item1, sinkNodeId.Item3, sourceNodeId.Item1,
                        tempSourceTableName, string.Join(",", allColumnsInNewTable2));
                    command.ExecuteNonQuery();

                    //Drop the old source node table and rename the new source table
                    const string dropTable = @"
                        drop table {0}";
                    command.Parameters.Clear();
                    command.CommandText = string.Format(dropTable, sourceTableName);
                    command.ExecuteNonQuery();
                    const string turnOffIdentityInsert = @"
                    SET IDENTITY_INSERT [{0}].[{1}] OFF";
                    command.CommandText = string.Format(turnOffIdentityInsert, tableSchema, tempSourceTableName);
                    command.ExecuteNonQuery();
                    const string changeTempTableName = @"
                    sp_rename '[{0}].[{1}]', '{2}'; ";
                    command.CommandText = string.Format(changeTempTableName, tableSchema, tempSourceTableName,
                        sourceTableName);
                    command.ExecuteNonQuery();

                    //Rebuild indexes or constraint in the new source node table
                    command.Parameters.Clear();
                    const string buildUnique = @"
                    ALTER TABLE {0}
                    ADD constraint {1}
                    {2}({3})";
                    const string buildIndex = @"
                    Create {0} {1} index {2}
                    on {3}({4})";
                    var tableNameWithSchema = "[" + tableSchema + "].[" + sourceTableName + "]";

                    foreach (var it in indexInfo)
                    {
                        if (it.isPrimaryKey || it.isConstraint == 1) //primary key or unique constraint
                        {
                            var type = it.isPrimaryKey ? "Primary key" : "Unique";
                            command.CommandText = string.Format(buildUnique, tableNameWithSchema,
                                it.name, type, string.Join(",", it.indexColumns));
                        }
                        else if (it.isConstraint == 0) //index
                        {
                            var unique = it.isUnique ? "unique" : "";
                            command.CommandText = string.Format(buildIndex, unique, it.cluseterType,
                                it.name, tableNameWithSchema, string.Join(",", it.indexColumns));
                        }
                        command.ExecuteNonQuery();
                    }

                    //Rebuild default constrain of source table
                    command.Parameters.Clear();
                    const string defaultConstrainRebuild = @"
                    ALTER TABLE [{0}].[{1}] ADD CONSTRAINT [{2}] DEFAULT ({3}) FOR [{4}]";
                    foreach (var it in defaultConstrainInfo)
                    {
                        command.CommandText = string.Format(defaultConstrainRebuild, tableSchema, sourceTableName,
                            it.Item1, it.Item3, it.Item2);
                        command.ExecuteNonQuery();
                    }

                    const string updateReversedEdgeData = @"
                        UPDATE [{3}].[{0}] SET [InDegree] += sourceCount
                        From (
                            Select tempTable.[{1}] as Sink, count(*) as sourceCount
                            From {2} tempTable
                            Join [{5}]
                            On [{5}].[{6}] = tempTable.[{7}]
                            Group by tempTable.[{1}]
                        ) as [GraphView_InsertEdgeInternalTable]
                        Where [GraphView_InsertEdgeInternalTable].Sink = [{0}].[{4}]";
                    command.CommandText = string.Format(updateReversedEdgeData, sinkTableName, sinkNodeId.Item3,
                        randomTempTableName, tableSchema, sinkNodeId.Item1, sourceTableName, sourceNodeId.Item1,
                        sourceNodeId.Item3);
                    command.ExecuteNonQuery();
                }

                //Drop temp table
                var dropTempTable = @"Drop table {0}";
                command.CommandText = string.Format(dropTempTable, randomTempTableName);
                command.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception error)
            {
                transaction.Rollback();
                throw new BulkInsertEdgeException(error.Message);
            }
        }
Esempio n. 3
0
 //After assigning LabelColumn, gets the the list of label in the file
 protected void getLabel()
 {
     var dataColumnName = new List<string>();
     var columnDataType = new List<string>();
     int fieldNumber = FileHeader.Count;
     for (int i = 0; i < fieldNumber; i++)
     {
         columnDataType.Add("nvarchar");
         dataColumnName.Add(RandomString());
     }
     var labelDict =  new Dictionary<string, bool>();
     using (var reader = new BulkInsertFileDataReader(FileName, FieldTerminator, RowTerminator,
         dataColumnName, columnDataType, true))
     {
         if (SkipScanLabel)
         {
             if (reader.Read())
             {
                 labelDict[reader.GetValue(LabelOffset).ToString().ToLower()] = true;
             }
         }
         else
         {
             while (reader.Read())
             {
                 labelDict[reader.GetValue(LabelOffset).ToString().ToLower()] = true;
             }
         }
     }
     Labels = labelDict.Select(x => x.Key).ToList();
 }