项目中查询数据和模糊查询
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
开发工具与关键技术：WPF 、Select
撰写日期：2020年08月07日
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
一、 在项目中要查询出数据要做四个步骤：1、先是搭建好页面、2、去数据库写存储过程、3、在服务端写对应的方法之后发布服务、4、最后到客户端写查询方法
- 首先来看页面搭建，如下：
<!--页面布局-->
<DockPanel>
<DockPanel DockPanel.Dock="Top">
<!--三行-->
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="1*"></RowDefinition>
<RowDefinition Height="1*"></RowDefinition>
<RowDefinition Height="12*"></RowDefinition>
</Grid.RowDefinitions>
<!--菜单按钮-->
<WrapPanel Orientation="Horizontal"
HorizontalAlignment="Right">
<Button Content="添加新闻" Style="{StaticResource BtnInsertStyle}" Margin="2" Name="btnInsert" Click="btnInsert_Click"></Button>
<Button Content="修改新闻" Style="{StaticResource BtnUpdateStyle}" Margin="2" Name="btnUpdate" Click="btnUpdate_Click"></Button>
<Button Content="删除" Style="{StaticResource BtnDeleteStyle}" Margin="2" Name="btnDelete" Click="btnDelete_Click"></Button>
</WrapPanel>
<!--搜索栏-->
<WrapPanel Grid.Row="1" HorizontalAlignment="Center"
VerticalAlignment="Center">
<TextBlock Text="搜索：" Foreground="Black"></TextBlock>
<TextBox x:Name="txt_Select" ToolTip="请输入：新闻标题\新闻分类\发布人" Width="300" Height="23" Background="Transparent" SelectionChanged="txtSelect_SelectionChanged" HorizontalAlignment="Left" VerticalAlignment="Center"></TextBox>
</WrapPanel>
<!--表格数据-->
<Grid Grid.Row="2" VerticalAlignment="Stretch">
<GroupBox Header="新闻信息" Foreground="Black">
<DataGrid x:Name="dgNews" IsReadOnly="True" HorizontalAlignment="Stretch" AutoGenerateColumns="False" CanUserAddRows="False" CanUserReorderColumns="False" CanUserResizeColumns="False" CanUserResizeRows="False" SelectionMode="Single" AlternationCount="2" RowHeaderWidth="0" Background="White" SelectedCellsChanged="dgNews_SelectedCellsChanged">
<DataGrid.Columns>
<DataGridTextColumn Header="序号" x:Name="number" Binding="{Binding number}" Width="80"/>
<DataGridTextColumn Header="新闻类型ID" Binding="{Binding NewsTypeID}" Visibility="Collapsed" Width="10"/>
<DataGridTextColumn Header="新闻分类" Binding="{Binding NewsTypeName}" Width="150"/>
<DataGridTextColumn Header="新闻标题" Binding="{Binding NewsHeadline}" Width="150"/>
<DataGridTextColumn Header="发布人" Binding="{Binding PowerName}" Width="150"/>
<DataGridTextColumn Header="权限ID" Binding="{Binding PowerID}" Visibility="Collapsed" Width="10"/>
<DataGridTextColumn Header="来源" Binding="{Binding Sources}" Width="150"/>
<DataGridTextColumn Header="是否固顶" Binding="{Binding SolidRoof}" Width="150"/>
<DataGridTextColumn Header="是否推荐" Binding="{Binding Recommend}" Width="150"/>
<DataGridTextColumn Header="发布时间" Binding="{Binding ReleaseTime,StringFormat=\{0:yyyy-MM-dd HH:mm:ss\}}" Width="200"/>
<DataGridTextColumn Header="新闻内容" Binding="{Binding NewsContent}" Width="250"></DataGridTextColumn>
</DataGrid.Columns>
</DataGrid>
</GroupBox>
</Grid>
</Grid>
</DockPanel>
</DockPanel>
- 搭完就去数据库中写查询的存储过程，如下图：
3、把数据库查询的方法绑定到服务端中，刚开始写的时候必须要引入[ServiceContract]服务契约还有一个是操作契约 [OperationContract]，你没写一个方法都必须要写操作契约，如果没写就会报错
，如下：
//服务契约
[ServiceContract]
public class Journalism
{
// 实例化数据层
DAL.DALMethod myDALMethod = new DAL.DALMethod();
//操作契约
[OperationContract]
//查询新闻信息
public DataSet UserControl_Select_Journalism()
{
SqlParameter[] myJournalism = {
new SqlParameter("@type",SqlDbType.Char), };
myJournalism[0].Value = "UserControl_Select_Journalism";
DataSet myDataSet = myDALMethod.QueryDataSet("UC_Journalism", myJournalism);
return myDataSet; }
4、写好之后就去发布服务，再到客户端引用服务，最后写查询数据方式，如下：
引用布局变量（服务端的方法）
BLL.Journalism.JournalismClient myClient = new BLL.Journalism.JournalismClient();
DataTable dt;
接着就是查询数据了
//封装刷新页面数据查询
public void GetDataTable()
{
dt = myClient.UserControl_Select_Journalism().Tables[0];
dgNews.ItemsSource = dt.DefaultView; }
-
结果如下
- 模糊查询方法如下：
//搜索栏加载事件
private void txtSelect_SelectionChanged(object sender, RoutedEventArgs e)
{
//声明变量
string select = "";
//获取控件数据
string strSt = txt_Select.Text.Trim();
//判断不能为空
if (strSt != "") {
//模糊查询内容
select += "NewsHeadline like '%" + strSt + "%'" +
"or NewsTypeName like '%" + strSt + "%'" +
"or PowerName like '%" + strSt + "%'"; }
//获取表格全部数据
DataTable dtSelect = myClient.UserControl_Select_Journalism().Tables[0];
//实例化视图（把表格数据化为视图）
DataView dv = new DataView(dtSelect);
DataTable dt = new DataTable();
//判断添加不存在
if (select != ""){
//调用视图的筛选数据
dv.RowFilter = select;
//再把视图转化为表格
dt = dv.ToTable(); }
if (select == "") {
//查询全部数据
dt = dv.ToTable(); }
//绑定控件数据
dgNews.ItemsSource = dt.DefaultView;
}
结果图如下：