ASP.NET pages without code-behind
Created 21 October 2004 00:00
Although this is not the best way to do it, there may be instances where you can't use a code-behind cs file. In that case, here is a basic example of how to set up the aspx file:
<%@Page language="C#"%>
<%@ Import Namespace="System" %>
<script runat="server" language="C#">
private void Page_Load(object sender, System.EventArgs e){
  this.btn.Value = "New value!";
}
</script>
<html>
<head>
<title>Example</title>
</head>
<body leftmargin="0" topmargin="0">
<form id="frmMain" runat="server" method="GET">
  <input type="button" name="btn" id="btn" runat="server">
</form>
</body>
</html>

Inheritance

For sites that need a standard look and feel, you can still inherit from a base page class. This will require the creation of a base class, from which the .aspx files inherit, as follows:

PageBase.cs

using System;
using System.Web.UI;

public class PageBase : System.Web.UI.Page {
  
  private string title = String.Empty;
  public string Title {
    get { return title; }
    set { title = value; }
  }

  protected override void Render(HtmlTextWriter writer) {
    writer.Write("<html><head><title>" +
      Title + "</title></head><body>");
    base.Render( writer );
    writer.Write("</body></html>");
  }
}

Derived.aspx

<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Web.UI" %>
<%@ Inherits="PageBase" Src="PageBase.cs" Debug="true" %>
<script runat="server" language="C#">
  private void Page_Load(object sender, System.EventArgs e){
    this.Title = "test title";
  }
</script>
<form id="Derived" method="post" runat="server">
    <p>
    This is a simple test page.
    </p>
</form>
SQL : UPDATE Subqueries
Created 11 October 2004 00:00
Example:

Copying the value of 1 field of a record in a table, to the same field of another record in the same table:

UPDATE MyTable 
SET Col1 = (SELECT Col1 FROM MyTable WHERE PK_Col = 8)
WHERE PK_Col = 9

Example:

Copying multiple fields and multiple records from one table to another:

  UPDATE Table1 SET 
    Col1 = t2.Col1, 
    Col2 = t2.Col2
  FROM Table2 t2
  INNER JOIN Table1 t1
  ON t2.PrimKey = t1.PrimKey
Forms Authentication on Subfolders
Created 07 October 2004 00:00

Forms authentication generally protects your entire IIS application. However, sometimes you may want to only protect subfolders. Usually you would make the subfolder an application which would have it's own web.config file, but this has two drawbacks:

1. You need to maintain two config files
2. In shared hosting you generally aren't allowed to create extra IIS applications.

You can protect ONLY subfolders in your web.config file, as follows (This would ONLY allow the user MattS access to the "admin" subfolder):

In the main section of your config file:

  <authentication mode="Forms">
    <forms name="AuthCookie" loginUrl="admin/login.aspx" timeout="30" path="/">
      <credentials passwordFormat="Clear">
        <user name="MattS" password="test"/>
      </credentials>
    </forms>
  </authentication>
  
  <!-- allow everyone access at the root level -->  
  <authorization>
    <allow users="*"/>
  </authorization>

At the bottom of your config file (outside the system.web tag):

  <!-- allows access to the forum folder for the authenticated user MattS only -->
  <location path="admin">  
    <system.web>   
      <authorization>    
        <allow users="MattS"/>
        <deny users="*"/>
      </authorization>  
    </system.web> 
  </location>
SQL Server's OPENXML - Inserting XML rows into a table
Created 11 October 2004 00:00

The OPENXML command provides a rowset view over an XML document and can be used to insert the contents of an XML document into a table.

CREATE PROCEDURE Test
  @pXml text
AS
  DECLARE @iDoc int
  -- create an internal representation of the XML document.
  EXEC sp_xml_preparedocument @iDoc OUTPUT, @pXml
  -- execute a SELECT statement that uses the OPENXML rowset provider.
  INSERT INTO <table>
    SELECT Col1, Col2 FROM OPENXML (@iDoc, '/root/child', 1)
  WITH 
  (
    Col1  varchar(10),
    Col2 varchar(40)
  )
  EXEC sp_xml_removedocument @iDoc

If you get namespace declaration problems, clean your xml before you pass it into the stored procedure, for example:

  string xml = "<root>" + xmlDoc.DocumentElement.InnerXml + "</root>";
  xml = xml.Replace("<ns:", "<");
MySQL : Adding Users
Created 12 October 2004 00:00

Log in to mysql:

 mysql --user=root <database>

Grant all privileges on the local machine:

  GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Grant all privileges from other machines:

  GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Oracle SplitToTable Function
Created 19 October 2004 00:00
CREATE FUNCTION SplitToTable (
  @list ntext , 
  @delim varchar(10)
  )
RETURNS @tbl TABLE (
  listpos int IDENTITY(1, 1) NOT NULL,
  number  int NOT NULL) 
AS
BEGIN
  DECLARE 
    @pos      int,
    @textpos  int,
    @chunklen smallint,
    @str      nvarchar(4000),
    @tmpstr   nvarchar(4000),
    @leftover nvarchar(4000) 

  SET @textpos = 1
  SET @leftover = ''
  WHILE @textpos <= datalength(@list) / 2
  BEGIN
    SET @chunklen = 4000 - datalength(@leftover) / 2
    SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
    SET @textpos = @textpos + @chunklen
    SET @pos = charindex(@delim, @tmpstr)
    WHILE @pos > 0
    BEGIN
      SET @str = substring(@tmpstr, 1, @pos - 1)
      INSERT @tbl (number) VALUES(convert(int, @str))
      SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
      SET @pos = charindex(@delim , @tmpstr)
    END
    SET @leftover = @tmpstr
  END
  IF ltrim(rtrim(@leftover)) <> ''
  INSERT @tbl (number) VALUES(convert(int, @leftover))
  RETURN
END
Caching tips for ASP.NET
Created 04 October 2004 00:00

Caching - File Dependancies

You can output the contents of a file from the cache, and ASP.NET provides the facility to clear the cache whenever the contents of the file change. For example:

  DataSet ds = (DataSet)Cache["MyInfo"];
  if (ds == null) {
    ds = new DataSet();
    ds.ReadXml(MapPath("MyInfo.xml"));
    Cache.Insert("MyInfo", ds, Caching.CacheDependency(MapPath("MyInfo.xml")));
  }
  // do whatever you want with the dataset here...
  DropDownList1.DataSource = ds;  ...

Output Caching

As per the microsoft documentation, pages can be cached on the server with the following:

  <%@ OutputCache Duration="60" VaryByParam="None" Location="Server: %>

This can also be cleared on another page (if someone changes the values to be shown on an item, for example), using the static RemoveOutputCacheItem method.

If you want to clear the output cache for "http://yourserver/webSiteRoot/cachedFile.aspx", then in your page that does the clear call, do the following:

  HttpResponse.RemoveOutputCacheItem("/webSiteRoot/cachedFile.aspx"); 

Output Caching - Clearing using Cache Dependencies

An easier way to remove output caching from multiple pages is to attach a cache dependency on those pages. In the page that is actually cached:

  string cacheKey = "mypage.aspx?" + parameters; 
  Cache[cacheKey] = new object(); 
  Response.AddCacheItemDependency(cacheKey); 

Then, on the page that invalidates the cache (for example, an admin page that inserts/updates database records):

  string cacheKey = "mypage.aspx?" + parameters; 
  Cache.Remove(cacheKey);