/// <summary>
/// name: ConvertDataTableToJSON
/// description: Method that is passed a filled DataTable object
/// and returns a JSON-formatted string.
/// </summary>
public static string ConvertDataTableToJSON(DataTable parFilledDataTable)
{
//Convert DataTable to List collection of Transfer Objects
List <TO_POI> items = new List <TO_POI>();
foreach (DataRow row in parFilledDataTable.Rows)
{
string ID = Convert.ToString(row["POI_ID"]);
decimal latitude = Convert.ToDecimal(row["POI_Latitude"]);
decimal longitude = Convert.ToDecimal(row["POI_Longitude"]);
string title = Convert.ToString(row["POI_Title"]);
string description = Convert.ToString(row["POI_Description"]);
string address1 = Convert.ToString(row["POI_Address1"]);
string address2 = Convert.ToString(row["POI_Address2"]);
string city = Convert.ToString(row["POI_City"]);
string state = Convert.ToString(row["POI_State"]);
int ZipCode = Convert.ToInt32(row["POI_ZipCode"]);
string contactName = Convert.ToString(row["POI_ContactName"]);
string phone = Convert.ToString(row["POI_Phone"]);
string url = Convert.ToString(row["POI_URL"]);
string category = Convert.ToString(row["POI_Category"]);
string imgFileName1 = Convert.ToString(row["POI_Image1"]);
string imgFileName2 = Convert.ToString(row["POI_Image2"]);
string imgFileName3 = Convert.ToString(row["POI_Image3"]);
string imgFileName4 = Convert.ToString(row["POI_Image4"]);
string imgFileName5 = Convert.ToString(row["POI_Image5"]);
string imgFileName6 = Convert.ToString(row["POI_Image6"]);
bool petFriendly = Convert.ToBoolean(row["Pet_Friendly"]);
string imgDesc1 = Convert.ToString(row["Image1Desc"]);
string imgDesc2 = Convert.ToString(row["Image2Desc"]);
string imgDesc3 = Convert.ToString(row["Image3Desc"]);
string imgDesc4 = Convert.ToString(row["Image4Desc"]);
string imgDesc5 = Convert.ToString(row["Image5Desc"]);
string imgDesc6 = Convert.ToString(row["Image6Desc"]);
TO_POI itemTransferObject =
new TO_POI(ID, latitude, longitude, title, description, imgFileName1,
imgFileName2, imgFileName3, imgFileName4, imgFileName5, imgFileName6, address1, address2, city, state,
ZipCode, contactName, phone, url,
category, petFriendly, imgDesc1, imgDesc2, imgDesc3, imgDesc4, imgDesc5, imgDesc6);
items.Add(itemTransferObject);
}
//Create JSON-formatted string
JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string JSONString = oSerializer.Serialize(items);
return(JSONString);
////Format json string
//string formattedJSONString = JsonFormatter.PrettyPrint(JSONString);
//return formattedJSONString;
}
/// <summary>
/// name: ConvertDataTableToJSON
/// description: Method that is passed a filled DataTable object
/// and returns a JSON-formatted string.
/// </summary>
public static string ConvertDataTableToJSON(DataTable parFilledDataTable)
{
//Convert DataTable to List collection of Transfer Objects
List<TO_POI> items = new List<TO_POI>();
foreach (DataRow row in parFilledDataTable.Rows)
{
string ID = Convert.ToString(row["POI_ID"]);
decimal latitude = Convert.ToDecimal(row["POI_Latitude"]);
decimal longitude = Convert.ToDecimal(row["POI_Longitude"]);
string title = Convert.ToString(row["POI_Title"]);
string description = Convert.ToString(row["POI_Description"]);
string category = Convert.ToString(row["POI_Category"]);
string imgFileName1 = Convert.ToString(row["POI_Image1"]);
string difficulty = Convert.ToString(row["POI_Difficulty"]);
string trailTime = Convert.ToString(row["POI_TrailTime"]);
string trailTime2 = Convert.ToString(row["POI_TrailTime2"]);
string travelTime = Convert.ToString(row["POI_TravelTime"]);
string phone = Convert.ToString(row["POI_Phone"]);
string url = Convert.ToString(row["POI_URL"]);
string elevation = Convert.ToString(row["POI_Elevation"]);
string verticalDrop = Convert.ToString(row["POI_VerticalDrop"]);
string additionalInfo = Convert.ToString(row["POI_Additional_Info"]);
string season = Convert.ToString(row["POI_Season"]);
string highlights = Convert.ToString(row["POI_Highlights"]);
string accessRating = Convert.ToString(row["POI_AccessRating"]);
string riverClass = Convert.ToString(row["POI_Class"]);
string length = Convert.ToString(row["POI_Length"]);
string level = Convert.ToString(row["POI_Level"]);
string putin = Convert.ToString(row["POI_Put_In"]);
string takeout = Convert.ToString(row["POI_Take_Out"]);
TO_POI itemTransferObject =
new TO_POI(ID, latitude, longitude, title, description, imgFileName1,
category, difficulty, trailTime, trailTime2,
travelTime, phone, url, elevation, verticalDrop, additionalInfo, season, highlights, accessRating,
riverClass, length, level, putin, takeout);
items.Add(itemTransferObject);
}
//Create JSON-formatted string
JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string JSONString = oSerializer.Serialize(items);
return JSONString;
////Format json string
//string formattedJSONString = JsonFormatter.PrettyPrint(JSONString);
//return formattedJSONString;
}
/// <summary>
/// The CovertTourPOIDataTableToJSON method returns a JSON string with only the locations in
/// a specific tour.
/// </summary>
public static string CovertCategoryPOIDataTableToJSON(string parCategoryID, DataTable parFilledDataTable, bool parCheckIsActive)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//Build sql string
string sqlString = "SELECT * FROM POIs "
+ " WHERE POIs.POI_Category=?"
+ "ORDER BY POI_ID;";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryID);
//4. Use the DataAdapter object to fill the DataTable object
//Instantiate a DataAdapter object
OleDbDataAdapter dataAdapterObject = new OleDbDataAdapter();
//Instantiate a DataTable object
DataTable dataTableObject = new DataTable();
//Set the SelectCommand property of the DataAdapter object
//to the filled Command object
dataAdapterObject.SelectCommand = commandObject;
//Fill the DataTable object
dataAdapterObject.Fill(dataTableObject);
//6. Close the connection: Always do this!!!!
connectionObject.Close();
//Convert DataTable to List collection of Transfer Objects
List<TO_POI> items = new List<TO_POI>();
foreach (DataRow row in dataTableObject.Rows)
{
string ID = Convert.ToString(row["POI_ID"]);
decimal latitude = Convert.ToDecimal(row["POI_Latitude"]);
decimal longitude = Convert.ToDecimal(row["POI_Longitude"]);
string title = Convert.ToString(row["POI_Title"]);
string description = Convert.ToString(row["POI_Description"]);
string category = Convert.ToString(row["POI_Category"]);
string imgFileName1 = Convert.ToString(row["POI_Image1"]);
string difficulty = Convert.ToString(row["POI_Difficulty"]);
string trailTime = Convert.ToString(row["POI_TrailTime"]);
string trailTime2 = Convert.ToString(row["POI_TrailTime2"]);
string travelTime = Convert.ToString(row["POI_TravelTime"]);
string phone = Convert.ToString(row["POI_Phone"]);
string url = Convert.ToString(row["POI_URL"]);
string elevation = Convert.ToString(row["POI_Elevation"]);
string verticalDrop = Convert.ToString(row["POI_VerticalDrop"]);
string additionalInfo = Convert.ToString(row["POI_Additional_Info"]);
string season = Convert.ToString(row["POI_Season"]);
string highlights = Convert.ToString(row["POI_Highlights"]);
string accessRating = Convert.ToString(row["POI_AccessRating"]);
string riverClass = Convert.ToString(row["POI_Class"]);
string length = Convert.ToString(row["POI_Length"]);
string level = Convert.ToString(row["POI_Level"]);
string putin = Convert.ToString(row["POI_Put_In"]);
string takeout = Convert.ToString(row["POI_Take_Out"]);
TO_POI itemTransferObject =
new TO_POI(ID, latitude, longitude, title, description, imgFileName1,
category, difficulty,trailTime, trailTime2,
travelTime, phone, url, elevation, verticalDrop, additionalInfo, season, highlights, accessRating,
riverClass, length, level, putin, takeout);
items.Add(itemTransferObject);
}
//Create JSON-formatted string
JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string JSONString = oSerializer.Serialize(items);
//add in Return JSONString when taking out PrettyPrint
return JSONString;
////Format json string
//string formattedJSONString = JsonFormatter.PrettyPrint(JSONString);
//return formattedJSONString;
}
/// <summary>
/// The CovertTourPOIDataTableToJSON method returns a JSON string with only the locations in
/// a specific tour.
/// </summary>
public static string CovertCategoryPOIDataTableToJSON(string parCategoryID, DataTable parFilledDataTable, bool parCheckIsActive)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//Build sql string
string sqlString = "SELECT * FROM POIs "
+ " WHERE POIs.POI_Category=?"
+ "And IsActive=?"
+ "ORDER BY POI_ID;";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryID);
commandObject.Parameters.AddWithValue("@parCheckIsActive", parCheckIsActive);
//4. Use the DataAdapter object to fill the DataTable object
//Instantiate a DataAdapter object
OleDbDataAdapter dataAdapterObject = new OleDbDataAdapter();
//Instantiate a DataTable object
DataTable dataTableObject = new DataTable();
//Set the SelectCommand property of the DataAdapter object
//to the filled Command object
dataAdapterObject.SelectCommand = commandObject;
//Fill the DataTable object
dataAdapterObject.Fill(dataTableObject);
//6. Close the connection: Always do this!!!!
connectionObject.Close();
//Convert DataTable to List collection of Transfer Objects
List<TO_POI> items = new List<TO_POI>();
foreach (DataRow row in dataTableObject.Rows)
{
string ID = Convert.ToString(row["POI_ID"]);
decimal latitude = Convert.ToDecimal(row["POI_Latitude"]);
decimal longitude = Convert.ToDecimal(row["POI_Longitude"]);
string title = Convert.ToString(row["POI_Title"]);
string description = Convert.ToString(row["POI_Description"]);
string address1 = Convert.ToString(row["POI_Address1"]);
string address2 = Convert.ToString(row["POI_Address2"]);
string city = Convert.ToString(row["POI_City"]);
string state = Convert.ToString(row["POI_State"]);
int ZipCode = Convert.ToInt32(row["POI_ZipCode"]);
string contactName = Convert.ToString(row["POI_ContactName"]);
string phone = Convert.ToString(row["POI_Phone"]);
string url = Convert.ToString(row["POI_URL"]);
string category = Convert.ToString(row["POI_Category"]);
string imgFileName1 = Convert.ToString(row["POI_Image1"]);
string imgFileName2 = Convert.ToString(row["POI_Image2"]);
string imgFileName3 = Convert.ToString(row["POI_Image3"]);
string imgFileName4 = Convert.ToString(row["POI_Image4"]);
string imgFileName5 = Convert.ToString(row["POI_Image5"]);
string imgFileName6 = Convert.ToString(row["POI_Image6"]);
bool petFriendly= Convert.ToBoolean(row["Pet_Friendly"]);
string imgDesc1 = Convert.ToString(row["Image1Desc"]);
string imgDesc2 = Convert.ToString(row["Image2Desc"]);
string imgDesc3 = Convert.ToString(row["Image3Desc"]);
string imgDesc4 = Convert.ToString(row["Image4Desc"]);
string imgDesc5 = Convert.ToString(row["Image5Desc"]);
string imgDesc6 = Convert.ToString(row["Image6Desc"]);
TO_POI itemTransferObject =
new TO_POI(ID, latitude, longitude, title, description, imgFileName1,
imgFileName2, imgFileName3, imgFileName4, imgFileName5, imgFileName6, address1, address2, city, state,
ZipCode, contactName, phone, url,
category, petFriendly, imgDesc1, imgDesc2, imgDesc3, imgDesc4, imgDesc5, imgDesc6);
items.Add(itemTransferObject);
}
//Create JSON-formatted string
JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string JSONString = oSerializer.Serialize(items);
//add in Return JSONString when taking out PrettyPrint
return JSONString;
////Format json string
//string formattedJSONString = JsonFormatter.PrettyPrint(JSONString);
//return formattedJSONString;
}
/// <summary>
/// name: ConvertDataTableToJSON
/// description: Method that is passed a filled DataTable object
/// and returns a JSON-formatted string.
/// </summary>
public static string ConvertDataTableToJSON(DataTable parFilledDataTable)
{
//Convert DataTable to List collection of Transfer Objects
List<TO_POI> items = new List<TO_POI>();
foreach (DataRow row in parFilledDataTable.Rows)
{
string ID = Convert.ToString(row["POI_ID"]);
decimal latitude = Convert.ToDecimal(row["POI_Latitude"]);
decimal longitude = Convert.ToDecimal(row["POI_Longitude"]);
string title = Convert.ToString(row["POI_Title"]);
string description = Convert.ToString(row["POI_Description"]);
string address1 = Convert.ToString(row["POI_Address1"]);
string address2 = Convert.ToString(row["POI_Address2"]);
string city = Convert.ToString(row["POI_City"]);
string state = Convert.ToString(row["POI_State"]);
int ZipCode = Convert.ToInt32(row["POI_ZipCode"]);
string contactName = Convert.ToString(row["POI_ContactName"]);
string phone = Convert.ToString(row["POI_Phone"]);
string url = Convert.ToString(row["POI_URL"]);
string category = Convert.ToString(row["POI_Category"]);
string imgFileName1 = Convert.ToString(row["POI_Image1"]);
string imgFileName2 = Convert.ToString(row["POI_Image2"]);
string imgFileName3 = Convert.ToString(row["POI_Image3"]);
string imgFileName4 = Convert.ToString(row["POI_Image4"]);
string imgFileName5 = Convert.ToString(row["POI_Image5"]);
string imgFileName6 = Convert.ToString(row["POI_Image6"]);
bool petFriendly = Convert.ToBoolean(row["Pet_Friendly"]);
string imgDesc1 = Convert.ToString(row["Image1Desc"]);
string imgDesc2 = Convert.ToString(row["Image2Desc"]);
string imgDesc3 = Convert.ToString(row["Image3Desc"]);
string imgDesc4 = Convert.ToString(row["Image4Desc"]);
string imgDesc5 = Convert.ToString(row["Image5Desc"]);
string imgDesc6 = Convert.ToString(row["Image6Desc"]);
TO_POI itemTransferObject =
new TO_POI(ID, latitude, longitude, title, description, imgFileName1,
imgFileName2, imgFileName3, imgFileName4, imgFileName5, imgFileName6, address1, address2, city, state,
ZipCode, contactName, phone, url,
category, petFriendly, imgDesc1, imgDesc2, imgDesc3, imgDesc4, imgDesc5, imgDesc6);
items.Add(itemTransferObject);
}
//Create JSON-formatted string
JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string JSONString = oSerializer.Serialize(items);
return JSONString;
////Format json string
//string formattedJSONString = JsonFormatter.PrettyPrint(JSONString);
//return formattedJSONString;
}
/// <summary>
/// The CovertTourPOIDataTableToJSON method returns a JSON string with only the locations in
/// a specific tour.
/// </summary>
public static string CovertCategoryPOIDataTableToJSON(string parCategoryID, DataTable parFilledDataTable, bool parCheckIsActive)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//Build sql string
string sqlString = "SELECT * FROM POIs "
+ " WHERE POIs.POI_Category=?"
+ "And IsActive=?"
+ "ORDER BY POI_ID;";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryID);
commandObject.Parameters.AddWithValue("@parCheckIsActive", parCheckIsActive);
//4. Use the DataAdapter object to fill the DataTable object
//Instantiate a DataAdapter object
OleDbDataAdapter dataAdapterObject = new OleDbDataAdapter();
//Instantiate a DataTable object
DataTable dataTableObject = new DataTable();
//Set the SelectCommand property of the DataAdapter object
//to the filled Command object
dataAdapterObject.SelectCommand = commandObject;
//Fill the DataTable object
dataAdapterObject.Fill(dataTableObject);
//6. Close the connection: Always do this!!!!
connectionObject.Close();
//Convert DataTable to List collection of Transfer Objects
List <TO_POI> items = new List <TO_POI>();
foreach (DataRow row in dataTableObject.Rows)
{
string ID = Convert.ToString(row["POI_ID"]);
decimal latitude = Convert.ToDecimal(row["POI_Latitude"]);
decimal longitude = Convert.ToDecimal(row["POI_Longitude"]);
string title = Convert.ToString(row["POI_Title"]);
string description = Convert.ToString(row["POI_Description"]);
string address1 = Convert.ToString(row["POI_Address1"]);
string address2 = Convert.ToString(row["POI_Address2"]);
string city = Convert.ToString(row["POI_City"]);
string state = Convert.ToString(row["POI_State"]);
int ZipCode = Convert.ToInt32(row["POI_ZipCode"]);
string contactName = Convert.ToString(row["POI_ContactName"]);
string phone = Convert.ToString(row["POI_Phone"]);
string url = Convert.ToString(row["POI_URL"]);
string category = Convert.ToString(row["POI_Category"]);
string imgFileName1 = Convert.ToString(row["POI_Image1"]);
string imgFileName2 = Convert.ToString(row["POI_Image2"]);
string imgFileName3 = Convert.ToString(row["POI_Image3"]);
string imgFileName4 = Convert.ToString(row["POI_Image4"]);
string imgFileName5 = Convert.ToString(row["POI_Image5"]);
string imgFileName6 = Convert.ToString(row["POI_Image6"]);
bool petFriendly = Convert.ToBoolean(row["Pet_Friendly"]);
string imgDesc1 = Convert.ToString(row["Image1Desc"]);
string imgDesc2 = Convert.ToString(row["Image2Desc"]);
string imgDesc3 = Convert.ToString(row["Image3Desc"]);
string imgDesc4 = Convert.ToString(row["Image4Desc"]);
string imgDesc5 = Convert.ToString(row["Image5Desc"]);
string imgDesc6 = Convert.ToString(row["Image6Desc"]);
TO_POI itemTransferObject =
new TO_POI(ID, latitude, longitude, title, description, imgFileName1,
imgFileName2, imgFileName3, imgFileName4, imgFileName5, imgFileName6, address1, address2, city, state,
ZipCode, contactName, phone, url,
category, petFriendly, imgDesc1, imgDesc2, imgDesc3, imgDesc4, imgDesc5, imgDesc6);
items.Add(itemTransferObject);
}
//Create JSON-formatted string
JavaScriptSerializer oSerializer = new JavaScriptSerializer();
string JSONString = oSerializer.Serialize(items);
//add in Return JSONString when taking out PrettyPrint
return(JSONString);
////Format json string
//string formattedJSONString = JsonFormatter.PrettyPrint(JSONString);
//return formattedJSONString;
}
