Asp. NET using Sqlsugar+syntacticsugar+jqwidgetssugar+jqwidgets framework MVC rapid Development Example Tutorial

Jqwidgets.js:

is a fully functional framework with professional touch jquery plug-ins, themes, input validation, drag-and-drop plug-ins, data adapters, built-in Wai-aria (barrier-free web applications) accessibility, internationalization, and MVVM mode support. Jqwidgets provides a comprehensive solution for building professional Web sites and developing mobile applications. It is entirely based on open standards and technologies such as HTML5, CSS, JavaScript, and jquery. Jqwidgets enables responsive web development to help you create applications and Web sites that look beautiful on desktops, tablets, and smartphones.

Whether it is aesthetic or function is better than Easyui, code open source use charges.


SyntacticSugar.dll:

A tool class library that includes validation, universal extension functions, type conversions, file uploads, and a large number of C # syntax sugars.

Source Address: Https://github.com/sunkaixuan/SyntacticSugar


SqlSugar.dll:

is a lightweight, high-performance, Easy-to-use ORM Framework based on MSSQL

First, the introduction of Jane

Advantages:

1, superior performance, query using Reflection.Emit to create IL language + delegate binding and then cache the object, DataReader directly assigned to the cache object, High-performance Lambda resolution, the overall performance comparable to Ado.net, Query speed slightly slower than DataReader but slightly faster than DataTable
2, a large number of grammatical sugar, lambda expression screening, new multiple table query, convenient paging, etc.
3, Support NOLOCK query, improve performance
4. Support Services
5. Built-in entity class generation function without using a third party code generator
6, simple and easy to use, examples are complete with a question will answer.

Disadvantages:

Currently only MSSQL support, will be fully developed in the future

Composition

Sqlsugar is a unified invocation pattern provided by SQLSUGARCLIENTR, and Sqlsugarclientr is made up of 5 parts

1. Self function
2. Entity generation
3, one-table query
4. Multi-Table Query
5. Base class function

As shown in figure:

Second, the use of the tutorial

Inquire


1, single table or single view query:

by calling Db. Queryable () Related extension function easy to make order form query

using  (Sqlsugarclient db = new sqlsugarclient (CONNSTR))//Open database connection {           //query all       var student = db. Queryable<student> ().
ToList ();       //Query single       var single = db. Queryable<student> ().
Single (c => c.id == 1);       //Take 10-20 strips       var page1 = db . Queryable<student> (). Where (C&NBSP;=&GT;&NBSP;C.ID&NBSP;&GT;&NBSP;10). by ("id"). Skip (10). Take (20).
ToList ();       //the simplified wording of the previous sentence, also take 10-20       var page2  = db. Queryable<student> (). Where (C&NBSP;=&GT;&NBSP;C.ID&NBSP;&GT;&NBSP;10). by ("id").
Topagelist (2,&NBSP;10);       //Query Bar Count       var counT = db. Queryable<student> (). Where (C&NBSP;=&GT;&NBSP;C.ID&NBSP;&GT;&NBSP;10).
Count ();       //take all       var skip = after starting from the 2nd article  db. Queryable<student> (). Where (C&NBSP;=&GT;&NBSP;C.ID&NBSP;&GT;&NBSP;10). by ("id"). Skip (2).
ToList ();       //take the first 2       var take = db. Queryable<student> (). Where (C&NBSP;=&GT;&NBSP;C.ID&NBSP;&GT;&NBSP;10). by ("id"). Take (2).
ToList ();       // not like       var notlike  = db. Queryable<student> (). Where (C => !c.name.contains ("a"). ToString ())).
ToList ();       //  can use  tostring and  convert in lambda, better place than EF        var convert1 = db. Queryable<student> (). Where (c => c.name ==  "a"). Tostring ()).
ToList ();       var convert2 = db. Queryable<student> (). Where (C => c.id == convert.toint32 ("1")). ToList ();//      var convert3 = db. Queryable<student> (). Where (C => datetime.now > convert.todatetime ("2015-1-1")).
ToList ();       var convert4 = db. Queryable<student> (). Where (C => datetime.now > datetime.now).
ToList ();       //Support String where  allows you to solve more complex queries       var  Student12 = db. Queryable<student> (). Where (c => 1 == 1). Where ("Id> @id", new{id=1}).
ToList (); ///There is a record back to true, no returns false  bool isany100 = db. Queryable<student> ().
Any (c => c.id == 100);  bool isany1 = db. Queryable<student> (). Any (C&NBSP;=&GT;&NBSP;C.ID&NBSP;==&NBSP;1); 

