CommonUtils类

2018/03/06 15:42
阅读数 17

一、帮助类

1、Json与对象、DataTable、Collection的转换

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.Runtime.Serialization;
  7 using System.Runtime.Serialization.Json;
  8 using System.IO;
  9 using System.Data;
 10 using System.Collections;
 11 using System.Text.RegularExpressions;
 12 using System.Web.Script.Serialization;
 13 
 14 public class JsonTools
 15 {
 16 
 17     // 从一个对象信息生成Json串        
 18     public static string ObjectToJson(object obj)
 19     {
 20         //将对象序列化为 JavaScript 对象表示法 (JSON),并将 JSON 数据反序列化为对象。 此类不能被继承。
 21         DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType());
 22         MemoryStream stream = new MemoryStream();
 23         serializer.WriteObject(stream, obj);
 24         byte[] dataBytes = new byte[stream.Length];
 25         stream.Position = 0;
 26         stream.Read(dataBytes, 0, (int)stream.Length);
 27         return Encoding.UTF8.GetString(dataBytes); //Json字符串
 28     }
 29     // 从一个Json串生成对象信息        
 30     public static object JsonToObject(string jsonString, object obj)
 31     {
 32         DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType());
 33         MemoryStream mStream = new MemoryStream(Encoding.UTF8.GetBytes(jsonString));
 34         return serializer.ReadObject(mStream);
 35     }
 36 
 37     /// <summary> 
 38     /// DataTable转为json 
 39     /// </summary> 
 40     /// <param name="dt">DataTable</param> 
 41     /// <returns>json数据</returns> 
 42     public static string DataTableToJson(DataTable dt)
 43     {
 44         ArrayList dic = new ArrayList();
 45         foreach (DataRow dr in dt.Rows)
 46         {
 47             Dictionary<string, object> result = new Dictionary<string, object>();
 48 
 49             foreach (DataColumn dc in dt.Columns)
 50             {
 51                 if (dc.DataType == typeof(DateTime) && !dr[dc].ToString().Equals(""))
 52                     result.Add(dc.ColumnName, DateTime.Parse(dr[dc].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
 53                 else
 54                     result.Add(dc.ColumnName, dr[dc].ToString());
 55             }
 56             dic.Add(result);
 57 
 58         }
 59         JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
 60 
 61         javaScriptSerializer.MaxJsonLength = Int32.MaxValue;
 62         return javaScriptSerializer.Serialize(dic);  
 63         //return ObjectToJson(dic);
 64     }
 65 
 66     /// <summary>
 67     /// json转为DataTable
 68     /// </summary>
 69     /// <param name="strJson"></param>
 70     /// <returns></returns>
 71     public static DataTable JsonToDataTable(string strJson)
 72     {
 73         ////取出表名  
 74         //Regex rg = new Regex(@"(?<={)[^:]+(?=:/[)", RegexOptions.IgnoreCase);
 75         //string strName = rg.Match(strJson).Value;
 76         DataTable dt = null;
 77         ////去除表名  
 78         //strJson = strJson.Substring(strJson.IndexOf("[") + 1);
 79         //strJson = strJson.Substring(0, strJson.IndexOf("]"));
 80 
 81         //获取数据  
 82         Regex rg = new Regex(@"(?<={)[^}]+(?=})");
 83         MatchCollection mc = rg.Matches(strJson);
 84         for (int i = 0; i < mc.Count; i++)
 85         {
 86             string strRow = mc[i].Value;
 87             string[] strRows = strRow.Split(',');
 88 
 89             //创建表  
 90             if (dt == null)
 91             {
 92                 dt = new DataTable();
 93                 dt.TableName = "";
 94                 foreach (string str in strRows)
 95                 {
 96                     DataColumn dc = new DataColumn();
 97                     string[] strCell = str.Split(':');
 98 
 99                     dc.ColumnName = strCell[0].ToString().Replace("\"", "").Trim();
100                     dt.Columns.Add(dc);
101                 }
102                 dt.AcceptChanges();
103             }
104 
105             //增加内容  
106             DataRow dr = dt.NewRow();
107             for (int r = 0; r < strRows.Length; r++)
108             {
109                 dr[r] = strRows[r].Split(':')[1].Trim().Replace("", ",").Replace("", ":").Replace("/", "").Replace("\"", "").Trim();
110             }
111             dt.Rows.Add(dr);
112             dt.AcceptChanges();
113         }
114 
115         return dt;
116     }
117 
118 
119     /// <summary> 
120     /// 返回结果将数据表属性转换成Json 
121     /// </summary> 
122     /// <param name="dt">DataTable</param> 
123     /// <returns>json数据</returns> 
124     public static SubmitResultJson ResultToResultJson(SubmitResult oResult)
125     {
126         SubmitResultJson oResultJson = new SubmitResultJson();
127         oResultJson.ResultCode = oResult.ResultCode;
128         oResultJson.ResultMsg = oResult.ResultMsg;
129         if (oResult.ResultCode == "1")
130         {
131             try
132             {
133                 if (oResult.ResultTable != null)
134                 {
135                     DataTable dt = new DataTable();
136                     dt = oResult.ResultTable.Copy();
137                     string strResultTable = JsonTools.DataTableToJson(dt);
138                     oResultJson.ResultTableJson = strResultTable;
139                 }
140             }
141             catch (Exception ex)
142             {
143                 oResultJson.ResultCode = "0";
144                 oResultJson.ResultMsg = ex.Message;
145                 return oResultJson;
146             }
147         }
148         return oResultJson;
149     }
150         /// <summary> 
151         /// 序列化方法,把自定义实体类转化为Json格式的字符串 
152         /// </summary> 
153         /// <typeparam name="T"></typeparam> 
154         /// <param name="objToJson"></param> 
155         /// <returns></returns> 
156         public static string SerializeJson<T>(T objToJson)
157         {
158             DataContractJsonSerializer json = new DataContractJsonSerializer(objToJson.GetType());
159             string resultString;
160             //序列化 
161             using (MemoryStream stream = new MemoryStream())
162             {
163                 json.WriteObject(stream, objToJson);
164                 resultString = Encoding.UTF8.GetString(stream.ToArray());
165                 return resultString;
166             }
167         }
168 
169 
170         /// <summary> 
171         /// 反序列化方法。将客户端发来的Json转化为Collection<T> 
172         /// </summary> 
173         /// <typeparam name="T"></typeparam> 
174         /// <param name="sJson"></param> 
175         /// <param name="type"></param> 
176         /// <returns></returns> 
177         public static T DeserializeJson<T>(string sJson, Type type)
178         {
179             using (MemoryStream ms = new MemoryStream(Encoding.UTF8.GetBytes(sJson)))
180             {
181                 DataContractJsonSerializer serializer = new DataContractJsonSerializer(type);
182                 object obj = serializer.ReadObject(ms);
183                 return (T)obj;
184             }
185         }
186 
187 }
Json转换Helper类

2、日志文件

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Configuration;
 6 
 7 public class LogManager
 8 {
 9     private static string logPath = ConfigurationSettings.AppSettings["LogPath"];
10     /// <summary>
11     /// 保存日志的文件夹
12     /// </summary>
13     public static string LogPath
14     {
15         get
16         {
17             //if (logPath == string.Empty)
18             //{
19             //    logPath = logPath;
20             //    //if (System.Web.HttpContext.Current == null)
21             //    //    // Windows Forms 应用
22             //    //    logPath = AppDomain.CurrentDomain.BaseDirectory;
23             //    //else
24             //    //    // Web 应用
25             //    //    logPath = AppDomain.CurrentDomain.BaseDirectory + @"bin\";
26             //}
27             return logPath;
28         }
29         set { logPath = value; }
30     }
31 
32     private static string logFielPrefix = string.Empty;
33     /// <summary>
34     /// 日志文件前缀
35     /// </summary>
36     public static string LogFielPrefix
37     {
38         get { return logFielPrefix; }
39         set { logFielPrefix = value; }
40     }
41 
42     /// <summary>
43     /// 写日志
44     /// </summary>
45     private static void WriteLog(string logFile, string msg)
46     {
47         try
48         {
49             System.IO.StreamWriter sw = System.IO.File.AppendText(
50                 LogPath + LogFielPrefix + logFile + " " +
51                 DateTime.Now.ToString("yyyyMMdd") + ".Log"
52                 );
53             sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss: ") + msg);
54             sw.Close();
55         }
56         catch(Exception ex)
57         {
58             throw new Exception(ex.Message);
59         }
60     }
61 
62     /// <summary>
63     /// 写日志
64     /// </summary>
65     public static void WriteLog(LogType logFile, string msg)
66     {
67         WriteLog(logFile.ToString(), msg);
68     }
69 }
70 
71 
72 /// <summary>
73 /// 日志类型
74 /// </summary>
75 public enum LogType
76 {
77     Trace,
78     Warning,
79     Error,
80     SQL
81 }
View Code

3、xml与某一类型转换

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.Xml;
 6 using System.Xml.Serialization;
 7 using System.Text;
 8 using System.IO;
 9 using System.Data;
10 
11 /// <summary>
12 /// 序列化和反序列化XML文件
13 /// </summary>
14 public class XmlUtil
15 {
16     public XmlUtil()
17     {
18         //
19         // TODO: 在此处添加构造函数逻辑
20         //
21     }
22 
23     /// <summary>    
24     /// XML序列化某一类型到指定的文件    
25     /// </summary>    
26     /// <param name="filePath"></param>    
27     /// <param name="obj"></param>    
28     /// <param name="type"></param>    
29     public static void SerializeToXml<T>(string filePath, T obj)
30     {
31         try
32         {
33             using (System.IO.StreamWriter writer = new System.IO.StreamWriter(filePath))
34             {
35                 System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(T));
36                 xs.Serialize(writer, obj);
37             }
38         }
39         catch (Exception ex)
40         {
41             throw ex;
42         }
43     }
44 
45     /// <summary>    
46     /// 从某一XML文件反序列化到某一类型    
47     /// </summary>    
48     /// <param name="filePath">待反序列化的XML文件名称</param>    
49     /// <param name="type">反序列化出的</param>    
50     /// <returns></returns>    
51     public static T DeserializeFromXml<T>(string filePath)
52     {
53         try
54         {
55             if (!System.IO.File.Exists(filePath))
56             {
57                 throw new ArgumentNullException(filePath + " 不存在");
58             }
59              
60             using (StreamReader reader = new StreamReader(filePath))
61             {
62                 System.Xml.Serialization.XmlSerializer xs = new System.Xml.Serialization.XmlSerializer(typeof(T));
63                 T ret = (T)xs.Deserialize(reader); 
64                 return ret;
65             }
66         }
67         catch (Exception ex)
68         {
69             return default(T);
70         }
71     }
72 }
View Code

