C# sql存儲過程(Stored Procedure)分页查詢功能
来源：互联网 发布：数据对账 英文怎么说 编辑：程序博客网 时间：2024/05/24 02:19
前几天工作，用到了存储过程的分页，觉得存储过程还挺好用的,工作了这么久,也没做啥笔记,整天就写写代码，这段时间我会慢慢整理一些简单的知识点,之前一直只是看着别人的文章，也没想说自己写一个,现在发觉写写还能当笔记用,顺便也给自己做一下笔记,
sql server 存储过程代码
USE [數據表]GO/****** Object: StoredProcedure [dbo].[sp_EmpInfo_Date] Script Date: 04/18/2017 09:50:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[sp_EmpInfo_Date]@pageSize int, --一页显示几笔数据@pageIndex int, -- 第几页@dataCount int output, --总数据@pageCount int output, --总页数@EmpName varchar(30) --輸入名字ASBEGINSET NOCOUNT ON;declare @sqlMain varchar(max) = ' 'declare @sqlFilter varchar(max)=' ' declare @sqlMaster varchar(max)=' ' declare @dataCountSql nvarchar(max)=' ' if(@pageIndex < 2) set @pageIndex = 1 declare @mmin int = @pageSize*(@pageIndex -1)+1 declare @mmax int = @pageIndex*@pageSize set @sqlMain = ' select row_number() over(order by tmpid) as num,UserName,CreateTime from MYW..SysAccount where 1=1'if(@EmpName<> '') set @sqlFilter = ' and @EmpName = ' + @EmpName set @sqlMaster = ' select * from (' +@sqlMain + @sqlFilter +' ) as temp where num between '+ cast(@mmin as varchar)+' and ' + cast(@mmax as varchar)print(@sqlMaster)set @dataCountSql = 'select @dataCount=count(*) from ( ' +@sqlMain + @sqlFilter +' ) as temp 'exec sp_executesql @dataCountSql,N'@dataCount int out',@dataCount outset @pageCount = ceiling(1.0*@dataCount/@pageSize)print(@sqlMaster)exec (@sqlMaster)end set nocount off;
C# 代码调用存储过程
界面代码
<Window x:Class="Ceishi.Test" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="Test" Height="678" Width="665"> <Grid Height="427" Name="grid1" Width="549"> <Grid.ColumnDefinitions> <ColumnDefinition Width="268*" /> <ColumnDefinition Width="281*" /> </Grid.ColumnDefinitions> <DataGrid AutoGenerateColumns="False" Height="392" HorizontalAlignment="Left" Margin="21,12,0,0" Name="dgDetail" VerticalAlignment="Top" Width="516" BorderBrush="#FF2B2B2B" FontSize="14" FontWeight="Normal" ForceCursor="False" VerticalContentAlignment="Stretch" Grid.ColumnSpan="2"> <DataGrid.ColumnHeaderStyle> <Style TargetType="DataGridColumnHeader"> <Setter Property="Control.Background" Value="{StaticResource {x:Static SystemColors.GrayTextBrushKey}}" /> <Setter Property="Control.Foreground" Value="White" /> <Setter Property="Control.FontSize" Value="13" /> <Setter Property="FrameworkElement.Height" Value="30" /> <Setter Property="Control.HorizontalContentAlignment" Value="Center" /> <Setter Property="DataGridColumnHeader.SeparatorBrush" Value="White" /> <Setter Property="Control.BorderThickness" Value="0.5" /> <Setter Property="Control.BorderBrush" Value="Black" /> </Style> </DataGrid.ColumnHeaderStyle> <DataGrid.Columns> <DataGridTextColumn Header="序號" Binding="{Binding Path=num}"/> <DataGridTextColumn Binding="{Binding Path=EmpNo}" Header="工號"></DataGridTextColumn> <DataGridTextColumn Binding="{Binding Path=UserName}" Header="姓名"></DataGridTextColumn> </DataGrid.Columns> </DataGrid> <Label Content="共 0 筆" FontSize="14" Foreground="#FF2020E2" Height="28" Margin="-15,-51,0,0" Name="lblDataCount" VerticalAlignment="Top" HorizontalAlignment="Left" Width="87" /> <Label Content="第" Height="28" HorizontalAlignment="Left" Margin="103,-49,0,0" VerticalAlignment="Top" /> <TextBox Height="23" HorizontalAlignment="Left" HorizontalContentAlignment="Right" IsReadOnly="True" Margin="131,-47,0,0" Name="txtPage" Text="0" VerticalAlignment="Top" Width="40" /> <Label Content="/" HorizontalAlignment="Left" HorizontalContentAlignment="Right" Margin="168,-50,0,0" VerticalAlignment="Top" Width="23" /> <Label Content="0" FontSize="14" Foreground="#FF2020E2" Height="28" HorizontalAlignment="Left" Margin="197,-51,0,0" Name="lblPageCount" VerticalAlignment="Top" /> <Label Content="頁" Height="28" HorizontalAlignment="Left" Margin="0,-50,0,0" VerticalAlignment="Top" Grid.Column="1" /> <Button Content="GO" FontWeight="Bold" Grid.Column="1" Height="28" HorizontalAlignment="Left" IsEnabled="False" Margin="53,-48,0,0" Name="btnDump" VerticalAlignment="Top" Width="36" Click="btnDump_Click"/> <Image Grid.Column="1" Height="30" HorizontalAlignment="Left" Margin="110,-48,0,0" Name="tbFirst" Source="/Ceishi;component/Image/first.ico" VerticalAlignment="Top" MouseUp="tbFirst__MouseUp" /> <Image Grid.Column="1" Height="30" HorizontalAlignment="Left" Margin="150,-48,0,0" Name="tbUp" Source="/Ceishi;component/Image/previous.ico" VerticalAlignment="Top" MouseUp="tbUp__MouseUp" /> <Image Grid.Column="1" Height="30" HorizontalAlignment="Right" Margin="0,-48,61,0" Name="tbDown" Source="/Ceishi;component/Image/next.ico" VerticalAlignment="Top" MouseUp="tbDown__MouseUp"/> <Image Grid.Column="1" Height="30" HorizontalAlignment="Left" Margin="230,-48,0,0" Name="tbLast" Source="/Ceishi;component/Image/last.ico" VerticalAlignment="Top" MouseUp="tbLast__MouseUp"/> <TextBox Height="34" HorizontalAlignment="Left" Margin="21,-91,0,0" Name="txtEmpno" VerticalAlignment="Top" Width="183" FontSize="18"/> <Button Content="Button" Grid.ColumnSpan="2" Height="33" HorizontalAlignment="Left" Margin="231,-91,0,0" Name="button1" VerticalAlignment="Top" Width="75" Click="btnSearch_Click" /> </Grid></Window>// 定义 public int pageNum = 20; // 每一页显示多少数据 //当前是第几页 public int pIndex = 1; //最大页数 private int MaxIndex = 1; //一共多少条 private int allNum = 0;
访问数据库
public DataTable ExecQuery(string cmdText, CommandType ct, SqlParameter[] para, string[] returnValue) { DataTable lo_Dtb = new DataTable(); SqlDataReader lo_DBSdr = null; SqlCommand lo_DBCmd = null; SqlConnection lo_DBConn = null; string ConnStr = "server= 數據庫地址;uid=用戶名;pwd= 密碼;database=數據表"; try { lo_DBConn = new SqlConnection(ConnStr); lo_DBConn.Open(); lo_DBCmd = new SqlCommand(cmdText, lo_DBConn); lo_DBCmd.CommandType = ct; lo_DBCmd.Parameters.AddRange(para); lo_DBCmd.Parameters["@dataCount"].Direction = ParameterDirection.Output; lo_DBCmd.Parameters["@pageCount"].Direction = ParameterDirection.Output; using (lo_DBSdr = lo_DBCmd.ExecuteReader(CommandBehavior.CloseConnection)) { lo_Dtb.Load(lo_DBSdr); } for (int i = 0; i < lo_DBCmd.Parameters.Count; i++) { returnValue[i] = lo_DBCmd.Parameters[i].Value.ToString(); } } catch (Exception lo_Ex) { throw new Exception(lo_Ex.Message); } finally { if (lo_DBCmd != null) lo_DBCmd.Dispose(); lo_DBCmd = null; lo_DBConn.Close(); } return lo_Dtb; }接下來查询数据
public void QueryData() { try { string ls_EmpNo = txtEmpno.Text.ToString().Trim(); SqlParameter[] para = { new SqlParameter("@pageSize",pageNum), //一頁顯示幾筆數據 new SqlParameter("@pageIndex",pIndex), // 第幾頁 new SqlParameter("@dataCount",allNum), //總數據 new SqlParameter("@pageCount",MaxIndex), //總頁數 new SqlParameter("@EmpNo",ls_EmpNo), }; string[] ls_Arry = new string[9]; dgDetail.ItemsSource = ExecQuery("sp_EmpNum", CommandType.StoredProcedure, para, ls_Arry).DefaultView; // 存儲過程名 lblDataCount.Content = "共 " + ls_Arry[2] + " 筆"; lblPageCount.Content = ls_Arry[3]; allNum = int.Parse(ls_Arry[2].ToString()); MaxIndex = int.Parse(ls_Arry[3].ToString()); if (this.MaxIndex > 1) { this.txtPage.IsReadOnly = false; this.btnDump.IsEnabled = true; } } catch (Exception lo_Ex) { MessageBox.Show(lo_Ex.Message, "錯誤信息", MessageBoxButton.OK, MessageBoxImage.Error); } finally { if (this.pIndex == 1) // 按钮状态 { this.tbUp.IsEnabled = false; this.tbFirst.IsEnabled = false; } else { this.tbUp.IsEnabled = true; this.tbFirst.IsEnabled = true; } if (this.pIndex == this.MaxIndex) { this.tbDown.IsEnabled = false; this.tbLast.IsEnabled = false; } else { this.tbDown.IsEnabled = true; this.tbLast.IsEnabled = true; } } }点击首頁按鈕
private void tbFirst__MouseUp(object sender, MouseButtonEventArgs e) { this.pIndex = 1; txtPage.Text = pIndex.ToString(); QueryData(); }
点击上一頁按鈕
private void tbUp__MouseUp(object sender, MouseButtonEventArgs e) { if (this.pIndex <= 1) return; this.pIndex--; txtPage.Text = pIndex.ToString(); QueryData(); }
点击下一頁按鈕
private void tbDown__MouseUp(object sender, MouseButtonEventArgs e) { if (this.pIndex >= this.MaxIndex) return; this.pIndex++; txtPage.Text = pIndex.ToString(); QueryData();点击尾页按钮
private void tbLast__MouseUp(object sender, MouseButtonEventArgs e) { this.pIndex = this.MaxIndex; txtPage.Text = pIndex.ToString(); QueryData(); }点击GO按钮
private void btnDump_Click(object sender, RoutedEventArgs e) { string nummber = txtPage.Text.ToString(); if (!IsNumber(nummber)) { MessageBox.Show("請輸入正確的數字", "錯誤信息", MessageBoxButton.OK, MessageBoxImage.Asterisk); return; } else { this.pIndex = Convert.ToInt32(nummber); } QueryData(); }下载完整代码：http://download.csdn.net/detail/why_n/9817567
0 1
- C# sql存儲過程(Stored Procedure)分页查詢功能
- Stored procedure & Sql Injection
- sql查询查所有存储过程(stored procedure)
- c# + sql procedure 实现分页
- MySQL Stored Procedure + C# Using ODBC
- Sql server stored procedure operating summary 01
- Sql server database stored procedure reference 01
- SQL Server 2000 Stored Procedure Programming
- Assignment 1: PL/SQL stored procedure P9
- Overview of SQL Server Stored Procedure
- SQL存储过程（Stored Procedure）
- SQL Server中存储过程Stored Procedure创建及C#调用
- Stored Procedure
- SQL Debug && Stored Procedure Debug ----- SQL Server Management Studio
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- illustrates how to call a SQL Server stored procedure
- SQL Server 2005: Stored Procedure call activity statistics & execution time
- SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET
- 经典进程同步问题：吸烟者问题
- 多个so文件引用起的坑
- 面试常考的常用数据结构与算法
- 随笔
- 慕课网地址
- C# sql存儲過程(Stored Procedure)分页查詢功能
- Job for postfix.service failed because the control process exited with error code. See "systemctl st
- spring的配置
- python 之正则表达式
- 常用三种类别的IP地址
- Linux服务端编程经验
- Java设计模式：工厂模式
- Found libBaiduMapSDK_base_v4_3_0.so error
- TensorFlow实战——入门
