The Database
For this example, we are going to use the AdventureWorks sample database.
The AdventureWorks database has a “HumanResources” schema that stores
information about your company’s organization, employees, and job
candidates. The AdventureWorks database has a stored procedure that
allows an application to update an Employee’s personal information. One
of these pieces of information is the employee’s marital status. Here is
what the stored procedure looks like:
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo] @EmployeeID [int], @NationalIDNumber [nvarchar](15), @BirthDate [datetime], @MaritalStatus [nchar](1), @Gender [nchar](1) WITH EXECUTE AS CALLER AS BEGIN SET NOCOUNT ON; UPDATE [HumanResources].[Employee] SET [NationalIDNumber] = @NationalIDNumber ,[BirthDate] = @BirthDate ,[MaritalStatus] = @MaritalStatus ,[Gender] = @Gender WHERE [EmployeeID] = @EmployeeID; END;
Table view
using System.Configuration; using System.Data; using System.Data.SqlClient; public partial class ApplicationDataService { partial void UpdatePersonalInfoOperations_Inserting(UpdatePersonalInfoOperation entity) { using (SqlConnection connection = new SqlConnection()) { string connectionStringName = this.DataWorkspace.AdventureWorksData.Details.Name; connection.ConnectionString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString; string procedure = "HumanResources.uspUpdateEmployeePersonalInfo"; using (SqlCommand command = new SqlCommand(procedure, connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters.Add( new SqlParameter("@EmployeeID", entity.EmployeeID)); command.Parameters.Add( new SqlParameter("@NationalIDNumber", entity.NationalIDNumber)); command.Parameters.Add( new SqlParameter("@BirthDate", entity.BirthDate)); command.Parameters.Add( new SqlParameter("@MaritalStatus", entity.MaritalStatus)); command.Parameters.Add( new SqlParameter("@Gender", entity.Gender)); connection.Open(); command.ExecuteNonQuery(); } } this.Details.DiscardChanges(); } }
VB:
Imports System.Configuration Imports System.Data Imports System.Data.SqlClient Namespace LightSwitchApplication Public Class ApplicationDataService Private Sub UpdatePersonalInfoOperations_Inserting(entity As UpdatePersonalInfoOperation) Using connection = New SqlConnection Dim connectionStringName = Me.DataWorkspace.AdventureWorksData.Details.Name connection.ConnectionString = ConfigurationManager.ConnectionStrings(connectionStringName).ConnectionString Dim procedure = "HumanResources.uspUpdateEmployeePersonalInfo" Using command = New SqlCommand(procedure, connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add( New SqlParameter("@EmployeeID", entity.EmployeeID)) command.Parameters.Add( New SqlParameter("@NationalIDNumber", entity.NationalIDNumber)) command.Parameters.Add( New SqlParameter("@BirthDate", entity.BirthDate)) command.Parameters.Add( New SqlParameter("@MaritalStatus", entity.MaritalStatus)) command.Parameters.Add( New SqlParameter("@Gender", entity.Gender)) connection.Open() command.ExecuteNonQuery() End Using End Using Me.Details.DiscardChanges() End Sub End Class End Namespace
Creating a Button to Call Our Operation
Now let’s invoke this middle tier operation. We wish to add a button
to our application that will mark a Single employee as Married. First,
open the SearchEmployees screen and add a new button to the ribbon. At
the top of the screen designer, right-click the “Screen Command Bar”
content item and select “Add Button…”.
Name the button “MarkAsMarried”. We only want this button to be enabled
for single employees, so right-click the button and select “Edit
CanExecute Code”. Enter in the following code:
partial void MarkAsMarried_CanExecute(ref bool result) { result = this.Employees.SelectedItem != null && this.Employees.SelectedItem.MaritalStatus == "S"; }VB:
Private Sub MarkAsMarried_CanExecute(ByRef result As Boolean) result = Me.Employees.SelectedItem IsNot Nothing And Me.Employees.SelectedItem.MaritalStatus = "S" End SubBack in the screen designer, right-click on the button again and select “Edit Execute Code”. Enter the following code:
C#:
partial void MarkAsMarried_Execute() { DataWorkspace dataWorkspace = new DataWorkspace(); Employee employee = this.Employees.SelectedItem; UpdatePersonalInfoOperation operation = dataWorkspace.ApplicationData.UpdatePersonalInfoOperations.AddNew(); operation.EmployeeID = employee.EmployeeID; operation.NationalIDNumber = employee.NationalIDNumber; operation.BirthDate = employee.BirthDate; operation.MaritalStatus = "M"; operation.Gender = employee.Gender; dataWorkspace.ApplicationData.SaveChanges(); }VB:
Private Sub MarkAsMarried_Execute() Dim dataWorkspace = New DataWorkspace Dim employee = Me.Employees.SelectedItem Dim operation = dataWorkspace.ApplicationData.UpdatePersonalInfoOperations.AddNew() operation.EmployeeID = employee.EmployeeID operation.NationalIDNumber = employee.NationalIDNumber operation.BirthDate = employee.BirthDate operation.MaritalStatus = "M" operation.Gender = employee.Gender dataWorkspace.ApplicationData.SaveChanges() End Sub
This code first creates a new DataWorkspace. This is needed because
we don’t want to modify the current change set on the screen. Instead,
we will make an independent change set that only contains the operation
entity we wish to invoke. When it is complete, we discard the
DataWorkspace and operation entity.
Next, we create a new UpdatePersonalInfoOperation making sure to use
our newly created DataWorkspace’s ApplicationData. We grab the
currently selected employee’s information and set it on the operation,
but we tell the operation to change the MaritalStatus to “M”. Finally,
we invoke the operation by calling SaveChanges.
That’s it. Now you can run your application, select a single
employee, and click on the Mark As Married button. The stored procedure
will be invoked.
Second Method to use the store procedure
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace LightSwitchApplication.UserCode
{
public class SqlHelper
{
public static SqlConnection conn(string Connection)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection].ConnectionString);
return con;
}
public static void InsInFeeDeatil(Int64 StuID, string CreatedBy,string ConnectionString)
{
string SP = "spInsFeeStructure";
SqlCommand cmd = new SqlCommand();
SqlConnection con = conn(ConnectionString);
cmd.Connection = con;
cmd.CommandText = SP;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@StuID",StuID ));
cmd.Parameters.Add(new SqlParameter("@CreatedBy",CreatedBy));
con.Open();
cmd.ExecuteNonQuery();
}
}
}
1. Create a class file in server folder under UserCode
2. Write the following code in that file
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace LightSwitchApplication.UserCode
{
public class SqlHelper
{
public static SqlConnection conn(string Connection)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection].ConnectionString);
return con;
}
public static void InsInFeeDeatil(Int64 StuID, string CreatedBy,string ConnectionString)
{
string SP = "spInsFeeStructure";
SqlCommand cmd = new SqlCommand();
SqlConnection con = conn(ConnectionString);
cmd.Connection = con;
cmd.CommandText = SP;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@StuID",StuID ));
cmd.Parameters.Add(new SqlParameter("@CreatedBy",CreatedBy));
con.Open();
cmd.ExecuteNonQuery();
}
}
}
Now write the following code under mail class file under inserting function
partial void tbStudentMasters_Inserting(tbStudentMaster entity)
{
SqlHelper.InsInFeeDeatil(StuId+1, this.Application.User.Name,this.DataWorkspace.SMData.Details.Name);
}
{
SqlHelper.InsInFeeDeatil(StuId+1, this.Application.User.Name,this.DataWorkspace.SMData.Details.Name);
}
The above code automatically fire when you are trying to insert the new entry in the data base
Allowing certain users to update personal information
A lot of times, you only want specific people to be able to invoke
the stored procedure. Using the pattern above, it is very simple to
enable this in your application. First, start by creating a Permission
by navigating to the Application’s Properties (right-click on the
application in the Solution Explorer and select “Properties” or double
click the “Properties” item under the application). On the “Access
Control” tab, enable authentication and enter a new Permission in the
grid.
Now, to only allow users with the UpdatePersonalInfo permission to
invoke the stored procedure, we just need to say that only these users
are allowed to “insert” the UpdatePersonalInfoOperation records. So go
back to the UpdatePersonalInfoOperation entity and drop down “Write
Code” –> UpdatePersonalInfoOperations_CanInsert and enter the
following code:
C#:
partial void UpdatePersonalInfoOperations_CanInsert(ref bool result) { result = this.Application.User.HasPermission(Permissions.UpdatePersonalInfo); }VB:
Private Sub UpdatePersonalInfoOperations_CanInsert(ByRef result As Boolean) result = Me.Application.User.HasPermission(Permissions.UpdatePersonalInfo) End Sub
Now if a user isn’t granted the UpdatePersonalInfo permission, and
they try invoking this operation, it will throw a PermissionException
and not allow the user to perform the operation. This isn’t a great
user experience, so the last thing to change in the application is the
button’s CanExecute code to check if the current user has permission to
insert UpdatePeronalInfoOperation records. Change your button’s
CanExecute method as follows:
C#:
partial void MarkAsMarried_CanExecute(ref bool result) { result = this.Employees.SelectedItem != null && this.Employees.SelectedItem.MaritalStatus == "S" && this.DataWorkspace.ApplicationData.UpdatePersonalInfoOperations.CanInsert; }VB:
Private Sub MarkAsMarried_CanExecute(ByRef result As Boolean) result = Me.Employees.SelectedItem IsNot Nothing And Me.Employees.SelectedItem.MaritalStatus = "S" And Me.DataWorkspace.ApplicationData.UpdatePersonalInfoOperations.CanInsert End Sub
Thanks Eric Erhardt
2 comments:
i tried my hand at above tutorial with vs 2013 lightswitch.
but got stuck at step where it says
"Back in the screen designer, right-click on the button again and select “Edit Execute Code”. Enter the following code:"
the code is shown in c#, but on button click it goes to JS file code.M i missing something??
Priya: please tell me what code are you writing under Edit Execute Code event?
Post a Comment