近来接触到C#语言,感觉到很强大,参照浪曦密码管理和北风网的家庭理财案例,写了一个最为基础的C#操作数据库实例,做了详细的注释作为备忘,也供初哥参考,高手就莫看了。
先贴界面:
主窗体源代码
view plainusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
namespace PassMan
{
public partial class PassMan : Form
{
public PassMan()
{
InitializeComponent();
//初始化加载皮肤
skinEngine1.SkinFile = "MacOS.ssk";
}
private void PassMan_Load(object sender, EventArgs e)
{
string sql = "Select * from passMan";
Bind(sql);
//实现隔行变色
dataGridView1.RowsDefaultCellStyle.BackColor = Color.White;
dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Gainsboro;
}
internal void Bind(string sql)//此处声明为internal级别,引用类中才能使用该方法
{
dataGridView1.DataSource= DBHelper.GetDataSet(sql);
//自定义dataGridView的表头以及列宽
dataGridView1.Columns[0].HeaderText = "序号";
dataGridView1.Columns[1].HeaderText = "标题";
dataGridView1.Columns[2].HeaderText = "网址";
dataGridView1.Columns[3].HeaderText = "用户名";
dataGridView1.Columns[4].HeaderText = "密码";
dataGridView1.Columns[5].HeaderText = "更新日期";
dataGridView1.Columns[0].Width = 52;
dataGridView1.Columns[1].Width = 85;
dataGridView1.Columns[2].Width = 175;
dataGridView1.Columns[3].Width = 75;
dataGridView1.Columns[4].Width = 75;
dataGridView1.Columns[5].Width = 120;
toolStripStatusLabel1.Text = "共有" + (dataGridView1.RowCount).ToString() + "条记录。";
}
private void PassMan_FormClosed(object sender, FormClosedEventArgs e)// 重载窗体退出事件,因为splash窗体是本窗体的父窗体,所以不会随本窗体关闭而退出程序
{
Application.Exit();
}
private void 查询ToolStripMenuItem_Click(object sender, EventArgs e)
{
Search sch = new Search();
//实现查询窗体中操做本窗体的可用方法
sch.ipassMan = this;
sch.Show();
}
private void 添加ToolStripMenuItem_Click(object sender, EventArgs e)
{
AddForm add = new AddForm();
//实现添加窗体中操做本窗体的可用方法
add.ipassMan = this;
add.Show();
}
private void 修改ToolStripMenuItem_Click(object sender, EventArgs e)
{
ModifyForm mod = new ModifyForm();
//实现修改窗体中操做本窗体的可用方法
mod.ipassMan = this;
mod.Show();
}
private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
{
string message = "您确实要删除选定记录吗?";
string caption = "删除提醒";
MessageBoxButtons buttons = MessageBoxButtons.YesNo;
DialogResult result;
result = MessageBox.Show(this, message, caption, buttons, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
if (result == DialogResult.Yes)
{
string sql = "Delete From passMan Where id=@id ";
SqlParameter[] ps ={
new SqlParameter("@id",nowSelectIndex())
};
DBHelper.ExecuteCommand(sql, ps);
string sqlall = "Select * from passMan ";
Bind(sqlall);
}
}
internal int nowSelectIndex()//声明为internal级别,用来在修改窗体中取得当前选择的序号值
{
return int.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
}
private void 退出ToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void 导出ToolStripMenuItem_Click(object sender, EventArgs e)
{
DataToExcel(dataGridView1);
}
public void DataToExcel(DataGridView m_DataView)//将dataGridView中数据导出为可被Excel识别的伪文件
{
SaveFileDialog sfile = new SaveFileDialog();
sfile.Title = "导出为Excel文件";
sfile.Filter = "xls文件(*.xls) |*.xls";
sfile.FilterIndex = 1;
if (sfile.ShowDialog() == DialogResult.OK)
{
string FileName = sfile.FileName;
if (File.Exists(FileName))
File.Delete(FileName);
FileStream objFileStream;
StreamWriter objStreamWriter;
string strLine = "";
objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);
for (int i = 0; i < m_DataView.Columns.Count; i++)
{
if (m_DataView.Columns[i].Visible == true)
{
strLine = strLine + m_DataView.Columns[i].HeaderText.ToString() + Convert.ToChar(9);
}
}
objStreamWriter.WriteLine(strLine);
strLine = "";
for (int i = 0; i < m_DataView.Rows.Count; i++)
{
if (m_DataView.Columns[0].Visible == true)
{
if (m_DataView.Rows[i].Cells[0].Value == null)
strLine = strLine + " " + Convert.ToChar(9);
else
strLine = strLine + m_DataView.Rows[i].Cells[0].Value.ToString() + Convert.ToChar(9);
}
for (int j = 1; j < m_DataView.Columns.Count; j++)
{
if (m_DataView.Columns[j].Visible == true)
{
if (m_DataView.Rows[i].Cells[j].Value == null)
strLine = strLine + " " + Convert.ToChar(9);
else
{
string rowstr = "";
rowstr = m_DataView.Rows[i].Cells[j].Value.ToString();
if (rowstr.IndexOf("\r\n") > 0)
rowstr = rowstr.Replace("\r\n", " ");
if (rowstr.IndexOf("\t") > 0)
rowstr = rowstr.Replace("\t", " ");
strLine = strLine + rowstr + Convert.ToChar(9);
}
}
}
objStreamWriter.WriteLine(strLine);
strLine = "";
}
objStreamWriter.Close();
objFileStream.Close();
MessageBox.Show(this, "导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void 导入ToolStripMenuItem_Click(object sender, EventArgs e)
{
DataSet ds;
ds=ReadExcel(OpenFile(), "Sheet1");
if (ds != null)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
String sql = "insert into passMan values(@title,@netAddress,@userName,@passKey,@updateTime)";
SqlParameter[] ps ={
new SqlParameter("@title",dr[1].ToString().Trim()),
new SqlParameter("@netAddress",dr[2].ToString().Trim()),
new SqlParameter("@userName",dr[3].ToString().Trim()),
new SqlParameter("@passKey",dr[4].ToString().Trim()),
new SqlParameter("@updateTime",DateTime.Now)
};
DBHelper.ExecuteCommand(sql, ps);
}
string sqlall = "Select * from passMan";
Bind(sqlall);
}
}
private DataSet ReadExcel(string strFileName, string sheetName)//使用OLE操作数据库的方法读取excel数据,导入到系统
{
if (strFileName == string.Empty)
{
return null;
}
else
{
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strFileName + ";Extended Properties = Excel 8.0";
OleDbConnection oleConnection = new OleDbConnection(strConnection);
oleConnection.Open();
DataSet dsRead = new DataSet();
OleDbDataAdapter oleAdper = new OleDbDataAdapter(" SELECT * FROM [" + sheetName + "$]", oleConnection);
oleAdper.Fill(dsRead, "result");
oleConnection.Close();
return dsRead;
}
}
private string OpenFile()
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel文件|*.xls";
openFileDialog.RestoreDirectory = true;
openFileDialog.Title = "打开文件";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
return (openFileDialog.FileName);
}
else return String.Empty;
}
private void 苹果味ToolStripMenuItem_Click(object sender, EventArgs e)
{
skinEngine1.SkinFile = "MacOS.ssk";
}
private void 纸张味ToolStripMenuItem_Click(object sender, EventArgs e)
{
skinEngine1.SkinFile = "PageColor.ssk";
}
private void 关于ToolStripMenuItem_Click(object sender, EventArgs e)
{
About ab = new About();
ab.Show();
}
private void PassMan_SizeChanged(object sender, EventArgs e)
{
if (this.WindowState == FormWindowState.Minimized)
{
this.Hide();
this.notifyIcon1.Visible = true;
}
}
private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
{
if (e.Button == MouseButtons.Left)
{
this.Visible = true;
this.WindowState = FormWindowState.Normal;
this.notifyIcon1.Visible = false;
}
}
private void 显示程序ToolStripMenuItem_Click(object sender, EventArgs e)
{
this.Visible = true;
this.WindowState = FormWindowState.Normal;
this.notifyIcon1.Visible = false;
}
private void 退出ToolStripMenuItem1_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void 作者主页ToolStripMenuItem_Click(object sender, EventArgs e)
{
System.Diagnostics.Process.Start("http://blog.csdn.net/shaobotao");
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
导入ToolStripMenuItem_Click(sender, e);
}
private void toolStripButton2_Click(object sender, EventArgs e)
{
导出ToolStripMenuItem_Click(sender, e);
}
private void toolStripButton3_Click(object sender, EventArgs e)
{
查询ToolStripMenuItem_Click(sender, e);
}
private void toolStripButton4_Click(object sender, EventArgs e)
{
添加ToolStripMenuItem_Click(sender, e);
}
private void toolStripButton5_Click(object sender, EventArgs e)
{
修改ToolStripMenuItem_Click(sender, e);
}
private void toolStripButton6_Click(object sender, EventArgs e)
{
删除ToolStripMenuItem_Click(sender, e);
}
private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
修改ToolStripMenuItem_Click(sender, e);
}
}
}
增添窗体源码:
view plainusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace PassMan
{
public partial class AddForm : Form
{
public PassMan ipassMan;//声明一个主窗体变量
public AddForm()
{
InitializeComponent();
}
private void addOk_Click(object sender, EventArgs e)
{
if (addCkeck())
{
String sql = "insert into passMan values(@title,@netAddress,@userName,@passKey,@updateTime)";
SqlParameter[] ps={
new SqlParameter("@title",txtTitle.Text.Trim()),
new SqlParameter("@netAddress",txtNetAdd.Text.Trim()),
new SqlParameter("@userName",txtUserName.Text.Trim()),
new SqlParameter("@passKey",txtPass.Text.Trim()),
new SqlParameter("@updateTime",DateTime.Now)
};
DBHelper.ExecuteCommand(sql,ps);
string sqlall = "Select * from passMan ";
ipassMan.Bind(sqlall);//调用主窗体中绑定数据函数
this.Close();
}
}
private bool addCkeck()
{
bool result = true;
if (txtTitle.Text.Trim() == string.Empty)
{
lbTitle.Text = "标题不能为空!";
result = false;
}
else if(txtNetAdd.Text.Trim() == string.Empty)
{
lbNetAdd.Text = "网址不能为空!";
result = false;
}
else if (txtUserName.Text.Trim() == string.Empty)
{
lbUserName.Text = "用户名不能为空!";
result = false;
}
else if (txtPass.Text.Trim() == string.Empty)
{
lbPass.Text = "密码不能为空!";
result = false;
}
return result;
}
private void addNo_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
修改窗体源码:
view plainusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace PassMan
{
public partial class ModifyForm : Form
{
public PassMan ipassMan;//声明一个主窗体变量
public ModifyForm()
{
InitializeComponent();
}
private bool modCkeck()
{
bool result = true;
if (txtTitle.Text.Trim() == string.Empty)
{
lbTitle.Text = "标题不能为空!";
result = false;
}
else if(txtNetAdd.Text.Trim() == string.Empty)
{
lbNetAdd.Text = "网址不能为空!";
result = false;
}
else if (txtUserName.Text.Trim() == string.Empty)
{
lbUserName.Text = "用户名不能为空!";
result = false;
}
else if (txtPass.Text.Trim() == string.Empty)
{
lbPass.Text = "密码不能为空!";
result = false;
}
return result;
}
private void ModifyForm_Load(object sender, EventArgs e)
{
string sql = "select * from passMan where id = " + ipassMan.nowSelectIndex();//取得主窗体中当前选择行的序号值
DataTable tb = DBHelper.GetDataSet(sql);
foreach (DataRow dr in tb.Rows)
{
if (dr[0].ToString() == ipassMan.nowSelectIndex().ToString())
{
lbid.Text = dr[0].ToString();
txtTitle.Text = dr[1].ToString();
txtNetAdd.Text = dr[2].ToString();
txtUserName.Text = dr[3].ToString();
txtPass.Text = dr[4].ToString();
}
}
}
private void modOk_Click(object sender, EventArgs e)
{
if (modCkeck())
{
String sql = "update passMan set title=@title,netAddress=@netAddress,userName=@userName,passKey=@passKey,updateTime=@updateTime where id=@id";
SqlParameter[] ps ={
new SqlParameter("@title",txtTitle.Text.Trim()),
new SqlParameter("@netAddress",txtNetAdd.Text.Trim()),
new SqlParameter("@userName",txtUserName.Text.Trim()),
new SqlParameter("@passKey",txtPass.Text.Trim()),
new SqlParameter("@updateTime",DateTime.Now),
new SqlParameter("@id",ipassMan.nowSelectIndex())
};
DBHelper.ExecuteCommand(sql, ps);
string sqlall = "Select * from passMan ";
ipassMan.Bind(sqlall);//调用主窗体中绑定数据函数
this.Close();
}
}
private void modNo_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
查询窗体源码:
view plainusing System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace PassMan
{
public partial class Search : Form
{
public PassMan ipassMan;//声明一个主窗体变量
public Search()
{
InitializeComponent();
}
private void Search_Load(object sender, EventArgs e)
{
cmbType.SelectedIndex = 0;
}
private void schOK_Click(object sender, EventArgs e)
{
string sql = "Select * from passMan ";
if (txtCondition.Text.Trim() == string.Empty)
{
ipassMan.Bind(sql);//调用主窗体中绑定数据函数
this.Close();
}
else
{
if (cmbType.SelectedIndex == 0)
{
sql = sql + schSql("title");
ipassMan.Bind(sql);
this.Close();
}
else if (cmbType.SelectedIndex == 1)
{
sql = sql + schSql("netAddress");
ipassMan.Bind(sql);
this.Close();
}
else
{
sql = sql + schSql("userName");
ipassMan.Bind(sql);
this.Close();
}
}
}
private string schSql(string schfield)
{
string sql;
sql = "Where "+ schfield + " like '%" + txtCondition.Text.Trim() +"%'";
return sql;
}
private void schNo_Click(object sender, EventArgs e)
{
this.Close();
}
}
}