easyui增删改查全部代码

//dataGrid自适应

$(window).resize(function() {

    $('#dgUser').datagrid('resize')

});

var businessTypes = [{ "value": "1","text": "电音" }, { "value": "2","text": "管乐器" }, { "value": "3","text": "吉他" }, { "value": "4","text": "钢琴" }];

var authoritys = [{ "value": "2","text": "管理者" }, { "value": "3","text": "技术" }, { "value": "5","text": "YAMAHA技术" }];

function businessTypeValue(value) {

    varvalues = new Array();

    values =value.split(',');

    varbusinessType = "";

    for(vari = 0; i < businessTypes.length; i++) {

        if(values.length > 0) {

            for(varj = 0; j < values.length; j++) {

               if (businessTypes[i].value ==values[j]) {

                   businessType += businessTypes[i].text + ",";

                }

            }

        }

        if(businessTypes[i].value == value) {

            returnbusinessTypes[i].text;

        }

    }

   businessType = businessType.substring(0, businessType.length - 1);

    returnbusinessType;

}

 

function authorityValue(value) {

    varvalues = new Array();

    values =value.split(',');

    varauthority = "";

    for(vari = 0; i < authoritys.length; i++) {

        if(values.length > 0) {

            for(varj = 0; j < values.length; j++) {

               if (authoritys[i].value == values[j]) {

                   authority += authoritys[i].text + ",";

               }

            }

        }

        if(authoritys[i].value == value) {

            returnauthoritys[i].text;

        }

    }

    authority= authority.substring(0, authority.length - 1);

    returnauthority;

}

 

$(function () {

    if($('#statusBF').val() == 1) {

        $('#zc').combobox({ selected: true});

    }

    if($('#statusBF').val() == 2) {

        $('#hmd').combobox({ selected: true});

    }

    if($('#statusBF').val() == 3) {

        $('#cx').combobox({ selected: true});

    }

    if($('#busTypes').val() == 1)

    {

        $('#P').show();

        $("#gqs").attr("checked",true);

    }

    if($('#busTypes').val() == 2) {

        $('#W').show();

        $("#gyqs").attr("checked",true);

    }

    if($('#busTypes').val() == 3) {

        $('#GT').show();

        $("#gts").attr("checked",true);

    }

    if($('#busTypes').val() == 4) {

        $('#DY').show();

        $("#dys").attr("checked",true);

    }

   

    //绑定用户管理数据

    $("#dgUser").datagrid({

       method: 'get',

       queryParams: queryParams,

        url:locationAddress + "/SysUser/UserList?rand="+ Math.random(),//加载的URL 

        pagination:true,//显示分页

       pageSize: 15,//分页大小

       singleSelect: true,//只允许选择单行

       pageList: [5, 10, 15, 20],//每页的个数 

        fit: false,//自动补全 

       fitColumns: true,

       iconCls: "icon-save",//图标 

       title: "用户信息",

        loadMsg:"正在加载,请稍等...",

       rownumbers: true,//行号

       autoRowHeight: true,//定义是否设置基于该行内容的行高度

       striped: true,//设置为 true,则把行条纹化。(即奇偶行使用不同背景色)

       columns: [[      //每个列具体内容 

                   {

                        field: 'UserId', title: 'ID', width:100

                   },

                   {

                        field: 'UserName', title: '姓名', width: 100

                   },

                   {

                        field: 'Password', title: '密码', width: 100

                    },

                   {

                        field: 'AsId', title: '公司', width: 100

                   },

                   {

                        field: 'BusinessType', title: '分类', width:100, formatter: businessTypeValue, editor: { type: 'combobox',options: { data: businessTypes, valueField: "value",textField: "text", panelHeight: 'auto', multiple: true } }

                   },

                   {

                        field: 'RoleID', title: '权限', width: 100, formatter: authorityValue,editor: { type: 'combobox', options: { data: authoritys,valueField: "value",textField: "text", panelHeight: 'auto' } }

                   },

                   {

                        field: 'Mail', title: '邮件地址', width: 100

                    },

                   {

                        field: 'id', title: '操作', width: 88, align: "center",

                        formatter: function(value, row, index) {

                            varhtml = "<a href='javascript:void(0)' οnclick=\"editeUser('"+ row.UserId + "','" + row.UserName + "','" + row.Password + "','"+ row.AsId + "','" + row.BusinessType + "','" + row.RoleID + "','"+ row.Mail + "','" + row.Remark + "')\">修改</a>&nbsp;&nbsp;&nbsp;";

                            html += "<a href='javascript:void(0)'   οnclick=\"deleUser('" + row.UserId + "')\">删除</a>";

                            returnhtml;

                        }

                   }

        ]],

       loadFilter: function (data) {

               return data;

        }

    });

 

    varqueryParams = $('#dgUser').datagrid('options').queryParams;

    //设置分页控件     

 

    varp = $('#dgUser').datagrid('getPager');

 

   $(p).pagination({

 

       beforePageText: '',//页数文本框前显示的汉字         

 

       afterPageText: '    {pages} ',

 

       displayMsg: '当前显示 {from} - {to} 条记录   {total} 条记录',

    });

 

    //查询按钮

    $("#selectUser").click(function() {

        $("#dgUser").datagrid("reload");

       queryParams["Cauthority"] = $("#cauthority").combobox('getValue');

       queryParams["BusinessType"] = $("#businessType").combobox('getValue');

       queryParams["UserId"] = $("input[name=userId]").val();

        $('#dgUser').datagrid('options').queryParams= queryParams;

        $('#dgUser').datagrid("reload");

    });

 

    //修改弹窗初始关闭

    $('#editUserDialog').dialog('close');

    $("#menuDialog").dialog("close");

});

 

 

 

//删除单条数据

function deleUser(id, UserId) {

   $.messager.confirm("删除操作", "是否继续删除操作?", function (r) {

        if(r) {

           $.get(locationAddress + "/SysUser/DeleteUser?rand="+ Math.random(), { userId: id }, function (data){

               if (data == 0)

                   $.messager.alert("删除操作", "删除失败");

               else {

                   $.messager.alert("删除操作", "删除成功");

                    $("#dgUser").datagrid("reload");

               }

           })

        }

    });

}

 

//修改单条数据

functionediteUser(userId,userName,password,asId, businessType, roleID, mail, remark) {

    $("#userId").val(userId);

    $("#userName").val(userName);

    $("#password").val(password);

    $("#asId").val(asId);

    $("#mail").val(mail);

    $("#remark").val(remark);

    $('#roleID').combobox('setValue',roleID);

    varopts = businessType.split(',');

    for(vari = 0; i < businessTypes.length; i++) {

        if(opts.length > 0) {

            for(varj = 0; j < opts.length; j++) {

               if (businessTypes[i].value == opts[j]){

                   if (businessTypes[i].value == "1")

                        $("#dy").attr("checked",true);

                   if (businessTypes[i].value == "2")

                        $("#gyq").attr("checked",true);

                   if (businessTypes[i].value == "3")

                        $("#jt").attr("checked",true);

                   if (businessTypes[i].value == "4")

                        $("#gq").attr("checked",true);

               }

            }

        }

    }

 

    $('#editUserDialog').dialog({

       title: '修改用户信息',

       width: 300,

       height: 500,

       closed: false,

       cache: false,

       modal: true,

       buttons: [{

           text: '确认修改',

           handler: function () {

               var userIdValue = $('#userId').val();

               var userNameValue = $('#userName').val();

                varpasswordValue = $('#password').val();

               var asIdValue = $('#asId').val();

               var roleIDValue = $('#roleID').combobox('getValue');

               var businessTypeValue = "";

               var obj = document.getElementsByName("businessType");

               for (vari = 0; i < obj.length; i++) {

                   if (obj[i].checked) {

                        businessTypeValue +=obj[i].value + ",";

                   }

               }

               businessTypeValue = businessTypeValue.substring(0,businessTypeValue.length - 1);

               var mailValue = $('#mail').val();

               var remarkValue = $('#remark').val();

               if (passwordValue == '')

                   $.messager.alert("操作提示", "密码不能为空");

               {

                   $.post(locationAddress + "/SysUser/UpdateUser?rand="+ Math.random(), {userId:userIdValue, userName:userNameValue,password:passwordValue,asId:asIdValue,roleID:roleIDValue,businessType: businessTypeValue, mail: mailValue, remark: remarkValue},

                        function(data) {

                           if(data > 0) {

                               $.messager.alert("操作提示", "修改成功");

 

                                $('#addUserDialog').dialog('close');

                                $("#dgUser").datagrid("reload");

                            }

                            else

                               $.messager.alert("操作提示", "修改失败");

                        });

               }

            }

        }, {

           text: '关闭',

           handler: function () { $("input[name='businessType']").attr("checked", false);$('#editUserDialog').dialog('close'); }

        }]

    });

}

 

 

 

@model Model.M00111

 

@{

   ViewBag.Title = "AsArchivesInput";

}

 

