Selecting a view from other database Options
wesleywillians
Posted: Wednesday, November 14, 2007 6:51:50 PM
Rank: Devotee

Joined: 9/14/2007
Posts: 86
Hi all,

I need to select a table that is in another database and display the data.
The database is the SQL Server. How can I do that?
Is the umbraco has any library to do it?

All the best
Wesley

Wesley Willians - wesley.blog.br
hoehler
Posted: Thursday, November 15, 2007 8:56:13 AM

Rank: Addict

Joined: 7/19/2006
Posts: 597
Location: Bad Homburg, Germany
No, you have to implement your own usercontrols or aspx...

Thomas

• 2007/2008 MVP • www.thoehler.com • Bad Homburg, Germany
imayat12
Posted: Thursday, November 15, 2007 10:01:36 AM

Rank: Addict

Joined: 7/19/2006
Posts: 649
Location: Preston, UK
Guys,

I have been meaning to create a templated server control where you can pass into it a conn string a query and csv list of fields to display. Then you could using the control template write out the data.

Its just getting the time to do it!:(

Regards

Ismail

Level 2 certified. If it aint broke dont fix.
sjors
Posted: Thursday, November 15, 2007 10:10:25 AM

Rank: Fanatic

Joined: 7/20/2006
Posts: 408
Location: Amsterdam
Maybe a better workaround is to create a Small webservice to retrieve this data?
imayat12
Posted: Thursday, November 15, 2007 10:27:45 AM

Rank: Addict

Joined: 7/19/2006
Posts: 649
Location: Preston, UK
Sjors,

Yes just get it to send back xml and you could then transform it to how you like :w00t: still problem of finding time to do it:(

Regards

Ismail

Level 2 certified. If it aint broke dont fix.
jesper
Posted: Thursday, November 15, 2007 11:08:30 PM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
How about an extension that implements this overloaded method:

Code:


        public static XPathNodeIterator SQL2XML(string sqlexpression)
        {

            return utils.SQLstuff.SQLXml(sqlexpression, umbraco.GlobalSettings.DbDSN);

        }

        public static XPathNodeIterator SQL2XML(string sqlexpression , string connectstring) {

            SqlConnection conn = new SqlConnection(connectstring);
            conn.Open();
            XmlReader xreader = SqlHelper.ExecuteXmlReader(conn, CommandType.Text, sqlexpression + " FOR XML AUTO");

            return new XPathDocument(xreader).CreateNavigator().Select(".");
           

        }


Now from your xslt you call it with:

Code:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]>
<xsl:stylesheet
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxml="urn:schemas-microsoft-com:xslt"
    xmlns:umbraco.library="urn:umbraco.library"
  xmlns:jesper.utils="urn:jesper.utils"
    exclude-result-prefixes="msxml umbraco.library jesper.utils">


<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:param name="currentPage"/>

<xsl:template match="/">

  <xsl:variable name="sqlstuff" select="jesper.utils:SQL2XML('select * from cmsContent')"/>
  <xsl:copy-of select="$sqlstuff"/>

 
</xsl:template>

</xsl:stylesheet>


(my extension lib is called jesper.utils)

Remember to add your extension to the file: /config/xsltExtensions.config like this:

Code:

<?xml version="1.0" encoding="utf-8"?>
<XsltExtensions>
   <ext assembly="/bin/jesper.umbracoutils" type="jesper.umbracoutils.library" alias="jesper.utils" />
</XsltExtensions>



Kindly,

Jesper

webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
wesleywillians
Posted: Friday, November 16, 2007 2:15:45 AM
Rank: Devotee

Joined: 9/14/2007
Posts: 86
Hi Vesper,

Is it only an example or you have already created it?
Do you think possible to get the data from another Database?

Where (which file) Can I overrride the XPath lib to recompile it with this new feature?

Dont you have source files, Do you?

All the best
Wesley

Wesley Willians - wesley.blog.br
jesper
Posted: Friday, November 16, 2007 7:55:43 AM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
Wesley Willians wrote:

Hi Vesper,

Is it only an example or you have already created it?
Do you think possible to get the data from another Database?

Where (which file) Can I overrride the XPath lib to recompile it with this new feature?

Dont you have source files, Do you?

All the best
Wesley


I'm Jesper :D

Hi Wesley,

I'm using it in a project - I just pulled some code out for you. Yes it's possible to get the data from any MS SQL database.

You can use the code to create your own extension. Lots of packages uses this approach. The best is you don't have to override and recompile anything. Just put the code in a separate project and make a dll, reference it in the xsltextension.config as shown above.

I'll compile a dll later today.

Kindly,

Jesper

webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
jesper
Posted: Friday, November 16, 2007 6:06:19 PM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark

I only want to use the VS2005 if I have to. If possible I use autoform and xslt for as much as possible and I rarely create custom usercontrols. I prefer to create simple extensions that I can reuse over and over again. jesper.umbracoSQL really helps me cover gabs when data needs to go into db's.

I've created a zip with the dll and an example xslt file. I've put it up on this page ...

Features:

Do your SQL magic and get the result back in XML (using FOR XML AUTO)
Uses umbracodb as default but you can specify other using ordinary connectstring.
There's a Executescalar for updating db's too. (this is what I love most .. supereasy)
Manual installation .. (sorry - no package)



Kindly,

Jesper

webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
wesleywillians
Posted: Saturday, November 17, 2007 12:27:57 AM
Rank: Devotee

Joined: 9/14/2007
Posts: 86
Hi JESPER,

Thank you a lot.
I didnt understand how can I change to uses another database instead of the umbraco's db.
Do you have any example?

Thanks
All the best
Wesley

Wesley Willians - wesley.blog.br
jesper
Posted: Saturday, November 17, 2007 1:07:07 AM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
Hi,

Just use the overloaded method that takes a second parameter "connectstring", which is exactly that - a regular connectstring. Like this:

Code:


    <xsl:variable name="sqlstuff" select="jesper.sql:SQLXml('select * from testtable','Server=127.0.0.1;Database=umbBabygogogo_303;User ID=ferrari;Password=ismine;Trusted_Connection=False')"/>



If you use the build in xslt editor in umbraco and click the insert <xsl:value-of/> button and from there you select "extensions" then you'll find all available extension libs and their methods.. after that you can change it to put the value in a variable (like i did here).

Kindly

Jesper

ps. Niels - the hidden extension browser is a frustation :p



webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
jesper
Posted: Saturday, November 17, 2007 1:12:55 AM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
Code:




    <xsl:variable name="sqlstuff" select="jesper.sql:SQLXml('select * from testtable','Server=127.0.0.1;Database=umbBabygogogo_303;User ID=ferrari;Password=ismine;Trusted_Connection=False')"/>







webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
SoerenS
Posted: Thursday, November 29, 2007 5:07:26 PM

Rank: Fanatic

Joined: 7/25/2006
Posts: 424
Location: Silkeborg, Denmark
Jesper, please view this thread for some very valid security concerns Audrius has raised regarding your extension.

/SoerenS

Brug for råd til hvordan du driver en god webshop? / Need advice on how to run an effective webshop?
jesper
Posted: Thursday, November 29, 2007 5:30:47 PM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
Soeren Sprogoe wrote:

Jesper, please view this thread for some very valid security concerns Audrius has raised regarding your extension.

/SoerenS

Yep - I just replied. IMHO it's xslt files being public available which is the real hazard. My extension just points it out.

Kindly,

Jesper

webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
jesper
Posted: Friday, December 07, 2007 2:19:59 PM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
I've updated the library to take dbDNS as a secondary parameter. This to ensure that u don't expose your db user/password in your xslt files.

<xsl:variable name="sqlstuff" select="jesper.sql:SQLXml('select * from testtable','otherdbDNS')"/>

where otherdbDNS is a key under AppSettings in web.config.

Download from http://www.jesper.com/start-page/umbracostuff.aspx

Kindly,
Jesper

webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
Nuser
Posted: Friday, January 18, 2008 3:50:24 PM
Rank: Enthusiast

Joined: 1/12/2008
Posts: 13
Hi,

I've downloaded the files for testing but encountered an "Error parsing XSLT file: \xslt\sqltest.xslt".

Here's what I've done:-
- copied jesper.umbracosql.dll to bin folder
- copied sqltest.xslt to xslt folder
- update xsltExtension with the required string
- create a macro sqltest to link to sqltest.xslt
- create a template with a richtext body type
- in the template added the following:-
<html>
<header></header>
<body><?UMBRACO_MACRO macroAlias="sqltest" ></?UMBRACO_MACRO></body>
</html>
- create a page sqltest in the content page using the above template.

When I look at the xslt and tried to save it, I've encountered the following error:-
Error occured
System.Xml.XPath.XPathException: Function 'jesper.sql:SQLExecuteScalarInt()' has failed. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open() .................


What have I missed out?:hmm:


jesper
Posted: Monday, January 21, 2008 9:15:39 AM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
Hi Nuser,

It looks like your connectstring is empty or at least not correct?

Could you paste your xslt statement that makes the request. And your connectstring from web.config

Kindly,
Jesper

Nuser wrote:

Hi,

I've downloaded the files for testing but encountered an "Error parsing XSLT file: \xslt\sqltest.xslt".

Here's what I've done:-
- copied jesper.umbracosql.dll to bin folder
- copied sqltest.xslt to xslt folder
- update xsltExtension with the required string
- create a macro sqltest to link to sqltest.xslt
- create a template with a richtext body type
- in the template added the following:-
<html>
<header></header>
<body><?UMBRACO_MACRO macroAlias="sqltest" ></?UMBRACO_MACRO></body>
</html>
- create a page sqltest in the content page using the above template.

When I look at the xslt and tried to save it, I've encountered the following error:-
Error occured
System.Xml.XPath.XPathException: Function 'jesper.sql:SQLExecuteScalarInt()' has failed. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The ConnectionString property has not been initialized.
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open() .................


What have I missed out?:hmm:






webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
Nuser
Posted: Monday, January 21, 2008 4:13:58 PM
Rank: Enthusiast

Joined: 1/12/2008
Posts: 13
Hi,

Here's my connection string : -

<add key="umbracoDbDSN" value="Server=localhost\SQLEXPRESS;Database=CMS;User IDSpeak to the handPasswordSpeak to the handTrusted_Connection=True" />

and the xlst is as follows:-


Code:
<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]> 
<xsl:stylesheet   
    version="1.0"   
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"   
    xmlns:msxml="urn:schemas-microsoft-com:xslt" 
    xmlns:umbraco.library="urn:umbraco.library" 
    xmlns:jesper.sql="urn:jesper.sql"
    exclude-result-prefixes="msxml umbraco.library jesper.sql">

 
 