2, single-table advanced query

Query and page by condition

 <summary>///  query based on criteria and paging/// </summary>/// <param name= "name" >
</param>/// <param name= "Sex" ></param>/// <returns></returns> Public static list<student> getstudent (String name, string sex, int  pageindex, int pagesize, string orderfileds,out int pagecount) {   using  (Sqlsugarclient db = sugardao.getinstance ())   {       var qable = db.
Queryable<student> ();       if  (!string. IsNullOrEmpty (name)       {           qable = qable.
Where (It => it.name.contains (name)); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}       if  (!string. IsNullOrEmpty (Sex))       {          qable = qable.
Where (It => it.sex == sex); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}       if  (!string. IsNullOrEmpty (orderfileds))//Don't worry about injecting       {           qable = qable.
(orderfileds); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}       pagecount = qable.
Count ();       return qable.
Topagelist (pageindex, pagesize); &NBSP;&NBSP}}

New Container Conversions

Public list<classnew> getselectlist (int id)
{
using (Sugardao db = new Sugardao ())
{
Return DB. Queryable<student> (). Where (C=>C.ID<10). Select (c => new Classnew {newid = c.id, newname = C.name,xx_name=c.name}). ToList ()//Does not support anonymous class conversions and does not recommend the use of
}

Grouped queries

Public list<sextotal> getsextotal ()
{
using (Sugardao db = new Sugardao ())
{return
db. Queryable<student> (). Where (c => c.id < 20). GroupBy ("Sex"). Select<student, Sextotal> ("Sex,count=count (*)").   ToList ();
}
}

SELECT Sex,count=count (*) from Student WHERE 1=1 and (IDs <) GROUP by Sex--Generate results

3. Multi-Table query:

When it comes to multiple-table queries that are not satisfied with performance or functionality in many ORM, or better to use SQL, the following is my creative, give up the strong type of writing, so that the code closer to the SQL statement to write, so that SQL fully controllable, but also to solve the performance of OMR multiple tables.

There are by-and-by, GroupBy and apply, and so on, the example is not introduced.

Compare it to EF:

EF query:

var Reval = (from S in db. Student
join SC in db. School on s.sch_id equals Sc.id
joins SB in DB. Subject on s.id equals Sb.sid to
SSB from
SB2 in SSB. DefaultIfEmpty ()
select new {
s.id,
s.name,
s.sch_id,
s.sex
}). Where (c=>c.id>1). Where (C=>C.ID>2). by (C=>c.id). ThenByDescending (C=>c.name). Skip (10). Take (10). ToList ();

Sqlsugar query:

Db. Sqlable (). Form<student> ("s")
. Join<school> ("SC", "Sc.id", "s.sch_id", Jointype.inner)
. Join<subject> ("SB", "Sb.sid", "S.id", Jointype.left). Where ("S.id> @id1"). Where ("s.id> @id2")
. Selecttopagelist<models.student> ("s.*", "S.id asc,s.name desc", 2, new {id1=1,id2=2});

More Sqlsugar Queries:

The table name is a string literal
list<school> dataList = db. Sqlable ()
. Form ("School", "s")
. Join ("Student", "St", "St.id", "S.id", Jointype.inner)
. Join ("Student", "St2", "St2.id", "St.id", Jointype.left). Where ("s.id>100 and s.id< @id"). Selecttolist<school> ("st.*", new {id = 1});
//Multiple table pagination
List<school> datapagelist = db. Sqlable ()
. Form<school> ("s")
. Join<student> ("St", "St.id", "S.id", Jointype.inner)
. Join<student> ("St2", "St2.id", "St.id", Jointype.left). Where ("s.id>100 and s.id<100"). Selecttopagelist<school> ("st.*", "S.id", 1, 10);

Sub-query plus dynamic stitching

Public list<student> getstudent (int id, string name) {   int
 pageCount = 0;    using  (Var db = sugardao.getinstance ())    {        //form ("Student", "s") syntax optimized to  Form<Student> ("s")         var sable = db. Sqlable (). Form<student> ("s").
Join<school> ("L",  "s.sch_id",  "L.id",  jointype.inner);        if  (!string. IsNullOrEmpty (name)        {            sable = sable.
Where ("S.name= @name"); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}        if  (!string. IsNullOrEmpty (name)        {            sable = sable.
Where ("s.id= @id  or s.id=100"); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}        if  (id >  0)  {           sable = sable. Where ("l.id in  (Select top 10 id from school)");//where Add subquery    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP}        //Parameters      
  var pars = new { id = id, name = name };        pagecount = sable.
Count (pars);        return sable.
Selecttolist<student> ("s.*",  pars); &NBSP;&NBSP;&NBSP}}

　

4. Use SQL or stored procedure query:

In order to be compatible with the above situation, I wrote a function to meet the needs of the emergency.

var School = db. Sqlquery<school> ("SELECT * from School");
//Get ID
var id = db. Sqlquery<int> ("Select top 1 IDs from School"). Single ();
//Stored procedure
//var spresult = db. Sqlquery<school> ("Exec sp_school @p1, @p2", new {p1=1,p2=2});

Add to

using  (Sqlsugarclient db = new sqlsugarclient (CONNSTR))//Open database connection {               school s = new school ()              {                 name =  "Blue Xiang"      
       };               //Insert Single              var id2 = convert.toint32 (db.
Insert (s));               //Insert Multiple              List<School> sList = new List<
School> ();             sList.add (s);             var ids = db.
Insertrange (slist);  }

　　
Modify

Specifies that the column update
DB. Update<school> (New {name = "Blue Xiang 2"}, it => it.id = = ID);
Entire entity update, note that the primary key must be the first property db of the entity class
. Update<school> (new School {id = id, name = "Blue Xiang 2"}, it => it.id = = ID);

Delete

Db. Delete<school> (ID);//Note the primary key must be the first property db of the entity class
. Delete<school> (it => it.id >);
Db.   Delete<school> (new string[] {"M", "a", "102"});
db. Falsedelete<school> ("Is_del", 100)//False deletion
//equivalent to update school set is_del=0 where ID in (m) <br>
DB . Falsedelete<school> ("Is_del", it=>it.id==100);

More underlying functions

Db. ExecuteCommand (SQL);
db. getdatatable (SQL);
Db. getlist<student> (SQL);
Db. getsingle<student> (sql + "where id=1");
using (SqlDataReader read = db. Getreader (SQL)) {}//transaction must release DataReader
db. Getscalar (SQL);
Db. GetString (SQL);
Db. GETINT (SQL);

　　
Entity generation

using  (Sqlsugarclient db = new sqlsugarclient (CONNSTR))/Open database connection  {                     // Generates entity class files for all tables based on the current database   (parameters: sqlsugarclient , file directories, namespaces)                      db.
Classgenerating.createclassfiles (Db,server.mappath ("~/models"), "Models");                      //generates entity class files based on table names                      db. Classgenerating.createclassfilesbytablenames (Db, server.mappath ("~/models"),  "Models"  ,  "
Student "," school ");                        //generates a class character from a table nameString                      var str = db.
Classgenerating.tablenametoclass (db,  "Student");                        //generates class strings based on SQL statements                      var str2 = db.
Classgenerating.sqltoclass (db,  "select top 1 * from student",  "Student"); ｝

　　
Transaction

using (sqlsugarclient db = new Sqlsugarclient (CONNSTR))//Open a database connection
{
try{
//Open transaction, you can use a transaction, or multiple transactions 
  
    db.         Begintran ();
//sq1
//sql2
//sql3
}catch (Exception ex) {
//ROLLBACK TRANSACTION
Db.       Rollbacktran ();
Throw ex;
}//Close the database connection
  

No lock query

When Isnolock is set to TRUE, the SQL statement table name that is generated by the query is followed with a with (Nolock)

using (sqlsugarclient db = new Sqlsugarclient (CONNSTR))//Open database connection
{
db.          Isnolock = true;
SQL XXX with (NOLOCK)
db.          Isnolock = false;
SQL xxx
//sql xxx
db.          Isnolock = true;
SQL XXX with (nolock)
}//Close the database connection

How to support multi-Library switching


Define a Sugardao class to extend the Sqlsugar

<summary>
///extension sqlsugarclient
///</summary> public
class Sugardao
{
//Prohibit instantiation  
Private Sugardao () {
} public
static Sqlsugarclient getinstance ()
{
String connection = "Server=.; Uid=sa;pwd=sasa;database=sqlsugartest ";  Here can be dynamically based on cookies or session to achieve multiple library switching return to
new sqlsugarclient (connection);
}
}

Use no incoming connectionstring

Public School getsingleschool (int id)
{
using (sqlsugarclient db = Sugardao.getinstance ())
{
  
    return DB. Queryable<school> ().      Single (c => c.id = = ID);
}
}
  

Third, the performance test:

10,000 times

1000 times

10,000 times

. NET4.52+EF 6.0+SQL12 to wash the shame

The. NET Framework 4.52+ sql12 +ef6.0, EF performance is obviously up, let it be a champion, I also do not go to test, Microsoft's things upgrade after the performance without question, in the Multiple table query and add to the deletion of the composite is basically a tie.


Sqlsugar Pursuit of lightweight, fast, easy to use the control of SQL, but also hope you can like or put forward your valuable advice.

JQWidgetsSugar.dll

Based on the Jqwidgets.js C # wrapper class library, currently only completes the grid section, all my Git projects will continue to be updated in future project development


Effect Chart:

C # code

using system;
using system.collections.generic;
using system.linq;
using system.web;
using system.web.mvc;
using sqlsugar;
using dal;
using jqwidgetssugar;
using models;
using syntacticsugar; namespace netjqwidgets.controllers {    public class gridcontroller :  controller     {        public  Actionresult index ()         {      
      var adp = new griddataadaptersource ();
            adp.url =  "/grid/data";             var gc = new 
Gridconfig ();             gc.gridbuttons = new  list&Lt Gridbutton> ()             {                new gridbutton () { click= "add" ",  name=" AddButton ",  icon=" Jqx-icon-plus ",  title=" Add "},                 new gridbutton () { click= "edit",  name= " Editbutton ",  icon=" Jqx-icon-edit ",  title=" edit "},                 new gridbutton () { click= "del",  name= "Delbutton",  icon= "Jqx-icon-delete",  title= "Delete"}           
  };
            gc.pageSize = 20;
            gc.width =  "80%";      &Nbsp;      gc.columns = new list<gridcolumn> () {                new gridcolumn () { text = "Number",  datafield= "id",  width= "40px", cellsalign=aligntype.left,datatype=datatype.dataint  &NBSP},                new  GridColumn () { text= "name",  datafield= "names",  cellsalign=aligntype.left,datatype= DATATYPE.DATASTRING&NBSP},                 new gridcolumn () { text= "product name",  datafield= "ProductName",  cellsalign= ALIGNTYPE.LEFT,DATATYPE=DATATYPE.DATASTRING&NBSP},                 new gridcolumn () { text= "quantity",  datafield= "Quantity",  Cellsalign=aligntype.right , datatype=dataTYPE.DATAINT&NBSP},                 New gridcolumn () { text= "creation time",  datafield= "date",  cellsformat= "Yyyy-mm-dd", cellsalign= Aligntype.right, datatype=datatype.datadate           
    }             };                    
  var grid = jqxgrid.bindgrid ("#netgrid", &NBSP;ADP,&NBSP;GC);             ViewBag.validationBind = 
Validationsugar.getbindscript ("Validate_key_grid_index");
            return view (GRID); &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}           [ Httpdelete]
        public jsonresult del (Int id)          {            using   (Sqlsugarclient db = sugardao.getinstance ())              {                 actionresultmodel<string> model = new actionresultmodel<string
> ();                  Model.issuccess = db.
Delete<gridtable> (ID);                 
model.respnseinfo = model.issuccess ?  "Delete succeeded"  :  "Delete failed";                 return 
Json (model);             }        &NBSP;&NBSP}           [httppost]          public jsonresult add (GRIDTABLE&NBSP;GT)          {            using  (sqlsugarclient  db = sugardao.getinstance ())              {                string  message = string.
Empty;                 var 
Isvalid = validationsugar.postvalidation ("Validate_key_grid_index",  out message);              &nbSp;  actionresultmodel<string> model = new actionresultmodel<string>
();                 if  ( IsValid)//Background Verify data integrity                  {                     model.issuccess = db.
Insert (GT)  != DBNull.Value;                   
  model.respnseInfo = model.isSuccess ?  "Add Success"  :  "Add failed";                 }                  else {                     model.issuccess = false;                   
  model.respnseInfo = message;                 }   
              return json (model);             }        &NBSP;&NBSP}         [httpput]          public jsonresult edit (GRIDTABLE&NBSP;GT)          {            using  (sqlsugarclient  db = sugardao.getinstance ())             {               
 ActionResultModel<string> model = new ActionResultModel<string> ();                 string  Message = string.
Empty;                 var 
Isvalid = validationsugar.postvalidation ("Validate_key_grid_index",  out message);                 if  ( IsValid)//Background Verify data integrity                  {                     model.issuccess = db. Update<gridtable> (gt, it => it.id == gt.id);                   
  model.respnseInfo = model.isSuccess ?  "Edit succeeded"  :  "edit failed";                 }                  else {                     
model.issuccess = false;                   
  model.respnseInfo = message;                 }   
              return json (model);       &nbsP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}         }            [outputcache (duration = 0)]          public jsonresult data (Gridsearchparams pars)          {            using  (sqlsugarclient  db = sugardao.getinstance ())              {                if   (pars.sortdatafield == null)  { //default by ID descending                      pars.sortdatafield =  "
ID ";                      pars.sortorder =  "desc";                 }                  sqlable sable =  db. Sqlable (). Form<gridtable> ("G");//Sqlable Object for query table                  var model = JQXGrid.GetWidgetsSource<Models.GridTable> ( Sable, pars)//automatic query based on grid parameters             
    return json (Model, jsonrequestbehavior.allowget);             }          }    &nbsp}}

Razor View

@{    ViewBag.Title =  "Index";     Layout =  "~/
Views/shared/_layout.cshtml "; } @using  jqwidgetssugar @section  head{    <script src= "/Content/ My97datepickerbeta/my97datepicker/wdatepicker.js " type=" Text/javascript "></script>      <link href= "/content/my97datepickerbeta/my97datepicker/skin/wdatepicker.css"  rel= " Stylesheet "        type=" Text/css " />      <script src= "/content/jquery-validation-1.13.1/dist/jquery.validate.min.js"  type= "Text/javascript "></script>     <link href="/content/jquery-validation-1.13.1/ Validation.sugar.css " rel=" stylesheet "        type=" Text/css "  />     <script src= "/content/jquery-validation-1.13.1/validation.sugar.js"   type= "Text/javascript" ></script>     <script type= "Text/javascript" >           //Add          Function add (Row)  {            save (Row,
 true);
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}           //Edit         function edit (Row)  {     
       save (Row, false);
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP}           //Delete         function del (Row)  {             if  (row == null)  {             &nbSp;   jqxalert (' Please select a record! ')             } else {                 jqxdelete ({ gridSelector:   "#netgrid",                      url:  "/grid/del",          
           data: { id: row.id }
                });             }       
