public async Task <StoreGroupMember> QueryByName(Guid groupID, string memberName)
{
StoreGroupMember member = null;
//获取只读连接字符串
var strConn = _storgeConnectionFactory.CreateReadForStorge();
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 {0},{1} from StoreGroupMember as m join StoreGroup as g on m.groupid=g.id where g.[id]=@groupid and m.[name]=@name", StoreHelper.GetStoreGroupMemberSelectFields("m"), StoreHelper.GetStoreGroupSelectFields("g"))
})
{
var parameter = new SqlParameter("@groupid", SqlDbType.UniqueIdentifier)
{
Value = groupID
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = memberName
};
commond.Parameters.Add(parameter);
commond.Prepare();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
member = new StoreGroupMember();
StoreHelper.SetStoreGroupMemberSelectFields(member, reader, "m");
member.Group = new StoreGroup();
StoreHelper.SetStoreGroupSelectFields(member.Group, reader, "g");
}
await reader.CloseAsync();
}
}
});
return(member);
}
public async Task <StoreGroup> QueryByName(string name)
{
StoreGroup group = null;
//获取只读连接字符串
var strConn = _storgeConnectionFactory.CreateReadForStorge();
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 {0} StoreGroup where [name]=@name", StoreHelper.GetStoreGroupSelectFields(string.Empty))
})
{
var parameter = new SqlParameter("@name", SqlDbType.VarChar, 150)
{
Value = name
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
group = new StoreGroup();
StoreHelper.SetStoreGroupSelectFields(group, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(group);
}
public async Task <QueryResult <MultipartStorgeInfo> > QueryByPage(string name, string displayName, string sourceInfo, string credentialInfo, int?status, int page, int pageSize)
{
string strStatusCondition = "1=1";
if (status == null)
{
strStatusCondition = "status=@status";
}
QueryResult <MultipartStorgeInfo> result = new QueryResult <MultipartStorgeInfo>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, _dbConnectionFactory.CreateReadForStorge(), 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(@"SET @currentpage = @page;
SELECT @count = COUNT(*)
FROM [dbo].[MultipartStorgeInfo]
WHERE name like @name and displayname like @displayname and {1} and sourceinfo=@sourceinfo and credentialinfo=@credentialinfo;
SELECT {0}
FROM [dbo].[MultipartStorgeInfo]
WHERE name like @name and displayname like @displayname and {1} and sourceinfo=@sourceinfo and credentialinfo=@credentialinfo
ORDER BY sequence OFFSET (@pagesize * (@currentpage - 1)) ROWS FETCH NEXT @pagesize ROWS ONLY;",
StoreHelper.GetMultipartStorgeInfoSelectFields(string.Empty), strStatusCondition),
Transaction = sqlTran
})
{
var parameter = new SqlParameter("@name", SqlDbType.NVarChar, 500)
{
Value = string.Format("{0}%", name.ToSqlLike())
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@displayname", SqlDbType.NVarChar, 500)
{
Value = string.Format("{0}%", displayName.ToSqlLike())
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@sourceinfo", SqlDbType.NVarChar, 500)
{
Value = string.Format("{0}%", sourceInfo.ToSqlLike())
};
command.Parameters.Add(parameter);
parameter = new SqlParameter("@credentialinfo", SqlDbType.NVarChar, 500)
{
Value = string.Format("{0}%", credentialInfo.ToSqlLike())
};
command.Parameters.Add(parameter);
if (status != null)
{
parameter = new SqlParameter("@status", SqlDbType.Int)
{
Value = status.Value
};
command.Parameters.Add(parameter);
}
parameter = new SqlParameter("@page", 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);
parameter = new SqlParameter("@currentpage", 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 info = new MultipartStorgeInfo();
StoreHelper.SetMultipartStorgeInfoSelectFields(info, reader, string.Empty);
result.Results.Add(info);
}
await reader.CloseAsync();
result.TotalCount = (int)command.Parameters["@count"].Value;
result.CurrentPage = (int)command.Parameters["@currentpage"].Value;
}
}
});
return(result);
}