|
|
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
|
|
 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
|
|
 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.
|
|
 Rank: Fanatic
Joined: 7/20/2006 Posts: 408 Location: Amsterdam
|
Maybe a better workaround is to create a Small webservice to retrieve this data?
|
|
 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.
|
|
 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 " "> ]> <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
|
|
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
|
|
 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
|
|
 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
|
|
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
|
|
 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
|
|
 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
|
|
 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?
|
|
 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
|
|
 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.aspxKindly, Jesper webbureau jesper.com doing webdesign / development / umbraco implementations / 2007&2008 MVP
|
|
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:
|
|
 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
|
|
Rank: Enthusiast
Joined: 1/12/2008 Posts: 13
|
Hi, Here's my connection string : - <add key="umbracoDbDSN" value="Server=localhost\SQLEXPRESS;Database=CMS;User ID  Password  Trusted_Connection=True" /> and the xlst is as follows:- Code:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp " "> ]> <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
|
|
 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.aspxKindly, Jesper Nuser wrote:Hi, Here's my connection string : - <add key="umbracoDbDSN" value="Server=localhost\SQLEXPRESS;Database=CMS;User ID  Password  Trusted_Connection=True" /> and the xlst is as follows:- Code:<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE xsl:Stylesheet [ <!ENTITY nbsp " "> ]> <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
|
|
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
|
|
|
Guest |