示例#1
0
        // Find an items in this map
        internal ColumnMap FindItemsByTargetName(string toFind)
        {
            ColumnMap itemFound = null;

            foreach (ColumnMap cm in columnMap)
            {
                if (cm.targetColumnName.Trim().Equals(toFind.Trim()))
                {
                    itemFound = cm;
                    break;
                }
            }
            return(itemFound);
        }
示例#2
0
        public static string FindFKValueInOther(string lookupValue, ColumnMap cmap, SqlConnection connection, bool genNewFK, string lookupColName)
        {

            string fkTable = cmap.fkRelationTable;
            string fkColumnKey = cmap.fkRelationColumn;

            if (lookupColName == null)
            {
                lookupColName = cmap.targetColumnName;
            }


            string statement1 = "SELECT " + fkColumnKey + " FROM " + fkTable + " WHERE " + lookupColName + " = \'" + lookupValue +"\'";

            SqlCommand sqc = new SqlCommand(statement1, connection);
            SqlDataReader reader = sqc.ExecuteReader();
            List<string> results = new List<string>();
            while (reader.Read())
            {
                string fkName = reader[0].ToString();
                results.Add(fkName);
            }
            reader.Close();

            if (results.Count == 0 && genNewFK == true)
            {
                //    // there is no matching entry in this dictionary.  Make a new entry
                Guid gg = Guid.NewGuid();

                string p1 = gg.ToString();
                results.Add(p1);

                string query = "INSERT INTO " + fkTable + " (" + fkColumnKey + ","+lookupColName+") VALUES " +
                            " (\'" + gg.ToString() + "\',\'" + lookupValue + "\' )";

                SqlCommand sqc2 = new SqlCommand(query, connection);
                sqc2.ExecuteNonQuery();

            }
            string res = null;
            if (results.Count > 0)
            {
                res = results.First();
            }
            return res;
        }
示例#3
0
        const int dictLen = 12; //i.e. X_Dictionary
        public static string FindFKValueInDictionary(string columnValue, ColumnMap cmap, SqlConnection connection, bool genNewFK)
        {
            // first check for an empty lookup - no point in doing an empty lookup or inserting blanks into the dictionaries
            if (columnValue == null || columnValue.Trim().Length == 0)
            {

                return null;
            }
            bool doStandardLookupFirst = false;
            string fkTable = cmap.fkRelationTable;
            string fkColumnKey = cmap.fkRelationColumn;
            string nameLookupColumnPrediction = "";
            string stdLookupColumnPrediction = "";
            List<string> results = new List<string>();
            // if the FK table is not a standard dictionary, have a go at finding enties in the FK table
            // using typical predicatable naming
            if (fkTable == null || !fkTable.StartsWith("Dictionary"))
            {
                string lookupColName = "Standard" + fkTable.Substring(dictLen) + "Name";
                //string lookupColName = fkTable.Substring("".Length) + "Name";
                string guid = FindFKValueInOther(columnValue, cmap, connection, genNewFK, lookupColName);
                results.Add(guid);

            }
            else
            {

                string partA = fkTable.Substring("Dictionary".Length);
                nameLookupColumnPrediction = "Custom" + partA + "Name";
                stdLookupColumnPrediction = "Standard" + partA + "Name";

              
                if (doStandardLookupFirst)
                {

                    string statement1 = "SELECT " + fkColumnKey + " FROM " + fkTable + " WHERE " + stdLookupColumnPrediction + " = \'" + columnValue + "\'";

                    SqlCommand sqc = new SqlCommand(statement1, connection);
                    SqlDataReader reader = sqc.ExecuteReader();

                    while (reader.Read())
                    {
                        string fkName = reader[0].ToString();
                        results.Add(fkName);
                    }
                    reader.Close();
                }

                if (results == null || results.Count == 0)
                {

                    string statement1 = "SELECT " + fkColumnKey + " FROM " + fkTable + " WHERE " + nameLookupColumnPrediction + " = \'" + columnValue + "\'";

                    SqlCommand sqc = new SqlCommand(statement1, connection);
                    SqlDataReader reader = sqc.ExecuteReader();
                    results = new List<string>();
                    while (reader.Read())
                    {
                        string fkName = reader[0].ToString();
                        results.Add(fkName);
                    }
                    reader.Close();
                }
                if (results.Count == 0 && genNewFK == true)
                {
                    // there is no matching entry in this dictionary.  Make a new entry
                    Guid gg = Guid.NewGuid();

                    string p1 = gg.ToString();
                    results.Add(p1);

                    char[] splitters = { '-' };
                    string[] items = p1.Split(splitters);
                    string p2 = "";
                    foreach (string it in items)
                    {
                        p2 += it;
                    }
                    //string stdValMock = p2;
                    p2 = columnValue + "-" + p2;
                    if (p2.Length > 15)
                    {
                        p2 = p2.Substring(0, 15);
                    }
                    string query = "INSERT INTO " + fkTable + " (" + fkColumnKey + "," + stdLookupColumnPrediction + "," + nameLookupColumnPrediction + ") VALUES " +
                                    " (\'" + gg.ToString() + "\',\'" + p2 + "\',\'" + columnValue + "\' )";

                    SqlCommand sqc2 = new SqlCommand(query, connection);
                    sqc2.ExecuteNonQuery();

                }
            }
            string res = null;
            if (results.Count > 0) {
                res = results.First();
            }
            return res;
        }
