public async Task <OptionSetValueItem> QueryByValue(Guid optionSetValueId, string stringValue)
{
OptionSetValueItem item = null;
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
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 OptionSetValueItem as item join OptionSetValueMetadata as metadata on item.optionsetvalueid=metadata.id where metadata.id=@optionsetvalueid and item.[stringvalue]=@stringvalue", StoreHelper.GetOptionSetValueItemSelectFields("item"), StoreHelper.GetOptionSetValueMetadataSelectFields("metadata"))
})
{
var parameter = new SqlParameter("@optionsetvalueid", SqlDbType.UniqueIdentifier)
{
Value = optionSetValueId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@stringvalue", SqlDbType.NVarChar, 100)
{
Value = stringValue
};
commond.Parameters.Add(parameter);
commond.Prepare();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
item = new OptionSetValueItem();
StoreHelper.SetOptionSetValueItemSelectFields(item, reader, "item");
item.OptionSetValue = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(item.OptionSetValue, reader, "metadata");
}
await reader.CloseAsync();
}
}
});
return(item);
}
public async Task QueryChildAll(Guid metadataId, Guid childMetadataId, Guid id, Func <OptionSetValueItem, Task> callback)
{
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
List <OptionSetValueItem> itemList = new List <OptionSetValueItem>();
await DBTransactionHelper.SqlTransactionWorkAsync(DBTypes.SqlServer, true, false, strConn, async (conn, transaction) =>
{
Int64?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} from OptionSetValueItem as item join OptionSetValueMetadata as metadata on item.optionsetvalueid=metadata.id join OptionSetValueItemRelation as itemR on item.id=itemR.fatherid join OptionSetValueItem as childItem on itemR.childid=childItem.id join OptionSetValueMetadataRelation as metadataR on metadata.id=metadataR.fatherid join OptionSetValueMetadata as childmetadata on metadataR.childid=childmetadata.id join OptionSetValueMetadata as childitemmetadata on childitemmetadata.id=childitem.optionsetvalueid where metadata.id=@metadataid and childitemmetadata.id=@childmetadataid and item.id=@id and childitemmetadata.id=childmetadata.id order by childItem.[sequence]", StoreHelper.GetOptionSetValueItemSelectFields("childItem"), StoreHelper.GetOptionSetValueMetadataSelectFields("childitemmetadata"));
}
else
{
commond.CommandText = string.Format(@"select top (@pagesize) {0},{1} from OptionSetValueItem as item join OptionSetValueMetadata as metadata on item.optionsetvalueid=metadata.id join OptionSetValueItemRelation as itemR on item.id=itemR.fatherid join OptionSetValueItem as childItem on itemR.childid=childItem.id join OptionSetValueMetadataRelation as metadataR on metadata.id=metadataR.fatherid join OptionSetValueMetadata as childmetadata on metadataR.childid=childmetadata.id join OptionSetValueMetadata as childitemmetadata on childitemmetadata.id=childitem.optionsetvalueid where metadata.id=@metadataid and childitemmetadata.id=@childmetadataid and item.id=@id and childitemmetadata.id=childmetadata.id and childItem.[sequence]>@sequence order by childItem.[sequence]", StoreHelper.GetOptionSetValueItemSelectFields("childItem"), StoreHelper.GetOptionSetValueMetadataSelectFields("childitemmetadata"));
}
var parameter = new SqlParameter("@metadataid", SqlDbType.UniqueIdentifier)
{
Value = metadataId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@childmetadataid", SqlDbType.UniqueIdentifier)
{
Value = childMetadataId
};
commond.Parameters.Add(parameter);
parameter = new SqlParameter("@id", SqlDbType.UniqueIdentifier)
{
Value = id
};
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 OptionSetValueItem();
StoreHelper.SetOptionSetValueItemSelectFields(item, reader, "childItem");
sequence = (Int64)reader["childitemsequence"];
item.OptionSetValue = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(item.OptionSetValue, reader, "childitemmetadata");
itemList.Add(item);
}
await reader.CloseAsync();
}
}
foreach (var item in itemList)
{
await callback(item);
}
if (itemList.Count != pageSize)
{
break;
}
}
});
}
public async Task <OptionSetValueMetadata> QueryByName(string name)
{
OptionSetValueMetadata metadata = null;
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
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} from OptionSetValueMetadata where [name]=@name", StoreHelper.GetOptionSetValueMetadataSelectFields(string.Empty))
})
{
var parameter = new SqlParameter("@name", SqlDbType.NVarChar, 100)
{
Value = name
};
commond.Parameters.Add(parameter);
await commond.PrepareAsync();
SqlDataReader reader = null;
await using (reader = await commond.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
metadata = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(metadata, reader, string.Empty);
}
await reader.CloseAsync();
}
}
});
return(metadata);
}
public async Task <QueryResult <OptionSetValueItem> > QueryAll(Guid optionSetValueId, int page, int pageSize)
{
QueryResult <OptionSetValueItem> result = new QueryResult <OptionSetValueItem>();
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
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(@"set @currentpage=@page
select @count= count(*) from OptionSetValueItem as item join OptionSetValueMetadata as metadata on item.optionsetvalueid=metadata.id where metadata.id=@optionsetvalueid
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 OptionSetValueItem as item join OptionSetValueMetadata as metadata on item.optionsetvalueid=metadata.id where metadata.id=@optionsetvalueid
order by sequence
offset (@pagesize * (@currentpage - 1)) rows
fetch next @pagesize rows only;" , StoreHelper.GetOptionSetValueItemSelectFields("item"), StoreHelper.GetOptionSetValueMetadataSelectFields("metadata"))
})
{
var parameter = new SqlParameter("@optionsetvalueid", SqlDbType.UniqueIdentifier)
{
Value = optionSetValueId
};
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);
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 item = new OptionSetValueItem();
StoreHelper.SetOptionSetValueItemSelectFields(item, reader, "item");
item.OptionSetValue = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(item.OptionSetValue, reader, "metadata");
result.Results.Add(item);
}
await reader.CloseAsync();
result.TotalCount = (int)commond.Parameters["@count"].Value;
result.CurrentPage = (int)commond.Parameters["@currentpage"].Value;
}
}
});
return(result);
}
public async Task <QueryResult <OptionSetValueMetadata> > QueryByName(string name, int page, int pageSize)
{
QueryResult <OptionSetValueMetadata> result = new QueryResult <OptionSetValueMetadata>();
//获取只读连接字符串
var strConn = _entityMetadataConnectionFactory.CreateReadForEntityMetadata();
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(@"set @currentpage=@page
select @count= count(*) from OptionSetValueMetadata where [name] like @name
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} from OptionSetValueMetadata where [name] like @name
order by sequence
offset (@pagesize * (@currentpage - 1)) rows
fetch next @pagesize rows only;" , StoreHelper.GetOptionSetValueMetadataSelectFields(string.Empty))
})
{
var parameter = new SqlParameter("@name", SqlDbType.NVarChar, 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);
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 metadata = new OptionSetValueMetadata();
StoreHelper.SetOptionSetValueMetadataSelectFields(metadata, reader, string.Empty);
result.Results.Add(metadata);
}
await reader.CloseAsync();
result.TotalCount = (int)commond.Parameters["@count"].Value;
result.CurrentPage = (int)commond.Parameters["@currentpage"].Value;
}
}
});
return(result);
}