protected void ImportFromExcel(object sender, EventArgs e) { string sCon = ClsConnection.Connection(); SqlConnection con1 = new SqlConnection(sCon); //con1.Open(); //using (SqlCommand cmd = new SqlCommand("usp_del_empDetails", con1)) //{ // cmd.CommandType = CommandType.StoredProcedure; // // con.Open(); // cmd.ExecuteNonQuery(); //} //con1.Close(); // CHECK IF A FILE HAS BEEN SELECTED. if ((FileUpload.HasFile)) { if (!Convert.IsDBNull(FileUpload.PostedFile) & FileUpload.PostedFile.ContentLength > 0) { //FIRST, SAVE THE SELECTED FILE IN THE ROOT DIRECTORY. string StrName = Request.Cookies["empcode"].Value.ToString() + "-" + DateTime.Now.ToString().Replace("/", "-").Replace(" ", "-").Replace(":", "-"); FileUpload.SaveAs(Server.MapPath(".") + "\\exceluploaded\\" + StrName + FileUpload.FileName); SqlBulkCopy oSqlBulk = null; // SET A CONNECTION WITH THE EXCEL FILE. OleDbConnection myExcelConn = new OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + Server.MapPath(".") + "\\exceluploaded\\" + StrName + FileUpload.FileName + ";Extended Properties=Excel 12.0;"); try { myExcelConn.Open(); // GET DATA FROM EXCEL SHEET. OleDbCommand objOleDB = new OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn); // READ THE DATA EXTRACTED FROM THE EXCEL FILE. OleDbDataReader objBulkReader = null; objBulkReader = objOleDB.ExecuteReader(); var str = ""; // objBulkReader.Read(); //while (objBulkReader.Read()) //{ // if (objBulkReader["Name"].ToString() != "") // { // str = objBulkReader["Name"].ToString(); // } // //if (objBulkReader["EmpName"].ToString() != "") // //{ // // txtname.Text = objBulkReader["EmpName"].ToString(); // //} // //if (objBulkReader["MobileNumber"].ToString() != "") // //{ // // txtmobno.Text = objBulkReader["MobileNumber"].ToString(); // //} // //if (objBulkReader["Address"].ToString() != "") // //{ // // txtaddress.Text = objBulkReader["Address"].ToString(); // //} //} // SET THE CONNECTION STRING. //string sCon = "server=49.50.77.98,1433\\DBServer;database=RupeeBoss;uid=rb;password=Kotak@123;"; // sCon= "server=10.0.0.220\\DBServer;database=RupeeBoss;uid=rupeeboss;password=boss@rupee;"; // sCon = "server=49.50.77.98,1433\\DBServer;database=RupeeBoss;uid=rb;password=Kotak@123;"; using (con1 = new SqlConnection(sCon)) { con1.Open(); oSqlBulk = new SqlBulkCopy(con1); using (SqlCommand cmd = new SqlCommand("usp_del_empDetails", con1)) { cmd.CommandType = CommandType.StoredProcedure; // con.Open(); cmd.ExecuteNonQuery(); } // FINALLY, LOAD DATA INTO THE DATABASE TABLE. oSqlBulk.DestinationTableName = "EmpDetails"; // TABLE NAME. oSqlBulk.WriteToServer(objBulkReader); //SqlCommand cmd = new SqlCommand(); //cmd.CommandType = CommandType.StoredProcedure; //cmd.CommandText = "usp_updt_cityIds"; //cmd.EndExecuteNonQuery(); using (SqlCommand cmd = new SqlCommand("usp_updt_cityIds_newR", con1)) { cmd.CommandType = CommandType.StoredProcedure; SqlParameter sqlparam = new SqlParameter(); sqlparam = new SqlParameter("@empCode", Request.Cookies["empcode"].Value.ToString()); cmd.Parameters.Add(sqlparam); // con.Open(); cmd.ExecuteNonQuery(); } } lblConfirm.Text = "DATA IMPORTED SUCCESSFULLY."; lblConfirm.Attributes.Add("style", "color:green"); } catch (Exception ex) { lblConfirm.Text = ex.Message; lblConfirm.Attributes.Add("style", "color:red"); } finally { // CLEAR. //oSqlBulk.Close(); //oSqlBulk = null; myExcelConn.Close(); myExcelConn = null; } } } }
protected void ddlgroup_SelectedIndexChanged(object sender, EventArgs e) { string[] param = new string[2]; param[0] = "groupid"; param[1] = ddlgroup.SelectedValue; DataSet ds = new DataSet(); SqlConnection con = new SqlConnection(ClsConnection.Connection()); SqlCommand cmd = new SqlCommand("sp_select_group", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@groupid", ddlgroup.SelectedValue); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); //ds = CommanDataLoad.ExecuteDataSet("sp_select_group", "1", "Group Rights", param); if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { TreeView1.Nodes.Clear(); int i = 0; TreeNode Parent = new TreeNode(); TreeNode Cheild = new TreeNode(); string StrParent = ""; var ParantData = (from d in ds.Tables[0].AsEnumerable() where d.Field <string>("Menu_Level").Contains("0") select new { Menu_Name = d.Field <string>("Menu_Name"), Menu_Id = d.Field <int>("MenuMasterId") }).Distinct(); if (ParantData.Count() > 0) { i = 0; while (i < ParantData.Count()) { StrParent = ParantData.ElementAt(i).Menu_Name.ToString(); Parent = new TreeNode(); Parent.Text = StrParent; Parent.Value = ParantData.ElementAt(i).Menu_Id.ToString(); var CheildData = (from d in ds.Tables[0].AsEnumerable() where d.Field <string>("Menu_Parent").Contains(StrParent) select new { Menu_Name = d.Field <string>("Menu_Name"), Menu_Id = d.Field <int>("MenuMasterId"), IsExits = d.Field <int>("IsExist") }); int j = 0; while (j < CheildData.Count()) { Cheild = new TreeNode(); Cheild.Text = CheildData.ElementAt(j).Menu_Name; Cheild.Value = CheildData.ElementAt(j).Menu_Id.ToString(); if (CheildData.ElementAt(j).IsExits > 0) { Cheild.Checked = true; } else { Cheild.Checked = false; } Parent.ChildNodes.Add(Cheild); j = j + 1; } TreeView1.Nodes.Add(Parent); i = i + 1; } } } } }