问题[sql-server]

有什么问题？程序执行不会产生任何错误，但是当尝试查看加载数据的表时，它只是空的，只有列本身。可能存在什么问题或者程序本身是否需要其他重新编码方法？是的，在将数据加载到表中之前，必须在过程本身中将编码从 utf 8 重新编码为 utf 16。

ALTER PROCEDURE [dbo].[LoadStoresFromXML]
    @XMLFilePath NVARCHAR(MAX) = 'D:\jobLasmart\virtualpos\stores.xml' -- Укажите путь к файлу
AS
BEGIN
    SET NOCOUNT ON;

    -- Переменные для работы с файлами
    DECLARE @XMLData NVARCHAR(MAX);
    DECLARE @FileHandle INT;
    DECLARE @TextStream INT;
    DECLARE @UTF16Data NVARCHAR(MAX);

    -- Читаем содержимое файла в переменную
    EXEC sp_OACreate 'Scripting.FileSystemObject', @FileHandle OUTPUT;
    EXEC sp_OAMethod @FileHandle, 'OpenTextFile', @TextStream OUTPUT, @XMLFilePath, 1, 0; -- 1 = ForReading, 0 = ASCII
    EXEC sp_OAMethod @TextStream, 'ReadAll', @XMLData OUTPUT; -- Читаем весь файл
    EXEC sp_OAMethod @TextStream, 'Close', NULL;
    EXEC sp_OADestroy @TextStream;
    EXEC sp_OADestroy @FileHandle;

    -- Преобразовываем данные в кодировку UTF-16
    -- Datalength увеличивается в 2 раза, так как в UTF-16 каждый символ занимает 2 байта
    SET @UTF16Data = CONVERT(NVARCHAR(MAX), @XMLData COLLATE Latin1_General_BIN);
    
    -- Вставка данных из XML в таблицу Stores
    DECLARE @XML XML = CAST(@UTF16Data AS XML);

    INSERT INTO dbo.Stores (id, open_time, close_time, name, address, phone, headquerter_id, created_date, created_by,
                            last_update_date, last_update_by, flag24hours, lat, lon, minusale, location_id,
                            external_id, show_in_shop, organisation_id, vat_mandatory, manager_user_id, [primary])
    SELECT 
        Store.value('(id)[1]', 'int') AS id,
        Store.value('(open_time)[1]', 'time(7)') AS open_time,
        Store.value('(close_time)[1]', 'time(7)') AS close_time,
        Store.value('(name)[1]', 'nvarchar(255)') AS name,
        Store.value('(address)[1]', 'nvarchar(255)') AS address,
        Store.value('(phone)[1]', 'nvarchar(50)') AS phone,
        Store.value('(headquerter_id)[1]', 'int') AS headquerter_id,
        Store.value('(created_date)[1]', 'datetime') AS created_date,
        Store.value('(created_by)[1]', 'int') AS created_by,
        Store.value('(last_update_date)[1]', 'datetime') AS last_update_date,
        Store.value('(last_update_by)[1]', 'int') AS last_update_by,
        Store.value('(flag24hours)[1]', 'bit') AS flag24hours,
        Store.value('(lat)[1]', 'decimal(9, 6)') AS lat,
        Store.value('(lon)[1]', 'decimal(9, 6)') AS lon,
        Store.value('(minusale)[1]', 'int') AS minusale,
        Store.value('(location_id)[1]', 'int') AS location_id,
        Store.value('(external_id)[1]', 'int') AS external_id,
        Store.value('(show_in_shop)[1]', 'bit') AS show_in_shop,
        Store.value('(organisation_id)[1]', 'int') AS organisation_id,
        Store.value('(vat_mandatory)[1]', 'bit') AS vat_mandatory,
        Store.value('(manager_user_id)[1]', 'int') AS manager_user_id,
        Store.value('(primary)[1]', 'bit') AS [primary]
    FROM @XML.nodes('/Stores/Store') AS Store(Store);
