You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
423 lines
18 KiB
C#
423 lines
18 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.SoftMng.Models.MsSysTable;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.Areas.CommMng.Models;
|
|
using HcUtility.Comm;
|
|
using System.Web;
|
|
using DSWeb.EntityDA;
|
|
|
|
namespace DSWeb.SoftMng.DAL.SoftSysTable
|
|
{
|
|
public class MsSoftSysTableDAL
|
|
{
|
|
#region Inquery DataList
|
|
|
|
static public List<sys_table> GetDataList(string strCondition, string sort = null)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT [GID],[TBLNAME],[TBLCNAME],[MODULENAME],REMARKS,INPUTBY,INPUTTIME");
|
|
strSql.Append(",(select ShowName from [user] where GID=s.INPUTBY) as INPUTBYREF");
|
|
strSql.Append(" from sys_table s ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else {
|
|
strSql.Append(" order by TBLNAME");
|
|
|
|
}
|
|
return SetData(strSql);
|
|
}
|
|
|
|
|
|
|
|
static public sys_table GetData(string condition)
|
|
{
|
|
sys_table data = null;
|
|
var list = GetDataList(condition);
|
|
if (list.Count > 0)
|
|
data = list[0];
|
|
|
|
if (data == null)
|
|
{
|
|
data = new sys_table();
|
|
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
|
|
private static List<sys_table> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<sys_table>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sys_table data = new sys_table();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.TBLNAME = Convert.ToString(reader["TBLNAME"]);
|
|
data.TBLCNAME = Convert.ToString(reader["TBLCNAME"]);
|
|
data.MODULENAME = Convert.ToString(reader["MODULENAME"]);
|
|
|
|
data.REMARKS = Convert.ToString(reader["REMARKS"]);
|
|
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
|
|
data.INPUTBYREF = Convert.ToString(reader["INPUTBYREF"]);
|
|
|
|
if (reader["INPUTTIME"] != DBNull.Value)
|
|
data.INPUTTIME = Convert.ToDateTime(reader["INPUTTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
public static DBResult DeleteSysTable(List<sys_table> headData)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
if (headData != null)
|
|
{
|
|
foreach (var enumValue in headData)
|
|
{
|
|
var cmdDelete2 = db.GetSqlStringCommand("delete from sys_table_detail where TBLGID='" + enumValue.GID + "'");
|
|
db.ExecuteNonQuery(cmdDelete2, tran);
|
|
var cmdDelete = db.GetSqlStringCommand("delete from sys_table where GID='" + enumValue.GID + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
}
|
|
}
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
#region 字段
|
|
|
|
static public List<sys_table_detail> GetTableDetailList(string strCondition, string sort = null)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT [GID],[TBLGID],[FIELDNAME],FIELDCNAME,FIELDTYPE,FIELDLEN,ISNULL,ISKEY,DEFVALUE,REMARKS,LNKTBL,LNKFIELDNAME,[INPUTBY],[INPUTTIME]");
|
|
strSql.Append(",(select ShowName from [user] where GID=sys_table_detail.INPUTBY) as INPUTBYREF,SORT");
|
|
|
|
strSql.Append(" from sys_table_detail ");
|
|
|
|
if (!string.IsNullOrEmpty(strCondition))
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var sortstring = DatasetSort.Getsortstring(sort);
|
|
if (!string.IsNullOrEmpty(sortstring))
|
|
{
|
|
strSql.Append(" order by " + sortstring);
|
|
}
|
|
else
|
|
{
|
|
strSql.Append(" order by SORT,INPUTTIME DESC");
|
|
|
|
}
|
|
return SetTableDetailData(strSql);
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
private static List<sys_table_detail> SetTableDetailData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<sys_table_detail>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sys_table_detail data = new sys_table_detail();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.TBLGID = Convert.ToString(reader["TBLGID"]);
|
|
data.FIELDNAME = Convert.ToString(reader["FIELDNAME"]);
|
|
data.FIELDCNAME = Convert.ToString(reader["FIELDCNAME"]);
|
|
data.FIELDTYPE = Convert.ToString(reader["FIELDTYPE"]);
|
|
data.FIELDLEN = Convert.ToString(reader["FIELDLEN"]);
|
|
data.ISNULL = Convert.ToString(reader["ISNULL"]);
|
|
data.ISKEY = Convert.ToString(reader["ISKEY"]);
|
|
data.DEFVALUE = Convert.ToString(reader["DEFVALUE"]);
|
|
data.LNKTBL = Convert.ToString(reader["LNKTBL"]);
|
|
data.LNKFIELDNAME = Convert.ToString(reader["LNKFIELDNAME"]);
|
|
data.REMARKS = Convert.ToString(reader["REMARKS"]);
|
|
data.INPUTBY = Convert.ToString(reader["INPUTBY"]);
|
|
data.INPUTBYREF = Convert.ToString(reader["INPUTBYREF"]);
|
|
if (reader["INPUTTIME"] != DBNull.Value)
|
|
data.INPUTTIME = Convert.ToDateTime(reader["INPUTTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
data.SORT = Convert.ToString(reader["SORT"]);
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
|
|
static public List<sys_table_detail> GetDefTableDetailList(string tablename)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN ");
|
|
strSql.Append("(SELECT name FROM sysindexes WHERE indid IN (SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid)))");
|
|
strSql.Append("THEN '1' ELSE '0' END AS ISKEY ");
|
|
strSql.Append(", a.name AS FIELDNAME, c.name AS FIELDTYPE, a.length AS 'byte', COLUMNPROPERTY(a.id, a.name, ");
|
|
strSql.Append("'PRECISION') AS FIELDLEN, a.xscale, a.isnullable ISNULL, ISNULL(e.text, '') AS DEFVALUE, ISNULL(p.value, '') AS REMARKS ");
|
|
strSql.Append("FROM sys.syscolumns AS a INNER JOIN sys.sysobjects AS b ON a.id = b.id INNER JOIN ");
|
|
strSql.Append("sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN ");
|
|
strSql.Append("sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id WHERE (b.name = '" + tablename + "') AND (c.status <> '1')");
|
|
|
|
|
|
return SetDefTableDetailData(strSql);
|
|
}
|
|
|
|
|
|
private static List<sys_table_detail> SetDefTableDetailData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<sys_table_detail>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
var i = 0;
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sys_table_detail data = new sys_table_detail();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["FIELDNAME"]);
|
|
data.TBLGID ="*";
|
|
data.FIELDNAME = Convert.ToString(reader["FIELDNAME"]);
|
|
data.FIELDCNAME = Convert.ToString(reader["REMARKS"]);
|
|
data.FIELDTYPE = Convert.ToString(reader["FIELDTYPE"]);
|
|
data.FIELDLEN = Convert.ToString(reader["FIELDLEN"]);
|
|
data.ISNULL = Convert.ToString(reader["ISNULL"]);
|
|
data.ISKEY = Convert.ToString(reader["ISKEY"]);
|
|
data.LNKTBL ="";
|
|
data.LNKFIELDNAME ="";
|
|
data.REMARKS = Convert.ToString(reader["REMARKS"]);
|
|
data.INPUTBY ="";
|
|
data.INPUTBYREF ="";
|
|
data.INPUTTIME ="";
|
|
data.SORT = i.ToString();
|
|
i = i + 1;
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
public static DBResult SaveTableDetail(List<sys_table_detail> bodyList, string PID, string userid)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
|
|
var cmdInsert =
|
|
db.GetSqlStringCommand(
|
|
@"insert into sys_table_detail (GID,TBLGID,FIELDNAME,FIELDCNAME,FIELDTYPE,FIELDLEN,ISKEY,ISNULL,REMARKS,LNKTBL,LNKFIELDNAME,INPUTBY,INPUTTIME,DEFVALUE,SORT)
|
|
values (@GID,@TBLGID,@FIELDNAME,@FIELDCNAME,@FIELDTYPE,@FIELDLEN,@ISKEY,@ISNULL,@REMARKS,@LNKTBL,@LNKFIELDNAME,@INPUTBY,@INPUTTIME,@DEFVALUE,@SORT) ");
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update sys_table_detail set FIELDNAME=@FIELDNAME,FIELDCNAME=@FIELDCNAME,FIELDTYPE=@FIELDTYPE,FIELDLEN=@FIELDLEN,ISKEY=@ISKEY,
|
|
ISNULL=@ISNULL,REMARKS=@REMARKS,LNKTBL=@LNKTBL,LNKFIELDNAME=@LNKFIELDNAME,DEFVALUE=@DEFVALUE,SORT=@SORT where GID=@GID ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
if (enumValue.SORT == "") enumValue.SORT = "0";
|
|
|
|
if (enumValue.TBLGID == "*" || enumValue.TBLGID == "")
|
|
{
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString());
|
|
db.AddInParameter(cmdInsert, "@TBLGID", DbType.String, PID);
|
|
db.AddInParameter(cmdInsert, "@FIELDNAME", DbType.String, enumValue.FIELDNAME);
|
|
db.AddInParameter(cmdInsert, "@FIELDCNAME", DbType.String, enumValue.FIELDCNAME);
|
|
db.AddInParameter(cmdInsert, "@FIELDTYPE", DbType.String, enumValue.FIELDTYPE);
|
|
db.AddInParameter(cmdInsert, "@FIELDLEN", DbType.String, enumValue.FIELDLEN);
|
|
db.AddInParameter(cmdInsert, "@ISKEY", DbType.String, enumValue.ISKEY);
|
|
db.AddInParameter(cmdInsert, "@ISNULL", DbType.String, enumValue.ISNULL);
|
|
db.AddInParameter(cmdInsert, "@REMARKS", DbType.String, enumValue.REMARKS);
|
|
db.AddInParameter(cmdInsert, "@LNKTBL", DbType.String, enumValue.LNKTBL);
|
|
db.AddInParameter(cmdInsert, "@LNKFIELDNAME", DbType.String, enumValue.LNKFIELDNAME);
|
|
db.AddInParameter(cmdInsert, "@DEFVALUE", DbType.String, enumValue.DEFVALUE);
|
|
db.AddInParameter(cmdInsert, "@INPUTBY", DbType.String, userid);
|
|
db.AddInParameter(cmdInsert, "@INPUTTIME", DbType.String, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
db.AddInParameter(cmdInsert, "@SORT", DbType.String, enumValue.SORT);
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
}
|
|
else
|
|
{
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID);
|
|
db.AddInParameter(cmdUpdate, "@FIELDNAME", DbType.String, enumValue.FIELDNAME);
|
|
db.AddInParameter(cmdUpdate, "@FIELDCNAME", DbType.String, enumValue.FIELDCNAME);
|
|
db.AddInParameter(cmdUpdate, "@FIELDTYPE", DbType.String, enumValue.FIELDTYPE);
|
|
db.AddInParameter(cmdUpdate, "@FIELDLEN", DbType.String, enumValue.FIELDLEN);
|
|
db.AddInParameter(cmdUpdate, "@ISKEY", DbType.String, enumValue.ISKEY);
|
|
db.AddInParameter(cmdUpdate, "@ISNULL", DbType.String, enumValue.ISNULL);
|
|
db.AddInParameter(cmdUpdate, "@REMARKS", DbType.String, enumValue.REMARKS);
|
|
db.AddInParameter(cmdUpdate, "@LNKTBL", DbType.String, enumValue.LNKTBL);
|
|
db.AddInParameter(cmdUpdate, "@LNKFIELDNAME", DbType.String, enumValue.LNKFIELDNAME);
|
|
db.AddInParameter(cmdUpdate, "@DEFVALUE", DbType.String, enumValue.DEFVALUE);
|
|
db.AddInParameter(cmdUpdate, "@INPUTBY", DbType.String, userid);
|
|
db.AddInParameter(cmdUpdate, "@INPUTTIME", DbType.String, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
db.AddInParameter(cmdUpdate, "@SORT", DbType.String, enumValue.SORT);
|
|
db.ExecuteNonQuery(cmdUpdate, tran);
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "保存成功" + result.Message;
|
|
|
|
return result;
|
|
}
|
|
|
|
public static DBResult DeleteTableDetail(List<sys_table_detail> headData)
|
|
{
|
|
var result = new DBResult();
|
|
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (var conn = db.CreateConnection())
|
|
{
|
|
conn.Open();
|
|
var tran = conn.BeginTransaction();
|
|
|
|
try
|
|
{
|
|
if (headData != null)
|
|
{
|
|
foreach (var enumValue in headData)
|
|
{
|
|
|
|
var cmdDelete = db.GetSqlStringCommand("delete from sys_table_detail where GID='" + enumValue.GID + "'");
|
|
db.ExecuteNonQuery(cmdDelete, tran);
|
|
}
|
|
}
|
|
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "删除出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "删除成功";
|
|
|
|
return result;
|
|
}
|
|
|
|
#endregion
|
|
|
|
|
|
static public int GetMaxSort(string strCondition)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT ISNULL(MAX(SORT),0) AS CT from sys_update_sql (NOLOCK) ");
|
|
if (strCondition.Trim() != String.Empty)
|
|
{
|
|
strSql.Append(" where " + strCondition);
|
|
}
|
|
var ct = 0;
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
ct = Convert.ToInt16(reader["CT"]);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return ct+1;
|
|
}
|
|
|
|
}
|
|
}
|