public async Task Update(ThirdPartySystemTokenRecord record)
{
var storeInfo = await GetDBAllStoreInfo(record.UserKey, _thirdPartySystemTokenRecordHashGroupName);
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, storeInfo[0], async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
command.CommandText = string.Format(@"
update {0}
set [token]=@token,
[timeout]=@timeout,
[lastrefeshtime]=@lastrefeshtime,
[modifytime]=getutcdate()
where [id] = @id", storeInfo[1]);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = record.ID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@token", SqlDbType.NVarChar, record.Token.Length)
{
Value = record.Token
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@timeout", SqlDbType.Int)
{
Value = record.Timeout
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@lastrefeshtime", SqlDbType.DateTime)
{
Value = record.LastRefeshTime
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
await command.ExecuteNonQueryAsync();
}
});
}
public async Task <ThirdPartySystemTokenRecord> QueryByID(string userKey, Guid id)
{
var storeInfo = await GetDBAllStoreInfo(userKey, _thirdPartySystemTokenRecordHashGroupName);
ThirdPartySystemTokenRecord result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, storeInfo[0], async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
command.CommandText = string.Format(@"SELECT {0} FROM [dbo].[{1}] WHERE id=@id;", StoreHelper.GetThirdPartySystemTokenRecordStoreSelectFields(string.Empty), storeInfo[1]);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = id
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
result = new ThirdPartySystemTokenRecord();
StoreHelper.SetThirdPartySystemTokenRecordSelectFields(result, reader, string.Empty);
result.SystemLoginEndpoint = await _systemLoginEndpointStore.QueryById(result.SystemLoginEndpointID);
result.AuthorizationEndpoint = await _authorizationEndpointStore.QueryById(result.AuthorizationEndpointID);
if (result.AuthorizationEndpoint == null || result.SystemLoginEndpoint == null)
{
result = null;
}
}
await reader.CloseAsync();
}
}
});
return(result);
}
/// <summary>
/// 为第三方系统令牌记录从DbDataReader中赋值
/// </summary>
/// <param name="endpoint"></param>
/// <param name="reader"></param>
/// <param name="prefix"></param>
public static void SetThirdPartySystemTokenRecordSelectFields(ThirdPartySystemTokenRecord record, DbDataReader reader, string prefix)
{
record.ID = (Guid)reader[string.Format("{0}id", prefix)];
if (reader[string.Format("{0}userkey", prefix)] != DBNull.Value)
{
record.UserKey = reader[string.Format("{0}userkey", prefix)].ToString();
}
if (reader[string.Format("{0}systemloginendpointid", prefix)] != DBNull.Value)
{
record.SystemLoginEndpointID = (Guid)reader[string.Format("{0}systemloginendpointid", prefix)];
}
if (reader[string.Format("{0}authorizationendpointid", prefix)] != DBNull.Value)
{
record.AuthorizationEndpointID = (Guid)reader[string.Format("{0}authorizationendpointid", prefix)];
}
if (reader[string.Format("{0}token", prefix)] != DBNull.Value)
{
record.Token = reader[string.Format("{0}token", prefix)].ToString();
}
if (reader[string.Format("{0}timeout", prefix)] != DBNull.Value)
{
record.Timeout = (int)reader[string.Format("{0}timeout", prefix)];
}
if (reader[string.Format("{0}lastrefeshtime", prefix)] != DBNull.Value)
{
record.LastRefeshTime = (DateTime)reader[string.Format("{0}lastrefeshtime", prefix)];
}
if (reader[string.Format("{0}createtime", prefix)] != DBNull.Value)
{
record.CreateTime = (DateTime)reader[string.Format("{0}createtime", prefix)];
}
if (reader[string.Format("{0}modifytime", prefix)] != DBNull.Value)
{
record.ModifyTime = (DateTime)reader[string.Format("{0}modifytime", prefix)];
}
}
public async Task Add(ThirdPartySystemTokenRecord record)
{
var storeInfo = await GetDBAllStoreInfo(record.UserKey, _thirdPartySystemTokenRecordHashGroupName);
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, storeInfo[0], async (conn, transaction) =>
{
//新增
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
if (record.ID == Guid.Empty)
{
command.CommandText = string.Format(@"
INSERT INTO {0}
(
[id]
,[userkey]
,[systemloginendpointid]
,[authorizationendpointid]
,[token]
,[timeout]
,[lastrefeshtime]
,[createtime]
,[modifytime]
)
VALUES
(default
, @userkey
, @systemloginendpointid
, @authorizationendpointid
, @token
, @timeout
, @lastrefeshtime
, GETUTCDATE()
, GETUTCDATE());
select @newid =[id] from {0} where [sequence] = SCOPE_IDENTITY()", storeInfo[1]);
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
}
else
{
command.CommandText = string.Format(@"
INSERT INTO {0}
(
[id]
,[userkey]
,[systemloginendpointid]
,[authorizationendpointid]
,[token]
,[timeout]
,[lastrefeshtime]
,[createtime]
,[modifytime]
)
VALUES
(
@id
, @userkey
, @systemloginendpointid
, @authorizationendpointid
, @token
, @timeout
, @lastrefeshtime
, GETUTCDATE()
, GETUTCDATE());", storeInfo[1]);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = record.ID
};
command.Parameters.Add(parameter);
}
parameter = new SqlParameter("@userkey", SqlDbType.NVarChar, 150)
{
Value = record.UserKey
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@systemloginendpointid", SqlDbType.UniqueIdentifier)
{
Value = record.SystemLoginEndpointID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@authorizationendpointid", SqlDbType.UniqueIdentifier)
{
Value = record.AuthorizationEndpointID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@token", SqlDbType.NVarChar, record.Token.Length)
{
Value = record.Token
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@timeout", SqlDbType.Int)
{
Value = record.Timeout
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@lastrefeshtime", SqlDbType.DateTime)
{
Value = record.LastRefeshTime
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
try
{
await command.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
if (ex == null)
{
throw;
}
if (ex.Number == 2601)
{
var fragment = new TextFragment()
{
Code = TextCodes.ExistSameThirdPartySystemTokenRecord,
DefaultFormatting = "已经存在登录终结点{0}、验证终结点{1}、用户关键字{2}的第三方系统令牌记录",
ReplaceParameters = new List <object>()
{
record.SystemLoginEndpointID.ToString(), record.AuthorizationEndpointID.ToString(), record.UserKey
}
};
throw new UtilityException((int)Errors.ExistSameThirdPartySystemTokenRecord, fragment);
}
else
{
throw;
}
}
//如果用户未赋值ID则创建成功后返回ID
if (record.ID == Guid.Empty)
{
record.ID = (Guid)command.Parameters["@newid"].Value;
}
;
}
});
}
public async Task <QueryResult <ThirdPartySystemTokenRecord> > QueryByUserKeyPage(string userKey, int page, int pageSize)
{
var storeInfo = await GetDBAllStoreInfo(userKey, _thirdPartySystemTokenRecordHashGroupName);
QueryResult <ThirdPartySystemTokenRecord> result = new QueryResult <ThirdPartySystemTokenRecord>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, storeInfo[0], async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
})
{
SqlParameter parameter;
command.CommandText = string.Format(@"SET @currentpage = @page;
SELECT @count = COUNT(*)
FROM [dbo].[{1}]
WHERE [userkey]=@userkey
SELECT {0}
FROM [dbo].[{1}]
WHERE [userkey]=@userkey
ORDER BY sequence OFFSET (@pagesize * (@currentpage - 1)) ROWS FETCH NEXT @pagesize ROWS ONLY;", StoreHelper.GetThirdPartySystemTokenRecordStoreSelectFields(string.Empty), storeInfo[1]);
parameter = new SqlParameter("@userkey", SqlDbType.NVarChar, 150)
{
Value = userKey
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@currentpage", SqlDbType.Int)
{
Value = page
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var record = new ThirdPartySystemTokenRecord();
StoreHelper.SetThirdPartySystemTokenRecordSelectFields(record, reader, string.Empty);
record.SystemLoginEndpoint = await _systemLoginEndpointStore.QueryById(record.SystemLoginEndpointID);
record.AuthorizationEndpoint = await _authorizationEndpointStore.QueryById(record.AuthorizationEndpointID);
result.Results.Add(record);
}
await reader.CloseAsync();
result.TotalCount = (int)command.Parameters["@count"].Value;
result.CurrentPage = page;
}
}
});
return(result);
}
