直接贴代码吧
SqlHelperusing System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;
namespace ADMEWinForm{ class SqlHelper { private static readonly string strCon = System.Configuration.ConfigurationSettings.AppSettings["connstr"].ToString();
#region 实例化对象 /// <summary> /// 实例化对象 /// </summary> SqlConnection con = new SqlConnection(strCon); private DataSet ds; private SqlCommand cmd; private SqlDataAdapter sda; private SqlDataReader myReader; private SqlTransaction transaction;
#endregion
#region 检测连接是否打开 /// <summary> /// 检测连接的方法CheckConnection(),若连接是关闭的则打开SqlConnection连接 /// </summary> public void CheckConnection() { if (this.con.State == ConnectionState.Closed) { this.con.Open(); } } #endregion
#region 执行SQL语句或存储过程的方法ExecuteDataSetProc() public DataSet ExecuteDataSetProc(string strSQL, int pagesize, int pageindex) { CheckConnection(); DataSet ds1 = new DataSet(); try { cmd = new SqlCommand(strSQL, con); cmd.CommandType = CommandType.StoredProcedure; SqlParameter[] para = { new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int) }; para[0].Value = pagesize; para[1].Value = pageindex; cmd.Parameters.AddRange(para); SqlDataAdapter dap = new SqlDataAdapter(); dap.SelectCommand = cmd; dap.Fill(ds1, "TGongWen"); } catch (Exception e) { e.Message.ToString(); } finally { con.Close(); } return ds1; }
#region 执行SQL语句的方法ExecuteNonQuery() /// <summary> /// 执行SQL语句的方法ExecuteNonQuery() /// </summary> /// <param name="strSQL">要执行的SQL语句</param> public int ExecuteNonQuery(string strSQL) { int count = 0; CheckConnection(); try { cmd = new SqlCommand(strSQL, con); count = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { con.Close(); } return count; } #endregion
#region 执行语句,返回该语句查询出的数据行的总数 /// <summary> /// 执行语句,返回该语句查询出的数据行的总数 /// </summary> /// <param name="strSQL">要执行的SQL语句</param> /// <returns>整型值－－数据总行数</returns> public int ReturnRowCount(string strSQL) { CheckConnection(); try {
cmd = new SqlCommand(strSQL, con); return Convert.ToInt32(cmd.ExecuteScalar().ToString()); } catch { return 0; } finally { con.Close(); } } #endregion }}
上图是分页列表显示页面，其对应的.cs 代码如下：
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;
namespace ADMEWinForm{ public partial class Form1 : Form { SqlHelper db = new SqlHelper(); public int rowcount; public int NowPage = 1, PageCount = 1, PageSize = 20; public Form1() { InitializeComponent(); } public void BindData() { setBtnStatus();
//这里是调用存储过程并传入页码和当前页面 this.dataGridView1.DataSource = db.ExecuteDataSetProc("Pagination", PageSize, NowPage).Tables["TGongWen"]; } private void Form1_Load(object sender, EventArgs e) { BindData(); } //计算余页 public int OverPage() { int pages = 0; if (rowcount % PageSize != 0) pages = 1; else pages = 0; return pages; } public void setBtnStatus() { rowcount= db.ReturnRowCount("select count(1) from TGongwen "); PageCount = rowcount / PageSize + OverPage(); this.label2.Text="共"+PageCount+"页 共"+rowcount+"条记录"; this.label1.Text = "当前是第" + NowPage + "页"; if (NowPage > 1) { this.button5.Enabled = true; this.button6.Enabled = true; } else { this.button5.Enabled = false; this.button6.Enabled = false; } if (NowPage == PageCount) { this.button7.Enabled = false; this.button8.Enabled = false; } else { this.button7.Enabled = true; this.button8.Enabled = true; } } private void button5_Click(object sender, EventArgs e) { NowPage = 1; Form1_Load(sender,e); } private void button6_Click(object sender, EventArgs e) { NowPage -= 1; Form1_Load(sender,e); } private void button7_Click(object sender, EventArgs e) { NowPage += 1; Form1_Load(sender,e); } private void button8_Click(object sender, EventArgs e) { NowPage = PageCount; Form1_Load(sender,e); } private void button2_Click(object sender, EventArgs e) { string id = this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString(); if (db.ExecuteNonQuery("delete from TGongwen where gid=" + id)>0) { MessageBox.Show("删除成功"); } Form1_Load(sender, e); } private void button3_Click(object sender, EventArgs e) { Manager m = new Manager(); m.CmdType = "edit"; m.Id = Convert.ToInt32(this.dataGridView1.SelectedRows[0].Cells[0].Value.ToString()); m.Show(); } private void button1_Click(object sender, EventArgs e) { Manager m = new Manager(); m.CmdType = "add"; m.Show(); } }}
新增数据和修改数据页面以及所对应的.cs 文件
namespace ADMEWinForm{ public partial class Manager : Form { SqlHelper db = new SqlHelper(); public Manager() { InitializeComponent(); } private string cmdType;
public string CmdType { get { return cmdType; } set { cmdType = value; } } private int id;
public int Id { get { return id; } set { id = value; } } public void GetModelData() { DataSet ds = db.ReturnDataSet("select * from tgongwen where gid=" + Id); if (ds.Tables[0].Rows.Count > 0) { this.textBox1.Text = ds.Tables[0].Rows[0]["title"].ToString(); this.textBox2.Text = ds.Tables[0].Rows[0]["neibuyonghu"].ToString(); this.textBox3.Text = ds.Tables[0].Rows[0]["reader"].ToString(); } } private void button1_Click(object sender, EventArgs e) { if (CmdType == "add") { if (add() > 0) { MessageBox.Show("添加成功！"); button2_Click(sender, e); } } else { if (update() > 0) { MessageBox.Show("修改成功！"); button2_Click(sender,e); } }
} public int add() { return db.ExecuteNonQuery("insert into tgongwen (title,fariqi,neibuyonghu,reader)values('"+this.textBox1.Text+"','"+System.DateTime.Now.ToString()+"','"+this.textBox2.Text+"','"+this.textBox3.Text+"')"); } public int update() { return db.ExecuteNonQuery("update tgongwen set title='"+this.textBox1.Text+"',neibuyonghu='"+this.textBox2.Text+"',reader='"+this.textBox3.Text+"' where gid="+Id ); } private void button2_Click(object sender, EventArgs e) { Form1 f = new Form1(); f.Show(); this.Close(); } private void Manager_Load(object sender, EventArgs e) { GetModelData(); } }}
对了，还有存储过程忘记贴了就在下面
到此结束。
