Repodb: Bug: Table schema is ignored for the identical table names

[sellig]

Dear Michael,

Found a Bug related to table schema and the way, I think you retrieve and cache SQL Table information.

Let's say you have a table
[directory].[Companies] with a Column [Name] maps to an Object Company with a Property Name

and another table (the schema is different)
[survey].[Companies] with also a Column [Name] maps to an Object SurveyCompany with a Property Name

If you an insert of a new SurveyCompany, you get the error

"The variable name '@Name' has already been declared. Variable names must be unique within a query batch or stored procedure."

If the second column [survey].[Companies].[Name] is renamed to [CompanyName], all is fine.

Best

Gilles

[mikependon]

@sellig - it seems that this issue is found already by @fake-fur from here #432 in relation to MySQL. Specifically on this comment.

I would assume you are using the PostgreSQL. Can you place the trace log here? Thanks!

[sellig]

I'm using SQL Server

[mikependon]

Thinking that this is already fix in SQL Server. The code is here. Anyway, can you give me which operation did you fail so I can look at it later when I get home? Also, when do you need this to be deployed? I am looking towards the criticality on your side. Thanks

[sellig]

Ok, I will update to the last version of RepoDb and let you know, I'm still on RepoDb.SqlServer 1.07

[mikependon]

@sellig - I do not think there are major changes pertaining to RepoDb.SqlServer TBH. I would assume your issue may still occur there. So a specific information where you encountered it is a good entry point for me to fix it ASAP. Of course, it would be great if that has been fixed in the latest release without me (or us) tracking it.

[sellig]

Seems still here will the last version of RepDb

Exemple of Exception with the column Website that is defined in the 2 tables with the same name.

System.Data.SqlClient.SqlException (0x80131904): The variable name '@Website' has already been declared. Variable names must be unique within a query batch or stored procedure.

[sellig]

Also, If I updated to the latest version of RepoDb, I have regression issue on another part of my Application, currently, using 1.11 et 1.0.7 for SqlServer
But I will open another ticket.

[mikependon]

I will do replicate this one later. When do you need the fix (in your end)?

[sellig]

Thank you Michael, for now, I have renamed my table column to avoid double.
Is it feasible for the end of the month ?

[mikependon]

@sellig - I will revert back to you with a fix probably this coming weekend. Thanks for this report.

[mikependon]

@sellig - can you also report the regression issue in a separate User Story?

[sellig]

Yes, I will, I must extract the case from my code.

[mikependon]

As mentioned earlier, the issue is located at the GetCommandText method of the SQL Server DB Helper class. It is tested with the following code.

Databases:

USE [TestDB]
GO

CREATE TABLE [survey].[Companies](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL
) ON [PRIMARY]

CREATE TABLE [directory].[Companies](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](256) NOT NULL
) ON [PRIMARY]

Classes:

public class DirectoryCompany
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class SurveyCompany
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Method Call:

// Initialize
SqlServerBootstrap.Initialize();

// Models
FluentMapper
    .Entity<DirectoryCompany>()
    .Table("[directory].[Companies]");
FluentMapper
    .Entity<SurveyCompany>()
    .Table("[survey].[Companies]");

// Calls
using (var connection = new SqlConnection("Server=.;Database=TestDB;Integrated Security=SSPI;"))
{
    var dbFields = DbFieldCache.Get(connection, ClassMappedNameCache.Get<DirectoryCompany>(), null);
}

The code above returns 4 fields instead of 2. A fix will be delivered soon.

[mikependon]

I just jump directly to a beta release and did not delivered any Unit Test for this. Never I added Integration Tests as well (for now). Thinking to the fact that this does not need it as of the moment.

Anyway, the fix is deployed at RepoDb.SqlServer (v1.0.11-beta1).

[sellig]

Thank you Michael.
I will test it this weekend and open at the same time the other regression issue.

Best

[mikependon]

No problem, please do let me know if that works so I can promote it together with other issue you're going to report soon. Thanks for reporting this.

[mikependon]

Btw, if you are releasing your product prior to the actual non-beta release, please do this.

1. Copy the entire SqlServerDbHelper class in your project and rename it to your some like SqlServerDbHelper.
2. Register it using the code below.

DbHelperMapper.Add<SqlConnection>(new <Custom>SqlServerDbHelper(), true);

Note: This is a part of the library extensibility. You just have to call it once. The library will then use your DB Helper instead of the default SqlServerDbHelper class.

Otherwise, you just have to wait for the next actual release (non-beta). I am planning and waiting for any other bug reports (or community requests) before doing so.

[mikependon]

@sellig - hey man, how is the status of this issue by now in your end?

[sellig]

You can close it. it's corrected.
Thank you.