} // function end

        /// <summary>
        /// To Load data in database
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void load_files_Click(object sender, EventArgs e)
        {
            if (Source_Directory_path != null)
            {
                Stack <string> dirs = new Stack <string>(20);

                if (!System.IO.Directory.Exists(Source_Directory_path.ToString()))
                {
                    // Edit this exception
                    throw new ArgumentException();
                }
                dirs.Push(Source_Directory_path.ToString());

                while (dirs.Count > 0)
                {
                    string   currentDir = dirs.Pop();
                    string[] subDirs;
                    try
                    {
                        subDirs = System.IO.Directory.GetDirectories(currentDir);
                    }
                    // An UnauthorizedAccessException exception will be thrown if we do not have
                    // discovery permission on a folder or file. It may or may not be acceptable
                    // to ignore the exception and continue enumerating the remaining files and
                    // folders. It is also possible (but unlikely) that a DirectoryNotFound exception
                    // will be raised. This will happen if currentDir has been deleted by
                    // another application or thread after our call to Directory.Exists. The
                    // choice of which exceptions to catch depends entirely on the specific task
                    // you are intending to perform and also on how much you know with certainty
                    // about the systems on which this code will run.
                    catch (UnauthorizedAccessException ee)
                    {
                        status.Text = (ee.Message);
                        continue;
                    }
                    catch (System.IO.DirectoryNotFoundException ee)
                    {
                        status.Text = (ee.Message);
                        continue;
                    }

                    string[] files = null;
                    try
                    {
                        files = System.IO.Directory.GetFiles(currentDir);
                    }

                    catch (UnauthorizedAccessException ee)
                    {
                        status.Text = (ee.Message);
                        continue;
                    }

                    catch (System.IO.DirectoryNotFoundException ee)
                    {
                        status.Text = (ee.Message);
                        continue;
                    }
                    // Perform the required action on each file here.
                    // Modify this block to perform your required task.
                    foreach (string file in files)
                    {
                        try
                        {
                            //string[] lines = System.IO.File.ReadAllLines(file);
                            richTextBox1.Text += file + "   \n";


                            /////////// Code to Load Data Start /////////////////


                            /* using (Stream s =   new StreamReader(@"C:\TestData.txt")   )
                             * // convert string to stream
                             * // byte[] byteArray = Encoding.UTF8.GetBytes(fi);
                             * // byte[] byteArray = Encoding.ASCII.GetBytes(contents);
                             * // MemoryStream stream = new MemoryStream(byteArray);
                             * // StreamReader d = new StreamReader(file.ToString());*/
                            //                      using (Stream s = d.BaseStream)
                            //                      {



                            // This code here Load a single file data and upload it to database
                            //BulkUploadToSql myData = new BulkUploadToSql(Path.GetFileNameWithoutExtension(file), 10000);

                            BulkUploadToSql myData = BulkUploadToSql.Load(file, Path.GetFileNameWithoutExtension(file), 10000, currentDir);
                            myData.Flush(comboBox1.SelectedItem.ToString()); // this code send the database name
                                                                             //} // using end

                            /////////// Code to Load Data Start /////////////////



                            // Perform whatever action is required in your scenario.
                            //System.IO.FileInfo fi = new System.IO.FileInfo(file);
                            //Console.WriteLine("{0}: {1}, {2}", fi.Name, fi.Length, fi.CreationTime);
                        } // try end

                        catch (System.IO.FileNotFoundException ee)
                        {
                            // If file was deleted by a separate application
                            //  or thread since the call to TraverseTree()
                            // then just continue.
                            status.Text = (ee.Message);
                            continue;
                        } // catch end
                    }     // foreach end

                    // Push the subdirectories onto the stack for traversal.
                    // This could also be done before handing the files.
                    foreach (string str in subDirs)
                    {
                        dirs.Push(str);
                    }
                } // while end
            }     // Source_Directory_path condition end
        }         // function end
