using System.Collections.Generic;
|
using System.Data;
|
using System.Data.SqlClient;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text.RegularExpressions;
|
using System.Threading.Tasks;
|
|
namespace WenSkin.SQL
|
{
|
|
public class SqlConnect
|
{
|
|
public SqlConnect() : this("SqlServer")
|
{
|
}
|
public SqlConnect(string server, string database, string uid, string pad)
|
{
|
this.Server = server;
|
this.Database = database;
|
this.Uid = uid;
|
this.Pad = pad;
|
|
Contr = $"Data Source={this.Server};Initial Catalog={this.Database};User ID={this.Uid};Password={this.Pad};Persist Security Info=True;TrustServerCertificate=True";
|
}
|
|
public SqlConnect(string configName)
|
{
|
WenConfig config = new WenConfig();
|
this.Server = config.Groups[configName, "Server"];
|
this.Database = config.Groups[configName, "Database"];
|
this.Uid = config.Groups[configName, "Uid"];
|
this.Pad = config.Groups[configName, "Pad"];
|
|
Contr = $"Data Source={this.Server};Initial Catalog={this.Database};User ID={this.Uid};Password={this.Pad};Persist Security Info=True;TrustServerCertificate=True";
|
}
|
public SqlConnect(WenConfig config, string configName = "SqlServer")
|
{
|
this.Server = config.Groups[configName, "Server"];
|
this.Database = config.Groups[configName, "Database"];
|
this.Uid = config.Groups[configName, "Uid"];
|
this.Pad = config.Groups[configName, "Pad"];
|
|
Contr = $"Data Source={this.Server};Initial Catalog={this.Database};User ID={this.Uid};Password={this.Pad};Persist Security Info=True;TrustServerCertificate=True";
|
}
|
|
#region 公有属性
|
|
public SqlConnection SqlCon => SqlConAsync().Result;
|
public async Task<SqlConnection> SqlConAsync() => await Task.Run(() => new SqlConnection(this.Contr));
|
public string Contr { get; set; }
|
public string Server { get; set; }
|
public string Database { get; set; }
|
public string Uid { get; set; }
|
public string Pad { get; set; }
|
|
#endregion
|
|
#region 重写
|
|
public override string ToString()
|
{
|
return Contr;
|
}
|
|
#endregion
|
|
/// <summary>
|
/// 测试数据库是否能成功过链接
|
/// </summary>
|
/// <returns></returns>
|
public bool SqlTestLook()
|
{
|
try
|
{
|
var con = new SqlConnection(Contr);
|
con.Open();
|
}
|
catch
|
{
|
return false;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 返回查询的数据表集合
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public DataSet SqlDataSet(string sql)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
DataSet ds = new DataSet();
|
try
|
{
|
con.Open();
|
SqlDataAdapter da = new SqlDataAdapter(sql, con);
|
da.Fill(ds);
|
return ds;
|
}
|
catch
|
{
|
return null;
|
}
|
finally
|
{
|
con.Dispose();
|
}
|
}
|
/// <summary>
|
/// 返回查询后的第一个表
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public DataTable SqlDataTable(string sql)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
DataSet ds = new DataSet();
|
try
|
{
|
con.Open();
|
SqlDataAdapter da = new SqlDataAdapter(sql, con);
|
da.Fill(ds);
|
return ds.Tables[0];
|
}
|
catch
|
{
|
return null;
|
}
|
finally
|
{
|
con.Dispose();
|
ds.Dispose();
|
}
|
|
}
|
|
public async Task<DataTable> SqlDataTableAsync(string sql) => await Task.Run(() => SqlDataTable(sql));
|
|
/// <summary>
|
/// 返回查询指定的表
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <param name="from"></param>
|
/// <returns></returns>
|
public DataTable SqlDataTable(string sql, int from)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
DataSet ds = new DataSet();
|
try
|
{
|
con.Open();
|
SqlDataAdapter da = new SqlDataAdapter(sql, con);
|
da.Fill(ds);
|
return ds.Tables[from];
|
}
|
catch
|
{
|
return null;
|
}
|
finally
|
{
|
con.Dispose();
|
ds.Dispose();
|
}
|
|
}
|
public async Task<object> SqlToOneObjectAsync(string sql) => await Task.Run(() => SqlToOneObject(sql));
|
/// <summary>
|
/// 返回数据第一行第一个值
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public object SqlToOneObject(string sql)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
SqlCommand com = new SqlCommand(sql, con);
|
try
|
{
|
con.Open();
|
return com.ExecuteScalar();
|
}
|
catch
|
{
|
return null;
|
}
|
finally
|
{
|
con.Dispose();
|
com.Dispose();
|
}
|
}
|
/// <summary>
|
/// 返回受影响行数
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public int SqlToInt(string sql)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
SqlCommand com = new SqlCommand(sql, con);
|
try
|
{
|
con.Open();
|
if (sql.Contains("insert") || sql.Contains("update") || sql.Contains("delete"))
|
return com.ExecuteNonQuery();
|
else
|
{
|
return SqlDataTable(sql).Rows.Count;
|
}
|
}
|
catch
|
{
|
return 0;
|
}
|
finally
|
{
|
con.Dispose();
|
com.Dispose();
|
}
|
}
|
/// <summary>
|
/// 返回执行结果
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public bool SqlBool(string sql)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
SqlCommand com = new SqlCommand(sql, con);
|
try
|
{
|
con.Open();
|
com.ExecuteScalar();
|
return true;
|
}
|
catch
|
{
|
return false;
|
}
|
finally
|
{
|
con.Dispose();
|
com.Dispose();
|
}
|
}
|
public async Task<bool> SqlBoolAsync(string sql) => await Task.Run(() => SqlBool(sql));
|
|
/// <summary>
|
/// 返回查询数据是否存在
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public bool SqlSelectValueBool(string sql)
|
{
|
if (sql.Contains("insert") || sql.Contains("update") || sql.Contains("delete"))
|
{
|
if (SqlToInt(sql) > 0)
|
return true;
|
}
|
else
|
{
|
if (SqlToOneObject(sql) != null)
|
return true;
|
}
|
return false;
|
}
|
/// <summary>
|
/// 返回数据后输出的消息内容
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public string SqlToPrint(string sql)
|
{
|
SqlConnection con = new SqlConnection(Contr);
|
SqlCommand com = new SqlCommand(sql, con);
|
try
|
{
|
string message = "";
|
void InfoMessage_SqlInfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
|
{
|
message = e.Message;
|
}
|
con.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage_SqlInfoMessageEventHandler);
|
con.Open();
|
com.ExecuteNonQuery();
|
return message;
|
}
|
catch
|
{
|
return null;
|
}
|
finally
|
{
|
con.Dispose();
|
com.Dispose();
|
}
|
}
|
/// <summary>
|
/// sql查询语句返回list数组
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public List<T> SqlToList<T>(string sql) where T : new()
|
{
|
DataTable dt = SqlDataTable(sql);
|
List<T> ts = DLL.DataTableList.ConvertToModel<T>(dt);
|
return ts;
|
}
|
|
#region list 转数据库插入语句，并执行
|
|
/// <summary>
|
/// list 转数据库插入语句，并执行
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="t"></param>
|
/// <returns></returns>
|
public bool ListToSqlInsert<T>(List<T> t)
|
{
|
var props = typeof(T).GetProperties();
|
|
string sql = string.Empty;
|
|
string tableName = typeof(T).Name;
|
|
string tableCloumns = string.Empty;
|
foreach (PropertyInfo pi in props)
|
{
|
tableCloumns += pi.Name + ",";
|
}
|
tableCloumns = tableCloumns.Remove(tableCloumns.Length - 1);
|
|
if (t.Count() > 0)
|
{
|
for (int i = 0; i < t.Count(); i++)
|
{
|
string sqlInsert = "insert into {0} ({1})values({2}) \r\n";
|
string tableValue = string.Empty;
|
foreach (PropertyInfo pi in props)
|
{
|
object obj = pi.GetValue(t.ElementAt(i), null);
|
tableValue += string.Format("N'{0}',", obj?.ToString());
|
}
|
tableValue = tableValue.Remove(tableValue.Length - 1);
|
sql += string.Format(sqlInsert, tableName, tableCloumns, tableValue);
|
}
|
}
|
return SqlBool(sql);
|
}
|
public bool ToSqlInsert<T>(T t)
|
{
|
var props = typeof(T).GetProperties();
|
|
string sql = string.Empty;
|
|
string tableName = typeof(T).Name;
|
|
string tableCloumns = string.Empty;
|
foreach (PropertyInfo pi in props)
|
{
|
tableCloumns += pi.Name + ",";
|
}
|
tableCloumns = tableCloumns.Remove(tableCloumns.Length - 1);
|
|
|
string sqlInsert = "insert into {0} ({1})values({2}) \r\n";
|
string tableValue = string.Empty;
|
foreach (PropertyInfo pi in props)
|
{
|
object obj = pi.GetValue(t, null);
|
tableValue += string.Format("N'{0}',", obj?.ToString());
|
}
|
tableValue = tableValue.Remove(tableValue.Length - 1);
|
sql += string.Format(sqlInsert, tableName, tableCloumns, tableValue);
|
|
return SqlBool(sql);
|
}
|
|
#endregion
|
|
#region 参数化数据查询，返回第一个datatable 避免数据库注入
|
|
/// <summary>
|
/// 参数化数据查询，返回第一个datatable 避免数据库注入
|
/// </summary>
|
/// <param name="comText">数据库语句</param>
|
/// <param name="id">参数id</param>
|
/// <returns></returns>
|
public DataTable ParameterDataTable(string comText, string id)
|
{
|
SqlCommand cmd = new SqlCommand
|
{
|
Connection = SqlCon,
|
CommandText = comText
|
};
|
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar) { Value = id });
|
cmd.ExecuteNonQuery();
|
SqlDataAdapter ada = new SqlDataAdapter(cmd);
|
DataSet ds = new DataSet();
|
ada.Fill(ds);
|
return ds.Tables[0];
|
}
|
public DataTable ParameterDataTable(string comText, params Parameter[] parameters)
|
{
|
SqlCommand cmd = new SqlCommand
|
{
|
Connection = SqlCon,
|
CommandText = comText
|
};
|
|
List<SqlParameter> sqlParameters = new List<SqlParameter>();
|
foreach (var item in parameters)
|
{
|
sqlParameters.Add(new SqlParameter("@" + item.Name, SqlDbType.NVarChar) { Value = item.Value });
|
}
|
cmd.Parameters.AddRange(sqlParameters.ToArray());
|
cmd.ExecuteNonQuery();
|
SqlDataAdapter ada = new SqlDataAdapter(cmd);
|
DataSet ds = new DataSet();
|
ada.Fill(ds);
|
return ds.Tables[0];
|
}
|
/// <summary>
|
/// 数据库参数化声明
|
/// </summary>
|
public class Parameter
|
{
|
public Parameter()
|
{
|
Name = "id";
|
Value = "";
|
}
|
public Parameter(string name, string value)
|
{
|
Name = name;
|
Value = value;
|
}
|
public string Name { get; set; }
|
public string Value { get; set; }
|
}
|
|
#endregion
|
}
|
}
|
