using UDP.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Reflection;
namespace UDP
{
///
/// DataBase实例
///
public class DataBase : IDisposable
{
#region 基础信息
///
/// sql链接字符串
///
internal static string connectionString = string.Empty;
///
/// sql 链接基础信息
///
internal static string user,
pwd,
server,
dbname;
#endregion
#region SQL五大对象
///
/// sql链接类
///
private SqlConnection _conn;
private SqlConnection Conn
{
get
{
if (_conn != null)
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
return _conn;
}
else
{
_conn = new SqlConnection(connectionString);
_conn.Open();
return _conn;
}
}
}
private SqlCommand Command = null;
private SqlTransaction tr = null;
public SqlCommand CreateCommand(string sql)
{
Command = Conn.CreateCommand();
sql = Regex.Replace(sql, "xp_cmdshell", "", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, "master", "", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, "master.dbo.sp_addlogin", "", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, "sp_addlogin", "", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, "drop", "", RegexOptions.IgnoreCase);
sql = Regex.Replace(sql, "delete", "", RegexOptions.IgnoreCase);
Command.CommandText = sql;
if (tr != null) Command.Transaction = tr;
return Command;
}
#endregion
#region sqlConnection状态
///
/// sqlConnection状态
///
public ConnectionState ConnectionState
{
get
{
return Conn.State;
}
}
#endregion
#region 打开链接
///
/// 打开连接
///
public void Open()
{
if (Conn.State == ConnectionState.Closed)
{
try
{
Conn.Open();
}
catch
{
throw;
}
}
}
#endregion
#region 事务处理
///
/// 开始事务
///
public void Transaction()
{
tr = Conn.BeginTransaction();
}
///
/// 提交事务
///
public void Commit()
{
tr.Commit();
}
///
/// 回滚事务
///
public void Rollback()
{
tr.Rollback();
}
///
/// 初始化SqlCommand对象
///
/// 执行的文本
///
#endregion
#region 根据sql执行ExecuteNonQuery并返回影响的条数
///
/// 根据sql执行ExecuteNonQuery
///
public int ExecuteNonQuery(string sql)
{
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
return Command.ExecuteNonQuery();
}
#endregion
#region 根据sql执行插入
///
/// 根据sql执行插入
///
/// 插入sql
/// 是否返回自增ID
///
public int Insert(string sql, bool Identity)
{
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
if (Identity)
{
Command.ExecuteNonQuery();
Command.CommandText = "SELECT @@Identity";
return Convert.ToInt32(Command.ExecuteScalar());
}
return Command.ExecuteNonQuery();
}
#endregion
#region 根据sql执行ExecuteScalar
///
/// 根据sql执行ExecuteScalar
///
public object ExecuteScalar(string sql, JSON pams = null)
{
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
if (pams != null)
{
Command.Parameters.Clear();
foreach (var item in pams)
{
Command.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
return Command.ExecuteScalar();
}
#endregion
#region 根据sql获取JSON文本
///
/// 根据sql获取JSON文本结果
///
public string GetJSONString(string sql, JSON pams = null)
{
DataTable tb = new DataTable();
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = Command;
if (pams != null)
{
da.SelectCommand.Parameters.Clear();
foreach (var item in pams)
{
da.SelectCommand.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
da.Fill(tb);
}
return JSON.ToString(tb);
}
#endregion
#region 根据sql获取JSON文本
///
/// 根据sql获取JSON文本结果
///
public string GetJSONString(string sql, int rowIndex, JSON pams = null)
{
var js = GetJSON(sql, rowIndex, pams);
if (js == null) return null;
return js.ToString();
}
#endregion
#region 根据sql获取JSON数组结果
///
/// 根据sql获取JSON数组结果
///
public JSON[] GetJSON(string sql, JSON pams = null)
{
if (Command == null) Command = CreateCommand(sql);
Command.CommandText = sql;
DataTable tb = new DataTable();
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = Command;
if (pams != null)
{
da.SelectCommand.Parameters.Clear();
foreach (var item in pams)
{
da.SelectCommand.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
da.Fill(tb);
}
return JSON.ToJSONArray(JSON.ToString(tb));
}
///
/// 根据sql获取JSON结果
///
public JSON GetJSON(string sql, int rowIndex)
{
JSON[] result = GetJSON(sql);
if (result.Length == 0) return null;
return result[rowIndex];
}
#endregion
#region 根据sql获取JSON结果
///
/// 根据sql获取JSON结果
///
public JSON GetJSON(string sql, int rowIndex, JSON param = null)
{
JSON[] result = GetJSON(sql, param);
if (result.Length == 0) return null;
return result[rowIndex];
}
#endregion
#region 更新表
///
/// 更新表
///
/// 表名
/// where 语句之后的条件 例如ID=1
/// 更新的数据
///
public int Update(string table, string condition, JSON data)
{
string sql = "update " + table + " set {0} where " + condition;
List ls = new List();
foreach (KeyValuePair kv in data)
{
ls.Add(string.Format("{0}='{1}'", "[" + kv.Key + "]", kv.Value.ToString().Replace("'", "''")));
}
sql = string.Format(sql, string.Join(",", ls));
return this.ExecuteNonQuery(sql);
}
#endregion
#region 插入表并返回自增ID
///
/// 插入表
///
/// 表名
/// 是否返回自增长ID
/// 插入的数据
///
public int Insert(string table, bool isIdentity, JSON data)
{
string sql = "insert into " + table + " ({0}) values ({1})";
List ns = new List();
List vs = new List();
foreach (KeyValuePair kv in data)
{
ns.Add("[" + kv.Key + "]");
vs.Add(kv.Value);
}
sql = string.Format(sql, string.Join(",", ns), "'" + string.Join("','", vs) + "'");
return this.Insert(sql, isIdentity);
}
#endregion
#region 获取sql返回值,结果为NULL则返回默认值
///
/// 获取sql返回值,结果为NULL则返回默认值
///
///
///
///
public int getInt(string sql, int defalutValue = 0, JSON pams = null)
{
object result = ExecuteScalar(sql, pams);
return (result == DBNull.Value || result == null) ? defalutValue : Convert.ToInt32(result);
}
public decimal getDecimal(string sql, decimal defalutValue, JSON pams = null)
{
object result = ExecuteScalar(sql, pams);
return (result == DBNull.Value || result == null) ? defalutValue : Convert.ToDecimal(result);
}
#endregion
#region 分页查询
///
/// 分页查询
///
/// 表名称
/// 页索引
/// 页条数
/// 排序字段
/// 排序方式
/// 返回字段
/// 查询条件
///
public string PageSqlToJsonString(
string Table
, int PageIndex
, int PageSize
, string orderby
, string shortby
, string Columns
, string condition)
{
string sql = string.Format(@"
select
x.*
from
(
select row$id=ROW_NUMBER() over (order by {0} {1}),{2} from {3} {4}
)x
where x.row$id>{5} and x.row$id<={6} "
, orderby
, shortby
, Columns
, Table
, string.IsNullOrEmpty(condition) ? "" : condition
, PageIndex * PageSize
, (PageIndex + 1) * PageSize
);
return this.GetJSONString(sql);
}
public DataTable PageSqlToDataTable(
string Table
, int PageIndex
, int PageSize
, string orderby
, string shortby
, string Columns
, string condition)
{
string sql = string.Format(@"
select
x.*
from
(
select row$id=ROW_NUMBER() over (order by {0} {1}),{2} from {3} {4}
)x
where x.row$id>{5} and x.row$id<={6} "
, orderby
, shortby
, Columns
, Table
, string.IsNullOrEmpty(condition) ? "" : condition
, PageIndex * PageSize
, (PageIndex + 1) * PageSize
);
return this.Query(sql);
}
#endregion
#region 获取指定列索引数据
///
/// 获取某一列数据
///
/// 查询sql
/// 索引列
///
public string[] getColumn(string sql, int index, JSON pams = null)
{
DataTable tb = new DataTable();
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = Command;
if (pams != null)
{
da.SelectCommand.Parameters.Clear();
foreach (var item in pams)
{
Command.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
da.Fill(tb);
}
return tb.Select().Select(x => x[index].ToString()).ToArray();
}
#endregion
#region 获取指定行索引数据
///
/// 获取某一行数据
///
/// 查询sql
/// 索引行
///
public string[] getRow(string sql, int index, JSON pams = null)
{
DataTable tb = new DataTable();
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = Command;
if (pams != null)
{
da.SelectCommand.Parameters.Clear();
foreach (var item in pams)
{
Command.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
da.Fill(tb);
}
return tb.Rows[index].ItemArray.Select(x =>
{
return x.ToString();
}).ToArray();
}
#endregion
#region 获取Table
///
/// 根据sql获取Table
///
/// 查询sql
///
public DataTable Query(string sql, JSON pams = null)
{
DataTable tb = new DataTable();
if (Command == null) { Command = CreateCommand(sql); }
Command.CommandText = sql;
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = Command;
if (pams != null)
{
da.SelectCommand.Parameters.Clear();
foreach (var item in pams)
{
Command.Parameters.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
da.Fill(tb);
}
return tb;
}
#endregion
#region 获取Value
//public dynamic getValue(string sql, JSON param = null, dynamic defaultValue = null)
//{
// object result = ExecuteScalar(sql, param);
// return (result == DBNull.Value || result == null) ? (defaultValue == null ? null : defaultValue) : result;
//}
public dynamic getValue(string sql, dynamic param = null, dynamic defaultValue = null)
{
object result;
if (param != null && param.GetType().IsClass && param.GetType().Name != "String")
{
if (param.GetType().FullName == "UDP.Data.JSON")
{
result = ExecuteScalar(sql, param);
return (result == DBNull.Value || result == null) ? (defaultValue == null ? null : defaultValue) : result;
}
else
{
Type type = param.GetType();
PropertyInfo[] propertyInfos = type.GetProperties();
JSON parma2 = new JSON();
foreach (var item in propertyInfos)
{
object objName = param.GetType().GetProperty(item.Name).GetValue(param);
parma2.Add(item.Name, objName);
}
result = ExecuteScalar(sql, parma2);
return (result == DBNull.Value || result == null) ? (defaultValue == null ? null : defaultValue) : result;
}
}
else
{
result = ExecuteScalar(sql);
return (result == DBNull.Value || result == null) ? defaultValue : result;
}
}
#endregion
#region 获取Value
public dynamic getValue(string sql, dynamic defaultValue)
{
object result = ExecuteScalar(sql);
return (result == DBNull.Value || result == null) ? (defaultValue == null ? null : defaultValue) : result;
}
#endregion
#region 资源回收
///
/// 资源回收
///
public void Dispose()
{
/* 释放Transaction */
if (tr != null) tr.Dispose();
/* 释放Command */
if (Command != null) Command.Dispose();
/* 释放Connection */
if (this._conn != null)
{
if (this._conn.State != ConnectionState.Closed) _conn.Close();
this._conn.Dispose();
}
}
#endregion
}
}