tongbu/TimedUpload/utils/NpgsqlHelper.cs

using log4net;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace TimedUpload
{
	class NpgsqlHelper
	{
		private static readonly ILog log = LogManager.GetLogger(typeof(NpgsqlHelper));
		private static string ConStr = ConfigurationManager.AppSettings["dbNpgsql"];
		public static int ExecuteNonQuery(string sql)
		{
			int result = -1;
			using (NpgsqlConnection connection = new NpgsqlConnection(ConStr))
			{
				using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
				{
					try
					{
						connection.Open();
						result = command.ExecuteNonQuery();
						log.Info(sql);
					}
					catch (NpgsqlException exception)
					{
						log.Error(string.Format("执行SQL[{0}]出错，详细信息为：{1}", sql, exception.Message));
					}
					finally
					{
						connection.Close();
					}
				}
			}
			return result;
		}
		public static object ExecuteScalar(string sql)
		{
			object obj = null;
			using (NpgsqlConnection connection = new NpgsqlConnection(ConStr))
			{
				using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
				{
					try
					{
						connection.Open();
						obj = command.ExecuteScalar();
						log.Info(sql);
					}
					catch (NpgsqlException exception)
					{
						obj = -1;
						log.Error(string.Format("执行SQL[{0}]出错，详细信息为：{1}", sql, exception.Message));
					}
					finally
					{
						connection.Close();
					}
				}
			}
			return obj;
		}
		public static DataSet ExecuteQuery(string sql)
		{

			DataSet result = new DataSet();
			using (NpgsqlConnection connection = new NpgsqlConnection(ConStr))
			{
				using (NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(sql, connection))
				{
					try
					{
						connection.Open();
						dataAdapter.Fill(result);
						log.Info(sql);
					}
					catch (NpgsqlException exception)
					{
						log.Error(string.Format("执行SQL【{0}】出错，详细信息为：{1}", sql, exception.Message));
					}
					finally
					{
						connection.Close();
					}
				}
			}
			return result;
		}
		public static string DesDecrypt(string decryptString, string key)
		{
			byte[] keyBytes = Encoding.UTF8.GetBytes(key.Substring(0, 8));
			byte[] keyIV = keyBytes;
			byte[] inputByteArray = Convert.FromBase64String(decryptString);
			DESCryptoServiceProvider provider = new DESCryptoServiceProvider();
			MemoryStream mStream = new MemoryStream();
			CryptoStream cStream = new CryptoStream(mStream, provider.CreateDecryptor(keyBytes, keyIV), CryptoStreamMode.Write);
			cStream.Write(inputByteArray, 0, inputByteArray.Length);
			cStream.FlushFinalBlock();
			return Encoding.UTF8.GetString(mStream.ToArray());
		}
	}
}