CODEWITHSUNDEEP
c#sql-serverasp.net-mvcdapper
user1604093
user1604093

Reputation: 89

How to read data from SQL server database table that is not in the default DBO schema

I am building a .NET MVC 4 app with SQL server as my database and I use Dapper and Dapper SimpleCRUD for database operations. In my database, I have tables in the default dbo schema and I also have tables in other schemas, like product.clients instead of dbo.clients. I have created a separate login on the SQL server that maps to a separate user account (who is the owner of the product schema and default to the product schema) on the database. But connecting to the DB using this credential gives me exceptions like System.Data.SqlClient.SqlException: Invalid object name 'clients'. Now, I have searched and searched but could not find how to construct the correct connection string element in web.config so that I can read data from schema other than the default dbo. How do I do this?

Upvotes: 1

Views: 1096

Answers (2)

user1604093
user1604093

Reputation: 89

Ok, the solution was simple but not obvious unless you know where to look. Maybe my google-fu just isn't very good.

The answer is that you can define schema in the Table attribute annotation.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;

namespace MyApp.Models
{
    [Table("Clients", Schema="Products")]
    public class Client
    {
        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
    }
}

This will work for Dapper and Dapper SimpleCRUD to find the correct table and read data from there.

https://msdn.microsoft.com/en-us/library/system.data.linq.mapping.tableattribute(v=vs.110).aspx

Upvotes: 1

Shyju
Shyju

Reputation: 218852

You may explicitly specify the schema name in your query

SELECT Id,ClientName FROM product.clients

As long as the sql user has access to the schema, it should work.

You can join tables from 2 schamas as well as needed.

 SELECT C.Id,C.ClientName FROM product.clients C WITH (NOLOCK) 
 INNER JOIN dbo.UserAccount U WITH(NOLOCK) 
                         ON U.UserId=C.CreatedById

Upvotes: 3

Related Questions