using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Web.Caching;

public partial class modules_EditCircuitOrder : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Response.Write("Page is loading...");

if (!IsPostBack)
{
Response.Write("!IsPostBack");
// GET Request
SqlConnection cn = new SqlConnection(
ConfigurationManager.AppSettings.Get("SOMConnectionString"));

// SqlCommand cmd = new SqlCommand(query,cn);
// SqlDataReader sdr = cmd.ExecuteReader();
Response.Write("Before Data Execute");

DataTable dtCityCoord;
dtCityCoord = (DataTable)Cache["cityCoordinates"];

if (dtCityCoord == null)
{
// we don't have the table in cache so load it now
string query = @"SELECT cityName + ', ' + UPPER(state) AS cityState, CityCoordID
FROM som.dbo.tblCityCoord
ORDER BY cityState";

using (SqlCommand cmd = new SqlCommand(query, cn))
{
cn.Open();

Response.Write("<br/><br/>NO CACHE<br/><br/>");

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dtCityCoord = new DataTable();

adapter.Fill(dtCityCoord); // Just pass the DataTable into the SqlDataAdapters Fill Method
//this.DgMethodOne.DataSource = dtAuthors;

SqlCacheDependency cityCoordDepends =
new SqlCacheDependency("SOM", "tblCityCoord");
Cache.Insert("cityCoordinates", dtCityCoord, cityCoordDepends);
cn.Close();
}
}

LocA.DataSource = dtCityCoord;
LocA.DataTextField = "cityState";
LocA.DataValueField = "CityCoordID";
LocA.DataBind();

LocZ.DataSource = dtCityCoord;
LocZ.DataTextField = "cityState";
LocZ.DataValueField = "CityCoordID";
LocZ.DataBind();


DataTable dtCircuitOrderLookups;
dtCircuitOrderLookups = (DataTable)Cache["circuitOrderLookups"];

if (dtCircuitOrderLookups == null)
{
// we don't have the table in cache so load it now
string query = @"SELECT CircuitOrderLookUpID AS Value, Text, Field
FROM som.dbo.tblCircuitOrderLookups";

using (SqlCommand cmd = new SqlCommand(query, cn))
{
cn.Open();

Response.Write("<br/><br/>NO CACHE<br/><br/>");

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
dtCircuitOrderLookups = new DataTable();

adapter.Fill(dtCircuitOrderLookups);

SqlCacheDependency circuitOrderLookupsDepends =
new SqlCacheDependency("SOM", "tblCircuitOrderLookups");
Cache.Insert("circuitOrderLookups", dtCircuitOrderLookups, circuitOrderLookupsDepends);
cn.Close();
}
}

LoadDropDownList(FRRPAUsage, dtCircuitOrderLookups, "FRRPAUsage");

LoadDropDownList(OrderType, dtCircuitOrderLookups, "OrderType");

LoadDropDownList(Net, dtCircuitOrderLookups, "Net");

Response.Write("After Data Execute");

RequiredFieldValidator rfv = new RequiredFieldValidator();
rfv.ControlToValidate = "CircuitID";
this.Controls.Add(rfv);
}
CircuitId.Focus();

Response.Write("Page Load Finished.");
}

private void LoadDropDownList(DropDownList ddl, DataTable source, string fieldName)
{
DataRow[] lookups =
source.Select("Field = '|REPLACE|'".Replace("|REPLACE|", fieldName));

foreach (DataRow row in lookups)
{
ListItem item = new ListItem();
item.Text = row["Text"].ToString();
item.Value = row["Value"].ToString();
ddl.Items.Add(item);
}

}
}