问题[c#](dba)

我的公司从一开始就被束缚在这种 MS Access '97 .MDB 格式上。我们的模式是分层的，具有多个一对多的关系。我们正处于寻找替代方案的地步，因为 Access 的缓慢和整体笨拙开始影响我们的生产力。

我们访问数据库的“现代”方法涉及 DAO.Net 和大量基于哈希的缓存。.NET System.Collections.Generic.Dictionary 类型在这里是天赐之物，因为没有它，我不知道我们将如何及时完成工作。我们有多个项目，每个项目都有一个与之关联的数据库文件（有时是多个），我们倾向于通过以下两种方式之一进行交互：要么手动创建数据库（使用我们的内部编辑器），要么使用程序生成它以其他格式获取我们从另一家公司收到的数据，并将其转换为我们的格式。

在这两种情况下，我们的通用 .NET 库通过 Dictionary 将整个数据库加载到哈希表中，并通过在哈希表中按 ID 查找值来解析对象与代码的关系。在自动生成数据库时，我们使用另一组哈希表来确定一个对象在添加之前是否已经存在于缓存中。一旦我们完成了源数据的解析，我们就开始一个多线程的批量插入操作。我们这样做是因为任何其他访问数据库的方法都非常慢。

我希望我已经为我的问题提供了足够的背景信息：是否有一个数据库引擎，其查询速度可以与我正在使用的哈希表相媲美？内存和磁盘使用无关紧要，这些数据库仅存在于开发人员机器上，我们将它们转换为不同的格式以与我们的软件一起使用。我只想摆脱我的哈希表，但我不想牺牲速度来做到这一点。

我编写了一个 Postgresql 函数来插入一行并返回从标识列生成的值。尝试从 C# 调用它时收到以下异常。

Npgsql.PostgresException：'42601：查询没有结果数据的目的地'

我环顾四周寻找答案，似乎 ExecuteScalar 对其他人有用，但在我见过的所有示例中，通常是在使用 RETURN QUERY 时，而不是局部变量。我错过了什么？

这是功能：

CREATE OR REPLACE FUNCTION public.func_insert_item(_name character varying)
 RETURNS BIGINT
 LANGUAGE plpgsql
AS $function$
DECLARE
    _item_id BIGINT;
BEGIN

    INSERT INTO public.items
    (
        name
    )
    VALUES
    (
        _name
    )
    RETURNING _item_id;
    
    RETURN _item_id;
END;
$function$

这是C#：

        static NpgsqlParameter CreateParameter(string name, ParameterDirection direction, string value)
        {
            var parameter = new NpgsqlParameter(name, NpgsqlTypes.NpgsqlDbType.Varchar, value.Length);
            parameter.Direction = direction;
            parameter.Value = value;

            return parameter;
        }

        static void Main(string[] args)
        {
            using var connection = new NpgsqlConnection(connectionString.ToString());
            connection.Open();
            
            using var command = new NpgsqlCommand("func_insert_item", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(CreateParameter("_name", ParameterDirection.Input, name));

            object itemId = command.ExecuteScalar();
        }

我需要实现以下要求。我已经设置了一个远程 sql server express，某些用户应该能够在远程服务器上创建数据库并分配其他用户对其进行访问（读、写）。用户只能看到他们创建的或他们拥有权限的数据库。

首先，我在远程服务器上创建了一个空数据库=> dbo for new database is x，然后我创建了一个不同的登录名：

USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

然后拒绝查看任何数据库：

USE master;
GO
DENY VIEW ANY DATABASE TO [username]; 

然后创建新用户登录：

USE dbName; 
CREATE USER [username] FOR LOGIN [username] ;
GO

赋予数据库所有者角色：

EXEC sp_addrolemember 'db_owner', [username]

授予查看数据库的权限（这里有点棘手，因为在 ssms 中它可以查看所有数据库但它只能访问映射的数据库）

USE master GRANT VIEW ANY DATABASE TO [username];

现在在我给定的数据库上，我有 2 个用户，一个是用户名，另一个是 dbo，它映射到创建数据库的用户上，并且与用户名不同。

之后，我使用 SMO 将此数据库从远程服务器传输到本地主机，并在执行时transferDatabase.TransferData();收到以下错误“登录名已经有一个使用不同用户名的帐户。” 当我创建传输实例时，我还设置了属性 CopyAllUsers=true。

我怎样才能解决这个问题？

我发现如果我进行以下更改

USE [master]
GO
CREATE LOGIN [username] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

然后拒绝查看任何数据库：

USE master;
GO
DENY VIEW ANY DATABASE TO [username]; 

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::Dinesh TO [username];
GO

现在用户名只能在 ssms 中看到映射的数据库，并且传输可能会起作用。但是现在我无法区分数据库的第一个创建者和最近授予的用户。对于一个数据库，我应该将 dbowner 权限授予多个用户。

因此，如果我创建另一个登录名并执行：

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::dbName TO [anotherUser];
GO

现在用户名无法访问数据库。

我们的工作涉及更新产品，我们有一个很大的产品表，价格和其他相关信息每小时更新一次。假设它是一家亚马逊商店，我们正在谈论亚马逊产品，我们必须更新销售价格，buy box 价格等。我们每小时从亚马逊提取信息到我们的程序中并更新数据库中的数据。

我的工作流程是，将数据库中的所有产品拉入程序中（我们使用 C# 和 EF Core），更新相关产品，并将更新发送回数据库。

这种方式的代价是从数据库中读取很多信息到程序中，但是我觉得这样很高效，因为 EF Core 有变化检测，所以即使我为所有产品分配了进货产品的价格，如果有is no change EF核心不会改变任何东西，它只会为那些信息发生变化的产品生成更新语句。

此外，它不会生成大的更新语句，它会生成小的、有针对性的更新语句，例如 update products, set BuyBoxPrice = 12.23 where productid = 23345.

我正在和一个非常有天赋的开发人员一起工作，他对 SQL 非常自然，他认为这种方式是错误的，我宁愿将所有传入的信息放在一个名为 #products 的临时表中，并将其发送到数据库中，然后运行一个应该这样做的存储过程，

update products, set BuyBoxPrice = #products.buyboxprice from products inner join #products on products.produtid = #products.productid.

因此，这种方法避免了从数据库中进行大量读取。

我不是那么有经验，我的问题是，读取会创建锁或降低数据库性能，可能是吗？

下面是我对他的方法不满意的原因。

它创建了很多不必要的更新，这在我看来是非常浪费的，因为只有 25% 的信息发生了变化，所以为什么要更新所有列。

我的同事反驳说，我可以通过添加 where 语句来解决这个问题，比如

update products where products.buyboxprice <> #products.buyboxprice

我不认为这会减少你支付的罚款，我认为它仍然是相同的效果。

另一个主要担心是大型更新会创建锁，仅此一项就应该避免。现在我当然可以将更新分解成小于 3000 的块等。

第三点，当SQL肚子疼的时候，它会全身而退，然后开始发生奇怪的事情，客户大喊大叫老板生气，我对发生的事情几乎没有了解，但是在C#中，只要有什么崩溃就对了对我来说很清楚。

所以我的问题是，谁是对的，是通过读取和 EF 核心还是通过 SQL 进行更新的性能更高

我所拥有的是一个SqlFunction产生 3 个双精度数组的 CLR。我希望此函数返回适当的内容，以便FillRowMethod可以将其作为 T-SQL 中的表输出给我。它适用于 1 个阵列，但我无法将其扩展到多个阵列。我主要不确定从我的方法返回什么。下面的一些代码：

[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow",
    TableDefinition = "impliedVol float, maturity float, strike float")]
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(
     string uploadDate, double strike, double yearsForward, double intervalDuration,
     string curve, string surface)
    
//Create 3 arrays of doubles
    double[] array1;
    double[] array2;
    double[] array3;

    return [???];
}
    

