You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1203 lines
41 KiB
1203 lines
41 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Reflection;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace XS_DAL
|
|
{
|
|
/// <summary>
|
|
/// 数据库操作
|
|
/// </summary>
|
|
public static class DBOperator
|
|
{
|
|
/// <summary>
|
|
/// 数据库帮助类
|
|
/// </summary>
|
|
private static DBHelper dbo;
|
|
private static DBHelper dbo2;
|
|
|
|
private static int ty = 0;
|
|
|
|
/// <summary>
|
|
/// 初始化数据库帮助类
|
|
/// </summary>
|
|
private static void Init(int i = 0)
|
|
{
|
|
if (dbo == null && i == 0)
|
|
{
|
|
//数据库类型
|
|
string DbType = ConfigurationManager.AppSettings["DbType"];
|
|
//连接字符串
|
|
string DbConnectionString = "";
|
|
|
|
if (i == 1)
|
|
{
|
|
DbConnectionString = ConfigurationManager.ConnectionStrings["connstring02"].ConnectionString;//升降库中表连接
|
|
}
|
|
else
|
|
{
|
|
DbConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
|
|
}
|
|
|
|
//数据库提供程序的固定名称
|
|
string dbProviderName = string.Empty;
|
|
|
|
switch (DbType.ToUpper())
|
|
{
|
|
case "SQL":
|
|
dbProviderName = "System.Data.SqlClient";
|
|
break;
|
|
case "ORACLE":
|
|
dbProviderName = "System.Data.OracleClient";
|
|
break;
|
|
case "MYSQL":
|
|
dbProviderName = "MySql.Data.MySqlClient";
|
|
break;
|
|
case "DB2":
|
|
dbProviderName = "IBM.Data.DB2";
|
|
break;
|
|
}
|
|
|
|
dbo = new DBHelper(DbConnectionString, dbProviderName);
|
|
}
|
|
|
|
if (dbo2 == null && i == 1)
|
|
{
|
|
//数据库类型
|
|
string DbType = ConfigurationManager.AppSettings["DbType"];
|
|
//连接字符串
|
|
string DbConnectionString = "";
|
|
|
|
if (i == 1)
|
|
{
|
|
DbConnectionString = ConfigurationManager.ConnectionStrings["connstring02"].ConnectionString;//升降库中表连接
|
|
}
|
|
else
|
|
{
|
|
DbConnectionString = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
|
|
}
|
|
|
|
//数据库提供程序的固定名称
|
|
string dbProviderName = string.Empty;
|
|
|
|
switch (DbType.ToUpper())
|
|
{
|
|
case "SQL":
|
|
dbProviderName = "System.Data.SqlClient";
|
|
break;
|
|
case "ORACLE":
|
|
dbProviderName = "System.Data.OracleClient";
|
|
break;
|
|
case "MYSQL":
|
|
dbProviderName = "MySql.Data.MySqlClient";
|
|
break;
|
|
case "DB2":
|
|
dbProviderName = "IBM.Data.DB2";
|
|
break;
|
|
}
|
|
|
|
dbo2 = new DBHelper(DbConnectionString, dbProviderName);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询表主键
|
|
/// </summary>
|
|
public static List<string> GetKeyColumn<T>(this T model, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "select column_name as key_col from information_schema.key_column_usage where table_name = '" + t.Name.ToLower() + "';";
|
|
|
|
DataTable dt = new DataTable();
|
|
if (i == 0)
|
|
{
|
|
dt = dbo.GetDataTable(sql);
|
|
}
|
|
else
|
|
{
|
|
dt = dbo2.GetDataTable(sql);
|
|
}
|
|
|
|
List<string> keys = new List<string>();
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
keys.Add(dr["key_col"].ToString().ToLower());
|
|
}
|
|
|
|
return keys;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return new List<string>();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 查询自增长列
|
|
/// </summary>
|
|
public static List<string> GetAutoKeyColumn<T>(this T model, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "select name as key_auto_col from syscolumns where colstat = '1' and id = object_id('" + t.Name.ToLower() + "');";
|
|
|
|
DataTable dt = new DataTable();
|
|
if (i == 0)
|
|
{
|
|
dt = dbo.GetDataTable(sql);
|
|
}
|
|
else
|
|
{
|
|
dt = dbo2.GetDataTable(sql);
|
|
}
|
|
|
|
List<string> keys = new List<string>();
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
keys.Add(dr["key_auto_col"].ToString().ToUpper());
|
|
}
|
|
|
|
return keys;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return new List<string>();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 插入单条数据
|
|
/// </summary>
|
|
public static bool Insert<T>(this T model, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
string sql = string.Empty;
|
|
List<string> strSql1 = new List<string>();
|
|
List<string> strSql2 = new List<string>();
|
|
|
|
Type t = model.GetType();
|
|
PropertyInfo[] pInfo = t.GetProperties();
|
|
List<string> aColumn = GetAutoKeyColumn(model);
|
|
|
|
foreach (var item in pInfo.Where(c => !aColumn.Contains(c.Name.ToUpper())))
|
|
{
|
|
string tName = item.PropertyType.Name;
|
|
|
|
if (item.PropertyType.IsGenericType)
|
|
{
|
|
if (item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
tName = item.PropertyType.GetGenericArguments()[0].Name;
|
|
}
|
|
}
|
|
|
|
switch (tName.ToLower())
|
|
{
|
|
case "boolean":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "byte":
|
|
case "short":
|
|
case "int32":
|
|
case "int64":
|
|
case "decimal":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(item.GetValue(model, null).ToString());
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "string":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("''");
|
|
}
|
|
break;
|
|
case "datetime":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
sql = "insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");";
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 按条件更新数据
|
|
/// </summary>
|
|
public static bool Update<T>(this T model, string strSet, string strWhere = "", int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "update " + t.Name.ToLower() + " set " + strSet;
|
|
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
{
|
|
sql += " where " + strWhere;
|
|
}
|
|
|
|
sql += ";";
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 按条件删除数据
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="model"></param>
|
|
/// <param name="strWhere"></param>
|
|
/// <param name="i"></param>
|
|
/// <returns></returns>
|
|
public static bool Delete<T>(this T model, string strWhere = "", int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "delete " + t.Name.ToLower();
|
|
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
{
|
|
sql += " where " + strWhere;
|
|
}
|
|
|
|
sql += ";";
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 按条件查询对象实体
|
|
/// </summary>
|
|
public static DataTable GetTable<T>(this T model, string strWhere = "", string strOrderBy = "", int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "select * from " + t.Name.ToLower();
|
|
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
{
|
|
sql += " where " + strWhere;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(strOrderBy))
|
|
{
|
|
sql += " order by " + strOrderBy;
|
|
}
|
|
|
|
sql += ";";
|
|
if (i == 0)
|
|
{
|
|
return dbo.GetDataTable(sql);
|
|
}
|
|
else
|
|
{
|
|
return dbo2.GetDataTable(sql);
|
|
}
|
|
//return dbo.GetDataTable(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return new DataTable();
|
|
}
|
|
}
|
|
|
|
public static List<T> GetModelList<T>(this T model, string strWhere = "", string strOrderBy = "", int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "select * from " + t.Name.ToLower();
|
|
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
{
|
|
sql += " where " + strWhere;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(strOrderBy))
|
|
{
|
|
sql += " order by " + strOrderBy;
|
|
}
|
|
|
|
sql += ";";
|
|
if (i == 0)
|
|
{
|
|
return ToDataList<T>(dbo.GetDataTable(sql));
|
|
}
|
|
else
|
|
{
|
|
return ToDataList<T>(dbo2.GetDataTable(sql));
|
|
}
|
|
//return dbo.GetDataTable(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return new List<T>();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 分页查询结果排序(参数均必填)
|
|
/// </summary>
|
|
/// <param name="strName">视图或表名</param>
|
|
/// <param name="strWhere">查询条件</param>
|
|
/// <param name="strOrderby">排序条件</param>
|
|
/// <param name="page">页码</param>
|
|
/// <param name="lines">每页行数</param>
|
|
/// <returns></returns>
|
|
public static DataTable GetTable<T>(this T model, string strWhere, string strOrderby, int page, int lines, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
if (page == 0 || lines == 0 || string.IsNullOrWhiteSpace(strWhere) || string.IsNullOrWhiteSpace(strOrderby))
|
|
{
|
|
return new DataTable();
|
|
}
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "select * from (select row_number() over (order by " + strOrderby + ") as i_row,* from " + t.Name.ToLower() + " where " + strWhere
|
|
|
|
+ ") q where q.i_row between " + (lines * (page - 1) + 1) + " and " + lines * page + ";";
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.GetDataTable(sql);
|
|
}
|
|
else
|
|
{
|
|
return dbo2.GetDataTable(sql);
|
|
}
|
|
//return dbo.GetDataTable(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return new DataTable();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 数据行转对象实体
|
|
/// </summary>
|
|
public static T DataRowToModel<T>(this T model, DataRow row)
|
|
{
|
|
if (row != null)
|
|
{
|
|
Type t = model.GetType();
|
|
T temp = (T)Activator.CreateInstance(t);
|
|
PropertyInfo[] pInfo = t.GetProperties();
|
|
|
|
foreach (var item in pInfo)
|
|
{
|
|
string tName = item.PropertyType.Name;
|
|
|
|
if (item.PropertyType.IsGenericType)
|
|
{
|
|
if (item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
tName = item.PropertyType.GetGenericArguments()[0].Name;
|
|
}
|
|
}
|
|
|
|
if (row[tName.ToLower()] != null)
|
|
{
|
|
switch (tName.ToLower())
|
|
{
|
|
case "boolean":
|
|
item.SetValue(temp, row[item.Name.ToLower()].ToString() == "1", null);
|
|
break;
|
|
case "byte":
|
|
case "short":
|
|
case "int32":
|
|
case "int64":
|
|
case "decimal":
|
|
item.SetValue(temp, row[item.Name.ToLower()], null);
|
|
break;
|
|
case "string":
|
|
if (row[item.Name.ToLower()] != null && row[item.Name.ToLower()] != DBNull.Value)
|
|
{
|
|
item.SetValue(temp, row[item.Name.ToLower()].ToString(), null);
|
|
}
|
|
break;
|
|
case "datetime":
|
|
if (row[item.Name.ToLower()] != null && row[item.Name.ToLower()] != DBNull.Value)
|
|
{
|
|
item.SetValue(temp, Convert.ToDateTime(row[item.Name.ToLower()]), null);
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
}
|
|
|
|
return temp;
|
|
}
|
|
else
|
|
{
|
|
return default(T);
|
|
}
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 批量插入数据
|
|
/// </summary>
|
|
public static bool Insert<T>(this List<T> modelList, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Type t = modelList.First().GetType();
|
|
PropertyInfo[] pInfo = t.GetProperties();
|
|
|
|
StringBuilder sql = new StringBuilder();
|
|
|
|
List<string> aColumn = GetAutoKeyColumn(modelList.First());
|
|
|
|
foreach (var model in modelList)
|
|
{
|
|
List<string> strSql1 = new List<string>();
|
|
List<string> strSql2 = new List<string>();
|
|
|
|
foreach (var item in pInfo.Where(c => !c.Name.Contains("_init") && !aColumn.Contains(c.Name)))
|
|
{
|
|
switch (item.PropertyType.Name.ToLower())
|
|
{
|
|
case "bool":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "byte":
|
|
case "short":
|
|
case "int32":
|
|
case "int64":
|
|
case "decimal":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(item.GetValue(model, null).ToString());
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "string":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("''");
|
|
}
|
|
break;
|
|
case "datetime":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
sql.AppendLine("insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");");
|
|
}
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 批量插入数据,并返回异常
|
|
/// </summary>
|
|
public static bool Insert<T>(this List<T> modelList, out string retEx, int i = 0)
|
|
{
|
|
|
|
retEx = string.Empty;
|
|
try
|
|
{
|
|
|
|
Type t = modelList.First().GetType();
|
|
|
|
PropertyInfo[] pInfo = t.GetProperties();
|
|
|
|
|
|
StringBuilder sql = new StringBuilder();
|
|
|
|
List<string> aColumn = GetAutoKeyColumn(modelList.First());
|
|
|
|
foreach (var model in modelList)
|
|
{
|
|
List<string> strSql1 = new List<string>();
|
|
List<string> strSql2 = new List<string>();
|
|
|
|
foreach (var item in pInfo.Where(c => !c.Name.Contains("_init") && !aColumn.Contains(c.Name)))
|
|
{
|
|
string type = "";
|
|
type = item.PropertyType.Name.ToLower();
|
|
if (item.PropertyType.IsGenericType && item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
type = item.PropertyType.GetGenericArguments()[0].Name;
|
|
}
|
|
switch (type.ToLower())
|
|
{
|
|
case "bool":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "byte":
|
|
case "short":
|
|
case "int32":
|
|
case "int64":
|
|
case "decimal":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(item.GetValue(model, null).ToString());
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "string":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("''");
|
|
}
|
|
break;
|
|
case "datetime":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
sql.AppendLine("insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");");
|
|
}
|
|
Init(i);
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retEx = ex.Message;
|
|
LogHelper.ErrorLog(ex);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
public static T ChangeType<T>(object value)
|
|
{
|
|
var t = typeof(T);
|
|
|
|
if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
|
|
{
|
|
if (value == null)
|
|
{
|
|
return default(T);
|
|
}
|
|
|
|
t = Nullable.GetUnderlyingType(t);
|
|
}
|
|
|
|
return (T)Convert.ChangeType(value, t);
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 插入单条数据,并返回异常
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="model">插入数据</param>
|
|
/// <param name="retEx">返回异常</param>
|
|
/// <returns></returns>
|
|
public static bool Insert<T>(this T model, out string retEx, int i = 0)
|
|
{
|
|
retEx = string.Empty;
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
string sql = string.Empty;
|
|
List<string> strSql1 = new List<string>();
|
|
List<string> strSql2 = new List<string>();
|
|
|
|
Type t = model.GetType();
|
|
PropertyInfo[] pInfo = t.GetProperties();
|
|
List<string> aColumn = GetAutoKeyColumn(model);
|
|
|
|
foreach (var item in pInfo.Where(c => !aColumn.Contains(c.Name)))
|
|
{
|
|
string tName = item.PropertyType.Name;
|
|
|
|
if (item.PropertyType.IsGenericType)
|
|
{
|
|
if (item.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
|
|
{
|
|
tName = item.PropertyType.GetGenericArguments()[0].Name;
|
|
}
|
|
}
|
|
|
|
switch (tName.ToLower())
|
|
{
|
|
case "boolean":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "byte":
|
|
case "short":
|
|
case "int32":
|
|
case "int64":
|
|
case "decimal":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(item.GetValue(model, null).ToString());
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "string":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("''");
|
|
}
|
|
break;
|
|
case "datetime":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
sql = "insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");";
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retEx = ex.Message;
|
|
LogHelper.ErrorLog(ex);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 批量插入数据, 删除唯一条件外旧数据，给表必须有唯一 i_id
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="modelList">插入集合</param>
|
|
/// <param name="delecel">删除列指定唯一,</param>
|
|
/// <param name="retEx">返回异常</param>
|
|
/// <returns></returns>
|
|
public static bool Insert<T>(this List<T> modelList, string delecel, out string retEx)
|
|
{
|
|
retEx = string.Empty;
|
|
try
|
|
{
|
|
Type t = modelList.First().GetType();
|
|
PropertyInfo[] pInfo = t.GetProperties();
|
|
|
|
StringBuilder sql = new StringBuilder();
|
|
|
|
List<string> aColumn = GetAutoKeyColumn(modelList.First());
|
|
|
|
foreach (var model in modelList)
|
|
{
|
|
List<string> strSql1 = new List<string>();
|
|
List<string> strSql2 = new List<string>();
|
|
|
|
foreach (var item in pInfo.Where(c => !c.Name.Contains("_init") && !aColumn.Contains(c.Name)))
|
|
{
|
|
switch (item.PropertyType.Name.ToLower())
|
|
{
|
|
case "bool":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(Convert.ToBoolean(item.GetValue(model, null)) ? "1" : "0");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "byte":
|
|
case "short":
|
|
case "int32":
|
|
case "int64":
|
|
case "decimal":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add(item.GetValue(model, null).ToString());
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("0");
|
|
}
|
|
break;
|
|
case "string":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + item.GetValue(model, null).ToString() + "'");
|
|
}
|
|
else
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("''");
|
|
}
|
|
break;
|
|
case "datetime":
|
|
if (item.GetValue(model, null) != null)
|
|
{
|
|
strSql1.Add(item.Name);
|
|
strSql2.Add("'" + Convert.ToDateTime(item.GetValue(model, null)).ToString("yyyy-MM-dd HH:mm:ss") + "'");
|
|
}
|
|
break;
|
|
}
|
|
}
|
|
|
|
sql.AppendLine("insert into " + t.Name.ToLower() + "(" + String.Join(",", strSql1) + ") values(" + String.Join(",", strSql2) + ");");
|
|
}
|
|
sql.Append("delete " + t.Name.ToLower() + " where i_id not in(select i_id from " + t.Name.ToLower() + " where i_id in(select max(i_id) from " + t.Name.ToLower() + " group by " + delecel + "));");
|
|
|
|
return dbo.ExecuteNonQuery(sql.ToString()) > 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retEx = ex.Message;
|
|
LogHelper.ErrorLog(ex);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改返回异常
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="model"> 实体类</param>
|
|
/// <param name="retUpEx">返回异常</param>
|
|
/// <param name="strSet">修改参数</param>
|
|
/// <param name="strWhere">修改条件</param>
|
|
/// <returns></returns>
|
|
public static bool Update<T>(this T model, out string retUpEx, string strSet, string strWhere = "", int i = 0)
|
|
{
|
|
retUpEx = string.Empty;
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
Type t = model.GetType();
|
|
|
|
string sql = "update " + t.Name.ToLower() + " set " + strSet;
|
|
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
{
|
|
sql += " where " + strWhere;
|
|
}
|
|
|
|
sql += ";";
|
|
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retUpEx = ex.Message;
|
|
LogHelper.ErrorLog(ex);
|
|
return false;
|
|
}
|
|
}
|
|
|
|
#region 附加
|
|
|
|
/// <summary>
|
|
/// 按条件查询对象实体
|
|
/// </summary>
|
|
public static DataTable GetTable(string tablename, string strWhere = "", string strOrderBy = "", int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
string sql = "select * from " + tablename;
|
|
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
{
|
|
sql += " where " + strWhere;
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(strOrderBy))
|
|
{
|
|
sql += " order by " + strOrderBy;
|
|
}
|
|
|
|
sql += ";";
|
|
|
|
return dbo.GetDataTable(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return new DataTable();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// sql查询
|
|
/// </summary>
|
|
public static DataTable GetTableSql(string sql, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.GetDataTable(sql);
|
|
}
|
|
else
|
|
{
|
|
return dbo2.GetDataTable(sql);
|
|
}
|
|
//return dbo.GetDataTable(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return new DataTable();
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 删除
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <param name="i"></param>
|
|
/// <returns></returns>
|
|
public static bool DelSql(string sql, out string retEx, int i = 0)
|
|
{
|
|
retEx = string.Empty;
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retEx = ex.Message;
|
|
LogHelper.ErrorLog(ex);
|
|
return false;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 修改
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <param name="i"></param>
|
|
/// <returns></returns>
|
|
public static bool UpdateSql(string sql, out string retEx, int i = 0)
|
|
{
|
|
retEx = string.Empty;
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
if (i == 0)
|
|
{
|
|
return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
else
|
|
{
|
|
return dbo2.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
//return dbo.ExecuteNonQuery(sql) > 0;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
retEx = ex.Message;
|
|
LogHelper.ErrorLog(ex);
|
|
return false;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// DataTable转成List
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="dt"></param>
|
|
/// <returns></returns>
|
|
public static List<T> ToDataList<T>(DataTable dt)
|
|
{
|
|
var list = new List<T>();
|
|
var plist = new List<PropertyInfo>(typeof(T).GetProperties());
|
|
foreach (DataRow item in dt.Rows)
|
|
{
|
|
T s = Activator.CreateInstance<T>();
|
|
for (int i = 0; i < dt.Columns.Count; i++)
|
|
{
|
|
PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
|
|
if (info != null)
|
|
{
|
|
try
|
|
{
|
|
if (!Convert.IsDBNull(item[i]))
|
|
{
|
|
object v = null;
|
|
if (info.PropertyType.ToString().Contains("System.Nullable"))
|
|
{
|
|
v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
|
|
}
|
|
else
|
|
{
|
|
v = Convert.ChangeType(item[i], info.PropertyType);
|
|
}
|
|
info.SetValue(s, v, null);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception("字段[" + info.Name + "]转换出错,value:" + item[i] + ex.ToString());
|
|
}
|
|
}
|
|
}
|
|
list.Add(s);
|
|
}
|
|
return list;
|
|
}
|
|
|
|
|
|
|
|
/// <summary>
|
|
/// 按条件查询主键
|
|
/// </summary>
|
|
public static int sqlzj(string sql, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
|
|
|
|
int ind = Convert.ToInt32(dbo.ExecuteScalar(sql));
|
|
return ind;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
return 0;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行update操作
|
|
/// </summary>
|
|
public static void sqlend(string sql, int i = 0)
|
|
{
|
|
try
|
|
{
|
|
Init(i);
|
|
|
|
|
|
|
|
dbo.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
|
|
}
|
|
}
|
|
}
|
|
}
|