Question

• Create an application programming interface (API) using stored procedures that allows an EMR system developer to work with the database without having to write SQL statements
• Add user authentication so that only authorized users can access the database
• Add some form of role-based access control to limit the functions that a user can perform. You should implement at least one use case to demonstrate this capability. For example, you may allow a physician to delete a prescription order, but not a receptionist. In this case, you have two roles, physician and receptionist.
• Add tables and other features to enable audit trail so that every query or change of every record in the database is monitored and the entire history of the data in the database is captured. Basically, every time a record is accessed (queried, inserted, or changed), the user and time of access is recorded. Every time any field of a record is updated or deleted, the previous value of the record is saved.
• Add indexes and views so that frequently used queries and changes to the database are most efficient.

Answer 1

Regarding the above question, let us consider that the database in use be MS-SQL Server.

We have to know first, the concept of Entity-Framework(EF), which is able to build native commands automatically, for the database based on the LINQ-to-Entities(Language Integrated Query-to- Entities) or simply, Entity SQL queries, as well as build the commands for performing the traditional functionalities of an RDBMS(Relational Database Management System), which are insertion, updation (or modification), and deletion of the data. One may want to override these steps and use their own predefined stored procedures. We also can use stored procedures either to get the data or to add/update/delete the records for one or multiple database tables.

Entity-Framework Application Programming Interface(EF-API) actually creates a function instead of an entity, for each stored procedure and User-Defined Function (UDF) in the target database in consideration.

Let us use concerned stored procedure to fetch/retrieve the data from the database.

Initially, let us create the following stored procedures GetEmployeeByEmpIdin your SQL Server database. This procedure returns all the courses assigned to a particular student.

CREATE PROCEDURE [dbo].[GetEmployeeByEmpId]

-- Add the parameters for the stored procedure here

@EmpId int = null

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 p.projectid, p.projname,p.location, p.EmpId

from Employee e

left outer join EmployeeProject ep on ep.EmpId = e.EmpId

left outer join Project p on p.projectid = ep.projecteid

where e.EmpId = @EmpId

END

Next thing what we are going to do is to add a new Entity Data Model by tapping on to the right click on the project in the solution explorer in Visual Studio, and then perform the following steps,

-> Add

-> New Item.

After the above two steps, an Add New Item popup will open. In the popup, we need to select ADO.NET Entity Data Model and provide an appropriate name for the EDM and click the Add button. Next the Entity Data Model Wizard will be opened. Select EF Designer from database and click the Next button.

After the second step, we need to create a connection with the existing database. In this scenarion, we may encounter two situations,

i) If we are creating an EDM for the database for the first time, then we need to create a new connection by clicking on the New Connection... button.

ii) We already have a connection, so we just need to select the database from the dropdown and click the Next button.

In this step, we need to select GetEmployeeByEmpId under Stored Procedures and Functions. We need to make sure that the "Import selected stored procedures and functions into the entity model" checkbox is selected and then click Finish.

After this, we shall see the GetEmployeeByEmpId stored procedure added in Stored Procedures/Functions and Function Imports shown below, with the new complex type GetEmployeeByEmpId in the Model Browser. From the next time on, whenever we import a stored procedure or UDF into an EDM, it creates a new complex type with the name {sp name}_Result by default.

The stored procedure GetEmployeeByEmpId, returns the same fields defined in the Project entity. So, we don't need to add a new complex type for the GetCoursesByStudentId. Furthermore, we may change it by right clicking on GetEmployeeByEmpId in Function Imports and selecting Edit. This will open Edit Function Import popup.

Next, to set the Project entity as result type, we need to select Entities and select Project from dropdown in the popup window and click OK.

Next, we shall see that the function named GetEmployeeByEmpId is added in the context (derived from DbContext) class as shown below:

public partial class EmpDBEntities:DBContext