public static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
   //impliedVol = (double)obj;  //This is what I do if only returning one array
}

编辑：

根据反馈，这是我尝试的新解决方案。

public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(string uploadDate, double strike, double yearsForward, double intervalDuration, string curve, string surface)
    {
            //omitted code above this line.
        CapletStipping thisCapletStripping = new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
        double[][] theseStrippedCapletVols = thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

         List<capletVolatilityNode> capletVolatilitiesList = new List<capletVolatilityNode>(theseStrippedCapletVols[0].Length);

        for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
        {
            capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode(theseStrippedCapletVols[0][i], theseStrippedCapletVols[1][i], theseStrippedCapletVols[2][i]);
            capletVolatilitiesList[i] = thisCapletVolatilityNode;
        }

        return capletVolatilitiesList; // theseStrippedCapletVols;
    }

    public class capletVolatilityNode
    {
        public double impliedVol;
        public double maturity;
        public double strike;
        public capletVolatilityNode(double impliedVol_, double maturity_, double strike_)
        {
            impliedVol = impliedVol_; 
            maturity = maturity_; 
            strike = strike_;
        }
    }

public static void FillRow(Object obj, out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{

    capletVolatilityNode row = (capletVolatilityNode)obj;

    impliedVol = Convert.ToDouble(row.impliedVol);
    maturity = Convert.ToDouble(row.maturity);
    strike = Convert.ToDouble(row.strike);

}

我对这个主题做了一些简单的研究，我想知道所有的优点和缺点，或者在 SQL Server 中启用/注册这个特定的 .dll 是什么？

返回信息 - 我们正在与第三方应用程序集成（不幸的是，这不是我的决定），其中一些需要此 .dll 其他 .dll。我需要 CLR 函数的目的是能够在 SSMS 中编写 SQL 查询，并将该数据发送到第三方应用程序的 API，然后该 API 将执行正确的数据加载/更改（插入和删除此应用程序有通过其 API 完成）。

编辑 - 也许我应该包括这个细节

在尝试注册我的 c# 类时，我显然得到了错误“system.web not registered blah blah blah”，这反过来又促使我对这个主题进行研究。

结束编辑

所以，我的难题是，为了能够注册我的 C# 类/.dll，我必须注册所有依赖的 .dll，但是根据我的研究，我知道这个特定的可能会很成问题。

所以看到我对谷歌研究之外的陷阱不是很熟悉，我想知道你们中的一个人是否可以帮助我了解如何在这方面做出最好的决定。

另外，我还能在这篇文章中添加什么，以便更容易提供洞察力？我不太确定 C# 代码是否相关？我知道这可能有点宽泛，但我希望它足够具体以至于不会被标记？

更具体地说明这里发生的事情（根据所罗门的要求）

1. 第 3 方应用程序使用“API”（松散地使用，因为我被告知它不是一个很好的 API）来来回发送数据。你会注意到它调用了一个 Importer 函数，该函数只接受一个数据表或它转换的 excel 文件。我没有其他选择，因为该公司告诉我，通过普通 XML 插入和删除非常慢并且有意外行为。

2. 引用 System.Web 的 .DLL 在我的 C# 类中被引用，这是首先能够向其发送数据所必需的。

3. 关于：

   为什么你不能使用我提到的方法？它们已经存在于 SQL Server 的 CLR 主机中。这是一个网络服务，对吧

我不确定我对 API 的了解是否足以回答这个问题。我很可能也只是缺乏这样做的知识和经验。这也可能是因为我对如何与这个特定的 API 交互有限制，我不确定这些限制如何应用于这些方法。（我将进一步调查，看看我是否可以自己回答这个问题）。

虽然我想得越多，我可以创建一个 SQL Server 可以调用的“中间人”类，然后它会调用另一个类，该类将具有所有正确的引用，这可能会让我摆脱目前的情况。但是，我仍然对具体的反馈感兴趣，以便从中学习。

这是我的 C# 类：

    using Perfion.Api;   -- this .DLL references System.Web. 
    using Perfion.Api.Import;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;

    namespace PerfInsert
    {
        public static class PerfionInsert 
        {
            public static bool CreateCommand(string tblString, string featureName, string connectionString, string perfionConnectionString, string logFile)
            {
                StringBuilder logInfo = new StringBuilder();
                try
                {
                    var wList = new Regex(@"[^0-9a-z\.\[\]_]#", RegexOptions.IgnoreCase);
                    if (wList.IsMatch(tblString))
                    {
                        logInfo.AppendLine($"{DateTime.UtcNow} - Regex Validation Failed for Table Name!");
                        return false;
                    }
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        var qryString = "SELECT * FROM " + tblString;
                        using (SqlCommand command = new SqlCommand(qryString, connection))
                        {
                            connection.Open();
                            using (var dataReader = command.ExecuteReader())
                            using (var dataTable = new DataTable())
                            {
                                dataTable.Load(dataReader);
                                PerfionApi api = new PerfionApi(perfionConnectionString);
                                Importer importer = new Importer(api.Connection);
                                importer.Status += (sender, e) => { logInfo.AppendLine($"{DateTime.UtcNow} - {e.Title}"); };
                                importer.LoadData(dataTable);
                                importer.ImportToDatabase(featureName);
                            }
                        }
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    logInfo.AppendLine($"{DateTime.UtcNow} - {ex.ToString()}");
                }
                finally
                {
                    File.AppendAllText(logFile, logInfo.ToString());
                }
                return false;
            }
        }
    }

对于搜索页面，我需要前 10 个结果和查询的总结果数。

那部分很简单：

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT COUNT(*) FROM ... WHERE ...

但它也非常缓慢。两个陈述都需要大约相同的时间。

所以我找到了这个：

SELECT TOP(10) ... FROM ... WHERE ... ORDER BY ...
SELECT @@ROWCOUNT

这将时间缩短了约 50%。伟大的。但不幸的是，返回的行数是 10。如果我删除“TOP(10)”行数是正确的，但时间跨度与原始版本中的时间跨度或多或少相同。（即使我只访问前 10 行。）

这令人沮丧，因为总行数信息必须存在，即使在 TOP(10) 版本中也是如此。（至少我是这么认为的，因为我们使用了一个非常复杂的 ORDER BY 子句，用 3 个“CASE”条件将空数据排到底）

有没有一种有效的方法来获得两者？TOP(10) 数据和总数？

我正在尝试（学习）使用 Visual Studio 2017 发布 SQLCLR 函数。（这是一个发送电子邮件的简单函数。）

作为参考，我在CodeProject和MSSQLTips上使用了这篇文章：

使用 Visual Studio 2013 数据库项目创建、运行、调试和部署 SQL CLR 函数

使用 CLR 存储过程从 SQL Server Express 发送电子邮件

在项目属性-> SQLCLR 我已经设置：

Permission level : UNSAFE

根据文章，我更新了目标数据库：

sp_configure 'clr enabled', 1;  
GO  
RECONFIGURE;  
GO  

在我得到错误之后，我在 MSSQLTips 文章中遵循了这个建议：

如果在尝试编译代码时收到错误消息，您可能需要使用以下命令更改数据库，然后再次尝试创建程序集和存储过程。

ALTER DATABASE msdb SET trustworthy ON

我尝试过使用两个不同的目标数据库：

• SQL-Server 2017 本地数据库
• SQL-Server 2017 速成版

我可以毫无错误地构建项目，但是当我发布项目时，我在执行下一个命令时收到一个错误：

CREATE ASSEMBLY [dbSysmac]
    AUTHORIZATION [dbo]
    FROM 0x5F8A900003000000...
    WITH PERMISSION_SET = UNSAFE;

(47,1)：SQL72014：.Net SqlClient 数据提供程序：消息 10327，级别 14，状态 1，第 1 行为程序集“dbSysmac”创建程序集失败，因为程序集“dbSysmac”不受信任。当以下任一情况为真时，程序集是受信任的：程序集使用证书或非对称密钥签名，该密钥具有相应的登录名并具有 UNSAFE ASSEMBLY 权限，或者程序集使用 sp_add_trusted_assembly 受信任。

我做错了什么？

我想通过从从服务器查询来检查我的主服务器是否已启动？有任何视图或命令吗？

我遇到过一个相当复杂的系统，其中业务逻辑是用SSIS编写的。由于SSIS无法执行此逻辑的大部分，因此它严重依赖C# 脚本任务来执行操作。

举一个需要C# 脚本任务的例子，应用程序需要对第三方应用程序进行 API 调用以获取数据，还需要第三方库来对数据执行一些计算。

调用C# 脚本任务使开发和调试变得更加困难……但同时，SSIS允许程序员使用C# 脚本任务。

• SSIS有哪些应用？

• 使用SSIS + C# 脚本任务实现复杂逻辑有什么好处吗？如果我们需要使用C# 脚本，那么为什么不将整个逻辑用C#编写呢？