How to map an Entity framework model to a table name dynamically

I am working in MVC 3 with entity frame work so I am using List, create, edit and details page by razor templates of csHTML5.

But I have found one problem while creating a templates from my company model which Company table available in my existing database which I cannot change.

So all related controller and View has been created along with context, but Problem is comes that created entity framework structure is expected tables “Companies” but in my case I have table with company. Below is the code for was exists.

public class CompanyServices : DbContext
{
public CompanyServices()
{
this.Database.Connection.ConnectionString = ServiceBase.DataConnectionString;
}

public DbSet Company { get; set; }
//public DbSet Players { get; set; }

}

I have got below error by executing this code.
________________________________________
Invalid object name ‘dbo.Companies’.
Error Source
System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +443

To resolve this error I found in Entity framework we have automatic conversion is available and based on that that will convert company entity to map with tables Companies.

So We need to modify above class with overriding this methods. So that will remove that conversion functionality so Entity framework will point to the exact tables.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove();
}

Below is completed update classs.

public class CompanyServices : DbContext
{
public CompanyServices()
{
this.Database.Connection.ConnectionString = ServiceBase.DataConnectionString;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove();

}
public DbSet Company { get; set; }

public void InActive(Team oTeam)
{
this.Entry(oTeam).State = System.Data.EntityState.Modified;
this.Database.ExecuteSqlCommand("EXEC InActiveTeam {0}, {1}", oTeam.TeamId, oTeam.isActive);

}
}

Note : Below namespace required to import to run this code.
using System.Data.Entity.ModelConfiguration.Conventions;

Thanks,
Amit Patel
“Happy Programming”

About these ads

Software Development Life Cycle

Below are the Stages of Software Development life cycle to make process smooth.

SDLC

Software Development Life Cycle

  1. 1. Project Plan
    1. Roles
      1. Client Team
      2.  Business Developer Team
      3.  Project manager
  • Check Project feasibility with company’s capabilities
  • Define high level requirement and stack holders goal
  • Define Business  problems and goals
  • Access Current Capabilities
  • Explore Solution approach
  • Prepare Statement of Work with estimation
  • Agreement sign with clients based on SOW and given estimation
  1. 2 . Analysis Phase
    1. Role
      1.  Client Team
      2. Business Developer Team
      3. Project Manager
      4. Architecture and Development Team
      5. QA Team
  • Analysis business process
  • Create and analyze product requirement and product quality-related matrices
  • Create Non functional prototype /Functional specification document/ Use cases / Activity diagrams
  • Create test plans and QA on all deliverables (NFP, documents)
  1. 3. Design Phase
    1. Role
      1. Development Team
  • Database Design
  • Application Architecture design
  • Adapt the design to address quality requirements and technical constraints, designing for performance.
  1. 4. Build Phase (Development)
    1. Role
      1.   Development Team
  • Based on application design and analysis develop application’s Modules
  • Integrate all the individually developed and tested components in an executable application.
  • Perform unit testing.
  1. 5. Test Phase
    1. Role
      1. QA Team
      2.  Development Team
  • Perform and executed assembly testing
  • Perform and execute product testing
  • Perform and execute performance testing
  • Application sent back to development team with test reports in bug tracker
  • Perform regression testing based on developers’ changes.
  • Perform manual and automation testing.

1.6. Deployment phase

  1. Role
    1.  Client
    2.  Architecture and development team
  • Prepare production and staging environment
  • Enable Stack holder to use or support to new application
  • Create access permission to stack holder group
  • Perform QA on new deployed application.
  1. 7. UAT
    1. Role
      1. Stack holders (Client)
      2. Development team
      3.  QA Team
      4.   Project manager
  • Client will perform Testing
  • Perform testing to check application meets on requirement specification or not.
  • If any bugs found then developer and QA team need to support on that.

1.8. Maintenance and Support

  1. Role
    1.  Stack holders (Client)
    2.  Development team
    3.   QA team
  • If Client requires supports then need to provide support based on contract agreement.

Maintaining a Log of Database Changes

I have prepared a common structure for maintain database history will all data changes. So that will be used full for any application with maintain transaction log for set of tables or all database tables.

In Financial, B2B, or any of the commercial application we need to maintain change history log that is help full to data maintain for auditing, tracking of records, fix any data related bugs.

I have prepared a structure so within that we can get the data only changed column so if user has updated only 2 columns then we are storing data in our table for those 2 column only. So we can get the history of that particular transaction about what value has been changed.

As per blow example.
For customer table below are the change log, with updated data and updated by data.

Column Name Previous Value Updated Value
Birth Date 6th Oct 2000 9th Oct 1990
City New Your AmsterDeam
Country USA Nill
Name Amit Amit
LastNAme Patel Patel
     

 

So here above three column value has been changed.

