private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
        {
            DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);

            var schemaColumns = databaseSchemaHelper.GetColumns(tableName);
            string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();

            string joinedColumnNames = string.Join(", ", columnNames);
            int numColumns = columnNames.Length;

            // Load the .unl file (renamed to .txt and added to the project as a resource file).
            string fileContents = Resources.Resources.ResourceManager.GetString(tableName);
            string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);

            string[] placeHolders = new string[numColumns];
            for (int i = 0; i < numColumns; i++) {
                placeHolders[i] = "?";
            }
            string joinedPlaceHolders = string.Join(", ", placeHolders);

            using (var cmd = cn.CreateCommand()) {

                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);

                for (int i = 0; i < numColumns; i++) {
                    cmd.Parameters.Add(columnNames[i], null);
                }

                foreach (var line in lines) {

                    string[] values = line.Split('|');

                    // "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
                    // i.e. there is an empty extra value at the end of the array.
                    // And, there is usually a blank line at the end of the file.
                    if (values.Length != (numColumns + 1)) {
                        break;
                    }

                    for (int i = 0; i < numColumns; i++) {
                        if (!string.IsNullOrEmpty(values[i])) {
                            // This is a temporary hack for the "lead_time" column of the "manufact" table.
                            // We should lookup the coltype and collength of the column
                            // using tableName and schemaColumns[i].ActualName. Then, if coltype
                            // is 14 (INTERVAL) then we need to look at collength to determine the
                            // detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
                            // or IfxMonthSpan and parse the string, values[i]. There may be other column types
                            // that need to be handled specially. I guess we need to test lots more
                            // different column types.
                            //
                            // SYSCOLUMNS              
                            //  http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
                            //
                            // Storing Column Length    
                            //  http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
                            //
                            // Using the Informix System Catalogs
                            //  http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
                            //
                            // collength = 836 = 0x344
                            // length = 3
                            // first_qualifier = 4
                            // last_qualifier = 4
                            //
                            if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time") {
                                cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
                            }
                            else {
                                cmd.Parameters[i].Value = values[i];
                            }
                        }
                        else {
                            cmd.Parameters[i].Value = DBNull.Value;
                        }
                    }

                    cmd.ExecuteNonQuery();
                }
            }
        }
Exemplo n.º 2
0
        private static void LoadRecords(IfxConnection cn, string connectionString, string tableName)
        {
            DatabaseSchemaHelper databaseSchemaHelper = new DatabaseSchemaHelper(connectionString);

            var schemaColumns = databaseSchemaHelper.GetColumns(tableName);

            string[] columnNames = (from schemaColumn in schemaColumns select schemaColumn.ActualName).ToArray();

            string joinedColumnNames = string.Join(", ", columnNames);
            int    numColumns        = columnNames.Length;

            // Load the .unl file (renamed to .txt and added to the project as a resource file).
            string fileContents = Resources.Resources.ResourceManager.GetString(tableName);

            string[] lines = fileContents.Split(new[] { Environment.NewLine }, StringSplitOptions.None);

            string[] placeHolders = new string[numColumns];
            for (int i = 0; i < numColumns; i++)
            {
                placeHolders[i] = "?";
            }
            string joinedPlaceHolders = string.Join(", ", placeHolders);

            using (var cmd = cn.CreateCommand()) {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format("insert into {0} ({1}) values ({2})", tableName, joinedColumnNames, joinedPlaceHolders);

                for (int i = 0; i < numColumns; i++)
                {
                    cmd.Parameters.Add(columnNames[i], null);
                }

                foreach (var line in lines)
                {
                    string[] values = line.Split('|');

                    // "value1|value2|value3|" after splitting on '|' yields "value1", "value2", value3, ""
                    // i.e. there is an empty extra value at the end of the array.
                    // And, there is usually a blank line at the end of the file.
                    if (values.Length != (numColumns + 1))
                    {
                        break;
                    }

                    for (int i = 0; i < numColumns; i++)
                    {
                        if (!string.IsNullOrEmpty(values[i]))
                        {
                            // This is a temporary hack for the "lead_time" column of the "manufact" table.
                            // We should lookup the coltype and collength of the column
                            // using tableName and schemaColumns[i].ActualName. Then, if coltype
                            // is 14 (INTERVAL) then we need to look at collength to determine the
                            // detailed type of INTERVAL. Then, create a new instance of IfxTimeSpan
                            // or IfxMonthSpan and parse the string, values[i]. There may be other column types
                            // that need to be handled specially. I guess we need to test lots more
                            // different column types.
                            //
                            // SYSCOLUMNS
                            //  http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_025.htm
                            //
                            // Storing Column Length
                            //  http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqlr.doc%2Fids_sqr_027.htm
                            //
                            // Using the Informix System Catalogs
                            //  http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0305parker/0305parker.html
                            //
                            // collength = 836 = 0x344
                            // length = 3
                            // first_qualifier = 4
                            // last_qualifier = 4
                            //
                            if (tableName == "manufact" && schemaColumns[i].ActualName == "lead_time")
                            {
                                cmd.Parameters[i].Value = new IfxTimeSpan(Convert.ToInt32(values[i]), IfxTimeUnit.Day);
                            }
                            else
                            {
                                cmd.Parameters[i].Value = values[i];
                            }
                        }
                        else
                        {
                            cmd.Parameters[i].Value = DBNull.Value;
                        }
                    }

                    cmd.ExecuteNonQuery();
                }
            }
        }