} // 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
// 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