&NBSP;&NBSP}           function save (ROW,&NBSP;ISADD)  {             var isedit = !
Isadd;             if  (isedit)  {                 if  (row ==  NULL)  {                     jqxalert (' Please select a record! ')                   
  return;                 }              }              //Pop-up Box             jqxwindow ("#editbox",  isadd?)
Add ":" "Edit", 400,  "Auto");               //Landscaping  button &NBsp;           $ ("#editbox  button"). JqxButton ();               //Cancel Event     
        $ (' #cancel '). Unbind ();             $ (' #cancel '). On (' click ',  function   (e)  {                 $ ("#editbox"). Jqxwindow ("close")             
});               if  (isadd)  {                 //Clear the form                  $ ("#frmtable"). FormClear ()
;             } else {                 //Date of day                  row.date = $.convert.todate (row.date,  "Yyyy-MM-dd")                  //automatically populate the form with JSON, or you can self implement                  $ ("#frmtable"). Formfill ({ data: row })                          //Determine event      
       $ (' #save '). Unbind ();             $ (' #save '). On (' click ',  function   (e)  {        &nbsP;       factory.ajaxsubmit (function  ()  {                     var url
 = isAdd ?  "/grid/add"  :  "/grid/edit";                   
  var type = isAdd ?  "POST"  :  "put";                      $ ("#frmtable"). Ajaxsubmit ({                         url: url,                           type: type,                         success:  function  (msg)  {                             if  (msg.issuccess  == false)  {                                
 jqxalert (Msg.respnseinfo);                              }                            
  $ ("#netgrid"). Jqxdatatable (' Updatebounddata ');                              $ ("#editbox"). Jqxwindow ("close")                          }, error: function   (msg)  {               
             console.log (msg);                          }              &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP})           
      });
            });   &NBSP;&NBSP;&NBSP;     &nbsp}               //binding validation          $ (function  ()  {             window.factory = new validatefactory ($ ("form"),  "      @Html. Raw (Model)} <div id= "Netgrid" > </div> <div id= "EditBox"  class= "Hide" >     <div class= " Savetable ">         <form id=" frmtable " class=" form " >         <table style= "table-layout: fixed;  border-style:  none; " >             <tr>                  <td align= "Right" >                        Name:                 </ Td> &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&LT;TD  align= "Left" >                      <input id= "id"  name= "id"  type= "hidden"  value= "0"  />                      <input id= "name"  name= "name"  type= "text"  />                  </td>              </tr>              <tr>                  <td align= "Right" >                       Product Name:                  </td>                  <td align= "Left" >                      <input  id= "ProductName"  name= "ProductName"  type= "text"  />                  </td>              </tr>             <tr>                  <td align= " Right ">                       Qty:                  </td>                  <td align= "Left" >                      <input id= "Quantity"  name= "Quantity"  type= "Text"  />                  </td> &NBsp;           </tr>              <tr>                  <td align= "Right" >                       Time:                  </td>                  <td align= "left" >                      <input id= "Date"  class= "Wdate"  onclick= "Wdatepicker ()"  name= "Date"  type= "Text"  />                  </td>             </tr>              <tr>                  <td>                  </td>                  <td>                      <br />                      <button  id= "Save"  type= "button" >                           Save </button>                      <button style= "Margin-left:  5px; "  type= "button"  id= "Cancel" >                           Cancellation </button>                  </td>              </tr>          </table>         </form>      </div> </div> @Html. Raw (Viewbag.validationbind)

 
This example is the most basic increase, delete, check and change. The

feature is simple but also takes into account a number of issues such as: The front-end plus the back-end of two-way authentication, strong code scalability, syntax concise, abnormal processing, and so on.