SQL in C# script in Xslt

Jul 22, 2011 at 1:51 PM
Edited Jul 22, 2011 at 1:53 PM

Is it possible to use a query in a C# script in xslt?

Something like the code below.

Or is there another solution for DNN to do this without building, WAP etc.?  

 

<xsl:variable name="KantoorNaam"/>

<xsl:variable name="Prefix" select="substring-before(@user,'_')"/> 

<msxsl:script language="C#" implements-prefix="script">
  using System.Data.SqlClient;
  public int HaalKantoorNaam(string PrefixUserID)
  {

	SqlConnection myConnection = new SqlConnection("user id=username;" + "password=pwd;server=servername;" + "Trusted_Connection=yes;" +  "database=dnn; " + "connection timeout=20");

	try {     myConnection.Open(); } 
	catch(Exception e) {     Console.WriteLine(e.ToString()); }

	SqlParameter Prefix = new SqlParameter("@Prefix", SqlDbType.nVarChar, 100); Prefix.Value = @prefix;
	SqlParameter KantoorNaam = new SqlParameter("KantoorNaam", SqlDbType.nVarChar, 100); 

	try  { 
	 SqlDataReader myReader = null; 
	 SqlCommand myCommand = new SqlCommand("SELECT DisplayName FROM Users WHERE username = @Prefix", myConnection); 
  
	 myReader = myCommand.ExecuteReader(); 
	 while(myReader.Read()) 
 		{ 
 		KantoorNaam = (myReader["DisplayName"].ToString()); 
 		KantoorNaam.Value = (myReader["DisplayName"].ToString());
 		} 
	 }  catch (Exception e)  {  Console.WriteLine(e.ToString());  }    

	try {     myConnection.Close(); } catch(Exception e) {     Console.WriteLine(e.ToString()); }

 	return (Kantoornaam);
  }
</msxsl:script>

<xsl:template match="/">
<xsl:value-of select="script:HaalKantoorNaam(Prefix)"/>
</xsl:template>