<script language="c#" runat=server>
//------------------------------------------------------------------------------------------------------------------
// Functions for replacing XML entities in strings
string ToXmlString(string value) { return value.Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;"); }
string FromXmlString(string value) { return HttpUtility.HtmlDecode(value); }
string ToHtmlString(string value) { return value.Replace("&", "&amp;").Replace("\"", "&quot;"); }
//------------------------------------------------------------------------------------------------------------------
// Functions for writting strings as XML attributes 
string ToXml(string value) { return "'" + value.Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "'"; }
string ToXml(string name, string value) {
   if (value.IndexOfAny("&'<".ToCharArray()) < 0) return " " + name + "='" + value + "'";
   return " "+name+"='" + value.Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "'";
   }
string ToXml(System.Data.OleDb.OleDbDataReader R, string colname) {
   string value = R[colname].ToString();
   if (value.IndexOfAny("&'<".ToCharArray()) < 0) return "'" + value + "'";
   return "'" + value.Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "'";
   }
string ToXml(string name, System.Data.OleDb.OleDbDataReader R, string colname) {
   string value = R[colname].ToString();
   if (value.IndexOfAny("&'<".ToCharArray()) < 0) return " " + name + "='" + value + "'";
   return " " + name + "='" + value.Replace("&", "&amp;").Replace("'", "&apos;").Replace("<", "&lt;") + "'";
   }

//------------------------------------------------------------------------------------------------------------------
// Functions for generating SQL commands
   
// Returns string in ' ' with doubled all '   
string ToSql(string value) { return "'" + (value==null?"":value.Replace("'", "''")) + "'"; }
string ToSql(System.Xml.XmlElement I, string name) { return "'" + I.GetAttribute(name).Replace("'", "''") + "'"; }

// Returns one string for UPDATE command, for string types
// Returns "name='value',"
string ToSqlUpdateString(string name, string value) { return value == null ? "" : name + "='" + value.Replace("'", "''") + "',"; }
string ToSqlUpdateString(string name, System.Xml.XmlElement I, string attname) { return I.HasAttribute(attname) ? name + "='" + I.GetAttribute(attname).Replace("'", "''") + "'," : ""; }

// Returns one string for UPDATE command, for number types
// Returns "name=value,"
string ToSqlUpdateNumber(string name, string value) { return value == null || value == "" ? "" : name + "=" + value + ","; }
string ToSqlUpdateNumber(string name, System.Xml.XmlElement I, string attname) { return I.HasAttribute(attname) ? name + "=" + I.GetAttribute(attname) + "," : ""; }

// Deletes all commas on the end of string
// Useful when building comma separated list in loop, call it after list is built to strip ending comma(s)
string TrimSQL(string value) { return value == null ? "" : value.TrimEnd(" ,".ToCharArray()); }


//------------------------------------------------------------------------------------------------------------------
// Functions for parsing TreeGrid output XML format 

// returns id attribute splitted by '$'
string[] GetIds(System.Xml.XmlElement I) { return I.GetAttribute("id").Split("$".ToCharArray()); }
      
// Returns <Changes> element
System.Xml.XmlElement GetChanges(string XML) {
   System.Xml.XmlDocument X = new System.Xml.XmlDocument();
   X.LoadXml(HttpUtility.HtmlDecode(XML));
   System.Xml.XmlNodeList Ch = X.GetElementsByTagName("Changes");
   if (Ch.Count == 0) return null;
   return (System.Xml.XmlElement) Ch[0];
   }


//------------------------------------------------------------------------------------------------------------------
// Helper class for Schools example
class TGSchools
{
   System.Data.OleDb.OleDbConnection Conn;
   HttpContext Context;
   string OrigFile, CopyFile;
   const string ConnStr1 = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=\"";
   const string ConnStr2 = "\";Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=\"Microsoft.Jet.OLEDB.4.0\";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";
   System.Data.OleDb.OleDbCommand Cmd;
   public bool IsCopy;
   System.Data.OleDb.OleDbDataReader Reader;
   string Path;

   //------------------------------------------------
   public TGSchools(HttpContext context)
   {
      // --- Initialization ---
      Context = context;
      Path = System.IO.Path.GetDirectoryName(Context.Request.PhysicalPath);
      string IP = Context.Request.ServerVariables["REMOTE_HOST"];
      CopyFile = Path + "\\..\\ServerDLL\\tmp\\" + IP + "Database.mdb";
      OrigFile = Path + "\\Database.mdb";
      IsCopy = System.IO.File.Exists(CopyFile);
      Reader = null;
      
      // --- Database initialization ---
      Conn = new System.Data.OleDb.OleDbConnection(ConnStr1 + (IsCopy ? CopyFile : OrigFile) + ConnStr2);
      Conn.Open();
      Cmd = Conn.CreateCommand();

      
      // --- Response initialization ---
      Context.Response.ContentType = "text/xml";
      Context.Response.Charset = "utf-8";
      System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
   }

   //------------------------------------------------
   // Copies database according IP and opens it
   public void Copy()
   {
      if (!IsCopy)
      {
         if (Reader!=null) { Reader.Close(); Reader = null; }
         Conn.Close();
         
         // --- Deletes old database files ---
         string[] Files = System.IO.Directory.GetFiles(Path + "\\..\\ServerDLL\\tmp", "*.*");
         DateTime T = DateTime.Now - new TimeSpan(12, 0, 0);
         foreach (string F in Files) {
            try { if (System.IO.File.GetLastAccessTime(F) < T) System.IO.File.Delete(F); }
            catch (Exception E) { }
            }
         
         // ---
         System.IO.File.Copy(OrigFile, CopyFile);
         Conn = new System.Data.OleDb.OleDbConnection(ConnStr1 + CopyFile + ConnStr2);
         Conn.Open();
         IsCopy = true;
         Cmd = Conn.CreateCommand();
         }
      }
   
   
   //------------------------------------------------
   // Executes SQL command (insert/update/delete)
   public void Exec(string SQL){
      if (Reader != null) { Reader.Close(); Reader = null; }
      Cmd.CommandText = SQL;
      Cmd.ExecuteNonQuery();
   }

   //------------------------------------------------
   // Executes SQL query (select)
   public System.Data.OleDb.OleDbDataReader Read(string SQL)
   {
      if (Reader != null) Reader.Close();
      Cmd.CommandText = SQL;
      Reader = Cmd.ExecuteReader();
      return Reader;
   }

   //------------------------------------------------
   // Returns Request parameter, if does not exist returns empty string (not null)
   public string GetParam(string name)
   {
      string value = Context.Request[name];
      return value == null ? "" : value;
   }

   //------------------------------------------------
   // Call it for releasing connection
   public void Dispose()
   {
      if (Reader != null) Reader.Close();
      Conn.Close();
   }
  
};
//------------------------------------------------------------------------------------------------------------------

</script>
