예제 #1
0
        } // function end

        private void PopulateDataTable(MyRecord record)
        {
            DataRow row;

            // populate the values
            // using your custom logic
            row     = this.dataTable.NewRow();
            row[0]  = record.District_Tehsil_Name;
            row[1]  = record.Mouza_town_Name;
            row[2]  = record.Name;
            row[3]  = record.Father_Name;
            row[4]  = record.Gender;
            row[5]  = record.Area;
            row[6]  = record.Variety_of_Crop;
            row[7]  = record.Sowing_Date;
            row[8]  = record.Visit_Date;
            row[9]  = record.Pest_Population1;
            row[10] = record.Pest_Population2;
            row[11] = record.Pest_Population3;
            row[12] = record.Pest_Population4;
            row[13] = record.Pest_Population5;
            row[14] = record.Pest_Population6;
            row[15] = record.Pest_Population7;
            row[16] = record.Pest_Population8;
            row[17] = record.Pest_Population9;
            row[18] = record.Pest_Population10;
            row[19] = record.Pest_Population11;
            row[20] = record.Pest_Population12;
            row[21] = record.Pesticide_Used;
            row[22] = record.Spray_Date;
            row[23] = record.Dosage;
            row[24] = record.CLCV_Disease;
            row[25] = record.Plant_Height;


            // add it to the base for final addition to the DB
            this.dataTable.Rows.Add(row);
            this.recordCount++;
        } // function end
예제 #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