開発のあいまに: SQL Server 2012

2019年4 月 6日 (土)

EFCoreで実行したストアドのSelect結果が0件になるバグ

ASP.NET Core + Entity Framework Core の組み合わせで、ストアドプロシージャ内でSelectした結果が0件になるというバグに遭遇しました。
実行するSQL文を、FromSql()実行前に生成しておくと解消する。

ストアドプロシージャ

CREATE PROCEDURE [clt].[spKeyword_Select]
    @SearchWord    [nvarchar](100)
AS
BEGIN
    SELECT Top 100
        [No] as Id,
        [Word]
    FROM
[mst].[tKeyword]
    WHERE
        Word like '%' + @SearchWord + '%'
END

// 実行結果：0件
var a = context.Keyword
    .FromSql("EXECUTE clt.spKeyword_Select '{0}'", "a")
    .ToList();

// 実行結果：0件
var b = context.Keyword
    .FromSql($"EXECUTE clt.spKeyword_Select '{"a"}'")
    .ToList();

// 実行結果：100件
var c = context.Keyword
    .FromSql("EXECUTE clt.spKeyword_Select 'a'")
    .ToList();

// 実行結果：100件
string para = $"EXECUTE clt.spKeyword_Select '{"a"}'";
var d = context.Keyword
    .FromSql(para)
    .ToList();

ただ、SQL文のパラメータをstringで渡すとSQLインジェクションの問題が発生するので、実質はSqlParameter()を使って渡すやり方しかない。

// 実行結果：100件
var paraSearchWord = new SqlParameter("SearchWord", "a");
var e = context.Keyword
.FromSql("EXECUTE clt.spKeyword_Select @SearchWord", paraSearchWord)
.ToList();

環境
・Windows 10
・Visual Studio 2017 15.9.11
・ASP.NET Core 2.2
・Entity Framework Core 2.2
・Microsoft SQL Server 2017 (RTM-CU13) 14.0.3048.4 (X64)

参考

https://docs.microsoft.com/ja-jp/ef/core/querying/raw-sql

2019/04/06 18:04:18 | .NET Core, ASP.NET Core, Entity Framework Core, SQL Server 2012

2018年4 月 8日 (日)

.NETCoreでSqlConnectionを使いSQLServerに接続する場合

.NET Core で SqlConnection を使う場合、System.Data.SqlClient をプロジェクトに追加する必要がある。
.NET Core は .NET Framwork の様に、プロジェクトの参照を右クリックして System.Data.SqlClient を参照先に追加するのではなく、「NuGet パッケージの管理」で System.Data.SqlClient を検索しインストールする。

あとは .NET Framwork と同じソースコードで、SqlConnectionを使ってDB処理ができる。


using System;
using System.Data.SqlClient;

namespace test
{
    public class TestClass
    {
        public void Test_DB接続()
        {
            using (var connection = new SqlConnection("Server=IP;Initial Catalog=DB;Persist Security Info=True;"))
            {
                var command = new SqlCommand("SELECT TOP 10 Id, Name, Price FROM Products", connection);
                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader[0]}:{reader[1]} ${reader[2]}");
                     }
                 }
            }
        }
    }
}

2018/04/08 11:56:22 | .NET Core, SQL Server 2012, Visual Studio 2017

2017年11 月13日 (月)

Microsoft公式の開発支援ドキュメントメモ

Microsoft Docs

ASP.NET Core チュートリアル

Visual Studio 入門

2017/11/13 4:28:34 | C#, SQL Server 2012, Visual Studio 2017, 開発方式

2017年11 月 5日 (日)

SQLServerで、テーブルの行数を、スキーマ名とテーブル名でソートして抽出するSQL


select schemaname, tblname, rowcnt
from (
  select
    schema_name(SYSOBJ.schema_id) as schemaname, 
    SYSOBJ.name as tblname,
    SYSINDEXES.rows as rowcnt
  from sys.objects as SYSOBJ inner join sys.sysindexes as SYSINDEXES
    on SYSOBJ.object_id = SYSINDEXES.id and SYSINDEXES.indid < 2
  where SYSOBJ.type = 'U'
) as t
order by schemaname, tblname;