<script src="~/Scripts/AsArchivesManagement/AsArchivesInput.js"></script>

<div class="yamahaDiv">

    <div class="yamahaStyle">维修站信息</div>

    <div style="padding:10px10px10px10px;">

        <form method="post">

            <table border="0">

               <tr>

                   <td style="margin:5px0px0px20px;float:right;">名称</td>

                   <td>

                        <input class="easyui-validatebox"type="text"size="25"value="@Model.AsFullName"/>

                   </td>

                   <td style="margin:5px0px0px70px;float:right;">简称</td>

                   <td>

                        <!--<input type="text" name="name"disabled="disabled" size="25" id="jname"/></td>-->

                        <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.AsName" id="jname"/>

                   </td>

                   <td id="discount1"style="margin:5px0px0px82px;float:right;">省份</td>

                   <td width="110px">

                        <select class="easyui-combobox"style="padding-right:60px;width:162px;"panelheight="auto"id="province">

                            <option value="0">@Model.Province</option>

                            <option value="province">请选择所在省份</option>

                            <option value="zxs">直辖市 </option>

                            <option value="jss">江苏省 </option>

                            <option value="fjs">福建省 </option>

                            <option value="gds">广东省 </option>

                            <option value="gss">甘肃省 </option>

                        </select>

                   </td>

                   <td style="margin:5px0px0px20px;float:right;">城市</td>

                   <td>

                        <select class="easyui-combobox"id="city"style="padding-right:60px;width:162px;"panelheight="auto">

                            <option value="0">@Model.City</option>

                            <option value="city">请选择所在城市 </option>

                        </select>

                   </td>

                </tr>

               <tr>

                   <td style="margin:5px0px0px50px;float:right;">状态</td>

                   <td>

                        <select class="easyui-combobox"id="status"style="padding-right:60px;width:162px;"panelheight="auto">

                            <option value="1"id="zc">正常</option>

                            <option value="2"id="hmd">黑名单</option>

                            <option value="3"id="cx">撤消</option>

                        </select>

                        <input type="text"id="statusBF"value="@Model.Status" style="display:none;"/>

                   </td>

                   

                   <td style="margin:5px0px0px70px;float:right;">公司代码</td>

                   <td>

                        <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"id="jehj3"value="@Model.AsId" />

                   </td>

                   <td style="margin:5px0px0px60px;float:right;">类别</td>

                   <td>

                        <select class="easyui-combobox"name="language"style="padding-right:60px;width:162px;"panelheight="auto"id="asType">

                            <option value="1">经销商</option>

                            <option value="2">维修站</option>

                            <option value="3">YAMAHA本社</option>

                        </select>

                   </td>

               </tr>

 

               <tr>

                   <td style="margin:5px0px0px49px;float:right;">开户行</td>

                   <td>

                        <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"id="jehj"value="@Model.Bank" />

                   </td>

                   <td style="margin:5px0px0px70px;float:right;">账号</td>

                   <td>

                        <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"id="dd"value="@Model.BankAccount"/>

                   </td>

                   <td style="margin:5px0px0px60px;float:right;">税号</td>

                   <td colspan="3">

                        <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"id="kpje"value="@Model.TaxNo" />

                   </td>

               </tr>

               <tr>

                   <td style="margin:5px0px0px49px;float:right;">黑名单原因</td>

                   <td colspan="3">

                        <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="79"id="wxbz"value="@Model.BlacklistReasons"/>

                   </td>

                    <td style="margin:5px0px0px60px;float:right;">业务种类</td>

                   <td colspan="3">

                        <input type="checkbox"name="businessTypes"value="1"id="dys"style="float:left;margin-top:7px;"/><span style="padding-top:5px;padding-right:4px;float:left;">电音</span>

                        <input type="checkbox"name="businessTypes"value="2"id="gyqs"style="float:left;margin-top:7px;"/><span style="padding-top:5px;padding-right:4px;float:left;">管乐器</span>

                        <input type="checkbox"name="businessTypes"value="3"id="jts"style="float:left;margin-top:7px;"/><span style="padding-top:5px;padding-right:4px;float:left;">吉他</span>

                        <input type="checkbox"name="businessTypes"value="4"id="gqs"style="float:left;margin-top:7px;"/><span style="padding-top:5px;padding-right:4px;float:left;">钢琴</span>

                   </td>

                   <td><input type="text"id="busTypes"value="@Model.BusinessType"  style="display:none;"/></td>

               </tr>

               <tr>

                   <td style="margin:5px0px0px50px;float:right;">备注</td>

                   <td colspan="3">

                        <input class="easyui-validatebox"type="text"data-options="required:true"size="79"id="jehj2"value="@Model.Remark" />

                   </td>

               </tr>

 

            </table>

        </form>

    </div>

</div>

 

<div class="yamahaDiv"id="P"hidden="hidden">

    <div class="yamahaStyle">钢琴</div>

    <div style="padding:10px10px10px10px;">

        <table border="0">

            <tr>

               <td style="margin:5px0px0px86px;float:right;">代码</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Code" />

               </td>

               <td style="margin:5px0px0px105px;float:right;">类别</td>

               <td>

                   <select class="easyui-combobox"name="language"style="padding-right:60px;width:162px;"data-options="multiple:true,disabled:true"panelheight="auto">

                        <option value="W">管乐器</option>

                        <option value="DY">电音</option>

                        <option value="P"selected="selected">钢琴</option>

                        <option value="GT">吉他</option>

                   </select>

               </td>

               <td style="margin:5px0px0px78px;float:right;">建站日期</td>

               <td width="110px">

                   <input class="easyui-datebox"data-options=""size="25"value="@Model.EstablishmentDate"/>

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">等级</td>

               <td>

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"value="@Model.Grade" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">状态</td>

               <td>

                   <input class="easyui-validatebox"type="text"id="pstatus"data-options="required:true"size="25"value="@Model.Status" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">电话</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Phone" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">地址</td>

               <td colspan="3">

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="78"value="@Model.Address" />

               </td>

            </tr>

            <tr>

 

               <td style="margin:5px0px0px0px;float:right;">传真</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Fax" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮编</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.ZipCode" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮件</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mail" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">经理</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Manager" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">负责人</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.PersonInCharge"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">手机</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mobile" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">维修员1</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance1"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">维修员2</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance2"/>

               </td>

            </tr>

        </table>

    </div>

</div>

 

AsArchivesInput.cshtml

<div class="yamahaDiv"id="W"hidden="hidden">

    <div class="yamahaStyle">管乐器</div>

    <div style="padding:10px10px10px10px;">

        <table border="0">

            <tr>

               <td style="margin:5px0px0px86px;float:right;">代码</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Code" />

               </td>

               <td style="margin:5px0px0px105px;float:right;">类别</td>

               <td>

                   <select class="easyui-combobox"name="language"style="padding-right:60px;width:162px;"data-options="multiple:true,disabled:true"panelheight="auto">

                         <option value="W"selected="selected">管乐器</option>

                        <option value="DY">电音</option>

                        <option value="P">钢琴</option>

                        <option value="GT">吉他</option>

                   </select>

               </td>

               <td style="margin:5px0px0px78px;float:right;">建站日期</td>

               <td width="110px">

                   <input class="easyui-datebox"data-options=""size="25"value="@Model.EstablishmentDate"/>

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">等级</td>

               <td>

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"value="@Model.Grade" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">状态</td>

               <td>

                   <input class="easyui-validatebox"type="text"id="wstatus"data-options="required:true"size="25"  value="@Model.Status"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">电话</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Phone" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">地址</td>

               <td colspan="3">

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="78"value="@Model.Address" />

               </td>

            </tr>

            <tr>

 

               <td style="margin:5px0px0px0px;float:right;">传真</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Fax" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮编</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.ZipCode" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮件</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mail" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">经理</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Manager" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">负责人</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.PersonInCharge"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">手机</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mobile" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">维修员1</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance1"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">维修员2</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance2"/>

               </td>

            </tr>

        </table>

    </div>

</div>

 

 

<div class="yamahaDiv"id="GT"hidden="hidden">

    <div class="yamahaStyle">吉他</div>

    <div style="padding:10px10px10px10px;">

        <table border="0">

            <tr>

               <td style="margin:5px0px0px86px;float:right;">代码</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Code" />

               </td>

               <td style="margin:5px0px0px105px;float:right;">类别</td>

               <td>

                   <select class="easyui-combobox"name="language"style="padding-right:60px;width:162px;"data-options="multiple:true,disabled:true"panelheight="auto">

                        <option value="W">管乐器</option>

                        <option value="DY">电音</option>

                        <option value="P">钢琴</option>

                        <option value="GT"selected="selected">吉他</option>

                   </select>

               </td>

               <td style="margin:5px0px0px78px;float:right;">建站日期</td>

               <td width="110px">

                   <input class="easyui-datebox"data-options=""size="25"value="@Model.EstablishmentDate"/>

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">等级</td>

               <td>

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"value="@Model.Grade" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">状态</td>

               <td>

                   <input class="easyui-validatebox"type="text"id="gtstatus"data-options="required:true"size="25"value="@Model.Status" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">电话</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Phone" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">地址</td>

               <td colspan="3">

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="78"value="@Model.Address" />

               </td>

            </tr>

            <tr>

 

               <td style="margin:5px0px0px0px;float:right;">传真</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Fax" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮编</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.ZipCode" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮件</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mail" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">经理</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Manager" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">负责人</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.PersonInCharge"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">手机</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mobile" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">维修员1</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance1"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">维修员2</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance2"/>

               </td>

            </tr>

        </table>

    </div>

