Question
I have 2 tables which have one to one relationship.The tables are as follows:
CREATE TABLE [dbo].[Provider](
[ProviderId] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FirstName] [varchar](40) NOT NULL,
[LastName] [varchar](40) NOT NULL,
[SSN] [varchar](15) NOT NULL,
[NPI] [varchar](15) NOT NULL,
[ProviderStatus] [bit] NOT NULL)
CREATE TABLE [dbo].[ProviderDetails](
[ProviderDetailsID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Certification] [varchar](40) NOT NULL,
[Specialization] [varchar](40) NOT NULL,
[TaxonomyCode] [varchar](40) NOT NULL,
[ContactNumber] [varchar](15) NOT NULL,
[ContactEmail] [varchar](40) NOT NULL,
[ProviderId] [int] FOREIGN KEY REFERENCES Provider(ProviderId) NOT NULL)
I have created 2 entities in C# (Mind you, I am not using EF)
public class Provider
{
public int ProviderID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string SSN { get; set; }
public string NPI { get; set; }
public ProviderDetails ProviderDetails { get; set; }
}
public class ProviderDetails
{
public int ProviderDetailsId { get; set; }
public string Certification { get; set; }
public string Specialization { get; set; }
public string TaxonomyCode { get; set; }
public string ContactNumber { get; set; }
public string ContactEmail { get; set; }
public int ProviderId { get; set; }
}
I, earlier had a single entity, Provider. So, I was able to get the list of providers to display with the following code.
public List<Provider> GetListofProviders()
{
List<Provider> Providers = new List<Provider>();
using (_dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DemoApplicationConnection"].ConnectionString))
{
try
{
Providers = (List<Provider>)(_dbConnection.Query<Provider>("spGetAllProviders", commandType: CommandType.StoredProcedure));
}
catch (Exception)
{
throw;
}
}
return Providers;
}
To get data from the database, I am using the following query:
ALTER PROC [dbo].[spGetAllProviders]
AS
SELECT * FROM Provider
INNER JOIN ProviderDetails
ON Provider.ProviderId = ProviderDetails .ProviderId
WHERE Provider.ProviderStatus = 1
Since I have split the entities, could someone please guide me on how I would map the data from the database to the 2 entities?
Popular Answer
Now this is not the right answer.But I think this would be the way to go about.Although its not working for me.So,if someone could tell me what I am doing wrong,would be really helpful.
public List<Provider> GetListofProviders()
{
List<Provider> Providers = new List<Provider>();
using (_dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DemoApplicationConnection"].ConnectionString))
{
try
{
Providers = _dbConnection.Query<Provider,ProviderDetails,Provider>("spGetAllProviders",null,splitOn: "ProviderId", commandType: CommandType.StoredProcedure).ToList();
}
catch (Exception)
{
throw;
}
}
return Providers;
}
Also the stored procedure is
ALTER PROC [dbo].[spGetAllProviders]
AS
SELECT Provider.*,
ProviderDetails.ProviderId,
ProviderDetails.ProviderDetailsID,
ProviderDetails.Certification,
ProviderDetails.Specialization,
ProviderDetails.TaxonomyCode,
ProviderDetails.ContactNumber,
ProviderDetails.ContactEmail
FROM Provider
INNER JOIN ProviderDetails
ON Provider.ProviderId = ProviderDetails .ProviderId
WHERE Provider.ProviderStatus = 1
I am getting the following error.
When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id