END;```

我不明白为什么数据会被覆盖。在第一个示例中，我使用了三个记录，例如我找到了三个员工。

在游标中，我在另外 2 个表中进行查询，检查课程是否位于包含已完成课程的表中和包含活动课程的表中，并将其写入变量。如果员工既没有完成也没有参加课程，PRINT 中的记录会出于某种原因消失吗？

create table personTest ( 
    id bigint primary key ,
    name varchar(MAX) not null,
);

insert into personTest values (1, 'Anna');
insert into personTest values (2, 'Andrey');
insert into personTest values (3, 'Kate');

create table activeCource ( 
    id bigint primary key ,
    name varchar(MAX) not null,
    person_id bigint
);

insert into activeCource values (1, 'Cource ololo', 1);
insert into activeCource values (2, 'Cource trololo', 2);


create table passedCource ( 
    id bigint primary key ,
    name varchar(MAX) not null,
    person_id bigint
);

insert into passedCource values (1, 'Cource ololo', 1);
insert into passedCource values (2, 'Cource trololo', 3);

DECLARE  @current_person BIGINT
        ,@person_fullname VARCHAR(MAX)
        ,@course_result VARCHAR(20) = 'Данных нет'

DECLARE @cursor CURSOR

SET @cursor = CURSOR SCROLL FOR
    SELECT 
         cls.id
        ,cls.name
    FROM personTest AS cls

OPEN @cursor

FETCH NEXT FROM @cursor INTO @current_person, @person_fullname
WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @course_result = (
        SELECT 
            CASE 
                WHEN ISNULL(pls.id, 0) != 0 THEN 'Курс назначен'
                ELSE 'Данных нет'
            END AS is_exists
        FROM activeCource AS pls
        WHERE pls.person_id = @current_person
    ) --например у одного сотрудника (Kate) тут ни чего нет.

    SELECT @course_result = (
        SELECT 
            CASE 
                WHEN ISNULL(pls.id, 0) != 0 THEN 'Курс пройден'
                ELSE 'Данных нет'
            END AS is_exists
        FROM passedCource AS pls
        WHERE pls.person_id = @current_person
    ) 

    PRINT @person_fullname + ' ' + @course_result
    -- Почему то принт выводит только двух сотрудников, а не трех. Понять не могу из за чего. Любой цикл в том же Swift выводит все.
    FETCH NEXT FROM @cursor INTO @current_person, @person_fullname
END

CLOSE @cursor
DEALLOCATE @cursor

我找到了很多有关 varchar 类型最大大小的信息，但没有找到任何提及西里尔字母的信息。

从mssql 2000规范中可以清楚地看出，varchar类型可以容纳8000个字节，相当于8000个ASCII表字符。但是 ASCII 中没有西里尔字母，然而，我的实验表明 varchar(8000) 完全适合并返回 8000 个字符，包括西里尔字母。

这是如何运作的？

我决定使用ASP.NET Core编写一个简单的应用程序；EFCore；码头工人；sql服务器。我使用了 docker 数据库映像并将其用于 docker 组合文件，还编写了自己的 docker 映像来构建 ASP.NET Core 应用程序。当我开始运行该项目时docker-compose up --build，一切都开始顺利，但是 ASP.NET Core 应用程序抛出错误，因为它无法连接到数据库，因为它没有找到这样的服务器。我显然不知道服务器名称，并尝试了我在互联网上找到的服务器名称。有db、localhost和localhost,1433。但他仍然找不到服务器。请帮帮我。

项目结构：

Dockerfile：

FROM mcr.microsoft.com/dotnet/sdk:8.0 AS sdk
WORKDIR /src
COPY MyWebApp.sln .
COPY MyWebApp.Web/MyWebApp.Web.csproj ./MyWebApp.Web/
COPY MyWebApp.DAL/MyWebApp.DAL.csproj ./MyWebApp.DAL/
RUN dotnet restore

COPY MyWebApp.Web/. ./MyWebApp.Web/
COPY MyWebApp.DAL/. ./MyWebApp.DAL/ 
WORKDIR /src/MyWebApp.Web
RUN dotnet publish -c Release -o ./app --no-restore
WORKDIR /src/MyWebApp.DAL
RUN dotnet publish -c Release -o ./app --no-restore

FROM sdk as publish
WORKDIR /src

FROM mcr.microsoft.com/dotnet/aspnet:8.0
WORKDIR /app
EXPOSE 8080
ENV ASPNETCORE_HTTP_PORTS=8080
COPY --from=publish /src/MyWebApp.Web/app ./
COPY --from=publish /src/MyWebApp.DAL/app ./
ENTRYPOINT [ "dotnet", "MyWebApp.Web.dll" ]

Program.cs从项目API连接：

builder.Services.AddDbContext<AppDbContext>(opt =>
{
    Thread.Sleep(15000);
    opt.UseSqlServer("Server=localhost; Database=MyDatabase; User Id=sa; Password=saroot1234A$; TrustServerCertificate=true");
});

错误本身：

未处理的异常。Microsoft.Data.SqlClient.SqlException (0x80131904): 建立与 SQL Server 的连接时发生与网络相关或特定于实例的错误。服务器未找到或无法访问。

如果您需要详细信息，请写入和/或存储库

您需要显示在某个时间段内销售的产品列表（SaleDate 使用日期时间数据类型），但可能没有第二个日期，在这种情况下您只需要显示当天的产品列表参数中指定的。就我而言，如果输入一个参数，则sql查询不会返回任何内容，但如果指定两个参数，则所有内容都会成功返回。我不太明白为什么会发生这种情况。代码：

@FirstDate date,
@SecondDate date = NULL
as
if (@SecondDate IS NULL)
select Product.Title, Manufacturer.[Name], ProductSale.SaleDate, (Product.Price * ProductSale.Quantity) AS 'Total Earn' from
ProductSale join Product on Product.ID = ProductSale.ProductID join Manufacturer on Manufacturer.ID = Product.ManufacturerID
where SaleDate = @FirstDate
else if (select count(*) from ProductSale where SaleDate between @FirstDate and @SecondDate) != 0
select Product.Title, Manufacturer.[Name], ProductSale.SaleDate, (Product.Price * ProductSale.Quantity) AS 'Total Earn' from
ProductSale join Product on Product.ID = ProductSale.ProductID join Manufacturer on Manufacturer.ID = Product.ManufacturerID
where SaleDate between @FirstDate and @SecondDate
else if (select count(*) from ProductSale where SaleDate between @FirstDate and @SecondDate) = 0
print 'Товаров не найдено!' 

exec PR_2DATES '16.03.2024'



| Title | Name | SaleDate | TotalEarn |