public void ReadDataFromExcelToDB()
{
objBL_Common = new BL_Common();
string cs = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
try
{
string path = Server.MapPath("~/FileUploadExcel/" + FileUpload1.FileName.ToString());
string excelread = string.Empty;
//validation userplan (24-9-2015)
DataTable dtCountValidation = new DataTable();
dtCountValidation = objBL_Common.SelectColumnQuery("(select count(b.PK_ContactID) from dbo.EC_UserLogin a inner join dbo.EC_UserContacts b on a.PK_UserID = b.FK_UserID where a.FK_CompanyID = " + Convert.ToInt32(Session["CompanyID"].ToString()) + ") + (SELECT count(*)FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database="+ path + "', 'SELECT * FROM [Sheet1$]')) as ccount");
int contactscount = 0;
string[] UPtype = new string[3];
if (dtCountValidation.Rows.Count > 0)
{
contactscount = Convert.ToInt32(dtCountValidation.Rows[0][0]);
}
if (Session["lstUserPlanType"] != null)
{
UPtype = (string[])Session["lstUserPlanType"];
}
if (contactscount < Convert.ToInt32(UPtype[1]))
{
SqlDataAdapter da = new SqlDataAdapter();
con.Open();
excelread = "INSERT INTO [dbo].[EC_UserContacts] (" + Mapcolumn() + ", FK_UserID, CreatedBy, CreatedOn) SELECT *, " + Convert.ToInt32(Session["UserID"].ToString()) + " as FK_UserID, " + Convert.ToInt32(Session["UserID"].ToString()) + " as CreatedBy, '" + DateTime.Now + "' as CreatedOn FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=" + path + "', 'SELECT * FROM [Sheet1$]');";
//excelread = "INSERT INTO [dbo].[EC_UserContacts] ([ContactName], [email_id], [ContactNo], [Designation], Addressline1, City1, State1, Country1, DateofBirth, CreatedBy, CreatedOn) SELECT *, " + Convert.ToInt32(Session["UserID"].ToString()) + " as FK_UserID, " + Convert.ToInt32(Session["UserID"].ToString()) + " as CreatedBy, '" + DateTime.Today + "' as CreatedOn FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=" + path + "', 'SELECT * FROM [Sheet1$]');";
da.SelectCommand = new SqlCommand(excelread, con);
//da.SelectCommand = new SqlCommand("select * from Addresses_Temp1",con);
DataTable dt = new DataTable();
//dt.Columns.Add("S No", typeof(int));
//dt.Columns.Add("Name", typeof(string));
//dt.Columns.Add("Email", typeof(string));
da.Fill(dt);
con.Close();
}
else
{
Label1.Text = "You cannot add more recipients beyond your plan, Totaly you can have " + Convert.ToInt32(UPtype[1]) + " Contacts !";
Label1.CssClass = "alert alert-danger";
if (File.Exists(path))
{
File.Delete(path);
}
return;
}
excelread = string.Empty;
//dg.DataSource = dt;
objBL_UserContacts.DeleteDuplicateMailid(Convert.ToInt32(Session["Listid"].ToString()), Convert.ToInt32(Session["UserID"].ToString()));
Label1.Text = "Your contacts are now successfully imported.";
Label1.CssClass = "alert alert-success";
}
catch (Exception ex)
{
if (ex.ToString().Contains("The select list for the INSERT statement contains more items than the insert list"))
{
Label1.Text = "The select list for the INSERT statement contains more items than the insert list.";
Label1.CssClass = "alert alert-danger";
}
else if (ex.ToString().Contains("Cannot insert the value NULL into column 'email_id'"))
{
Label1.Text = "No data found for emailid. It is a mandatory one.";
Label1.CssClass = "alert alert-danger";
}
New_EmailCampaign.App_Code.GlobalFunction.StoreLog("ImportReciepientsList.aspx:ReadDataFromExcelToDB() - " + ex.Message);
}
finally
{
con.Close();
}
}