Exemple #2
0
        // end string to date



        /// <summary>
        /// In this function Data will read from the file
        /// added to the data table
        /// In the end an object will be returned having the data in it
        /// </summary>
        /// <param name="dataSource"></param>
        /// <returns></returns>



        public static BulkUploadToSql Load(string file_Path, string file_Name, int Commit_Size, string folder_Name)
        {
            // create a new object to return


            BulkUploadToSql o      = new BulkUploadToSql();
            int             index  = 0;
            string          value  = null; // THis will be used to get person name
            string          value2 = null; // THis will be used to get person father name

            o.tableName       = file_Name; // Just the filename, it is not used here
            o.commitBatchSize = Commit_Size;

            string FolderName = new DirectoryInfo(System.IO.Path.GetDirectoryName(file_Path)).Name; // Folder name of the file

            o.folder_Name = FolderName;                                                             // storing folder name in Static object of class BulkUploadToSql

            string extension = Path.GetExtension(file_Path);                                        // Extension of the files



            // replace the code below
            // with your custom logic
            // code to Load data from file
            /////////////////////  MY CODE START ///////////////////////////

            if (extension.Equals(".xlsx")) // Check for excel files
            {
                DataSet ds = new DataSet();
                string  connectionString = GetConnectionString(file_Path);           // function get file path return connection string for that file

                using (OleDbConnection conn = new OleDbConnection(connectionString)) // Making connecton
                {
                    conn.Open();                                                     // opening conection
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = conn;

                    // Get all Sheets in Excel File
                    DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    // Loop through all Sheets to get data
                    foreach (DataRow dr in dtSheet.Rows)
                    {
                        string sheetName = dr["TABLE_NAME"].ToString();

                        if (!sheetName.EndsWith("$"))
                        {
                            continue;
                        }

                        // Get all rows from the Sheet
                        cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

                        DataTable dt = new DataTable();
                        dt.TableName = sheetName;

                        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                        da.Fill(dt); // filling data table with data adapter

                        // GENDER IDENTIFICATION USING SEX MACHINE
                        char[] delimiters = new char[] { ' ' };
                        using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String.txt"))
                        {
                            for (int i = 0; i < dt.Rows.Count; i++) // loop to iterate through all the rows
                            {
                                if (dt.Rows[i][2].ToString().ToLower().Contains("s/o"))
                                {
                                    index  = dt.Rows[i][2].ToString().ToLower().IndexOf("s/o"); // to get index of the S/0 for seperation of name
                                    value  = dt.Rows[i][2].ToString().Substring(0, index);      // person name
                                    value2 = dt.Rows[i][2].ToString().Substring(index + 3);     // father name
                                }
                                else
                                {
                                    value  = dt.Rows[i][2].ToString().Substring(0);
                                    value2 = dt.Rows[i][2].ToString().Substring(0);
                                }


                                string[] parts = value.Split(delimiters, StringSplitOptions.RemoveEmptyEntries); // to seperate first name from person name
                                file.WriteLine(parts[0]);                                                        // writing firstname for gender identification
                            }  // for end

                            file.Close(); // closing string.txt file
                        } // using  end
                        // Starting a new process to run python code
                        System.Diagnostics.Process proc = new System.Diagnostics.Process();
                        proc.EnableRaisingEvents = false;
                        proc.StartInfo.FileName  = @"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\pythonFile.bat";
                        proc.Start();
                        proc.WaitForExit();
                        proc.Close();
                        // Reading all the gender from the string1.txt file that was generated by the python code
                        string[] gender = System.IO.File.ReadAllLines(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String1.txt");
                        // END GENDER IDENTIFICATION USING SEX MACHINE

                        for (int i = 0; i < dt.Rows.Count; i++) // loop to iterate through all the rows
                        {
                            // To seperate Father Name
                            if (dt.Rows[i][2].ToString().ToLower().Contains("s/o"))
                            {
                                index  = dt.Rows[i][2].ToString().ToLower().IndexOf("s/o"); // to get index of the S/0 for seperation of name
                                value  = dt.Rows[i][2].ToString().Substring(0, index);      // person name
                                value2 = dt.Rows[i][2].ToString().Substring(index + 3);     // father name
                            }
                            else
                            {
                                value  = dt.Rows[i][2].ToString().Substring(0);
                                value2 = dt.Rows[i][2].ToString().Substring(0);
                            }

                            // End to seperate Father Name
                            string gender1 = gender[i].ToString();                                                  // getting gender
                            if (gender1.ToString().ToLower() != "female" && gender1.ToString().ToLower() != "male") // if gender neither male or female
                            {
                                gender1 = "UNKNOWN";
                            }
                            MyRecord rec = new MyRecord(
                                dt.Rows[i][0].ToString(),             // getting column values
                                dt.Rows[i][1].ToString(),
                                //                                          dt.Rows[i][2].ToString().Substring(0, index),
                                //                                          dt.Rows[i][2].ToString().Substring(index),
                                value,
                                value2,
                                gender1,
                                dt.Rows[i][3].ToString(),
                                dt.Rows[i][4].ToString(),
                                dt.Rows[i][5].ToString(),
                                dt.Rows[i][6].ToString(),
                                dt.Rows[i][7].ToString(),
                                dt.Rows[i][8].ToString(),
                                dt.Rows[i][9].ToString(),
                                dt.Rows[i][10].ToString(),
                                dt.Rows[i][11].ToString(),
                                dt.Rows[i][12].ToString(),
                                dt.Rows[i][13].ToString(),
                                dt.Rows[i][14].ToString(),
                                dt.Rows[i][15].ToString(),
                                dt.Rows[i][16].ToString(),
                                dt.Rows[i][17].ToString(),
                                dt.Rows[i][18].ToString(),
                                dt.Rows[i][19].ToString(),
                                dt.Rows[i][20].ToString(),
                                dt.Rows[i][21].ToString(),
                                dt.Rows[i][22].ToString(),
                                dt.Rows[i][23].ToString()
                                );
                            o.internalStore.Add(rec); // storing object
                        } // for end
                    } // foreach end

                    cmd = null;
                    conn.Close();
                } //using end


                //////////////////////////// END CODE TO READ XLXS FILE ////////////////////////////
            }    // File Format if end

            else // FOr text files
            {
                // File reading from a datasource
                string[] lines = System.IO.File.ReadAllLines(file_Path.ToString());
                string   dd    = lines.Length.ToString();

                char[] delimiters = new char[] { '|', ',', '$' }; // delimeters to parse the line


                // START GENDER IDENTIFICATION CODE
                char[] delimiters2 = new char[] { ' ' }; // delimeter to sepeate firstname
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String.txt"))
                {
                    for (int cnt = 0; cnt < Int32.Parse(dd); cnt++)
                    {
                        string[] parts = lines[cnt].Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
                        index = 0;
                        // To seperate Father Name
                        if (parts[2].ToLower().Contains("s/o"))
                        {
                            index  = parts[2].ToLower().IndexOf("s/o"); //index to seperate person and father name
                            value  = parts[2].Substring(0, index);      // person name
                            value2 = parts[2].Substring(index + 3);     // father name
                        }
                        else
                        {
                            value  = parts[2].Substring(0);
                            value2 = parts[2].Substring(0);
                        }

                        string [] parts2 = value.Split(delimiters2, StringSplitOptions.RemoveEmptyEntries); // getting first name
                        file.WriteLine(parts2[0]);                                                          // writing firstname in file string.txt for gender identification
                    } // for end
                    file.Close();
                }
                // Starting python code for gender identification
                System.Diagnostics.Process proc = new System.Diagnostics.Process();
                proc.EnableRaisingEvents = false;
                proc.StartInfo.FileName  = @"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\pythonFile.bat";
                proc.Start();
                proc.WaitForExit();
                proc.Close();
                // reading all the genders
                string[] gender = System.IO.File.ReadAllLines(@"C:\Users\john\Downloads\QueryBuilder\QueryBuilder\QueryBuilder\SexMachine-0.1.1\String1.txt");
                // END GENDER IDENTIFICATION CODE



                /////////////////////  MY CODE END ///////////////////////////
                for (int cnt = 0; cnt < Int32.Parse(dd); cnt++)
                {
                    string[] parts = lines[cnt].Split(delimiters, StringSplitOptions.RemoveEmptyEntries); // to read



                    index = 0;
                    // To seperate Father Name
                    if (parts[2].ToLower().Contains("s/o"))
                    {
                        index  = parts[2].ToLower().IndexOf("s/o"); // index for seratiing person and father name
                        value  = parts[2].Substring(0, index);      // person name
                        value2 = parts[2].Substring(index + 3);     // father name
                    }
                    else
                    {
                        value  = parts[2].Substring(0);
                        value2 = parts[2].Substring(0);
                    }



                    // End to seperate Father Name
                    string gender1 = gender[cnt].ToString();
                    if (gender1.ToString().ToLower() != "female" && gender1.ToString().ToLower() != "male") // if neither male or female
                    {
                        gender1 = "UNKNOWN";
                    }



                    MyRecord rec = new MyRecord(
                        parts[0],
                        parts[1],
                        value,
                        value2,
                        gender1,
                        parts[3],
                        parts[4],
                        parts[5],
                        parts[6],
                        parts[7],
                        parts[8],
                        parts[9],
                        parts[10],
                        parts[11],
                        parts[12],
                        parts[13],
                        parts[14],
                        parts[15],
                        parts[16],
                        parts[17],
                        parts[18],
                        parts[19],
                        parts[20],
                        parts[21],
                        parts[22],
                        parts[23]

                        );

                    o.internalStore.Add(rec); // storing object
                } // for end
            } // else end
            return(o);
        }  // function end