</div>

 

 

<div class="yamahaDiv"id="DY"hidden="hidden">

    <div class="yamahaStyle">电音</div>

    <div style="padding:10px10px10px10px;">

        <table border="0">

            <tr>

               <td style="margin:5px0px0px86px;float:right;">代码</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Code" />

               </td>

               <td style="margin:5px0px0px105px;float:right;">类别</td>

               <td>

                   <select class="easyui-combobox"name="language"style="padding-right:60px;width:162px;"data-options="multiple:true,disabled:true"panelheight="auto">

                        <option value="W">管乐器</option>

                        <option value="DY"selected="selected">电音</option>

                        <option value="W">钢琴</option>

                        <option value="GT">吉他</option>

                   </select>

               </td>

               <td style="margin:5px0px0px78px;float:right;">建站日期</td>

               <td width="110px">

                   <input class="easyui-datebox"data-options=""size="25"value="@Model.EstablishmentDate"/>

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">等级</td>

               <td>

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="25"value="@Model.Grade" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">状态</td>

               <td>

                   <input class="easyui-validatebox"type="text"id="dystatus"data-options="required:true"size="25"  value="@Model.Status"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">电话</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Phone" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">地址</td>

               <td colspan="3">

                   <input class="easyui-validatebox"type="text"name="name"data-options="required:true"size="78"value="@Model.Address" />

               </td>

            </tr>

            <tr>

 

               <td style="margin:5px0px0px0px;float:right;">传真</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Fax" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮编</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.ZipCode" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">邮件</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mail" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">经理</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Manager" />

               </td>

               <td style="margin:5px0px0px0px;float:right;">负责人</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.PersonInCharge"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">手机</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Mobile" />

               </td>

            </tr>

            <tr>

               <td style="margin:5px0px0px0px;float:right;">维修员1</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance1"/>

               </td>

               <td style="margin:5px0px0px0px;float:right;">维修员2</td>

               <td>

                   <input class="easyui-validatebox"type="text"data-options="required:true"size="25"value="@Model.Maintenance2"/>

               </td>

            </tr>

        </table>

    </div>

</div>

 

<div id="editUserDialog"class="easyui-dialog">

    <form method="post"action="">

            <table>

                <tr>

                    <td style="margin:15px0px10px10px;float:right">ID</td>

               <td><input class="easyui-textbox"  id="userId"type="text"size="31"disabled="disabled"/></td>

               </tr>

 

                <tr>

                    <td style="margin:15px0px10px10px;float:right">姓名</td>

               <td><input class="easyui-textbox"  id="userName"type="text"size="31"/></td>

               </tr>

 

               <tr>

                    <td style="margin:15px0px10px10px;float:right">密码</td>

               <td><input class="easyui-textbox"  id="password"type="text"size="31"/></td>

               </tr>

 

                <tr>

                    <td style="margin:15px0px10px10px;float:right">公司</td>

               <td><input class="easyui-textbox"  id="asId"type="text"size="31"disabled="disabled"/></td>

               </tr>

 

               <tr>

                   <td style="padding:15px0px10px10px;float:right">分类</td>

                   <td>

                        <div class="easyui-panel" style="width:inherit;margin-top:0px;height:79px;">

                        <input type="checkbox"name="businessType"value="1"id="dy"/>电音<br/>

                        <input type="checkbox"name="businessType"value="2"id="gyq"/>管乐器<br/>

                        <input type="checkbox"name="businessType"value="3"id="jt"/>吉他<br/>

                        <input type="checkbox"name="businessType"value="4"id="gq"/>钢琴<br/>

                            </div>

                   </td>

               </tr>

               <tr>

                   <td style="padding:15px0px10px10px;float:right">权限</td>

                   <td>

                         @*data-options="multiple: true"*@

                        <select class="easyui-combobox"panelheight="auto"style="width:197px"id="roleID">

                        <option value="2"id="glz">管理者</option>

                        <option value="3"id="js">技术</option>

                        <option value="5"id="yamahajs">YAMAHA技术</option>

                   </select>

                   </td>

               </tr>

               <tr>

                    <td style="padding:15px0px10px10px;float:right">邮件地址</td>

               <td><input class="easyui-textbox"name="mail"id="mail"type="text"size="31"/></td>

               </tr>

 

               <tr>

                    <td style="padding:15px0px10px10px;float:right">备注</td>

               <td><input class="easyui-textbox"name="remark"id="remark"type="text"size="31"/></td>

               </tr>

            </table>

        </form>

</div>

 

<div id="menuDialog"class="easyui-dialog">

    <ul id="menuTree"></ul>

</div>

 

<div class="yamahaDiv">

    <div class="yamahaStyle">用户</div>

    <div style="padding:10px10px10px10px;">

        <table id="dgUser"data-options="

                rownumbers:true,

                singleSelect:false,

               toolbar: '#tb',

                autoRowHeight:false,

                pagination:false,

               checkOnSelect:false,

               iconCls: 'icon-edit',

               checkOnSelect: false,

               selectOnCheck: false,

                pageSize:10">

        </table>

        <div id="tb"style="height:auto">

            <table>

            <tr>

               <td>用户名</td>

               <td><input class="easyui-textbox"name="userId"type="text"/></td>

              <td style="float:right;padding:5px0030px">业务种类</td>

                   <td>

                        <select class="easyui-combobox"panelheight="auto"style="width:133px"id="businessType">

                            <option value="0">-------请选择-------</option>

                        <option value="1"id="cdy">电音</option>

                        <option value="2"id="cgyq">管乐器</option>

                        <option value="3"id="cjt">吉他</option>

                        <option value="4"id="cgq">钢琴</option>

                   </select>

                   </td>

               <td style="float:right;padding:5px0030px">权限</td>

                   <td>

                        <select class="easyui-combobox"panelheight="auto"style="width:133px"id="cauthority">

                            <option value="0">-------请选择-------</option>

                        <option value="2"id="cglz">管理者</option>

                        <option value="3"id="cjs">技术</option>

                        <option value="5"id="cyamahajs">YAMAHA技术</option>

                   </select>

                   </td>

               <td>

                   <a id="selectUser"href="javascript:void(0)"class="easyui-linkbutton"data-options="iconCls:'icon-search'">查询</a>

               </td>

            </tr>

        </table>

        </div>

    </div>

</div>

 

 

 

AsArchivesManagementController.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text.RegularExpressions;

using System.Web;

using System.Web.Mvc;

using System.Web.Script.Serialization;

using System.Windows.Forms;

using BLL;

using DAL;

using Model;

 

namespace WebMVC.Controllers

{

    [Authorize]

    publicclassAsArchivesManagementController : Controller

