/// <summary> /// /// </summary> /// <param name="DBName"></param> /// <param name="model"></param> /// <returns></returns> public bool Add(string DBName, Model.Sys_Columns model) { IDataBase db = DBHelperFactory.CreateDBInstance(DBName); if (db.DBType == Model.Enum.DBEnum.SqlServer)//SqlServer { StringBuilder sb = new StringBuilder(); sb.Append("alter table " + model.TableName + " add column " + model.ColumnName + " " + model.ColumnType + model.Size); if (model.IsNullable) { sb.Append(" null"); } else { sb.Append(" not null "); } //TODO:增加默认值 } else if (db.DBType == Model.Enum.DBEnum.MySql)//MySql { } else if (db.DBType == Model.Enum.DBEnum.Oracle)//Oracle { } else { throw new Exception(DBName + "数据库类型未知"); } return(true); }
/// <summary> /// 将表和信息记录到sys表 /// </summary> /// <param name="model"></param> public bool AddTableInfo(MyPlatform.Model.Sys_Tables model) { List <SqlCommandData> sqlCommands = new List <SqlCommandData>();//事务参数 IDataBase dbDefault = DBHelperFactory.CreateDBInstance(defaultCon); SqlCommandData sc2 = SqlFactory.CreateInsertSqlByRef <MyPlatform.Model.Sys_Tables>(model); sqlCommands.Add(sc2); //dbDefault.ExecuteTran(sqlCommands); //sqlCommands = new List<SqlCommandData>(); //SqlCommandData scID = new SqlCommandData(); //scID.CommandText = "select SCOPE_IDENTITY()"; //sqlCommands.Add(scID); //int id = Convert.ToInt32(dbDefault.ExecuteScalar("select IDENT_CURRENT('Sys_Tables')")); SqlCommandData sc3 = new SqlCommandData(); sc3.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','CreatedBy','CreatedBy','创建人','NVarchar',30,0,'','')"; sqlCommands.Add(sc3); SqlCommandData sc4 = new SqlCommandData(); sc4.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','CreatedDate','CreatedDate','创建时间','DateTime',0,0,'','')"; sqlCommands.Add(sc4); SqlCommandData sc5 = new SqlCommandData(); sc5.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','UpdatedBy','UpdatedBy','更新人','NVarchar',30,1,'','')"; sqlCommands.Add(sc5); SqlCommandData sc6 = new SqlCommandData(); sc6.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','UpdatedDate','UpdatedDate','更新时间','DateTime',0,1,'','')"; sqlCommands.Add(sc6); SqlCommandData sc7 = new SqlCommandData(); sc7.CommandText = @"INSERT INTO [dbo].[Sys_Columns] ([CreatedBy] ,[CreatedDate] ,[UpdatedBy] ,[UpdatedDate] ,[Deleted] ,[TableID] ,[TableName] ,[ColumnName] ,[ColumnName_EN] ,[ColumnName_CN] ,[ColumnType] ,[Size] ,[IsNullable] ,[DefaultValue] ,[Remark]) VALUES ('" + model.CreatedBy + "','" + model.CreatedDate + "','" + model.UpdatedBy + "','" + model.UpdatedDate + "','0',(SELECT IDENT_CURRENT('Sys_Tables')),'" + model.TableName + "','Deleted','Deleted','是否已删除','Bit',0,1,0,'')"; sqlCommands.Add(sc7); return(dbDefault.ExecuteTran(sqlCommands)); }
public void Init() { var connStr = System.Configuration.ConfigurationManager.AppSettings["ConntionString"].ToString(); DBBase dbbase = new DBBase(); DataBaseHelper = DBHelperFactory.GetSqlDataClassHelper(SqlPrividerType.SqlClient, connStr, GetNameSpaceName(dbbase.GetType())); }
/// <summary> /// 获取列集合 /// </summary> /// <param name="tableID">表ID</param> /// <returns></returns> public DataSet GetList(int tableID) { string sql = "SELECT * FROM dbo.Sys_Columns where tableID=@tableID"; IDataParameter[] pars = { new SqlParameter("tableID", SqlDbType.Int) }; pars[0].Value = tableID; IDataBase db = DBHelperFactory.CreateDBInstance("Default"); return(db.Query(sql, pars)); }
/// <summary> /// 是否存在表,0-不存在,1-存在 /// </summary> /// <param name="tableName">表名</param> /// <param name="dbName">数据库名</param> /// <param name="dbTypeCode">数据库类型</param> /// <returns></returns> public ReturnData ExistsTable(string tableName, string dbCon) { ReturnData result = new ReturnData(); try { IDataBase db = DBHelperFactory.CreateDBInstance(dbCon); Dictionary <string, string> dic = DBInfoCache.GetDBInfo(dbCon); if (dic == null) { result.SetErrorMsg("找不到对应的数据库配置信息"); } else { string sql = ""; IDataParameter[] paras = new IDataParameter[1]; switch (dic["DBTypeCode"].ToLower()) { case "sqlserver": sql = " SELECT 1 FROM dbo.SysObjects WHERE ID = object_id(@tableName) AND OBJECTPROPERTY(ID, 'IsTable') = 1 "; paras = new IDataParameter[1] { new SqlParameter("@tableName", SqlDbType.VarChar, 30) }; paras[0].Value = tableName; break; case "oracle": //TODO:集成oracle result.SetErrorMsg("oracle数据库未实现!"); return(result); break; case "mysql": //TODO:集成mysql result.SetErrorMsg("oracle数据库未实现!"); return(result); break; default: break; } result.S = Convert.ToInt32(db.ExecuteScalar(sql, paras)) > 0; if (!result.S) { result.M = "数据库已经存在表名为:" + tableName + "的表"; } result.S = true; } } catch (SqlException ex) { result.SetErrorMsg(ex.Message); } return(result); }
public DataSet GetDetail(int apiID) { DataSet ds = new DataSet(); string sql = "select * from sys_apis where ID=@ID;select * from api_input where apiid=@ID;select * from api_output where apiid=@ID;"; SqlParameter[] pars = { new SqlParameter("@ID", apiID) }; IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon); ds = db.Query(sql, pars); return(ds); }
/// <summary> /// 查询API信息 /// </summary> /// <param name="condition"></param> /// <returns></returns> public DataSet GetList(string condition) { DataSet ds = new DataSet(); condition = "%" + condition + "%"; string sql = "select * from Sys_APIs WHERE PATINDEX(@condition,Title+ApiName+Description)>0"; IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon); IDataParameter[] pars = { new SqlParameter("@condition", condition) }; return(db.Query(sql, pars)); }
public DataTable GetMemberUniqueRuns(int memberID) { string sql = "select distinct l.Location, p.Product from RunLog rl " + "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " + "join Product p on rlp.ProductID = p.ProductID " + "join Location l on l.LocationID = rl.DeliverToLocationID " + "where rl.RiderMemberID = " + memberID + " " + "order by Location"; return(DBHelperFactory.DBHelper().ExecuteDataTable(sql)); }
/// <summary> /// 统计表数据记录数 /// </summary> /// <param name="tableName">表名</param> /// <param name="dbCon">数据库连接名</param> /// <returns></returns> public int RecordCount(string tableName, string dbCon) { try { IDataBase db = DBHelperFactory.CreateDBInstance(dbCon); string sql = ""; sql = "select count(1) from @tableName"; SqlParameter[] pars = { new SqlParameter("@tableName", SqlDbType.VarChar, 100) }; pars[0].Value = tableName; return(Convert.ToInt32(db.ExecuteScalar(sql, pars))); } catch (SqlException ex) { throw ex; } }
/// <summary> /// 创建表以及默认字段CreatedBy、CreatedDate、UpdatedDate、CreatedDate、Deleted /// </summary> /// <param name="model">表信息</param> /// <returns></returns> public ReturnData Add(MyPlatform.Model.Sys_Tables model) { ReturnData result = new ReturnData(); try { List <SqlCommandData> sqlCommands = new List <SqlCommandData>();//事务参数 StringBuilder sql = new StringBuilder(); sql.Append("Create table {0} ("); sql.Append(" ID int primary key identity(1,1),"); sql.Append(" CreatedBy nvarchar(30) not null,"); sql.Append(" CreatedDate DATETIME DEFAULT(GETDATE()) not null,"); sql.Append(" UpdatedBy nvarchar(30) default(''),"); sql.Append(" UpdatedDate datetime default(getdate()),"); sql.Append(" Deleted bit DEFAULT(0)"); sql.Append(" )"); SqlCommandData sc = new SqlCommandData(); sc.CommandText = string.Format(sql.ToString(), model.TableName); sqlCommands.Add(sc); IDataBase dbCreate = DBHelperFactory.CreateDBInstance(model.DBCon); if (dbCreate.ExecuteTran(sqlCommands)) { if (AddTableInfo(model))//记录表信息和列信息 { result.S = true; } else { result.S = false; result.SetErrorMsg("保存表和列信息到系统表失败"); } } else { result.S = false; result.SetErrorMsg("表格创建失败!"); } } catch (Exception ex) { result.S = false; result.SetErrorMsg("保存失败:" + ex.Message); } return(result); }
/// <summary> /// 获取 /// </summary> /// <param name="DBName"></param> /// <returns></returns> public DataTable GetListByDBName(string DBCon) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from Sys_Tables where 1=1 "); IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon); if (!string.IsNullOrEmpty(DBCon)) { strSql.Append(" and DBCon=@DBCon"); SqlParameter[] parameters = { new SqlParameter("@DBCon", SqlDbType.VarChar, 30) }; parameters[0].Value = DBCon; return(db.Query(strSql.ToString(), parameters).Tables[0]); } else { return(db.Query(strSql.ToString()).Tables[0]); } }
public List <string> ListMobileNumbersWithTag(string tag) { List <string> ret = new List <string>(); string sql = "select distinct m.MobileNumber " + "from Member m " + "join Member_Tag mt on mt.MemberID = m.MemberID " + "join Tag t on t.TagID = mt.TagID " + "and m.LeaveDate is null " + "where t.Tag = '" + tag + "'"; DataTable tbl = DBHelperFactory.DBHelper().ExecuteDataTable(sql); if (tbl != null && tbl.Rows.Count > 0) { foreach (DataRow r in tbl.Rows) { ret.Add(r[0].ToString()); } } return(ret); }
public List <string> ListMobileNumbersWithAnyTagsIn(string tagsCsv) { List <string> ret = new List <string>(); string[] tags = tagsCsv.Split(','); List <string> tagList = new List <string>(); foreach (string tag in tags) { if (tag.Trim() != string.Empty) { tagList.Add(tag); } } string inClause = "in ("; foreach (string t in tagList) { inClause += "'" + t.Trim() + "',"; } inClause = inClause.Substring(0, inClause.Length - 1); inClause += ")"; string sql = "select distinct m.MobileNumber " + "from Member m " + "join Member_Tag mt on mt.MemberID = m.MemberID " + "join Tag t on t.TagID = mt.TagID " + "and m.LeaveDate is null " + "where t.Tag " + inClause; DataTable tbl = DBHelperFactory.DBHelper().ExecuteDataTable(sql); if (tbl != null && tbl.Rows.Count > 0) { foreach (DataRow r in tbl.Rows) { ret.Add(r[0].ToString()); } } return(ret); }
/// <summary> /// /// </summary> /// <param name="tableID"></param> /// <param name="page"></param> /// <returns></returns> public ReturnData GetDetail(int tableID, Pagination page) { ReturnData result = new ReturnData(); try { DataSet ds = new DataSet(); int startIndex = DALUtils.CalStartIndex(page.PageSize, page.PageIndex); int endIndex = DALUtils.CalEndIndex(page.PageSize, page.PageIndex); string sql = "select * from sys_tables where ID=@ID;select * from (select ROW_NUMBER() OVER(ORDER BY orderNO)RN,* from sys_columns where tableID=@ID)t where t.rn>" + startIndex.ToString() + " and t.rn<" + endIndex.ToString() + ";select count(1)TotalCount from sys_columns where tableID=@ID"; IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon); SqlParameter[] pars = { new SqlParameter("@ID", tableID) }; ds = db.Query(sql, pars); result.D = ds; result.S = true; } catch (Exception ex) { result.SetErrorMsg(ex.Message); } return(result); }
/* * * public DataTable Report_RecentRunLog() * { * string sql = * "SELECT " + * "date(CallDate) as Date, " + * "TIME(rpad(replace(CallTime, '.',':'), 5, '0')) as Time, " + * "CONCAT(m.FirstName, ' ', m.LastName) as Rider, " + * "Consignment, " + * "CollectFrom as Origin, " + * "Destination, " + * "CONCAT(con.FirstName, ' ', con.LastName) as Controller " + * "FROM RawRunLog rr " + * "LEFT JOIN Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " + * "LEFT JOIN Member con on rr.Controller = (CONCAT(con.LastName, ' ', con.FirstName)) " + * "order by CallDate desc, RawRunLogID desc " + * "LIMIT 100;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_Top10Riders() * { * string sql = * "select Name from " + * "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " + * "from RunLog rl " + * "LEFT join Member m on m.MemberID = rl.RiderMemberID " + * "group by Name " + * "order by Runs desc " + * "LIMIT 10) top " + * "order by Name;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_Top102013Riders() * { * string sql = * "select Name from " + * "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " + * "from RawRunLog rr " + * "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " + * "where m.FirstName is not null " + * "group by Name " + * "order by Runs desc " + * "LIMIT 10) sub " + * "order by Name;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_RunButNoLogin() * { * string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Rider, date(m.JoinDate) as Joined, m.EmailAddress as Email, date(max(rr.CallDate)) as LastRun, count(*) as Runs " + * "from RawRunLog rr " + * "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " + * "where m.MemberID not in " + * "(select m.MemberID from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate is not null) " + * "and rr.CallDate > '2013-05-01' " + * "and m.LeaveDate is null " + * "group by m.MemberID " + * "order by max(rr.CallDate) desc;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_AverageCallsPerDay() * { * string sql = "SELECT dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as ShiftDay " + * ", round(count(*) / 34.28) as AverageCalls " + * //", ceil((count(*) / @weeks) * @riderfactor) as RidersRequired " + * "FROM RawRunLog " + * "WHERE CallDate > AddDate(CURRENT_DATE, -240) " + * "AND(Consignment like '%blood%' " + * "or Consignment like '%plate%' " + * "or Consignment like '%plas%' " + * "or Consignment like '%ffp%' " + * "or Consignment like '%sample%' " + * "or Consignment like '%drugs%' " + * "or Consignment like '%cd%' " + * "or Consignment like '%data%' " + * "or Consignment like '%disk%' " + * "or Consignment like '%disc%' " + * "or Consignment like '%package%') " + * "GROUP BY dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) " + * "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end);"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_CallsPerHourHeatMap() * { * string sql = "select dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as Day, " + * "Hour(CallTime) as Hour, count(*) as Calls " + * "from RawRunLog " + * "WHERE CallDate > AddDate(CURRENT_DATE, -240) " + * "AND Hour(CallTime) >= 0 AND Hour(CallTime) <= 23 " + * "AND(Consignment like '%blood%' " + * "or Consignment like '%plate%' " + * "or Consignment like '%plas%' " + * "or Consignment like '%ffp%' " + * "or Consignment like '%sample%' " + * "or Consignment like '%drugs%' " + * "or Consignment like '%cd%' " + * "or Consignment like '%data%' " + * "or Consignment like '%disk%' " + * "or Consignment like '%disc%' " + * "or Consignment like '%package%') " + * "group by dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime) " + * "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime)"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_TodaysUsers() * { * string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Member " + * "from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate > CURRENT_DATE() " + * "order by lastLoginDate desc;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * * * public DataTable Report_BoxesByProductByMonth() * { * string sql = "select concat(MONTHNAME(rl.DutyDate), ' ', year(rl.DutyDate)) as Month " + * ", p.Product, sum(rlp.Quantity) as BoxesCarried from RunLog rl " + * "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " + * "join Product p on p.ProductID = rlp.ProductID " + * "group by Month, Product " + * "order by month(rl.DutyDate), Product;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * */ public DataTable Report_RunLog(string year) { string sql = "select RunLogID as ID, date_format(DutyDate, '%Y-%m-%d') as 'DutyDate', " + "coalesce(date_format(CallDateTime, '%Y-%m-%d %H:%i'), 'N/A') as 'CallDateTime', cf.Location as 'CallFrom', " + "CASE WHEN rl.RunLogType='M' THEN Concat(cl.Location,' ', rl.CollectionPostcode) ELSE cl.Location END 'From', " + "CASE WHEN rl.RunLogType = 'M' THEN Concat(dl.Location,' ', rl.DeliverToPostcode) ELSE dl.Location END as 'To', coalesce(date_format(rl.CollectDateTime, '%H:%i'), 'NOT ACCEPTED') as Collected, " + "date_format(rl.DeliverDateTime, '%H:%i') as Delivered, " + "CASE WHEN rl.RunLogType = 'M' THEN Concat(fl.Location,' ', rl.DeliverToPostcode) ELSE fl.Location END as 'Destination', concat(m.LastName, ' ', m.FirstName) as Rider, " + "v.VehicleType as 'Vehicle', rl.Description as 'Consignment', " + "concat(c.LastName, ' ', c.FirstName) as Controller from RunLog rl " + "left join Member m on m.MemberID = rl.RiderMemberID " + "join Member c on c.MemberID = rl.ControllerMemberID " + "join Location cf on cf.LocationID = rl.CallFromLocationID " + "join Location cl on cl.LocationID = rl.CollectionLocationID " + "join Location dl on dl.LocationID = rl.DeliverToLocationID " + "join Location fl on fl.LocationID = rl.FinalDestinationLocationID " + "left join VehicleType v on v.VehicleTypeID = rl.VehicleTypeID " + "where YEAR(DutyDate) = " + year + " order by rl.DutyDate desc, rl.CallDateTime desc;"; DataTable ret = DBHelperFactory.DBHelper().ExecuteDataTable(sql); return(ret); }
/* * * public DataTable Report_RecentRunLog() * { * string sql = * "SELECT " + * "date(CallDate) as Date, " + * "TIME(rpad(replace(CallTime, '.',':'), 5, '0')) as Time, " + * "CONCAT(m.FirstName, ' ', m.LastName) as Rider, " + * "Consignment, " + * "CollectFrom as Origin, " + * "Destination, " + * "CONCAT(con.FirstName, ' ', con.LastName) as Controller " + * "FROM RawRunLog rr " + * "LEFT JOIN Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " + * "LEFT JOIN Member con on rr.Controller = (CONCAT(con.LastName, ' ', con.FirstName)) " + * "order by CallDate desc, RawRunLogID desc " + * "LIMIT 100;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_Top10Riders() * { * string sql = * "select Name from " + * "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " + * "from RunLog rl " + * "LEFT join Member m on m.MemberID = rl.RiderMemberID " + * "group by Name " + * "order by Runs desc " + * "LIMIT 10) top " + * "order by Name;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_Top102013Riders() * { * string sql = * "select Name from " + * "(select CONCAT(m.FirstName, ' ', m.LastName) Name, count(*) Runs " + * "from RawRunLog rr " + * "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " + * "where m.FirstName is not null " + * "group by Name " + * "order by Runs desc " + * "LIMIT 10) sub " + * "order by Name;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_RunButNoLogin() * { * string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Rider, date(m.JoinDate) as Joined, m.EmailAddress as Email, date(max(rr.CallDate)) as LastRun, count(*) as Runs " + * "from RawRunLog rr " + * "LEFT join Member m on rr.Rider = (CONCAT(m.LastName, ' ', m.FirstName)) " + * "where m.MemberID not in " + * "(select m.MemberID from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate is not null) " + * "and rr.CallDate > '2013-05-01' " + * "and m.LeaveDate is null " + * "group by m.MemberID " + * "order by max(rr.CallDate) desc;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_AverageCallsPerDay() * { * string sql = "SELECT dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as ShiftDay " + * ", round(count(*) / 34.28) as AverageCalls " + * //", ceil((count(*) / @weeks) * @riderfactor) as RidersRequired " + * "FROM RawRunLog " + * "WHERE CallDate > AddDate(CURRENT_DATE, -240) " + * "AND(Consignment like '%blood%' " + * "or Consignment like '%plate%' " + * "or Consignment like '%plas%' " + * "or Consignment like '%ffp%' " + * "or Consignment like '%sample%' " + * "or Consignment like '%drugs%' " + * "or Consignment like '%cd%' " + * "or Consignment like '%data%' " + * "or Consignment like '%disk%' " + * "or Consignment like '%disc%' " + * "or Consignment like '%package%') " + * "GROUP BY dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) " + * "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end);"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_CallsPerHourHeatMap() * { * string sql = "select dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end) as Day, " + * "Hour(CallTime) as Hour, count(*) as Calls " + * "from RawRunLog " + * "WHERE CallDate > AddDate(CURRENT_DATE, -240) " + * "AND Hour(CallTime) >= 0 AND Hour(CallTime) <= 23 " + * "AND(Consignment like '%blood%' " + * "or Consignment like '%plate%' " + * "or Consignment like '%plas%' " + * "or Consignment like '%ffp%' " + * "or Consignment like '%sample%' " + * "or Consignment like '%drugs%' " + * "or Consignment like '%cd%' " + * "or Consignment like '%data%' " + * "or Consignment like '%disk%' " + * "or Consignment like '%disc%' " + * "or Consignment like '%package%') " + * "group by dayname(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime) " + * "ORDER BY dayofweek(case when Hour(CallTime) > 17 then CallDate else AddDate(CallDate, -1) end), Hour(CallTime)"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * public DataTable Report_TodaysUsers() * { * string sql = "select CONCAT(m.FirstName, ' ', m.LastName) as Member " + * "from User u join Member m on m.MemberID = u.MemberID where u.lastLoginDate > CURRENT_DATE() " + * "order by lastLoginDate desc;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * * * * public DataTable Report_BoxesByProductByMonth() * { * string sql = "select concat(MONTHNAME(rl.DutyDate), ' ', year(rl.DutyDate)) as Month " + * ", p.Product, sum(rlp.Quantity) as BoxesCarried from RunLog rl " + * "join RunLog_Product rlp on rlp.RunLogID = rl.RunLogID " + * "join Product p on p.ProductID = rlp.ProductID " + * "group by Month, Product " + * "order by month(rl.DutyDate), Product;"; * return DBHelperFactory.DBHelper().ExecuteDataTable(sql); * } * */ public DataTable Report_RunLog() { string sql = "select RunLogID as ID, date_format(DutyDate, '%Y-%m-%d') as 'DutyDate', " + "coalesce(date_format(CallDateTime, '%Y-%m-%d %H:%i'), 'N/A') as 'CallDateTime', cf.Location as 'CallFrom', cl.Location as 'From', " + "dl.Location as 'To', coalesce(date_format(rl.CollectDateTime, '%H:%i'), 'NOT ACCEPTED') as Collected, " + "date_format(rl.DeliverDateTime, '%H:%i') as Delivered, " + //"timediff(rl.DeliverDateTime, rl.CollectDateTime) as 'Run Time', " + "fl.Location as 'Destination', concat(m.FirstName, ' ', m.LastName) as Rider, " + "v.VehicleType as 'Vehicle', rl.Description as 'Consignment', " + "concat(c.FirstName, ' ', c.LastName) as Controller from RunLog rl " + "left join Member m on m.MemberID = rl.RiderMemberID " + "join Member c on c.MemberID = rl.ControllerMemberID " + "join Location cf on cf.LocationID = rl.CallFromLocationID " + "join Location cl on cl.LocationID = rl.CollectionLocationID " + "join Location dl on dl.LocationID = rl.DeliverToLocationID " + "join Location fl on fl.LocationID = rl.FinalDestinationLocationID " + "left join VehicleType v on v.VehicleTypeID = rl.VehicleTypeID " + "where DutyDate > '2013-12-31' or CallDateTime > '2013-12-31' " + "order by rl.DutyDate desc, rl.CallDateTime desc;"; DataTable ret = DBHelperFactory.DBHelper().ExecuteDataTable(sql); return(ret); }
/// <summary> /// 新增API /// </summary> /// <param name="model"></param> /// <returns></returns> public ReturnData Add(Dictionary <object, object> dic) { ReturnData result = new ReturnData(); try { IDataBase db = DBHelperFactory.CreateDBInstance(defaultCon); //保存API信息 string sqlApi = @"insert into sys_apis(Title , ApiName , Description , CreatedBy , CreatedDate ) values(@title,@apiName,@description,'',getdate())"; List <SqlCommandData> liSql = new List <SqlCommandData>(); SqlCommandData cmdApi = new SqlCommandData(); cmdApi.CommandText = sqlApi; cmdApi.Paras = new SqlParameter[] { new SqlParameter("@title", dic["title"]), new SqlParameter("@apiName", dic["apiName"]), new SqlParameter("@description", dic["description"]) }; liSql.Add(cmdApi); //保存输入参数信息 string sqlInput = ""; string[] arrInput = (string[])dic["inputParam"]; SqlParameter[] parInput = new SqlParameter[4]; for (int i = 0; i < arrInput.Length; i++) { if (i % 4 == 0) { sqlInput = @"insert into api_input( ApiID , ParamName , ParamType , IsRequired , Description , OrderNo , CreatedBy , CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@input" + i.ToString(); SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]); parInput[i % 4] = param; } else if (i % 4 == 3) { sqlInput += ",@input" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())"; SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]); parInput[i % 4] = param; SqlCommandData cmdInput = new SqlCommandData(); cmdInput.CommandText = sqlInput; cmdInput.Paras = parInput; liSql.Add(cmdInput); //重新声明新的输入参数信息 parInput = new SqlParameter[4]; sqlInput = ""; } else { sqlInput += ",@input" + i.ToString(); SqlParameter param = new SqlParameter("@input" + i.ToString(), arrInput[i]); parInput[i % 4] = param; } } //保存输出参数信息 string sqlOutput = ""; string[] arrOutput = (string[])dic["outputParam"]; int outputColumns = Convert.ToInt32(dic["outputColumns"]); SqlParameter[] parOutput = new SqlParameter[outputColumns]; if (outputColumns == 4) { for (int i = 0; i < arrOutput.Length; i++) { if (i % 4 == 0) { sqlOutput = @"insert into api_output( ApiID , ParamName , ParamType , IsRequired , Description , OrderNo , CreatedBy , CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 4] = param; } else if (i % 4 == 3) { sqlOutput += ",@output" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())"; SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 4] = param; SqlCommandData cmdOutput = new SqlCommandData(); cmdOutput.CommandText = sqlOutput; cmdOutput.Paras = parOutput; liSql.Add(cmdOutput); //重新声明新的输入参数信息 parOutput = new SqlParameter[4]; sqlOutput = ""; } else { sqlOutput += ",@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 4] = param; } } } else if (outputColumns == 3) { for (int i = 0; i < arrOutput.Length; i++) { if (i % 3 == 0) { sqlOutput = @"insert into api_output( ApiID , ParamName , ParamType , IsRequired , Description , OrderNo , CreatedBy , CreatedDate) values((SELECT IDENT_CURRENT('Sys_Apis')),@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 3] = param; } else if (i % 3 == 2) { sqlOutput += ",'',@output" + i.ToString() + "," + (i * 40).ToString() + ",'',getdate())"; SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 3] = param; SqlCommandData cmdOutput = new SqlCommandData(); cmdOutput.CommandText = sqlOutput; cmdOutput.Paras = parOutput; liSql.Add(cmdOutput); //重新声明新的输入参数信息 parOutput = new SqlParameter[3]; sqlOutput = ""; } else { sqlOutput += ",@output" + i.ToString(); SqlParameter param = new SqlParameter("@output" + i.ToString(), arrOutput[i]); parOutput[i % 3] = param; } } } if (db.ExecuteTran(liSql)) { result.S = true; } else { result.SetErrorMsg("添加失败!"); } } catch (Exception ex) { result.SetErrorMsg(ex.Message); } return(result); }
public void GetDataBase(string con) { currentDB = DBHelperFactory.CreateDBInstance(con); }
public SqlHelper(string connectString, DataBaseClass dataBaseClass) { _dataBaseClass = dataBaseClass; _iDBHelper = DBHelperFactory.CreateDBHelper(dataBaseClass, connectString); }
/// <summary> /// Initializes and instance of a class implementing the IDBHelper interface. /// </summary> private static void InitializeDBHelper() { var connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; IDBHelper _DB = DBHelperFactory.GetInstance(DataBaseType.SQLServer, connectionString); }
public DataTable RunReport(SERVDataContract.Report report) { return(DBHelperFactory.DBHelper().ExecuteDataTable(report.Query)); }
/// <summary> /// 刪除表以及相关信息 /// </summary> /// <param name="tableID"></param> /// <param name="dbName"></param> /// <returns></returns> public bool Delete(int tableID) { IDataBase db = new SqlServerDataBase("Default"); //无数据的表可以删除 string sql = "select ID,DBCon,TableName,DBTypeCode from Sys_tables where id=@tableID"; SqlParameter[] pars = { new SqlParameter("@tableID", tableID) }; DataTable dt = db.Query(sql, pars).Tables[0]; if (dt.Rows.Count > 0) { IDataBase db2 = DBHelperFactory.CreateDBInstance(dt.Rows[0]["DBCon"].ToString()); string sql2 = "select count(1) from " + dt.Rows[0]["TableName"].ToString(); IDataParameter[] pars2 = new IDataParameter[1]; //TODO:增加多类型数据库操作 switch (dt.Rows[0]["DBTypeCode"].ToString().ToLower()) { case "sqlserver": pars2 = new IDataParameter[1] { new SqlParameter("@tableName", dt.Rows[0]["TableName"]) }; break; default: break; } if (Convert.ToInt32(db2.ExecuteScalar(sql2)) == 0) { string sqlDelete = ""; switch (dt.Rows[0]["DBTypeCode"].ToString().ToLower()) { case "sqlserver": sqlDelete = "drop table " + dt.Rows[0]["TableName"].ToString(); break; default: break; } db2.ExecuteNonQuery(sqlDelete); IDataParameter[] pars4 = { new SqlParameter("@tableID", tableID) }; List <SqlCommandData> li = new List <SqlCommandData>(); SqlCommandData scd = new SqlCommandData(); scd.CommandText = "update sys_tables set deleted=1 where id=@tableID"; scd.Paras = new SqlParameter[1] { new SqlParameter("@tableID", tableID) }; li.Add(scd); SqlCommandData scd2 = new SqlCommandData(); scd2.CommandText = "update sys_columns set deleted=1 where tableid=@tableID"; scd2.Paras = new SqlParameter[1] { new SqlParameter("@tableID", tableID) }; li.Add(scd2); return(db.ExecuteTran(li)); } else { return(false); } } return(true); }
private int GetTagId(string tagName) { return(Convert.ToInt32(DBHelperFactory.DBHelper().ExecuteScalar("select TagID from Tag where Tag = " + SERV.Utils.String.DBSafeString(tagName)))); }
public DataTable ExecuteSQL(string sql) { return(DBHelperFactory.DBHelper().ExecuteDataTable(sql)); }