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.
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.
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.
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!
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
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?
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. :)
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.
@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!
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
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.
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!
@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
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.
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, 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.
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.
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 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 CZcamss that you could point me to that would help to understand this better, it would be greatly appreciated.
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.
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 CZcams. I do have the Application Lifecycle Design course which covers all of that: www.iamtimcorey.com/p/application-lifecycle-design
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.
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 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 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?
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 CZcams, I am using this in my TimCo Retail Manager series to handle all of the database work.
In this kind of project, why wouldn't you assign it the name of the database? That's the purpose of the project, to generate a specific database. The objects below it (Tables, Views etc.) are database objects. Wouldn't the project be kept by the developer team to support that database? Example: Project name / database for client Acme could be AcmePartsDev. An imported database (revison of same db) into a new project could be named AcmePartsDev2. These would publish to client's server as database AcmeParts. If another database is needed, start a new project with that new db name (AcmeMachinesDev).
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??
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?
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?
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?
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 ...
Tim, can you do a video for begineers to start from a Empty-Application in Visual Studion 2017, and learn everything from a single video. It will be good if the video includes SQL DataTools.
There is too much to cover in C# to do that. I cover all of that in my Foundation in C# course series though: www.iamtimcorey.com/p/complete-foundation-in-c-course-series
@IAmTimCorey Hi! I just want to ask you a question. I have a PC with Win10 and Visual Studio 2019 on it. I created the SQLproject as you did but when I try to create the Person table, I receive this error message: 'Could not load file or assembly 'Microsoft.SqlServer.BatchParser.dll' or one of its dependencies. The specified module could not be found.' What is the problem? What can I do? Please help me! Thank you in advance! Sorry for my English.
At 47:41 when you import from existing DemoDB, a solution exists already, so you add a second SSDP "SQLFromExisting". I've done this and there's no problem when I reopen VS. Another way to start project from existing db: Start VS but "Continue with no code", no project. In SSOE, find existing db in my real server. Right click that; Start New Project. It creates a project as if I had selected SSDP at the start. Everything looks good until I close VS and open it, then the project doesn't show the objects that came from the original db. This result is similar to the problem I explained in my last comment. This other method came from a VS Las Vegas video (2015). If you haven't seen these problems, perhaps they're in the Community edition? Or I'm doing something wrong...
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
Yes, it is a best practice. When you don't specify dbo, the system uses the default namespace (which might or might not be dbo). If you have more than one namespace, this might cause you an issue. It also can, in certain circumstances, cause a slight speed issue (practically not a concern, but still something to consider).
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.💻
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.
My god tutorials are hard to find for SSDT, and this one is pure gold. 🥇 Thank you for making this.
You are welcome.
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!
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.
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.
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.
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!
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.
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.
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
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.
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 pure gold, Tim. I'm embarrassed to say that I'm a developer and not doing this.
Thank you!
Excellent introductory tutorial to start with. Thanks a lot.
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.
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. :)
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!
I had used database project, and published but seems like today I have understood it. Thanks Tim!
Excellent!
Taking a kickstarter so I could continue the Retail Manager Course!
The entire series is free on CZcams. No Kickstarter needed.
Thanks a lot
@@IAmTimCorey
@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.
I am so glad I found this video! Thanks Tim.
You' are very welcome!
I love it! So easy to understand, covers everything I needed to know plus more..Thank you for sharing
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.
this is an amazing guide, thank you so much
You are welcome.
Perfect Tim, this video is exactly what I needed.
Excellent!
Hi Tim, I'm late to the game and this completely blew my mind! Thanks so much for this video.
You are welcome.
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!
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.
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.
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 introduction video to this. Helped get me started really quickly - thank you!
Glad it was helpful!
Thank you Tim. Exactly what I've been wanting to learn.
Excellent!
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!
@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 tutorial! The only thing we miss in VS is the gaphical way to create view in SSMS.
Thanks!
Oh Tim, you saved me actually days with this tutorial! Thank you! Again... :-)
You are welcome.
Hey Tim, great video. Excellent guidance rithm through the tools to creating DBs. thanks.
You are welcome.
Amazing video! great content, great pace, and lucid explanations. Thanks much!
Glad you liked 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
Probably Stored Procedures, then Connect C# to SQL, and then Database Connection Strings but there is some overlap there.
@@IAmTimCorey Great thank you!
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 for all your videos 🙂
You are welcome.
The Comparing Tool Is Very Nice and Helpful .... Great job thank you brother.
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.
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.
Wonderful video. Very clear and informative. Thanks!
You are welcome.
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
Excellent video! I have leaned a lot from it. Thanks!
You are welcome.
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.
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 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'
wow! its really great tool. Many thanks!
You are welcome.
This video is fire
@34:24 "You've got mail!"
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 CZcamss that you could point me to that would help to understand this better, it would be greatly appreciated.
Could please make a video if possible explaining WCF(Windows Communication Foundation).
It is on my suggestion list. It might take a bit though.
Thank you Tim, another great video!
You are welcome.
I thought about it yesterday. Very in time ) thank you =)
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?
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.
This is great! I'm sure going to use it!
Thanks for building your skills with Tim.
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 CZcams. I do have the Application Lifecycle Design course which covers all of that: www.iamtimcorey.com/p/application-lifecycle-design
great Tutorial Tim, thanks a lot!
You are most welcome. Thanks for watching.
Good intro - very concise
Thank you!
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 do I publish a project with a database inside? New to programming here.
AWESOME...Really helpful sir. Thank you.
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.
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 :)
It's so great video. Thanks Tim!
Glad you liked it!
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 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?
Greate overview of this VS project type! keep up
Thanks!
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 CZcams, I am using this in my TimCo Retail Manager series to handle all of the database work.
In this kind of project, why wouldn't you assign it the name of the database?
That's the purpose of the project, to generate a specific database.
The objects below it (Tables, Views etc.) are database objects.
Wouldn't the project be kept by the developer team to support that database?
Example:
Project name / database for client Acme could be AcmePartsDev.
An imported database (revison of same db) into a new project could be named AcmePartsDev2.
These would publish to client's server as database AcmeParts.
If another database is needed, start a new project with that new db name (AcmeMachinesDev).
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.
Cool, it was very helpful. Thank you a lot !!!
You are welcome.
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?
This is just superb.
Thanks!
Excellent, thanks Tim
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.
Spectacular! 👏👏👏
Thanks!
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
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.
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, 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.
Tim, can you do a video for begineers to start from a Empty-Application in Visual Studion 2017, and learn everything from a single video. It will be good if the video includes SQL DataTools.
There is too much to cover in C# to do that. I cover all of that in my Foundation in C# course series though: www.iamtimcorey.com/p/complete-foundation-in-c-course-series
@IAmTimCorey Hi! I just want to ask you a question. I have a PC with Win10 and Visual Studio 2019 on it. I created the SQLproject as you did but when I try to create the Person table, I receive this error message:
'Could not load file or assembly 'Microsoft.SqlServer.BatchParser.dll' or one of its dependencies. The specified module could not be found.'
What is the problem? What can I do? Please help me! Thank you in advance! Sorry for my English.
At 47:41 when you import from existing DemoDB, a solution exists already, so you add a second SSDP "SQLFromExisting".
I've done this and there's no problem when I reopen VS.
Another way to start project from existing db:
Start VS but "Continue with no code", no project. In SSOE, find existing db in my real server.
Right click that; Start New Project. It creates a project as if I had selected SSDP at the start.
Everything looks good until I close VS and open it, then the project doesn't show the objects that came from the original db.
This result is similar to the problem I explained in my last comment.
This other method came from a VS Las Vegas video (2015).
If you haven't seen these problems, perhaps they're in the Community edition?
Or I'm doing something wrong...
Thanks Tim! super useful!
Thanks for watching
Thanks very much, excellent video
You are welcome.
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
Thank you, sir, for this video
You are welcome.
Thanks Tim, great job ^^
You are most welcome. Thanks for watching.
Why do we need to put "dbo." @40:14?
I tried to do just
select *
from Person
and it gives me the same output.
is the dbo. part of the best practices?
Yes, it is a best practice. When you don't specify dbo, the system uses the default namespace (which might or might not be dbo). If you have more than one namespace, this might cause you an issue. It also can, in certain circumstances, cause a slight speed issue (practically not a concern, but still something to consider).
Thank you for this information @@IAmTimCorey !
But, do we still do this even at an inline sql (inside our winfroms app).
Yep. That command is still going to SQL.
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!
Always love your videos :)
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.
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.
Great video. Great tool. Thanks.
You're welcome!
Spectacular!
Thanks for watching and trusting Tim