public int updateDatabyId(string data)
{
string[] dataarray = data.Split(';');
int retunvalue = 0;
try
{
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
SqlCommand cmd = new SqlCommand("TAS_Towers_Editdata", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Id", dataarray[0]));
cmd.Parameters.Add(new SqlParameter("@SiteId", dataarray[1]));
cmd.Parameters.Add(new SqlParameter("@District", dataarray[2]));
cmd.Parameters.Add(new SqlParameter("@Cell", dataarray[3]));
cmd.Parameters.Add(new SqlParameter("@Ckt", dataarray[4]));
cmd.Parameters.Add(new SqlParameter("@EqptatAEnd", dataarray[5]));
cmd.Parameters.Add(new SqlParameter("@Port_A", dataarray[6]));
cmd.Parameters.Add(new SqlParameter("@EqptatbEnd", dataarray[7]));
cmd.Parameters.Add(new SqlParameter("@Port_B", dataarray[8]));
cmd.Parameters.Add(new SqlParameter("@POP", dataarray[9]));
cmd.Parameters.Add(new SqlParameter("@Connectivity", dataarray[10]));
cmd.Parameters.Add(new SqlParameter("@EngineerName", dataarray[11]));
cmd.Parameters.Add(new SqlParameter("@RBL2", dataarray[12]));
cnn.Open();
retunvalue = cmd.ExecuteNonQuery();
cnn.Close();
}
catch (Exception fex)
{
throw new Exception(fex.Message);
}
return(retunvalue);
}
public void BindUsers()
{
SqlDataAdapter da = new SqlDataAdapter("select UserName from aspnet_users", TAS_Helper.getConnectionString());
DataSet ds = new DataSet();
da.Fill(ds, "Roles");
lstusers.DataSource = ds;
lstusers.DataTextField = "UserName";
lstRoles.DataValueField = "UserName";
lstusers.DataBind();
}
private void DeleteData()
{
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
cnn.Open();
SqlCommand cmd = new SqlCommand("truncatedata", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
cnn.Close();
}
private void ImportData()
{
switch (Path.GetExtension(FileUpload1.FileName))
{
case ".xls":
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", Server.MapPath(@"..\Uploads\Towers.xls"));
break;
case ".xlsx":
connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", Server.MapPath(@"..\Uploads\Towers.xlsx"));
break;
}
OleDbConnection oledbConn = new OleDbConnection(connString);
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
try
{
DeleteData();
oledbConn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Towers$]", oledbConn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.AcceptChangesDuringFill = false;
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds);
DataTable dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand sqlcmd = new SqlCommand("TAS_Towers_Insertdata", cnn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@SiteId", dt.Rows[i]["SiteId"]);
sqlcmd.Parameters.AddWithValue("@District", dt.Rows[i]["District"]);
sqlcmd.Parameters.AddWithValue("@Cell", dt.Rows[i]["Cell"]);
sqlcmd.Parameters.AddWithValue("@Ckt", dt.Rows[i]["Ckt"]);
sqlcmd.Parameters.AddWithValue("@EqptatAEnd", dt.Rows[i]["EqptatAEnd"]);
sqlcmd.Parameters.AddWithValue("@Port_A", dt.Rows[i]["Port_A"]);
sqlcmd.Parameters.AddWithValue("@EqptatbEnd", dt.Rows[i]["EqptatbEnd"]);
sqlcmd.Parameters.AddWithValue("@Port_B", dt.Rows[i]["Port_B"]);
sqlcmd.Parameters.AddWithValue("@POP", dt.Rows[i]["POP"]);
sqlcmd.Parameters.AddWithValue("@Connectivity", dt.Rows[i]["Connectivity"]);
sqlcmd.Parameters.AddWithValue("@EngineerName", dt.Rows[i]["EngineerName"]);
sqlcmd.Parameters.AddWithValue("@RBL2", dt.Rows[i]["RBL2"]);
cnn.Open();
sqlcmd.ExecuteNonQuery();
cnn.Close();
}
Label1.Text = "DataBase Updated Succesfully";
}
catch (Exception ex)
{
Label1.Text = ex.Message;
}
}
public int updateData(string idvalue, string fieldname, string fieldvalue)
{
int id = 0;
try
{
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
SqlCommand cmd = new SqlCommand("update TAS_Towers set " + fieldname + " ='" + fieldvalue + "' where Id =" + idvalue + "", cnn);
cnn.Open();
id = cmd.ExecuteNonQuery();
cnn.Close();
}
catch (Exception fex)
{
throw new Exception(fex.Message);
}
return(id);
}
public List <Towers> getDatabyId(string id)
{
List <Towers> TASTowers = new List <Towers>();
try
{
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
SqlCommand cmd = new SqlCommand("TAS_Towers_Retrivedatabyid", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Id", Convert.ToInt32(id)));
cnn.Open();
cmd.ExecuteNonQuery();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Towers");
foreach (DataRow dr in ds.Tables["Towers"].Rows)
{
Towers towers = new Towers();
towers.id = Convert.ToInt32(dr["Id"]);
towers.SiteId = dr["SiteId"].ToString();
towers.District = dr["District"].ToString();
towers.Cell = dr["Cell"].ToString();
towers.Ckt = dr["Ckt"].ToString();
towers.EqptatAEnd = dr["EqptatAEnd"].ToString();
towers.Port_A = dr["Port_A"].ToString();
towers.EqptatbEnd = dr["EqptatbEnd"].ToString();
towers.Port_B = dr["Port_B"].ToString();
towers.POP = dr["POP"].ToString();
towers.Connectivity = dr["Connectivity"].ToString();
towers.EngineerName = dr["EngineerName"].ToString();
towers.RBL2 = dr["RBL2"].ToString();
//towers.PagingData = new TowersPagingData(){pagesize=10 };
TASTowers.Add(towers);
}
cnn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(TASTowers);
}
public int deleteData(string idvalue)
{
int id = Convert.ToInt32(idvalue);
int val = 0;
try
{
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
SqlCommand cmd = new SqlCommand("TAS_Towers_Deletedata", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Id", id));
cnn.Open();
val = cmd.ExecuteNonQuery();
cnn.Close();
}
catch (Exception fex)
{
throw new Exception(fex.Message);
}
return(val);
}
public List <string> getdistrict()
{
List <string> district = new List <string>();
try
{
SqlConnection cnn = new SqlConnection(TAS_Helper.getConnectionString());
SqlCommand cmd = new SqlCommand("TAS_Towers_getdistrict", cnn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds, "Towers");
foreach (DataRow dr in ds.Tables["Towers"].Rows)
{
Towers towers = new Towers();
towers.District = dr["District"].ToString();
district.Add(towers.District);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
return(district);
}
