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.
459 lines
16 KiB
C#
459 lines
16 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using DSWeb.SoftMng.Models.MsSoftUpGrade;
|
|
using Microsoft.Practices.EnterpriseLibrary.Data;
|
|
using DSWeb.Areas.CommMng.Models;
|
|
using HcUtility.Comm;
|
|
using System.Web;
|
|
using DSWeb.EntityDA;
|
|
|
|
namespace DSWeb.SoftMng.DAL.SoftUpGrade
|
|
{
|
|
public class MsSoftUpGradeDAL
|
|
{
|
|
#region Inquery DataList
|
|
|
|
static public List<sys_update_sql> GetDataList(string strCondition, string sort = null)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT [GID],[SQLVER],[SORT],[SQLTEXT],UPSTATUS,[REMARKS],INPUTBY,INPUTTIME,AUDITBY,AUDITTIME");
|
|
strSql.Append(",(select ShowName from [user] where GID=s.INPUTBY) as INPUTBYREF");
|
|
strSql.Append(",(select ShowName from [user] where GID=s.AUDITBY) as AUDITBYREF,'' UPFILES");
|
|
strSql.Append(" from sys_update_sql 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 SQLVER DESC,SORT desc");
|
|
|
|
}
|
|
return SetData(strSql);
|
|
}
|
|
|
|
|
|
|
|
static public sys_update_sql GetData(string condition)
|
|
{
|
|
sys_update_sql data = null;
|
|
var list = GetDataList(condition);
|
|
if (list.Count > 0)
|
|
data = list[0];
|
|
|
|
if (data == null)
|
|
{
|
|
data = new sys_update_sql();
|
|
|
|
}
|
|
|
|
return data;
|
|
}
|
|
|
|
|
|
private static List<sys_update_sql> SetData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<sys_update_sql>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sys_update_sql data = new sys_update_sql();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.SQLVER = Convert.ToString(reader["SQLVER"]);
|
|
data.SORT = Convert.ToString(reader["SORT"]);
|
|
data.SQLTEXT = Convert.ToString(reader["SQLTEXT"]);
|
|
|
|
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.UPSTATUS = Convert.ToString(reader["UPSTATUS"]);
|
|
data.AUDITBY = Convert.ToString(reader["AUDITBY"]);
|
|
data.AUDITBYREF = Convert.ToString(reader["AUDITBYREF"]);
|
|
|
|
if (reader["AUDITTIME"] != DBNull.Value)
|
|
data.AUDITTIME = Convert.ToDateTime(reader["AUDITTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
|
|
|
|
data.UPFILES = Convert.ToString(reader["UPFILES"]);
|
|
|
|
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
reader.Close();
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
public static DBResult Deletesys_update_sql(List<sys_update_sql> 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_update_sql_table where VERGID='" + enumValue.GID + "'");
|
|
db.ExecuteNonQuery(cmdDelete2, tran);
|
|
var cmdDelete = db.GetSqlStringCommand("delete from sys_update_sql 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;
|
|
}
|
|
|
|
|
|
public static List<sys_update_sql> GetUpDateData(string connectionstring,string sqlver,string sort)
|
|
{
|
|
var headList = new List<sys_update_sql>();
|
|
T_ALL_DA T_ALL_DA = new EntityDA.T_ALL_DA();
|
|
|
|
if (sort == "") sort = "100";
|
|
|
|
DataSet ds = T_ALL_DA.GetAllSQL("SELECT [GID],[SQLVER],[SORT],[SQLTEXT],UPSTATUS,[REMARKS] from sys_update_sql WHERE SQLVER>'" + sqlver+ "' OR (SQLVER='"+sqlver+"' AND SORT>"+sort+") ORDER BY SQLVER,SORT");
|
|
if (ds != null)
|
|
{
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
foreach (DataTable myTable in ds.Tables)
|
|
{
|
|
foreach (DataRow myRow in myTable.Rows)//遍历表
|
|
{
|
|
sys_update_sql data = new sys_update_sql();
|
|
#region Set DB data to Object
|
|
data.GID = myRow["GID"].ToString().Trim();
|
|
data.SQLVER =myRow["SQLVER"].ToString().Trim();
|
|
data.SORT = myRow["SORT"].ToString().Trim();
|
|
data.SQLTEXT =myRow["SQLTEXT"].ToString().Trim();
|
|
data.REMARKS =myRow["REMARKS"].ToString().Trim();
|
|
#endregion
|
|
headList.Add(data);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
return headList;
|
|
}
|
|
|
|
|
|
static public List<sys_update_sql> GetUpDateLogList(string strCondition, string sort = null)
|
|
{
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT '' GID,版本号 SQLVER,[SORT],'' SQLTEXT,UPSTATUS,uptext REMARKS,INPUTBY,INPUTTIME,'' AUDITBY,null AUDITTIME");
|
|
strSql.Append(",(select ShowName from [user] where GID=s.INPUTBY) as INPUTBYREF");
|
|
strSql.Append(",'' as AUDITBYREF,UPFILES");
|
|
strSql.Append(" from t_sys_soft 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 版本号 DESC,SORT DESC");
|
|
|
|
}
|
|
return SetData(strSql);
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region 本地版本更新
|
|
|
|
public static DBResult SaveSysSoft(sys_update_sql headdata,string upstatus,string errostr,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 t_sys_soft(版本号,软件名称,INPUTBY,INPUTTIME,UPSTATUS,SORT,UPFILES,uptext,UPREMARK )
|
|
values (@SQLVER,'d7',@INPUTBY,@INPUTTIME,@UPSTATUS,@SORT,@UPFILES,@UPTEXT,@UPREMARK ) ");
|
|
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@SQLVER", DbType.String,headdata.SQLVER);
|
|
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, "@UPSTATUS", DbType.String, upstatus);
|
|
db.AddInParameter(cmdInsert, "@SORT", DbType.String,headdata.SORT);
|
|
db.AddInParameter(cmdInsert, "@UPFILES", DbType.String,"0");
|
|
db.AddInParameter(cmdInsert, "@UPTEXT", DbType.String,headdata.REMARKS);
|
|
db.AddInParameter(cmdInsert, "@UPREMARK", DbType.String, errostr);
|
|
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
|
|
tran.Commit();
|
|
}
|
|
catch (Exception)
|
|
{
|
|
tran.Rollback();
|
|
|
|
result.Success = false;
|
|
result.Message = "保存出现错误，请重试或联系系统管理员";
|
|
|
|
return result;
|
|
}
|
|
}
|
|
|
|
result.Success = true;
|
|
result.Message = "保存成功" + result.Message;
|
|
|
|
return result;
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
#region 相关表
|
|
|
|
static public List<sys_update_sql_table> GetSqlTableList(string strCondition, string sort = null)
|
|
{
|
|
|
|
var strSql = new StringBuilder();
|
|
strSql.Append("SELECT [GID],[VERGID],[TBLGID],[INPUTBY],[INPUTTIME]");
|
|
strSql.Append(",(select TBLCNAME from sys_table where GID=sys_update_sql_table.TBLGID) TBLNAME");
|
|
strSql.Append(",(select ShowName from [user] where GID=sys_update_sql_table.INPUTBY) as INPUTBYREF");
|
|
|
|
strSql.Append(" from sys_update_sql_table ");
|
|
|
|
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 INPUTTIME DESC");
|
|
|
|
}
|
|
return SetsqltableData(strSql);
|
|
}
|
|
|
|
|
|
private static List<sys_update_sql_table> SetsqltableData(StringBuilder strSql)
|
|
{
|
|
var headList = new List<sys_update_sql_table>();
|
|
Database db = DatabaseFactory.CreateDatabase();
|
|
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
|
|
{
|
|
while (reader.Read())
|
|
{
|
|
sys_update_sql_table data = new sys_update_sql_table();
|
|
#region Set DB data to Object
|
|
data.GID = Convert.ToString(reader["GID"]);
|
|
data.VERGID = Convert.ToString(reader["VERGID"]);
|
|
data.TBLGID = Convert.ToString(reader["TBLGID"]);
|
|
data.TBLNAME = Convert.ToString(reader["TBLNAME"]);
|
|
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 Savesqltable(List<sys_update_sql_table> 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_update_sql_table (GID,VERGID,TBLGID,INPUTBY,INPUTTIME)
|
|
values (@GID,@VERGID,@TBLGID,@INPUTBY,@INPUTTIME) ");
|
|
|
|
var cmdUpdate =
|
|
db.GetSqlStringCommand(
|
|
@"update sys_update_sql_table set TBLGID=@TBLGID where GID=@GID ");
|
|
|
|
if (bodyList != null)
|
|
{
|
|
foreach (var enumValue in bodyList)
|
|
{
|
|
|
|
if (enumValue.VERGID == "*" || enumValue.VERGID == "")
|
|
{
|
|
cmdInsert.Parameters.Clear();
|
|
db.AddInParameter(cmdInsert, "@GID", DbType.String, Guid.NewGuid().ToString());
|
|
db.AddInParameter(cmdInsert, "@VERGID", DbType.String, PID);
|
|
db.AddInParameter(cmdInsert, "@TBLGID", DbType.String, enumValue.TBLGID);
|
|
db.AddInParameter(cmdInsert, "@INPUTBY", DbType.String, userid);
|
|
db.AddInParameter(cmdInsert, "@INPUTTIME", DbType.String, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
|
db.ExecuteNonQuery(cmdInsert, tran);
|
|
}
|
|
else
|
|
{
|
|
|
|
cmdUpdate.Parameters.Clear();
|
|
db.AddInParameter(cmdUpdate, "@GID", DbType.String, enumValue.GID);
|
|
|
|
db.AddInParameter(cmdUpdate, "@TBLGID", DbType.String, enumValue.TBLGID);
|
|
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 Deletesqltable(List<sys_update_sql_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 cmdDelete = db.GetSqlStringCommand("delete from sys_update_sql_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
|
|
|
|
|
|
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;
|
|
}
|
|
|
|
}
|
|
}
|