{

public EmpDBEntities

: base(mane="EmpDBEntities")

{}

protected override void onModelCreating(DbModelBuilder modelBuilder)

public virtual DbSet<Project> Projects {get; set;}

public virtual DbSet<Emplyee> Employees {get; set;}

publiv virtual ObjectResult <Project> GetEmployeeByEmpId(Nullable<int> EmpId)

{

    var EmpIDParameter = EmpId.hasValue ? new ObjectParameter("EmployeeID", EmpId) : new   ObjectParameter("EmployeeID", typeof(int));

   return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Project>("GetEmployeeByEmpId", EmpIDParameter )

}

Now the above portion made bold can be replaced by using the function defined by the name as GetEmployeeByEmpId and get the desired data as per our choice:

using (var context = new EmpDBEntities()

{

var courses = context.GetEmployeeByEmpId (1);

foreach (Project ps in projects)

Console.WriteLine(ps.projname);

}

For Testing purpose, the above example will be executed by the following

exec [dbo].[GetEmployeeByEmpId] @EmpId=1

In the next section, we shall use the following stored procedures for Employee entity:

1. sp_InsertEmployeeInfo: Inserts a new student record into the database
2. sp_UpdateEmployee: Updates a student record
3. sp_DeleteEmployee: Deletes a student record in the database.

Let us now write SQL scripts for each stored procedure.

sp_InsertEmployeeInfo

CREATE PROCEDURE [dbo].[sp_InsertStudentInfo]

-- Add the parameters for the stored procedure here

@StandardId int = null,

@EmpName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

INSERT INTO [EmpDB].[dbo].[Employee]([EmpName],[StandardId]) VALUES(@StudentName, @StandardId) SELECT SCOPE_IDENTITY() AS EmpId

END

sp_UpdateEmployee

CREATE PROCEDURE [dbo].[sp_UpdateEmployee]

-- Add the parameters for the stored procedure here

@EmpId int,

@StandardId int = null,

@EmpName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Update [EmpDB].[dbo].[Employee]

set EmpName = @EmpName,StandardId = @StandardId

where EmpID = @EmpId;

END

sp_DeleteEmployee

CREATE PROCEDURE [dbo].[sp_DeleteEmployee]

-- Add the parameters for the stored procedure here

@EmpId int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DELETE FROM [dbo].[Employee]

where EmpID = @EmpId

END

To make the above stored procedures working or functional, we need to update our existing EDM to add these stored procedures into our EDM. To do that, we have to right click on the designer and click Update Model from Database.. to open the update wizard. Next, expand the Stored Procedures and Functions node, and we select the above stored procedures and uncheck the Import selected stored procedures and function into the entity model checkbox because we shall have to map/connect these stored procedures with the Employee entity directly. And finally Click on the Finish Button.

In the Mapping Details, we shall see <Select Insert Function>, <Select Update Function>, and <Select Delete Function>. Select the appropriate stored procedure for each one in the dropdown, e.g. sp_InsertEmployeeInfo for the Insert function, sp_UpdateEmployee for the update function, and sp_DeleteEmployee for the delete function.

Now, we need to validate it before executing to ensure that there will not be a run time error. To accomplish this, Right click on the Employee entity in the designer and click Validate and make sure that there exists no warnings or errors.

Now, whenever we shall add, update, or delete the Employee entity, EF(Entity Framework), we shall use these stored procedures for CUD operations, instead of executing SQL commands.

Let us demonstrate this with the following example:

using (var context = new EmpDBEntities())

{

Employee employee = new Employee() { EmpName = "New Employee using SP"};

context.Employees.Add(employee);

//will execute sp_InsertEmployeeInfo

context.SaveChanges();

student.EmployeeName = "Edit employee using SP";

//will execute sp_UpdateEmployee

context.SaveChanges();

context.Employees.Remove(employee);

//will execute sp_DeleteEmployeeInfo

context.SaveChanges();

}

The above example will execute the following statements on each SaveChanges() call:

exec [dbo].[sp_InsertEmployeeInfo] @StandardId=NULL,@EmpName='New employee using SP'

go

exec [dbo].[sp_UpdateEmployee] @EmpId=56,@StandardId=NULL,@EmpName='Edit employee using SP'

go

exec [dbo].[sp_DeleteEmployee] @EmpId=56

go