/// <summary>
/// 根据ID查询
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task <ClientSystemLoginEndpoint> QueryById(Guid id)
{
ClientSystemLoginEndpoint result = null;
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForSystemToken(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = string.Format(@"SELECT {0} FROM [dbo].[ClientSystemLoginEndpoint] WHERE id=@id;", StoreHelper.GetClientSystemLoginEndpointSelectFields(string.Empty)),
Transaction = sqlTran
})
{
var 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 ClientSystemLoginEndpoint();
StoreHelper.SetClientSystemLoginEndpointSelectFields(result, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(result);
}
public async Task Delete(Guid optionSetValueId, Guid itemId)
{
//获取读写连接字符串
var strConn = _entityMetadataConnectionFactory.CreateAllForEntityMetadata();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = "delete from OptionSetValueItem where [optionsetvalueid]=@optionsetvalueid and [id]=@id"
})
{
SqlParameter parameter;
parameter = new SqlParameter("@optionsetvalueid", SqlDbType.UniqueIdentifier)
{
Value = optionSetValueId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = itemId
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task Update(OptionSetValueMetadata metadata)
{
//获取读写连接字符串
var strConn = _entityMetadataConnectionFactory.CreateAllForEntityMetadata();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update OptionSetValueMetadata set [name]=@name,[modifytime]=getutcdate()
where [id]=@id"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = metadata.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.NVarChar, 100)
{
Value = metadata.Name
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task DeletePrivilegeRelation(Guid roleId, Guid privilegeId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _privilegeManagementConnectionFactory.CreateAllForPrivilegeManagement(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"delete from RolePrivilegeRelation([roleid],[privilegeid])
value(@roleid,@privilegeid)"
})
{
SqlParameter parameter;
parameter = new SqlParameter("@roleid", SqlDbType.UniqueIdentifier)
{
Value = roleId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@privilegeid", SqlDbType.UniqueIdentifier)
{
Value = privilegeId
};
commond.Parameters.Add(parameter);
commond.Prepare();
await commond.ExecuteNonQueryAsync();
}
});
}
/// <summary>
/// 删除与系统登陆终结点的关联关系
/// </summary>
/// <param name="authorizationId"></param>
/// <param name="systemLoginEndpointId"></param>
/// <returns></returns>
public async Task DeleteSystemLoginEndpointRelation(Guid authorizationId, Guid systemLoginEndpointId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionFactory.CreateAllForSystemToken(), 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 = @"delete [dbo].[SystemLoginEndpointAuthorizationEndpointRelation]
where [systemloginendpointid] = @systemloginendpointid
and [authorizationendpointid] = @authorizationendpointid";
parameter = new SqlParameter("@systemloginendpointid", SqlDbType.UniqueIdentifier)
{
Value = systemLoginEndpointId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@authorizationendpointid", SqlDbType.UniqueIdentifier)
{
Value = authorizationId
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
await command.ExecuteNonQueryAsync();
}
});
}
public async Task DeleteGroupRelation(Guid actionId, Guid groupId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _businessSecurityRuleConnectionFactory.CreateAllForBusinessSecurityRule(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"delete from BusinessActionGroupRelation WITH (SNAPSHOT) where [groupid]=@groupid and [actionid]=@actionid"
})
{
SqlParameter parameter;
parameter = new SqlParameter("@groupid", SqlDbType.UniqueIdentifier)
{
Value = groupId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@actionid", SqlDbType.UniqueIdentifier)
{
Value = actionId
};
commond.Parameters.Add(parameter);
commond.Prepare();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task AddRetry(SQueue queue, Guid id, string exceptionMessage)
{
//根据存储类型和服务器名称获取连接字符串
var strConn = _messageQueueConnectionFactory.CreateAllForMessageQueue(queue.StoreType, queue.ServerName);
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"update {0} set [retrynumber]=case when [retrynumber]>=10000 then 10000 else [retrynumber]+1 end,[exceptionmessage]=@exceptionmessage where [id]=@id", queue.Name)
})
{
SqlParameter parameter;
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = id
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@exceptionmessage", SqlDbType.NVarChar, exceptionMessage.Length)
{
Value = exceptionMessage
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task UpdateContent(SystemConfiguration configuration)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionMainFactory.CreateAllForSystemConfiguration(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update systemconfiguration set [content]=@content,[modifytime]=getutcdate()
where [id]=@id"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = configuration.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@content", SqlDbType.NVarChar, 1000)
{
Value = configuration.Content
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task Update(PrivilegeSystem system)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _privilegeManagementConnectionFactory.CreateAllForPrivilegeManagement(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update PrivilegeSystem set [name]=@name,[modifytime]=getutcdate()
where [id]=@id"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = system.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.NVarChar, 150)
{
Value = system.Name
};
commond.Parameters.Add(parameter);
commond.Prepare();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task DeleteByRelation(Guid groupId, Guid id)
{
//获取读写连接字符串
var strConn = _hashConnectionFactory.CreateAllForHash();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
CommandText = @"
delete HashRealNode
where n.id = @id and groupid = @groupId"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = id
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@groupid", SqlDbType.UniqueIdentifier)
{
Value = groupId
};
command.Parameters.Add(parameter);
command.Prepare();
await command.ExecuteNonQueryAsync();
}
});
}
public async Task Delete(Guid systemOperationId, Guid whitelistId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionFactory.CreateAllForWhitelistPolicy(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"delete from systemoperationwhiteListrelation
where systemoperationid=@systemoperationid and whitelistid=@whitelistid"
})
{
var parameter = new SqlParameter("@systemoperationid", SqlDbType.UniqueIdentifier)
{
Value = systemOperationId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@whitelistid", SqlDbType.UniqueIdentifier)
{
Value = whitelistId
};
commond.Parameters.Add(parameter);
commond.Prepare();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task Update(BusinessActionGroup group)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _businessSecurityRuleConnectionFactory.CreateReadForBusinessSecurityRule(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update BusinessActionGroup WITH (SNAPSHOT) set [name]=@name,[modifytime]=getutcdate()
where [id]=@id"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = group.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.NVarChar, 150)
{
Value = group.Name
};
commond.Parameters.Add(parameter);
commond.Prepare();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task DeleteByTypeRelation(Guid typeId, Guid listenerId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForMessageQueueMain(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"delete from SMessageTypeListener where [id]=@id and [messagetypeid]=@messagetypeid"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = listenerId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@typeid", SqlDbType.UniqueIdentifier)
{
Value = typeId
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task Update(SQueueProcessGroup group)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForMessageQueueMain(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update SQueueProcessGroup set [name]=@name,[modifytime]=getutcdate()
where [id]=@id"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = group.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = group.Name
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task Delete(Guid id)
{
//获取读写连接字符串
var strConn = _hashConnectionFactory.CreateAllForHash();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @" delete from
[dbo].[HashGroup]
where [id]=@id"
})
{
SqlParameter parameter;
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = id
};
commond.Parameters.Add(parameter);
commond.Prepare();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task DeleteBySourceID(string sourceInfo, Guid infoID)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionFactory.CreateAllForStorge(), 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 = @"delete from MultipartStorgeInfo where [id]=@id and [sourceinfo]=@sourceinfo";
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = infoID
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@sourceinfo", SqlDbType.NVarChar, 500)
{
Value = sourceInfo
};
command.Parameters.Add(parameter);
await command.PrepareAsync();
await command.ExecuteNonQueryAsync();
}
});
}
/// <summary>
/// 增加动作与组的关联关系
/// </summary>
/// <param name="actionId"></param>
/// <param name="queueId"></param>
/// <returns></returns>
public async Task AddActionGroupRelation(Guid actionId, Guid queueId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionFactory.CreateAllForSchedule(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand command = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"UPDATE [dbo].[ScheduleAction]
SET [groupid]=@groupid,[modifytime] = GETUTCDATE()
WHERE id=@id AND [groupid] IS NULL"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = actionId
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@groupid", SqlDbType.UniqueIdentifier)
{
Value = queueId
};
command.Parameters.Add(parameter);
command.Prepare();
await command.ExecuteNonQueryAsync();
}
});
}
public async Task DeleteProcessGroupRelation(Guid processGroupId, Guid queueId)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForMessageQueueMain(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update SQueue set processgroupid=null where [id]=@id and processgroupid=@processgroupid"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = queueId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@processgroupid", SqlDbType.UniqueIdentifier)
{
Value = processGroupId
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task Add(SerialNumberRecord record)
{
record.Value = 1;
var storeInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, record.Prefix);
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SerialNumber, out string tableName))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName, HashEntityNames.SerialNumber
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, storeInfo.DBConnectionNames.ReadAndWrite, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (record.ID == Guid.Empty)
{
commond.CommandText = string.Format(@"insert into {0}([id],[prefix],[value],[createtime],[modifytime])
values(default,@prefix,1,getutcdate(),getutcdate());
select @newid=[id] from {0} where [sequence]=SCOPE_IDENTITY()", tableName);
}
else
{
commond.CommandText = string.Format(@"insert into {0}([id],[prefix],[value],[createtime],[modifytime])
values(@id,@prefix,1,getutcdate(),getutcdate())", tableName);
}
SqlParameter parameter;
if (record.ID != Guid.Empty)
{
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = record.ID
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
commond.Parameters.Add(parameter);
}
parameter = new SqlParameter("@prefix", SqlDbType.NVarChar, 1000)
{
Value = record.Prefix
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
try
{
await commond.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
if (ex.Number == 2601)
{
var fragment = new TextFragment()
{
Code = TextCodes.ExistFoundSerialNumberRecordByPrefix,
DefaultFormatting = "前缀为{0}的序列号记录已经存在",
ReplaceParameters = new List <object>()
{
record.Prefix
}
};
throw new UtilityException((int)Errors.ExistFoundSerialNumberRecordByPrefix, fragment);
}
else
{
throw;
}
}
if (record.ID == Guid.Empty)
{
record.ID = (Guid)commond.Parameters["@newid"].Value;
}
}
});
}
public async Task Lock(DBConnectionNames connNames, string lockName, int timeout)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionFactory.CreateAllForApplicationLock(connNames, lockName),
async (conn, transaction) =>
{
string strTimeout;
if (timeout == -1)
{
strTimeout = "null";
}
else
{
strTimeout = timeout.ToString();
}
string strSql = string.Format(@"declare @result int,@resource nvarchar(300),@timeout int,@message nvarchar(300)
set @result = 0;
set @resource='{0}';
set @timeout={1}
begin
if @@TRANCOUNT>0
begin
if @timeout is null
EXEC @result = sp_getapplock @Resource = @resource,
@LockMode = 'Exclusive'
else
EXEC @result = sp_getapplock @Resource = @resource,
@LockMode = 'Exclusive',@LockTimeout=@timeout
end
else
begin
if @timeout is null
EXEC @result = sp_getapplock @Resource = @resource,
@LockMode = 'Exclusive',@LockOwner='Session'
else
EXEC @result = sp_getapplock @Resource = @resource,
@LockMode = 'Exclusive',@LockOwner='Session',@LockTimeout=@timeout
end
if @result<0
begin
set @message=N'applock加锁失败，失败码:'+convert(nvarchar(20),@result)+',详细信息：'+ERROR_MESSAGE();
throw 50001,@message,1
end
end
", lockName, strTimeout);
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand())
{
command.CommandTimeout = 300;
command.Connection = (SqlConnection)conn;
command.CommandType = CommandType.Text;
command.CommandText = strSql;
command.Transaction = sqlTran;
await command.PrepareAsync();
try
{
await command.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
if (ex.Number == 50001)
{
var fragment = new TextFragment()
{
Code = TextCodes.ExistLicationLock,
DefaultFormatting = "当前请求{0}已被锁定",
ReplaceParameters = new List <object>()
{
lockName
}
};
throw new UtilityException((int)Errors.ExistLicationLock, fragment);
}
else
{
throw;
}
}
}
}
);
}
public async Task <SerialNumberRecord> QueryByPrefix(string prefix)
{
SerialNumberRecord record = null;
var storeInfo = await StoreInfoHelper.GetHashStoreInfo(_storeInfoResolveService, _hashGroupRepository, _hashGroupName, prefix);
if (!storeInfo.TableNames.TryGetValue(HashEntityNames.SerialNumber, out string tableName))
{
var fragment = new TextFragment()
{
Code = TextCodes.NotFoundKeyInHashNodeKeyInfo,
DefaultFormatting = "哈希组{0}中的哈希节点关键信息中找不到键值{1}",
ReplaceParameters = new List <object>()
{
_hashGroupName, HashEntityNames.SerialNumber
}
};
throw new UtilityException((int)Errors.NotFoundKeyInHashNodeKeyInfo, fragment);
}
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, storeInfo.DBConnectionNames.Read, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"select {0} from {1} where [prefix]=@prefix", StoreHelper.GetSerialNumberRecordSelectFields(string.Empty), tableName)
})
{
var parameter = new SqlParameter("@prefix", SqlDbType.NVarChar, 1000)
{
Value = prefix
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
record = new SerialNumberRecord();
StoreHelper.SetSerialNumberRecordSelectFields(record, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(record);
}
public async Task UpdateByTypeRelation(SMessageTypeListener listener)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForMessageQueueMain(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = @"update SMessageTypeListener set [name]=@name,[mode]=@mode,[listenerfactorytype]=@listenerfactorytype,[listenerfactorytypeusedi]=@listenerfactorytypeusedi,[listenerweburl]=@listenerweburl,[listenerwebsignature]=@listenerwebsignature where [id]=@id and messagetypeid=@messagetypeid"
})
{
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = listener.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@messagetypeid", SqlDbType.UniqueIdentifier)
{
Value = listener.MessageType.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = listener.Name
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@mode", SqlDbType.Int)
{
Value = listener.Mode
};
commond.Parameters.Add(parameter);
if (listener.ListenerFactoryType != null)
{
parameter = new SqlParameter("@listenerfactorytype", SqlDbType.VarChar, 150)
{
Value = listener.ListenerFactoryType
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerfactorytype", SqlDbType.VarChar, 150)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
if (listener.ListenerFactoryTypeUseDI != null)
{
parameter = new SqlParameter("@listenerfactorytypeusedi", SqlDbType.Bit)
{
Value = listener.ListenerFactoryTypeUseDI
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerfactorytypeusedi", SqlDbType.Bit)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
if (listener.ListenerWebUrl != null)
{
parameter = new SqlParameter("@listenerweburl", SqlDbType.VarChar, 200)
{
Value = listener.ListenerWebUrl
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerweburl", SqlDbType.VarChar, 200)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
if (listener.ListenerWebSignature != null)
{
parameter = new SqlParameter("@listenerwebsignature", SqlDbType.VarChar, 150)
{
Value = listener.ListenerWebSignature
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerwebsignature", SqlDbType.VarChar, 150)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
}
});
}
public async Task UnLock(DBConnectionNames connNames, string lockName)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _dbConnectionFactory.CreateAllForApplicationLock(connNames, lockName),
async (conn, transaction) =>
{
string strSql = string.Format(@"declare @result int,@resource nvarchar(300),@message nvarchar(300)
set @result = 0;
set @resource='{0}';
begin
begin try
EXEC @result = sp_releaseapplock @Resource = @resource
end try
begin catch
end catch
if @result<0
begin
set @message=N'applock解锁失败，失败码:'+convert(nvarchar(20),@result)+N',详细信息：'+ERROR_MESSAGE();
throw 50001,@message,1
end
end
", lockName);
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand command = new SqlCommand())
{
command.CommandTimeout = 300;
command.Connection = (SqlConnection)conn;
command.CommandType = CommandType.Text;
command.CommandText = strSql;
command.Transaction = sqlTran;
await command.PrepareAsync();
try
{
await command.ExecuteNonQueryAsync();
}
catch (SqlException ex)
{
if (ex.Number == 50001)
{
var fragment = new TextFragment()
{
Code = TextCodes.ExistLicationLock,
DefaultFormatting = "当前请求{0}已被锁定",
ReplaceParameters = new List <object>()
{
lockName
}
};
throw new UtilityException((int)Errors.ExistLicationLock, fragment);
}
else
{
throw;
}
}
}
}
);
}
public async Task QueryByType(Guid typeId, Func <SMessageTypeListener, Task> callback)
{
List <SMessageTypeListener> listenerList = new List <SMessageTypeListener>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _messageQueueConnectionFactory.CreateReadForMessageQueueMain(), async (conn, transaction) =>
{
Int64?sequence = null;
int pageSize = 500;
while (true)
{
listenerList.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (!sequence.HasValue)
{
commond.CommandText = string.Format(@"select top (@pagesize) {0},{1} from SMessageTypeListener as listener join SMessageExecuteType as mtype on listener.messagetypeid=mtype.id where mtype.id=@typeid order by listener.[sequence]", StoreHelper.GetSMessageTypeListenerSelectFields("listener"), StoreHelper.GetSMessageExecuteTypeSelectFields("mtype"));
}
else
{
commond.CommandText = string.Format(@"select top (@pagesize) {0},{1} from SMessageTypeListener as listener join SMessageExecuteType as mtype on listener.messagetypeid=mtype.id where mtype.id=@typeid and listener.[sequence]>@sequence order by listener.[sequence]", StoreHelper.GetSMessageTypeListenerSelectFields("listener"), StoreHelper.GetSMessageExecuteTypeSelectFields("mtype"));
}
var parameter = new SqlParameter("@typeid", SqlDbType.UniqueIdentifier)
{
Value = typeId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
commond.Parameters.Add(parameter);
if (sequence.HasValue)
{
parameter = new SqlParameter("@sequence", SqlDbType.BigInt)
{
Value = sequence
};
commond.Parameters.Add(parameter);
}
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var listener = new SMessageTypeListener();
StoreHelper.SetSMessageTypeListenerSelectFields(listener, reader, "listener");
sequence = (Int64)reader["listenersequence"];
listener.MessageType = new SMessageExecuteType();
StoreHelper.SetSMessageExecuteTypeSelectFields(listener.MessageType, reader, "mtype");
listenerList.Add(listener);
}
await reader.CloseAsync();
}
}
foreach (var listenerItem in listenerList)
{
await callback(listenerItem);
}
if (listenerList.Count != pageSize)
{
break;
}
}
});
}
public async Task <QueryResult <SMessageTypeListener> > QueryByType(Guid typeId, int page, int pageSize)
{
QueryResult <SMessageTypeListener> result = new QueryResult <SMessageTypeListener>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _messageQueueConnectionFactory.CreateReadForMessageQueueMain(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"set @currentpage=@page
select @count= count(*) from SMessageTypeListener where messagetypeid=@typeid
if @pagesize*@page>=@count
begin
set @currentpage= @count/@pagesize
if @count%@pagesize<>0
begin
set @currentpage=@currentpage+1
end
if @currentpage=0
set @currentpage=1
end
else if @page<1
begin
set @currentpage=1
end
select {0},{1} from SMessageTypeListener as listener join SMessageExecuteType as mtype where listener.messagetypeid=@typeid
order by listener.[sequence]
offset (@pagesize * (@currentpage - 1)) rows
fetch next @pagesize rows only;" , StoreHelper.GetSMessageTypeListenerSelectFields("listener"), StoreHelper.GetSMessageExecuteTypeSelectFields("mtype"))
})
{
var parameter = new SqlParameter("@page", SqlDbType.Int)
{
Value = page
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@typeid", SqlDbType.UniqueIdentifier)
{
Value = typeId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@currentpage", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var listener = new SMessageTypeListener();
StoreHelper.SetSMessageTypeListenerSelectFields(listener, reader, "listener");
listener.MessageType = new SMessageExecuteType();
StoreHelper.SetSMessageExecuteTypeSelectFields(listener.MessageType, reader, "mtype");
result.Results.Add(listener);
}
await reader.CloseAsync();
result.TotalCount = (int)commond.Parameters["@count"].Value;
result.CurrentPage = (int)commond.Parameters["@currentpage"].Value;
}
}
});
return(result);
}
public async Task Add(SMessageTypeListener listener)
{
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, _messageQueueConnectionFactory.CreateAllForMessageQueueMain(), async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (listener.ID == Guid.Empty)
{
commond.CommandText = @"insert into SMessageTypeListener([id],[messagetypeid],[name],[queuegroupname],[mode],[listenerfactorytype],[listenerfactorytypeusedi],[listenerweburl],[listenerwebsignature],[createtime],[modifytime])
values(default,@messagetypeid,@name,@queuegroupname,@mode,@listenerfactorytype,@listenerfactorytypeusedi,@listenerweburl,@listenerwebsignature,getutcdate(),getutcdate());
select @newid=[id] from SMessageTypeListener where [sequence]=SCOPE_IDENTITY()";
}
else
{
commond.CommandText = @"insert into SMessageTypeListener([id],[messagetypeid],[name],[queuegroupname],[mode],[listenerfactorytype],[listenerfactorytypeusedi],[listenerweburl],[listenerwebsignature],[createtime],[modifytime])
values(@id,@messagetypeid,@name,@queuegroupname,@mode,@listenerfactorytype,@listenerfactorytypeusedi,@listenerweburl,@listenerwebsignature,getutcdate(),getutcdate())";
}
SqlParameter parameter;
if (listener.ID != Guid.Empty)
{
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = listener.ID
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
commond.Parameters.Add(parameter);
}
parameter = new SqlParameter("@messagetypeid", SqlDbType.UniqueIdentifier)
{
Value = listener.MessageType.ID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = listener.Name
};
commond.Parameters.Add(parameter);
if (listener.QueueGroupName != null)
{
parameter = new SqlParameter("@queuegroupname", SqlDbType.VarChar, 150)
{
Value = listener.Name
};
}
else
{
parameter = new SqlParameter("@queuegroupname", SqlDbType.VarChar, 150)
{
Value = DBNull.Value
};
}
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@mode", SqlDbType.Int)
{
Value = listener.Mode
};
commond.Parameters.Add(parameter);
if (listener.ListenerFactoryType != null)
{
parameter = new SqlParameter("@listenerfactorytype", SqlDbType.VarChar, 150)
{
Value = listener.ListenerFactoryType
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerfactorytype", SqlDbType.VarChar, 150)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
if (listener.ListenerFactoryTypeUseDI != null)
{
parameter = new SqlParameter("@listenerfactorytypeusedi", SqlDbType.Bit)
{
Value = listener.ListenerFactoryTypeUseDI
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerfactorytypeusedi", SqlDbType.Bit)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
if (listener.ListenerWebUrl != null)
{
parameter = new SqlParameter("@listenerweburl", SqlDbType.VarChar, 200)
{
Value = listener.ListenerWebUrl
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerweburl", SqlDbType.VarChar, 200)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
if (listener.ListenerWebSignature != null)
{
parameter = new SqlParameter("@listenerwebsignature", SqlDbType.VarChar, 150)
{
Value = listener.ListenerWebSignature
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@listenerwebsignature", SqlDbType.VarChar, 150)
{
Value = DBNull.Value
};
commond.Parameters.Add(parameter);
}
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
if (listener.ID == Guid.Empty)
{
listener.ID = (Guid)commond.Parameters["@newid"].Value;
}
}
});
}
public async Task QueryByRole(Guid roleId, Func <Privilege, Task> callback)
{
List <Privilege> queueList = new List <Privilege>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _privilegeManagementConnectionFactory.CreateReadForPrivilegeManagement(), async (conn, transaction) =>
{
Int64?sequence = null;
int pageSize = 500;
while (true)
{
queueList.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (!sequence.HasValue)
{
commond.CommandText = string.Format(@"select top (@pagesize) {0} from [Privilege] as p join [dbo].[RolePrivilegeRelation] as rpr
on p.id=rpr.privilegeid
where rpr.roleid=@roleid order by p.[sequence]", StoreHelper.GetPrivilegeSelectFields("p"));
}
else
{
commond.CommandText = string.Format(@"select top (@pagesize) {0} from [Privilege] as p join [dbo].[RolePrivilegeRelation] as rpr
on p.id=rpr.privilegeid
where rpr.roleid=@roleid and p.[sequence]>@sequence order by p.[sequence]", StoreHelper.GetPrivilegeSelectFields("p"));
}
var parameter = new SqlParameter("@roleid", SqlDbType.UniqueIdentifier)
{
Value = roleId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
commond.Parameters.Add(parameter);
if (sequence.HasValue)
{
parameter = new SqlParameter("@sequence", SqlDbType.BigInt)
{
Value = sequence
};
commond.Parameters.Add(parameter);
}
commond.Prepare();
SqlDataReader reader = null;
using (reader = await commond.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var privilege = new Privilege();
StoreHelper.SetPrivilegeSelectFields(privilege, reader, "p");
sequence = (Int64)reader["psequence"];
queueList.Add(privilege);
}
reader.Close();
}
}
foreach (var queueItem in queueList)
{
await callback(queueItem);
}
if (queueList.Count != pageSize)
{
break;
}
}
});
}
/// <summary>
/// 根据实体元数据ID查询该实体元数据关联的全部记录
/// </summary>
/// <param name="infoId"></param>
/// <param name="callback"></param>
/// <returns></returns>
public async Task QueryAllByEntityInfoId(Guid infoId, Func <EntityInfoKeyRelation, Task> callback)
{
List <EntityInfoKeyRelation> itemList = new List <EntityInfoKeyRelation>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _entityMetadataConnectionFactory.CreateReadForEntityMetadata(), async (conn, transaction) =>
{
int?sequence = null;
int pageSize = 500;
while (true)
{
itemList.Clear();
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (!sequence.HasValue)
{
commond.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2}
FROM [EntityInfoKeyRelation] AS keyrelation INNER JOIN [EntityInfo] AS info ON keyrelation.[entityinfoid] = info.[id]
INNER JOIN [EntityAttributeInfo] AS attribute ON keyrelation.[entityattributeinfoid] = attribute.[id]
WHERE info.[id] = @id ORDER BY keyrelation.[order];",
StoreHelper.GetEntityInfoKeyRelationSelectFields("keyrelation"),
StoreHelper.GetEntityInfoSelectFields("info"),
StoreHelper.GetEntityAttributeInfoSelectFields("attribute"));
}
else
{
commond.CommandText = string.Format(@"SELECT TOP (@pagesize) {0},{1},{2}
FROM [EntityInfoKeyRelation] AS keyrelation INNER JOIN [EntityInfo] AS info ON keyrelation.[entityinfoid] = info.[id]
INNER JOIN [EntityAttributeInfo] AS attribute ON keyrelation.[entityattributeinfoid] = attribute.[id]
WHERE info.[id] = @id AND keyrelation.[order] > @sequence ORDER BY keyrelation.[order];",
StoreHelper.GetEntityInfoKeyRelationSelectFields("keyrelation"),
StoreHelper.GetEntityInfoSelectFields("info"),
StoreHelper.GetEntityAttributeInfoSelectFields("attribute"));
}
var parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = infoId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
commond.Parameters.Add(parameter);
if (sequence.HasValue)
{
parameter = new SqlParameter("@sequence", SqlDbType.BigInt)
{
Value = sequence
};
commond.Parameters.Add(parameter);
}
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var item = new EntityInfoKeyRelation();
StoreHelper.SetEntityInfoKeyRelationSelectFields(item, reader, "keyrelation");
sequence = (int)reader["keyrelationorder"];
item.EntityInfo = new EntityInfo();
StoreHelper.SetEntityInfoSelectFields(item.EntityInfo, reader, "info");
item.EntityAttributeInfo = new EntityAttributeInfo();
StoreHelper.SetEntityAttributeInfoSelectFields(item.EntityAttributeInfo, reader, "attribute");
itemList.Add(item);
}
await reader.CloseAsync();
}
}
foreach (var item in itemList)
{
await callback(item);
}
if (itemList.Count != pageSize)
{
break;
}
}
});
}
public async Task Add(HttpClaimGenerator generator)
{
//获取读写连接字符串
var strConn = _contextConnectionFactory.CreateAllForContext();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, false, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran
})
{
if (generator.ID == Guid.Empty)
{
commond.CommandText = @"insert into httpclaimgenerator([id],[name],[type],[createtime],[modifytime])
values(default,@name,@tyep,getutcdate(),getutcdate());
select @newid=[id] from httpclaimgenerator where [sequence]=SCOPE_IDENTITY()";
}
else
{
commond.CommandText = @"insert into httpclaimgenerator([id],[name],[type],[createtime],[modifytime])
values(@id,@name,@type,getutcdate(),getutcdate())";
}
SqlParameter parameter;
if (generator.ID != Guid.Empty)
{
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = generator.ID
};
commond.Parameters.Add(parameter);
}
else
{
parameter = new SqlParameter("@newid", SqlDbType.UniqueIdentifier)
{
Direction = ParameterDirection.Output
};
commond.Parameters.Add(parameter);
}
parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = generator.Name
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@type", SqlDbType.VarChar, 150)
{
Value = generator.Type
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
await commond.ExecuteNonQueryAsync();
if (generator.ID == Guid.Empty)
{
generator.ID = (Guid)commond.Parameters["@newid"].Value;
}
}
});
}
public async Task <QueryResult <HttpClaimGenerator> > QueryByPage(string name, int page, int pageSize)
{
QueryResult <HttpClaimGenerator> result = new QueryResult <HttpClaimGenerator>();
//获取只读连接字符串
var strConn = _contextConnectionFactory.CreateReadForContext();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, strConn, async (conn, transaction) =>
{
SqlTransaction sqlTran = null;
if (transaction != null)
{
sqlTran = (SqlTransaction)transaction;
}
await using (SqlCommand commond = new SqlCommand()
{
Connection = (SqlConnection)conn,
CommandType = CommandType.Text,
Transaction = sqlTran,
CommandText = string.Format(@"
select @count= count(*) from HttpClaimGenerator where [name] like @name
select {0} from HttpClaimGenerator where [name] like @name
order by sequence
offset (@pagesize * (@page - 1)) rows
fetch next @pagesize rows only;" , StoreHelper.GetHttpClaimGeneratorSelectFields(string.Empty))
})
{
var parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = $"{name.ToSqlLike()}%"
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@page", SqlDbType.Int)
{
Value = page
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@pagesize", SqlDbType.Int)
{
Value = pageSize
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var generator = new HttpClaimGenerator();
StoreHelper.SetHttpClaimGeneratorSelectFields(generator, reader, string.Empty);
result.Results.Add(generator);
}
await reader.CloseAsync();
result.TotalCount = (int)commond.Parameters["@count"].Value;
result.CurrentPage = page;
}
}
});
return(result);
}