/// <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);
}