<xsl:output method="xml" omit-xml-declaration="yes"/> 
 
<xsl:param name="currentPage"/> 
 
<xsl:template match="/"> 
 
  <xsl:variable name="sqlstuff" select="jesper.sql:SQLXml('select * from cmsContent')"/> 
  <xsl:copy-of select="$sqlstuff"/> 
   
     
</xsl:template>

Tks n Rgds
jesper
Posted: Monday, January 21, 2008 6:24:00 PM

Rank: Administration

Joined: 7/25/2006
Posts: 415
Location: vipperoed, denmark
Hi Nuser,

I found a bug. Sorry for the trouble. I'e uploaded a new version to
http://www.jesper.com/start-page/umbracostuff.aspx

Kindly,

Jesper

Nuser wrote:

Hi,

Here's my connection string : -

<add key="umbracoDbDSN" value="Server=localhost\SQLEXPRESS;Database=CMS;User IDSpeak to the handPasswordSpeak to the handTrusted_Connection=True" />

and the xlst is as follows:-


Code:
<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp "&#x00A0;"> ]> 
<xsl:stylesheet   
    version="1.0"   
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"   
    xmlns:msxml="urn:schemas-microsoft-com:xslt" 
    xmlns:umbraco.library="urn:umbraco.library" 
    xmlns:jesper.sql="urn:jesper.sql"
    exclude-result-prefixes="msxml umbraco.library jesper.sql">

 
 