示例#4
0
        public static string FindFKValueInOther(string lookupValue, ColumnMap cmap, SqlConnection connection, bool genNewFK, string lookupColName, Guid NKDProjectID)
        {

            string fkTable = cmap.fkRelationTable;
            string fkColumnKey = cmap.fkRelationColumn;

            if (lookupColName == null)
            {
                lookupColName = cmap.targetColumnName;
            }


            string statement1 = "SELECT " + fkColumnKey + " FROM " + fkTable + " WHERE " + lookupColName + " = \'" + lookupValue + "\' AND ProjectID = \'"+NKDProjectID.ToString()+"\'";

            SqlCommand sqc = new SqlCommand(statement1, connection);
            SqlDataReader reader = sqc.ExecuteReader();
            List<string> results = new List<string>();
            while (reader.Read())
            {
                string fkName = reader[0].ToString();
                results.Add(fkName);
            }
            reader.Close();

            if (results.Count == 0 && genNewFK == true)
            {
            //    // there is no matching entry in this dictionary.  Make a new entry
            //    Guid gg = Guid.NewGuid();

            //    string p1 = gg.ToString();
            //    results.Add(p1);

            //    char[] splitters = { '-' };
            //    string[] items = p1.Split(splitters);
            //    string p2 = "";
            //    foreach (string it in items)
            //    {
            //        p2 += it;
            //    }
            //    string stdValMock = p2;
            //    if (p2.Length > 15)
            //    {
            //        p2 = p2.Substring(0, 15);
            //    }
            //    string query = "INSERT INTO " + fkTable + " (" + fkColumnKey + "," + stdLookupColumnPrediction + "," + nameLookupColumnPrediction + ") VALUES " +
            //                    " (\'" + gg.ToString() + "\',\'" + p2 + "\',\'" + columnValue + "\' )";

            //    SqlCommand sqc2 = new SqlCommand(query, connection);
            //    sqc2.ExecuteNonQuery();

            }
            string res = null;
            if (results.Count > 0)
            {
                res = results.First();
            }
            return res;
        }
示例#5
0
        private string GetPreviousDefaultFromMap(ImportDataMap oldMap, ColumnMap mi)
        {

            string defaultVal = "";
            if (oldMap != null)
            {
                foreach (ColumnMap cm in oldMap.columnMap)
                {
                    if (cm.sourceColumnName.Trim().Equals(mi.sourceColumnName.Trim()))
                    {
                        defaultVal = cm.defaultValue;
                        break;
                    }
                }
            }

            return defaultVal;
        }
示例#6
0
        private string GetPreviousTypeFromMap(ImportDataMap oldMap, ColumnMap mi)
        {

            string importDataType = mi.importDataType;
            if (oldMap != null)
            {
                foreach (ColumnMap cm in oldMap.columnMap)
                {
                    if (cm.sourceColumnName.Trim().Equals(mi.sourceColumnName.Trim()))
                    {
                        importDataType = cm.importDataType;
                        break;
                    }
                }
            }

            return importDataType;
        }