    {

       #region

        //GET: /AsArchivesManagement/

        ///<summary>

        ///AS站档案管理

        ///</summary>

        ///<returns></returns>

        publicActionResult AsArchivesManagement()

        {

            returnView();

        }

 

        ///<summary>

        ///AS站档案管理列表

        ///</summary>

        ///<paramname="aSType"></param>

        ///<paramname="code"></param>

        ///<paramname="productType"></param>

        ///<returns></returns>

        publicstringAsArchivesManagementList(string aSType,stringcode, string productType)

        {

            intpageIndex = string.IsNullOrEmpty(Request["page"]) ? 0 : Convert.ToInt32(Request["page"]);

            intpageSize = string.IsNullOrEmpty(Request["rows"]) ? 0 : Convert.ToInt32(Request["rows"]);

            intcount = 0;

 

            List<M00111>list =AsArchivesManagementBLL.PageASManage(aSType,code, productType, pageIndex, pageSize, refcount);

            stringresult = string.Empty;

 

            if(list != null)

            {

               var obj = new{ total = count, rows = list };

               result = new JavaScriptSerializer().Serialize(obj);

               result = Regex.Replace(result, @"\\/Date\((\d+)\)\\/", match =>

               {

                   DateTime dt = newDateTime(1970, 1, 1);

                   dt = dt.AddMilliseconds(long.Parse(match.Groups[1].Value));

                   dt = dt.ToLocalTime();

                   return dt.ToString("yyyy-MM-ddHH:mm:ss");

               });

            }

            returnresult;

        }

 

        ///<summary>

        ///AS站档案管理-输入画面

        ///</summary>

        ///<returns></returns>

        publicActionResult AsArchivesInput(stringasId,string businessType)

        {

            List<M00111>asManagement = AsArchivesInputShow(asId, businessType);

            M00111 m = new M00111();

           m.Address = asManagement[0].Address;

           m.AsFullName = asManagement[0].AsFullName;

           m.AsId = asManagement[0].AsId;

           m.AsName = asManagement[0].AsName;

            m.ASType = asManagement[0].ASType;

           m.Bank = asManagement[0].Bank;

           m.BankAccount = asManagement[0].BankAccount;

           m.BlacklistReasons = asManagement[0].BlacklistReasons;

           m.BusinessType = asManagement[0].BusinessType;

           m.City = asManagement[0].City;

           m.Code = asManagement[0].Code;

           m.EstablishmentDate = asManagement[0].EstablishmentDate;

           m.Fax = asManagement[0].Fax;

           m.Grade = asManagement[0].Grade;

           m.InvoiceType = asManagement[0].InvoiceType;

           m.Mail = asManagement[0].Mail;

           m.Maintenance1 = asManagement[0].Maintenance1;

           m.Maintenance2 = asManagement[0].Maintenance2;

           m.Manager = asManagement[0].Manager;

           m.Mobile = asManagement[0].Mobile;

           m.PersonInCharge = asManagement[0].PersonInCharge;

           m.Phone = asManagement[0].Phone;

           m.ProductType = asManagement[0].ProductType;

           m.Province = asManagement[0].Province;

           m.Remark = asManagement[0].Remark;

           m.Status = asManagement[0].Status;

           m.TaxNo = asManagement[0].TaxNo;

           m.ZipCode = asManagement[0].ZipCode;

            returnView(m);

        }

 

        publicList<M00111>AsArchivesInputShow(string asId,stringbusinessType)

        {

            intpageIndex = string.IsNullOrEmpty(Request["page"]) ? 0 : Convert.ToInt32(Request["page"]);

            intpageSize = string.IsNullOrEmpty(Request["rows"]) ? 0 : Convert.ToInt32(Request["rows"]);

            intcount = 0;

 

            List<M00111>list=AsArchivesManagementBLL.PageAsShowManage(asId,businessType, pageIndex, pageSize, ref count);

            stringresult = string.Empty;

 

            if(list != null)

            {

               var obj = new{ total = count, rows = list };

               result = new JavaScriptSerializer().Serialize(obj);

               result = Regex.Replace(result, @"\\/Date\((\d+)\)\\/", match =>

               {

                   DateTime dt = newDateTime(1970, 1, 1);

                   dt = dt.AddMilliseconds(long.Parse(match.Groups[1].Value));

                   dt = dt.ToLocalTime();

                   return dt.ToString("yyyy-MM-ddHH:mm:ss");

                });

            }

            returnlist;

        }

       #endregion

    }

}

 

 

 

AsArchivesManagementBLL.cs

using Command;

using Model;

using DAL;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace BLL

{

    publicclassAsArchivesManagementBLL

    {

        ///<summary>

        ///分页获取档案管理信息

        ///</summary>

        ///<paramname="pageIndex"></param>

        ///<paramname="pageSize"></param>

        ///<returns></returns>

        publicstaticList<M00111>PageASManage(string aSType, stringcode, string productType, intpageIndex, int pageSize, refintcount)

        {

            stringwheresql = string.Empty;

            stringwheresql1 = string.Empty;

            if(!string.IsNullOrEmpty(aSType))

               wheresql1 += " and ASType like '%"+ aSType + "%'";

            if(!string.IsNullOrEmpty(code))

               wheresql += " and Code like '%"+ code + "%'";

 

            if(!string.IsNullOrEmpty(productType)&& productType != "0")

               wheresql += " and ProductType like '%"+ productType + "%'";

 

            //string[]roleIDs = roleID.Split(new char[]{','},StringSplitOptions.RemoveEmptyEntries);

            //默认只显示有效状态的角色信息

           wheresql += " and DeleteFlg=0 ";

            List<M00111>list = AsArchivesManagementDAL.Page<M00111>(aSType, code, productType, pageIndex, pageSize, refcount);

            returnlist;

        }

 

        ///<summary>

        ///显示AS档案信息

        ///</summary>

        ///<paramname="asId"></param>

        ///<paramname="businessType"></param>

        ///<paramname="pageIndex"></param>

        ///<paramname="pageSize"></param>

        ///<paramname="count"></param>

        ///<returns></returns>

        publicstaticList<M00111>PageAsShowManage(string asId, stringbusinessType, int pageIndex, intpageSize, ref intcount)

        {

            List<M00111>list = null;

            //默认只显示有效状态的角色信息

           list = AsArchivesManagementDAL.PageShow<M00111>(asId, businessType, pageIndex, pageSize, refcount);

            returnlist;

        }

    }

}

 

 

AsArchivesManagementDAL.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Model;

using Command;

using System.Data;

using System.Data.SqlClient;

using System.ServiceModel.Web; //用户把json转化为实体

using System.Runtime.Serialization.Json;//用户把json转化为实体

using System.IO;

 

 

namespace DAL

{

    publicclassAsArchivesManagementDAL

    {

        ///<summary>

        ///分页获取角色权限

        ///</summary>

        ///<paramname="pageIndex"></param>

        ///<paramname="pageSize"></param>

        ///<returns></returns>

        publicstaticList<M00111> Page<T>(stringaSType, string code, stringproductType, int pageIndex, intpageSize, ref intcount)

        {

            stringsql = "select a.*,b.* from ASStation as a joinASStation_ch as b on a.asid=b.asid";

            returnSqlHelper.ExecuteList<M00111>(SqlHelper.DBConnection, CommandType.Text,sql, null);

        }

 

        publicstaticList<M00111>PageShow<T>(string asId, stringbusinessType, int pageIndex, intpageSize, ref intcount)

        {

            stringsql = "select a.*,b.* from ASStation as a joinASStation_ch as b on a.asid=b.asid where b.asid='" + asId+ "'";

            returnSqlHelper.ExecuteList<M00111>(SqlHelper.DBConnection, CommandType.Text,sql, null);

        }

 

        ///<summary>

        ///JSON字符串还原为对象

        ///</summary>

        ///<typeparamname="T">对象类型</typeparam>

        ///<paramname="szJson">JSON字符串</param>

        ///<returns>对象实体</returns>

        publicstaticM00111 ParseFormJson<M00111>(string szJson)

        {

           M00111 obj = Activator.CreateInstance<M00111>();

            using(MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(szJson)))

            {

               DataContractJsonSerializer dcj = newDataContractJsonSerializer(typeof(M00111));

               return (M00111)dcj.ReadObject(ms);

            }

        }

    }

}

 

 

SqlHelper.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Reflection;

 

namespace Command

{

    ///<summary>

 

    ///基于MySQL的数据层基类

 

    ///</summary>

 

    ///<remarks>

 

    ///参考于MS Petshop 4.0

 

    ///</remarks>

 

    publicabstractclassSqlHelper