<xsl:output method="xml" omit-xml-declaration="yes"/> 
 
<xsl:param name="currentPage"/> 
 
<xsl:template match="/"> 
 
  <xsl:variable name="sqlstuff" select="jesper.sql:SQLXml('select * from cmsContent')"/> 
  <xsl:copy-of select="$sqlstuff"/> 
   
     
</xsl:template>

Tks n Rgds



webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
Nuser
Posted: Tuesday, January 22, 2008 1:41:20 AM
Rank: Enthusiast

Joined: 1/12/2008
Posts: 13
Hi,

I have reinstalled the new version and hit the following error:-

Code:
Server Error in '/' Application.

Could not load file or assembly 'jesper.umbracoSQL' or one of its dependencies. Access is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IO.FileLoadException: Could not load file or assembly 'jesper.umbracoSQL' or one of its dependencies. Access is denied.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 

Assembly Load Trace: The following information can be helpful to determine why the assembly 'jesper.umbracoSQL' could not be loaded.


WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog


Tried adding following into web.config as suggested in one of the forum post:-

Code:
<location path="." inheritInChildApplications="false"> </location>


but encountered the following error:-

Code:
Server Error in '/' Application.
--------------------------------------------------------------------------------

Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.

Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".


<!-- Web.Config Configuration File -->

<configuration>
    <system.web>
        <customErrors mode="Off"/>
    </system.web>
</configuration>


Notes: The current error page you are seeing can be replaced by a custom error page by modifying the "defaultRedirect" attribute of the application's <customErrors> configuration tag to point to a custom error page URL.


<!-- Web.Config Configuration File -->

<configuration>
    <system.web>
        <customErrors mode="RemoteOnly" defaultRedirect="mycustompage.htm"/>
    </system.web>
</configuration>


Rgds
Users browsing this topic
Guest


You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.