基于C#开发数据库应用程序的基本应用

基于C#开发数据库应用程序

一、思路

　　1.窗体上使用DataGridView控件来实现对数据库表格内容的显示

　　2.绑定数据源到DataGridView控件（应用程序连接到数据库）

　　3.在应用程序上实现对数据库文件的增、删、改、查

二、界面

　　

 

 三、实现过程

　1.绑定数据源到DataGridView控件（应用程序连接到数据库）

　方法引用：

private void Form1_Load(object sender, EventArgs e)
        {
            //Binding Mode Get DataSource to DataGridView
            dataGridView1.DataSource = BindingMode_BindingSource("Hyson_Staff", "Table_Staff_Info").Tables[0];

            //dataGridView1.DataSource = NonBindingMode_BindingSource();
        }

　　方法实现：

        /// <summary>
        /// 绑定模式下获取数据源
        /// </summary>
        /// <param name="DB_Name">数据库名称</param>
        /// <param name="Table_Name">表格名称</param>
        /// <returns>返回类型为DataSet</returns>
        private DataSet BindingMode_BindingSource(string DB_Name, string Table_Name)
        {
            string constr = @"Server=DESKTOP-K1D8VOK\HYSON_STOCK_V01;user=sa;pwd=sa;database=" + DB_Name;
            SqlConnection mycon = new SqlConnection(constr);
            DataSet myds = new DataSet();
            try
            {
                mycon.Open();
                string sql = "select * from " + Table_Name;
                SqlDataAdapter myda = new SqlDataAdapter(sql, mycon);
                myda.Fill(myds, "Table_Staff_Info");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mycon.Close();
            }
            return myds;
        }

　　2.实现数据库的新增记录

方法引用：

 InsertDB( "Hyson_Staff","Table_Staff_Info",
                        new string[]{"Name","Gender","Department","BrithDay","JoinTime", "LeaveTime","IsWork"},
                        new string[]{   textBox_Name.Text,
                                        textBox_Gender.Text,
                                        textBox_Department.Text,
                                        textBox_BrithDay.Text,
                                        textBox_JoinTime.Text,
                                        textBox_LeaveTime.Text,
                                        checkBox_IsWork.Checked.ToString()});
                    dataGridView1.DataSource = BindingMode_BindingSource("Hyson_Staff", "Table_Staff_Info").Tables[0];

方法实现：

　　　　　/// <summary>
        /// 向数据库中增加数据
        /// </summary>
        /// <param name="DB_Name">数据库名称</param>
        /// <param name="Table_Name">表名</param>
        /// <param name="Array_Keys">Key数组</param>
        /// <param name="Array_Vaules">Value数组</param>
        private void InsertDB(string DB_Name, string Table_Name, string[] Array_Keys, string[] Array_Vaules)
        {
            string constr = @"Server=DESKTOP-K1D8VOK\HYSON_STOCK_V01;user=sa;pwd=sa;database=" + DB_Name;
            SqlConnection mycon = new SqlConnection(constr);

            try
            {
                mycon.Open();
                string keys = " (";
                for (int i = 0; i < Array_Keys.Length; i++)
                {
                    if (i<=Array_Keys.Length-2)
                    {
                        keys = keys + "[" + Array_Keys[i].Trim() + "],";
                    }
                    else
                    {
                        keys = keys + "[" + Array_Keys[i].Trim() + "])";
                    }
                }

                string values="";
                for (int i = 0; i < Array_Vaules.Length; i++)
                {
                    if (i <= Array_Vaules.Length - 2)
                    {
                        values = values + "'" + Array_Vaules[i].Trim() + "',";
                    }
                    else
                    {
                        values = values + "'" + Array_Vaules[i].Trim() + "'";
                    }
                }
                
                string update_sql = "insert into [" + Table_Name + "] "+keys + "values(" + values + ")";
                SqlCommand mycom = new SqlCommand(update_sql, mycon);
                mycom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mycon.Close();
            }
        }

3.实现数据库的删除记录

