How To Work With Multiple Databases in Umbraco at a time
Hi All,
I have a requirement that i need to work with multiple databases at a time, when installing umbraco i gave one database credentials so it takes that database defaultly and created connection string also.. now my requirement is i want to get data from other database also but here in umbraco how to acces other database data and fetch that data into my content page, and where do we perform crud operations.. (here I installed umbraco7.1.3 version and I am using visualstudio2013 and mvc4).
What I would do is Create another Project of type "Class Library" and I would code my data layer in there.
Add a 2nd connection string to the umbraco web.config for your 2nd database connection with a different name and the Provider Name "MySql Data Provider", "Sql Client... " etc
Then you could use PetaPoco (the same tech Umbraco uses now days for data access. PetaPoco has T4 Generator templates that will generate POCO's for you to your database "Plain Ordinary CLR Objects". Then you can access your project through the Database class that poco generates and use Linq Queries against it.
Then just reference your Dll in Umbraco,
And make calls to it in a view in Umbraco.
But ideally, I would create a surface controller and serve data to models through my Custom Surface controller.
I used Contour Package which you sent as a reference but it is used only for inserting data into other database, but i want to retrieve the data from database and show that data in content pages.....and also i tried by declaring another connection string in web.config file but by default it is taking the first database which I installed with umbraco.. if any other possibilities are there please let me know
Thanks for your suggestion but Here my requirement is i need to get the data from multiple databases and show it in content pages with in a single project... if you have any example link please let me know
You need to get data from multiple databases, is a vague statement on which many assumptions could be made.
My assumption would be that you need to query something from Database A as it's own operation and something from Database B as it's own operation that will be displayed on the same page.
However another assumption might be that you want to use 1 query to query 2 databases, which would require enable cross database queries in MSSql Server (assuming again you are using Microsoft Sql Server and both databases are in the same MS SQL Instance.)
Regardless though, you query against a database just like you would in any other .Net project. Umbraco does not give you tools for querying other databases, only it's own database. So if you need to bring data in from other databases you will need to write your own data layer code.
You could go straight with just SqlDataReader and do it all manually, or you could leverage PetaPoco as I said before. You can have two sets of peta poco t4 templates and point 1 set to one database and the other set to another.
In fact I do that now, but with schemas, I have a database.TT file for each schema in my database. So you would want to have a database.TT file for each Database. Then in the project where PetaPoco and the datbase.tt files are, add connection string for each project.
Just tidbits to point you in a direction, if you are looking for general information on querying databases with code, just go search google for something like "C# query database SqlDataReader DataSet DataTable" etc.
Optionally there could be a package in the umbraco repository that will let you get information from database with some kind of gui (like sharepoint has) but I don't know of any.
Futhermore, you could be stating you have two umbraco sites and you need data from Site A and Site B.. In which case umbraco has webservices you can call to get data as long as there is a common user in both umbraco sites (User ID is the same on both and the login/password is the same, with the same encrypted password), or you pass credentials to webservices in site b etc.
How To Work With Multiple Databases in Umbraco at a time
Hi All,
I have a requirement that i need to work with multiple databases at a time, when installing umbraco i gave one database credentials so it takes that database defaultly and created connection string also.. now my requirement is i want to get data from other database also but here in umbraco how to acces other database data and fetch that data into my content page, and where do we perform crud operations.. (here I installed umbraco7.1.3 version and I am using visualstudio2013 and mvc4).
Thanks&Regards
Divya Y
Hi Divya
Regarding how you connect to more than 1 database it should be a matter of placing the another connection string in the web.config as well. Even though this video is outdated I think it illustrates the concept http://umbraco.com/help-and-support/video-tutorials/umbraco-pro/contour/integration-with-other-databases-and-systems.aspx
It's not really Umbraco specific but a way to set it up in the web.config and then referencing the database in your code.
Hope this helps.
/Jan
You'll likely need to add some code to do this.
What I would do is Create another Project of type "Class Library" and I would code my data layer in there.
Add a 2nd connection string to the umbraco web.config for your 2nd database connection with a different name and the Provider Name "MySql Data Provider", "Sql Client... " etc
Then you could use PetaPoco (the same tech Umbraco uses now days for data access. PetaPoco has T4 Generator templates that will generate POCO's for you to your database "Plain Ordinary CLR Objects". Then you can access your project through the Database class that poco generates and use Linq Queries against it.
Then just reference your Dll in Umbraco,
And make calls to it in a view in Umbraco.
But ideally, I would create a surface controller and serve data to models through my Custom Surface controller.
Hello Jan Skovgaard ,
Thanks&Regards Divya
Hello Ryan Mann...
Thanks&Regards Divya
You need to get data from multiple databases, is a vague statement on which many assumptions could be made.
My assumption would be that you need to query something from Database A as it's own operation and something from Database B as it's own operation that will be displayed on the same page.
However another assumption might be that you want to use 1 query to query 2 databases, which would require enable cross database queries in MSSql Server (assuming again you are using Microsoft Sql Server and both databases are in the same MS SQL Instance.)
Regardless though, you query against a database just like you would in any other .Net project. Umbraco does not give you tools for querying other databases, only it's own database. So if you need to bring data in from other databases you will need to write your own data layer code.
You could go straight with just SqlDataReader and do it all manually, or you could leverage PetaPoco as I said before. You can have two sets of peta poco t4 templates and point 1 set to one database and the other set to another.
In fact I do that now, but with schemas, I have a database.TT file for each schema in my database. So you would want to have a database.TT file for each Database. Then in the project where PetaPoco and the datbase.tt files are, add connection string for each project.
Just tidbits to point you in a direction, if you are looking for general information on querying databases with code, just go search google for something like "C# query database SqlDataReader DataSet DataTable" etc.
Optionally there could be a package in the umbraco repository that will let you get information from database with some kind of gui (like sharepoint has) but I don't know of any.
Futhermore, you could be stating you have two umbraco sites and you need data from Site A and Site B.. In which case umbraco has webservices you can call to get data as long as there is a common user in both umbraco sites (User ID is the same on both and the login/password is the same, with the same encrypted password), or you pass credentials to webservices in site b etc.
is working on a reply...