分页存储过程在.net中的应用
下面是分页存储过程在.NET中应用的源码：
代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
namespace DataGridView_Page
{
public partial class frmMarkingPage : Form
{
int currPage = 1; //当前页码
string showColumn = "EmployeId,EmployeName,EmployeSex,EmployeAge,EmployeSection,ContractDateStart,ContractDateEnd,positionName,EmployeUid,EmployePwd"; //需要得到的字段
string tabName = "Employee,Position"; //需要查看的表名
string strCondition = "Employee.positionid=Position.positionID"; //查询条件
string ascColumn = "EmployeId"; //排序的字段名
byte bitOrderType = 0; //排序的类型
string pkColumn = "EmployeId"; //主键名称
int pageSize = 3; //分页大小
int resultSum = 0; //返回数据总条数
int pageSum = 0;
public frmMarkingPage()
{
InitializeComponent();
Page();
}
private void frmMarkingPage_Load(object sender, EventArgs e)
{
currPage = 1;
Page();
}
private void btnFirst_Click(object sender, EventArgs e)
{
currPage = 1;
Page();
}
private void Page()
{
this.dgbPage.DataSource = this.GetInfo(); //数据绑定到控件
resultSum = SelCount();
if (resultSum % pageSize == 0)
pageSum = resultSum / pageSize;
else
pageSum = resultSum / pageSize + 1;
this.lblPageInfo.Text = "第 " + currPage + " 页 | 共 " + pageSum + " 页 | 每页 " + pageSize + " 条 | 共 " + resultSum + " 条";
}
/// <summary>
/// 返回数据总条数
/// </summary>
/// <returns></returns>
private static int SelCount()
{
SqlConnection conn = new SqlConnection("server=.;database=Finance;uid=sa;pwd=123456");
conn.Open();
string sqlselect = "select * from Employee";
SqlCommand cmd = new SqlCommand(sqlselect, conn);
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dtb = new DataTable();
dtb = new DataTable();
da.Fill(dtb);
return dtb.Rows.Count;
}
/// <summary>
/// 获取数据库中表的信息
/// </summary>
/// <returns>DataTable dt</returns>
public DataTable GetInfo()
{
SqlParameter[] sqlParameter =
{
//@currPage int, --当前页页码 (即Top currPage)
//@showColumn varchar(2000) = '', --需要得到的字段 (即 column1,column2,......)
//@tabName varchar(2000), --需要查看的表名 (即 from table_name)
//@strCondition varchar(2000) = '', --查询条件 (即 where condition......) 不用加where关键字
//@ascColumn varchar(100) = '', --排序的字段名 (即 order by column asc/desc)
//@bitOrderType bit = 0, --排序的类型 (0为升序,1为降序)
//@pkColumn varchar(50) = '', --主键名称
//@pageSize int
new SqlParameter("@currPage",currPage),
new SqlParameter("@showColumn",showColumn),
new SqlParameter("@tabName",tabName),
new SqlParameter("@strCondition",strCondition),
new SqlParameter("@ascColumn",ascColumn),
new SqlParameter("@bitOrderType",bitOrderType),
new SqlParameter("@pkColumn",pkColumn),
new SqlParameter("@pageSize",pageSize)
};
using (SqlConnection sqlConnection = new SqlConnection("server=.;database=Finance;uid=sa;pwd=123456"))
{
try
{
sqlConnection.Open();
SqlCommand sqlcommand = new SqlCommand("proc_Page", sqlConnection);
sqlcommand.CommandType = CommandType.StoredProcedure;
sqlcommand.Parameters.AddRange(sqlParameter);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlcommand;
DataTable dt = new DataTable();
sqlDataAdapter.Fill(dt);
return dt;
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
}
private void btnFirst_Click_1(object sender, EventArgs e)
{
if (currPage == 1)
{
MessageBox.Show("已经到达首页!");
}
else
{
currPage = 1;
Page();
}
}
private void btnFirstPage_Click(object sender, EventArgs e)
{
try
{
if (currPage == 1)
MessageBox.Show("已经到达首页!");
else
{
currPage--;
Page();
}
}
catch(Exception ex)
{}
}
private void btnNext_Click(object sender, EventArgs e)
{
try
{
if (currPage == pageSum)
{
MessageBox.Show("已经到达尾页!");
}
else
{
currPage++;
Page();
}
}
catch(Exception ex)
{}
}
private void btnLast_Click(object sender, EventArgs e)
{
try
{
if (currPage == pageSum)
{
MessageBox.Show("已经到达尾页!");
}
else
{
currPage = pageSum;
Page();
}
}
catch(Exception ex)
{}
}
/// <summary>
/// 常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
/// </summary>
/// <remarks>
/// using System.IO;
/// </remarks>
/// <param name="datagridview"></param>
private void DataGridViewToExcel(DataGridView datagridview)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
if (dlg.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = dlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string columnTitle = "";
try
{
//写入列标题
for (int i = 0; i < datagridview.ColumnCount; i++)
{
if (i > 0)
{
columnTitle += "\t";
}
columnTitle += datagridview.Columns[i].HeaderText;
}
sw.WriteLine(columnTitle);
//写入列内容
for (int j = 0; j < datagridview.Rows.Count; j++)
{
string columnValue = "";
for (int k = 0; k < datagridview.Columns.Count; k++)
{
if (k > 0)
{
columnValue += "\t";
}
if (datagridview.Rows[j].Cells[k].Value == null)
columnValue += "";
else
columnValue += datagridview.Rows[j].Cells[k].Value.ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
/// <summary>
/// 将DataGridView中的数据导入到Excel中
/// </summary>
/// <param name="dt">DataGridView</param>
/// <param name="templatePath">模板的路径</param>
/// <param name="excelName">导入Excel的路径</param>
/// <returns></returns>
public static bool DataGridViewToExcel(DataGridView dg, string templatePath, string excelName)
{
bool result = true;
XmlDocument xmlDoc = new XmlDocument();
if (File.Exists(templatePath))
{
xmlDoc.Load(templatePath);
XmlNode root = xmlDoc.SelectSingleNode(@"/*[local-name()='Workbook']/*[local-name()='Worksheet'][1]/*[local-name()='Table']");
foreach (DataGridViewRow dgRow in dg.Rows)
{
XmlElement row = xmlDoc.CreateElement("", "Row", "urn:schemas-microsoft-com:office:spreadsheet");
row.SetAttribute("AutoFitHeight", "urn:schemas-microsoft-com:office:spreadsheet", "0");
foreach (DataGridViewColumn dgcol in dg.Columns)
{
if (dgcol.Visible && dgcol.IsDataBound && dgcol.GetType().Name == "DataGridViewTextBoxColumn")
{
XmlElement cell = xmlDoc.CreateElement("", "Cell", "urn:schemas-microsoft-com:office:spreadsheet");
XmlElement data = xmlDoc.CreateElement("", "Data", "urn:schemas-microsoft-com:office:spreadsheet");
data.SetAttribute("Type", "urn:schemas-microsoft-com:office:spreadsheet", "String");
data.InnerText = dgRow.Cells[dgcol.Name].Value.ToString();
cell.AppendChild(data);
row.AppendChild(cell);
}
}
//root.InsertBefore(row, root.LastChild);
root.AppendChild(row);
}
#region 添加合计行
XmlElement totalRow = xmlDoc.CreateElement("", "Row", "urn:schemas-microsoft-com:office:spreadsheet");
totalRow.SetAttribute("AutoFitHeight", "urn:schemas-microsoft-com:office:spreadsheet", "0");
for (int i = 0; i < 2; i++)
{
XmlElement cell = xmlDoc.CreateElement("", "Cell", "urn:schemas-microsoft-com:office:spreadsheet");
XmlElement data = xmlDoc.CreateElement("", "Data", "urn:schemas-microsoft-com:office:spreadsheet");
data.SetAttribute("Type", "urn:schemas-microsoft-com:office:spreadsheet", i == 0 ? "String" : "Number");
data.InnerText = (i == 0 ? "合计" : dg.Rows.Count.ToString());
cell.AppendChild(data);
totalRow.AppendChild(cell);
}
root.AppendChild(totalRow);
#endregion
XmlNode countnode = xmlDoc.SelectSingleNode(@"/*[local-name()='Workbook']/*[local-name()='Worksheet'][1]/*[local-name()='Table']");
countnode.Attributes["ss:ExpandedRowCount"].Value = (Int32.Parse(countnode.Attributes["ss:ExpandedRowCount"].Value) + dg.Rows.Count + 1).ToString();//标题+合计+数据
xmlDoc.Save(excelName);
result = true;
}
else
{
result = false;
}
return result;
}
private void btnDataGridViewToExcel_Click(object sender, EventArgs e)
{
//string path = saveFileDialog1.FileName;
this.DataGridViewToExcel(dgbPage);
}
}
}