So we have to preserver all changed value in our history table with old and new value with each entity, so I have created one common structure for that and create one common Store procedure that need to execute after each insert, edit operation, however delete case we don’t need to manage anything.

So this data we can use as tracing application,

For use this structure we need to require below tables

Table 1 : Operation

Operation LogData  
OperationID 1  
Tablename CustomerDetails  
EntityID 1  
Action  Date 10/10/2010  
Action  By 12  

 

Table 2 : TransactionLog

Operation LogData  
LogId 1  
OperationID 1  
ColumnName BirthDate  
PreviosValue 10/10/2001  
NewValue 05/11/2001  

 

Tables for Log

Below are the SP for Insert data:

– ============================================= 

– Author:  Amit Patel 

– Create date: 11th Apr 2011 

– Description: Insert log details 

/* 

 @TableName — Affected Table

 @UserID – Actioned User

 @ActionID — Action Add/update , 

 @EntityID – Relvant Tables entity ID, Primary key, 

 @EntityColumnName – Column Name

for action 

1-  Add 

2 – Update 

*/ 

– ============================================= 

CREATE PROCEDURE [dbo].[InsertLogHistroy]  

 – Add the parameters for the stored procedure here 

 @TableName nvarchar(100),  

 @UserID Bigint, 

 @ActionID Smallint, 

 @EntityID Bigint, 

 @EntityColumnName nVARCHAR(100) = ‘ID’ 

AS 

BEGIN 

 – SET NOCOUNT ON added to prevent extra result sets from 

 – interfering with SELECT statements. 

 SET NOCOUNT ON; 

    —- Insert data in Operation table— 

    INSERT INTO Operation Values(@TableName,@EntityID,@ActionID,GETDATE(),@UserID) 

    DECLARe @OperationID bigint 

 set @OperationID = SCOPE_IDENTITY(); 

    —— End Operation Data insert process——— 

    if(ISNULL(@OperationID,0) > 0) 

    BEGIN 

  DECLARE @ColumnName varchar(100) 

     create TABLE #tempdata (ColumnValue nvarchar(250)) 

  DECLARE columnList CURSOR FOR select Name from sys.all_columns where object_id in (select object_id from sys.all_objects where object_id = object_id(@TableName)) and max_length<250 

   OPEN columnList; 

   FETCH NEXT FROM columnList INTO @ColumnName    

   WHILE @@FETCH_STATUS = 0    

   BEGIN    

   Declare @NewColumnValue nvarchar(250) 

   Declare @PreColumnValue nvarchar(250) 

   set @NewColumnValue=”; 

   set @PreColumnValue=”; 

   delete from #tempdata 

   declare @sqlQuery nvarchar(250) 

   set @sqlQuery = ‘SELECT CAST(‘ + @ColumnName + ‘ as nvarchar(250)) as ColumnValue FROM ‘ + @TableName + ‘ WHERE ‘ + @EntityColumnName + ‘=’ + cast(@EntityID as varchar(10)) 

   print @sqlQuery 

   INSERT INTO #tempdata 

   EXEC(@sqlQuery) 

   SELECT @NewColumnValue = ColumnValue from #tempdata 

   set @NewColumnValue = ltrim(rtrim(ISNULL(@NewColumnValue,”))); 

   SELECT Top 1 @PreColumnValue = NewValue from dbo.TransactionLog where OperationID  in (SELECT  ID from Operation where TableName=@TableName and EnityID=@EntityID and ID < @OperationID) and ColumnName = @ColumnName order by ID desc 

   set @PreColumnValue = ltrim(rtrim(ISNULL(@PreColumnValue,”))); 

   print @ColumnName 

   print @PreColumnValue 

   print @NewColumnValue 

   if(@PreColumnValue <> @NewColumnValue) 

   BEGIN 

    INSERT INTO TransactionLog VALUES(@OperationID,@ColumnName,@PreColumnValue,@NewColumnValue) 

   END 

   ELSE 

   begin 

   print ‘no update’ 

   end 

   FETCH NEXT FROM columnList INTO @ColumnName    

   END    

  CLOSE columnList    

  DEALLOCATE columnList  

  DROP Table #tempdata; 

 END 

END 

So We need to execute this store procedure after each operation so this will check each column previous value and new value and if found any difference then store this value.

Ex.

We are updating operation on “client” table with clientID = 25 and this operation is doing by user ID 7 then

exec [InsertLogHistroy] ‘Client’,7, 2, 25, ‘ClientID’

So, based on this we can maintain Log history with each column data. So any of the financial or any tracking system this logic is very use full where we can easily tracking where this update affected by which user and when.

Let me know if you have questions on this.

Thanks,

Amit Patel

“Happy Programming”

Follow

Get every new post delivered to your Inbox.

Join 238 other followers