private void ImporttoDatatable()
{
//
//read tab separated csv file
string strFileID = string.Empty;
string strInputFileName = string.Empty;
string strOutputFileName = string.Empty;
string strEntityID = string.Empty;
string strName = string.Empty;
string MainStatus = "";
int SuccessData = 0;
string StrErrorList = string.Empty;
string colName = "";
try
{
if (FUploadEntityDetails.HasFile)
{
string strDataMode = string.Empty;
string strEntityCode = string.Empty;
string strEntityBankID = string.Empty;
string LeadID = string.Empty;
strFileID = GeneralFunctions._GenerateNewStringID("F", "FileID", "2", "14", "EntityBulkUpload");
string FileName = FUploadEntityDetails.FileName;
string path = string.Concat(Server.MapPath("LeadBulkUpload/") + FUploadEntityDetails.PostedFile.FileName);
FUploadEntityDetails.PostedFile.SaveAs(path);
OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);
ds = new DataSet();
objAdapter1.Fill(ds);
Dt = ds.Tables[0];
//remove empty rows
for (int i = Dt.Rows.Count - 1; i >= 0; i += -1)
{
DataRow row = Dt.Rows[i];
if (row[0] == null)
{
Dt.Rows.Remove(row);
}
else if (string.IsNullOrEmpty(row[0].ToString()))
{
Dt.Rows.Remove(row);
}
}
Dt.Columns.Add("Status", typeof(string));
foreach (DataRow dr in Dt.Rows)
{
// strEntityID = "";
// ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "WaitTime();", true);
if (!string.IsNullOrEmpty(dr["domain_name"].ToString()) &&
!string.IsNullOrEmpty(dr["create_date"].ToString()) && !string.IsNullOrEmpty(dr["registrant_email"].ToString()) &&
!string.IsNullOrEmpty(dr["registrant_phone"].ToString())
// && !string.IsNullOrEmpty(dr["State"].ToString()) && !string.IsNullOrEmpty(dr["City"].ToString())
// && !string.IsNullOrEmpty(dr["Country"].ToString()) && !string.IsNullOrEmpty(dr["ZipCode"].ToString())
//&& !string.IsNullOrEmpty(dr["ContactPerson"].ToString()) && !string.IsNullOrEmpty(dr["ContactPersonMobile"].ToString())
//)
)
{
dr["Status"] = "Success";
//entity logic
SqlParameter[] sqlPar = new SqlParameter[1];
sqlPar[0] = new SqlParameter("@EntityCode", SqlDbType.NVarChar);
sqlPar[0].Value = dr["registrant_email"].ToString();
SqlDataReader dr1 = DataAccessor.ExecuteProcDataReader("check_EntityCode", sqlPar);
while (dr1.Read())
{
if (dr1["Exists"].ToString() == "True")
{
dr["Status"] = "EmailID already Exixst";
}
else
{
SuccessData += 1;
LeadID = GeneralFunctions._GenerateNewStringID("L", "LeadID", "2", "20", "LeadMaster");
MasterData.LeadMaster("Insert", LeadID, "Google.com",
dr["domain_name"].ToString(), dr["registrant_company"].ToString(), dr["registrant_phone"].ToString(), "",
dr["registrant_email"].ToString(), dr["registrant_address"].ToString(), dr["registrant_state"].ToString(), dr["registrant_city"].ToString(), dr["registrant_country"].ToString(),
dr["registrant_zip"].ToString(), dr["registrant_name"].ToString(), "-", true,
false, dr["create_date"].ToString(), "SC1", false
);
//GetEntityYpe
//MasterData.ProductPoint(strPPID, dr["PartnerID"].ToString(), "", dr["Product Code"].ToString(), dr["Product Desc"].ToString(), dr["ScratchCode"].ToString(), ProdcutValue, stratdate, enddate, true, "", true);
//MasterData.NewEarnPointsScheme("Insert", strEPointID, strECode, "", "", strPPID, decSpendKSH, decSpendUSD, intEarnPoints, intReedemPoints, strFromDate, strToDate, true, "Earn", "", "", "", "", "", "", "", true, strUserID, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "");
//lblErrorMsg.Text = ep.Message.ToString() + "<br>" + ep.StackTrace.ToString() + "<br>" + ep.Source.ToString();
//lblErrorMsg.Visible = true;
}
}
dr1.Close();
}
else
{
//if (dr["Domain Source"].ToString() == "")
//{
// colName = "Domain Source";
// StrErrorList += colName + ",";
//}
if (dr["domain_name"].ToString() == "")
{
colName = "domain_name";
StrErrorList += colName;
}
if (dr["registrant_phone"].ToString() == "")
{
colName = "registrant_phone";
StrErrorList += colName;
}
if (dr["registrant_email"].ToString() == "")
{
colName = "registrant_email";
StrErrorList += colName;
}
//if (dr["EmailID"].ToString() == "")
//{
// colName = "EmailID";
// StrErrorList += colName;
//}
//if (dr["Address"].ToString() == "")
//{
// colName = "Address";
// StrErrorList += colName;
//}
//if (dr["State"].ToString() == "")
//{
// colName = "State";
// StrErrorList += colName;
//}
//if (dr["City"].ToString() == "")
//{
// colName = "City";
// StrErrorList += colName;
//}
//if (dr["Country"].ToString() == "")
//{
// colName = "Country";
// StrErrorList += colName;
//}
//if (dr["ZipCode"].ToString() == "")
//{
// colName = "ZipCode";
// StrErrorList += colName;
//}
//if (dr["ContactPerson"].ToString() == "")
//{
// colName = "ContactPerson";
// StrErrorList += colName;
//}
//if (dr["ContactPersonMobile"].ToString() == "")
//{
// colName = "ContactPersonMobile";
// StrErrorList += colName;
//}
//Bank1 IFSC
dr["Status"] = "Failed " + "Kindly Insert " + StrErrorList;
StrErrorList = "";
}
if (SuccessData == dr.Table.Rows.Count)
{
MainStatus = "File successfully Uplaoded";
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "SuccessFIle();", true);
}
else
{
MainStatus = "File Failed";
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "FailedFIle();", true);
}
}
CreateOutput(Dt, Server.MapPath("LeadBulkUpload/") + strFileID + (".xlsx"));
DataTable DtEntityUpload;
DtEntityUpload = InsertEntityBulkUpload(strFileID, "~/Admin/LeadBulkUpload/" + strFileID + ".xlsx", "~/Admin/LeadBulkUpload/" + strFileID + ".xlsx", MainStatus, SuccessData, System.DateTime.Now, System.DateTime.Now, Session["UserID"].ToString(), Session["UserID"].ToString());
BindGridBulk();
LblError.Text = "";
}
}
catch (Exception ex)
{
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "InvalidFile();", true);
// ScriptManager.RegisterStartupScript(Page, Page.GetType(), "InvalidArgs", "alert('File is not valid " + ex + " , Upload File Again');", true);
//LblError.Text = "File is not Valid,Kindly Download Sample file and upload again";
}
}