using Npgsql;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Text.RegularExpressions;
|
using System.Web;
|
|
namespace MoonExp.cs
|
{
|
/// <summary>
|
/// Postgre帮助类
|
/// </summary>
|
public class PostgreHelper
|
{
|
private const string StrRegex = @"<[^>]+?style=[\w]+?:expression\(|\b(alert|confirm|prompt)\b|^\+/v(8|9)|<[^>]*?=[^>]*?&#[^>]*?>|\b(and|or)\b.{1,6}?(=|>|<|\bin\b|\blike\b)|/\*.+?\*/|<\s*script\b|<\s*img\b|\bEXEC\b|UNION.+?SELECT|UPDATE.+?SET|INSERT\s+INTO.+?VALUES|(SELECT|DELETE).+?FROM|(CREATE|ALTER|DROP|TRUNCATE)\s+(TABLE|DATABASE)";
|
|
private string conStr = null;
|
|
public PostgreHelper()
|
{
|
this.conStr = ConfigurationManager.AppSettings["pgConn"];
|
}
|
|
/// <summary>
|
/// 连接字符串
|
/// </summary>
|
public String ConStr
|
{
|
get
|
{
|
return this.conStr;
|
}
|
}
|
|
/// <summary>
|
/// 获取DataTable
|
/// </summary>
|
public DataTable GetDataTable(string sql, params DbParameter[] args)
|
{
|
using (NpgsqlConnection con = new NpgsqlConnection(ConStr))
|
{
|
if (con.State != ConnectionState.Open)
|
{
|
con.Open();
|
}
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, con))
|
{
|
if (args != null)
|
{
|
foreach (DbParameter arg in args)
|
{
|
//arg.ParameterName = arg.ParameterName.Replace("@", ":");
|
cmd.Parameters.Add(arg);
|
}
|
}
|
|
using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(cmd))
|
{
|
DataTable dt = new DataTable();
|
adapter.Fill(dt);
|
|
return dt;
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 获取记录数
|
/// </summary>
|
public int GetCount(string sql, params DbParameter[] args)
|
{
|
using (NpgsqlConnection con = new NpgsqlConnection(ConStr))
|
{
|
if (con.State != ConnectionState.Open)
|
{
|
con.Open();
|
}
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, con))
|
{
|
if (args != null)
|
{
|
foreach (DbParameter arg in args)
|
{
|
//arg.ParameterName = arg.ParameterName.Replace("@", ":");
|
cmd.Parameters.Add(arg);
|
}
|
}
|
|
object obj = cmd.ExecuteScalar();
|
|
return obj == null ? 0 : Convert.ToInt32(obj);
|
}
|
}
|
}
|
|
public Object GetScalar(string sql, params DbParameter[] args)
|
{
|
using (NpgsqlConnection con = new NpgsqlConnection(ConStr))
|
{
|
if (con.State != ConnectionState.Open)
|
{
|
con.Open();
|
}
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, con))
|
{
|
if (args != null)
|
{
|
foreach (DbParameter arg in args)
|
{
|
cmd.Parameters.Add(arg);
|
}
|
}
|
|
return cmd.ExecuteScalar();
|
}
|
}
|
}
|
|
public int GetIntScalar(string sql, params DbParameter[] args)
|
{
|
using (NpgsqlConnection con = new NpgsqlConnection(ConStr))
|
{
|
if (con.State != ConnectionState.Open)
|
{
|
con.Open();
|
}
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, con))
|
{
|
if (args != null)
|
{
|
foreach (DbParameter arg in args)
|
{
|
cmd.Parameters.Add(arg);
|
}
|
}
|
|
object obj = cmd.ExecuteScalar();
|
|
return null == obj ? 0 : Convert.ToInt32(obj);
|
}
|
}
|
}
|
|
/// <summary>
|
/// 执行SQL，返回影响行数
|
/// </summary>
|
public int ExecuteNonQuery(string sql, params DbParameter[] args)
|
{
|
using (NpgsqlConnection con = new NpgsqlConnection(ConStr))
|
{
|
if (con.State != ConnectionState.Open)
|
{
|
con.Open();
|
}
|
|
using (NpgsqlCommand cmd = new NpgsqlCommand(sql, con))
|
{
|
if (args != null)
|
{
|
foreach (DbParameter arg in args)
|
{
|
//arg.ParameterName = arg.ParameterName.Replace("@", ":");
|
cmd.Parameters.Add(arg);
|
}
|
}
|
|
return cmd.ExecuteNonQuery();
|
}
|
}
|
}
|
|
/// <summary>
|
/// 检查字符串
|
/// </summary>
|
public static bool CheckStr(string input)
|
{
|
return Regex.IsMatch(input, StrRegex, RegexOptions.IgnoreCase);
|
}
|
}
|
}
|