4、数据库帮助类

4.1、创建DalFactory类库项目:

  -- 工厂类:

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 
 7 public class MESDalContext
 8 {
 9     public static readonly MESDalContext instance = new MESDalContext();
10     private MESDalContext()
11     {
12 
13     }
14     public ISQL_DAL GetDalObject()
15     {
16         return new SQL_DAL();
17     }
18 }
MESDalContext类

 -- DbHelper类:

  1 using Oracle.ManagedDataAccess.Client;
  2 using System;
  3 using System.Collections;
  4 using System.Collections.Generic;
  5 using System.Configuration;
  6 using System.Data;
  7 
  8 public abstract class DatabaseHelper
  9 {
 10     AppSettingsReader AppRead = new AppSettingsReader();
 11 
 12       private static readonly string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
 13     #region "执行简单SQL语句"
 14     /// <summary>
 15     /// 执行多条SQL语句,实现数据库事务。
 16     /// </summary>
 17     /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的OracleParameter[])</param>
 18     public static void ExecuteSqlTranByHash(Dictionary<string, OracleParameter[]> SQLStringList)
 19     {
 20         using (OracleConnection conn = new OracleConnection(connectionString))
 21         {
 22             conn.Open();
 23             using (OracleTransaction trans = conn.BeginTransaction())
 24             {
 25                 OracleCommand cmd = new OracleCommand();
 26                 try
 27                 {
 28                     //循环
 29                     foreach (var myDE in SQLStringList)
 30                     {
 31                         string cmdText = myDE.Key.ToString();
 32                         //获得键值
 33                         OracleParameter[] cmdParms = myDE.Value;
 34                         //通过键值获得值
 35                         PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
 36                         int val = cmd.ExecuteNonQuery();
 37                         cmd.Parameters.Clear();
 38                     }
 39                     //事务提交
 40                     trans.Commit();
 41                 }
 42                 catch (Exception ex)
 43                 {
 44                     trans.Rollback();
 45                     throw ex;
 46                 }
 47             }
 48         }
 49     }
 50     /// <summary>
 51     /// 执行SQL语句,返回影响的记录数
 52     /// 例如数据添加、删除、更新操作
 53     /// </summary>
 54     /// <param name="SQLString">SQL语句</param>
 55     /// <returns>影响的记录数</returns>
 56     public static int ExecuteSql(string SQLString)
 57     {
 58         using (OracleConnection connection = new OracleConnection(connectionString))
 59         {
 60             using (OracleCommand cmd = new OracleCommand(SQLString, connection))
 61             {
 62                 try
 63                 {
 64                     connection.Open();
 65                     int rows = cmd.ExecuteNonQuery();
 66                     return rows;
 67                 }
 68                 catch (OracleException ex)
 69                 {
 70                     connection.Close();
 71                     throw ex;
 72                 }
 73             }
 74         }
 75     }
 76     /// <summary>
 77     /// 执行多条SQL语句,实现数据库事务。
 78     /// 当所有的执行语句全部执行成功,没有异常后才能进行事务的提交
 79     /// </summary>
 80     /// <param name="SQLStringList">多条SQL语句</param>        
 81     public static void ExecuteSqlTran(ArrayList SQLStringList)
 82     {
 83 
 84         using (OracleConnection conn = new OracleConnection(connectionString))
 85         {
 86             conn.Open();
 87             OracleCommand cmd = new OracleCommand();
 88             cmd.Connection = conn;
 89             OracleTransaction tx = conn.BeginTransaction();
 90             cmd.Transaction = tx;
 91             try
 92             {
 93                 for (int i = 0; i < SQLStringList.Count; i++)
 94                 {
 95                     string strSql = SQLStringList[i].ToString();
 96                     if (strSql.Trim().Length > 1)
 97                     {
 98                         cmd.CommandText = strSql;
 99                         cmd.ExecuteNonQuery();
100                     }
101                 }
102                 //事务提交
103                 tx.Commit();
104             }
105             catch (OracleException ex)
106             {
107                 tx.Rollback();
108                 //事务回滚
109                 throw ex;
110             }
111         }
112     }
113     /// <summary>
114     /// 执行带一个存储过程参数的的SQL语句。
115     /// </summary>
116     /// <param name="strSQL">SQL语句/或者是存储过程</param>
117     /// <param name="content">参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加</param>
118     /// <returns>影响的记录数</returns>
119     public static int ExecuteSql(string strSQL, string content)
120     {
121         using (OracleConnection connection = new OracleConnection(connectionString))
122         {
123             OracleCommand cmd = new OracleCommand(strSQL, connection);
124             OracleParameter myParameter = new OracleParameter("content", SqlDbType.Image);
125             myParameter.Value = content;
126             cmd.Parameters.Add(myParameter);
127             try
128             {
129                 connection.Open();
130                 int rows = cmd.ExecuteNonQuery();
131                 return rows;
132             }
133             catch (OracleException ex)
134             {
135                 throw ex;
136             }
137             finally
138             {
139                 cmd.Dispose();
140                 connection.Close();
141             }
142         }
143     }
144     /// <summary>
145     /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
146     /// </summary>
147     /// <param name="strSQL">SQL语句</param>
148     /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
149     /// <returns>影响的记录数</returns>
150     public static int ExecuteSqlInsertImg(string strSQL, Byte[] fs)
151     {
152         using (OracleConnection connection = new OracleConnection(connectionString))
153         {
154             OracleCommand cmd = new OracleCommand(strSQL, connection);
155             OracleParameter myParameter = new OracleParameter("fs", SqlDbType.Image);
156             myParameter.Value = fs;
157             cmd.Parameters.Add(myParameter);
158             try
159             {
160                 connection.Open();
161                 int rows = cmd.ExecuteNonQuery();
162                 return rows;
163             }
164             catch (OracleException ex)
165             {
166                 throw ex;
167             }
168             finally
169             {
170                 cmd.Dispose();
171                 connection.Close();
172             }
173         }
174     }
175 
176     /// <summary>
177     /// 执行一条计算查询结果语句,返回查询结果(object)。
178     /// </summary>
179     /// <param name="strSQL">计算查询结果语句</param>
180     /// <returns>查询结果(object)</returns>
181     public static object GetSingle(string strSQL)
182     {
183         using (OracleConnection connection = new OracleConnection(connectionString))
184         {
185             using (OracleCommand cmd = new OracleCommand(strSQL, connection))
186             {
187                 try
188                 {
189                     connection.Open();
190                     object obj = cmd.ExecuteScalar();
191                     //统计功能(一行一列)
192                     if ((obj == null) || (obj == System.DBNull.Value))
193                     {
194                         return null;
195                     }
196                     else
197                     {
198                         return obj;
199                     }
200                 }
201                 catch (OracleException ex)
202                 {
203                     connection.Close();
204                     throw new Exception(ex.Message);
205                 }
206             }
207         }
208     }
209     /// <summary>
210     /// 执行查询语句,返回OracleDataAdapter
211     /// </summary>
212     /// <param name="strSQL">查询语句</param>
213     /// <returns>OracleDataAdapter</returns>
214     public static OracleDataReader ExecuteReader(string strSQL)
215     {
216         OracleConnection connection = new OracleConnection(connectionString);
217         OracleCommand cmd = new OracleCommand(strSQL, connection);
218         try
219         {
220             connection.Open();
221             OracleDataReader myReader = cmd.ExecuteReader();
222             return myReader;
223         }
224         catch (OracleException ex)
225         {
226             connection.Close();
227             throw new Exception(ex.Message);
228         }
229     }
230     /// <summary>
231     /// 执行查询语句,返回DataSet
232     /// </summary>
233     /// <param name="strSQL">查询语句</param>
234     /// <returns>DataSet</returns>
235     public static DataSet Query_ReturnDataSet(string strSQL)
236     {
237         using (OracleConnection connection = new OracleConnection(connectionString))
238         {
239             DataSet ds = new DataSet();
240             try
241             {
242                 connection.Open();
243                 OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
244                 command.Fill(ds, "ds");
245             }
246             catch (OracleException ex)
247             {
248                 connection.Close();
249                 throw new Exception(ex.Message);
250             }
251             return ds;
252         }
253     }
254 
255     /// <summary>
256     /// 执行查询语句,返回DataTable
257     /// </summary>
258     /// <param name="strSQL">查询语句</param>
259     /// <returns>DataTable</returns>
260     public static DataTable Query_ReturnDatTable_old(string strSQL)
261     {      
262         using (OracleConnection connection = new OracleConnection(connectionString))       
263         {
264             DataTable dt = new DataTable("dt");
265             try
266             {
267                 connection.Open();
268                 OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
269                 command.Fill(dt);
270             }
271             catch (OracleException ex)
272             {
273                 connection.Close();
274                 throw new Exception(ex.Message);
275             }
276             return dt;
277         }
278     }
279 
280 
281 
282 
283     ///// <summary>
284     ///// 执行查询语句,返回DataTable
285     ///// </summary>
286     ///// <param name="strSQL">查询语句</param>
287     ///// <returns>DataTable</returns>
288     //public static DataTable Query_ReturnDatTable_old(string strSQL)
289     //{
290 
291     //    using (OracleConnection connection = new OracleConnection("data source=KDMESDB;User Id=kdmesdb;Password=kdmesdb;"))       
292     //    {
293     //        DataTable dt = new DataTable();
294     //        try
295     //        {
296     //            connection.Open();
297     //            OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
298     //            command.Fill(dt);
299     //        }
300     //        catch (OracleException ex)
301     //        {
302     //            connection.Close();
303     //            throw new Exception(ex.Message);
304     //        }
305     //        return dt;
306     //    }
307     //}
308 
309 
310 
311     /// <summary>
312     /// 带数据库连接参数执行查询语句,返回DataSet
313     /// </summary>
314     /// <param name="strSQL">查询语句</param>
315     /// <returns>DataSet</returns>
316     public static DataSet QueryByConn(string strSQL, string strConn)
317     {
318         using (OracleConnection connection = new OracleConnection(strConn))
319         {
320             DataSet ds = new DataSet("ds");
321             try
322             {
323                 connection.Open();
324                 OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
325                 command.Fill(ds, "ds");
326             }
327             catch (OracleException ex)
328             {
329                 connection.Close();
330                 throw new Exception(ex.Message);
331             }
332             return ds;
333         }
334     }
335     #endregion
336 
337     #region "执行带参数的SQL语句"
338     /// <summary>
339     /// 执行SQL语句,返回影响的记录数
340     /// </summary>
341     /// <param name="strSQL">SQL语句</param>
342     /// <returns>影响的记录数</returns>
343     public static int ExecuteSql(string strSQL, params OracleParameter[] cmdParms)
344     {
345         using (OracleConnection connection = new OracleConnection(connectionString))
346         {
347             using (OracleCommand cmd = new OracleCommand())
348                 try
349                 {
350                     PrepareCommand(cmd, connection, null, strSQL, cmdParms);
351                     int rows = cmd.ExecuteNonQuery();
352                     cmd.Parameters.Clear();
353                     return rows;
354                 }
355                 catch (OracleException ex)
356                 {
357                     throw new Exception(ex.Message);
358                 }
359         }
360     }
361     /// <summary>
362     /// 执行多条SQL语句,实现数据库事务。
363     /// </summary>
364     /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的OracleParameter[])</param>
365     public static void ExecuteSqlTran(Hashtable SQLStringList)
366     {
367         using (OracleConnection connection = new OracleConnection(connectionString))
368         {
369             connection.Open();
370             using (OracleTransaction trans = connection.BeginTransaction())
371             {
372                 OracleCommand cmd = new OracleCommand();
373                 try
374                 {
375                     //循环
376                     foreach (DictionaryEntry myDE in SQLStringList)
377                     {
378                         string cmdText = myDE.Key.ToString();
379                         //获得键值
380                         OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
381                         //通过键值获得值
382                         PrepareCommand(cmd, connection, trans, cmdText, cmdParms);
383                         int val = cmd.ExecuteNonQuery();
384                         cmd.Parameters.Clear();
385                         //事务提交
386                         trans.Commit();
387                     }
388                 }
389                 catch (OracleException ex)
390                 {
391                     trans.Rollback();
392                     throw new Exception(ex.Message);
393                 }
394             }
395         }
396     }
397     /// <summary>
398     /// 执行一条计算查询结果语句,返回查询结果(object)。
399     /// </summary>
400     /// <param name="strSQL">计算查询结果语句</param>
401     /// <param name="cmdParms">查询参数</param>
402     /// <returns>查询结果(object)</returns>
403     public static object GetSingle(string strSQL, params OracleParameter[] cmdParms)
404     {
405         using (OracleConnection connection = new OracleConnection(connectionString))
406         {
407             using (OracleCommand cmd = new OracleCommand())
408             {
409                 try
410                 {
411                     PrepareCommand(cmd, connection, null, strSQL, cmdParms);
412                     object obj = cmd.ExecuteScalar();
413                     cmd.Parameters.Clear();
414                     //统计功能(一行一列)
415                     if ((obj == null) || (obj == System.DBNull.Value))
416                     {
417                         return null;
418                     }
419                     else
420                     {
421                         return obj;
422                     }
423                 }
424                 catch (OracleException ex)
425                 {
426                     throw new Exception(ex.Message);
427                 }
428 
429             }
430         }
431     }
432     /// <summary>
433     /// 执行查询语句,返回OracleDataAdapter
434     /// </summary>
435     /// <param name="strSQL">查询语句</param>
436     /// <param name="cmdParms">查询参数</param>
437     /// <returns>OracleDataAdapter</returns>
438     public static OracleDataReader ExecuteReader(string strSQL, params OracleParameter[] cmdParms)
439     {
440         OracleConnection connection = new OracleConnection(connectionString);
441         OracleCommand cmd = new OracleCommand();
442         try
443         {
444             PrepareCommand(cmd, connection, null, strSQL, cmdParms);
445             OracleDataReader myReader = cmd.ExecuteReader();
446             cmd.Parameters.Clear();
447             return myReader;
448         }
449         catch (OracleException ex)
450         {
451             throw new Exception(ex.Message);
452         }
453     }
454     /// <summary>
455     /// 执行查询语句,返回DataSet
456     /// </summary>
457     /// <param name="strSQL">查询语句</param>
458     /// <param name="cmdParms">查询参数</param>
459     /// <returns>DataSet</returns>
460     public static DataSet Query(string strSQL, params OracleParameter[] cmdParms)
461     {
462         using (OracleConnection connection = new OracleConnection(connectionString))
463         {
464             OracleCommand cmd = new OracleCommand();
465             PrepareCommand(cmd, connection, null, strSQL, cmdParms);
466 
467             using (OracleDataAdapter da = new OracleDataAdapter(cmd))
468             {
469                 DataSet ds = new DataSet();
470                 try
471                 {
472                     da.Fill(ds, "ds");
473                     cmd.Parameters.Clear();
474                 }
475                 catch (OracleException ex)
476                 {
477                     throw new Exception(ex.Message);
478                 }
479                 return ds;
480             }
481         }
482     }
483     /// <summary>
484     ///在存在事务的情况下,新建数据库连接,执行sql语句,此处是关键,上面的所有功能操作都沿此而进行
485     /// 此处的sql语句可能是增加、删除、更新、查询等等操作
486     /// Prepare a command for execution
487     /// </summary>
488     /// <param name="cmd">OracleCommand对象</param>
489     /// <param name="conn">OracleConnection对象</param>
490     /// <param name="trans">OracleTransaction对象</param>
491     /// <param name="cmdType">Cmd type e.g. 存储过程或者sql语句</param>
492     /// <param name="cmdText">Command text, e.g. Select * from Products</param>
493     /// <param name="cmdParms">OracleParameters to use in the command</param>
494     private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
495     {
496         if (conn.State != ConnectionState.Open)
497         {
498             conn.Open();
499         }
500         cmd.Connection = conn;
501         cmd.CommandText = cmdText;
502         if (trans != null)
503         {
504             cmd.Transaction = trans;
505         }
506         cmd.CommandType = CommandType.Text;
507         //cmdType;
508         if (cmdParms != null)
509         {
510             foreach (OracleParameter parm in cmdParms)
511             {
512                 cmd.Parameters.Add(parm);
513             }
514         }
515     }
516     #endregion
517 
518     #region "存储过程操作"
519     /// <summary>
520     /// 执行存储过程
521     /// </summary>
522     /// <param name="storedProcName">存储过程名</param>
523     /// <param name="parameters">存储过程参数</param>
524     /// <returns>OracleDataAdapter</returns>
525     public static OracleDataReader RunProcedure(string strProcedureName, IDataParameter[] parameters)
526     {
527         OracleConnection connection = new OracleConnection(connectionString);
528         OracleDataReader returnReader;
529         connection.Open();
530         OracleCommand command = BuildQueryCommand(connection, strProcedureName, parameters);
531         command.CommandType = CommandType.StoredProcedure;
532         returnReader = command.ExecuteReader();
533         return returnReader;
534     }
535     /// <summary>
536     /// 执行存储过程
537     /// </summary>
538     /// <param name="storedProcName">存储过程名</param>
539     /// <param name="parameters">存储过程参数</param>
540     /// <param name="tableName">DataSet结果中的表名</param>
541     /// <returns>DataSet</returns>
542     public static DataSet RunProcedure(string strProcedureName, IDataParameter[] parameters, string tableName)
543     {
544         
545         try
546         {
547             using (OracleConnection connection = new OracleConnection(connectionString))
548             {
549                 DataSet dataSet = new DataSet();
550                 connection.Open();
551                 OracleDataAdapter da = new OracleDataAdapter();
552                 da.SelectCommand = BuildQueryCommand(connection, strProcedureName, parameters);
553                 da.Fill(dataSet, tableName);
554                 connection.Close();
555                 //trans.Dispose();
556                 return dataSet;
557             }
558         }
559 
560         catch (Exception ex)
561         {
562             throw ex;
563         }
564     }
565     
566     /// <summary>
567     /// 构建 OracleCommand 对象(用来返回一个结果集,而不是一个整数值)
568     /// </summary>
569     /// <param name="connection">数据库连接</param>
570     /// <param name="storedProcName">存储过程名</param>
571     /// <param name="parameters">存储过程参数</param>
572     /// <returns>OracleCommand</returns>
573     private static OracleCommand BuildQueryCommand(OracleConnection connection, string strProcedureName, IDataParameter[] parameters)
574     {
575         OracleCommand command = new OracleCommand(strProcedureName, connection);
576         command.CommandType = CommandType.StoredProcedure;
577         foreach (OracleParameter parameter in parameters)
578         {
579             command.Parameters.Add(parameter);
580         }
581         return command;
582     }
583     /// <summary>
584     /// 执行存储过程,返回影响的行数        
585     /// </summary>
586     /// <param name="storedProcName">存储过程名</param>
587     /// <param name="parameters">存储过程参数</param>
588     /// <param name="rowsAffected">影响的行数</param>
589     /// <returns></returns>
590     public static int RunProcedure(string strProcedureName, IDataParameter[] parameters, ref int rowsAffected)
591     {
592         int result = 0;
593         try
594         {
595             using (OracleConnection connection = new OracleConnection(connectionString))
596             {
597                 connection.Open();
598                 OracleCommand command = BuildIntCommand(connection, strProcedureName, parameters);
599                 OracleTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
600                 if (trans != null)
601                 {
602                     command.Transaction = trans;
603                     result = command.ExecuteNonQuery();
604                     trans.Commit();
605                 }
606             }
607         }
608         catch (Exception ex)
609         {
610             throw ex;
611         }
612         rowsAffected = result;
613         return result;
614     }
615     /// <summary>
616     /// 执行存储过程,查询,并且有输出参数    
617     /// </summary>
618     /// <param name="storedProcName">存储过程名</param>
619     /// <param name="parameters">存储过程参数</param>
620     /// <param name="rowsAffected">影响的行数</param>
621     /// <returns></returns>
622     public static string RunProcedureQuery(string strProcedureName, IDataParameter[] parameters, ref int rowsAffected)
623     {
624         string outString = "";
625         try
626         {
627             using (OracleConnection connection = new OracleConnection(connectionString))
628             {
629                 connection.Open();
630                 OracleCommand command = BuildIntCommand(connection, strProcedureName, parameters);
631                 OracleTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
632                 if (trans != null)
633                 {
634                     command.Transaction = trans;
635                     command.ExecuteNonQuery();
636                     trans.Commit();
637                 }
638                 //输出参数
639                 outString = command.Parameters["TempID"].Value.ToString();
640             }
641         }
642         catch (Exception ex)
643         {
644             throw ex;
645         }
646         rowsAffected = 0;
647         return outString;
648     }
649     /// <summary>
650     /// 创建 OracleCommand 对象实例(用来返回一个整数值)    
651     /// </summary>
652     /// <param name="storedProcName">存储过程名</param>
653     /// <param name="parameters">存储过程参数</param>
654     /// <returns>OracleCommand 对象实例</returns>
655     private static OracleCommand BuildIntCommand(OracleConnection connection, string strProcedureName, IDataParameter[] parameters)
656     {
657         OracleCommand command = BuildQueryCommand(connection, strProcedureName, parameters);
658         return command;
659     }
660     /// <summary>
661     /// 执行存储过程,没有返回任何信息,如插入数据等
662     /// </summary>
663     /// <param name="procname"></param>
664     /// <param name="cmdParms"></param>
665     public static void ExecuteProc(string procname, params OracleParameter[] cmdParms)
666     {
667         using (OracleConnection SqlConn = new OracleConnection(connectionString))
668         {
669             if (SqlConn.State == ConnectionState.Closed)
670             {
671                 SqlConn.Open();
672             }
673             OracleDataAdapter da = new OracleDataAdapter();
674             da.SelectCommand = new OracleCommand();
675             da.SelectCommand.Connection = SqlConn;
676             da.SelectCommand.CommandText = procname;
677             da.SelectCommand.CommandType = CommandType.StoredProcedure;
678             if (cmdParms != null)
679             {
680                 foreach (OracleParameter parm in cmdParms)
681                 {
682                     da.SelectCommand.Parameters.Add(parm);
683                 }
684             }
685             int i = da.SelectCommand.ExecuteNonQuery();
686             da.Dispose();
687         }
688     }
689     /// <summary>
690     /// 执行参数化的Sql语句,返回DataTable
691     /// </summary>
692     /// <param name="p_ProcedureName">sql语句</param>
693     /// <param name="p_OracleParameterArray">Parm数组</param>
694     /// <returns>返回DataTable</returns>
695     public static DataTable ExeParameDataTable(string p_ProcedureName, OracleParameter[] p_OracleParameterArray)
696     {
697         try
698         {
699             using (OracleConnection SqlConn = new OracleConnection(connectionString))
700             {
701                 if (SqlConn.State == ConnectionState.Closed)
702                 {
703                     SqlConn.Open();
704                 }
705                 DataTable dt = new DataTable();
706                 OracleDataAdapter da = new OracleDataAdapter(p_ProcedureName, SqlConn);
707                 foreach (OracleParameter Sq in p_OracleParameterArray)
708                 {
709                     da.SelectCommand.Parameters.Add(Sq);
710                 }
711                 da.Fill(dt);
712                 da.SelectCommand.Parameters.Clear();
713                 return dt;
714             }
715         }
716         catch (Exception ex)
717         {
718             throw ex;
719         }
720     }
721 
722     /// <summary>
723     /// 批量执行Command对象列表
724     /// </summary>
725     public static void ExecCmdListTrans(List<OracleCommand> list)
726     {
727         using (OracleConnection connection = new OracleConnection(connectionString))
728         {
729             if (connection.State == ConnectionState.Closed)
730             {
731                 connection.Open();
732             }
733             OracleTransaction trans = connection.BeginTransaction();
734             try
735             {
736                 for (int i = 0; i < list.Count; i++)
737                 {
738                     list[i].Connection = connection;
739                     list[i].Transaction = trans;
740                     list[i].ExecuteNonQuery();
741                 }
742                 trans.Commit();
743             }
744             catch (Exception ex)
745             {
746                 trans.Rollback();
747                 throw ex;
748             }
749         }
750     }
751     #endregion
752 
753     #region oracle select
754     /// <summary>
755     /// 执行查询语句,返回DataTable
756     /// </summary>
757     /// <param name="strSQL">查询语句</param>
758     /// <returns>DataTable</returns>
759     public static DataTable Query_ReturnDatTable(string strSQL)
760     {
761 
762         using (  OracleConnection connection = new OracleConnection(connectionString))
763         {
764             DataTable dt = new DataTable();
765             try
766             {
767                 connection.Open();
768                 OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
769                 command.Fill(dt);
770             }
771             catch (OracleException ex)
772             {
773                 connection.Close();
774                 throw new Exception(ex.Message);
775             }
776             return dt;
777         }
778     }
779 
780     #endregion
781 
782 
783 }
DbHelper类