    {

 

       #region 数据库连接字符串

        //要插入的实体库地址

        publicstaticreadonlystringDBConnection = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ToString();

      #endregion

 

       #region PrepareCommand

 

        ///<summary>

 

        ///Command预处理

 

        ///</summary>

 

        ///<paramname="conn">SqlConnection对象</param>

 

        ///<paramname="trans">SqlTransaction对象,可为null</param>

 

        ///<paramname="cmd">SqlCommand对象</param>

 

        ///<paramname="cmdType">CommandType,存储过程或命令行</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组,可为null</param>

 

        privatestaticvoidPrepareCommand(SqlConnection conn, SqlTransactiontrans, SqlCommand cmd, CommandTypecmdType, string cmdText, SqlParameter[]cmdParms)

        {

 

            if(conn.State != ConnectionState.Open)

 

               conn.Open();

 

           cmd.Connection = conn;

 

           cmd.CommandText = cmdText;

 

            if(trans != null)

 

               cmd.Transaction = trans;

 

           cmd.CommandType = cmdType;

           cmd.CommandTimeout = 240;

 

            if(cmdParms != null)

            {

 

               foreach (SqlParameterparm in cmdParms)

 

                   cmd.Parameters.Add(parm);

 

            }

 

        }

       #endregion

 

       #region ExecuteNonQuery

 

        ///<summary>

 

        ///执行命令

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组</param>

 

        ///<returns>返回受引响的记录行数</returns>

 

        publicstaticintExecuteNonQuery(string connectionString, CommandType cmdType, stringcmdText, params SqlParameter[]cmdParms)

        {

 

            SqlCommand cmd = new SqlCommand();

 

            using(SqlConnection conn = new SqlConnection(connectionString))

            {

 

               PrepareCommand(conn, null, cmd,cmdType, cmdText, cmdParms);

 

               int val = cmd.ExecuteNonQuery();

 

               cmd.Parameters.Clear();

 

               return val;

 

            }

 

        }

 

 

 

 

 

        ///<summary>

 

        ///执行命令

 

        ///</summary>

 

        ///<paramname="conn">Connection对象</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组</param>

 

        ///<returns>返回受引响的记录行数</returns>

 

        publicstaticintExecuteNonQuery(SqlConnection conn, CommandType cmdType, stringcmdText, params SqlParameter[]cmdParms)

        {

 

            SqlCommand cmd = new SqlCommand();

 

           PrepareCommand(conn, null, cmd,cmdType, cmdText, cmdParms);

 

            intval = cmd.ExecuteNonQuery();

 

           cmd.Parameters.Clear();

 

            returnval;

 

        }

 

        ///<summary>

 

        ///执行事务

 

        ///</summary>

 

        ///<paramname="trans">SqlTransaction对象</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组</param>

 

        ///<returns>返回受引响的记录行数</returns>

 

        publicstaticintExecuteNonQuery(SqlTransaction trans, CommandType cmdType, stringcmdText, params SqlParameter[]cmdParms)

        {

 

            SqlCommand cmd = new SqlCommand();

 

           PrepareCommand(trans.Connection, trans, cmd, cmdType, cmdText,cmdParms);

 

            intval = cmd.ExecuteNonQuery();

 

           cmd.Parameters.Clear();

 

            returnval;

 

        }

 

       #endregion

 

       #region ExecuteScalar

 

        ///<summary>

 

        ///执行命令,返回第一行第一列的值

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组</param>

 

        ///<returns>返回Object对象</returns>

 

        publicstaticobjectExecuteScalar(string connectionString, CommandType cmdType, stringcmdText, params SqlParameter[]cmdParms)

        {

 

            SqlCommandcmd = new SqlCommand();

 

            using(SqlConnection connection = newSqlConnection(connectionString))

            {

 

               PrepareCommand(connection, null, cmd,cmdType, cmdText, cmdParms);

 

               object val = cmd.ExecuteScalar();

 

               cmd.Parameters.Clear();

 

               return val;

 

            }

 

        }

 

        ///<summary>

 

        ///执行命令,返回第一行第一列的值

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组</param>

 

        ///<returns>返回Object对象</returns>

 

        publicstaticobjectExecuteScalar(SqlConnection conn, CommandTypecmdType, string cmdText, paramsSqlParameter[] cmdParms)

        {

 

            SqlCommand cmd = new SqlCommand();

 

           PrepareCommand(conn, null, cmd,cmdType, cmdText, cmdParms);

 

            objectval = cmd.ExecuteScalar();

 

           cmd.Parameters.Clear();

 

            returnval;

 

        }

 

       #endregion

 

       #region ExecuteReader

 

        ///<summary>

 

        ///执行命令或存储过程,返回SqlDataReader对象

 

        ///注意SqlDataReader对象使用完后必须Close以释放SqlConnection资源

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组</param>

 

        ///<returns></returns>

 

        publicstaticSqlDataReader ExecuteReader(stringconnectionString, CommandTypecmdType, string cmdText, paramsSqlParameter[] cmdParms)

        {

 

            SqlCommand cmd = new SqlCommand();

 

            SqlConnection conn = new SqlConnection(connectionString);

 

            try

            {

 

               PrepareCommand(conn, null, cmd,cmdType, cmdText, cmdParms);

 

               SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

 

               cmd.Parameters.Clear();

 

               return dr;

 

            }

 

            catch

            {

 

               conn.Close();

 

               throw;

 

            }

 

        }

 

       #endregion

 

       #region ExecuteDataSet

 

        ///<summary>

 

        ///执行命令或存储过程,返回DataSet对象

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组(可为null)</param>

 

        ///<returns></returns>

 

        publicstaticDataSet ExecuteDataSet(stringconnectionString, CommandTypecmdType, string cmdText, paramsSqlParameter[] cmdParms)

        {

 

            SqlCommand cmd = new SqlCommand();

 

            using(SqlConnection conn = new SqlConnection(connectionString))

            {

 

               PrepareCommand(conn, null, cmd,cmdType, cmdText, cmdParms);

 

               SqlDataAdapter da = newSqlDataAdapter(cmd);

 

               DataSet ds = newDataSet();

 

               da.Fill(ds);

 

               conn.Close();

 

               cmd.Parameters.Clear();

 

               return ds;

 

            }

 

        }

 

        #endregion

 

       #region ExecuteDataTable

 

        ///<summary>

 

        ///执行命令或存储过程,返回DataTable对象

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组(可为null)</param>

 

        ///<returns></returns>

 

        publicstaticDataTable ExecuteDataTable(stringconnectionString, CommandTypecmdType, string cmdText, paramsSqlParameter[] cmdParms)

        {

            DataSet dataset = ExecuteDataSet(connectionString, cmdType,cmdText, cmdParms);

            if(dataset != null && dataset.Tables.Count >0)

               return dataset.Tables[0];

            else

               return null;

        }

 

       #endregion

 

       #region ExecuteList

 

        ///<summary>

 

        ///执行命令或存储过程,返回ExecuteList对象

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组(可为null)</param>

 

        ///<returns></returns>

 

        publicstaticList<T> ExecuteList<T>(stringconnectionString, CommandTypecmdType, string cmdText,

            paramsSqlParameter[] cmdParms) whereT : new()

        {

            DataTable table = ExecuteDataTable(connectionString, cmdType,cmdText, cmdParms);

            if(table != null && table.Rows.Count > 0)

               return ToList<T>(table);

            else

               return null;

        }

 

       #endregion

 

       #region ExecuteList

 

        ///<summary>

 

        ///执行命令或存储过程,返回ExecuteList对象

 

        ///</summary>

 

        ///<paramname="connectionString">数据库连接字符串</param>

 

        ///<paramname="cmdType">命令类型(存储过程或SQL语句)</param>

 

        ///<paramname="cmdText">SQL语句或存储过程名</param>

 

        ///<paramname="cmdParms">SqlCommand参数数组(可为null)</param>

 

        ///<returns></returns>

 

        publicstaticT ExecuteModel<T>(stringconnectionString, CommandTypecmdType, string cmdText,

            paramsSqlParameter[] cmdParms) whereT : new()

        {

            List<T> list = ExecuteList<T>(connectionString,cmdType, cmdText, cmdParms);

            if(list != null && list.Count > 0)

               return list[0];

            else

               return default(T);

        }

 

       #endregion

 

        publicstaticList<T> ToList<T>(DataTabledt) where T : new()

        {

            //定义集合

            List<T> ts = new List<T>();

 

            //获得此模型的类型

            Type type = typeof(T);

 

            stringtempName = "";

 

            foreach(DataRow dr indt.Rows)

            {

               T t = new T();

 

               // 获得此模型的公共属性

               PropertyInfo[] propertys =t.GetType().GetProperties();

 

               foreach (PropertyInfop in propertys)

               {

                   tempName = p.Name;

 

                    // 检查DataTable是否包含此列

                   if (dt.Columns.Contains(tempName))

                   {

                        //判断此属性是否有Setter

                        if(!p.CanWrite) continue;

 

                        objectvalue = dr[tempName];

                        if(value != DBNull.Value)

                        {

                            if(p.PropertyType == typeof(string))

                            {

                                p.SetValue(t,value, null);

                            }

                            elseif(p.PropertyType == typeof(int))

                            {

                                p.SetValue(t, int.Parse(value.ToString()),null);

                            }

                            elseif(p.PropertyType == typeof(DateTime))

                            {

                                p.SetValue(t, Convert.ToDateTime(value.ToString()), null);

                            }

                            elseif(p.PropertyType == typeof(float))

                            {

                                p.SetValue(t, float.Parse(value.ToString()),null);

                            }

                            elseif(p.PropertyType == typeof(double))

                            {

                                p.SetValue(t, double.Parse(value.ToString()),null);

                            }

                        }

                   }

               }

 

               ts.Add(t);

            }

 

            returnts;

 

        }

    }

}

 

 

ToolsHelper.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Reflection;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

 

namespace Command

{

    publicclassToolsHelper