2017/11/05 14:54:36 | SQL Server 2012, T-SQL

2017年5 月 4日 (木)

「'String[1]: Size プロパティのサイズが 0 です。これは無効です。」例外が発生する原因

C#でSQLServerのストアドを実行する際、Intput/Outputのパラメータを指定しますが、VarCharはOutputパラメータだけ桁数を指定しないといけません。 Outputパラメータの桁数を指定し忘れると「'String[1]: Size プロパティのサイズが 0 です。これは無効です。」例外が発生します。


cmd = new SqlCommand("スキーマ名.ストアド名", cn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("抽出条件", SqlDbType.VarChar));
cmd.Parameters["抽出条件"].Direction = ParameterDirection.Input;
cmd.Parameters["抽出条件"].Value = 抽出条件;

cmd.Parameters.Add(new SqlParameter("結果", SqlDbType.VarChar, 8000));	// VarChar(8000)
cmd.Parameters["結果"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

return (string)cmd.Parameters["結果"].Value;

2017/05/04 21:35:46 | C#, SQL Server 2012, T-SQL, Visual Studio 2015

2016年2 月13日 (土)

SQLServerで、バックアップ先のアーカイブテーブルにあって、本テーブルに無いデータを抽出するSQL

複数列をキーにしている場合、IN()は使えないのでexistsを使う。
where not exists ()の中で「SELECT *」を指定していますがあまり意味はなく、アーカイブテーブルと本テーブルのテーブル定義が違っていてもexists()は使えます。今回サンプルとしたtSecテーブルは、[通貨ペアNo]と[日時]の2列が主キーでした。

SELECT *
FROM FX_ACV.hstr.tSec as a
where not exists 
(
	SELECT *
	FROM FX.hstr.tSec as b
	where (a.[通貨ペアNo] = b.[通貨ペアNo] and a.[日時] = b.[日時])
)

アーカイブDBに有って本DBには無いデータを全て、本DBへInsertするSQLはこんな感じ。

INSERT INTO FX.hstr.tSec
           (通貨ペアNo
           ,日時
           ,買いRate
           ,売りRate)
SELECT 通貨ペアNo
      ,日時
      ,Rate_買い
      ,Rate_売り
FROM FX_ACV.hstr.tSec as a
where not exists 
(
	SELECT *
	FROM FX.hstr.tSec as b
	where (a.通貨ペアNo = b.通貨ペアNo and a.日時 = b.StartDate)
)

2016/02/13 13:52:17 | SQL Server 2005, SQL Server 2008, SQL Server 2012, T-SQL

2015年9 月23日 (水)

データの上昇角度を求めるSQL

ビッグデータシステムなど、大量のデータを解析する場合、JAVAやC#では計算元のデータを読込むだけで膨大な時間がかかってしまうので、最近では数十テラのデータであってもDBにインポートしてから、SQLで分析するのが主流になっています。
以下は、新しいデータが1つ前のデータに対して、どれだけ値が上昇したのか、その角度を求めるSQLServerのT-SQLです。データが増える度にリアルタイムでこのSQLを実行すると、角度から、データの急激な動きを視覚的に把握できるようになります。

DECLARE @StartDate DATETIME = '2015-08-29 06:45:00.000';

DECLARE @現在の値 FLOAT = 0;
DECLARE @1つ前の値 FLOAT = 0;

DECLARE cursor_DataHistory CURSOR FOR
SELECT TOP 2 [値]
FROM [DataHistory]
WHERE [StartDate] <= @StartDate
ORDER BY [StartDate] DESC;
	
open cursor_DataHistory;

FETCH NEXT FROM cursor_DataHistory INTO @現在の値;
FETCH NEXT FROM cursor_DataHistory INTO @1つ前の値;

CLOSE cursor_DataHistory;
DEALLOCATE cursor_DataHistory;

SELECT (ATAN((@現在の値 - @1つ前の値) / 1) * 180 / PI()) AS 上昇角度;

2015/09/23 2:48:52 | SQL Server 2005, SQL Server 2008, SQL Server 2012, T-SQL, ビッグデータ, 分析