Hello again…today I’m going to start posting while reviewing my wonder app’s code…and it all starts from Sql so I’ll start there.
Where I work…MS Sql is pretty readily available…so I’ve always just used that. To be honest, I don’t really have a preference…but I’ve used MS Sql for learning 90% of my Sql knowledge so that’s where I’m most comfortable. The other 10% is split between some messing around in MySql for storing Minecraft mod data…and a little a for doing some php forums storage once a loooong time ago.
So my initial knowledge comes from way back in 2007-2008 time frame where I was given a lot of time to dig in and figure it out. I’m one of those self taught idiots though so it took me a lot of time. Back then I created my databases through use of script files that had to be run in a specific order and miticulously maintained through some sort of “master script”…a real pain in the butt. I used a lot of stored procedures in my early days but that’s how it was done. You create a sproc to do heavy processing of data and return the desired info in a dataset…you feed that into some C# objects and then you work with them using DataTables…pretty straight forward.
In current day, the Sql engine pretty much runs the same…sure there’s more bells and whistles…but deploying the database and accessing the data are handled way different. I like it for the most part, but there’s bits that I’ve found to be a pain in the butt.
So now for deployment, I make use of a Sql Server Database Project in Visual Studio.
Really this doesn’t do anything different than what I was doing creating script files and running them in the correct order when deploying the database…however Visual Studio is now taking the creation and execution part out of the equation. It takes care of all that now and I just have to focus on creating the tables and relationships between them. Also the Schema Compare functionality has spoiled me pretty bad when it comes to deploying.
One of the pain in the butt things about using this however…when creating a new table it likes to use the following for defining the default Id column…
CREATE TABLE [dbo].[InterestingDemoStuff] ( [Id] INT NOT NULL PRIMARY KEY )
This works just fine for creating new Id’s automagically…however what it doesn’t do is create an index for it that Linq To Sql can use. It creates an “unnamed” index which works within the context of Sql Server, but for some reason not Linq to Sql, so that being the case here’s what I like to use…
CREATE TABLE [dbo].[InterestingDemoStuff] ( ID INT IDENTITY(1, 1) NOT NULL, CONSTRAINT PK_InterestingDemoStuff PRIMARY KEY CLUSTERED (Id ASC) )
This will create a named index that works exactly the same as the unnamed one. The other quirck about using a Sql Server Project is that it doesn’t handle scripting security roles very well…especially if dealing with an Azure Sql database…so take that with a grain of salt. You can set it up on the server, do a Schema Compare and import the security settings into the project…but since the project doesn’t have any access to Azure AD…there’s no way for it to validate the user names and it causes issues when trying to deploy through Visual Studio. Here’s how I’ve been doing it.
- Create the tables and relationships in Sql Server Project
- Create a database on the server with no tables
- Use the Sql Server Project to do a Schema Compare between the project and the newly created database
- Update the target with any differences detected in the Schema Compare
- Apply security settings to the database on the server
This seems to be a pretty “quirk free” process to get all the bits and pieces of a Sql Db deployed. Next I’ll post about my thoughts on table design, normalizing, and foreign key relationships in the context of using it with Linq to Sql. Until then…hopefully this will be useful to someone. Enjoy! 🙂