bulkCopy + SQL验证栏位格式

bulkCopy 插入临时表
ISNUMERIC 验证数字
ISDATE 验证日期

public DataTable FileContrastReview(DataTable dt)
{
    DataSet ds = new DataSet();
    using (var conn = new SqlConnection(CRM.DBUtility.DbHelperSql.CrmUpdate))
    {
        using (var command = new SqlCommand("", conn))
        {
            try
            {
                conn.Open();
                //数据库并创建一个临时表来保存数据表的数据
                command.CommandText = @"CREATE TABLE #tpResalesTarget (
                            [Branch_Code] NVARCHAR(20) NOT NULL,
                            [Year] NVARCHAR(10) NOT NULL,
                            [Month] NVARCHAR(10) NOT NULL,
                            [Resales_Money_Target] NVARCHAR(20) NOT NULL );";
                command.ExecuteNonQuery();

                //使用SqlBulkCopy 加载数据到临时表中
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.BulkCopyTimeout = 660;
                    bulkCopy.DestinationTableName = "#tpResalesTarget";
                    bulkCopy.WriteToServer(dt);
                    bulkCopy.Close();
                }
                string strsql = @" 
                    --验证栏位格式
                    IF OBJECT_ID('tempdb..#CheckResalesTarget') IS NOT NULL
                        DROP TABLE #CheckResalesTarget;
                    SELECT  *
                    INTO    #CheckResalesTarget
                    FROM    ( SELECT  * ,
                                    CASE WHEN ISNUMERIC([Branch_Code]) = 0 THEN CONVERT(NVARCHAR(10), [Branch_Code]) + N')行号有误'
                                            ELSE N''
                                            END + CASE WHEN ISDATE([Year]) = 0 THEN CONVERT(NVARCHAR(10), [Year]) + N')年份有误'
                                            ELSE N''
                                            END + CASE WHEN ISDATE('1990' + '' + [Month] + '' + '01') = 0 THEN CONVERT(NVARCHAR(10), [Month]) + N')月份有误'
                                            ELSE N''
                                            END + CASE WHEN ISNUMERIC([Resales_Money_Target]) = 0 THEN CONVERT(NVARCHAR(10), [Resales_Money_Target]) + N')金额有误'
                                            ELSE N''
                                    END Error_Msg
                                FROM      #tpResalesTarget
                            ) TCheck;

                    IF EXISTS ( SELECT * FROM  #CheckResalesTarget WHERE Error_Msg<>N'')
                    BEGIN
                        SELECT  Branch_Code AS BranchCode ,
                                Year + '-' + Month AS MonthDate ,
                                Resales_Money_Target AS ResalesTarget ,
                                Error_Msg AS ErrorMsg
                        FROM    #CheckResalesTarget
                        ORDER BY Error_Msg DESC ,
                                Branch_Code ASC ;
                        RETURN;
                    END; ";
                    SqlDataAdapter cmd = new SqlDataAdapter(strsql, conn);
                cmd.Fill(ds, "ds");
            }
            catch (Exception e)
            {
                throw new DBUtility. CrmDbException(e.Message, e.StackTrace, null);
            }
            finally
            {
                conn.Close();
            }
        }
    }
    return ds.Tables[0];
}