C#连接Oracle数据库的方法

1.引用Oracle.ManagedDataAccess，利用Nuget查找Oracle.ManagedDataAccess

2.在config中加入连接oracle数据库的字符串

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         }

View Code

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         }

View Code

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                     &nbsp&nbsp<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 + '&nbsp;&nbsp;' + 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 + '&nbsp;&nbsp;&nbsp;' + 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

View Code

相关阅读:
概率的定义
 二项式分布与伯努利分布
 复数的表示方法
 <诗经>的由来
 王国维
 应该记住的历史事件
 switch条件变量的取值类型
 & 和 && 区别和联系，| 和 || 区别和联系
 成功安装 Visio 2016 和 Office 2016 的64位版本~~
删除MicrosoftOffice2016的扫尾工作
原文地址：https://www.cnblogs.com/Depingblogs/p/13431599.html