    {

       #region 根据表名获取主键名称

        ///<summary>

        ///根据表名获取主键名称

        ///</summary>

        ///<paramname="tableName">表名称</param>

        ///<returns>主键列名称</returns>

        privatestaticstringGetPK(string tableName)

        {

            StringBuilder sql = new StringBuilder();

           sql.Append(" SELECT SYSCOLUMNS.name ");

           sql.Append(" FROMSYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS ");

           sql.Append(" WHERE SYSCOLUMNS.id = object_id('"+ tableName + "') ");//syscolumns.id为该列所属的表对象ID

           sql.Append(" AND SYSOBJECTS.xtype = 'PK'");//sysobjects.xtype对象类型

           sql.Append(" AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id");

           sql.Append(" AND SYSINDEXES.id = SYSCOLUMNS.id ");

           sql.Append(" AND SYSOBJECTS.name = SYSINDEXES.name ");

           sql.Append(" AND SYSINDEXKEYS.id = SYSCOLUMNS.id ");

           sql.Append(" AND SYSINDEXKEYS.indid = SYSINDEXES.indid");//同一表的同一列,可能建有不同类型的索引

           sql.Append(" AND SYSCOLUMNS.colid =SYSINDEXKEYS.colid");

            objectobj = SqlHelper.ExecuteScalar(SqlHelper.DBConnection, CommandType.Text,sql.ToString(), null);

            if(obj != null)

               return obj.ToString();

            else

                return "";

        }

       #endregion

 

       #region 获取指定表名的自增列名

        ///<summary>

        ///根据表名获取自增列名称

        ///</summary>

        ///<paramname="tableName">指定的表名</param>

        ///<returns></returns>

        privatestaticstringGetIdentity(string tableName)

        {

            stringresult = string.Empty;//接收返回的自增列字段名

            stringsql = "SELECT COLUMN_NAME FROMINFORMATION_SCHEMA.columns";

           sql += " WHERE TABLE_NAME='" +tableName + "' AND COLUMNPROPERTY( ";

           sql += " OBJECT_ID('" +tableName + "'),COLUMN_NAME,'IsIdentity')=1";

            try

            {

               result = SqlHelper.ExecuteScalar(SqlHelper.DBConnection, CommandType.Text,sql, null).ToString();

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnresult;

        }

       #endregion

 

       #region 动态把数据字典Dictionary中数据组合成SQL中Where条件字符串

        ///<summary>

        ///动态把数据字典Dictionary中数据组合成SQLWhere条件字符串

        ///</summary>

        ///<paramname="dictionary">条件数据字典</param>

        ///<returns>返回SQLWhere条件字符串</returns>

        privatestaticstringConvertDictionaryToWhereSql(Dictionary<string,dynamic>dictionary)

        {

            stringwhereSql = string.Empty;

            try

            {

               if (dictionary != null&& dictionary.Count > 0)

               {

                   foreach (stringkey in dictionary.Keys)

                   {

                       dynamicvalue = dictionary[key];

                        if((value is int)|| (value is double)|| (value is float))

                            whereSql += " and " + key + "="+ value + " ";

                        elseif(value is string)

                        {

                            //如果传入的字典key中包含like表明进行模糊查询

                            if(key.ToUpper().Contains(("_like").ToUpper()))

                                whereSql += " and " + key.Replace("_like", "").Replace("_LIKE", "")+ " like '%" + value+ "%'";

                            else

                                whereSql += " and " + key + "='"+ value + "' ";

                        }

                        elseif(value is DateTime)

                            whereSql += " and " + key + "='"+ value + "' ";

                   }

               }

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog("方法:GetByWhereSqlDictionary)类型转换错误:" +whereSql, ex);

            }

            returnwhereSql;

        }

       #endregion

 

       #region 拼接判断记录是否存在sql语句

 

        ///<summary>

        ///拼接判断记录是否存在sql语句

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="info">实体对象</param>

        ///<paramname="cellNames">进行判断条件字段,如(name,age)</param>

        ///<returns>返回拼接后的语句</returns>

        privatestaticstringGetExitSql<T>(T info, stringcellNames) where T:class,new()

        {

            //判断该记录是否存在

            StringBuilder isExistSql = newStringBuilder();

           isExistSql.Append(" if not exists (select * from" + info.GetType().Name + "where 1=1  ");

            PropertyInfo[] propertys = info.GetType().GetProperties();

            string[]cellArray = cellNames.Split(new char[]{ ',' }, StringSplitOptions.RemoveEmptyEntries);

 

            foreach(PropertyInfo p inpropertys)

            {

               foreach (stringname in cellArray)

               {

                   if (p.Name.ToLower() ==name.Trim().ToLower())

                   {

                        if(p.PropertyType == typeof(string)||

                            (p.PropertyType == typeof(DateTime) || p.PropertyType == typeof(Nullable<System.DateTime>)))

                        {

                            isExistSql.Append(" and " + name + "='"+ p.GetValue(info, null) + "'");

                        }

                        else

                        {

                            isExistSql.Append(" and " + name + "="+ p.GetValue(info, null) + "");

                        }

                   }

               }

            }

           isExistSql.Append(" ) ");

            returnisExistSql.ToString();

        }

       #endregion

 

       #region 添加数据公共方法

 

 

 

        ///<summary>

        ///根据传入的对象动态获取插入一条的sql语句

        ///</summary>

        ///<typeparamname="T"></typeparam>

        ///<paramname="info"></param>

        ///<returns></returns>

        privatestaticstringGetInsertSql<T>(T info, string identity)whereT : new()

        {

           

            StringBuilder sqlHeader = newStringBuilder(" insert into ");

            StringBuilder sqlMain = newStringBuilder(" values(");

            if(info != null)

            {

               //如果是插入单条数据这里会传入空值

               if (string.IsNullOrEmpty(identity))

                   identity = GetIdentity(info.GetType().Name);

               sqlHeader.Append(info.GetType().Name + "(");

               PropertyInfo[] propertys =info.GetType().GetProperties();

 

               int num = 0;

               foreach (PropertyInfop in propertys)

               {

                   num += 1;

                   object value = p.GetValue(info, null);

                   if (!string.IsNullOrEmpty(identity)&& p.Name.ToUpper().Equals(identity.ToUpper()))

                        continue;

                   else

                   {

                        //if(value != DBNull.Value)

                        //{

                        if(p.PropertyType == typeof(string))

                        {

                           sqlHeader.Append(p.Name);

                            sqlMain.Append("'" + p.GetValue(info, null)+ "'");

                        }

                        elseif(p.PropertyType == typeof(int))

                        {

                           sqlHeader.Append(p.Name);

                           sqlMain.Append(p.GetValue(info, null));

                        }

                        elseif(p.PropertyType == typeof(DateTime)|| p.PropertyType == typeof(Nullable<System.DateTime>))

                        {

                           sqlHeader.Append(p.Name);

                            sqlMain.Append("'" + p.GetValue(info, null)+ "'");

                        }

                        elseif(p.PropertyType == typeof(float))

                        {

                           sqlHeader.Append(p.Name);

                           sqlMain.Append(p.GetValue(info, null));

                        }

                        elseif(p.PropertyType == typeof(double))

                        {

                           sqlHeader.Append(p.Name);

                           sqlMain.Append(p.GetValue(info, null));

                        }

                       //}

                   }

                   if (num ==info.GetType().GetProperties().Length)

                   {

                        sqlHeader.Append(")");

                        sqlMain.Append(")");

                   }

                   else

                   {

                        sqlHeader.Append(",");

                        sqlMain.Append(",");

                   }

               }

            }

            returnsqlHeader.ToString() + sqlMain.ToString();

        }

 

        ///<summary>

        ///向指定表插入一条记录

        ///</summary>

        ///<typeparamname="T">泛型</typeparam>

        ///<paramname="info">传入要进行插入的实体类</param>

        ///<returns>返回受影响的行数(不带返回值)</returns>

        publicstaticintInsert<T>(T info) where T :class,new()

        {

            stringsql = string.Empty;

            intresult = 0;

            if(info == null)

               return result;

            else

            {

               try

               {

                   sql =GetInsertSql<T>(info, "");

                   result = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

               }

               catch (Exceptionex)

               {

                   LogHelper.WriteLog(sql, ex);

               }

            }

            returnresult;

        }

 

        ///<summary>

        ///向指定表插入一条记录(返回新插入的那条记录ID)

        ///</summary>

        ///<typeparamname="T">泛型</typeparam>

        ///<paramname="info">传入要进行插入的实体类</param>

        ///<returns>返回新插入的那条记录ID</returns>

        publicstaticintInsertReturnIdentity<T>(T info) where T :class,new()

        {

            stringsql = string.Empty;

            intresult = 0;

            if(info == null)

               return result;

            else

            {

               try

               {

                   sql = GetInsertSql<T>(info, "");

                   sql += " select @@identity";

                   object obj = SqlHelper.ExecuteScalar(SqlHelper.DBConnection, CommandType.Text,sql, null);

                   if (obj != null)

                        result = Convert.ToInt32(obj);

               }

               catch (Exceptionex)

               {

                   LogHelper.WriteLog(sql, ex);

               }

            }

            returnresult;

        }

 

        ///<summary>

        ///向指定数据库插入多条数据信息

        ///</summary>

        ///<typeparamname="T"></typeparam>

        ///<paramname="list"></param>

        ///<returns></returns>

        publicstaticintInsertList<T>(List<T> list) whereT : class,new()

        {

            StringBuilder sql = new StringBuilder();

            intresult = 0;

           sql.Append("begin try ");

           sql.Append("begin tran ");

            //获取传入的泛型作为表名

            Tt = new T();

            stringidentity = GetIdentity(t.GetType().Name);

 

            //拼接多条插入语句

            for(inti = 0; i < list.Count; i++)

            {

               sql.Append(GetInsertSql<T>(list[i], identity));

            }

 

            try

            {

               if (list!=null&&list.Count>0)

               {

                   sql.Append(" commit tran ");

                   sql.Append("end try ");

                   sql.Append("begin catch ");

                   sql.Append("rollback tran ");

                   sql.Append("end catch ");

                   result = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql.ToString(), null);

               }

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql.ToString(), ex);

            }

            returnresult;

        }

 

