Блог

Я использую Dapper для подключения к базе данных SQL server

Моей базой данных была SeasonDB, я создал базу данных anther с именем SeasonDb2 для тестирования изменений

он работал хорошо, пока я не удалил SeasonDb2 с SQL server и не изменил строку подключения на SeasonDB

получить строку подключения из файла конфигурации

   <connectionStrings>

<add name="SQLServer_Connection" connectionString="data source=.;integrated security=SSPI;initial catalog=SeasonDB"/>
 

при запуске приложения отображается это сообщение об ошибке

 System.Data.DataException
  HResult=0x80131501
  Message=Error parsing column 1 (DegreeID=1 - Int32)
  Source=Dapper
  StackTrace:
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in /_/Dapper/SqlMapper.cs:line 3665
   at Dapper.SqlMapper.<QueryImpl>d__140`1.MoveNext() in /_/Dapper/SqlMapper.cs:line 1102
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 725
   at Season_Models.DataAccess.SQLServerConnector.UsersSearch2(String searchValue) in C:UsersMohamedSaqrSourceReposPrintAndPublishBranchSeasonAppSeason ModelsDataAccessSQLServerConnector.cs:line 221
   at Season_Models.GlobalData.GlobalData.get_Users() in C:UsersMohamedSaqrSourceReposPrintAndPublishBranchSeasonAppSeason ModelsGlobalDataGlobalData.cs:line 149
   at Season_Application.UI.Login.UserLogin() in C:UsersMohamedSaqrSourceReposPrintAndPublishBranchSeasonAppSeason ApplicationUILogin.cs:line 100
   at Season_Application.UI.Login.SaveOnEnterKeyPressed(Object sender, KeyPressEventArgs e) in C:UsersMohamedSaqrSourceReposPrintAndPublishBranchSeasonAppSeason ApplicationUILogin.cs:line 170
   at System.Windows.Forms.Control.OnKeyPress(KeyPressEventArgs e)
   at System.Windows.Forms.Control.ProcessKeyEventArgs(Messageamp; m)
   at System.Windows.Forms.Control.WmKeyChar(Messageamp; m)
   at System.Windows.Forms.Control.WndProc(Messageamp; m)
   at System.Windows.Forms.TextBox.WndProc(Messageamp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSGamp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at Season_Application.Program.Main() in C:UsersMohamedSaqrSourceReposPrintAndPublishBranchSeasonAppSeason ApplicationProgram.cs:line 36
Inner Exception 1:
SqlException: Invalid object name 'SeasonDB2.dbo.TableLastModificationTime'.
 

Выполняемый код

  public List<UserModel> UsersSearch2(string searchValue)
    {
        List<UserModel> users;
        using (IDbConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServer_Connection"].ToString()))
        {
            var p = new DynamicParameters();
            p.Add("@SearchValue", searchValue);

            users =
                connection.Query<UserModel>("SPGetUsers2", p, commandType: CommandType.StoredProcedure).ToList();
            foreach (var user in users)
                user.UserDegree = GlobalData.GlobalData.Degrees
                    .Where(degree => degree.DegreeID == user.DegreeID).ToList()[0];
        }

        return users;
    }
 

ошибка отображается здесь

                 users =
                connection.Query<UserModel>("SPGetUsers2", p, commandType: CommandType.StoredProcedure).ToList();
 

проблема в том, что dapper подключается к SeasonDb2

Исключение SQLException: недопустимое имя объекта ‘SeasonDB2.dbo.TableLastModificationTime’.

Моя хранимая процедура использует SeasonDB

 USE [SeasonDB]
GO
/****** Object:  StoredProcedure [dbo].[SPTableLastModificationTime]    
Script Date: 25/11/2020 02:43:32 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: 
SQLQuery1.sql|7|0|C:UsersNavy5AppDataLocalTemp~vs5FCC.sql

ALTER PROCEDURE [dbo].[SPTableLastModificationTime]
-- Add the parameters for the stored procedure here
@TableName sysname,
@TableLastModificationTime DateTime output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
Select  @TableLastModificationTime =  [TableLastModiticationTime]
    FROM [SeasonDB2].[dbo].[TableLastModificationTime]
    Where TableName = @TableName

    if @TableLastModificationTime is null
    Begin
         select @TableLastModificationTime = 
        ius.last_user_update
        FROM
        sys.dm_db_index_usage_stats ius INNER JOIN
        sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
        WHERE ius.database_id = DB_ID() and tbl.name  = @TableName


        if @TableLastModificationTime is null
        Begin
            select @TableLastModificationTime = GETDATE()
        END


        INSERT INTO [dbo].[TableLastModificationTime]
        ([TableName],TableLastModiticationTime)
        VALUES
        (@TableName,@TableLastModificationTime)


    END
    Else
    Begin
        Declare @TempTime DATETIME


        SELECT @TempTime =  ius.last_user_update
        FROM
        sys.dm_db_index_usage_stats ius INNER JOIN
        sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
        WHERE ius.database_id = DB_ID() and tbl.name  = @TableName

        IF @TempTime is not null AND @TempTime <> @TableLastModificationTime
        BEGIN
            SET @TableLastModificationTime = @TempTime

            UPDATE [dbo].[TableLastModificationTime]
            SET 
              [TableLastModiticationTime] = @TableLastModificationTime,
              EditLastTime = GetDate()
            WHERE [TableName] = @TableName
        END

        


    END
        
END
 

Заранее спасибо

Ваша проблема заключается в хранимой процедуре. Точнее, здесь:

 Select  @TableLastModificationTime =  [TableLastModiticationTime]
    FROM [SeasonDB2].[dbo].[TableLastModificationTime] ...
 

SeasonDB2 жестко запрограммирован в запросе.

Вам необходимо изменить свой SP и удалить его, поскольку до тех пор, пока хранимая процедура находится в правильной базе данных, этот префикс не требуется