/// <summary>
/// The update category method updates a specific category based on a given category ID and
/// supplied user input.
/// </summary>
public void UpdateCategory(string parCategoryName, string parCategoryCode)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "UPDATE POIs_Category " +
"SET Category_Name=? " + "WHERE Category_Code=? ";
currentContext.Trace.Warn("sqlString = " + sqlString);
//3b. Set the Command object properties properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@Category_Name", parCategoryName);
commandObject.Parameters.AddWithValue("@Category_Code", parCategoryCode);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
public DataTable GetPOIs()
{
//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_Category, POIs Where POIs_Category.Category_Code=POIs.POI_Category" +
" Order by POI_Title";
//Build Command object
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Use the DataAdapter object to fill the DataTable object
OleDbDataAdapter dataAdapterObject = new OleDbDataAdapter();
DataTable dataTableObject = new DataTable();
dataAdapterObject.SelectCommand = commandObject;
dataAdapterObject.Fill(dataTableObject);
//Close the connection object
connectionObject.Close();
return(dataTableObject);
}
/// <summary>
/// name: DeleteRecord
/// description: Carries out the deletion of the specified POI, it is passed the POI ID
/// </summary>
private void DeleteRecord(string parLocationID)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "DELETE FROM POIs WHERE POI_ID=parLocationID";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_ID", parLocationID);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// name: DeleteRecord
/// description: Carries out the deletion of the specified POI, it is passed the POI ID
/// </summary>
private void DeleteRecord(string parLocationID)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "DELETE FROM POIs WHERE POI_ID=parLocationID";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_ID", parLocationID);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// The add category method adds a category to the database given user input
/// </summary>
public void AddCategory(string parCategoryName)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "INSERT INTO POIs_Category " +
"(Category_Name)" +
"VALUES(?)";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@Category_Name", parCategoryName);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// name: AddPOI
/// description: Method for Adding a new POI to the database
/// </summary>
public void AddPOI(decimal parLatitude,
decimal parLongitude, string parTitle, string parDescription,
string parAddress1, string parAddress2, string parCity, string parState,
int parZipCode, string parContactName, string parPhone, string parURL,
string parCategoryCode, bool parIsActive, bool parPetFriendly)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
currentContext.Trace.Warn(" Inside AddPOI DA_POI Class Method");
OleDbConnection connectionObject =
new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "INSERT INTO POIs " +
"(POI_Latitude, POI_Longitude, POI_Title, POI_Description, POI_Address1, POI_Address2, POI_City, POI_State, POI_ZipCode, POI_ContactName, POI_Phone, POI_URL, POI_Category, IsActive, Pet_Friendly)" +
"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
currentContext.Trace.Warn(" sqlstring = " + sqlString);
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_Latitude", parLatitude);
commandObject.Parameters.AddWithValue("@POI_Longitude", parLongitude);
commandObject.Parameters.AddWithValue("@POI_Title", parTitle);
commandObject.Parameters.AddWithValue("@POI_Description", parDescription);
commandObject.Parameters.AddWithValue("@POI_Address1", parAddress1);
commandObject.Parameters.AddWithValue("@POI_Address2", parAddress2);
commandObject.Parameters.AddWithValue("@POI_City", parCity);
commandObject.Parameters.AddWithValue("@POI_State", parState);
commandObject.Parameters.AddWithValue("@POI_ZipCode", parZipCode);
commandObject.Parameters.AddWithValue("@POI_ContactName", parContactName);
commandObject.Parameters.AddWithValue("@POI_Phone", parPhone);
commandObject.Parameters.AddWithValue("@POI_URL", parURL);
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryCode);
commandObject.Parameters.AddWithValue("@IsActive", parIsActive);
commandObject.Parameters.AddWithValue("@Pet_Friendly", parPetFriendly);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
connectionObject.Close();
}
/// <summary>
/// name: AddPOI
/// description: Method for Adding a new POI to the database
/// </summary>
public void AddPOI(decimal parLatitude,
decimal parLongitude, string parTitle, string parDescription,
string parAddress1, string parAddress2, string parCity, string parState,
int parZipCode, string parContactName, string parPhone, string parURL,
string parCategoryCode, bool parIsActive, bool parPetFriendly)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
currentContext.Trace.Warn(" Inside AddPOI DA_POI Class Method");
OleDbConnection connectionObject =
new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "INSERT INTO POIs " +
"(POI_Latitude, POI_Longitude, POI_Title, POI_Description, POI_Address1, POI_Address2, POI_City, POI_State, POI_ZipCode, POI_ContactName, POI_Phone, POI_URL, POI_Category, IsActive, Pet_Friendly)" +
"VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
currentContext.Trace.Warn(" sqlstring = " + sqlString);
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_Latitude", parLatitude);
commandObject.Parameters.AddWithValue("@POI_Longitude", parLongitude);
commandObject.Parameters.AddWithValue("@POI_Title", parTitle);
commandObject.Parameters.AddWithValue("@POI_Description", parDescription);
commandObject.Parameters.AddWithValue("@POI_Address1", parAddress1);
commandObject.Parameters.AddWithValue("@POI_Address2", parAddress2);
commandObject.Parameters.AddWithValue("@POI_City", parCity);
commandObject.Parameters.AddWithValue("@POI_State", parState);
commandObject.Parameters.AddWithValue("@POI_ZipCode", parZipCode);
commandObject.Parameters.AddWithValue("@POI_ContactName", parContactName);
commandObject.Parameters.AddWithValue("@POI_Phone", parPhone);
commandObject.Parameters.AddWithValue("@POI_URL", parURL);
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryCode);
commandObject.Parameters.AddWithValue("@IsActive", parIsActive);
commandObject.Parameters.AddWithValue("@Pet_Friendly", parPetFriendly);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
connectionObject.Close();
}
static string Run_command(Connection_Info connection_info, string command)
{
var pass_auth = new PasswordAuthenticationMethod(connection_info.Username, connection_info.Password);
var con_info = new ConnectionInfo(connection_info.Address, connection_info.Username, pass_auth);
var ssh = new SshClient(con_info);
ssh.Connect();
var com = ssh.CreateCommand(command);
var result = com.Execute();
ssh.Disconnect();
return(result);
}
//NEW
public int GetNewlyAddedPOI_ID()
{
currentContext.Trace.Warn("Get Newly Added POI");
//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" +
" Order By POI_ID DESC";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Use the DataAdapter to fill a DataTable 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);
//Grab the values out of the first (and only) row of
//the DataTable object and put it in a DataRow object
DataRow dataRowObject = dataTableObject.Rows[0];
int newPOI_ID = Convert.ToInt32(dataRowObject["POI_ID"]);
//Close the connection.
connectionObject.Close();
return(newPOI_ID);
// return dataRowObject;
}
public void AddImageFilename(string parPOIID,
string parImageFilename,
int parImageNumber)
{
//Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject =
new OleDbConnection(connectionString);
connectionObject.Open();
string sqlString = "UPDATE POIs " +
"SET ";
//TODO: What if bad parImageNumber??
if (parImageNumber == 1)
sqlString += " POI_Image1=?";
else if (parImageNumber == 2)
sqlString += " POI_Image2=?";
else if (parImageNumber == 3)
sqlString += " POI_Image3=?";
else if (parImageNumber == 4)
sqlString += " POI_Image4=?";
else if (parImageNumber == 5)
sqlString += " POI_Image5=?";
else if (parImageNumber == 6)
sqlString += " POI_Image6=?";
sqlString += " WHERE POI_ID=?";
//Set the Command object properties properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_ImageFilename", parImageFilename);
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
//Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//Close the connection
connectionObject.Close();
}
//NEW
public int GetNewlyAddedPOI_ID()
{
currentContext.Trace.Warn("Get Newly Added POI");
//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" +
" Order By POI_ID DESC";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Use the DataAdapter to fill a DataTable 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);
//Grab the values out of the first (and only) row of
//the DataTable object and put it in a DataRow object
DataRow dataRowObject = dataTableObject.Rows[0];
int newPOI_ID = Convert.ToInt32(dataRowObject["POI_ID"]);
//Close the connection.
connectionObject.Close();
return newPOI_ID;
// return dataRowObject;
}
/// <summary>
/// name: GetPOI
/// description: Method for fetching all the POI's from the database
/// </summary>
///
public DataRow GetPOI(string parID)
{
//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 POI_ID=?" + " 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_ID", parID);
//Use the DataAdapter to fill a DataTable 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);
//Grab the values out of the first (and only) row of
//the DataTable object and put it in a DataRow object
DataRow dataRowObject = dataTableObject.Rows[0];
//Close the connection.
connectionObject.Close();
return(dataRowObject);
}
/// <summary>
/// name: GetCategoryByCategoryCode
/// description: Retrieves Category name by CategoryCode
/// </summary>
public DataRow GetCategoryByCategoryCode(string parCategoryCode)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//2. Construct sql string
//Build sql string
string sqlString = "Select * From POIs_Category" + " Where Category_Code=?" + " Order By Category_Code";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@Category_Code", parCategoryCode);
//4. Use the DataAdapter to fill a 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);
//Grab the values out of the first (and only) row of
//the DataTable object and put it in a DataRow object
DataRow dataRowObject = dataTableObject.Rows[0];
//6. Close the connection.
//Always do this!
connectionObject.Close();
return(dataRowObject);
}
/// <summary>
/// name: GetCategoryByCategoryCode
/// description: Retrieves Category name by CategoryCode
/// </summary>
public DataRow GetCategoryByCategoryCode(string parCategoryCode)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//2. Construct sql string
//Build sql string
string sqlString = "Select * From POIs_Category" + " Where Category_Code=?" + " Order By Category_Code";
//Build Command object with Parameter
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@Category_Code", parCategoryCode);
//4. Use the DataAdapter to fill a 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);
//Grab the values out of the first (and only) row of
//the DataTable object and put it in a DataRow object
DataRow dataRowObject = dataTableObject.Rows[0];
//6. Close the connection.
//Always do this!
connectionObject.Close();
return dataRowObject;
}
/// <summary>
/// The DeleteCategory method deletes a specified category
/// </summary>
public void DeleteCategory(int parCategoryID)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
//SQL (Updating POIs to have Uncategorized Category Code
string sqlString = "UPDATE POIs SET POI_Category=1" +
" WHERE POI_Category=?";
//SQL (Delete Category)
string sqlString2 = "DELETE * FROM POIs_Category WHERE Category_Code=?";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryID);
//Execute SQL (Updating POIs to have Uncategorized Category Code
commandObject.ExecuteNonQuery();
//Execute SQL (Delete Category)
commandObject.CommandText = sqlString2;
commandObject.Parameters.AddWithValue("@Category_Code", parCategoryID);
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// name: GetAllCategories
/// description: Method that retrieves all Categories from database
/// </summary>
public DataTable GetAllCategories()
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//2. Construct sql string
string sqlString = "Select * From POIs_Category" + " Order By Category_Code";
//3. Build Command object
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//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();
return(dataTableObject);
}
/// <summary>
/// name: GetAllCategories
/// description: Method that retrieves all Categories from database
/// </summary>
public DataTable GetAllCategories()
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//2. Construct sql string
string sqlString = "Select * From POIs_Category" + " Order By Category_Code";
//3. Build Command object
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//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();
return dataTableObject;
}
public static Nvidia_Info Get_Nvidia_Info(Connection_Info connection_info)
{
string str = Run_command(connection_info, "nvidia-smi");
str = str.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries)[8];
string re1 = ".*?"; // Non-greedy match on filler
string re2 = "(\\d+)"; // Integer Number 1
string re3 = ".*?"; // Non-greedy match on filler
string re4 = "(\\d+)"; // Integer Number 2
string re5 = ".*?"; // Non-greedy match on filler
string re6 = "\\d+"; // Uninteresting: int
string re7 = ".*?"; // Non-greedy match on filler
string re8 = "(\\d+)"; // Integer Number 3
string re9 = ".*?"; // Non-greedy match on filler
string re10 = "(\\d+)"; // Integer Number 4
string re11 = ".*?"; // Non-greedy match on filler
string re12 = "(\\d+)"; // Integer Number 5
string re13 = ".*?"; // Non-greedy match on filler
string re14 = "(\\d+)"; // Integer Number 6
string re15 = ".*?"; // Non-greedy match on filler
string re16 = "(\\d+)"; // Integer Number 7
Regex r = new Regex(re1 + re2 + re3 + re4 + re5 + re6 + re7 + re8 + re9 + re10 + re11 + re12 + re13 + re14 + re15 + re16, RegexOptions.IgnoreCase | RegexOptions.Singleline);
Match m = r.Match(str);
int int1 = Convert.ToInt32(m.Groups[1].ToString());
int int2 = Convert.ToInt32(m.Groups[2].ToString());
int int3 = Convert.ToInt32(m.Groups[3].ToString());
int int4 = Convert.ToInt32(m.Groups[4].ToString());
int int5 = Convert.ToInt32(m.Groups[5].ToString());
int int6 = Convert.ToInt32(m.Groups[6].ToString());
int int7 = Convert.ToInt32(m.Groups[7].ToString());
Nvidia_Info nv = new Nvidia_Info(int1, int2, int3, int4, int5, int6, int7);
return(nv);
}
/// <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: GetPOI
/// description: Method for fetching all the POI's from the database
/// </summary>
///
public DataRow GetPOI(string parID)
{
//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 POI_ID=?" + " 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_ID", parID);
//Use the DataAdapter to fill a DataTable 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);
//Grab the values out of the first (and only) row of
//the DataTable object and put it in a DataRow object
DataRow dataRowObject = dataTableObject.Rows[0];
//Close the connection.
connectionObject.Close();
return dataRowObject;
}
public DataTable GetPOIs()
{
//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_Category, POIs Where POIs_Category.Category_Code=POIs.POI_Category" +
" Order by POI_Title";
//Build Command object
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Use the DataAdapter object to fill the DataTable object
OleDbDataAdapter dataAdapterObject = new OleDbDataAdapter();
DataTable dataTableObject = new DataTable();
dataAdapterObject.SelectCommand = commandObject;
dataAdapterObject.Fill(dataTableObject);
//Close the connection object
connectionObject.Close();
return dataTableObject;
}
/// <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;
}
public void AddImageFilename(string parPOIID,
string parImageFilename,
int parImageNumber)
{
//Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject =
new OleDbConnection(connectionString);
connectionObject.Open();
string sqlString = "UPDATE POIs " +
"SET ";
//TODO: What if bad parImageNumber??
if (parImageNumber == 1)
{
sqlString += " POI_Image1=?";
}
else if (parImageNumber == 2)
{
sqlString += " POI_Image2=?";
}
else if (parImageNumber == 3)
{
sqlString += " POI_Image3=?";
}
else if (parImageNumber == 4)
{
sqlString += " POI_Image4=?";
}
else if (parImageNumber == 5)
{
sqlString += " POI_Image5=?";
}
else if (parImageNumber == 6)
{
sqlString += " POI_Image6=?";
}
sqlString += " WHERE POI_ID=?";
//Set the Command object properties properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_ImageFilename", parImageFilename);
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
//Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//Close the connection
connectionObject.Close();
}
/// <summary>
/// The DeleteCategory method deletes a specified category
/// </summary>
public void DeleteCategory(int parCategoryID)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
//SQL (Updating POIs to have Uncategorized Category Code
string sqlString = "UPDATE POIs SET POI_Category=1" +
" WHERE POI_Category=?";
//SQL (Delete Category)
string sqlString2 = "DELETE * FROM POIs_Category WHERE Category_Code=?";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_Category", parCategoryID);
//Execute SQL (Updating POIs to have Uncategorized Category Code
commandObject.ExecuteNonQuery();
//Execute SQL (Delete Category)
commandObject.CommandText = sqlString2;
commandObject.Parameters.AddWithValue("@Category_Code", parCategoryID);
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
//this determines which picture was deleted then slides the other pictures into place.
//ex. if picture #1 was deleted, the file name in slot 2 would move to slot 1
// and if their was a picture in slot 3 it would move to slot 2
public void OrganizeImages(string parPOIID, int parImageNumber)
{
//Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
string sqlString1 = "";
string sqlString2 = "";
string sqlString3 = "";
string sqlString4 = "";
string sqlString5 = "";
string sqlString6 = "";
if (parImageNumber == 1)
{
sqlString1 = "UPDATE POIs SET POI_Image1 = POI_Image2 WHERE POI_ID=?";
commandObject.CommandText = sqlString1;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString2 = "UPDATE POIs SET POI_Image2 = POI_Image3 WHERE POI_ID=?";
commandObject.CommandText = sqlString2;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString3 = "UPDATE POIs SET POI_Image3 = POI_Image4 WHERE POI_ID=?";
commandObject.CommandText = sqlString3;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 2)
{
sqlString2 = "UPDATE POIs SET POI_Image2 = POI_Image3 WHERE POI_ID=?";
commandObject.CommandText = sqlString2;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString3 = "UPDATE POIs SET POI_Image3 = POI_Image4 WHERE POI_ID=?";
commandObject.CommandText = sqlString3;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 3)
{
sqlString3 = "UPDATE POIs SET POI_Image3 = POI_Image4 WHERE POI_ID=?";
commandObject.CommandText = sqlString3;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 4)
{
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 5)
{
sqlString4 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 6)
{
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
//Close the connection
connectionObject.Close();
}
//this determines which picture was deleted then slides the other pictures into place.
//ex. if picture #1 was deleted, the file name in slot 2 would move to slot 1
// and if their was a picture in slot 3 it would move to slot 2
public void OrganizeImages(string parPOIID, int parImageNumber)
{
//Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
string sqlString1 = "";
string sqlString2 = "";
string sqlString3 = "";
string sqlString4 = "";
string sqlString5 = "";
string sqlString6 = "";
if (parImageNumber == 1)
{
sqlString1 = "UPDATE POIs SET POI_Image1 = POI_Image2 WHERE POI_ID=?";
commandObject.CommandText = sqlString1;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString2 = "UPDATE POIs SET POI_Image2 = POI_Image3 WHERE POI_ID=?";
commandObject.CommandText = sqlString2;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString3 = "UPDATE POIs SET POI_Image3 = POI_Image4 WHERE POI_ID=?";
commandObject.CommandText = sqlString3;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 2)
{
sqlString2 = "UPDATE POIs SET POI_Image2 = POI_Image3 WHERE POI_ID=?";
commandObject.CommandText = sqlString2;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString3 = "UPDATE POIs SET POI_Image3 = POI_Image4 WHERE POI_ID=?";
commandObject.CommandText = sqlString3;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 3)
{
sqlString3 = "UPDATE POIs SET POI_Image3 = POI_Image4 WHERE POI_ID=?";
commandObject.CommandText = sqlString3;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 4)
{
sqlString4 = "UPDATE POIs SET POI_Image4 = POI_Image5 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 5)
{
sqlString4 = "UPDATE POIs SET POI_Image5 = POI_Image6 WHERE POI_ID=?";
commandObject.CommandText = sqlString4;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
sqlString5 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString5;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
if (parImageNumber == 6)
{
sqlString6 = "UPDATE POIs SET POI_Image6='' WHERE POI_ID=?";
commandObject.CommandText = sqlString6;
commandObject.Parameters.AddWithValue("@POI_ID", parPOIID);
commandObject.ExecuteNonQuery();
}
//Close the connection
connectionObject.Close();
}
/// <summary>
/// name: UpdatePOI
/// description: Method for updating a selected POI in the database
/// </summary>
public void UpdatePOI(decimal parLatitude, decimal parLongitude, string parTitle,
string parDescription, string parAddress1, string parAddress2, string parCity, string parState,
int parZipCode, string parContactName, string parPhone, string parURL,
string parCategory, bool parIsActive, string parID, bool parPetFriendly)
{
//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 = "UPDATE POIs " +
"SET " + " POI_Latitude=?," +
" POI_Longitude=?," +
" POI_Title=?," +
" POI_Description=?," +
" POI_Address1=?," +
" POI_Address2=?," +
" POI_City=?," +
" POI_State=?," +
" POI_ZipCode=?," +
" POI_ContactName=?," +
" POI_Phone=?," +
" POI_URL=?," +
" POI_Category=?," +
" IsActive=?," +
" Pet_Friendly=?" +
" WHERE POI_ID=?";
//Set the Command object properties properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_Latitude", parLatitude);
commandObject.Parameters.AddWithValue("@POI_Longitude", parLongitude);
commandObject.Parameters.AddWithValue("@POI_Title", parTitle);
commandObject.Parameters.AddWithValue("@POI_Description", parDescription);
commandObject.Parameters.AddWithValue("@POI_Address1", parAddress1);
commandObject.Parameters.AddWithValue("@POI_Address2", parAddress2);
commandObject.Parameters.AddWithValue("@POI_City", parCity);
commandObject.Parameters.AddWithValue("@POI_State", parState);
commandObject.Parameters.AddWithValue("@POI_ZipCode", parZipCode);
commandObject.Parameters.AddWithValue("@POI_ContactName", parContactName);
commandObject.Parameters.AddWithValue("@POI_Phone", parPhone);
commandObject.Parameters.AddWithValue("@POI_URL", parURL);
commandObject.Parameters.AddWithValue("@POI_Category", parCategory);
commandObject.Parameters.AddWithValue("@IsActive", parIsActive);
commandObject.Parameters.AddWithValue("@Pet_Friendly", parPetFriendly);
commandObject.Parameters.AddWithValue("@POI_ID", parID);
//Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//Close the connection
connectionObject.Close();
}
/// <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: UpdatePOI
/// description: Method for updating a selected POI in the database
/// </summary>
public void UpdatePOI(decimal parLatitude, decimal parLongitude, string parTitle,
string parDescription, string parAddress1, string parAddress2, string parCity, string parState,
int parZipCode, string parContactName, string parPhone, string parURL,
string parCategory, bool parIsActive, string parID, bool parPetFriendly)
{
//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 = "UPDATE POIs " +
"SET " + " POI_Latitude=?," +
" POI_Longitude=?," +
" POI_Title=?," +
" POI_Description=?," +
" POI_Address1=?," +
" POI_Address2=?," +
" POI_City=?," +
" POI_State=?," +
" POI_ZipCode=?," +
" POI_ContactName=?," +
" POI_Phone=?," +
" POI_URL=?," +
" POI_Category=?," +
" IsActive=?," +
" Pet_Friendly=?" +
" WHERE POI_ID=?";
//Set the Command object properties properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_Latitude", parLatitude);
commandObject.Parameters.AddWithValue("@POI_Longitude", parLongitude);
commandObject.Parameters.AddWithValue("@POI_Title", parTitle);
commandObject.Parameters.AddWithValue("@POI_Description", parDescription);
commandObject.Parameters.AddWithValue("@POI_Address1", parAddress1);
commandObject.Parameters.AddWithValue("@POI_Address2", parAddress2);
commandObject.Parameters.AddWithValue("@POI_City", parCity);
commandObject.Parameters.AddWithValue("@POI_State", parState);
commandObject.Parameters.AddWithValue("@POI_ZipCode", parZipCode);
commandObject.Parameters.AddWithValue("@POI_ContactName", parContactName);
commandObject.Parameters.AddWithValue("@POI_Phone", parPhone);
commandObject.Parameters.AddWithValue("@POI_URL", parURL);
commandObject.Parameters.AddWithValue("@POI_Category", parCategory);
commandObject.Parameters.AddWithValue("@IsActive", parIsActive);
commandObject.Parameters.AddWithValue("@Pet_Friendly", parPetFriendly);
commandObject.Parameters.AddWithValue("@POI_ID", parID);
//Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//Close the connection
connectionObject.Close();
}
/// <summary>
/// name: DeleteImage
/// description: Method for deleting a specific image, used the POI ID and ImageNumber(slot passed from POI_Images.aspx)
/// </summary>
public void DeleteImage(string parLocationID, int parImageNumber)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
currentContext.Trace.Warn("imageNum= " + parImageNumber);
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "UPDATE POIs " +
"SET ";
//TODO: What if bad parImageNumber??
if (parImageNumber == 1)
sqlString += " POI_Image1=?";
else if (parImageNumber == 2)
sqlString += " POI_Image2=?";
else if (parImageNumber == 3)
sqlString += " POI_Image3=?";
else if (parImageNumber == 4)
sqlString += " POI_Image4=?";
else if (parImageNumber == 5)
sqlString += " POI_Image5=?";
else if (parImageNumber == 6)
sqlString += " POI_Image6=?";
sqlString += " WHERE POI_ID=?";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_ImageFilename", "");
commandObject.Parameters.AddWithValue("@POI_ID", parLocationID);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//this determines which picture was deleted then slides the other pictures into place.
//ex. if picture #1 was deleted, the file name in slot 2 would move to slot 1
// and if their was a picture in slot 3 it would move to slot 2
if (parImageNumber == 1)
{
sqlString = "UPDATE POIs SET POI_Image1 = POI_Image2 WHERE POI_ID=?";
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@POI_ID", parLocationID);
currentContext.Trace.Warn("sqlstring= " + sqlString);
commandObject.ExecuteNonQuery();
}
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// The add category method adds a category to the database given user input
/// </summary>
public void AddCategory(string parCategoryName)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "INSERT INTO POIs_Category " +
"(Category_Name)" +
"VALUES(?)";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@Category_Name", parCategoryName);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// The update category method updates a specific category based on a given category ID and
/// supplied user input.
/// </summary>
public void UpdateCategory(string parCategoryName, string parCategoryCode)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "UPDATE POIs_Category " +
"SET Category_Name=? " + "WHERE Category_Code=? ";
currentContext.Trace.Warn("sqlString = " + sqlString);
//3b. Set the Command object properties properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@Category_Name", parCategoryName);
commandObject.Parameters.AddWithValue("@Category_Code", parCategoryCode);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
/// <summary>
/// name: DeleteImage
/// description: Method for deleting a specific image, used the POI ID and ImageNumber(slot passed from POI_Images.aspx)
/// </summary>
public void DeleteImage(string parLocationID, int parImageNumber)
{
//1. Build connection object
Connection_Info connectionInfoObject = new Connection_Info();
currentContext.Trace.Warn("imageNum= " + parImageNumber);
string connectionString = connectionInfoObject.poiConnectionString;
OleDbConnection connectionObject = new OleDbConnection(connectionString);
connectionObject.Open();
//3a. Build the sql string
string sqlString = "UPDATE POIs " +
"SET ";
//TODO: What if bad parImageNumber??
if (parImageNumber == 1)
{
sqlString += " POI_Image1=?";
}
else if (parImageNumber == 2)
{
sqlString += " POI_Image2=?";
}
else if (parImageNumber == 3)
{
sqlString += " POI_Image3=?";
}
else if (parImageNumber == 4)
{
sqlString += " POI_Image4=?";
}
else if (parImageNumber == 5)
{
sqlString += " POI_Image5=?";
}
else if (parImageNumber == 6)
{
sqlString += " POI_Image6=?";
}
sqlString += " WHERE POI_ID=?";
//3. Set the Command object properties
OleDbCommand commandObject = new OleDbCommand();
commandObject.Connection = connectionObject;
commandObject.CommandType = CommandType.Text;
commandObject.CommandText = sqlString;
//3c. Add parameters to collection
commandObject.Parameters.AddWithValue("@POI_ImageFilename", "");
commandObject.Parameters.AddWithValue("@POI_ID", parLocationID);
//4. Execute the INSERT command on the database
commandObject.ExecuteNonQuery();
//this determines which picture was deleted then slides the other pictures into place.
//ex. if picture #1 was deleted, the file name in slot 2 would move to slot 1
// and if their was a picture in slot 3 it would move to slot 2
if (parImageNumber == 1)
{
sqlString = "UPDATE POIs SET POI_Image1 = POI_Image2 WHERE POI_ID=?";
commandObject.CommandText = sqlString;
commandObject.Parameters.AddWithValue("@POI_ID", parLocationID);
currentContext.Trace.Warn("sqlstring= " + sqlString);
commandObject.ExecuteNonQuery();
}
//6. Close the connection.
//Always do this!
connectionObject.Close();
}
