JsonStore转换SQL语句实现后台存取

      Extjs中的JsonStore可以非常方便地在客户端操作和存储数据， 服务器端多以页面方式接受数据后再转换存储。其实在RIA应用中完全可以在客户端实现SQL语句转换，发送服务器端直接存储，节省了服务端负担及资源开销。

      以下代码以JavaScript为前端，后台MS VS2005,数据库是MS SQL2000。

 

javascript代码

 

 function saveJson(cs){
   if(cs){
     Ext.Ajax.request({
      url: 'WebService.asmx/SaveA',
      jsonData: {ics:cs},
      method: 'POST',
      async: false,
      success: onSuccess,
      failure: onFailure
    });
   }

     function onSuccess(response){
        Ext.MessageBox.show({
          title: '提示',
          msg: response.responseText,
          buttons: Ext.MessageBox.OK,
          icon: Ext.MessageBox.INFO
       });
     }

     function onFailure(request){
       Ext.MessageBox.show({
         title: '错误',
         msg: request.responseText,
         buttons: Ext.MessageBox.OK,
         icon: Ext.MessageBox.ERROR
       });
     }

  }

      function JsToStr(js){
        if(js){
          var str="",DB="";
          var index=Js_DataSet.findBy(function(record,id){return record.get("Jsn")==js.storeId});
          if (index>=0){
            DB=Js_DataSet.getAt(index).get("Ta");
          }
          js.each(function(record){
            if(record.phantom==true){
              var a="INSERT INTO "+DB+" (",b=" VALUES (";
              for (var i=0,len=js.fields.length-1;i<=len;i++){
                var fi=js.fields.item(i).name,mpfi=js.fields.ite(i).mapping,idfi=js.idProperty;
                if (!mpfi){mpfi=fi;}
                if (fi!=idfi){
                  a+=mpfi;
                  b+="'"+record.get(fi)+"'";
                  if (len-i>=1){a+=",";b+=",";}
                }
              }
              len=i=fi=mpfi=null;
              a+=")";
              if(js.getCount()-js.indexOf(record)>1){b+=");";}else{b+=")";}
              str+=a+b+" ";a=b=null;
            }
            else
            {
              if(js.modified){
                var modi=js.modified;

                if (modi.length>=1){
                  var strs="",idfi=js.idProperty;
                  for (var i=0,len=modi.length-1;i<=len;i++){
                   if(modi[i].get(idfi)==record.get(idfi)){
                     var a="UPDATE "+DB+" SET ",b="",c=" WHERE ("+idfi+"="+modi[i].get(idfi)+")";
                     for (j in modi[i].modified){
                      if (j){
                        for (var k=0,l=js.fields.length-1;k<=l;k++){
                          var fi=js.fields.item(k).name,mpfi=js.fields.item(k).mapping;
                          if (!mpfi){mpfi=fi;}
                          if (j==fi){b+=mpfi+"='"+record.get(fi)+"',";}
                          fi=mpfi=null;
                        }
                      }j=k=l=null;
                     }
                     b=b.slice(0,b.length-1);
                     strs=a+b+c+" ";str+=strs;

                     a=b=c=null;
                   }
                  }
                  modi=idfi=strs=i=len=null;
                }
              }
            }
          });
          js.commitChanges();
          try{return str;}finally{str=null;}
        }
      }

 

后台服务端代码：WebService.asmx

    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Xml)]
    public string SaveA(string ics)
    {
        SqlConnection myConnection = new SqlConnection(Global.vpConString);
        myConnection.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = myConnection;
        cmd.CommandTimeout = 30;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = ics.ToString();
        cmd.ExecuteNonQuery();//这里可以根据需要定义返回错误代码
        myConnection.Close();

        return "保存成功!";
    }

 

还要说明一下

function JsToStr(js)代码中

mpfi=js.fields.item(k).mapping;

这句是指映射字段，就是指别名（懂SQL都知道的），有了映射字段可以使用SQL中文字段。

Jsonstore中不能使用中文字段,通过这个映射字段就可以使用SQL中文字段了。