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!
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.
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.....
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.
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!
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.
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
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.
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?
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. :)
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!
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.
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
@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.
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.
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
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.
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!
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 UA-cams that you could point me to that would help to understand this better, it would be greatly appreciated.
Hello Tim, thank you for this video very helpful. I'm at the starting point, this really made sense. My next step is to push the project to source control and eventually built pipelines to deploy to dev, stage and finally prod. Which of your videos would you recommend watching next to accomplish that?
I don't have videos that show all of that on UA-cam. I do have the Application Lifecycle Design course which covers all of that: www.iamtimcorey.com/p/application-lifecycle-design
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 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?
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.💻
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 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.
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 UA-cam, I am using this in my TimCo Retail Manager series to handle all of the database work.
Thank you for this video, very clear and complete. I'm asking a question, do you ever know you have time to answer. I have to do a dropbox like locally (local database, multiple users, etc.) . Regarding file storage, I was wondering what was the best? File System vs. Database (FILESTREAM). Probably too heavy for the database but security level with the file system...
If you are doing something locally that stores/tracks files, you probably have to use the file system. Trying to use SQL locally at that capacity is almost impossible. So you will need to track the files. You can monitor the folder for changes to the files using C#.
@@IAmTimCorey Wow, I didn't expect such a quick response. Thank you ! To answer, this concerns a school project in which you have to perform a dropbox like. A few files and users will therefore be sufficient for this work.
So it's local for the project but ... the idea is more general. But if the BDD is not the good idea to store files and its revisions ...
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.
SSDT is really great, but I would love a video that explains what is going on under the hood, and how you would do source control on your database if you didn't have SSDT.
Unless you want to do a lot of manual work, you don't put a database into source control without a third-party tool like SSDT. Typically, those tools are expensive.
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, 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?
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.
Great tutorial Tim!! My question is: how can I integrate the SQL Project into a C# solution and how can I create the edmx file for EF6.2 database access? Tim, is th ere any suggestions or any video related?
I haven't covered EF. As for integrating this project with C#, you just add a C# project to this solution and you are set. There isn't a true integration since they don't depend on one another but you use the database in C# that the SQL project creates.
Hi Tim. Thanx for your prompt reply!! To be more specific: In vs2017 I have created a c# solution with two projects. The 1st project is the SQL one that creates the database, indeed (and thanks a million for the tutorial) and the 2nd one is a windows forms project. On the SQL server object explorer the just created database name is correctly displayed but on the disk the filename is altered (eg: MyDB is with the name SSMSMyDb_Primary or something like that). So I have ended up with 2 different names. Now, how can I utilize the database created? I mean how to inform the 2nd project for the database of the 1st project and by what name? The DB one or the filename one? Should I add it "As a Link" to the 2nd one or should I add a reference to the 2nd project or should I copy the database to the 2nd one? This is the point where I get confused and I would be more than grateful if you could guide me please... And... thanx for your time!!
Hi Tim, have you ever used SQL Server data tools, like Integration Services or Reporting Services? Do you have plans to make videos on these in the future?
I would love to see a video using SQL Data Tools to manage a database schema with Entity Framework Core as the data access layer. Or would this just be the database first approach? I would also like to see using entity framework with a data grid view. Also is there a Patreon account to donate money?
Great content. Thanks. This is a very nice way of saving our Database schema and deploy to different instances, and versions of databases. I noticed when I created a post deployment script, the id of Demo User was 1002 instead of the next available int which was 4. I am not sure what I did wrong. I am concerned about it since the max rows value is set to 1000.
Nothing wrong. When SQL restarts, it skips a set up IDs so it doesn't step on the toes of previous transactions. As for the max value of 1000, I don't think that is what you have set. If so, you wouldn't get a value of 1002.
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, thanks for the video, this was very helpful! One quick question, is it possible to only import specific tables from an existing Database? I couldn't find anything in the import UI which allowed me to select the individual tables.
No, I don't think so. You would have to manually delete the ones you don't want. The point is to be able to recreate the database, so capturing only part of it doesn't really make sense for the design.
Thank you so much for your amazing guide. We're trying to move our database developments from SSMS to VS and we have more than 5000 stored procedures on the database. Is there any way to filter objects in VS 2022 similar to SSMS (by schema or name)? Thanks in advance!
You can use the Search field at the top of the Solution Explorer window to filter by name. Don't use wildcards, just type "spPeople" to get all of the items that contain that set of characters (so "spPeople_Get" would show up, for example). Does that help?
How good is the sql project at detecting differences between to project and the target server? I tried the database project a long time ago and was not impressed with this aspect. I am still using my own sql files and a loader script but I do really like the intellisense and some of the other things you showed. Will give it another go. Do you have any experience with larger databases and the database project type?
I was concerned with the same thing so I tried it out on a couple large databases I have access to. It worked great. The biggest tricky thing is to be careful around actions that would lose data (dropping columns, etc.) Make sure to test it first to be sure you are going to get the results you expect. Also, if you need certain data in a table after it is created, make sure to script that.
Hi Tim, there is one thing I am not sure I get correctly.. Can I for example create a codebook sql table in this LocalDB and deploy it on IIS Folder with my API project, so the API will not depend on real SQL Server that might be sometimes unavailable, but will read the codebook value from this localDB server instance, that will be deployed in the same folder as the API project? Thank you
Is there a way to generate these publish scripts from a C# project? We would like to loop through a few databases and programmatically change what database the publish process points to so we can deploy our changes to multiple databases when we update our client installations. Is that possible through this VS project type??
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!
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!
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.....
My god tutorials are hard to find for SSDT, and this one is pure gold. 🥇 Thank you for making this.
You are welcome.
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.
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.
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.
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.
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.
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!
Excellent introductory tutorial to start with. Thanks a lot.
You are welcome.
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.
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. :)
This is pure gold, Tim. I'm embarrassed to say that I'm a developer and not doing this.
Thank you!
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.
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!
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.
I am so glad I found this video! Thanks Tim.
You' are very welcome!
I had used database project, and published but seems like today I have understood it. Thanks Tim!
Excellent!
Hi Tim, I'm late to the game and this completely blew my mind! Thanks so much for this video.
You are welcome.
I love it! So easy to understand, covers everything I needed to know plus more..Thank you for sharing
You are welcome.
Taking a kickstarter so I could continue the Retail Manager Course!
The entire series is free on UA-cam. No Kickstarter needed.
Thanks a lot
@@IAmTimCorey
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.
Perfect Tim, this video is exactly what I needed.
Excellent!
Great introduction video to this. Helped get me started really quickly - thank you!
Glad it was helpful!
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!
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 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 Tim. Exactly what I've been wanting to learn.
Excellent!
@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!
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.
Amazing video! great content, great pace, and lucid explanations. Thanks much!
Glad you liked it!
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
Hey Tim, great video. Excellent guidance rithm through the tools to creating DBs. thanks.
You are welcome.
Oh Tim, you saved me actually days with this tutorial! Thank you! Again... :-)
You are welcome.
Wonderful video. Very clear and informative. Thanks!
You are welcome.
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.
Hi Tim, another good video from you! That is really cool feature to manage database scheme, will include in my current project) thanks!
Awesome!
The Comparing Tool Is Very Nice and Helpful .... Great job thank you brother.
You are welcome.
Excellent video! I have leaned a lot from it. Thanks!
You are welcome.
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!
Good intro - very concise
Thank you!
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.
Thank you for all your videos 🙂
You are welcome.
Great tutorial! The only thing we miss in VS is the gaphical way to create view in SSMS.
Thanks!
Thank you Tim, another great video!
You are welcome.
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!
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'
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 UA-cams that you could point me to that would help to understand this better, it would be greatly appreciated.
Hello Tim, thank you for this video very helpful. I'm at the starting point, this really made sense. My next step is to push the project to source control and eventually built pipelines to deploy to dev, stage and finally prod. Which of your videos would you recommend watching next to accomplish that?
I don't have videos that show all of that on UA-cam. I do have the Application Lifecycle Design course which covers all of that: www.iamtimcorey.com/p/application-lifecycle-design
AWESOME...Really helpful sir. Thank you.
You are welcome.
Thank you very much for another awesome tutorial
You are most welcome. Thanks for watching.
I thought about it yesterday. Very in time ) thank you =)
You are welcome.
It's so great video. Thanks Tim!
Glad you liked it!
This is great! I'm sure going to use it!
Thanks for building your skills with Tim.
great Tutorial Tim, thanks a lot!
You are most welcome. Thanks for watching.
wow! its really great tool. Many thanks!
You are welcome.
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.
This video is fire
@34:24 "You've got mail!"
Thanks!
@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/
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!
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.
Greate overview of this VS project type! keep up
Thanks!
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.
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.
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 UA-cam, I am using this in my TimCo Retail Manager series to handle all of the database work.
Thank you for this video, very clear and complete.
I'm asking a question, do you ever know you have time to answer. I have to do a dropbox like locally (local database, multiple users, etc.)
. Regarding file storage, I was wondering what was the best? File System vs. Database (FILESTREAM). Probably too heavy for the database but security level with the file system...
If you are doing something locally that stores/tracks files, you probably have to use the file system. Trying to use SQL locally at that capacity is almost impossible. So you will need to track the files. You can monitor the folder for changes to the files using C#.
@@IAmTimCorey Wow, I didn't expect such a quick response. Thank you !
To answer, this concerns a school project in which you have to perform a dropbox like. A few files and users will therefore be sufficient for this work.
So it's local for the project but ... the idea is more general. But if the BDD is not the good idea to store files and its revisions ...
Cool, it was very helpful. Thank you a lot !!!
You are welcome.
Thank you for this tutorial... it's excellent... Can I create a similar project using ORACLE?
Nope, sorry. This is for SQL Server only.
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
SSDT is really great, but I would love a video that explains what is going on under the hood, and how you would do source control on your database if you didn't have SSDT.
Unless you want to do a lot of manual work, you don't put a database into source control without a third-party tool like SSDT. Typically, those tools are expensive.
@@IAmTimCorey Thanks, I am definitely using SSDT in my daily work, I am just curious as to what is going on underneath :)
Thanks Tim! super useful!
Thanks for watching
Excellent, thanks Tim
You are welcome.
Thanks Tim, great job ^^
You are most welcome. Thanks for watching.
Always love your videos :)
You are welcome.
Can i create a DB project and publish to MSSQLLocalDB and host it on a machine where there is no SQL server installed?
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.
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?
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.
Thanks very much, excellent video
You are welcome.
Great tutorial Tim!! My question is: how can I integrate the SQL Project into a C# solution and how can I create the edmx file for EF6.2 database access? Tim, is th
ere any suggestions or any video related?
I haven't covered EF. As for integrating this project with C#, you just add a C# project to this solution and you are set. There isn't a true integration since they don't depend on one another but you use the database in C# that the SQL project creates.
Hi Tim. Thanx for your prompt reply!! To be more specific: In vs2017 I have created a c# solution with two projects. The 1st project is the SQL one that creates the database, indeed (and thanks a million for the tutorial) and the 2nd one is a windows forms project. On the SQL server object explorer the just created database name is correctly displayed but on the disk the filename is altered (eg: MyDB is with the name SSMSMyDb_Primary or something like that). So I have ended up with 2 different names. Now, how can I utilize the database created? I mean how to inform the 2nd project for the database of the 1st project and by what name? The DB one or the filename one? Should I add it "As a Link" to the 2nd one or should I add a reference to the 2nd project or should I copy the database to the 2nd one? This is the point where I get confused and I would be more than grateful if you could guide me please... And... thanx for your time!!
This is just superb.
Thanks!
Spectacular! 👏👏👏
Thanks!
How do I publish a project with a database inside? New to programming here.
Hi Tim, have you ever used SQL Server data tools, like Integration Services or Reporting Services? Do you have plans to make videos on these in the future?
I have and I probably will be doing videos on them. Just not in the next couple months.
I would love to see a video using SQL Data Tools to manage a database schema with Entity Framework Core as the data access layer. Or would this just be the database first approach? I would also like to see using entity framework with a data grid view. Also is there a Patreon account to donate money?
I'll add your suggestions to the list (yes, it would be database first at that point). As for Patreon, yes, I do have one: www.patreon.com/IAmTimCorey
Great content. Thanks. This is a very nice way of saving our Database schema and deploy to different instances, and versions of databases. I noticed when I created a post deployment script, the id of Demo User was 1002 instead of the next available int which was 4. I am not sure what I did wrong. I am concerned about it since the max rows value is set to 1000.
Nothing wrong. When SQL restarts, it skips a set up IDs so it doesn't step on the toes of previous transactions. As for the max value of 1000, I don't think that is what you have set. If so, you wouldn't get a value of 1002.
@@IAmTimCorey I got the same problem and it was on max 1000 xD Anyways really good video, and i am considering buying a turtorial soon :)
Thank you, sir, for this video
You are welcome.
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.
Great video. Great tool. Thanks.
You're welcome!
Really helpful.. Tim
Great!
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?
Thank you, really helpful
You're welcome!
Hi Tim, thanks for the video, this was very helpful! One quick question, is it possible to only import specific tables from an existing Database? I couldn't find anything in the import UI which allowed me to select the individual tables.
No, I don't think so. You would have to manually delete the ones you don't want. The point is to be able to recreate the database, so capturing only part of it doesn't really make sense for the design.
@@IAmTimCorey okay sure, thank you, really appreciate you responding to comments :)
Spectacular!
Thanks for watching and trusting Tim
Thank you so much for your amazing guide. We're trying to move our database developments from SSMS to VS and we have more than 5000 stored procedures on the database. Is there any way to filter objects in VS 2022 similar to SSMS (by schema or name)? Thanks in advance!
You can use the Search field at the top of the Solution Explorer window to filter by name. Don't use wildcards, just type "spPeople" to get all of the items that contain that set of characters (so "spPeople_Get" would show up, for example). Does that help?
How good is the sql project at detecting differences between to project and the target server? I tried the database project a long time ago and was not impressed with this aspect. I am still using my own sql files and a loader script but I do really like the intellisense and some of the other things you showed. Will give it another go. Do you have any experience with larger databases and the database project type?
I was concerned with the same thing so I tried it out on a couple large databases I have access to. It worked great. The biggest tricky thing is to be careful around actions that would lose data (dropping columns, etc.) Make sure to test it first to be sure you are going to get the results you expect. Also, if you need certain data in a table after it is created, make sure to script that.
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.
Hi Tim, there is one thing I am not sure I get correctly.. Can I for example create a codebook sql table in this LocalDB and deploy it on IIS Folder with my API project, so the API will not depend on real SQL Server that might be sometimes unavailable, but will read the codebook value from this localDB server instance, that will be deployed in the same folder as the API project? Thank you
Is there a way to generate these publish scripts from a C# project? We would like to loop through a few databases and programmatically change what database the publish process points to so we can deploy our changes to multiple databases when we update our client installations. Is that possible through this VS project type??
Sure. The entire deployment process can be automated, so it can also be deployed to multiple locations.