方法引用：

  DeleteDB("Hyson_Staff", "Table_Staff_Info", label_ID.Text);

方法实现：

 　　　　/// <summary>
        /// 实现数据库的删除记录
        /// </summary>
        /// <param name="DB_Name">数据库名称</param>
        /// <param name="Table_Name">表名</param>
        /// <param name="id">主键（数字主键ID）</param>
        private void DeleteDB(string DB_Name, string Table_Name,string id)
        {
            string constr = @"Server=DESKTOP-K1D8VOK\HYSON_STOCK_V01;user=sa;pwd=sa;database=" + DB_Name;
            SqlConnection mycon = new SqlConnection(constr);

            try
            {
                mycon.Open();
                string delete_sql = "delete top(1) from " + Table_Name + " where id=" + id;
                SqlCommand mycom = new SqlCommand(delete_sql, mycon);
                mycom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mycon.Close();
            }
        }

4.实现数据库的修改记录

方法引用：

　　　　　　　　　　　　string ID = (label_ID.Text);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "Name", textBox_Name.Text, ID);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "Gender", textBox_Gender.Text, ID);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "Department", textBox_Department.Text, ID);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "BrithDay", textBox_BrithDay.Text, ID);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "JoinTime", textBox_JoinTime.Text, ID);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "LeaveTime", textBox_LeaveTime.Text, ID);
                    UpdateDB("Hyson_Staff", "Table_Staff_Info", "IsWork", checkBox_IsWork.Checked.ToString(), ID);

方法实现：

        /// <summary>
        /// 修改数据库数据
        /// </summary>
        /// <param name="DB_Name">数据库名称</param>
        /// <param name="Table_Name">表名</param>
        /// <param name="Key">待修改键</param>
        /// <param name="NewValue">数值</param>
        /// <param name="Update_ID">当前记录的主键</param>
        private void UpdateDB(string DB_Name, string Table_Name, string Key, string NewValue, string Update_ID)
        {
            string constr = @"Server=DESKTOP-K1D8VOK\HYSON_STOCK_V01;user=sa;pwd=sa;database=" + DB_Name;
            SqlConnection mycon = new SqlConnection(constr);

            try
            {
                mycon.Open();
                string update_sql = "update " + Table_Name + " set " + Key + "='" + NewValue + "' where id=" + Update_ID;
                SqlCommand mycom = new SqlCommand(update_sql, mycon);
                mycom.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mycon.Close();
            }
        }

5.实现数据库的修改记录

方法引用：（将查询到的内容，填充到dataGridView1中）

dataGridView1.DataSource = SelectDB("Hyson_Staff", "Table_Staff_Info", comboBox_SelectKey.Text, comboBox_Relationship.Text,comboBox_SelectValue.Text).Tables[0];

方法实现：

　　　　 /// <summary>
        /// 基于Select..Where..的数据库查询
        /// </summary>
        /// <param name="DB_Name">数据库名</param>
        /// <param name="Table_Name">表名</param>
        /// <param name="SelectKey">待查询的键</param>
        /// <param name="Relationship">关系（大于，小于，等于）</param>
        /// <param name="SelectValue">待查询的值</param>
        /// <returns>返回类型DataSet</returns>
        private DataSet SelectDB(string DB_Name, string Table_Name,string SelectKey,string Relationship,string SelectValue)
        {
            string constr = @"Server=DESKTOP-K1D8VOK\HYSON_STOCK_V01;user=sa;pwd=sa;database=" + DB_Name;
            //string constr = @"Server=192.168.10.123;user=sa;pwd=sa;database=" + DB_Name;
            SqlConnection mycon = new SqlConnection(constr);
            DataSet myds = new DataSet();

            try
            {
                mycon.Open();
                string sql = "select * from " + Table_Name + " where (" + SelectKey + Relationship+"'" + SelectValue + "')";
                SqlDataAdapter myda = new SqlDataAdapter(sql, mycon);
                myda.Fill(myds, "Table_Staff_Info");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                mycon.Close();
            }
            return myds;
        }