Click here to Skip to main content
15,118,865 members
Articles / Web Development / ASP.NET
Article
Posted 13 Mar 2016

Stats

20.7K views
10 bookmarked

spGet Framework 1.0

Rate me:
Please Sign up or sign in to vote.
3.32/5 (12 votes)
17 Mar 2016CPOL5 min read
No more ORM, EF, A Stored Procedure direct Access framework for JavaScript.

Introduction

The spGet is a JavaScript Framework to give you a way of direct access to the Stored Procedures from your front-end web applications. You can use your Stored Procedures that are stored in remote databases directly from your client script with the spGet. Now, however, spGet provides you only a version for C# / ASP.NET / MS-SQL combination, I will add other various combinations such as Java, JSP, Spring, MySQL, and Oracle. spGet framework is powerful, useful but easy and simple. There is no learning curve, you can adapt new framework in a minute. With the spGet, you do not worry about back-end controllers and models any more. Simply you just make your stored procedure in your database, and call the stored procedure from your JavaScript code. In addition, spGet sets you free from ORM or back-end controllers. You do not have to use ORM frameworks such as Entity Framework or Hibernate, and do not have to make Models, Controllers, and Services to access database.

Prerequisites

Nuget: PM> Install-Package Newtonsoft.Json

Environment

  • Browser Compatibility
  • Microsoft IE(8,9,10,11 ~ ), Microsoft Edge, Chrome, Fire Fox, Safari, Opera, Modern mobile browsers
  • ASP.NET Versions
  • Visual Studio Versions
  • 2012, 2013, 2015

Installation

Download

  • Get the latest version of spGet release from the below link.

https://github.com/jxhv/spGet/releases/download/spGet/Jeremisoft-spGet-CORE.zip

Define your database connection

  • Web.config
<connectionStrings>

    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Demo.mdf;Initial Catalog=aspnet-WebApplication2-20160213110200;Integrated Security=True"

      providerName="System.Data.SqlClient" />

</connectionStrings>

Copy files

For the ASP.NET MVC Version

  • Copy ASP.NET-MVC\Controllers\SpGetController.cs file to your project Controllers folder
  • Copy ASP.NET-MVC\Models\spGet folder to your project Models folder
  • Copy ASP.NET-MVC\Scripts\spGet folder to your project Scripts folder

For the WebForm Version

  • Copy ASP.NET-WebForm\Services\spGet.asmx(spGet.asmx.cs) files to your project Services folder. If you do not have Services folder, create it first.
  • Copy ASP.NET-WebForm\Models\spGet folder to your project Models folder
  • Copy ASP.NET-WebForm\Scripts\spGet folder to your project Scripts folder

Write MapRoute for spGet (for the ASP.NET MVC Version only)

  • Add a MapRoute for spGet into your RouteConfig.cs
C#
routes.MapRoute(

    name: "SpGet",

    url: "SpGet/{action}",

    defaults: new { controller = "SpGet", action = "" }

);

Write front-end code

HTML
<!DOCTYPE html>

<html>

<head>

    <title>SpGet - StordProcedure to Script Framework</title>

    <style>

        li { display: inline-block; width: 120px; border-bottom: 1px solid #808080; }

    </style>

    <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.12.0.min.js"></script>

    <script src="~/Scripts/ParaScript/spGet.js"></script>

    <script>

        $(function () {

           // list a table

var result = "";

            var data = new SpGet().AsJson("mem_get_tbl");

            for (var i = 0, ln = data.length; i < ln; i++) {

                result += "<ul><li>" + data[i].name + "</li></ul>");

            }

            $("#board").html(result);

        });

    </script>

</head>

<body>

    @* Define an AntyForgeryForm for script security *@

    <form id="__ajaxAntiForgeryForm" action="#"

       method="post">@Html.AntiForgeryToken()</form>

    <h1>SpGet - Member List</h1>

    <div id="board"></div>

</body>

</html>

Library

getAsJson() Method

The getAsJson() method gets table from a database through a Stored Procedure as JSON object array. The target Stored Procedure must return a table or tables by SELECT statement.

Syntax

var data = new spGet().getAsJson(SP_NAME, [param], [log]);

Parameters

  • The required SP_NAME parameter specifies the stored procedure name you wish to use.
  • The optional [param] parameter specifies a set of stored procedure parameter key-value pairs.
  • The optional [log] parameters specifies a Log string to send to the stored procedure. This parameter is designated to write log only. You need a logging parameter by the name of @logging in the target Stored Procedure to use this parameter.

