/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sqlConnection">数据库连接字符</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static DataTable GetDataTable(string sqlConnection, string sql)
{
if (string.IsNullOrEmpty(sqlConnection))
{
sqlConnection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
}
using (SqlConnection connection = new SqlConnection(sqlConnection))
{
DataTable dt = new DataTable();
try
{
connection.Open();
SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connection);
dataAdapter.Fill(dt);
}
catch (SqlException ex)
{
NHibernateHelper.WriteErrorLog("SQLHelper获取DataTable异常", ex);
}
finally
{
connection.Close();
connection.Dispose();
}
return(dt);
}
}
/// <summary>
/// 增删改
/// </summary>
/// <param name="sqlConnection">数据库连接字符</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static int ExecuteSql(string sqlConnection, string sql)
{
if (string.IsNullOrEmpty(sqlConnection))
{
sqlConnection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
}
using (SqlConnection connection = new SqlConnection(sqlConnection))
{
int rows = 0;
try
{
SqlCommand cmd = new SqlCommand(sql, connection);
connection.Open();
rows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("SQLHelper增删改异常", ex);
}
finally
{
connection.Close();
}
return(rows);
}
}
/// <summary>
/// 粘贴列字段说明信息
/// </summary>
/// <param name="copyTablesCode">复制表编码</param>
/// <param name="pasteTablesCode">粘贴表编码</param>
/// <returns></returns>
public static int PasteColumnBewrite(string copyTablesCode, string pasteTablesCode)
{
//根据复制表编码，获取表的列名以及说明信息
IList <Column> list = GetColumn(new Guid(copyTablesCode));
if (list.Count == 0)
{
return(0);
}
string sql = "begin transaction ";
foreach (Column column in list)
{
if (!string.IsNullOrEmpty(column.Bewrite))
{
sql += string.Format("update Z_Column set Bewrite='{0}' where Name='{1}' and TablesCode='{2}';", column.Bewrite, column.Name, pasteTablesCode);
}
}
sql += @"if @@error<>0 begin rollback transaction end else begin commit transaction end";
try
{
return(SQLHelper.ExecuteSql(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString(), sql));
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("粘贴列字段说明信息", ex);
throw;
}
}
/// <summary>
/// 判断列名是否重复
/// </summary>
/// <param name="ColumnName">列名</param>
/// <param name="TablesCode">表编码</param>
/// <param name="Author">作者</param>
/// <returns>列名是否重复</returns>
public static bool RepeatColumnName(string ColumnName, Guid TablesCode, string Author)
{
try
{ //获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Column>();
if (!String.IsNullOrEmpty(ColumnName))
{
criteria.Add(Expression.Eq("Name", ColumnName));
}
if (!TablesCode.Equals(Guid.Empty))
{
criteria.Add(Expression.Eq("TablesCode", TablesCode));
}
if (!String.IsNullOrEmpty(Author))
{
criteria.Add(Expression.Eq("Author", Author));
}
int count = criteria.SetCacheable(true).List <Column>().Count;
//提交事务
transaction.Commit();
return(count > 0 ? true : false);
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("判断列名是否重复", ex);
throw;
}
}
/// <summary>
/// 根据数据库编码，获取表名称
/// </summary>
/// <param name="databaseCode">数据库编码</param>
/// <returns></returns>
public static string GetTablesName(Guid databaseCode)
{
try
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Tables>();
criteria.Add(Expression.Eq("DatabaseCode", databaseCode));
IList <Tables> list = criteria.AddOrder(Order.Asc("Id")).List <Tables>();
//提交事务
transaction.Commit();
if (list.Count > 0)
{
string TablesName = string.Empty;
foreach (Tables tbs in list)
{
TablesName += "'" + tbs.Name + "',";
}
return(TablesName.Substring(0, TablesName.Length - 1));
}
else
{
return("");
}
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("根据数据库编码，获取表名称", ex);
throw;
}
}
/// <summary>
/// 根据数据库编码，获取表(分组后内部排序)
/// </summary>
/// <param name="databaseCode">数据库编码</param>
/// <returns></returns>
public static DataTable GetTables2(Guid databaseCode)
{
try
{
string sql = string.Format(@"SELECT *,RANK() OVER(PARTITION BY GroupCode order by Name) as GroupId FROM Z_Tables as [Tables] WHERE DatabaseCode = '{0}'", databaseCode);
return(SQLHelper.GetDataTable("", sql));
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("根据数据库编码，获取表", ex);
throw;
}
}
/// <summary>
/// 分页获取功能分组信息
/// </summary>
/// <param name="start">起始</param>
/// <param name="pageSize">结束</param>
/// <param name="query">条件</param>
/// <param name="projectCode">项目编码</param>
/// <param name="databaseCode">数据库编码</param>
/// <returns></returns>
public static string GetPageGroup(int start, int pageSize, string query, string projectCode, string databaseCode)
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
using (ITransaction transaction = session.BeginTransaction())
{
IList <Group> list = null; //分页的记录
int count = 0; //总的记录条数
try
{
ICriteria criteria = session.CreateCriteria <Group>();
if (projectCode == "")
{
count = 0;
list = null;
}
else
{
criteria.Add(Expression.Eq("ProjectCode", new Guid(projectCode)));
if (!string.IsNullOrEmpty(databaseCode))
{
criteria.Add(Expression.Eq("DatabaseCode", new Guid(databaseCode)));
}
if (!String.IsNullOrEmpty(query))
{
criteria.Add(Expression.Like("Name", "%" + query + "%"));
}
count = criteria.SetCacheable(true).List <Group>().Count;
list = criteria.SetCacheable(true).SetFirstResult(start).SetMaxResults(pageSize).AddOrder(Order.Asc("Id")).List <Group>();
}
//提交事务
transaction.Commit();
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("分页获取功能分组信息", ex);
throw;
}
finally
{
session.Close();
}
Hashtable hasTable = new Hashtable();
hasTable.Add("total", count);
hasTable.Add("rows", list);
return(JsonHelper.ToJson(hasTable));
}
}
}
/// <summary>
/// 读取模板,生成静态HTML
/// </summary>
/// <param name="TempElement">模板标签</param>
/// <param name="HtmlElement">Html元素</param>
/// <param name="ReadFile">读取文件路径</param>
/// <param name="OutFile">输出文件路径</param>
/// <returns></returns>
public static bool WriteHtml(string[] TempElement, string[] HtmlElement, string ReadFile, string OutFile)
{
bool result = false;//生成结果
Encoding code = Encoding.GetEncoding("utf-8");
StreamReader sr = null;
StreamWriter sw = null;
string str = "";
try
{
//读取模板内容
sr = new StreamReader(ReadFile, code);
str = sr.ReadToEnd();
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("读取模板内容", ex);
if (sr != null)
{
sr.Close();
}
}
//替换内容
for (int i = 0; i < TempElement.Length; i++)
{
str = str.Replace(TempElement[i], HtmlElement[i]);
}
try
{
sw = new StreamWriter(OutFile, false, code);
sw.Write(str);
sw.Flush();
result = true;
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("读取模板,生成静态HTML", ex);
}
finally
{
if (sw != null)
{
sw.Close();
}
}
return(result);
}
/// <summary>
/// 批量保存列字段说明
/// </summary>
/// <param name="Columnlist">列字段信息</param>
/// <returns></returns>
public static int SaveColumnRemark(List <Column> Columnlist)
{
string sql = "begin transaction ";
foreach (Column column in Columnlist)
{
sql += string.Format("update Z_Column set Bewrite='{0}' where Code='{1}';", column.Bewrite, column.Code);
}
sql += @"if @@error<>0 begin rollback transaction end else begin commit transaction end";
try
{
return(SQLHelper.ExecuteSql(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString(), sql));
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("批量保存列字段说明", ex);
throw;
}
}
/// <summary>
/// 通过编号获得单个对象
/// </summary>
/// <param name="code">编码</param>
/// <returns>T</returns>
public static T GetProjectById(Guid code)
{
using (ISession session = NHibernateHelper.GetCurrentSession())
{
ITransaction transaction = session.BeginTransaction();
try
{
T entity = session.Load <T>(code);
transaction.Commit();
return(entity);
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("NHibernate通过编号获得单个对象", ex);
transaction.Rollback();
return(default(T));
}
}
}
/// <summary>
/// 更新
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns>T</returns>
public static T Update(T entity)
{
using (ISession session = NHibernateHelper.GetCurrentSession())
{
ITransaction transaction = session.BeginTransaction();
try
{
session.Update(entity);
transaction.Commit();
return(entity);
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("NHibernate执行更新操作", ex);
transaction.Rollback();
return(default(T));
}
}
}
/// <summary>
/// 添加
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns>T</returns>
public static T Add(T entity)
{
using (ISession session = NHibernateHelper.GetCurrentSession())
{
ITransaction transaction = session.BeginTransaction();
try
{
session.Save(entity);
transaction.Commit();
return(entity);
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("NHibernate执行添加操作", ex);
transaction.Rollback();
throw null;
}
}
}
/// <summary>
/// 分页获取列字段信息
/// </summary>
/// <param name="start">起始</param>
/// <param name="pageSize">结束</param>
/// <param name="query">条件</param>
/// <param name="tablesCode">数据库编码</param>
/// <returns></returns>
public static string GetPageColumn(int start, int pageSize, string query, string tablesCode)
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{ //事务开始
using (ITransaction transaction = session.BeginTransaction())
{
IList <Column> list = null; //分页的记录
int count = 0; //总的记录条数
try
{
ICriteria criteria = session.CreateCriteria <Column>();
if (tablesCode == "")
{
count = 0;
list = null;
}
else
{
criteria.Add(Expression.Eq("TablesCode", new Guid(tablesCode)));
criteria.Add(Expression.Eq("FieldState", 1));//状态为启用
if (!String.IsNullOrEmpty(query))
{
criteria.Add(Expression.Or(Expression.Like("Name", "%" + query + "%"), Expression.Like("Alias", "%" + query + "%")));
}
count = criteria.SetCacheable(true).List <Column>().Count;
list = criteria.SetCacheable(true).SetFirstResult(start).SetMaxResults(pageSize).AddOrder(Order.Asc("Id")).List <Column>();
}
transaction.Commit();//提交事务
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("分页获取列字段信息", ex);
transaction.Rollback();//回滚事务
}
Hashtable hasTable = new Hashtable();
hasTable.Add("total", count);
hasTable.Add("rows", list);
return(JsonHelper.ToJson(hasTable));
}
}
}
/// <summary>
/// 分页获取项目信息
/// </summary>
/// <param name="start">起始</param>
/// <param name="pageSize">结束</param>
/// <param name="query">条件</param>
/// <param name="userName">用户名</param>
/// <returns></returns>
public static string GetPageProject(int start, int pageSize, string query, string userName)
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
using (ITransaction transaction = session.BeginTransaction())
{
IList <Project> list = null; //分页的记录
int count = 0; //总的记录条数
try
{
ICriteria criteria = session.CreateCriteria <Project>();
criteria.Add(Expression.Eq("Author", userName));
if (!String.IsNullOrEmpty(query))
{
criteria.Add(Expression.Or(Expression.Like("Name", "%" + query + "%"), Expression.Like("Bewrite", "%" + query + "%")));
}
count = criteria.SetCacheable(true).List <Project>().Count;
list = criteria.SetCacheable(true).SetFirstResult(start).SetMaxResults(pageSize).AddOrder(Order.Asc("Id")).List <Project>();
//提交事务
transaction.Commit();
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("分页获取项目信息", ex);
throw;
}
finally
{
session.Close();
}
Hashtable hasTable = new Hashtable();
hasTable.Add("total", count);
hasTable.Add("rows", list);
return(JsonHelper.ToJson(hasTable));
}
}
}
/// <summary>
/// 判断表名是否重复
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="DatabaseCode">数据库编码</param>
/// <param name="GroupCode">功能分组编码</param>
/// <param name="Author">作者</param>
/// <returns>表名是否重复</returns>
public static bool RepeatTablesName(string TableName, string DatabaseCode, string GroupCode, string Author)
{
try
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Tables>();
if (!String.IsNullOrEmpty(TableName))
{
criteria.Add(Expression.Eq("Name", TableName));
}
if (!String.IsNullOrEmpty(DatabaseCode))
{
criteria.Add(Expression.Eq("DatabaseCode", new Guid(DatabaseCode)));
}
if (!String.IsNullOrEmpty(GroupCode))
{
criteria.Add(Expression.Eq("GroupCode", new Guid(GroupCode)));
}
if (!String.IsNullOrEmpty(Author))
{
criteria.Add(Expression.Eq("Author", Author));
}
int count = criteria.List <Tables>().Count;
//提交事务
transaction.Commit();
return(count > 0 ? true : false);
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("判断表名是否重复", ex);
throw;
}
}
/// <summary>
/// 根据项目编码，获取数据库
/// </summary>
/// <param name="projectCode">项目编码</param>
/// <returns></returns>
public static IList <Database> GetDatabase(Guid projectCode)
{
try
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Database>();
criteria.Add(Expression.Eq("ProjectCode", projectCode));
IList <Database> list = criteria.SetCacheable(true).AddOrder(Order.Asc("Id")).List <Database>();
//提交事务
transaction.Commit();
return(list);
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("根据项目编码，获取数据库", ex);
throw;
}
}
/// <summary>
/// 根据分组编码，获取分组名称
/// </summary>
/// <param name="Code">分组编码</param>
/// <returns></returns>
public static string GetGroupName(Guid?Code)
{
try
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Group>();
criteria.Add(Expression.Eq("Code", Code));
IList <Group> list = criteria.List <Group>();
//提交事务
transaction.Commit();
return(list[0].Name);
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("分页获取功能分组信息", ex);
throw;
}
}
/// <summary>
/// 根据表编码，获取列字段
/// </summary>
/// <param name="tablesCode">表编码</param>
/// <returns></returns>
public static IList <Column> GetColumn(Guid tablesCode)
{
try
{ //获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Column>();
criteria.Add(Expression.Eq("TablesCode", tablesCode));
criteria.Add(Expression.Eq("FieldState", 1));//状态为启用
IList <Column> list = criteria.SetCacheable(true).AddOrder(Order.Asc("Id")).List <Column>();
//提交事务
transaction.Commit();
return(list);
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("根据表编码，获取列字段", ex);
throw;
}
}
/// <summary>
/// 测试连接
/// </summary>
/// <param name="sqlConnection">数据库连接字符</param>
/// <returns></returns>
public static bool TestConnection(string sqlConnection)
{
bool result = false;
using (SqlConnection connection = new SqlConnection(sqlConnection))
{
try
{
connection.Open();
result = true;
}
catch (SqlException ex)
{
NHibernateHelper.WriteErrorLog("测试连接异常", ex);
}
finally
{
connection.Close();
connection.Dispose();
}
return(result);
}
}
/// <summary>
/// 根据数据库编码，获取表
/// </summary>
/// <param name="databaseCode">数据库编码</param>
/// <returns></returns>
public static IList <Tables> GetTables(Guid databaseCode)
{
try
{
//获得当前运行的NHibernate实例
using (ISession session = NHibernateHelper.GetCurrentSession())
{
//事务开始
ITransaction transaction = session.BeginTransaction();
ICriteria criteria = session.CreateCriteria <Tables>();
criteria.Add(Expression.Eq("DatabaseCode", databaseCode));
IList <Tables> list = criteria.AddOrder(Order.Asc("Id")).List <Tables>();
//提交事务
transaction.Commit();
return(list);
}
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("根据数据库编码，获取表", ex);
throw;
}
}
/// <summary>
/// 根据编码删除表并返回这个实体对象.
/// </summary>
/// <param name="code">编码</param>
/// <returns>T</returns>
public static T Delete(Guid code)
{
using (ISession session = NHibernateHelper.GetCurrentSession())
{
ITransaction transaction = session.BeginTransaction();
try
{
T entity = GetProjectById(code);
if (entity != null)
{
session.Delete(entity);
}
session.Flush();
transaction.Commit();
return(entity);
}
catch (Exception ex)
{
NHibernateHelper.WriteErrorLog("NHibernate执行删除操作", ex);
transaction.Rollback();
return(default(T));
}
}
}