/// <summary> /// 判断指定id是否为表中记录.enabled参数为true时,除了是表中记录还要是开启的 /// </summary> /// <param name="code"></param> /// <returns></returns> public static bool CheckByCode(string code, bool enabled = true) { StringBuilder sb = new StringBuilder("1=1"); string where = sb.ToString(); string sql = $@"SELECT COUNT(id) FROM Keyval WHERE id=@id"; string isEnabled = $@"SELECT COUNT(id) FROM Keyval WHERE id=@id and status=1"; // SQLServer db = new SQLServer(); object res = db.ExecuteScalar(sql, code, 1); if (int.Parse(res.ToString()) != 1) { return(false); } if (enabled == false) { return(true); } // status=1是已开启的记录,status=2为停用记录 res = db.ExecuteScalar(isEnabled, code, 1); return(int.Parse(res.ToString()) == 1); }
public void SQLServerConnector() { using (var connection = new SQLServer(_msSqlConnection)) { try { connection.ExecuteNonQuery($@"CREATE TABLE [dbo].[TestTable]([id] int primary key,[value] nvarchar(255))"); for (var iter = 0; iter < 10; iter++) { var affectedCreate = connection.ExecuteNonQuery(@"INSERT INTO [dbo].[TestTable]([id],[value]) VALUES(@id,@value)", new[] { new SqlParameter("id", iter), new SqlParameter("value", "test") }); Assert.AreEqual(affectedCreate, 1); var selectedById = connection.ExecuteReader <TestTable>(@"SELECT * FROM [dbo].[TestTable] WHERE [id] = @id", new[] { new SqlParameter("id", iter) }); Assert.AreEqual(selectedById.First().id, iter); Assert.AreEqual(selectedById.First().value, "test"); var selectedByIdDynamic = connection.ExecuteReader(@"SELECT * FROM [dbo].[TestTable] WHERE [id] = @id", new[] { new SqlParameter("id", iter) }); Assert.AreEqual(selectedByIdDynamic.First().id, iter); Assert.AreEqual(selectedByIdDynamic.First().value, "test"); var affectedUpdate = connection.ExecuteNonQuery(@"UPDATE [dbo].[TestTable] SET [value] = @value WHERE [id] = @id", new[] { new SqlParameter("id", iter), new SqlParameter("value", "updated") }); Assert.AreEqual(affectedUpdate, 1); var affectedDelete = connection.ExecuteNonQuery(@"DELETE FROM [dbo].[TestTable] WHERE [id] = @id", new[] { new SqlParameter("id", iter) }); Assert.AreEqual(affectedDelete, 1); } var affectedSelectScalar = connection.ExecuteScalar <int>(@"SELECT COUNT(1) FROM [dbo].[TestTable]"); Assert.AreEqual(affectedSelectScalar, 0); Assert.Pass(); } finally { connection.ExecuteNonQuery($@"DROP TABLE [dbo].[TestTable]"); } } }
/// <summary> /// 数据(分页):查找出符合条件的多个记录 /// </summary> /// <param name="para">查询条件参数</param> /// <returns></returns> public static AssetM[] List(AssetM para) { StringBuilder sb = new StringBuilder("1=1"); string where = sb.ToString(); string sql = $@"SELECT id,title,code,amount,value,positions,price,remark,profit,excorg,risk,kind,valuedate,expdate,rate,action,ctime,itemCode FROM Asset WHERE {where} ORDER BY ctime DESC OFFSET @OffSetRows ROWS FETCH NEXT @PageSize ROWS ONLY"; string sqlcount = $@"SELECT COUNT(id) FROM Asset WHERE {where}"; // SQLServer db = new SQLServer(); // 总条数(如果为0无需再查询) int count = 0; var listcount = db.ExecuteScalar <AssetM>(sqlcount, para); if (listcount == null || !int.TryParse(listcount.ToString(), out count) || count == 0) { return(null); } para.ListCount = count; // 数据列表 AssetM[] data = db.ExecuteQuery <AssetM, AssetM>(sql, para); return(data); }
/// <summary> /// 查询最后有效更新资产日期 返回一个yyyy-MM-dd的日期公式字符串. /// 失败返回 default /// </summary> /// <returns></returns> public static DateTimeOffset GetLastUpDay() { string sql = $@" SELECT TOP 1 ctime FROM Asset WHERE status=1 ORDER BY ctime DESC"; SQLServer db = new SQLServer(); object lastDate = db.ExecuteScalar(sql); if (lastDate == null) { return(default);
/// <summary> /// ItemCode值标识同一个资产的更新周期,从首次添加起到清仓止,首次添加资产时生成. /// 以后更新这个资产时会传来资产Id,据此查得ItemCode值. /// 举例说明:510300这个资产,在第一次添加时生成ItemCode "xxx",后续更新资产时, /// 此值都是"xxx",一直到清仓都是. /// 假如下一次又添加510300时,会生成新的ItemCode "xxx2". /// </summary> /// <param name="data"></param> private static bool AssetItemCode(AssetM data) { if (string.IsNullOrWhiteSpace(data.Id)) { data.ItemCode = RandHelp.NewGuid(); return(true); } string sql = "SELECT itemCode FROM Asset WHERE Id=@Id"; SQLServer db = new SQLServer(); object itemcode = db.ExecuteScalar(sql, data.Id, 1); if (itemcode == null || string.IsNullOrWhiteSpace(itemcode.ToString())) { return(false); } data.ItemCode = itemcode.ToString(); return(true); }
/// <summary> /// 今天是否更新过资产(只要有1条有效更新就是) /// 也可以指定日期yyyy-MM-dd /// </summary> /// <param name="dateDay">yyyy-MM-dd</param> /// <returns></returns> public static bool IsUpdateToday(string dateDay = null) { string day = dateDay ?? DateTimeOffset.Now.ToString("yyyy-MM-dd"); DateTimeOffset dayStart = DateTimeOffset.Parse(day + " 00:00:00"); DateTimeOffset dayEnd = DateTimeOffset.Parse(day + " 23:59:59"); string sql = $@" SELECT count(b.id) FROM Asset WHERE ctime >= @stime AND ctime <= @etime AND status=1"; SQLServer db = new SQLServer(); object count = db.ExecuteScalar(sql, dayStart, dayEnd); if (count == null) { return(false); } return(int.Parse(count.ToString()) > 0); }