4.2、创建接口ISQL_DAL,实现类SQL_DAL。

 1 public class SQL_DAL : ISQL_DAL
 2 {
 3     AppSettingsReader AppRead = new AppSettingsReader();
 4     private static readonly string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
 5     private static readonly string strSpecType_Completion = ConfigurationSettings.AppSettings["SpecType_Completion"];
 6     private static readonly string strSpecType_Stock = ConfigurationSettings.AppSettings["SpecType_Stock"];
 7 
 8     #region 新增托盘信息返回日志信息
 9     public void InsertReWorkConfirmLog(string tableName, string handleResult, string date, string desc)
10     {
11         try
12         {
13             string sql = "insert into meslog (tablename, handleresult, handledate, describe) values (:tablename, :handleresult, :handledate, :describe)";
14             OracleParameter[] pars = {
15                 new OracleParameter(":tablename",tableName),
16                 new OracleParameter(":handleresult",handleResult),
17                 new OracleParameter(":handledate",DateTime.Parse(date)),
18                 new OracleParameter(":describe",desc)
19             };
20             DatabaseHelper.ExecuteSql(sql, pars);
21         }
22         catch (Exception ex)
23         {
24             throw new Exception("添加总日志信息出错:" + ex.Message);
25         }
26     }
27     #endregion
SQL_DAL类

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部