系列文章
C#底层库–程序日志记录类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/124187709
C#底层库–MySQLBuilder脚本构建类（select、insert、update、in、带条件的SQL自动生成）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129179216
C#底层库–MySQL数据库访问操作辅助类（推荐阅读）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126886379
C#底层库–XML配置参数读写辅助类（推荐阅读）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129175304
C#底层库–获取文件版本和MD5值
本文链接：https://blog.csdn.net/youcheng_ge/article/details/112513871
C#底层库–FilesHelper文件辅助类（删除目录文件、复制文件到指定目录）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126887161
C#底层库–Excel操作帮助库（可读加密Excel表格）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126887445
C#底层库–随机数生成器
本文链接：https://blog.csdn.net/youcheng_ge/article/details/126888812
C#底层库–RegexHelper正则表达式辅助类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/109745286
C#底层库–CSV和DataTable相互转换
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128804367
C#底层库–Image图片操作类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128805298
C#底层库–JSON使用教程_详细（序列化、反序列化、转DataTable）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128805705
C#底层库–cookie使用教程
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128816347
C#底层库–Session操作辅助类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128817096
C#底层库–Image图片操作类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128805298
C#底层库–数据库类型与程序类型转换器
本文链接：https://blog.csdn.net/youcheng_ge/article/details/128817610
C#底层库–StringExtension字符串扩展类
本文链接：https://blog.csdn.net/youcheng_ge/article/details/129520428
C#底层库–自定义进制转换器（可去除特殊字符，非Convert.ToString方式）
本文链接：https://blog.csdn.net/youcheng_ge/article/details/130444724
前言
本专栏为【底层库】，主要介绍编程过程中 通用函数。我们将这些通用固化的源码，进行重写、封装、拓展，再进行单元测试、集成测试、beta测试，最终形成通用化模板，这里我们称为“底层库”。
作为研发人员的你，并不需要花大量时间，研究“底层库”的含义，及“底层库”的实现方法。你只需要几行调用代码，就可以解决项目上碰到的难题。而底层库使用方法，本专栏均有详细介绍，也有项目应用场景。
底层库已实现功能：MySQL脚本构建器、MySQL数据库访问操作、参数配置文件读写、加解密算法、日志记录、HTTP通信、Socket通信、API前后端交互、邮件发送、文件操作、配置参数存储、Excel导入导出、CSV和DataTable转换、压缩解压、自动编号、Session操作等。
本专栏会持续更新，不断优化【底层库】，大家有任何问题，可以私信我。本专栏之间关联性较强（我会使用到某些底层库，某些文章可能忽略介绍），如果您对本专栏感兴趣，欢迎关注，我将带你用最简洁的代码，实现最复杂的功能。
一、底层库介绍
数据实体类。可以实现编程开发中，数据集定义、转换实体、字段读取等
二、底层库源码
2.1 创建类Entity.cs
创建类Entity.cs，复制以下代码。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace GYC_Utils.Common
{
/// <summary>
/// 数据实体类 表头使用
/// </summary>
public class Entity : Dictionary<string, object>
{
/// <summary>
/// 获取Int 型数据
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public int GetInt(string name)
{
return TypeConverter.ObjectToInt(GetString(name), 0);
}
public bool GetBoolean(string name, bool def)
{
return TypeConverter.ObjectToBool(GetString(name), def);
}
/// <summary>
/// 获取 int
/// </summary>
/// <param name="name"></param>
/// <param name="def"></param>
/// <returns></returns>
public int GetInt(string name, int def)
{
return TypeConverter.BitToInt(GetString(name), def);
}
/// <summary>
/// 覆盖式加入
/// </summary>
/// <param name="key"></param>
/// <param name="val"></param>
public void Put(string key, object val)
{
if (this.ContainsKey(key)) this[key] = val;
else this.Add(key, val);
}
/// <summary>
/// 转实体类
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public T ToModel<T>()
{
return Newtonsoft.Json.JsonConvert.DeserializeObject<T>(ToJson());
}
/// <summary>
/// 转json字符串
/// </summary>
/// <returns></returns>
public string ToJson()
{
return Newtonsoft.Json.JsonConvert.SerializeObject(this);
}
/// <summary>
/// 获取float
/// </summary>
/// <param name="name"></param>
/// <param name="def"></param>
/// <returns></returns>
public float GetFloat(string name, float def)
{
return TypeConverter.ObjectToFloat(GetString(name), def);
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public bool Exist(string name)
{
if (this.ContainsKey(name)) return true;
if (this.ContainsKey(name.ToLower())) return true;
if (this.ContainsKey(name.ToUpper())) return true;
return false;
}
/// <summary>
/// 获取String
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public string GetString(string name)
{
if (this.ContainsKey(name) && this[name] != null) return this[name].ToString();
if (this.ContainsKey(name.ToUpper()) && this[name.ToUpper()] != null) return this[name.ToUpper()].ToString();
if (this.ContainsKey(name.ToUpper()) && this[name.ToUpper()] != null) return this[name.ToUpper()].ToString();
return "";
}
public string GetString(string name, string def)
{
string str = GetString(name);
if (str == "") return def;
return str;
}
/// <summary>
/// 获取日期
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public DateTime GetDateTime(string name)
{
try
{
return DateTime.Parse(GetString(name));
}
catch (Exception ex)
{
LogHelper.error(ex);
return DateTime.Now;
}
}
/// <summary>
/// 获取可空的日期
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public DateTime? GetDateTimeNull(string name)
{
try
{
return DateTime.Parse(GetString(name));
}
catch (Exception ex)
{
LogHelper.error(ex);
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public object GetObject(string name)
{
if (this.ContainsKey(name) && this[name] != null) return this[name];
if (this.ContainsKey(name.ToUpper()) && this[name.ToUpper()] != null) return this[name.ToUpper()];
if (this.ContainsKey(name.ToUpper()) && this[name.ToUpper()] != null) return this[name.ToUpper()];
return "";
}
}
}
2.2 创建类 BodyEntity.cs
创建类 BodyEntity.cs，复制以下代码。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace GYC_Utils.Common
{
/// <summary>
/// 表体数据
/// key 表名
/// value 表体数据， 数组
/// </summary>
public class BodyEntity : Dictionary<string, List<Entity>>
{
/// <summary>
/// 转json格式数据
/// </summary>
/// <returns></returns>
public string ToJson()
{
return Newtonsoft.Json.JsonConvert.SerializeObject(this);
}
/// <summary>
/// 返回表体
/// </summary>
/// <param name="body"></param>
/// <returns></returns>
public List<Entity> GetBody(string body)
{
if (this.ContainsKey(body)) return this[body];
else return new List<Entity>();
}
}
}
2.3 创建类Repository.cs
创建类 Repository.cs，复制以下代码。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
namespace GYC_Utils.Common
{
/// <summary>
/// 数据库操作类
/// </summary>
public class Repository
{
protected static string[] exfield = new string[] { "f_id", "f_cu", "f_cd", "f_mu", "f_md", "f_du", "f_dd", "f_cku", "f_ckd" };
private static SqlHelper _sqlhelper;
/// <summary>
/// 获取基础的连接对象
/// </summary>
public static SqlHelper SqlHelper
{
get
{
if (SysConfig.Intance.rent)
{
if (_listsqlhelper == null)
{
_listsqlhelper = new Dictionary<string, SqlHelper>();
}
if (_listsqlhelper.ContainsKey(LoginProvider.rent))
{
return _listsqlhelper[LoginProvider.rent];
}
throw new Exception("租户信息不存在");
}
else
{
if (_sqlhelper == null)
{
if (TypeConverter.StrToInt(System.Configuration.ConfigurationManager.AppSettings["DESEncrypt"], 0) == 1)
_sqlhelper = new SqlHelper(DESEncrypt.Decrypt(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString));
else
_sqlhelper = new SqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
}
return _sqlhelper;
}
}
}
/// <summary>
/// 默认数据库链接 主要网站使用
/// </summary>
public static SqlHelper DefaultSqlHelper
{
get
{
if (_sqlhelper == null)
_sqlhelper = new SqlHelper(System.Configuration.ConfigurationManager.ConnectionStrings["default"].ConnectionString);
return _sqlhelper;
}
}
/// <summary>
/// 添加租户对象
/// </summary>
/// <param name="code"></param>
/// <param name="ip"></param>
/// <param name="port"></param>
/// <param name="db"></param>
/// <param name="username"></param>
/// <param name="userpass"></param>
public static void AddRent(string code, string ip, int port, string db, string username, string userpass)
{
if (_listsqlhelper == null)
{
_listsqlhelper = new Dictionary<string, SqlHelper>();
}
//Server=www.ufrontal.com,11422;uid=sa;pwd=yfd@20141118;Database=yn_menjin
_listsqlhelper.Add(code, new SqlHelper(string.Format("Server={0},{1};uid={2};pwd={3};Database={4}", ip, port, username, userpass, db)));
}
private static Dictionary<string, SqlHelper> _listsqlhelper;
/// <summary>
/// 快速拷贝到临时表。 并返回表名称
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static string FastCopyToTemp(DataTable dt)
{
string tableName = "temp" + LoginProvider.NickName + DateTime.Now.ToString("yyyyMMddHHmmss");// + Guid.NewGuid().ToString().Replace("-", "").Replace("}", "").Replace("{", "");
SqlConnection conn = Repository.SqlHelper.GetSqlConnection();
conn.Open();
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn);
CreateTable(dt.Columns, tableName, new string[] { "f_id" });
sqlBulkCopy.DestinationTableName = tableName;
foreach (DataColumn c in dt.Columns)
{
sqlBulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}
//SqlBulkCopy.BulkCopyTimeout = this.timeout; //超时时间
sqlBulkCopy.BatchSize = 3000; //每次传输3000行
sqlBulkCopy.WriteToServer(dt);
conn.Close();
return tableName;
}
/// <summary>
/// 导入数据 cno 编码字段， 唯一
/// </summary>
/// <param name="_config"></param>
/// <param name="filename"></param>
/// /// <param name="type">1 追加 2 更新追加</param>
/// <returns></returns>
//public static bool Import(PageConfig _config, string filename, string updateSupport)
//{
// bool isnoupsys = true;//true 使用当前信息更新系统字段
// StringBuilder createtable = new StringBuilder();
// StringBuilder copytable = new StringBuilder();
// StringBuilder insertsql = new StringBuilder();
// StringBuilder updatesql = new StringBuilder();
// //createtable.Append("select top 0 ");
// if (isnoupsys)
// insertsql.AppendFormat(" insert into {0} (f_cu,f_cd ,", _config.maininfo.GetString("cno"));
// else
// insertsql.AppendFormat(" insert into {0} ( ", _config.maininfo.GetString("cno"));
// if (isnoupsys)
// updatesql.AppendFormat(" update " + _config.maininfo.GetString("cno") + " a set a.f_mu={0},a.f_md=getdate(), ", LoginProvider.Userid);
// else
// updatesql.AppendFormat(" update " + _config.maininfo.GetString("cno") + " a set ", LoginProvider.Userid);
// string tableName = "temp" + LoginProvider.NickName + DateTime.Now.ToString("yyyyMMddHHmmss");
// SqlConnection conn = Repository.SqlHelper.GetSqlConnection();
// createtable.Append(" create table " + tableName + " ( ");
// copytable.Append(" select ");
// SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn);
// sqlBulkCopy.DestinationTableName = tableName;
// // bool hspri = false;
// bool idot = false;
// // bool udot = false;
// using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
// {
// HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
// Sheet sheet = hssfworkbook.GetSheetAt(0);
// DataTable dt = new DataTable();
// System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
// ///待导入字段
// Dictionary<int, DataRow> cols = new Dictionary<int, DataRow>();
// int irow = 1;
// int column = 0;
// while (rows.MoveNext())
// {
// Row row = (HSSFRow)rows.Current;
// if (irow == 1)
// {
// for (int i = 0; i < row.LastCellNum; i++)
// {
// Cell cell = row.GetCell(i);
// string col = (cell == null || cell.ToString().Trim() == "" ? ("COL" + i) : cell.ToString().Trim());
// DataRow[] drc = _config.fields.Select("cname='" + col + "' and iroot=1 ");
// if (drc.Length == 1)
// {
// if (drc[0]["cno"].ToString() == "f_id")
// {///按编号导入， 主键不考虑
// continue;
// }
// if (drc[0]["cno"].ToString() == "f_cd" || drc[0]["cno"].ToString() == "f_md" || drc[0]["cno"].ToString() == "f_dd" || drc[0]["cno"].ToString() == "f_ckd")
// {
// if (isnoupsys)
// continue;
// }
// if (drc[0]["cno"].ToString() == "f_cu" || drc[0]["cno"].ToString() == "f_mu" || drc[0]["cno"].ToString() == "f_du" || drc[0]["cno"].ToString() == "f_cku")
// {
// if (isnoupsys)
// continue;//系统字段不处理
// /// copytable.Append(" ( select f_id from sys_user c where c.cname=p." + drc[0]["cno"] + " and isnull(c.f_du,0)=0 and isnull(c.f_cku,0)>0 ) " + drc[0]["cno"].ToString());
// }
// string f = Newtonsoft.Json.JsonConvert.SerializeObject(drc);
// if (cols.Count > 0)
// {
// createtable.Append(",");
// copytable.Append(",");
// //
// }
// DataColumn dtc = new DataColumn();
// dtc.AllowDBNull = true;
// dtc.ColumnName = drc[0]["cno"].ToString();
// dtc.DataType = typeof(System.String);
// //if (drc[0]["dbtype"].ToString() == "int")
// //{
// // dtc.DataType = typeof(System.Int32);
// //}
// //else if (drc[0]["dbtype"].ToString() == "bit")
// //{
// // dtc.DataType = typeof(bool);
// //}
// //else if (drc[0]["dbtype"].ToString() == "decimal")
// //{
// // dtc.DataType = typeof(decimal);
// //}
// //else if (drc[0]["dbtype"].ToString() == "float")
// //{
// // dtc.DataType = typeof(float);
// //}
// //else if (drc[0]["dbtype"].ToString() == "datetime")
// //{
// // dtc.DataType = typeof(System.DateTime);
// //}
// //else
// //{
// // dtc.DataType = typeof(System.String);
// //}
// dtc.DataType = typeof(System.String);
// dt.Columns.Add(dtc);
// //if (drc[0]["cno"].ToString() == "f_id") createtable.Append(" 0 ");
// // createtable.Append(drc[0]["cno"].ToString()+" varchar(200)");
// createtable.Append(drc[0]["cno"].ToString() + " varchar(300)");
// int dstype = TypeConverter.ObjectToInt(drc[0]["dstype"], 0);
// int itype = TypeConverter.ObjectToInt(drc[0]["itype"], 0);
// //else if (dstype == 0)//
// //{
// //}
// if (itype == 25 || itype == 24 || itype == 11 || itype == 28 || itype == 30 || itype == 3 || itype == 4 || itype == 10 || itype == 27 || itype == 26 || itype == 29)//25 24 11 28 30 3 4 10 27 26 29
// {
// if (dstype == 2)//档案
// {
// ///附加导入关联脚本
// copytable.Append(" (select " + drc[0]["dataval"] + " from " + drc[0]["tablename"] + " c where c." + drc[0]["datatext"] + "=p." + drc[0]["cno"] + " and isnull(c.f_du,0)=0 and isnull(c.f_cku,0)>0 " + drc[0].GetString("imrelation") + " ) " + drc[0]["cno"].ToString());
// }
// else
// {///数据字典
// copytable.Append(" (select " + drc[0]["dataval"] + " from sys_dataitem c where c.dicid=" + drc[0]["datadic"] + " and c." + drc[0]["datatext"] + "=p." + drc[0]["cno"] + " and isnull(c.f_du,0)=0 and isnull(c.f_cku,0)>0 ) " + drc[0]["cno"].ToString());
// }
// }
// else
// {
// copytable.Append(drc[0]["cno"].ToString());
// }
// if (cols.Count > 0)
// insertsql.Append(",");
// insertsql.Append(drc[0]["cno"].ToString());
// if (cols.Count > 0)
// updatesql.Append(",");
// updatesql.AppendFormat(" a.{0}=b.{0}", drc[0]["cno"].ToString());
// cols.Add(i, drc[0]);
// //if (drc[0]["cno"].ToString() == "f_id")
// // hspri =true ;
// //else
// //{
// // ///更新插入脚本
// // if (idot) insertsql.Append(",");
// // insertsql.Append(cols[i]["cno"].ToString());
// // //更新更新脚本
// // if (udot) updatesql.Append(",");
// // updatesql.AppendFormat(" a.{0}=b.{0}", cols[i]["cno"].ToString());
// // idot = true;
// // udot = true;
// //}
// ///先分析结构
// sqlBulkCopy.ColumnMappings.Add(drc[0]["cno"].ToString(), drc[0]["cno"].ToString());
// }
// }
// createtable.Append(" ) ");
// copytable.Append(" into " + tableName + "_copy from " + tableName + " p ");
// column = dt.Columns.Count;
// irow++;
// continue;
// }
// DataRow dr = dt.NewRow();
// ///第二行
// for (int i = 0; i < row.LastCellNum && i < column; i++)
// {
// try
// {
// if (cols.ContainsKey(i))
// {
// Cell cell = row.GetCell(i);
// if (cell == null)
// {
// dr[cols[i]["cno"].ToString()] = null;
// }
// else
// {
// ///判断数据类型
// //if ((cols[i]["dbtype"].ToString() == "int"))
// //{
// // if (cell.ToString().Trim() != "")
// // dr[cols[i]["cno"].ToString()] = TypeConverter.StrToInt(cell.ToString().Trim(), 0);//&&
// //}
// //else if ((cols[i]["dbtype"].ToString() == "float"))
// //{
// // if (cell.ToString().Trim() != "")
// // dr[cols[i]["cno"].ToString()] = TypeConverter.StrToFloat(cell.ToString().Trim(), 0);//&& cell.ToString().Trim() == ""
// //}
// //else if ((cols[i]["dbtype"].ToString() == "datetime"))
// //{
// // if (cell.ToString().Trim() != "")
// // dr[cols[i]["cno"].ToString()] = TypeConverter.StrToDateTime(cell.ToString().Trim());//&& cell.ToString().Trim() == ""
// //}
// //else if ((cols[i]["dbtype"].ToString() == "decimal"))
// //{
// // if (cell.ToString().Trim() != "")
// // dr[cols[i]["cno"].ToString()] = TypeConverter.StrToDecimal(cell.ToString().Trim(), 0);//&& cell.ToString().Trim() == ""
// //}
// //else if ((cols[i]["dbtype"].ToString() == "bit"))
// //{
// // if (cell.ToString().Trim() != "")
// // dr[cols[i]["cno"].ToString()] = cell.ToString().Trim() == "0" ? false : true;//&& cell.ToString().Trim() == ""
// //}
// //else
// dr[cols[i]["cno"].ToString()] = cell.ToString().Trim();
// }
// }
// }
// catch (Exception e)
// {
// LogHelper.error(e.Message + e.InnerException + e.StackTrace);
// }
// }
// dt.Rows.Add(dr);
// irow++;
// }
// // createtable.AppendFormat(" into {1} from {0} ", _config.maininfo.GetString("cno"), tableName);
// if (isnoupsys)
// insertsql.AppendFormat(") select {0},getdate()", LoginProvider.Userid);
// else
// insertsql.AppendFormat(") select ", LoginProvider.Userid);
// idot = false;
// foreach (int k in cols.Keys)
// {
// if (cols[k]["cno"].ToString() == "f_id") continue;
// if (isnoupsys)/// 不更新系统的 不需要判断
// {
// insertsql.Append(",");
// }
// else ///系统字段按导入的来 需要判断逗号
// {
// if (idot) insertsql.Append(",");
// }
// insertsql.Append(cols[k]["cno"].ToString());
// idot = true;
// }
// insertsql.AppendFormat(" from {0} where 1=1 ", tableName + "_copy");
// //if (hspri)
// // {
// insertsql.AppendFormat(" and cno not in (select isnull({1},'') from {0} where isnull(f_du,0)=0 ) and isnull( {1},'')<>'' ", _config.maininfo.GetString("cno")
// , _config.nofiled);
// //只更新未审核的 left join
// updatesql.AppendFormat(" from {0} a , {1} b where a.{2}=b.{2} and b.{2} is not null and b.f_du is null ", _config.maininfo.GetString("cno"), tableName + "_copy", _config.nofiled);
// if (_config.menu.GetBoolean("bcheck", false))
// {
// updatesql.Append(" and isnull(a.f_cku,0)=0 ");
// }
// //}
// try
// {
// ///创建物理表
// Repository.ExecuteSql(createtable.ToString());
// // Repository.ExecuteSql(string.Format("alter table {0} alter column f_id int null ", tableName));
// // sqlBulkCopy.BatchSize = 3000; //每次传输3000行
// conn.Open();
// sqlBulkCopy.WriteToServer(dt);
// conn.Close();
// Repository.ExecuteSql(copytable.ToString());
// // Repository.ExecuteSql(@" alter table {0}_copy add f_cu int
// // alter table {0}_copy add f_cd datetime ", tableName);
// // Repository.ExecuteSql(@"update {0}_copy set f_cu={1},f_cd=getdate() ",tableName,LoginProvider.Userid);
// ///执行插入和更新操作
// if (updateSupport == "on")
// Repository.ExecuteSql(updatesql.ToString());
// Repository.ExecuteSql(insertsql.ToString());
// // if (hspri && type == 2)///如果右主键 才进行更新
// ///删除临时表
// Repository.ExecuteSql("drop table " + tableName);
// Repository.ExecuteSql("drop table " + tableName + "_copy");
// System.IO.File.Delete(filename);
// }
// catch (Exception ex)
// {
// Repository.ExecuteSql("drop table " + tableName);
// Repository.ExecuteSql("drop table " + tableName + "_copy");
// System.IO.File.Delete(filename);
// throw ex;
// }
// }
// return true;
//}
/// <summary>
/// 创建表以及触发器
/// </summary>
/// <param name="columns">列名</param>
/// <param name="tableName">表名</param>
/// <param name="primaryKeys">主键的列名</param>
public static bool CreateTable(System.Data.DataColumnCollection columns, string tableName, string[] primaryKeys)
{
if (primaryKeys == null || primaryKeys.Length < 1)
{
// MessageBox.Show("主键不允许为空!");
return false;
}
StringBuilder sb = new StringBuilder();
// sb.Append("create table [" + tableName + "] (autoId int identity(1,1),");
sb.Append("create table [" + tableName + "] (");
foreach (DataColumn column in columns)
{
sb.Append(" [" + column.ColumnName + "] " + GetTableColumnType(column.DataType) + ",");
}
string sql = sb.ToString();
sql = sql.TrimEnd(',');
sql += ")";
sb.Clear();
var temp1 = primaryKeys;
for (int i = 0; i < primaryKeys.Length; i++)
{
temp1[i] = tableName + "." + primaryKeys[i] + "=Inserted." + primaryKeys[i];
}
List<string> temp2 = new List<string>();
for (int i = 0; i < columns.Count; i++)
{
temp2.Add(columns[i].ColumnName + "=Inserted." + columns[i].ColumnName);
}
List<string> temp3 = new List<string>();
for (int i = 0; i < columns.Count; i++)
{
temp3.Add(columns[i].ColumnName);
}
sb.Append("CREATE TRIGGER [tri_" + tableName + "_edit] ON [" + tableName + "] instead of insert as");
sb.Append(" IF EXISTS (");
sb.Append("SELECT * FROM " + tableName + ",Inserted WHERE " + String.Join(" AND ", temp1) + ")");
sb.Append(" UPDATE [" + tableName + "] SET " + string.Join(",", temp2) + " FROM [" + tableName + "] JOIN inserted ON " + String.Join(" AND ", temp1) + " ");
sb.Append(" ELSE ");
sb.Append(" INSERT [" + tableName + "](" + string.Join(",", temp3) + ") SELECT " + string.Join(",", temp3) + " FROM inserted ");
// sql = sql + " ; " + sb.ToString();
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sql);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.Text, sb.ToString());
return true;
}
private static string GetTableColumnType(System.Type type)
{
string result = "varchar(255)";
string sDbType = type.ToString();
switch (sDbType)
{
case "System.String":
break;
case "System.Int16":
result = "int";
break;
case "System.Int32":
result = "int";
break;
case "System.Int64":
result = "float";
break;
case "System.Decimal":
result = "decimal(18,4)";
break;
case "System.Double":
result = "decimal(18,4)";
break;
case "System.DateTime":
result = "datetime";
break;
default:
break;
}
return result;
}
/// <summary>
/// 执行外部sql
/// </summary>
/// <param name="ip"></param>
/// <param name="port"></param>
/// <param name="db"></param>
/// <param name="username"></param>
/// <param name="userpass"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable Select(string ip, int port, string db, string username, string userpass, string sql)
{
SqlHelper s = new SqlHelper(string.Format("Server={0},{1};uid={3};pwd={4};Database={2}", ip, port, db, username, userpass));
try
{
DataTable dt = s.ExecuteTable(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
return dt;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
/// <summary>
/// 获取数据库操作类
/// </summary>
/// <param name="ip"></param>
/// <param name="port"></param>
/// <param name="db"></param>
/// <param name="username"></param>
/// <param name="userpass"></param>
/// <returns></returns>
public static SqlHelper GetSqlHelper(string ip, int port, string db, string username, string userpass)
{
return new SqlHelper(string.Format("Server={0},{1};uid={3};pwd={4};Database={2}", ip, port, db, username, userpass));
}
/// <summary>
/// 执行外部脚本
/// </summary>
/// <param name="ip"></param>
/// <param name="port"></param>
/// <param name="db"></param>
/// <param name="username"></param>
/// <param name="userpass"></param>
/// <param name="sql"></param>
/// <param name="par">key=value,key=value</param>
/// <returns></returns>
public static DataTable Select(string ip, int port, string db, string username, string userpass, string sql, string par)
{
SqlHelper s = new SqlHelper(string.Format("Server={0},{1};uid={2};pwd={4};Database={2}", ip, port, db, username, userpass));
List<SqlParameter> list = new List<SqlParameter>();
if (par != null)
{
string[] prs = par.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
foreach (string p in prs)
{
string[] a = p.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries);
list.Add(new SqlParameter("@" + a[0], a[1]));
}
}
try
{
DataTable dt = s.ExecuteTable(CommandType.Text, sql, list.ToArray());
LogHelper.SqlSuccess(sql);
return dt;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
public static int Execute(string ip, int port, string db, string username, string userpass, string sql)
{
SqlHelper s = new SqlHelper(string.Format("Server={0},{1};uid={2};pwd={4};Database={2}", ip, port, db, username, userpass));
try
{
int i = s.ExecuteNonQuery(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
return i;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
public static int Execute(string ip, int port, string db, string username, string userpass, string sql, string par)
{
SqlHelper s = new SqlHelper(string.Format("Server={0},{1};uid={2};pwd={4};Database={2}", ip, port, db, username, userpass));
List<SqlParameter> list = new List<SqlParameter>();
if (par != null)
{
string[] prs = par.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
foreach (string p in prs)
{
string[] a = p.Split(new string[] { "=" }, StringSplitOptions.RemoveEmptyEntries);
list.Add(new SqlParameter("@" + a[0], a[1]));
}
}
try
{
int i = s.ExecuteNonQuery(CommandType.Text, sql, list.ToArray());
LogHelper.SqlSuccess(sql + Newtonsoft.Json.JsonConvert.SerializeObject(list));
return i;
}
catch (Exception ex)
{
LogHelper.error(ex, sql + Newtonsoft.Json.JsonConvert.SerializeObject(list));
throw ex;
}
}
/// <summary>
/// 获取单个实例
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static Entity GetEntity(string sql)
{
try
{
DataTable model = SqlHelper.ExecuteTable(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
if (model.Rows.Count > 0) return RowToEntity(model.Rows[0]);
return null;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
/// <summary>
/// 查询单个对象
/// </summary>
/// <param name="str"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static Entity GetEntity(string str, params object[] obj)
{
try
{
DataTable model = SqlHelper.ExecuteTable(CommandType.Text, string.Format(str, obj), null);
LogHelper.SqlSuccess(string.Format(str, obj));
if (model.Rows.Count > 0) return RowToEntity(model.Rows[0]);
return null;
}
catch (Exception ex)
{
LogHelper.error(ex, string.Format(str, obj));
throw ex;
}
}
/// <summary>
/// 获取实例列表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static List<Entity> GetListEntity(string sql)
{
try
{
DataTable model = SqlHelper.ExecuteTable(CommandType.Text, sql, null);
List<Entity> list = new List<Entity>();
foreach (DataRow dr in model.Rows)
{
list.Add(RowToEntity(dr));
}
LogHelper.SqlSuccess(sql);
return list;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
/// <summary>
/// 列表
/// </summary>
/// <param name="sql"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static List<Entity> GetListEntity(string sql, params object[] obj)
{
try
{
DataTable model = SqlHelper.ExecuteTable(CommandType.Text, string.Format(sql, obj), null);
if (model == null) return new List<Entity>();
//if (model.Rows.Count > 0) return RowToEntity(model.Rows[0]);
List<Entity> list = new List<Entity>();
foreach (DataRow dr in model.Rows)
{
list.Add(RowToEntity(dr));
}
LogHelper.SqlSuccess(string.Format(sql, obj));
return list;
}
catch (Exception ex)
{
LogHelper.error(ex, string.Format(sql, obj));
throw ex;
}
}
/// <summary>
/// DataRow 转实例
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
public static Entity RowToEntity(DataRow dr)
{
Entity entity = new Entity();
foreach (DataColumn col in dr.Table.Columns)
{
entity.Add(col.ColumnName, dr[col.ColumnName]);
}
return entity;
}
/// <summary>
/// 查询sql脚本
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable SelectBySql(string sql)
{
try
{
DataTable dt = SqlHelper.ExecuteTable(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
return dt;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
/// <summary>
/// 查询DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet SelectBySqlDataSet(string sql)
{
try
{
DataSet ds = SqlHelper.ExecuteDataSet(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
return ds;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
/// <summary>
/// 查询DataSet
/// </summary>
/// <param name="sql"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static DataSet SelectBySqlDataSet(string sql, params object[] obj)
{
try
{
DataSet ds = SqlHelper.ExecuteDataSet(CommandType.Text, string.Format(sql, obj), null);
LogHelper.SqlSuccess(string.Format(sql, obj));
return ds;
}
catch (Exception ex)
{
LogHelper.error(ex, string.Format(sql, obj));
throw ex;
}
}
/// <summary>
/// 查询单个数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static Object GetSingle(string sql)
{
try
{
object val = SqlHelper.GetSingle(sql);
LogHelper.SqlSuccess(sql);
return val;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
public static Object GetSingle(string sql, params object[] obj)
{
try
{
object val = SqlHelper.GetSingle(string.Format(sql, obj));
if (obj != null)
LogHelper.SqlSuccess(sql + obj.ToJson());
else
LogHelper.SqlSuccess(sql);
return val;
}
catch (Exception ex)
{
LogHelper.error(ex, string.Format(sql, obj));
throw ex;
}
}
public static int GetSingleInt(string sql)
{
try
{
int r = TypeConverter.ObjectToInt(SqlHelper.GetSingle(sql));
LogHelper.SqlSuccess(sql);
return r;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
/// <summary>
/// 返回单个Int
/// </summary>
/// <param name="str"></param>
/// <param name="obj"></param>
/// <returns></returns>
public static int GetSingleInt(string str, params object[] obj)
{
try
{
int r = TypeConverter.ObjectToInt(SqlHelper.GetSingle(string.Format(str, obj)), 0);
if (obj != null)
LogHelper.SqlSuccess(str + obj.ToJson());
else
LogHelper.SqlSuccess(str);
return r;
}
catch (Exception ex)
{
LogHelper.error(ex, string.Format(str, obj));
throw ex;
}
}
/// <summary>
/// 获取流水号
/// </summary>
/// <param name="table">表名</param>
/// <param name="prev">前缀</param>
/// <param name="length">流水号长度</param>
/// <returns></returns>
public static string GetSerialNumber(string table, string prev, int length)
{
return Repository.GetSingle(string.Format("select '{1}'+ right('000000'+ convert(varchar,max(right(cno,{2}))+1),{2}) from {0} where cno like '{1}%'", table, prev, length)).ToString();
}
/// <summary>
/// 查询单个数据
/// </summary>
/// <param name="sql"></param>
/// <param name="par"></param>
/// <returns></returns>
public static Object GetSingle(string sql, params SqlParameter[] par)
{
try
{
return SqlHelper.GetSingle(sql, par);
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
return null;
}
}
/// <summary>
/// 查询存储过程
/// </summary>
/// <param name="proc"></param>
/// <param name="names"></param>
/// <param name="values"></param>
/// <returns></returns>
public static DataTable SelectByProc(string proc, string[] names, object[] values)
{
List<SqlParameter> list = new List<SqlParameter>();
for (int i = 0; i < names.Length; i++)
{
list.Add(new SqlParameter("@" + names[i], values[i]));
}
try
{
return SqlHelper.ExecuteTable(CommandType.StoredProcedure, proc, list.ToArray());
LogHelper.SqlSuccess(proc + Newtonsoft.Json.JsonConvert.SerializeObject(list));
}
catch (Exception ex)
{
LogHelper.error(ex, proc + Newtonsoft.Json.JsonConvert.SerializeObject(list));
return null;
}
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="type"></param>
/// <param name="text"></param>
/// <param name="p"></param>
/// <returns></returns>
public static DataTable ExecuteTable(CommandType type, string text, SqlParameter[] p)
{
try
{
DataTable dt = SqlHelper.ExecuteTable(type, text, p);
if (p != null)
LogHelper.SqlSuccess(text + p.ToJson());
else
LogHelper.SqlSuccess(text);
return dt;
}
catch (Exception ex)
{
LogHelper.error(ex, text + Newtonsoft.Json.JsonConvert.SerializeObject(p));
throw ex;
}
}
public static DataTable GetTableIntoLike(string where)
{
DataTable dt = SqlHelper.ExecuteTable(CommandType.Text,// "select * from U_ColumnInfo where tablename='" + table + "'"
string.Format(@"
SELECT
TableName=O.name,--CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=isnull(CONVERT(varchar,PTB.[value]),''),--CONVERT(varchar, ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N'')),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'1'ELSE N'0' END,
Computed=CASE WHEN C.is_computed=1 THEN N'1'ELSE N'0' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'1'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc= convert(varchar,ISNULL(PFD.[value],N'')),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) bitsCN.Com网管联盟
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'1'ELSE N'0' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
WHERE {0} --O.name=N'{0}' -- 如果只查询指定表,加上此条件
order by C.column_id asc
--ORDER BY O.name,C.column_id", where)
, null);
return dt;
}
/// <summary>
/// 获取物理表结构
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static DataTable GetTableStructure(string table)
{
if (CacheHelper.FileExists("Structure" + table) && !SysConfig.Intance.tablecache)
{
return CacheHelper.FileCacheDataTable("Structure" + table);
}
// DataTable dt = SqlHelper.ExecuteTable(CommandType.Text, string.Format(@"
//select c.name ColumnName,ty.name [Type] from syscolumns c left join sysobjects t on c.id=t.id left join systypes ty on c.xusertype=ty.xusertype
//where t.name='{0}' ", table), null);
DataTable dt = SqlHelper.ExecuteTable(CommandType.Text,// "select * from U_ColumnInfo where tablename='" + table + "'"
string.Format(@"
SELECT
TableName=O.name,--CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
TableDesc=isnull(CONVERT(varchar,PTB.[value]),''),--CONVERT(varchar, ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N'')),
Column_id=C.column_id,
ColumnName=C.name,
PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
[IDENTITY]=CASE WHEN C.is_identity=1 THEN N'1'ELSE N'0' END,
Computed=CASE WHEN C.is_computed=1 THEN N'1'ELSE N'0' END,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'1'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc= convert(varchar,ISNULL(PFD.[value],N'')),
IndexName=ISNULL(IDX.IndexName,N''),
IndexSort=ISNULL(IDX.Sort,N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
-- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
-- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) bitsCN.Com网管联盟
LEFT JOIN -- 索引及主键信息
(
SELECT
IDXC.[object_id],
IDXC.column_id,
Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'1'ELSE N'0' END,
IndexName=IDX.Name
FROM sys.indexes IDX
INNER JOIN sys.index_columns IDXC
ON IDX.[object_id]=IDXC.[object_id]
AND IDX.index_id=IDXC.index_id
LEFT JOIN sys.key_constraints KC
ON IDX.[object_id]=KC.[parent_object_id]
AND IDX.index_id=KC.unique_index_id
INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
(
SELECT [object_id], Column_id, index_id=MIN(index_id)
FROM sys.index_columns
GROUP BY [object_id], Column_id
) IDXCUQ
ON IDXC.[object_id]=IDXCUQ.[object_id]
AND IDXC.Column_id=IDXCUQ.Column_id
AND IDXC.index_id=IDXCUQ.index_id
) IDX
ON C.[object_id]=IDX.[object_id]
AND C.column_id=IDX.column_id
WHERE O.name=N'{0}' -- 如果只查询指定表,加上此条件
order by C.column_id asc
--ORDER BY O.name,C.column_id", table)
, null);
CacheHelper.FileCacheDataTable("Structure" + table, dt
);
return dt;
}
public static int Insert(string tablename)
{
Dictionary<string, object> values = DNTRequest.GetString();
return Insert(tablename, values);
}
/// <summary>
/// 按照表名插入
/// </summary>
/// <param name="tablename"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int Insert(string tablename, Dictionary<string, object> values)
{
StringBuilder str = new StringBuilder();
StringBuilder val = new StringBuilder();
List<SqlParameter> par = new List<SqlParameter>();
// StringBuilder strpar = new StringBuilder();
DataTable dt = GetTableStructure(tablename);
if (values.ContainsKey("f_id"))
{
values.Remove("f_id");
}
//if (!values.ContainsKey("f_id"))
//{
// values.Add("f_id", NewGUID);
//}
//else values["f_id"] = NewGUID;
if (!values.ContainsKey("f_cu"))
{
values.Add("f_cu", LoginProvider.UserInfo.userid);
}
if (!values.ContainsKey("f_cd"))
{
values.Add("f_cd", DateTime.Now);
}
//if (!values.ContainsKey("ienable"))
//{
// values.Add("ienable", true);
//}
//if (!values.ContainsKey("icheck"))
//{
// values.Add("icheck", true);
//}
str.Append(" insert into " + tablename + " (");
val.Append(" values (");
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["IDENTITY"].ToString() == "0")
if (values.ContainsKey(dt.Rows[i]["ColumnName"].ToString()))
{
if (values[dt.Rows[i]["ColumnName"].ToString()] == null) continue;
if (dt.Rows[i]["Type"].ToString() == "datetime" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "")
{
continue;
}
if (dt.Rows[i]["Type"].ToString() == "int" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
if (dt.Rows[i]["Type"].ToString() == "float" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
if (dt.Rows[i]["Type"].ToString() == "decimal" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
if (dt.Rows[i]["Type"].ToString() == "bit" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
if (dt.Rows[i]["Type"].ToString() == "bigint" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
if (dt.Rows[i]["Type"].ToString() == "datetime" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "")
{
continue;
}
if (val.ToString().IndexOf("@") > 0)
{
str.Append(","); val.Append(",");
}
str.Append(dt.Rows[i]["ColumnName"].ToString());
val.Append("@" + dt.Rows[i]["ColumnName"].ToString());
if (dt.Rows[i]["Type"].ToString() == "varchar")
if (TypeConverter.ObjectToInt(dt.Rows[i]["Length"]) < values[dt.Rows[i]["ColumnName"].ToString()].ToString().Length)
{
LogHelper.LogError(dt.Rows[i]["ColumnName"].ToString() + "字段值" + values[dt.Rows[i]["ColumnName"].ToString()].ToString() + "太长");
}
par.Add(new SqlParameter("@" + dt.Rows[i]["ColumnName"].ToString(), values[dt.Rows[i]["ColumnName"].ToString()]));
// strpar.AppendFormat("@" + dt.Rows[i]["ColumnName"].ToString() + "='" + values[dt.Rows[i]["ColumnName"].ToString()] + "'");
}
}
str.Append(")");
val.Append(")");
val.AppendLine(" select @@IDENTITY ");
// LogHelper.LogSql(str.ToString() + val.ToString() + strpar.ToString());
return TypeConverter.ObjectToInt(SqlHelper.GetSingle(str.ToString() + val.ToString(), par.ToArray()));
//return SqlHelper.ExecuteNonQuery(CommandType.Text, str.ToString() + val.ToString(), par.ToArray());
}
public static int Update(string tablename)
{
Dictionary<string, object> values = DNTRequest.GetString();
return Update(tablename, values);
}
/// <summary>
/// 按照表名更新数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="values"></param>
/// <returns></returns>
public static int Update(string tablename, Dictionary<string, object> values)
{
StringBuilder str = new StringBuilder();
StringBuilder where = new StringBuilder();
List<SqlParameter> par = new List<SqlParameter>();
DataTable dt = GetTableStructure(tablename);
str.Append(" update " + tablename + " set ");
where.Append(" where 1=1 ");
//if (!values.ContainsKey("f_id"))
//{
// values.Add("f_id", NewGUID);
//}
if (!values.ContainsKey("f_mu"))
{
values.Add("f_mu", LoginProvider.UserInfo.userid);
}
if (!values.ContainsKey("f_md"))
{
values.Add("f_md", DateTime.Now);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
if (values.ContainsKey(dt.Rows[i]["ColumnName"].ToString()))
{
if (str.ToString().IndexOf("=") > 0)
{
str.Append(",");
}
if (values[dt.Rows[i]["ColumnName"].ToString()] == null || values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "")
{
str.Append(dt.Rows[i]["ColumnName"].ToString());
str.Append("=null");
continue;
}
//if (dt.Rows[i]["Type"].ToString() == "int" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
//if (dt.Rows[i]["Type"].ToString() == "float" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
//if (dt.Rows[i]["Type"].ToString() == "decimal" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
//if (dt.Rows[i]["Type"].ToString() == "bit" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
//if (dt.Rows[i]["Type"].ToString() == "bigint" && values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "") continue;
if (dt.Rows[i]["PrimaryKey"].ToString() == "1")
{
where.Append(" and ");
where.Append(dt.Rows[i]["ColumnName"].ToString());
where.Append("=@" + dt.Rows[i]["ColumnName"].ToString());
///条件
par.Add(new SqlParameter("@" + dt.Rows[i]["ColumnName"].ToString(), values[dt.Rows[i]["ColumnName"].ToString()]));
}
else if (dt.Rows[i]["Type"].ToString() == "datetime")
{///日期
//continue;
str.Append(dt.Rows[i]["ColumnName"].ToString());
if (values[dt.Rows[i]["ColumnName"].ToString()].ToString() == "")
{
str.Append("=null");
}
else
{
str.Append("=@" + dt.Rows[i]["ColumnName"].ToString());
par.Add(new SqlParameter("@" + dt.Rows[i]["ColumnName"].ToString(), values[dt.Rows[i]["ColumnName"].ToString()]));
}
}
else
{///正常的
str.Append(dt.Rows[i]["ColumnName"].ToString());
str.Append("=@" + dt.Rows[i]["ColumnName"].ToString());
if (dt.Rows[i]["Type"].ToString() == "varchar")
if (TypeConverter.ObjectToInt(dt.Rows[i]["Length"]) < values[dt.Rows[i]["ColumnName"].ToString()].ToString().Length)
{
LogHelper.LogError(dt.Rows[i]["ColumnName"].ToString() + "字段值" + values[dt.Rows[i]["ColumnName"].ToString()].ToString() + "太长");
}
///条件
par.Add(new SqlParameter("@" + dt.Rows[i]["ColumnName"].ToString(), values[dt.Rows[i]["ColumnName"].ToString()]));
}
}
}
return SqlHelper.ExecuteNonQuery(CommandType.Text, str.ToString() + where.ToString(), par.ToArray());
}
public static int Update(string table, string where, string[] columns, object[] values)
{
StringBuilder str = new StringBuilder();
List<SqlParameter> par = new List<SqlParameter>();
str.AppendFormat("update {0} set ", table);
for (int i = 0; i < columns.Length && i < values.Length; i++)
{
if (i > 0) str.AppendLine(",");
str.AppendFormat("{0}=@{0}", columns[i]);
par.Add(new SqlParameter("@" + columns[i], values[i]));
}
str.AppendLine(" where " + where);
return SqlHelper.ExecuteNonQuery(CommandType.Text, str.ToString(), par.ToArray());
}
/// <summary>
/// 保存单个数据。 考虑效率问题， 一定确定字段名称是否正确
/// </summary>
/// <param name="table"></param>
/// <param name="data"></param>
/// <returns></returns>
public static int Save(string table, Entity data)
{
foreach (string k in exfield)
{
data.Remove(k);
}
List<string> col = new List<string>();
List<string> par = new List<string>();
List<SqlParameter> vals = new List<SqlParameter>();
if (!data.ContainsKey("f_cu"))
data.Add("f_cu", LoginProvider.Userid);
if (!data.ContainsKey("f_cd"))
data.Add("f_cd", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = Repository.GetTableStructure(table);
foreach (DataRow dr in dt.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && data.ContainsKey(dr["ColumnName"].ToString()))
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && (data[dr["ColumnName"].ToString()] == null || data[dr["ColumnName"].ToString()].ToString() == ""))
{
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), data.GetString(dr["ColumnName"].ToString())));
}
}
//foreach (string key in data.Keys)
//{
// col.Add(key);
// par.Add("@" + key);
// vals.Add(new SqlParameter("@" + key, data.GetString(key)));
//}
object pk = Repository.ExecuteScalar(CommandType.Text, string.Format("insert into {0} ({1}) values({2}) SELECT @@IDENTITY AS ID", table,
String.Join(",", col.ToArray()), String.Join(",", par.ToArray())
), vals.ToArray());
return TypeConverter.ObjectToInt(pk, 0);
}
/// <summary>
/// 保存单据数据。 考虑效率问题， 一定确定字段名称是否正确
/// </summary>
/// <param name="table"></param>
/// <param name="data"></param>
/// <param name="fkey">表外键: tablename:fkey,tablename:fkey</param>
/// <returns></returns>
public static int Save(string table, Entity header, BodyEntity bodys, string fkey)
{
foreach (string k in exfield)
{
header.Remove(k);
}
List<string> col = new List<string>();
List<string> par = new List<string>();
List<SqlParameter> vals = new List<SqlParameter>();
if (!header.ContainsKey("f_cu"))
header.Add("f_cu", LoginProvider.Userid);
if (!header.ContainsKey("f_cd"))
header.Add("f_cd", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = Repository.GetTableStructure(table);
foreach (DataRow dr in dt.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && header.ContainsKey(dr["ColumnName"].ToString()))
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && (header[dr["ColumnName"].ToString()] == null || header[dr["ColumnName"].ToString()].ToString() == ""))
{
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), header.GetString(dr["ColumnName"].ToString())));
}
}
//foreach (string key in data.Keys)
//{
// col.Add(key);
// par.Add("@" + key);
// vals.Add(new SqlParameter("@" + key, data.GetString(key)));
//}
object pk = Repository.ExecuteScalar(CommandType.Text, string.Format("insert into {0} ({1}) values({2}) SELECT @@IDENTITY AS ID", table,
String.Join(",", col.ToArray()), String.Join(",", par.ToArray())
), vals.ToArray());
Dictionary<string, string> fkeyd = new Dictionary<string, string>();
foreach (string kv in fkey.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
{
string[] kvs = kv.Split(new string[] { ":", "=" }, StringSplitOptions.RemoveEmptyEntries);
fkeyd.Add(kvs[0], kvs[1]);
}
foreach (string ckey in bodys.Keys)
{//ckey 表名称
DataTable dtb = Repository.GetTableStructure(ckey);
foreach (Entity data in bodys[ckey])
{//c 行数据
col.Clear();
par.Clear();
vals.Clear();
foreach (string k in exfield)
{
data.Remove(k);
}
data.Add(fkeyd[ckey], pk);
data.Add("f_cu", LoginProvider.Userid);
data.Add("f_cd", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
foreach (DataRow dr in dtb.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && data.ContainsKey(dr["ColumnName"].ToString()))
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && (data[dr["ColumnName"].ToString()] == null || data[dr["ColumnName"].ToString()].ToString() == ""))
{
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), data[dr["ColumnName"].ToString()]));
}
}
//foreach (string key in c.Keys)
//{
// col.Add(key);
// par.Add("@" + key);
// vals.Add(new SqlParameter("@" + key, data.GetString(key)));
//}
Repository.ExecuteScalar(CommandType.Text, string.Format("insert into {0} ({1}) values({2}) SELECT @@IDENTITY AS ID", ckey,
String.Join(",", col.ToArray()), String.Join(",", par.ToArray())
), vals.ToArray());
}
}
return TypeConverter.ObjectToInt(pk, 0);
}
/// <summary>
/// 修改单个数据。 考虑效率问题， 一定确定字段名称是否正确
/// </summary>
/// <param name="table"></param>
/// <param name="data"></param>
/// <returns></returns>
public static int Update(string table, Entity data, string where)
{
try
{
foreach (string k in exfield)
{
if (k == "f_id") continue;
data.Remove(k);
}
data.Add("f_mu", LoginProvider.Userid);
data.Add("f_md", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
List<string> col = new List<string>();
List<string> par = new List<string>();
List<SqlParameter> vals = new List<SqlParameter>();
DataTable dt = Repository.GetTableStructure(table);
//foreach (string key in data.Keys)//
foreach (DataRow dr in dt.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && data.ContainsKey(dr["ColumnName"].ToString()))
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && (data[dr["ColumnName"].ToString()] == null || data[dr["ColumnName"].ToString()].ToString() == ""))
{
col.Add(dr["ColumnName"].ToString() + "=" + "@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString() + "=" + "@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), data.GetString(dr["ColumnName"].ToString())));
}
}
//if (where == null || where == "") where = " 1=1 ";
StringBuilder str = new StringBuilder();
str.Append(" where 1=1 ");
if (where != null && where != "")
{
str.Append(" and " + where);
}
if (data.ContainsKey("f_id"))
{
str.AppendFormat(" and f_id={0}", data.GetInt("f_id", 0));
}
//vals.Add(new SqlParameter("@f_id", data.GetInt("f_id")));
int rows = Repository.ExecuteNonQuery(CommandType.Text, string.Format("update {0} set {1} " + str.ToString(), table,
String.Join(",", col.ToArray())//, String.Join(",", par.ToArray())
), vals.ToArray());
//Repository.ExecuteSql(str.ToString());
return rows;// TypeConverter.ObjectToInt(pk, -1);
}
catch (Exception ex)
{
throw ex;
}
//return TypeConverter.ObjectToInt(data, 0);
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="header">需要含 f_id </param>
/// <param name="body"></param>
/// <param name="tablefkey">table:fkey,table:fkey</param>
/// <returns></returns>
public int Update(string table, Entity header, BodyEntity body, string tablefkey)
{
try
{
foreach (string k in exfield)
{
if (k == "f_id") continue;
header.Remove(k);
}
header.Add("f_mu", LoginProvider.Userid);
header.Add("f_md", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
DataTable dt = Repository.GetTableStructure(table);
List<string> col = new List<string>();
List<string> par = new List<string>();
List<SqlParameter> vals = new List<SqlParameter>();
//str.AppendFormat("insert into {0}" ,maininfo.GetString("cno"));
foreach (DataRow dr in dt.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && header.ContainsKey(dr["ColumnName"].ToString()))
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && (header[dr["ColumnName"].ToString()] == null || header[dr["ColumnName"].ToString()].ToString() == ""))
{
col.Add(dr["ColumnName"].ToString() + "=" + "@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString() + "=" + "@" + dr["ColumnName"].ToString());
//par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), header.GetString(dr["ColumnName"].ToString())));
}
}
vals.Add(new SqlParameter("@f_id", header.GetInt("f_id")));
int rows = Repository.ExecuteNonQuery(CommandType.Text, string.Format("update {0} set {1} where f_id=@f_id", table,
String.Join(",", col.ToArray())//, String.Join(",", par.ToArray())
), vals.ToArray());
Dictionary<string, string> fkeyd = new Dictionary<string, string>();
foreach (string kv in tablefkey.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
{
string[] kvs = kv.Split(new string[] { ";", "=" }, StringSplitOptions.RemoveEmptyEntries);
fkeyd.Add(kvs[0], kvs[1]);
}
foreach (string c in fkeyd.Keys)
{
if (!body.ContainsKey(c)) continue;
DataTable dtb = Repository.GetTableStructure(c);
Repository.ExecuteSql(string.Format(" update {0} set f_du={1},f_dd=getdate() where {2}={3} and f_du is null ", c, 1, fkeyd[c], header.GetInt("f_id")));
foreach (Entity data in body[c])
{
foreach (string k in exfield)
{
if (k == "f_id") continue;
data.Remove(k);
}
data.Add("f_mu", LoginProvider.Userid);
data.Add("f_md", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
col.Clear();
par.Clear();
vals.Clear();
if (data.GetInt("f_id") == 0)
{
col.Add(fkeyd[c]);
par.Add("@" + fkeyd[c]);
vals.Add(new SqlParameter("@" + fkeyd[c], header.GetInt("f_id")));
foreach (DataRow dr in dtb.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && data.ContainsKey(dr["ColumnName"].ToString()))
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && (data[dr["ColumnName"].ToString()] == null || data[dr["ColumnName"].ToString()].ToString() == ""))
{
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString());
par.Add("@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), data[dr["ColumnName"].ToString()]));
}
}
Repository.ExecuteScalar(CommandType.Text, string.Format("insert into {0} ({1}) values({2}) SELECT @@IDENTITY AS ID", c,
String.Join(",", col.ToArray()), String.Join(",", par.ToArray())
), vals.ToArray());
}///新增
else
{///修改
foreach (DataRow dr in dtb.Rows)
{
if (dr["ColumnName"].ToString() != "f_id" && data.ContainsKey(dr["ColumnName"].ToString()) && dr["ColumnName"].ToString() != fkeyd[c])
{
if ((dr["Type"].ToString() == "int" || dr["Type"].ToString() == "datetime" || dr["Type"].ToString() == "float" || dr["Type"].ToString() == "decimal") && data[dr["ColumnName"].ToString()].ToString() == "")
{
col.Add(dr["ColumnName"].ToString() + "=" + "@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), DBNull.Value));
continue;
}
col.Add(dr["ColumnName"].ToString() + "=" + "@" + dr["ColumnName"].ToString());
vals.Add(new SqlParameter("@" + dr["ColumnName"].ToString(), data[dr["ColumnName"].ToString()]));
}
}
col.Add("f_du=null,f_dd=null," + fkeyd[c] + "=" + header.GetInt("f_id"));///清空删除标记
Repository.ExecuteScalar(CommandType.Text, string.Format("update {0} set {1} where f_id={2}", c,
String.Join(",", col.ToArray())//, String.Join(",", par.ToArray())
, data.GetInt("f_id")
), vals.ToArray());
}
}
}
//Repository.ExecuteSql(str.ToString());
return rows;// TypeConverter.ObjectToInt(pk, -1);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行脚本 或存储过程
/// </summary>
/// <param name="type"></param>
/// <param name="text"></param>
/// <param name="p"></param>
/// <returns></returns>
public static int ExecuteNonQuery(CommandType type, string text, SqlParameter[] p)
{
StringBuilder par = new StringBuilder();
if (p != null)
foreach (SqlParameter pa in p)
{
par.Append(pa.ParameterName + ":" + pa.Value);
}
try
{
int r = SqlHelper.ExecuteNonQuery(type, text, p);
LogHelper.SqlSuccess(text + "参数:" + par.ToString());
return r;
}
catch (Exception ex)
{
LogHelper.error(ex, text + par.ToString());
throw ex;
}
}
/// <summary>
/// 查询单个实例
/// </summary>
/// <param name="type"></param>
/// <param name="sql"></param>
/// <param name="p"></param>
/// <returns></returns>
public static object ExecuteScalar(CommandType type, string sql, SqlParameter[] p)
{
StringBuilder par = new StringBuilder();
if (p != null)
foreach (SqlParameter pa in p)
{
par.Append(pa.ParameterName + ":" + pa.Value);
}
try
{
object val = SqlHelper.ExecuteScalar(type, sql, p);
LogHelper.SqlSuccess(sql + "参数:" + par.ToString());
return val;
}
catch (Exception ex)
{
LogHelper.error(ex, sql + par.ToString());
throw ex;
}
}
/// <summary>
/// 执行sql 脚本
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int ExecuteSql(string sql)
{
try
{
int r = SqlHelper.ExecuteSql(sql);
LogHelper.SqlSuccess(sql);
return r;
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
throw ex;
}
}
public static int ExecuteSql(string sql, params object[] obj)
{
try
{
int k = SqlHelper.ExecuteSql(string.Format(sql, obj));
LogHelper.SqlSuccess(string.Format(sql, obj));
return k;
}
catch (Exception ex)
{
LogHelper.error(ex, string.Format(sql, obj));
throw ex;
}
}
/// <summary>
/// 获取对象列表
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static List<TEntity> GetEntityList<TEntity>(string sql)
{
List<TEntity> list = new List<TEntity>();
try
{
Type type = typeof(TEntity);
DataTable dt = SqlHelper.ExecuteTable(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
TEntity entity = Activator.CreateInstance<TEntity>();// default(TEntity); // new TEntity();
//foreach (PropertyInfo pi in type.GetProperties())
foreach (DataColumn col in dt.Columns)
{
object v = dt.Rows[i][col.ColumnName];
PropertyInfo pi = type.GetProperty(col.ColumnName);
//if (dt.Columns.Contains(pi.Name))
if (pi != null)
{
CommonHelper.SetValue(entity, pi, v);
}
else
{
//(entity as BaseDynModel).Values.Add(col.ColumnName, v);
}
}
list.Add(entity);
}
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
}
return list;
}
/// <summary>
/// 获取实例列表
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static TEntity GetEntity<TEntity>(string sql)
{
try
{
Type type = typeof(TEntity);
DataTable dt = SqlHelper.ExecuteTable(CommandType.Text, sql, null);
LogHelper.SqlSuccess(sql);
//for (int i = 0; i < dt.Rows.Count; i++)
if (dt.Rows.Count > 0)
{
TEntity entity = Activator.CreateInstance<TEntity>();// default(TEntity); // new TEntity();
//foreach (PropertyInfo pi in type.GetProperties())
foreach (DataColumn col in dt.Columns)
{
object v = dt.Rows[0][col.ColumnName];
PropertyInfo pi = type.GetProperty(col.ColumnName);
//if (dt.Columns.Contains(pi.Name))
if (pi != null)
{
CommonHelper.SetValue(entity, pi, v);
}
else
{
//(entity as BaseDynModel).Values.Add(col.ColumnName, v);
}
}
return entity;
// list.Add(entity);
}
}
catch (Exception ex)
{
LogHelper.error(ex, sql);
}
return default(TEntity);
// return list;
}
}
}
三、调用方法
List<Entity> users = GetString("users").JonsToList<Entity>();//.ToList();
StringBuilder str = new StringBuilder();
foreach (Entity u in users)
{
Entity fnode = new Entity();
fnode.Add("flowid", 0);
fnode.Add("workid", key);
fnode.Add("menuid", menuid);
fnode.Add("userid", u.GetInt("f_id", 0));//发起者
str.Append(u.GetString("cname") + " ");
fnode.Add("nodeid", nodeid);
fnode.Add("nodepk", startnode.GetInt("f_id", 0));
fnode.Add("istatus", 0);
fnode.Add("prevhid", GetInt("fhsid", 0));
fnode.Add("flowsid", GetInt("flowsid", 0));
fnode.Add("itype", startnode.GetInt("nodetype", 0));
fnode.Add("msg", "转办");
Repository.Save("sys_workflowhistorynode", fnode);
}
四、项目样例
可用于代码模板生成器的开发
3307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