        ///<summary>

        ///事务提交,批量插入去除已存在的数据

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="list">要处理的实体类集合</param>

        ///<paramname="whereSql">进行重复限定的条件,(and name='张三')</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintInsertList<T>(List<T> list,stringwhereSql) where T : class,new()

        {

            StringBuilder sql = new StringBuilder();

            intresult = 1;//事物处理如果所有记录都已存在,返回-1

           sql.Append("begin try ");

           sql.Append("begin tran ");

            try

            {

               //获取传入的泛型作为表名

               T t = new T();

               string identity =GetIdentity(t.GetType().Name);

 

               //如果约束条件为空不进行处理

               if (!string.IsNullOrEmpty(whereSql))

               {

                   //拼接多条插入语句

                   for (inti = 0; i < list.Count; i++)

                   {

 

                        sql.Append(" "+GetExitSql<T>(list[i], whereSql.Trim()));

                       sql.Append(GetInsertSql<T>(list[i], identity));

                   }

 

                   sql.Append(" commit tran ");

                   sql.Append("end try ");

                   sql.Append("begin catch ");

                   sql.Append("rollback tran ");

                   sql.Append("end catch ");

                   SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql.ToString(), null);

               }

            }

            catch(Exception ex)

            {

               result = -1;

               LogHelper.WriteLog(sql.ToString(), ex);

            }

