幽嵐飋翼

這篇筆記裡可能有用到其他大神們的網頁紀錄，但愧疚於內容是好久以前查的，最近另一個專案要重寫這一塊，所以重新翻出以前的程式碼，順便再於此註記筆記，若各路大神有發現其中語法有模仿痕跡，還請通知並見諒，我會補上參考連結，謝謝。

 

1. 首先SQL裡有一張DBTable，其格式如下圖：

2. 在StoredProcedure(預存程序)裡建立一個SQL Type，可以讓其他StoredProcedure和C#使用：

USE [MI]
GO
/****** Object:  StoredProcedure [dbo].[CatchFuturesCode_FutStkMapping_CreateType] ******/    
/****** Script Date: 2019/4/14 下午 03:47:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wingel
-- Create date: 2019-04-13
-- Description:	創造一個Type可以讓CatchFuturesCode以DataTable的形式批次更新FutStkMapping
-- =============================================
ALTER PROCEDURE [dbo].[CatchFuturesCode_FutStkMapping_CreateType] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	--刪除舊有的Schema
	IF TYPE_ID('[FutStkMappingType]') IS NULL
	--DROP TYPE [FutStkMappingType]
	BEGIN
	CREATE TYPE [FutStkMappingType] AS TABLE
	(
		[FuturesSymbol] [varchar](10)	NOT NULL,
		[StockSymbol]	[varchar](10)	NOT NULL,
		[ModUser]		[varchar](50)	NOT NULL,
		[ModDate]		[decimal](8, 0) NOT NULL,
		[ModTime]		[decimal](6, 0) NOT NULL,
		PRIMARY KEY ([FuturesSymbol])
	);
	END
END

第二步驟按F5執行並存入StoredProcedure後，要開新查詢寫入

EXEC [MI].[dbo].[CatchFuturesCode_FutStkMapping_CreateType]

執行一遍這樣才能在SQL裡建立[FutStkMappingType]，讓第四步驟的StoredProcedure能正常執行並儲存。

3. 正常一筆一筆寫入DB的StoredProcedure長這樣，簡單易懂，但是如果資料筆數過多，容易造成C#執行時效率不佳：

USE [MI]
GO
/****** Object:  StoredProcedure [dbo].[CatchFuturesCode_FutStkMapping_SetRawData] ******/    
/****** Script Date: 2019/4/14 下午 03:48:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wingel
-- Create date: 2019-04-12
-- Description:	從CatchFuturesCode把期交所的個股期代號與標的代號之配對寫進此表
-- =============================================
CREATE PROCEDURE [dbo].[CatchFuturesCode_FutStkMapping_SetRawData]
	-- Add the parameters for the stored procedure here
	@FuturesSymbol varchar(10)
		,@StockSymbol varchar(10)
		,@ModUser varchar(10)
		,@ModDate decimal(8,0)
		,@ModTime decimal(6,0)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM [dbo].[FutStkMapping];

	INSERT INTO [dbo].[FutStkMapping]
		([FuturesSymbol]
		,[StockSymbol]
		,[ModUser]
		,[ModDate]
		,[ModTime])
	VALUES
		(@FuturesSymbol
		,@StockSymbol
		,@ModUser
		,@ModDate
		,@ModTime);
END

在這篇批次運行之中，第三步驟的StoredProcedure是不用建立的，不會用到，寫出來僅是筆記一下，方便未來查詢。

4. 批次寫入的方式會長這樣：

USE [MI]
GO
/****** Object:  StoredProcedure [dbo].[CatchFuturesCode_FutStkMapping_SetRawDataBatch] ******/    
/****** Script Date: 2019/4/14 下午 03:50:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Wingel
-- Create date: 2019-04-13
-- Description:	從CatchFuturesCode把期交所的個股期代號與標的代號之配對批次寫進此表
-- =============================================
CREATE PROCEDURE [dbo].[CatchFuturesCode_FutStkMapping_SetRawDataBatch]
	-- Add the parameters for the stored procedure here
	@dt [FutStkMappingType] READONLY
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DELETE FROM [dbo].[FutStkMapping]
	INSERT INTO [dbo].[FutStkMapping]
	(
		[FuturesSymbol],
		[StockSymbol],
		[ModUser],
		[ModDate],
		[ModTime]
	)
	SELECT *
	FROM @dt AS newdata
	--WHERE NOT EXISTS ( SELECT [FuturesSymbol]
	--				   FROM [dbo].[FutStkMapping] AS db
	--				   WHERE db.[FuturesSymbol] = newdata.[FuturesSymbol])
	--刪除本次新增的Schema
	--IF TYPE_ID('[FutStkMappingType]') IS NOT NULL
	--DROP TYPE [FutStkMappingType]
END

其中@dt的格式是第二步驟建立的Type，所以第二步驟建立完要先

以上，SQL這邊該處理的事情就完成了！

5. 在C#這邊，需要寫入的方法如下：

(註：DataSource的名字是假的，亂碼換過了，但\SQLEXPRESS寫上是可以連的到，

User ID使用sa，密碼也是Demo而已，請各位路過大神們別拿這個測試，不會成功登入的XD)

        public void Write_in_DB_batch1()
        {
            string connectionString = @"Data Source=DESKTOP-8B9EPN2\SQLEXPRESS;" +
                                       "Initial Catalog=MI;Persist Security Info=True;" +
                                       "User ID=sa;Password=000000";

            string strSQL = "";

            SqlCommand command = new SqlCommand
            {
                //CommandText = strSQL;
                Connection = new SqlConnection(connectionString),
                CommandType = CommandType.Text
            };

            ConnectionState previousConnectionState = command.Connection.State;
            if ((command.Connection.State & ConnectionState.Open) != ConnectionState.Open)
            {
                command.Connection.Open();
            }
            int returnValue;
            try
            {
                strSQL = "EXEC CatchFuturesCode_FutStkMapping_CreateType";
                command.CommandText = strSQL;
                returnValue = command.ExecuteNonQuery();

                strSQL = "EXEC CatchFuturesCode_FutStkMapping_SetRawDataBatch @outputdt";
                SqlParameter sp = new SqlParameter
                {
                    ParameterName = "@outputdt",          //同strSQL後面代的參數名稱
                    TypeName = "FutStkMappingType",       //需與SQL的Type同名
                    SqlDbType = System.Data.SqlDbType.Structured,
                    Value = FutStkMappingDataTable        //C#裡面的DataTable變數，欄位數與各欄的Type需與SQL的自訂Type一致
                };
                command.Parameters.Add(sp);
                command.CommandText = strSQL;
                returnValue = command.ExecuteNonQuery();
            }
            finally
            {
                if ((previousConnectionState == System.Data.ConnectionState.Closed))
                {
                    command.Connection.Close();
                }
            }
        }

可以自己另寫辦法去接兩個returnValue，如果執行成功，返回值會是-1，似乎是因為使用StoredProcedure的原因，所以無法返回受影響列數

如果失敗，則會丟出SqlData.Exception，可以去接exc.Message查看錯誤原因。