SQL Data Tools In C# - Database Creation, Management, and Deployment in Visual Studio
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 2024
- Full courses: www.iamtimcorey.com/courses
Source Code: leadmagnets.app/?Resource=SQL...
Patreon: / iamtimcorey
Newsletter signup: signup.iamtimcorey.com/
What if I told you that you could create a SQL Database in Visual Studio, manage it like any other project, track it with source control, deploy it to multiple environment, and refactor it like C# code? In this video, I am going to show you how to do all of those tasks and more. The best part is that it is free and comes baked into Visual Studio. And no, we aren't going to be using Entity Framework, although this method is compatible with Entity Framework.
Chapters provided by: Ralfs HBK
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
All these years a have been living in the dark. So much effort put into manual versioning databases when there were elegant solutions for the job. This is really great! Thank you Tim!
Amazing! Just amazing. I never knew that Visual Studio had such powerful tools for working with data. Thank you so much for creating and sharing such helpful content.
You are welcome.
Hi Tim, I worked through that video last night and thought it was excellent, I live in SSMS and am now have a project which needs to be deployed to multiple DBs, this is just what I need. Thank you.
Fantastic!
Once again Tim, great video. No matter how complex a topic is, you always make it simple. Thank you for your great work and contribution to the software development community. If I had known this for years, my database development experience would have been much easier. Nevertheless, this tutorial is an additional asset to my toolbox.
Thanks a lot!
Fantastic endorsement, thanks for your faithful support.
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 had used database project, and published but seems like today I have understood it. Thanks Tim!
Excellent!
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.
This is pure gold, Tim. I'm embarrassed to say that I'm a developer and not doing this.
Thank you!
Hi Tim, I'm late to the game and this completely blew my mind! Thanks so much for this video.
You are welcome.
I've been wondering what was that project type is being used for and now I know! Great video as always. Thanks!
You are welcome.
Excellent introductory tutorial to start with. Thanks a lot.
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.
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 am so glad I found this video! Thanks Tim.
You' are very welcome!
Wow I didn'T realize all of these features for SQL in Visual Studio, thanks for this. Truly a masterpiece of a software by microsoft, I gotta say.
Glad you enjoyed it!
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. :)
Hey Tim, great video. Excellent guidance rithm through the tools to creating DBs. thanks.
You are welcome.
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.
Hi Tim, another good video from you! That is really cool feature to manage database scheme, will include in my current project) thanks!
Awesome!
Great introduction video to this. Helped get me started really quickly - thank you!
Glad it was helpful!
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!
Oh Tim, you saved me actually days with this tutorial! Thank you! Again... :-)
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.
Perfect Tim, this video is exactly what I needed.
Excellent!
Thank you Tim. Exactly what I've been wanting to learn.
Excellent!
Amazing video! great content, great pace, and lucid explanations. Thanks much!
Glad you liked 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.
The Comparing Tool Is Very Nice and Helpful .... Great job thank you brother.
You are welcome.
Wonderful video. Very clear and informative. Thanks!
You are welcome.
Excellent video! I have leaned a lot from it. Thanks!
You are welcome.
I thought about it yesterday. Very in time ) thank you =)
You are welcome.
0:00 - Intro
1:12 - SQL Server Database Project in Visual Studio
4:35 - Designing a new SQL database: adding and editing tables
11:22 - Adding a foreign key
12:46 - Publishing the database
17:10 - Putting data in database
18:52 - Adding a View
23:20 - Adding a Stored Procedure
25:00 - Publishing database with the added changes
27:06 - Changing column names
31:41 - Schema Compare tool
39:37 - Database Queries
41:26 - New Database data: Data Comparison tool
43:13 - New Database data: Scripts
47:41 - Importing existing database to SQL project
49:49 - Summary and concluding remarks
Thank you! Rock Star!
this is an amazing guide, thank you so much
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.
@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!
Good intro - very concise
Thank you!
It's so great video. Thanks Tim!
Glad you liked it!
Thank you for all your videos 🙂
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!
This is great! I'm sure going to use it!
Thanks for building your skills with Tim.
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.
great Tutorial Tim, thanks a lot!
You are most welcome. Thanks for watching.
@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.
AWESOME...Really helpful sir. Thank you.
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
Tim, Thanks for the great video. It has helped me understand localdb much better. I still have 2 questions. 1. If I publish my project and copy the executable to a new computer, will it create the databases automatically the first time it runs? 2. Could I allow the user to point to an SQL Server on the cloud and (with the correct permissions) create the databases automatically there? If you have any courses on your web site or TH-cams that you could point me to that would help to understand this better, it would be greatly appreciated.
This is an amazing tutorial! Thank you!
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!
Thank you very much for another awesome tutorial
You are most welcome. Thanks for watching.
Greate overview of this VS project type! keep up
Thanks!
wow! its really great tool. Many thanks!
You are welcome.
Cool, it was very helpful. Thank you a lot !!!
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?
Excellent, thanks Tim
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.
Taking a kickstarter so I could continue the Retail Manager Course!
The entire series is free on TH-cam. No Kickstarter needed.
Thanks a lot
@@IAmTimCorey
Always love your videos :)
You are welcome.
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.
Thanks Tim, great job ^^
You are most welcome. Thanks for watching.
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!
Thanks Tim! super useful!
Thanks for watching
How do I publish a project with a database inside? New to programming here.
Thanks very much, excellent video
You are welcome.
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 TH-cam. I do have the Application Lifecycle Design course which covers all of that: www.iamtimcorey.com/p/application-lifecycle-design
Spectacular! 👏👏👏
Thanks!
A small coffe (I wish my currency was not that bad =P ) for the simple and easy to understand video
Thank you!
@aindaecedo realmente, dá vontade de chorar rsrsrs. Vc já trabalha na área?
@@Antonio-lt1sp oi Antonio, trabalho sim, mas não conhecia criar via VS studio, sempre usei o MSSQL para criar tudo
This is just superb.
Thanks!
This video is fire
@34:24 "You've got mail!"
Thanks!
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 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.
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.
Great video. Great tool. Thanks.
You're welcome!
Nice work! Thank you. Do you have any follow up videos on this? You had mentioned showing us how to use with TFS and within teams.
I have two courses that do just that. The Database DevOps from Start to Finish course ( www.iamtimcorey.com/p/database-devops-from-start-to-finish/ ) shows you how to build a database with this, deploy it, bring in an existing database, and even work with external changes. The Application Lifecycle Design course ( www.iamtimcorey.com/p/application-lifecycle-design/ ) covers using Azure DevOps to deploy this automatically to an Azure SQL database (although the principle applies to even local databases). It also covers deploying web apps, creating different versions (development, staging, and production), and how to put all of this under source control. On TH-cam, I am using this in my TimCo Retail Manager series to handle all of the database work.
@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/
Thank you Tim for video, just question: if I have user in security options of my db, how to add it automatically in publishment of my c# project?
That would be a post-deploy script.
Hi Tim, is the a way to add conditions? for example script that should run on enterprise (compression) vs standard, sql different versions (2014, 2016) with unsupported features?
You have to specify which database you are going to target. That will limit what tools you have available. However, you can run pre and post scripts using TSQL so if you can do it in TSQL, you can run it during deployment.
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.
Question for you, Tim... What if you have several db instances. What is the best practice to deploy the latest schema changes to all instances? I tried creating a publish.xml file with multiple property groups (one for each db instance) but that did not work. I was thinking of writing code that would automate via command line and generate .publish.xml files for each db, and then deploy to each sequentially. Any advice, or a known method for doing this?
You can use a tool like Azure DevOps to automate the deployment of your database to as many instances as you want.
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?
Really helpful.. Tim
Great!
Hello Tim is the Post Publish Script the only way to alter a Table after it has been created and already has data stored in it? I'm trying to add a column I forgot about but I can no longer publish. I just went ahead and deleted the table and created it again because I only had 2 rows, but in the future when there is significant data stored that wouldn't be a good idea.
You can do a Pre-Deploy script as well, in case you need to clean things up before you add a column or delete a column. If you attempt to do something that will cause data loss, the system won't do it automatically without you explicitly forcing it (it is a safety measure). You can get the script that it created for the update and modify it to force the update, then run the script.
hi Tim, can i ask what is the best practice to do the database versioning in code? for example for database version 2.0 i have added 3 new columns, how do i keep track on those changes and upgrade or downgrade the database versions for different customer?
This tool will allow you to keep track of how your database changes over time. Rollback is a tricky one. If you added columns, put data in, and then try a rollback, it will fail (or delete data).
Thank you, sir, for this video
You are welcome.
Hi Tim, I have reached the point of linking the foreign key and the ID key. But every time I publish it the value for Address.PersonID will return to allowing nulls because it says there is no data present so allowing it will cause data loss
It sounds like a setting on your SQL Server that is preventing the change. However, you can manually run the change and it should work.
another beginner question: You use the term scripts @ 16:39 while hoovering over the properties window. Also you are renaming a lot of files, hard to keep up.
I have been trying to connect two tables (primary/ foreign key) but the Red exclamation point say My data cannot be saved. Any suggestions.
It sounds like you have a syntax error but I'm not sure. You can add a foreign key relationship to link the two tables. The trick is that the types need to be of the same type (int to int) and you should link it to a primary key column (one that is marked as such).
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.
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?
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 :)
Hi Tim. Great video. So I have a Post Script question. Lets say I do a release on Monday and I have some post scripts where I add Frank to the User table. Then Wednesday I'm gonna do another release where I'm gonna add some Addresses to the Address table. Given the fact I'm gonna keep running the post script file on every release it means all my scripts need to do some type of check so it does not add from the previous script. Like you did in your example from the video where you did an exists check before adding the Demo user. So my question is there a way so that on every release I don't have to run the previous post scripts? Each release just runs whatever is new for this release. I don't want to keep running the previous post scripts. Thanks!
Not natively, no. You could create a version table and based upon the current version, you would run the post scripts after that version.
As of 2021, will this work for non-relational databases (i.e. cosmos db) as well? My initial thoughts are, it wouldn't. However I cannot find much about it in the web and I was wondering if someone might have tried?
The SQL Data Tools only work with Microsoft SQL. NoSQL databases are totally different and are managed totally different, especially since they don't have a structure like a relational database. You can find out more here: th-cam.com/video/exXavNOqaVo/w-d-xo.html
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.
Thank you, nice video.
Glad you liked it!
Hello, at 46:31 you say that if we rename the name of added person, it will affect the PostDeployPerson script as well. I tried this and it didn't work. I renamed the added person in table, and after that I pressed "publish" and this added the previous person again. Is there any special way to rename the added person, so it would affect the PostDeploy script?
Did you use the Ctrl+. method and select the rename everywhere option?
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'
Thank you, really helpful
You're welcome!
Spectacular!
Thanks for watching and trusting Tim
Hi Tim, under dbo.Person when refactoring its PersonID, I notice from the View FullPerson.sql, does this will not affect:
Before:
select [p].[Id] AS PersonID, [p].[FirstName], [p].[LastName],
After refactoing from ID to PersonID
select [p].[PersonId] AS PersonID, [p].[FirstName], [p].[LastName],
i was hoping the intellisense drop the "as PersonID", since you already execute refactoring process.
Yeah, it isn't quite that intelligent.
32:35 I don't have such "Update" button. What should I do then? :( Also, at 45:12 I cannot publish even when I leave PostDeployPerson script empty. What do you think, where can be the error?
For the first problem, it sounds like you were not modifying the view from the right location. If you modified it from the project, you won't get the Update button. If you modify it from the database, you will get that update button. As for not being able to publish, that all depends on what the error is. I would recommend reading the error statement and fixing what it says is the problem.