程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> C#入門知識 >> C#操作數據庫基礎實例《密碼管理工具》

C#操作數據庫基礎實例《密碼管理工具》

編輯：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();
        }
    }
}