Skip to content

BBB: On client side, fetching a value from server side out of database

JohnRanger edited this page Sep 29, 2018 · 7 revisions

Basic Building Blocks Series: Within the basic building blocks series over time I will add all the little things which someone who just starts with Serenity Framework would have to spend a lot of time figuring it out him/herself.

What you get with this article:

From Client-Side, you can fetch a value out of a record of a table (different from the current entity) from the Database on the Server-Side.


Note: But this can be used to transport any data from client-side to server-side and back


For our example, we will have a settings table where we store application-wide settings of which we want to fetch a specific setting from the client-side.

(1) Create the settings table on your SQL backend. I use a Microsoft SQL 2016 Backend here.

USE [<yourDB>]
GO

/****** Object:  Table [dbo].[Settings]    Script Date: 29.09.2018 12:13:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Settings](
	[SettingId] [int] IDENTITY(1,1) NOT NULL,
	[SettingName] [nvarchar](max) NOT NULL,
	[SettingValue] [nvarchar](max) NOT NULL,
	[SettingDescription] [nvarchar](max) NULL,
 CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED 
(
	[SettingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

(2) Import the settings table with sergen.

(3) Add a new setting with the following data:

  • SettingName = TestSetting1
  • SettingValue = MySettingValue

(4) Create a temporary fake table called Generic which has an ID and a name field (we only use this to auto-create an additional endpoint and let sergen do the heavy work for us):

USE [<yourDB>]
GO

/****** Object:  Table [dbo].[Generic]    Script Date: 29.09.2018 12:22:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Generic](
	[GenericID] [int] IDENTITY(1,1) NOT NULL,
	[GenericName] [nvarchar](max) NULL,
 CONSTRAINT [PK_Generic] PRIMARY KEY CLUSTERED 
(
	[GenericID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

(5) Use sergen to create a new module. Put it under "Common" like this:

sergen


Hint: With older (e.g. V2.9.x) sergen versions, you had the possibility to only generate certain files. In our case, we only need xyzEndpoint.cs and xyzRepository.cs. If your sergen does not anymore provide the option to select which files are generated, please delete the unneccessary files by hand from your project.


(6) Modify your new GenericEndpoint.cs

  1. Remove all endpoints like Create, Update, delete, etc
  2. Change the endpoint type from ServiceEndpoint to Controller like this:

before:

    public class GenericController : ServiceEndpoint

after:

    public class GenericController : Controller

Hint: This gives us a standard asp.net MVC controller which we can access with standard jQuery Ajax.

Add the following endpoint within GenericEndpoint.cs:

        [HttpPost]
        public ActionResult GetSettingValue(string SettingName)
        {
            //String output = (ManageSettings.GetSettingValue(SettingName)).Replace(@"\","/");
            String output = ManageSettings.GetSettingValue(SettingName);
            return Json(new { result = output }, JsonRequestBehavior.AllowGet);
        }

Hint: There will be an error, showing that ManageSettings does not exist. That's correct for the moment as we will add this funtionality in a moment.

(7) Create the ManageSettings class like this:

  1. Under /Common/Helpers/ create a new class file calles ManageSettings.cs with following content:
using Serenity.Data;
using System.Data;
using System.Linq;

using mySettingsRow = <your project>.<your Module>.Entities.SettingsRow;


namespace <your project>.Modules.Common.Helpers
{
    public class ManageSettings
    {
     
        public static string GetSettingValue(string SettingName)
        {
            var connection = SqlConnections.NewFor<mySettingsRow>();

            var row = new mySettingsRow();
            if (new SqlQuery().From(row)
                .Select(mySettingsRow.Fields.SettingName, mySettingsRow.Fields.SettingValue)
                .Where(mySettingsRow.Fields.SettingName == SettingName)
                .GetSingle(connection))
            {
                return row.SettingValue;
            }

            return null;
        }

    }

}

... TO BE CONTINUED shortly ...

Clone this wiki locally