Returns

  • A JSON object array (One-dimensional array): If the Stored Procedure returns single Table
  • An array of JSON object array (Two-dimensional array): If the target Stored Procedure returns several tables by several SELECT statements, the results are passed as a two dimensional array. The first dimension represents the list of tables, and the second dimension represents the list of records.
  • Type Conversion of Return fields:
  • Type conversion from Stored Procedure result fields to JavaScript object is performed implicitly by spGet. You do not need to make a Model or ORM to receive the table result.

Behaviors

  • spGet detects the data type of each [param] attribute values and generates stored procedure parameters that have corresponding data types of SQL Server. You do not need to worry about the data type conversion of the parameters.
SQL: CREATE PROCEDURE test_sp @name nvarchar(36), @age int, @height float
JavaScript: new spGet().getAsJson("test_sp", { name:"John Doe", age:30, height:5.76 });

Corresponding Types

spGet (JavaScript)

SQL Server

string

char, nchar, varchar, nvarchar, text, ntext, datetime

integer

int, smallint, tinyint, bigint, (decimal, numeric)

Float

float, real, money

Bool

Bit

getAsInt() Method

The getAsInt() method gets a integer return from a Stored Procedure. The target Stored Procedure must return an integer value by using RETURN statement. It is good to use this method to call a Stored Procedure that performs a batch update procedure and returns the result code of execution.

Syntax

var result = new spGet().getAsInt(SP_NAME, [param], [log]);

Returns

  • An integer value

Parameters and Behaviors are the same with the getAsJson()

getAsStr() Method

The getAsStr() method gets a string value from a Stored Procedure. The target Stored Procedure must return a string value through an OUTPUT parameter by the name of @OUTPUT.

Syntax:

var result = new spGet().getAsStr(SP_NAME, [param], [log]);

Returns

  • A string value

Parameters and Behaviors are the same with the getAsJson()

Examples

Example 1: Stored Procedure which returns single table

Stored procedure:

SQL
CREATE PROCEDURE employee_get_tbl @name nvarchar(36), @mobile nvarchar(36)

AS

BEGIN

   SELECT name_n, LEFT(mobile, 5) AS mobile, user_id

   FROM Member

   WHERE name_n LIKE '%' + @name + '%' AND mobile LIKE '%' + @mobile + '%'

END

Html code

HTML
<table>

   <tbody id=tbMembers">

   </tbody>

</table>

JavaScript code

JavaScript
var result = "";

var spget = new spGet();

var data = spget.getAsJson("employee_get_tbl", { name: "ba", mobile: "714" });

for(var i = 0; i < data.length; i++) {

   result += "\

      <tr>\

         <td>" + data[i].name_n + "</td>\

         <td>" + data[i].mobile + "</td>\

         <td>" + data[i].user_id + "</td>\

      </tr>";

}

$("#tbMembers").html(result);

Example 2: Stored Procedure which returns multiple table

Stored procedure:

SQL
CREATE PROCEDURE employee_get_tbl_member_n_company @member nvarchar(36), @com_id int

AS

BEGIN

   SELECT name_n, LEFT(mobile, 5) AS mobile, user_id

   FROM Member

   WHERE name_n LIKE '%' + @name + '%'


   SELECT name, address, phone

   FROM Company

   WHERE id = @com_id

END

Html code

HTML
<table>

   <tbody id=tbMembers">

   </tbody>

</table>

<table>

   <tbody id=tbCompanies">

   </tbody>

</table>

JavaScript code

JavaScript
var result1 = "";

var result2 = "";

var members = [];

var companies = [];

var rec = {};

var param = { member: "Sam", com_id: 1234 };

var data = new spGet().getAsJson("employee_get_tbl_member_n_company", param);


if(data == null)

   return;

members = data[0];

companies = data[1];


for(var i = 0; i < members.length; i++) {

   rec = members[i];

   result1 += "\

      <tr>\

         <td>" + rec.name_n + "</td>\

         <td>" + rec.mobile + "</td>\

         <td>" + rec.user_id + "</td>\

      </tr>";

}

$("#tbMembers").html(result1);


