|
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;
namespace opdata
{
public partial class Form1 : Form
{
private static String connectstring = "server=192.168.15.175;database='wentest';user id='sa';password='19831221'";
private SqlConnection allconn; //连接对象
private int allpage; //计算得出的总页数
private int currpage; //当前页数
private int allrows;//所有条数
private int psize;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
SqlConnection conn = new SqlConnection(connectstring);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select top 100 * from adonet", conn);
SqlDataReader sread = cmd.ExecuteReader();
while (sread.Read())
{
ListViewItem lv = new ListViewItem(sread.GetString(0));
lv.SubItems.Add(sread.GetString(1));
lv.SubItems.Add(sread.GetString(2));
lv.SubItems.Add(sread.GetDateTime(3).ToString());
listView1.Items.Add(lv);
}
sread.Close();
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
conn.Close();
}
}
private void setListview(ListView lview)
{
lview.View = View.Details; //表格方式
lview.GridLines = true;
lview.FullRowSelect = true;
ColumnHeader header1 = new ColumnHeader();
ColumnHeader header2 = new ColumnHeader();
ColumnHeader header3 = new ColumnHeader();
ColumnHeader header4 = new ColumnHeader();
header1.Text = "编号";
header2.Text = "名称";
header3.Text = "地址";
header4.Text = "日期";
header1.Width = 80;
header2.Width = 100;
header3.Width = 280;
header4.Width = 140;
header1.TextAlign = HorizontalAlignment.Center;
header2.TextAlign = HorizontalAlignment.Center;
header3.TextAlign = HorizontalAlignment.Center;
header4.TextAlign = HorizontalAlignment.Center;
lview.Columns.Add(header1);
lview.Columns.Add(header2);
lview.Columns.Add(header3);
lview.Columns.Add(header4);
lview.Items.Clear();
}
private void Form1_Load(object sender, EventArgs e)
{
//初始化
allconn = new SqlConnection(connectstring);
allrows = getpageall(allconn); //取得所有条数
currpage = 1;//设为第一页
psize = 50;
allpage = getpage(allrows, psize); //取得页数
setListview(listView1);
//MessageBox.Show(allpage.ToString());
showlist(allconn, listView1, 1);
//statusStrip1.
toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize .ToString()+ "条记录，共有" +allpage.ToString()+ "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
toolStripStatusLabel2.Text = "当前时间：" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
}
private void button2_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connectstring);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from adonet", conn);
MessageBox.Show(cmd.ExecuteScalar().ToString());
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
conn.Close();
}
}
private void button5_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connectstring);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "myfy_one"; //存储过程名
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.CreateParameter();
param.Direction = ParameterDirection.Input; //输入参数
param.ParameterName = "@size";
param.DbType = DbType.Int32;
param.Value = 100;
SqlParameter param1 = cmd.CreateParameter();
param1.Direction = ParameterDirection.Input;
param1.ParameterName = "@number";
param1.DbType = DbType.Int32;
param1.Value = 3;
cmd.Parameters.Add(param);
cmd.Parameters.Add(param1);
SqlDataReader sread = cmd.ExecuteReader();
while (sread.Read())
{
ListViewItem lv = new ListViewItem(sread.GetString(0));
lv.SubItems.Add(sread.GetString(1));
lv.SubItems.Add(sread.GetString(2));
lv.SubItems.Add(sread.GetDateTime(3).ToString());
listView1.Items.Add(lv);
}
sread.Close();
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
conn.Close();
}
}
private void button6_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(connectstring);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("sall",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@allnum",SqlDbType.Int,4);
cmd.Parameters["@allnum"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
MessageBox.Show(cmd.Parameters["@allnum"].Value.ToString());
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
conn.Close();
}
}
private void button7_Click(object sender, EventArgs e)
{
currpage = 1;
showlist(allconn, listView1, currpage);
toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
}
private int getpageall(SqlConnection gconn) //取得条数
{
int rnum=0;
try
{
gconn.Open();
SqlCommand cmd = new SqlCommand("sall", gconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@allnum", SqlDbType.Int, 4);
cmd.Parameters["@allnum"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//MessageBox.Show(cmd.Parameters["@allnum"].Value.ToString());
rnum = (int)cmd.Parameters["@allnum"].Value;
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
gconn.Close();
}
return rnum;
}
private int getpage(int rows,int size) //取得页数
{
int page;
if (rows % size == 0 ) //若是能整除
{
page = rows / size;
}
else {
page = rows / size +1;
}
return page;
}
private void showlist(SqlConnection sconn,ListView lvw,int cur) //显示记录到listview1
{
lvw.Items.Clear();
if (allrows != 0) //有记录才显示
{
try
{
sconn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sconn;
cmd.CommandText = "myfy_one"; //存储过程名
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmd.CreateParameter();
param.Direction = ParameterDirection.Input; //输入参数
param.ParameterName = "@size";
param.DbType = DbType.Int32;
param.Value = psize; //分页大小
SqlParameter param1 = cmd.CreateParameter();
param1.Direction = ParameterDirection.Input;
param1.ParameterName = "@number";
param1.DbType = DbType.Int32;
param1.Value = cur; //当前页
cmd.Parameters.Add(param);
cmd.Parameters.Add(param1);
SqlDataReader sread = cmd.ExecuteReader();
while (sread.Read())
{
ListViewItem lv = new ListViewItem(sread.GetString(0));
lv.SubItems.Add(sread.GetString(1));
lv.SubItems.Add(sread.GetString(2));
lv.SubItems.Add(sread.GetDateTime(3).ToString());
lvw.Items.Add(lv);
}
sread.Close();
}
catch (SqlException ee)
{
MessageBox.Show(ee.ToString());
}
finally
{
sconn.Close();
}
}
}
private void button8_Click(object sender, EventArgs e)
{
currpage = allpage;
showlist(allconn,listView1, currpage);
toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
}
private void button3_Click(object sender, EventArgs e)
{
if (currpage > 1)
{
currpage = currpage - 1;
showlist(allconn, listView1, currpage);
toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
}
}
private void button4_Click(object sender, EventArgs e)
{
if (currpage < allpage)
{
currpage = currpage + 1;
showlist(allconn, listView1, currpage);
toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
}
}
private void timer1_Tick(object sender, EventArgs e)
{
toolStripStatusLabel2.Text = "当前时间：" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
}
private void button9_Click(object sender, EventArgs e)
{
int size = System.Int32.Parse( textBox1.Text );
if (size < 1)
{
currpage = 1;
}
if (size > allpage)
{
currpage = allpage;
}
currpage = size;
showlist(allconn, listView1, currpage);
toolStripStatusLabel1.Text = "总共有" + allrows.ToString() + "条记录，每页" + psize.ToString() + "条记录，共有" + allpage.ToString() + "页，当前页：" + currpage.ToString() + "/" + allpage.ToString();
}
}
}
|
