I'm trying to add a live voting system to Umbraco (V6). My idea was to create a package so it would easily install in Umbraco. I have Umbraco running in Visual Studio 2012 (installed from nuget) and already succeed to implement a "Hello World" example. I'm looking for a few days now to useful documentation how to access a databases a correct way, but couldn't find any. Where can I find it? Or should I do it on an other way?
I also took a look at the source code from Nibble (a Poll voting package), and found the SQLConnection they use and how they execute queries on it. But how do they create their database? I'm not very familiar with databases. Any help would be really appreciated!
In a development environment just create the database and tables yourself. For the productive deployment you can use a package action to create additional tables within the umbraco database. If you have an additional database, that one needs to be created manually. You can then use the package actions only to add an additional connection string and then using the package action to create the tables.
I guess the decision is whether you want the voting data to be held within Umbraco (as Node data) or in a DB. If you hold it in the DB then you could use any tech you want, it's open to your preference.
Long story short, if you want it held in Umbraco then use the API (you don't need to write direct DB access code), if you want it in separate DB tables then use any tech you want to write to your custom tables.
I think I can go a lot further now. I was thinking of using my own database, because I thought it would be more flexible. For local development, the access code is only the connectionstring, right?
Since this morning (after the first reply on this topic) i tried to connect to my database, but didn't succeed (probably it's my connectionstring, but I looked at http://www.connectionstrings.com/sql-server-2012 and didn't see something wrong). So I want to keep the data in Umbraco. In some code I found this:
SqlConnection conn = new SqlConnection(umbraco.GlobalSettings.DbDSN); string select = "select * from tblPoll where memberid = @memberid and pollid = @pollid";
How is "tblPoll" created? Which API should I use? I'm really sorry, but I can't find my way in the API documentation here on our umbraco.
You need to create the poll table yourself. It's easiest if you do this through the management studio of SQL Server. You mentioned that you want to use V6. V6 doesn't use the DbDSN anymore. You can use the connections string by name. V6 also comes with a complete new API for Database access which is very straight forward to use. Check out this blogpost how to query data in Umbraco V6
Not completely Rich, but it will work with your custom objects, it's awesome! Umbraco wrote some extensions on top of PetaPoco to support creating tables from objects as well. Pff Need some time to blog about this ;-)
I had some troubles to get DEWD working, but I finally have a successful database connection! Also I noticed I'm using Umbraco V4 (4.11.3) in VS (nuget gives me still V4), but because I don't use the API of Umbraco I suppose it won't be a problem to upgrade to V6. (Sorry for the confusion.)
Because databases are really new for me, I made a lot mistakes between the differences of SQL CE en the normal SQL. I didn't thought the difference was that big. So SqlConnection doesn't work for SQL CE databases, and SqlCeConnection should be used. Next line of code did the trick:
SqlCeConnection myConn = new SqlCeConnection(umbraco.BusinessLogic.Application.SqlHelper.ConnectionString);
also some stuff had to be different in the queries:
string mySelectQuery = "SELECT [nodeId] FROM cmsContent";
Now I can create my own tables in the database file Umbraco.sdf and work in those tables. Thanks everyone for your help!!!
Database documentation
Hello
I'm trying to add a live voting system to Umbraco (V6). My idea was to create a package so it would easily install in Umbraco. I have Umbraco running in Visual Studio 2012 (installed from nuget) and already succeed to implement a "Hello World" example. I'm looking for a few days now to useful documentation how to access a databases a correct way, but couldn't find any. Where can I find it? Or should I do it on an other way?
I also took a look at the source code from Nibble (a Poll voting package), and found the SQLConnection they use and how they execute queries on it. But how do they create their database? I'm not very familiar with databases. Any help would be really appreciated!
Robin
In a development environment just create the database and tables yourself. For the productive deployment you can use a package action to create additional tables within the umbraco database. If you have an additional database, that one needs to be created manually. You can then use the package actions only to add an additional connection string and then using the package action to create the tables.
Hey Robin,
Welcome to the forum!
I guess the decision is whether you want the voting data to be held within Umbraco (as Node data) or in a DB. If you hold it in the DB then you could use any tech you want, it's open to your preference.
Long story short, if you want it held in Umbraco then use the API (you don't need to write direct DB access code), if you want it in separate DB tables then use any tech you want to write to your custom tables.
Hope that makes sense.
Rich
If you're storing the data in a custom table this package might help: http://our.umbraco.org/projects/developer-tools/dewd
Jeroen
Thanks a lot!
I think I can go a lot further now. I was thinking of using my own database, because I thought it would be more flexible. For local development, the access code is only the connectionstring, right?
Hello
Since this morning (after the first reply on this topic) i tried to connect to my database, but didn't succeed (probably it's my connectionstring, but I looked at http://www.connectionstrings.com/sql-server-2012 and didn't see something wrong). So I want to keep the data in Umbraco. In some code I found this:
SqlConnection conn = new SqlConnection(umbraco.GlobalSettings.DbDSN);
string select = "select * from tblPoll where memberid = @memberid and pollid = @pollid";
How is "tblPoll" created? Which API should I use? I'm really sorry, but I can't find my way in the API documentation here on our umbraco.
Hey Robin,
There would be no Umbraco API to work with your custom table.
You could use whatever database access technology you prefered.
I would also suggest you check out our.umbraco.org/projects/developer-tools/dewd
Hope that makes sense.
Rich
HI Robin,
You need to create the poll table yourself. It's easiest if you do this through the management studio of SQL Server. You mentioned that you want to use V6. V6 doesn't use the DbDSN anymore. You can use the connections string by name. V6 also comes with a complete new API for Database access which is very straight forward to use. Check out this blogpost how to query data in Umbraco V6
http://www.enkelmedia.se/blogg/2013/1/24/the-new-data-layer-in-umbraco-6.aspx
Cheers,
Richard
Well, I was wrong, looks like the V6 API will work with your custom tables :)
Not completely Rich, but it will work with your custom objects, it's awesome! Umbraco wrote some extensions on top of PetaPoco to support creating tables from objects as well. Pff Need some time to blog about this ;-)
I had some troubles to get DEWD working, but I finally have a successful database connection!
Also I noticed I'm using Umbraco V4 (4.11.3) in VS (nuget gives me still V4), but because I don't use the API of Umbraco I suppose it won't be a problem to upgrade to V6. (Sorry for the confusion.)
Because databases are really new for me, I made a lot mistakes between the differences of SQL CE en the normal SQL. I didn't thought the difference was that big. So SqlConnection doesn't work for SQL CE databases, and SqlCeConnection should be used. Next line of code did the trick:
SqlCeConnection myConn = new SqlCeConnection(umbraco.BusinessLogic.Application.SqlHelper.ConnectionString);
also some stuff had to be different in the queries:
string mySelectQuery = "SELECT [nodeId] FROM cmsContent";
Now I can create my own tables in the database file Umbraco.sdf and work in those tables.
Thanks everyone for your help!!!
Hi,
Did you make any progress with the Poll package for v6?
Or perhaps someone knows of a package out there?
I tried
http://our.umbraco.org/projects/website-utilities/poll
and
http://our.umbraco.org/projects/developer-tools/multi-poll-module
but they don't seem to work.
http://our.umbraco.org/projects/website-utilities/mapteams-polls
works but the admin part is not integrated in umbraco but as a seperate aspx page.
Thanks,
Damjan
is working on a reply...