for(var i = 0; i < companies.length; i++) {

   rec = companies[i];

   result2 += "\

      <tr>\

         <td>" + rec.name + "</td>\

         <td>" + rec.address + "</td>\

        <td>" + rec.phone + "</td>\

      </tr>";

}

$("#tbCompanies").html(result2);

Example 3: How to use log parameter (with getAsInt)

Stored procedure

SQL
CREATE PROCEDURE employee_set_member_phone

@member_id int,

@phone nvarchar(36),

@logging nvarchar(max) -- you must add this parameter at the end of parameter list

AS

BEGIN

   DECLARE @result int


   UPDATE Member

   SET phone = @phone

   WHERE id = @member_id


   SET @result = @@ROWCOUNT


   EXEC write_error_log @log -- custom log writing SP that has been made by you


   RETURNS @result

END

JavaScript code

JavaScript
var param = {

   member_id: 17623,

   phone: "802-132-1111"

};

var log = '{ date:"02/09/2012", editor: 19772, ip:"122.12.177.123" }';

var result = new spGet().getAsInt("employee_set_member_phone", param, log);


if(result < 1)

   alert("error");

else

   alert("success");

Example 4: Receiving a String with getAsStr

Stored procedure:

JavaScript
CREATE PROCEDURE employee_get_val_name_only

   @member_id int,

   @OUTPUT nvarchar(36) OUTPUT

AS

BEGIN

   SELECT @OUTPUT = name_n

   FROM Member

   WHERE id = @member_id

END

JavaScript code

JavaScript
var result = new spGet().getAsStr("employee_get_val_name_only", { member_id: 18928 });


if(result == null)

   alert("error");

else

   alert(result);

Example 5: Enciphering Stored Procedure Name

If you do not want that your Stored Procedure name be revealed to the public, there is a way to help you.

Stored procedure

SQL
CREATE PROCEDURE employee_get_val_name_only

   @member_id int,

   @OUTPUT nvarchar(36) OUTPUT

AS

BEGIN

   SELECT @OUTPUT = name_n

   FROM Member

   WHERE id = @member_id

END

your_view_file.cshtml

ASP.NET MVC Version
<script>

   var sp_mem_get_name                                                              

       = "@Html.Raw(SpGet.Security.Encrypt("employee_get_val_name_only "))";

<script>
ASP.NET MVC Version
<script>

   var sp_mem_get_name = "<%=SpGet.Security.Encrypt("mem_add")%>";

<script>
  • your_script_file.js

JavaScript
var result = new spGet().getAsStr(sp_mem_get_name, { member_id: 18928 });


if(result == null)

   alert("error");

else

   alert(result);

For your security, you hve to change DES_KEY in the /Models/spGet/Security.cs with your one.

License and Conclusion

License

The spGet framework distributed under MIT License (https://opensource.org/licenses/MIT)

Libraries

License Information: The jQuery library is being used under the terms of the MIT License. Jquery License information.

License Information: The Json.NET is being used under the terms of the MIT License.

Conclusion

Author

Distribution

You can try the latest version of spGet from the below.

https://github.com/jxhv/spGet

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Daniel JXHV
United States United States
No Biography provided

Comments and Discussions

 
QuestionSuggestion Pin
zpaulo_carraca22-Mar-16 7:59
Memberzpaulo_carraca22-Mar-16 7:59 
Server side could match the user on browser with the request. If a user is asking for a sp result he is not allowed, then block it. Other option, I often use, every sp has user as an input parameter and then checks what info can be supplied for that user.
Where is the code for other DBs ?
Great work.
QuestionThis seems pretty odd Pin
Sacha Barber18-Mar-16 9:35
mvaSacha Barber18-Mar-16 9:35 
GeneralYet another example... Pin
d.shapiro15-Mar-16 6:53
Memberd.shapiro15-Mar-16 6:53 
QuestionWhy expose procedures to front end? Pin
Rafael Nicoletti14-Mar-16 5:04
MemberRafael Nicoletti14-Mar-16 5:04 
AnswerRe: Why expose procedures to front end? Pin
Daniel JXHV14-Mar-16 14:08
MemberDaniel JXHV14-Mar-16 14:08 
QuestionPerformance and Security Pin
Dushmantha Baranige14-Mar-16 0:44
professionalDushmantha Baranige14-Mar-16 0:44 
AnswerRe: Performance and Security Pin
Daniel JXHV14-Mar-16 3:23
MemberDaniel JXHV14-Mar-16 3:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.