1.引用Oracle.ManagedDataAccess，利用Nuget查找Oracle.ManagedDataAccess
2.在config中加入连接oracle数据库的字符串
<add key="OracleLink" value="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME= Oracleppd.ideal.com)));User Id=sa; Password=abcd1234" />
3.cs
添加引用
using Oracle.ManagedDataAccess;
using Oracle.ManagedDataAccess.Types;
using Oracle.ManagedDataAccess.Client;
1 public DataTable GetSPstoDataTable(string proNo, string proDsc) 2 { 3 string constr = System.Configuration.ConfigurationManager.AppSettings["JDELink"]; 4 OracleConnection con = new OracleConnection(constr); 5 //System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(constr); 6 con.Open(); //建立连接，打开数据库 7 OperationResult returnResult = new OperationResult(); 8 List<OracleParameter> lsPar = new List<OracleParameter>(); 9 string sql = ""; 10 if (proNo == "" && proDsc == "") 11 { 12 sql = "SELECT * FROM T1 where 1=1 WHERE (IMDSC1) like '%海棉%' "; 13 } 14 else 15 { 16 sql = "SELECT * FROM T1 where 1=1 WHERE trim(LIMCU) in ('00000')"; 17 } 18 string sWhere = ""; 19 if (proNo.Trim() != "") 20 { 21 sWhere += " and trim(IMLITM) = :IMLITM "; 22 lsPar.Add(new OracleParameter(":IMLITM", proNo)); 23 } 24 if (proDsc.Trim() != "") 25 { 26 sWhere += " and IMDSC1 like :IMDSC1 "; 27 28 OracleParameter IMDSC1 = new OracleParameter(":IMDSC1", OracleDbType.NVarchar2); 29 IMDSC1.Value = "%" + proDsc + "%"; 30 lsPar.Add(IMDSC1); 31 } 32 33 OracleCommand cmd = new OracleCommand(sql + sWhere, con); 34 DataSet ds = new DataSet(); 35 cmd.Parameters.AddRange(lsPar.ToArray()); 36 OracleDataAdapter oda = new OracleDataAdapter(); 37 oda.SelectCommand = cmd; 38 oda.Fill(ds); 39 var aa = ds.Tables[0].DefaultView; 40 41 //string getWeekSql = "SELECT calenderYear,calenderYearWeek FROM BI_DW_DIM_Calender WHERE CalenderDate = CONVERT(VARCHAR,GETDATE(),23)"; 42 //var dt = this.FillTable(getWeekSql); 43 return ds.Tables[0]; 44 }
4.调用
1 public JsonResult GetJDEspinfo(int limit, int offset, string SPItemNo, string Desc) 2 { 3 BaseManager bm = new BaseManager(); 4 var data1 = bm.GetSPstoDataTable(SPItemNo, Desc); 5 int total = data1.Rows.Count; 6 var List = data1.AsEnumerable().Select(p => new 7 { 8 Building = p.Field<string>("BUILDING"), 9 Spname1 = p.Field<string>("SPNAME1"), 10 SPItemNo = p.Field<string>("SPITEMNO"), 11 Loction = p.Field<string>("LOCATION"), 12 Qty = p.Field<Decimal>("QTY"), 13 });//.OrderBy(a => a.SPItemNo); //.Skip(pageSize * (pageIndex - 1)).Take(pageSize); 14 var datalist = from a in List.ToList() select new { Building = a.Building, Spname1 = a.Spname1, SPItemNo = a.SPItemNo, Loction = a.Loction, Qty = a.Qty }; 15 //var datalist = from a in List.ToList() select new { SPItemNo = a.SPItemNo }; 16 var result = new { total = total, rows = datalist.Skip(50) }; 17 return Json(datalist, JsonRequestBehavior.AllowGet); 18 }
5.前端展示
1 @{ 2 Layout = "~/Views/Shared/_LayoutWithoutMenu.cshtml"; 3 ViewBag.Title = "SpMaster"; 4 } 5 6 <style> 7 body{margin: 10px;} 8 .demo-carousel{height: 200px; line-height: 200px; text-align: center;} 9 .datagrid-cell { 10 font-size:14px; 11 padding:0 5px; 12 height:auto; 13 overflow:visible; 14 text-overflow:inherit; 15 white-space:normal; 16 word-break: break-all; 17 } 18 19 </style> 20 21 <div> 22 23 <div class="widget-box ui-sortable-handle" data-role="query"> 24 <div class="widget-header"> 25 <h5 class="widget-title">Query Box</h5> 26 <div class="widget-toolbar"> 27 <a href="#" data-action="clear" onclick="$('#queryForm').clear();" style="padding:0 8px;color:#fe9e19;" title="clear search condition"> 28 <i class="ace-icon fa fa-undo"></i> 29 </a> 30 <a href="#" data-action="collapse" title="collapse/expand query box"> 31 <i class="ace-icon fa fa-chevron-up"></i> 32 </a> 33 </div> 34 </div> 35 <div class="widget-body" style="display: block;padding-top:4px;"> 36 <form id="queryForm" class="form-group" method="post"> 37 <div class="row" style="padding-left: 5px;"> 38 <div class="col-sm-2"> 39 <span>配件编号</span> 40 @Html.TextBox("SPItemNo", "",new { @class = "form-control", }) 41 </div> 42   <div class="col-sm-2"> 43 <span>配件描述</span> 44 @Html.TextBox("Desc", "",new { @class = "form-control", }) 45 </div> 46 <div> 47 <button type="submit" class="btn btn-primary btn-app btn-xs"><i class="ace-icon fa fa-search bigger-120"></i>Query</button> 48 </div> 49 </div> 50 </form> 51 </div> 52 </div> 53 <table id="tbEmp" class="table"></table> 54 </div> 55 @section scripts 56 { 57 <script src="~/Content/js/jquery-3.3.1.min.js"></script> 58 <link href="~/Content/bootstrap-datetimepicker/css/bootstrap-datetimepicker.min.css" rel="stylesheet" /> 59 <script src="~/Content/bootstrap-datetimepicker/js/bootstrap-datetimepicker.min.js"></script> 60 <script src="~/Content/bootstrap-table/bootstrap-table.js"></script> 61 <link href="~/Content/bootstrap-table/bootstrap-table.css" rel="stylesheet" /> 62 <script src="~/Content/bootstrap-table/locale/bootstrap-table-zh-CN.js"></script> 63 <script src="~/Content/bootstrap-table/table_export/bootstrap-table-export.js"></script> 64 <script src="~/Content/bootstrap-table/table_export/tableExport.js"></script> 65 <script src="~/Content/bootstrap-table/table_export/xlsx.core.min.js"></script> 66 <script src="~/Content/bootstrap-table/table_export/xlsx.full.min.js"></script> 67 68 <script type="text/javascript"> 69 70 $(function () { 71 //1.初始化Table 72 var oTable = new TableInit(); 73 oTable.Init(); 74 75 //2.初始化select的change事件 76 $("#sel_exportoption").change(function () { 77 $('#tbEmp').bootstrapTable('refreshOptions', { 78 exportDataType: $(this).val() 79 }); 80 }); 81 }); 82 83 84 var TableInit = function () { 85 var oTableInit = new Object(); 86 //初始化Table 87 oTableInit.Init = function () { 88 $('#tbEmp').bootstrapTable({ 89 url: '/Sp/GetJDEspinfo', //请求后台的URL（*） 90 method: 'get', //请求方式（*） 91 toolbar: '#toolbar', //工具按钮用哪个容器 92 striped: true, //是否显示行间隔色 93 cache: false, //是否使用缓存，默认为true，所以一般情况下需要设置一下这个属性（*） 94 pagination: true, //是否显示分页（*） 95 sortable: true, //是否启用排序 96 sortOrder: "asc", //排序方式 97 queryParams: oTableInit.queryParams, //传递参数（*） 98 sidePagination: "client", //分页方式：client客户端分页，server服务端分页（*） 99 pageNumber: 1, //初始化加载第一页，默认第一页 100 pageSize: 10, //每页的记录行数（*） 101 pageList: [10, 20, 30], //可供选择的每页的行数（*） 102 search: true, 103 clickToSelect: true, //是否启用点击选中行 104 showColumns: true, 105 showRefresh: false, 106 showExport: true, //是否显示导出 107 //exportDataType: "basic", //basic', 'all', 'selected'. 108 exportDataType: "all", 109 exportTypes: [ 'xlsx'], //导出类型 110 //sortName: 'SPItemNo,Spname1,Building',// 定义哪列可以排序 111 //sortOrder: 'desc,asc,desc',// 定义列的排列顺序，只能是'asc'或'desc'。默认asc 112 columns: [ 113 // { 114 // checkbox: true 115 //}, 116 { 117 field: 'Number', 118 title: '序号', 119 align: 'center', 120 30, 121 formatter: function (value, row, index) { 122 return index + 1; 123 } 124 125 }, { 126 field: 'Building', 127 title: '工厂', 128 align: 'center', 129 100 130 }, { 131 field: 'SPItemNo', 132 title: '配件编号', 133 sortable: true 134 }, { 135 field: 'Spname1', 136 title: '配件描述', 137 sortable: true 138 }, { 139 field: 'Loction', 140 title: '位置' 141 }, { 142 field: 'Qty', 143 title: '库存数' 144 }, 145 //{ 146 // field: 'operation', 147 // title: '操作', 148 // align: 'center', 149 // 100, 150 // formatter: function (value, row, index) { 151 // // 1: 第一种方式，在外部定义点击事件 152 // // var a = '<a class = "edit fa fa-edit" onclick="updateRole(' + row.id + ');" title = "编辑" href="javascript:void(0)"></a>'; 153 // // var b = '<a class = "remove fa fa-trash-o" onclick="delRole(' + row.id + ');" title = "删除" href="javascript:void(0)"></a>'; 154 // // return a + ' ' + b; 155 // // 2：第二种方式，通过定义 events: operateEvents 直接在定义点击事件 156 // var s = '<a class = "save fa fa-save" title = "保存" href="javascript:void(0)"></a>'; 157 // var d = '<a class = "remove fa fa-trash-o" title = "删除" href="javascript:void(0)"></a>'; 158 // return s + ' ' + d; 159 // }, events: 'operateEvents' 160 //}, 161 ] 162 }); 163 }; 164 165 window.operateEvents = { 166 'click .save': function (e, value, row, index) { 167 $.ajax({ 168 type: "post", 169 dataType: "JSON", 170 data: row, 171 url: '/Sp/Getdata', 172 success: function (data) { 173 debugger; 174 alert(data); 175 if (data == 1) { 176 alert("sucess") 177 } else { 178 alert("error") 179 // showMessage("删除失败，请联系管理员"); 180 } 181 } 182 }); 183 }, 184 'click .remove': function (e, value, row, index) { 185 //var result = isConfirm(); 186 if (result) { 187 layer.confirm("确定要删除[" + row.staff_name + "]的记录吗?", { 188 btn: ['确定', '取消'] 189 }, function () { 190 showLoading(); 191 $.ajax({ 192 type: "post", 193 data: { id: row.id }, 194 dataType: "JSON", 195 url: basePath + '/prizeAmount/delete.html', 196 success: function (data) { 197 hideLoading(); 198 if (0 == data.code) { 199 $('#tablelist').bootstrapTable('refresh', { url: basePath + '/prizeAmount/list.html', query: { organizeCode: organizeCode } }); 200 showMessage(data.message); 201 } else { 202 showMessage(data.message); 203 // showMessage("删除失败，请联系管理员"); 204 } 205 } 206 }); 207 }, function () { 208 209 }) 210 211 } else { 212 showMessage("确认已完成，不能进行删除"); 213 } 214 } 215 }; 216 217 //得到查询的参数 218 oTableInit.queryParams = function (params) { 219 var temp = { //这里的键的名字和控制器的变量名必须一直，这边改动，控制器也需要改成一样的 220 limit: params.limit, //页面大小 221 offset: params.offset, //页码 222 SPItemNo: $('#SPItemNo').val(), 223 Desc: $('#Desc').val() 224 }; 225 return temp; 226 }; 227 return oTableInit; 228 }; 229 230 231 var ButtonInit = function () { 232 var oInit = new Object(); 233 var postdata = {}; 234 235 oInit.Init = function () { 236 $("#btn_add").click(function () { 237 $("#myModalLabel").text("新增"); 238 $("#myModal").find(".form-control").val(""); 239 $('#myModal').modal() 240 241 postdata.DEPARTMENT_ID = ""; 242 }); 243 244 $("#btn_edit").click(function () { 245 var arrselections = $("#tbEmp").bootstrapTable('getSelections'); 246 if (arrselections.length > 1) { 247 toastr.warning('只能选择一行进行编辑'); 248 249 return; 250 } 251 if (arrselections.length <= 0) { 252 toastr.warning('请选择有效数据'); 253 254 return; 255 } 256 $("#myModalLabel").text("编辑"); 257 $("#txt_departmentname").val(arrselections[0].DEPARTMENT_NAME); 258 $("#txt_parentdepartment").val(arrselections[0].PARENT_ID); 259 $("#txt_departmentlevel").val(arrselections[0].DEPARTMENT_LEVEL); 260 $("#txt_statu").val(arrselections[0].STATUS); 261 262 postdata.DEPARTMENT_ID = arrselections[0].DEPARTMENT_ID; 263 $('#myModal').modal(); 264 }); 265 266 $("#btn_delete").click(function () { 267 var arrselections = $("#tbEmp").bootstrapTable('getSelections'); 268 if (arrselections.length <= 0) { 269 toastr.warning('请选择有效数据'); 270 return; 271 } 272 273 Ewin.confirm({ message: "确认要删除选择的数据吗？" }).on(function (e) { 274 if (!e) { 275 return; 276 } 277 $.ajax({ 278 type: "post", 279 url: "/Home/Delete", 280 data: { "": JSON.stringify(arrselections) }, 281 success: function (data, status) { 282 if (status == "success") { 283 toastr.success('提交数据成功'); 284 $("#tbEmp").bootstrapTable('refresh'); 285 } 286 }, 287 error: function () { 288 toastr.error('Error'); 289 }, 290 complete: function () { 291 292 } 293 294 }); 295 }); 296 }); 297 298 $("#btn_submit").click(function () { 299 postdata.DEPARTMENT_NAME = $("#txt_departmentname").val(); 300 postdata.PARENT_ID = $("#txt_parentdepartment").val(); 301 postdata.DEPARTMENT_LEVEL = $("#txt_departmentlevel").val(); 302 postdata.STATUS = $("#txt_statu").val(); 303 $.ajax({ 304 type: "post", 305 url: "/Home/GetEdit", 306 data: { "": JSON.stringify(postdata) }, 307 success: function (data, status) { 308 if (status == "success") { 309 toastr.success('提交数据成功'); 310 $("#tbEmp").bootstrapTable('refresh'); 311 } 312 }, 313 error: function () { 314 toastr.error('Error'); 315 }, 316 complete: function () { 317 318 } 319 320 }); 321 }); 322 323 $("#btn_query").click(function () { 324 $("#tbEmp").bootstrapTable('refresh'); 325 }); 326 }; 327 328 return oInit; 329 }; 330 </script> 331 332 } 333