C#操作数据库基础实例《密码管理工具》

近来接触到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(); 
        } 
    } 
}