            returnresult;

        }

       #endregion

 

       #region 分页查询数据

 

        ///<summary>

        ///分页查询单表数据,返回List集合对象

        ///</summary>

        ///<typeparamname="T">要进行操作的对象</typeparam>

        ///<paramname="pageIndex">当前页码</param>

        ///<paramname="pageSize">每页显示条数</param>

        ///<paramname="whereSql">如有查询条件,要拼接上,格式(andname='zhangsan' and age=20</param>

        ///<paramname="orderBySql">如有要指定进行排序的字段,请拼接上,格式(namedesc,age)</param>

        ////// <param name="count">分页查询返回总条数</param>

        ///<returns>返回List集合对象</returns>

        publicstaticList<T> Page<T>(intpageIndex, int pageSize, stringwhereSql, string orderBySql,refintcount)

            whereT : class, new()

        {

            //"select top 10 * from (select row_number() over(order by id) asrownumber,* from test) A where rownumber > 40"

            Tt = new T();

 

            //获取要操作的表名

            stringtableName = t.GetType().Name;

            stringsql = string.Empty;

           sql += " select top " +pageSize + " ";

 

            //获取当前对象的列名

            for(inti = 0; i < t.GetType().GetProperties().Length; i++)

            {

               if (i ==t.GetType().GetProperties().Length - 1)

                   sql += "a." +t.GetType().GetProperties()[i].Name;

               else

                   sql += "a." +t.GetType().GetProperties()[i].Name + ",";

            }

 

           sql += " from (select row_number() over(order by ";

 

            //如果有排序条件,拼接上,没有默认用id

            if(!string.IsNullOrEmpty(orderBySql))

               sql += orderBySql;

            else

               sql += " " + GetPK(tableName);

 

           sql += " ) as rownumber,* from "+ tableName + " where 1=1 ";

 

            if(!string.IsNullOrEmpty(whereSql))//如果有查询条件拼接上条件

               sql += whereSql;

           sql += ") a where a.rownumber>"+ (pageIndex - 1) * pageSize + " and 1=1 ";

            //如果有排序条件,拼接上,没有默认用id

            if(!string.IsNullOrEmpty(orderBySql))

               sql += " order by " +orderBySql;

 

           sql += " select count(1) from "+ tableName + " where 1=1 ";

 

            if(!string.IsNullOrEmpty(whereSql))//如果有查询条件拼接上条件

               sql += whereSql;

 

            List<T> list = newList<T>();

            try

            {

               DataSet dataset = SqlHelper.ExecuteDataSet(SqlHelper.DBConnection, CommandType.Text,sql, null);

               if (dataset != null&& dataset.Tables.Count > 0)

               {

                   list = SqlHelper.ToList<T>(dataset.Tables[0]);

                   count = Convert.ToInt32(dataset.Tables[1].Rows[0][0]);

               }

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnlist;

        }

 

        ///<summary>

        ///分页查询单表数据,返回List集合对象

        ///</summary>

        ///<typeparamname="T">要进行操作的对象</typeparam>

        ///<paramname="pageIndex">当前页码</param>

        ///<paramname="pageSize">每页显示条数</param>

        ///<paramname="dictionary">要筛选的条件</param>

        ///<paramname="orderBySql">如有要指定进行排序的字段,请拼接上,格式(namedesc,age)</param>

        ////// <param name="count">分页查询返回总条数</param>

        ///<returns>返回List集合对象</returns>

        publicstaticList<T> Page<T>(intpageIndex, int pageSize, Dictionary<string,dynamic>dictionary, string orderBySql, refintcount)

            whereT : class, new()

        {

            stringwhereSql = string.Empty;

           whereSql = ConvertDictionaryToWhereSql(dictionary);

 

            returnPage<T>(pageIndex, pageSize, whereSql, orderBySql, refcount);

        }

 

        ///<summary>

        ///分页查询单表数据,返回List集合对象

        ///</summary>

        ///<typeparamname="T">要进行操作的对象</typeparam>

        ///<paramname="pageIndex">当前页码</param>

        ///<paramname="pageSize">每页显示条数</param>

        ///<paramname="count">分页查询返回总条数</param>

        ///<returns>返回List集合对象</returns>

        publicstaticList<T> Page<T>(intpageIndex, int pageSize,refintcount)

            whereT : class, new()

        {

            returnPage<T>(pageIndex, pageSize, "","",ref count);

        }

 

       #endregion

 

       #region 查询单条记录

        ///<summary>

        ///查询单条记录

        ///</summary>

        ///<typeparamname="T"></typeparam>

        ///<paramname="id">主键值</param>

        ///<paramname="type">1.主键是int型,0.主键是string</param>

        ///<returns>返回实体对象</returns>

        privatestaticT GetById<T>(string id,inttype) where T:class,new()

        {

            Tt=new T();

            stringtableName = t.GetType().Name;

            stringsql = string.Empty;

            try

            {

               string pk_name = GetPK(tableName);//获取主键名称

               if (!string.IsNullOrEmpty(pk_name))

               {

                   if(type==1)

                        sql += "select * from " + tableName + " where " + pk_name + "="+ id;

                   else

                        sql += "select * from " + tableName + " where " + pk_name + "='"+ id+"'";

               }

               t = SqlHelper.ExecuteModel<T>(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnt;

        }

 

        ///<summary>

        ///查询单条记录

        ///</summary>

        ///<typeparamname="T"></typeparam>

        ///<paramname="id">主键值</param>

        ///<returns>返回实体对象</returns>

        publicstaticT GetById<T>(string id) whereT : class,new()

        {

            returnGetById<T>(id,0);

        }

 

        ///<summary>

        ///查询单条记录

        ///</summary>

        ///<typeparamname="T"></typeparam>

        ///<paramname="id">主键值</param>

        ///<returns>返回实体对象</returns>

        publicstaticT GetById<T>(int id) whereT : class,new()

        {

            returnGetById<T>(id.ToString(), 1);

        }

       #endregion

 

       #region 根据条件查询单表或视图多条记录

 

        ///<summary>

        ///根据条件查询单表记录是否存在

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="whereSql">要筛选的条件,( and name='张三')</param>

        ///<returns>返回bool类型标注是否存在</returns>

        publicstaticboolGetExitsByWhereSql<T>(stringwhereSql) where T : class,new()

        {

            boolisExit = false;

            Tinfo = GetByWhereSql<T>(whereSql, "");

            if(info != null)

               isExit=true;

            returnisExit;

        }

 

        ///<summary>

        ///根据条件查询单表或视图单条条记录

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="whereSql">要筛选的条件,( and name='张三')</param>

        ///<paramname="orderSql">要进行排序的条件,如(name desc,id)</param>

        ///<returns>返回实体对象</returns>

        publicstaticT GetByWhereSql<T>(stringwhereSql, string orderSql) whereT : class,new()

        {

            List<T> list = GetByWhereSqlList<T>(whereSql,orderSql);

            if(list != null && list.Count > 0)

               return list[0];

            else

               return null;

        }

 

        ///<summary>

        ///根据条件查询单表或视图多条记录

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="whereSql">要筛选的条件,( and name='张三')</param>

        ///<paramname="orderSql">要进行排序的条件,如(name desc,id)</param>

        ///<returns>返回实体对象集</returns>

        publicstaticList<T> GetByWhereSqlList<T>(stringwhereSql, string orderSql) whereT:class,new()

        {

            List<T> list=new List<T>();

            stringsql=string.Empty;

            try

            {

               T t = new T();

               sql = " select * from " +t.GetType().Name + " where 1=1 ";

               if (!string.IsNullOrEmpty(whereSql))

                   sql += whereSql + " ";

 

               if (!string.IsNullOrEmpty(orderSql))

                   sql +=" order by "+orderSql + " ";

               list = SqlHelper.ExecuteList<T>(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnlist;

        }

 

       

 

        ///<summary>

        ///根据条件查询单表或视图多条记录

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="dictionary">要筛选的条件</param>

        ///<paramname="orderSql">要进行排序的条件,如(name desc,id)</param>

        ///<returns>返回实体对象集</returns>

        publicstaticList<T> GetByWhereSqlList<T>(Dictionary<string,dynamic>dictionary, string orderSql) whereT : class,new()

        {

            stringwhereSql = string.Empty;

           whereSql = ConvertDictionaryToWhereSql(dictionary);

 

            returnGetByWhereSqlList<T>(whereSql,orderSql);

        }

       #endregion

 

       #region 删除数据

        ///<summary>

        ///操作主键是INT类型的表数据,删除

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="pkValue">传入要进行删除操作的主键值</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintDelete<T>(int pkValue) whereT:class, new()

        {

            intnum = 0;

            stringsql = string.Empty;//要执行的sql

            try

            {

               T t = new T();

               string tableName = t.GetType().Name;

               string pk_name = GetPK(tableName);

               

               if (!string.IsNullOrEmpty(pk_name))

                   sql = "delete from " +tableName + " where " +pk_name + "=" + pkValue;

               if (!string.IsNullOrEmpty(sql))

                   num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnnum;

        }

        ///<summary>

        ///根据条件删除记录

        ///</summary>

        ///<typeparamname="T">实体类</typeparam>

        ///<paramname="whereSql">条件</param>

        ///<returns></returns>

        publicstaticintDeleteByWhereSql<T>(stringwhereSql) where T : class,new()

        {

            intnum = 0;

            stringsql = string.Empty;//要执行的sql

            try

            {

               T t = new T();

               string tableName = t.GetType().Name;

               string pk_name = GetPK(tableName);

 

               if (!string.IsNullOrEmpty(pk_name))

                   sql = "delete from " +tableName+" where 1=1 ";

               if (!string.IsNullOrEmpty(whereSql))

               {

                   sql = sql + whereSql;

               }

               if (!string.IsNullOrEmpty(sql))

                   num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnnum;

        }

 

        ///<summary>

        ///操作主键是string类型的表数据,删除

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="pkValue">传入要进行删除操作的主键值</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintDelete<T>(string pkValue) whereT : class, new()

        {

            intnum = 0;

            stringsql = string.Empty;//要执行的sql

            try

            {

               T t = new T();

               string tableName = t.GetType().Name;

               string pk_name = GetPK(tableName);

 

                if(!string.IsNullOrEmpty(pk_name))

                   sql = "delete from " +tableName + " where " +pk_name + "='" + pkValue + "'";

               if (!string.IsNullOrEmpty(sql))

                   num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnnum;

        }

 

        ///<summary>

        ///批量操作主键是string类型的表数据,删除

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="list">传入要进行删除操作的主键值集合</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintDelete<T>(List<string>list) where T : class,new()

        {

            intnum = 0;

            stringsql = string.Empty;//要执行的sql

            try

            {

               T t = new T();

               string tableName = t.GetType().Name;

               string pk_name = GetPK(tableName);

 

               if (!string.IsNullOrEmpty(pk_name))

               {

                   foreach (stringpkValue in list)

                        sql += " delete from " + tableName + " where " + pk_name + "='"+ pkValue + "' ";

               }

               if (!string.IsNullOrEmpty(sql))

                   num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnnum;

        }

 

        ///<summary>

        ///批量操作主键是int类型的表数据,删除

        ///</summary>

        ///<typeparamname="T">实体对象</typeparam>

        ///<paramname="list">传入要进行删除操作的主键值集合</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintDelete<T>(List<int>list) where T : class,new()

        {

            intnum = 0;

            stringsql = string.Empty;//要执行的sql

            try

            {

               T t = new T();

               string tableName = t.GetType().Name;

               string pk_name = GetPK(tableName);

 

               if (!string.IsNullOrEmpty(pk_name))

               {

                   foreach (intpkValue in list)

                        sql += " delete from " + tableName + " where " + pk_name + "="+ pkValue;

               }

               if (!string.IsNullOrEmpty(sql))

                   num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnnum;

        }

 

       #endregion

 

       #region 修改数据

 

       #region 拼接单条修改语句

        ///<summary>

        ///拼接修改语句(属性为空代码不进行修改)

        ///</summary>

        ///<typeparamname="T"></typeparam>

        ///<paramname="t"></param>

        ///<returns>返回update修改语句</returns>

        privatestaticstringGetUpadateSql<T>(T t) where T:class,new()

        {

            PropertyInfo[] propertys = t.GetType().GetProperties();

            stringtableName=t.GetType().Name;//获取表名

            stringpk_name = GetPK(t.GetType().Name);//获取主键名称

            StringBuilder sql = new StringBuilder();

            stringwhereSql = string.Empty;

            intnum = 0;

           sql.Append(" update "+tableName+" set ");

            foreach(PropertyInfo p inpropertys)

            {

               num += 1;

               object value = p.GetValue(t, null);

               //如果字段为空不进行修改操作

               if (value != DBNull.Value)

               {

                   if (!string.IsNullOrEmpty(pk_name)&& p.Name.ToUpper().Equals(pk_name.ToUpper()))

                   {

                        if(p.PropertyType == typeof(string))

                            whereSql = " where " + pk_name + "='"+ p.GetValue(t, null) + "'";

                        elseif(p.PropertyType == typeof(int))

                            whereSql = " where " + pk_name + "="+ p.GetValue(t, null);

                        continue;

                   }

                   else

                   {

                        //if(value != DBNull.Value)

                        //{

                        if(p.PropertyType == typeof(string))

                        {

                           sql.Append(p.Name + "='" + p.GetValue(t, null)+ "' ");

                        }

                        elseif(p.PropertyType == typeof(int))

                        {

                            sql.Append(p.Name +"=" + p.GetValue(t, null)+ " ");

                        }

                        elseif(p.PropertyType == typeof(DateTime)|| p.PropertyType == typeof(Nullable<System.DateTime>))

                        {

                            sql.Append(p.Name +"='" + p.GetValue(t, null)+ "' ");

                        }

                        elseif(p.PropertyType == typeof(float))

                        {

                            sql.Append(p.Name +"=" + p.GetValue(t, null)+ " ");

                        }

                       elseif(p.PropertyType == typeof(double))

                        {

                            sql.Append(p.Name +"=" + p.GetValue(t, null)+ " ");

                        }

                        //}

                   }

 

                   if(num == t.GetType().GetProperties().Length)

                   {

                        sql.Append(" " + whereSql);

                   }

                   else

                   {

                        sql.Append(",");

                   }

                }

            }

 

            returnsql.ToString();

        }

       #endregion

 

        ///<summary>

        ///修改单条记录(如果传入的对象有为空的属性则表示不进行操作)

        ///</summary>

        ///<typeparamname="T">传入的实体对象</typeparam>

        ///<paramname="t">传入的实体对象</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintUpdate<T>(T t) where T : class,new()

        {

            intnum = 0;

            stringsql = GetUpadateSql<T>(t);

            try

            {

               num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog(sql, ex);

            }

            returnnum;

        }

 

        ///<summary>

        ///修改多条记录(如果传入的对象有为空的属性则表示不进行操作)

        ///</summary>

        ///<typeparamname="T">传入的实体对象</typeparam>

        ///<paramname="list">传入的实体对象集合</param>

        ///<returns>返回受影响行数</returns>

        publicstaticintUpdate<T>(List<T> list) whereT : class,new()

        {

            stringsql = string.Empty;

            intnum = 0;

            try

            {

               foreach (T info inlist)

                   sql+=GetUpadateSql<T>(info);

               if(!string.IsNullOrEmpty(sql))

                   num = SqlHelper.ExecuteNonQuery(SqlHelper.DBConnection, CommandType.Text,sql, null);

            }

            catch(Exception ex)

            {

               LogHelper.WriteLog("多条修改出错:", ex);

            }

            returnnum;

        }

 

       #endregion

    }

}


参与评论 您还未登录,请先 登录 后发表或查看评论
相关推荐

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:编程工作室 设计师:CSDN官方博客 返回首页

打赏作者

HRL煜

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值