This is great! I work with a team that has struggled with managing database changes, but we've been using SQL Server and Visual Studio all along. This has been a great intro to something that I think will really improve our coordination around development, testing, and managing our changes. Thank you Tim!
All these years a have been living in the dark. So much effort put into manual versioning databases when there were elegant solutions for the job. This is really great! Thank you Tim!
Amazing! Just amazing. I never knew that Visual Studio had such powerful tools for working with data. Thank you so much for creating and sharing such helpful content.
Hi Tim, I worked through that video last night and thought it was excellent, I live in SSMS and am now have a project which needs to be deployed to multiple DBs, this is just what I need. Thank you.
Once again Tim, great video. No matter how complex a topic is, you always make it simple. Thank you for your great work and contribution to the software development community. If I had known this for years, my database development experience would have been much easier. Nevertheless, this tutorial is an additional asset to my toolbox. Thanks a lot!
This type of demo on database is a clear explanation. The good thing for a beginner practice. Managing the project is to create for testing development and separate real world project. Thanks for sharing this topic about database. I learn more.....
I'm so glad I began developing when you had to do this as the standard. I've come across senior Devs who don't know T-SQL other than a select * from statement. EF has ruined a lot of developers.
Superb, Tim. Just to let you know that team management could be a hot topic for your channel. Because you know how to teach complex things in a simple manner, a lot of developers like me could benefit from your team experience. What I means is: there are thousands of developers who are good at what they do - develop code - but with no experience with team tools/team management. And those skill are a must when you are " entrepeneuring" ou "solopreneuring", which is pretty common this days and should be more and more regular in a foreseable future. Thank you. I learnt a lot with this video. Will see it again in a few days.
Another great video Tim. I just wanted to share that I wrote a program for work using Entity Framework. It was great. Entity Framework set up my Microsoft SQL database (the more coding I do, the more I appreciate Microsoft, they really are doing great things) and as long as I played according to EF rules I was was golden. I decided to rewrite my program for MySQL. Exact same program, only this time no hint of DB Context in my code (yes I know, it is still there behind the scenes :) ). The Visual Studio file without EF (identical programs remember) was six times smaller! Both programs do exactly the same thing!
@Mark Botirius, Glad you got it all working and as you say, the MySQL version was 6 times smaller, in a previous role I found this too, and when our DBA looked at the SQL code that EF generated he was horrified how poor it actually was. I would recommend that you look into Dapper - and check out some of the videos on Tims' site on using it. Yes you can then use it for both MSSQL and MySQL - not two programs either!... Give yourself some homework to make a 3rd application that will do both databases via dapper. :)
Quick tip, for those who may be attempting to create a table and the design is not showing, make sure that you update the VS to the latest version or packages. Worked for me
@IAmTimCorey absolute awesome. Didn't know it's that easy.... Iam totally blown and smiling as fuck because I know what Iam now going to implement at work. Thank you so much for all of your videos.
Been a few weeks since I watched one of your videos, and as always it's very clear. I currently use SSMS and VS2017 for development as I never really dived into the sql side of things via VS. I'll have to look into this more now. Would love a later video showing how to move all this into a build environment and how to set it up for team use. Great work Tim!
Tim, thank you so much for such a descriptive video covering mostly all the uses of SQL Server Database Project. I have been struggling to understand the purpose of it but this video gave me complete clarity. In the end you talked about putting it in Source control and using it to sync DB in different environments. I checked in you channel but could not find the video. I hope you did make that video, can you point me to it please?
Thanks for this phenomenally instructive video! There are so many video tutorials out there with either a top down or a bottom up approach where it's hard for learners to connect the dots. As far as the quirk in Visual Studio is concerned, I think I can confirm that it possibly is a quirk. In the version 16.9.2 I'm currently using I can authenticate MSSQLLocalDB with Windows.
At 3:30 you say we could add a Winform or Console app; I assume you mean a project added along with the SQL Server Database Project. My impression of this project type is that it provides methods to develop SQL Server scripts for a database. This makes the job much easier than in SSMS. When Publish is done, the database is created (or updated) to a specific server. The SSOE provides two test servers in which queries and stored procedures can be tested with temporary data. Eventually Publish is done to the client's real server. SSDP helps with the SQL development and maintenance, separate from the C# application staff. Question: How could a Winform or Console project help in doing SQL development? I imagine in a big company, the db people are in a separate place using SSMS. Will they start using Visual Studio to take advantage of these improvements?
Hi Tim, as always ANOTHER AMAZING TUTORIAL! I absolutely love your videos! I wanted to pick your brains on something. The Schema Compare tool seems to have issues for some people. The compare tool just freezes and not lets you do anything, i can see a few people have reached out to VS for this..I seems to work just fine for you tho, any advise? 😆
Not sure. I've not had a problem, but maybe my databases don't use things that yours do. If you can narrow it down, submit a bug report to Microsoft to see if they can fix it.
This is gold, thank you. Do you have a vid explaining how post deploy and pre deploy scripts work in database projects? Edit: It's already explained in the video towards the end....Thanks again
Fantastic video, Tim. I just heard about this tool and I thought, "I would bet Tim Corey has something on this." You are awesome and you have helped me tremendously. 😁 Thank you.💻
@@IAmTimCorey Hey, thanks for the reply, sorry I think I must have missed something in the video, what is the tool that is being used for this? Is this an extra add-on?
Tim, thanks for all your videos they're great. Im using them to get up to date with all the latest additions in the past 8 years Ive been away from c#. I have one question though, it seems like in all your videos you name the table names singlular when Ive always made them plural and in other languages it's either recommended or forced to make them plural so certain apis work seemlessly with them. Is there a specific reason you name them singular? I always looked at it like a table is called "Users" because it holds many users, but the model class is singular "User.cs" because it only represents one instance or row from that table, just wondering if there was any particular reason you do that, thanks again
Good question. That naming convention is much more ambiguous. I lean towards singular because when you query a table, you say things like User.FirstName to represent a user's first name rather than Users.FirstName. However, I've worked on projects that do it the other way and that's fine.
@IAmTimCorey hey tim, thanks for putting this video together, I’ve really learned a lot from it. Do you mind doing a more in-depth tutorial to share your best practices, pipelines (in azure devops) and workflows to manage, collaborate (e.g. on github), potentially handle huge schema changes (e.g. translating data from old tables to new tables, and mapping the right web app ver to the right db ver) and push changes (e.g. schemas and stored procedures changes) to the development, staging and production environment?
Hi Tim! Thank you for your great crystal clear videosss! I'm not sure which one I should watch next on the same vs/sql/local DB topics and in which order... "Stored procedures" "Database connections strings" "Connect C# to SQL" ? Thank you for your time
Thanks for this, Tim. I got a question. At 10:26 you're linking an address to a person. This would mean that one person could be linked to several different addresses, right? Wouldn't it make more sense to do it the other way around by having an AddressId in the Person table, allowing several people to be listed under one address? Maybe I'm confused and got this flipped, or maybe it just depends on what you want the database to do.
It all depends on what you want the database to do. In my solution, one person can have multiple addresses associated with them (second home, work address, etc.) In your solution, one address can have multiple people associated with it (a family). Another solution is to have a table that does the linking so that one person can be associated with multiple addresses and one address can be associated with multiple people.
There are many professional people who specialize in the Database business, never writing application code. Some of their work is done manually (analyzing business data, stages of normalization, designing tables and other db objects, etc.). Each db system has software to help manage it (eg. SSMS for Microsoft SQL Server). There are many add-ins and plug-ins to "improve" SSMS in various ways. This new VS "SQL Server Database Project" aims at a small part of what's done in SSMS. For those in small companies (or for personal projects) who already utilize Visual Studio, this project type provides great tools to make it easier to generate and maintain a database. Tim says some of these are "golden". Were these improvements also made directly in SSMS? Are there clues about the future?
SSDT isn't necessarily a new tool. It has been around for a bit. The difference between SSMS and SSDT is that SSMS is about management, where SSDT is about development. SSDT provides the tools to build a database (refactoring, source control, etc.) that are really beneficial to the development process. SSDT can be really useful for any size application and company, even if you are dedicated to building databases only. It does not replace SSMS, it just approaches a different part of the problem.
I started looking for alternatives to SSMS yesterday. Considered some open source stuff and whatnot. It occurred to me to see what was up with SSDT because it's been a long time since I last looked at it (it was horrible at the time). Wow. This tutorial just convinced me to dig into these tools again. Something I never thought I'd do. Thanks!
At 4:55 you mention Server Explorer, that "it's not as good as" SQL Server Object Explorer and you recommend opening the newer tool. To do this kind of project, we don't have a choice. SE provides access to my server, simulating SSMS. This new SSOE provides a development server (actually two for some reason). Any data I enter in tables here are not reflected in My server, as is true with SE.
@@IAmTimCorey If my server is already available in SE, why would I add it to SSOE? SSOE already has three containers of database objects. It has two instances of (localdb) and the SQLProject. Do you have a map to show the purpose of these containers? I've tried to search for info but haven't found it yet. Thanks.
After another review, I see this: The “server” here called SQLProject has scripts; it represents the database development staff. The server called MSSQLLocalDB simulates a production site that stores a client’s data using a database generated from Published scripts. I still don’t know why Ms provides the server called ProjectsV13. Thanks for your coverage of this great improvement for the database people.
Tim, Thanks for the great video. It has helped me understand localdb much better. I still have 2 questions. 1. If I publish my project and copy the executable to a new computer, will it create the databases automatically the first time it runs? 2. Could I allow the user to point to an SQL Server on the cloud and (with the correct permissions) create the databases automatically there? If you have any courses on your web site or TH-cams that you could point me to that would help to understand this better, it would be greatly appreciated.
Hi Mr Corey. This tutorial was amazing and helped me alot i had a serious problem in working with team on DB and now i learned how to do it. But there I came up with a question: How to make installation from this db and install (deploy) DB on customer pc? Thanks alot for your great courses.
Deploying on a customer DB is the trick, since they will need SQL Server installed. You would need to set up a publish script to push to each SQL Server unless you could point each customer at a central SQL Server.
Hi Tim, awesome video. Do you think Dapper + SSDT would be a viable choice for enterprise applications? To be honest I'm not a fan of Entity Framework since merging changes can be painful + performance is of course impacted by all that ORM mechanism.
Yes, it is definitely a viable choice. I agree on your EF assessment, although if you do decide to go EF, go EF Core if possible. Lots of speed improvements there.
I'm glad to see these tools to make SQL projects easier. I assume the tools wanted an SQL server with known specifications. Is that why my existing servers are not utilized? But what's the purpose of two servers, first is (localdb)]MSSQLLocalDB, second is (localdb)\ProjectsV13? Only the first one is used here.
LocalDB allows for an easy development environment without the expense of installing full SQL. As for why there are two "servers", these are just two locations to install the database files. You don't have to have or use both.
So this is a good question. There are a number of facets to this argument. EF is not objectively bad (or fully good). It depends on your situation. EF is slow (EF Core fixes the speed issue...mostly) but that isn't always an issue. EF is harder to work with in a team situation (merging changes is a mess) and EF generates a lot of code that can be tricky to debug. On the other side, it makes development easier (as long as it works) and it writes the queries for you (which means it writes queries better than most developers could do but it isn't as precise as a DBA would be). I do have an old post on it that I need to update with EF Core info. I use Dapper almost exclusively because it is fast, simple, and powerful.
I have two courses that do just that. The Database DevOps from Start to Finish course ( www.iamtimcorey.com/p/database-devops-from-start-to-finish/ ) shows you how to build a database with this, deploy it, bring in an existing database, and even work with external changes. The Application Lifecycle Design course ( www.iamtimcorey.com/p/application-lifecycle-design/ ) covers using Azure DevOps to deploy this automatically to an Azure SQL database (although the principle applies to even local databases). It also covers deploying web apps, creating different versions (development, staging, and production), and how to put all of this under source control. On TH-cam, I am using this in my TimCo Retail Manager series to handle all of the database work.
Hi Tim, first of all, thank you for all the amazing work that you are doing, you truly are helping us a lot. I have a small issue when following this tutorial, whenever I click publish, I don't get the publish window you are getting at 13:08 instead visual studio just executes the code and even fails. I'm just wording why am I not getting it (the publish window), is there anything that I should download first?
I encountered this scenario. Right clicking the project and hit Publish, the publish window didnt show up. The problem I had was, there was an error in my sql script. Looking at the output window, after hitting publish, it will build your project and then this publish database window will show up when building is successful. I fixed the error i got, and then everyting works fine.
Hi, @@linojrasmolo7161 thanks for your reply. Actually, for me, I am using mac book running Parallels for mac. That was the issue. I ran the same code on a windows laptop and I didn't face this issue.
The M in SSMS stands for Management. That is what SSMS does best. The SQL Data Tools do development best (the D used to stand for Development). Both tools are great and can do a ton but those are the general lanes they do best in.
Hi Tim I found this very helpful. There is another aspect to SSDT which I think needs its own treatment and that is Unit Testing once a database project has been built. Would you be able to do another video to cover this topic? I have scoured youtube recently and there isn't much content on this. The area I am most interested in is how to create a custom unit test in ssdt.
Hi Tim, thanks for sharing this great contents. This is great when you working with single database. What about multiple databases and they all have cross databases query in views and procs? Can you please make another video about how to add database references DACPAC files and how to deal with 3 parts name query for cross databases and cross servers environment? I'm a bit unclear how to make a good use of it. Much appreciated.
Hi Tim, thanks for a great video. I have been working with SQL for a number of years, but I realize I have been living in the dark, without this amazing tool! At 30:00 you mention that the generated script can be executed without publishing using SQL Data Tools. How would you do this, would you manually replace all the variables e.g. ":setvar DatabaseName "DemoDB"" with SQL-declarations i.e. "DECLARE DatabaseName AS VARCHAR" and then run the script in SSMS?
One of the publish options is to just generate the SQL script. It sets everything up to update your database, but it does not run it. That's what I meant by not publishing. You use the publish dialog but don't actually update your SQL. The generated script handles all of the variable names, etc.
@@IAmTimCorey Thanks Tim. I understand how to generate the script and run it in VS. I was just wondering if it it possible to run the script in SSMS, and it turns out it is. In SSMS you go to the Query tab and then press SQLCMD Mode. After that the script will run.
A small coffe (I wish my currency was not that bad =P ) for the simple and easy to understand video
Thank you!
@aindaecedo realmente, dá vontade de chorar rsrsrs. Vc já trabalha na área?
@@Antonio-lt1sp oi Antonio, trabalho sim, mas não conhecia criar via VS studio, sempre usei o MSSQL para criar tudo
This is great! I work with a team that has struggled with managing database changes, but we've been using SQL Server and Visual Studio all along. This has been a great intro to something that I think will really improve our coordination around development, testing, and managing our changes.
Thank you Tim!
I am glad it was helpful.
All these years a have been living in the dark. So much effort put into manual versioning databases when there were elegant solutions for the job. This is really great! Thank you Tim!
Amazing! Just amazing. I never knew that Visual Studio had such powerful tools for working with data. Thank you so much for creating and sharing such helpful content.
You are welcome.
Hi Tim, I worked through that video last night and thought it was excellent, I live in SSMS and am now have a project which needs to be deployed to multiple DBs, this is just what I need. Thank you.
Fantastic!
Once again Tim, great video. No matter how complex a topic is, you always make it simple. Thank you for your great work and contribution to the software development community. If I had known this for years, my database development experience would have been much easier. Nevertheless, this tutorial is an additional asset to my toolbox.
Thanks a lot!
Fantastic endorsement, thanks for your faithful support.
My god tutorials are hard to find for SSDT, and this one is pure gold. 🥇 Thank you for making this.
You are welcome.
0:00 - Intro
1:12 - SQL Server Database Project in Visual Studio
4:35 - Designing a new SQL database: adding and editing tables
11:22 - Adding a foreign key
12:46 - Publishing the database
17:10 - Putting data in database
18:52 - Adding a View
23:20 - Adding a Stored Procedure
25:00 - Publishing database with the added changes
27:06 - Changing column names
31:41 - Schema Compare tool
39:37 - Database Queries
41:26 - New Database data: Data Comparison tool
43:13 - New Database data: Scripts
47:41 - Importing existing database to SQL project
49:49 - Summary and concluding remarks
Thank you! Rock Star!
This type of demo on database is a clear explanation. The good thing for a beginner practice. Managing the project is to create for testing development and separate real world project. Thanks for sharing this topic about database. I learn more.....
You are welcome.
Excellent introductory tutorial to start with. Thanks a lot.
You are welcome.
Wow I didn'T realize all of these features for SQL in Visual Studio, thanks for this. Truly a masterpiece of a software by microsoft, I gotta say.
Glad you enjoyed it!
This is pure gold, Tim. I'm embarrassed to say that I'm a developer and not doing this.
Thank you!
I'm so glad I began developing when you had to do this as the standard. I've come across senior Devs who don't know T-SQL other than a select * from statement. EF has ruined a lot of developers.
It does make for better development if you know T-SQL.
Superb, Tim. Just to let you know that team management could be a hot topic for your channel. Because you know how to teach complex things in a simple manner, a lot of developers like me could benefit from your team experience.
What I means is: there are thousands of developers who are good at what they do - develop code - but with no experience with team tools/team management. And those skill are a must when you are " entrepeneuring" ou "solopreneuring", which is pretty common this days and should be more and more regular in a foreseable future.
Thank you. I learnt a lot with this video. Will see it again in a few days.
Thanks for the suggestion. We are looking into how I could do something like this.
I am so glad I found this video! Thanks Tim.
You' are very welcome!
Another great video Tim. I just wanted to share that I wrote a program for work using Entity Framework. It was great. Entity Framework set up my Microsoft SQL database (the more coding I do, the more I appreciate Microsoft, they really are doing great things) and as long as I played according to EF rules I was was golden. I decided to rewrite my program for MySQL. Exact same program, only this time no hint of DB Context in my code (yes I know, it is still there behind the scenes :) ). The Visual Studio file without EF (identical programs remember) was six times smaller! Both programs do exactly the same thing!
Nice!
@Mark Botirius, Glad you got it all working and as you say, the MySQL version was 6 times smaller, in a previous role I found this too, and when our DBA looked at the SQL code that EF generated he was horrified how poor it actually was. I would recommend that you look into Dapper - and check out some of the videos on Tims' site on using it. Yes you can then use it for both MSSQL and MySQL - not two programs either!... Give yourself some homework to make a 3rd application that will do both databases via dapper. :)
Perfect Tim, this video is exactly what I needed.
Excellent!
I had used database project, and published but seems like today I have understood it. Thanks Tim!
Excellent!
I love it! So easy to understand, covers everything I needed to know plus more..Thank you for sharing
You are welcome.
Thanks so much, leveling up on VS2019 SQL tools. Especially useful was the schema comparison between the deployed DB and the project code.
That is a pretty great feature.
Quick tip, for those who may be attempting to create a table and the design is not showing, make sure that you update the VS to the latest version or packages. Worked for me
Thanks for sharing.
The priest as hit again ! Great tutorial, perfect for managing test and production database in the same time and replicate them easily !
Thanks for growing your skills with this video.
@IAmTimCorey absolute awesome. Didn't know it's that easy.... Iam totally blown and smiling as fuck because I know what Iam now going to implement at work.
Thank you so much for all of your videos.
Awesome!
Been a few weeks since I watched one of your videos, and as always it's very clear. I currently use SSMS and VS2017 for development as I never really dived into the sql side of things via VS. I'll have to look into this more now.
Would love a later video showing how to move all this into a build environment and how to set it up for team use. Great work Tim!
Sounds exactly like the course I'm working on this weekend.
Tim, thank you so much for such a descriptive video covering mostly all the uses of SQL Server Database Project. I have been struggling to understand the purpose of it but this video gave me complete clarity.
In the end you talked about putting it in Source control and using it to sync DB in different environments. I checked in you channel but could not find the video. I hope you did make that video, can you point me to it please?
We do that in the TimCo Retail Manager series.
Thank you for all your videos 🙂
You are welcome.
Thanks for this phenomenally instructive video! There are so many video tutorials out there with either a top down or a bottom up approach where it's hard for learners to connect the dots.
As far as the quirk in Visual Studio is concerned, I think I can confirm that it possibly is a quirk. In the version 16.9.2 I'm currently using I can authenticate MSSQLLocalDB with Windows.
Thanks for watching and sharing!
Thats amazing having source control of sql server
Sharing tools and tips helps build the whole community.
@@IAmTimCorey You are amazing Tim, thank you for sharing your knowledge!
Amazing video! great content, great pace, and lucid explanations. Thanks much!
Glad you liked it!
Hi Tim, I'm late to the game and this completely blew my mind! Thanks so much for this video.
You are welcome.
Great introduction video to this. Helped get me started really quickly - thank you!
Glad it was helpful!
Oh Tim, you saved me actually days with this tutorial! Thank you! Again... :-)
You are welcome.
Thank you Tim. Exactly what I've been wanting to learn.
Excellent!
Wonderful video. Very clear and informative. Thanks!
You are welcome.
I've been wondering what was that project type is being used for and now I know! Great video as always. Thanks!
You are welcome.
wow! its really great tool. Many thanks!
You are welcome.
Hey Tim, great video. Excellent guidance rithm through the tools to creating DBs. thanks.
You are welcome.
Good intro - very concise
Thank you!
Thank you Tim, another great video!
You are welcome.
AWESOME...Really helpful sir. Thank you.
You are welcome.
This video is fire
@34:24 "You've got mail!"
Thanks!
The Comparing Tool Is Very Nice and Helpful .... Great job thank you brother.
You are welcome.
Hi Tim, another good video from you! That is really cool feature to manage database scheme, will include in my current project) thanks!
Awesome!
Thank you very much for another awesome tutorial
You are most welcome. Thanks for watching.
Taking a kickstarter so I could continue the Retail Manager Course!
The entire series is free on TH-cam. No Kickstarter needed.
Thanks a lot
@@IAmTimCorey
Excellent video! I have leaned a lot from it. Thanks!
You are welcome.
great Tutorial Tim, thanks a lot!
You are most welcome. Thanks for watching.
It's so great video. Thanks Tim!
Glad you liked it!
Spectacular! 👏👏👏
Thanks!
This is great! I'm sure going to use it!
Thanks for building your skills with Tim.
Thanks Tim! super useful!
Thanks for watching
I thought about it yesterday. Very in time ) thank you =)
You are welcome.
Excellent, thanks Tim
You are welcome.
Always love your videos :)
You are welcome.
Thanks Tim, great job ^^
You are most welcome. Thanks for watching.
At 3:30 you say we could add a Winform or Console app; I assume you mean a project added along with the SQL Server Database Project.
My impression of this project type is that it provides methods to develop SQL Server scripts for a database.
This makes the job much easier than in SSMS.
When Publish is done, the database is created (or updated) to a specific server.
The SSOE provides two test servers in which queries and stored procedures can be tested with temporary data.
Eventually Publish is done to the client's real server.
SSDP helps with the SQL development and maintenance, separate from the C# application staff.
Question: How could a Winform or Console project help in doing SQL development?
I imagine in a big company, the db people are in a separate place using SSMS.
Will they start using Visual Studio to take advantage of these improvements?
Thanks very much, excellent video
You are welcome.
Hi Tim, as always ANOTHER AMAZING TUTORIAL! I absolutely love your videos!
I wanted to pick your brains on something. The Schema Compare tool seems to have issues for some people. The compare tool just freezes and not lets you do anything, i can see a few people have reached out to VS for this..I seems to work just fine for you tho, any advise? 😆
Not sure. I've not had a problem, but maybe my databases don't use things that yours do. If you can narrow it down, submit a bug report to Microsoft to see if they can fix it.
Great video. Great tool. Thanks.
You're welcome!
Cool, it was very helpful. Thank you a lot !!!
You are welcome.
At minute 46:10 you capitalize all SQL keyword in one go: how do you do this? Did you use a special keyboard shortcut?
It is the auto-formatting in Visual Studio. It is actually not something I want, because I prefer the all-lowercase versions.
Thank you, sir, for this video
You are welcome.
This is gold, thank you. Do you have a vid explaining how post deploy and pre deploy scripts work in database projects? Edit: It's already explained in the video towards the end....Thanks again
I'm glad you found it.
Thank you, really helpful
You're welcome!
This is just superb.
Thanks!
Fantastic video, Tim. I just heard about this tool and I thought, "I would bet Tim Corey has something on this." You are awesome and you have helped me tremendously. 😁 Thank you.💻
Glad it was helpful!
Thanks for another great video. Are these amazing tools included in any of your paid courses (e.g. Tournament Tracker, TimCo)?
Yes they are! 'C# Application from Start to Finish Complete Bundle'
Greate overview of this VS project type! keep up
Thanks!
Great tutorial! The only thing we miss in VS is the gaphical way to create view in SSMS.
Thanks!
Really helpful.. Tim
Great!
How are you making the SQL keywords uppercase?
That's the tool applying automatic formatting.
@@IAmTimCorey Hey, thanks for the reply, sorry I think I must have missed something in the video, what is the tool that is being used for this? Is this an extra add-on?
At 4:34 you mention importing a database into this project. When do you do that?
About 47:41 Thanks
50:30 Why there is no reference to episode for source control? Was it recoreded?
I share a bit here: th-cam.com/video/H-R2bCXfz8I/w-d-xo.html plus I do this actively in the TimCo Retail Manager series when we deploy the database.
@@IAmTimCorey I already put the comment there. Thanks for quick reply :)
Tim, thanks for all your videos they're great. Im using them to get up to date with all the latest additions in the past 8 years Ive been away from c#. I have one question though, it seems like in all your videos you name the table names singlular when Ive always made them plural and in other languages it's either recommended or forced to make them plural so certain apis work seemlessly with them. Is there a specific reason you name them singular? I always looked at it like a table is called "Users" because it holds many users, but the model class is singular "User.cs" because it only represents one instance or row from that table, just wondering if there was any particular reason you do that, thanks again
Good question. That naming convention is much more ambiguous. I lean towards singular because when you query a table, you say things like User.FirstName to represent a user's first name rather than Users.FirstName. However, I've worked on projects that do it the other way and that's fine.
@IAmTimCorey hey tim, thanks for putting this video together, I’ve really learned a lot from it.
Do you mind doing a more in-depth tutorial to share your best practices, pipelines (in azure devops) and workflows to manage, collaborate (e.g. on github), potentially handle huge schema changes (e.g. translating data from old tables to new tables, and mapping the right web app ver to the right db ver) and push changes (e.g. schemas and stored procedures changes) to the development, staging and production environment?
Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/
Great - Very Helpful
Glad to hear that!
@@IAmTimCorey Hi Tim, could you provide a list showing the order/sequence I should attempt each of your TH-cam videos. Many thanks. Barry
Hi Tim! Thank you for your great crystal clear videosss! I'm not sure which one I should watch next on the same vs/sql/local DB topics and in which order... "Stored procedures" "Database connections strings" "Connect C# to SQL" ? Thank you for your time
Probably Stored Procedures, then Connect C# to SQL, and then Database Connection Strings but there is some overlap there.
@@IAmTimCorey Great thank you!
Is this relevant for data analysts , or data engineers ? Thank you
It can be but mostly it is for database developers.
IAmTimCorey gotcha thanks
Thanks for this, Tim. I got a question. At 10:26 you're linking an address to a person. This would mean that one person could be linked to several different addresses, right?
Wouldn't it make more sense to do it the other way around by having an AddressId in the Person table, allowing several people to be listed under one address?
Maybe I'm confused and got this flipped, or maybe it just depends on what you want the database to do.
It all depends on what you want the database to do. In my solution, one person can have multiple addresses associated with them (second home, work address, etc.) In your solution, one address can have multiple people associated with it (a family). Another solution is to have a table that does the linking so that one person can be associated with multiple addresses and one address can be associated with multiple people.
@@IAmTimCorey this makes total sense.
Spectacular!
Thanks for watching and trusting Tim
in 17:14 i see You have option to Create CRUD procedures. Its extensions ?
That might be an Enterprise feature. Not sure. I don't typically install extensions and I don't have it in my Community Edition version.
There are many professional people who specialize in the Database business, never writing application code.
Some of their work is done manually (analyzing business data, stages of normalization, designing tables and other db objects, etc.).
Each db system has software to help manage it (eg. SSMS for Microsoft SQL Server).
There are many add-ins and plug-ins to "improve" SSMS in various ways.
This new VS "SQL Server Database Project" aims at a small part of what's done in SSMS.
For those in small companies (or for personal projects) who already utilize Visual Studio, this project type provides great tools to make it easier to generate and maintain a database.
Tim says some of these are "golden".
Were these improvements also made directly in SSMS? Are there clues about the future?
SSDT isn't necessarily a new tool. It has been around for a bit. The difference between SSMS and SSDT is that SSMS is about management, where SSDT is about development. SSDT provides the tools to build a database (refactoring, source control, etc.) that are really beneficial to the development process. SSDT can be really useful for any size application and company, even if you are dedicated to building databases only. It does not replace SSMS, it just approaches a different part of the problem.
@@IAmTimCorey Thanks Tim. I didn't know this video was about SSDT.
Could please make a video if possible explaining WCF(Windows Communication Foundation).
It is on my suggestion list. It might take a bit though.
I started looking for alternatives to SSMS yesterday. Considered some open source stuff and whatnot. It occurred to me to see what was up with SSDT because it's been a long time since I last looked at it (it was horrible at the time). Wow. This tutorial just convinced me to dig into these tools again. Something I never thought I'd do. Thanks!
Excellent!
At 4:55 you mention Server Explorer, that "it's not as good as" SQL Server Object Explorer and you recommend opening the newer tool. To do this kind of project, we don't have a choice. SE provides access to my server, simulating SSMS. This new SSOE provides a development server (actually two for some reason). Any data I enter in tables here are not reflected in My server, as is true with SE.
You can open a connection to your server using SSOE as well.
@@IAmTimCorey If my server is already available in SE, why would I add it to SSOE? SSOE already has three containers of database objects. It has two instances of (localdb) and the SQLProject. Do you have a map to show the purpose of these containers? I've tried to search for info but haven't found it yet. Thanks.
After another review, I see this: The “server” here called SQLProject has scripts; it represents the database development staff. The server called MSSQLLocalDB simulates a production site that stores a client’s data using a database generated from Published scripts. I still don’t know why Ms provides the server called ProjectsV13. Thanks for your coverage of this great improvement for the database people.
Tim, Thanks for the great video. It has helped me understand localdb much better. I still have 2 questions. 1. If I publish my project and copy the executable to a new computer, will it create the databases automatically the first time it runs? 2. Could I allow the user to point to an SQL Server on the cloud and (with the correct permissions) create the databases automatically there? If you have any courses on your web site or TH-cams that you could point me to that would help to understand this better, it would be greatly appreciated.
Hi Mr Corey.
This tutorial was amazing and helped me alot i had a serious problem in working with team on DB and now i learned how to do it.
But there I came up with a question: How to make installation from this db and install (deploy) DB on customer pc?
Thanks alot for your great courses.
Deploying on a customer DB is the trick, since they will need SQL Server installed. You would need to set up a publish script to push to each SQL Server unless you could point each customer at a central SQL Server.
Hi Tim, awesome video. Do you think Dapper + SSDT would be a viable choice for enterprise applications? To be honest I'm not a fan of Entity Framework since merging changes can be painful + performance is of course impacted by all that ORM mechanism.
Yes, it is definitely a viable choice. I agree on your EF assessment, although if you do decide to go EF, go EF Core if possible. Lots of speed improvements there.
I'm glad to see these tools to make SQL projects easier. I assume the tools wanted an SQL server with known specifications. Is that why my existing servers are not utilized? But what's the purpose of two servers, first is (localdb)]MSSQLLocalDB, second is (localdb)\ProjectsV13? Only the first one is used here.
LocalDB allows for an easy development environment without the expense of installing full SQL. As for why there are two "servers", these are just two locations to install the database files. You don't have to have or use both.
Hi Tim, as always great content. Thank you so much! I was wondering if this is something you would use instead of something like flyway scripts?
Yes, I use these all of the time. I haven't gotten into using Flyway.
Can i create a DB project and publish to MSSQLLocalDB and host it on a machine where there is no SQL server installed?
One Question. EF is bad? i didnt find any videos about it in your channel Tim. What is your view about it?
So this is a good question. There are a number of facets to this argument. EF is not objectively bad (or fully good). It depends on your situation. EF is slow (EF Core fixes the speed issue...mostly) but that isn't always an issue. EF is harder to work with in a team situation (merging changes is a mess) and EF generates a lot of code that can be tricky to debug. On the other side, it makes development easier (as long as it works) and it writes the queries for you (which means it writes queries better than most developers could do but it isn't as precise as a DBA would be). I do have an old post on it that I need to update with EF Core info. I use Dapper almost exclusively because it is fast, simple, and powerful.
Nice work! Thank you. Do you have any follow up videos on this? You had mentioned showing us how to use with TFS and within teams.
I have two courses that do just that. The Database DevOps from Start to Finish course ( www.iamtimcorey.com/p/database-devops-from-start-to-finish/ ) shows you how to build a database with this, deploy it, bring in an existing database, and even work with external changes. The Application Lifecycle Design course ( www.iamtimcorey.com/p/application-lifecycle-design/ ) covers using Azure DevOps to deploy this automatically to an Azure SQL database (although the principle applies to even local databases). It also covers deploying web apps, creating different versions (development, staging, and production), and how to put all of this under source control. On TH-cam, I am using this in my TimCo Retail Manager series to handle all of the database work.
Hi Tim, first of all, thank you for all the amazing work that you are doing, you truly are helping us a lot.
I have a small issue when following this tutorial, whenever I click publish, I don't get the publish window you are getting at 13:08 instead visual studio just executes the code and even fails. I'm just wording why am I not getting it (the publish window), is there anything that I should download first?
I encountered this scenario. Right clicking the project and hit Publish, the publish window didnt show up. The problem I had was, there was an error in my sql script. Looking at the output window, after hitting publish, it will build your project and then this publish database window will show up when building is successful. I fixed the error i got, and then everyting works fine.
Hi, @@linojrasmolo7161 thanks for your reply. Actually, for me, I am using mac book running Parallels for mac. That was the issue. I ran the same code on a windows laptop and I didn't face this issue.
Great tutorial! Is there anything that SSMS can do that a VS SQL Database project can't?
The M in SSMS stands for Management. That is what SSMS does best. The SQL Data Tools do development best (the D used to stand for Development). Both tools are great and can do a ton but those are the general lanes they do best in.
Hi Tim I found this very helpful. There is another aspect to SSDT which I think needs its own treatment and that is Unit Testing once a database project has been built. Would you be able to do another video to cover this topic? I have scoured youtube recently and there isn't much content on this. The area I am most interested in is how to create a custom unit test in ssdt.
Thank you. I have added this to my list of possible future topics.
Thanks for such a good video. Do you have a video on source control (git) for sql server?
You would use the techniques in this video. You would apply source control to your solution on down and it would put your SQL code in git as well.
Hi Tim, thanks for sharing this great contents. This is great when you working with single database. What about multiple databases and they all have cross databases query in views and procs? Can you please make another video about how to add database references DACPAC files and how to deal with 3 parts name query for cross databases and cross servers environment? I'm a bit unclear how to make a good use of it. Much appreciated.
That can be done, although I would encourage you to avoid cross-database queries wherever possible.
@@IAmTimCorey Have you done any video for Multiple Databases or Linked Servers
Cool stuff!
Thanks!
Hi Tim, thanks for a great video. I have been working with SQL for a number of years, but I realize I have been living in the dark, without this amazing tool! At 30:00 you mention that the generated script can be executed without publishing using SQL Data Tools. How would you do this, would you manually replace all the variables e.g. ":setvar DatabaseName "DemoDB"" with SQL-declarations i.e. "DECLARE DatabaseName AS VARCHAR" and then run the script in SSMS?
One of the publish options is to just generate the SQL script. It sets everything up to update your database, but it does not run it. That's what I meant by not publishing. You use the publish dialog but don't actually update your SQL. The generated script handles all of the variable names, etc.
@@IAmTimCorey Thanks Tim. I understand how to generate the script and run it in VS. I was just wondering if it it possible to run the script in SSMS, and it turns out it is. In SSMS you go to the Query tab and then press SQLCMD Mode. After that the script will run.