18
JAN
.NET and Azure Developer
Data Platform SQL Server, Entity Framework and Data Related Technologies
11
JUN
Import Data to Azure Storage Table from SQL Server
Recently, there was a demand to switch data storage from SQL Server database to Azure Storage Table. However, neither SSMS nor Azure Portal provide a direct import function. You may now consider writing a tool to import data, but actually it isn't necessary. I still managed to do it within mouse clicks. But the first thing to warn everyone is that relational databases like SQL Server are not …
20
MAY
SQL Server Export Data Tier Application via Command Line
We can use SSMS to export data-tier application for an SQL Server database. I've been using this method for several years on my local machine. However, to make things work under automated environments, like CI/CD environments, or some automated scheduled tasks. We usually need to do it under the command line. Microsoft has provided a cross-platform command-line tool that can import/export DAC: …
29
JAN
Merge Data for N-N Relationship Tables in T-SQL
Recently, I have been organizing my blog data and needed to perform an operation to merge article categories. On my blog, articles and categories have a many-to-many relationship. That is, one article can belong to multiple categories, and one category can contain multiple articles. This is a very typical many-to-many relationship, and I use a many-to-many table with a composite primary key to associate this data. What I need to do is move articles from the "DotNetBeginner" category to the "CSharpAndDotNet" category. However, since some of the articles originally in the "DotNetBeginner" category also belong to "CSharpAndDotNet", directly updating the association table would result in duplicate composite keys, which would cause issues.
22
MAR
Azure SQL数据库Web Tier爆了，如何迁移数据库
今天做了次数据库迁移，目的是开个最新版的Azure SQL Database（V12 Update），然后把博客的数据库迁移到新的server上去。按以往的做法（也就是我曾经写过的《图解：如何将SQL Server数据库迁移到SQL Azure 》），把bacpac文件下载下来，然后import到新的数据里，结果爆了： 爆炸是因为Web Tier和Business Tier在最新版的Azure上面已经被微软撸掉了，SSMS 2014却没有升级，不认识这两个Tier，还在用老的Web Tier。 TITLE: Microsoft SQL Server Management Studio ------------------------------ An exception occurred while executing a Transact-SQL statement or batch.
03
SEP
EntityFramework 6 SqlQuery Passes Nullable Type Arguments
有时候我们需要在EF里直接执行参数化的SQL语句，如果有返回，就要把结果映射成C#对象集合。貌似是从EF4.2开始（4.0写法不一样）提供了DbContext.Database.SqlQuery()的API可以直接执行SQL。但是如果碰到可控类型的参数，比如Guid?，就会爆。 看一个例子，定义的SQL语句如下： string sql = @"SELECT p2.Id AS PeriodId, p2.Title AS PeriodTitle, u.UserId AS UserId, u.DisplayName AS
04
OCT
EntityFramework中使用.Include()做饥饿加载可能产生的性能问题
这几天在码新版博客程序，因为文章表字段太多，手贱把几个相关列拆分到了1-1的表中。比如Post 1-1 PostPublish，Post 1-1 PostExtension。但是性能突然比以前差了一点。 和这两张相关表直接交互的是这么一段代码： var query = Repository.Select().Include(p => p.PostExtension).Include(p => p.PostPublish) .Where(p => p.PostPublish.IsPublished && (authorName == null || String.Compare(p.Author, …
29
APR
图解：如何将SQL Azure数据库备份到本地SQL Server
用了两天SQL Azure后，发现SQL Azure数据库没有办法做传统意义上的“bak”备份，但我们确实很需要能够随时将数据库备份到本地，以更灵活的使用。所以今天就开荒了一下操作方法，并分享给大家。这样一来，我们既可以把SQL Server迁移到SQL Azure，又可以反过来把SQL Azure撸回SQL Server，这是一个比较完整的装逼过程，妹子们一定会赞不绝口。 步骤如下： 1. 启动SSMS2012，在目标SQL Azure数据库上点右键，选择“Export Data-tier Application”。 2. 在Export Settings选项卡里制定本地的备份路径，现在已经是“bacpac”拓展名的了。。。 3. 然后点击Next，确认后就向导就开始撸了。 4. 撸完以后会显示Operation Complete的消息，现在本地备份文件已经创建完成了。 5. 在 …
28
APR
图解：如何将SQL Server数据库迁移到SQL Azure
最近为了进一步装逼，决定将本博客的数据库从SQL Server 2008 R2迁移到Windows Azure上，即SQL Azure，云数据库。今天花了半天时间成功完成了迁移，把经验拿出来给大家分享。 注意：本文的方法只在SQL Server 2008 R2和SQL Server 2012数据库上试过，其他版本生死未卜。 首先，不管你现在的数据库是MSSQL2008R2的还是MSSQL2012的，你都需要安装SQL Server 2012版的SSMS。这是最方便的迁移方法。如果不想用SSMS，可以试试codeplex上的这个工具：http://sqlazuremw.codeplex.com/ 1. 启动SSMS后，在要被迁移的本地数据库上点右键，选择Tasks - Deploy Database to SQL Azure... 2. 在弹出的向导中，点击Connect，连接到你在 …
09
DEC