How to support other sql data sources in new control
Hi everyone, I've made a new gridview control for Umbraco binding a sql data source as input. It works nicely on SQL server, and I am just about to wrap it up as a new package.
However, I would like to be able to support the use of other data sources also (and not necessarily the db that the Umbraco installation uses), e.g. MySql databases etc. - maybe even without package users having to edit and recompile the full package when adding support for a new data source. For a momemt I was considering using app_code...
So, is there a best practice approach for doing this kind of data source integration? I could hardcode some MySql connector stuff into the control, but then I would need to supply some specific mysql connector dll's with the package also, which I do not want to.
What you could do is have a look at the Application.SqlHelper Class? This class abstract away the underlying database so in theory you could use MySql also. Depends a bit on the dialect you are using in your sql statements.
No, you can use the SqlHelper class for any connections. If your tables are in the Umbraco DB you can just use it (as that's what it holds a connection to).
You'll have to write in-line SQL statements and do parameterising of the sql yourself though.
I'd suggest using LINQ to SQL or any other ORM framework, it's how we do interaction with custom tables. Means we don't have to write SQL ourselves and it's a lot faster
Actually I would like to use the SqlHelper class for my purpose, but it seems to me that I need to know the field names of the query to get access to the values, thus having to parse the input sql query or, alternatively, generate the sql query from input field names and tables thus loosing the some of the power of sql expressions and making the mechanism much more complex.
I have been looking for ways to fetch the available field names dynamically from e.g. the IRecordsReader, but apparently thats not possible.
Hi Tarek, it's working on SQLServer, but I had hoped for a more generic solution for other db's also. I still have not found a solution for this yet, though. Unfortunately.
I'll wrap up my current stuff one of the next days and post an update here.
Ok, now I have a new master/detail gridview control ready for test and download
I solved the generic data source issue a bit differently as origninally planned, because I implemeted all data sources as separate dll plugins, so it should be possible relatively easily to include new data sources.
If you download and test the package, please provide some feedback as to whether it worked out for you, or if you had any issues installing/using it. Mail to tommy at blackpoint dot dk.
How to support other sql data sources in new control
Hi everyone, I've made a new gridview control for Umbraco binding a sql data source as input. It works nicely on SQL server, and I am just about to wrap it up as a new package.
However, I would like to be able to support the use of other data sources also (and not necessarily the db that the Umbraco installation uses), e.g. MySql databases etc. - maybe even without package users having to edit and recompile the full package when adding support for a new data source. For a momemt I was considering using app_code...
So, is there a best practice approach for doing this kind of data source integration? I could hardcode some MySql connector stuff into the control, but then I would need to supply some specific mysql connector dll's with the package also, which I do not want to.
Any ideas ... ?
>Tommy
Hi Tommy,
What you could do is have a look at the Application.SqlHelper Class? This class abstract away the underlying database so in theory you could use MySql also. Depends a bit on the dialect you are using in your sql statements.
Hope it helps you,
Richard
Hi Richard, and thanks for the reply - I was considering this, actually, but this only applies to the underlying Umbraco database, right?
No, you can use the SqlHelper class for any connections. If your tables are in the Umbraco DB you can just use it (as that's what it holds a connection to).
You'll have to write in-line SQL statements and do parameterising of the sql yourself though.
I'd suggest using LINQ to SQL or any other ORM framework, it's how we do interaction with custom tables. Means we don't have to write SQL ourselves and it's a lot faster
Thanks guys, I'll investigate the SqlHelper further to come up with a solution. And maybe even take a look at LINQ...
Actually I would like to use the SqlHelper class for my purpose, but it seems to me that I need to know the field names of the query to get access to the values, thus having to parse the input sql query or, alternatively, generate the sql query from input field names and tables thus loosing the some of the power of sql expressions and making the mechanism much more complex.
I have been looking for ways to fetch the available field names dynamically from e.g. the IRecordsReader, but apparently thats not possible.
Any suggestions on how to solve this?
Hi Tommy,
Is your package to bind a UserControl to a GridView ready ?
I am interested.
Tarek.
Hi Tarek, it's working on SQLServer, but I had hoped for a more generic solution for other db's also. I still have not found a solution for this yet, though. Unfortunately.
I'll wrap up my current stuff one of the next days and post an update here.
>Tommy
Ok, now I have a new master/detail gridview control ready for test and download
I solved the generic data source issue a bit differently as origninally planned, because I implemeted all data sources as separate dll plugins, so it should be possible relatively easily to include new data sources.
If you download and test the package, please provide some feedback as to whether it worked out for you, or if you had any issues installing/using it. Mail to tommy at blackpoint dot dk.
>Tommy
is working on a reply...