ADO.NET简单总结

实现功能：窗体连接SQL Server数据库，并显示相应的数据库内容，同时也可以进行增删改查并实时显示

1、获取连接字符串

新建记事本，改后缀名为.udl 然后根据测试相应的数据库

最后用记事本打开.udl文件 截取Integrated Security=SSPI后面所有信息，再添加测试数据库

例如：Integrated Security=SSPI;PersistSecurity Info=False;Initial Catalog=db_stuInfo;Data Source=DESKTOP-6H6M5FF;Database=Library（红色部分即为添加，注意不要掉前面的分号）

2、建立简单页面

更改所有控件命名，注意规范

3，写增删改查的类

注意：添加以下两个命名空间：

using System.Data;

using System.Data.SqlClient;

原理如图：

//SQL.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

namespace SQLServer

{

    class SQL

    {

        static stringconnectString = "Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=db_stuInfo;DataSource=DESKTOP-6H6M5FF;Database=db_stuInfo";

        SqlConnection conn =new SqlConnection(connectString);

        public voidconnectToDatabase()

        {

            conn.Open();

        }

        public voidcloseDatabase()

        {

            conn.Close();

        }

        /// <summary>

        /// 查询的语句返回给dt

        /// </summary>

        /// <paramname="sql"></param>

        ///<returns>DataTable</returns>

        public DataTableSelectSqlReturnDataTable(string sql)

        {

           SqlDataAdapter sda = new SqlDataAdapter(sql, conn);//相当于运货车

            DataTable dt = new DataTable();///相当于临时仓库

            sda.Fill(dt);//运货车将货倒进临时仓库等待别人来拿货

            return dt;

        }

/// <summary>

        /// 判断是否成功影响行

        /// </summary>

        /// <paramname="sql"></param>

        ///<returns></returns>

        public boolIsSuccess(string sql)

        {

            SqlCommand cmd =new SqlCommand(sql, conn);

            int i =cmd.ExecuteNonQuery();

            if (i > 0)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

    }

}

//frmMain.cs

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

namespace SQLServer

{

    public partial classfrmMain : Form

    {

        public frmMain()

        {

           InitializeComponent();

        }

        SQL publicsql = newSQL();

        private voidfrmMain_Load(object sender, EventArgs e)

        {

            string sql ="select * from dbo.tb_student";

           publicsql.connectToDatabase();

            DataTable dt =publicsql.SelectSqlReturnDataTable(sql);

           this.dgvshowdata.DataSource = dt;

           publicsql.closeDatabase();

        }

        /// <summary>

        /// 查询

        /// </summary>

        /// <paramname="sender"></param>

        /// <paramname="e"></param>

        private voidbtnQuery_Click(object sender, EventArgs e)

        {

            string tbname =this.tbName.Text.ToString();

            string tbsex =this.tbSex.Text.ToString();

            string tbage=  this.tbAge.Text.ToString();

            string tbclass =this.tbClass.Text.ToString();

            if(tbname ==string.Empty && tbsex == string.Empty && tbage==string.Empty&& tbclass == string.Empty )

            {

               MessageBox.Show("至少要填一项");

            }

            else

            {

                string sql ="select * from dbo.tb_student where name ='"+tbname+"' orage='"+tbage+"' or sex='"+tbsex+"' orclass='"+tbclass+"'";

               publicsql.connectToDatabase();

                DataTable dt =publicsql.SelectSqlReturnDataTable(sql);

               this.dgvshowdata.DataSource = dt;

               publicsql.closeDatabase();

            }

        }

        /// <summary>

        /// 增加

        /// </summary>

        /// <paramname="sender"></param>

        /// <paramname="e"></param>

        private voidbtnAdd_Click(object sender, EventArgs e)

        {

           

string tbname = this.tbName.Text.ToString();

            string tbsex =this.tbSex.Text.ToString();

            string tbage =this.tbAge.Text.ToString();

            string tbclass =this.tbClass.Text.ToString();

            if (tbname ==string.Empty || tbsex == string.Empty || tbage == string.Empty || tbclass ==string.Empty)

            {

               MessageBox.Show("要全部填完整");

            }

            else

            {

                string sql ="insert into dbo.tb_student values('" + tbname + "' ,'" +tbsex + "' ,'" + tbage + "' ,'" + tbclass + "')";

               publicsql.connectToDatabase();

                DataTable dt =publicsql.SelectSqlReturnDataTable(sql);

               this.dgvshowdata.DataSource = dt;

               publicsql.closeDatabase();

               frmMain_Load(sender, e);

            }

        }

        /// <summary>

        /// 删除

        /// </summary>

        /// <paramname="sender"></param>

        /// <paramname="e"></param>

        private voidbtnDelete_Click(object sender, EventArgs e)

        {

            string tbname =this.tbName.Text.ToString();

            if (tbname ==string.Empty){

               MessageBox.Show("请填写名字");

            }

            else

            {

                string sql ="delete from dbo.tb_student where name='"+tbname+"'";

               publicsql.connectToDatabase();

               

               

                try

                {

                    if(publicsql.IsSuccess(sql))

                    {

                        MessageBox.Show("删除成功");

                    }

                    else

                    {

                        MessageBox.Show("删除失败");

                    }

                }

                catch(Exception ex)

                {

                   MessageBox.Show(ex.Message);

                }

               publicsql.closeDatabase();

            }

           frmMain_Load(sender, e);

        }

        /// <summary>

        /// 更改

        /// </summary>

        /// <paramname="sender"></param>

        /// <paramname="e"></param>

        private voidbtnUpdate_Click(object sender, EventArgs e)

        {

            string tbname =this.tbName.Text.ToString();

            string tbsex =this.tbSex.Text.ToString();

            string tbage =this.tbAge.Text.ToString();

            string tbclass =this.tbClass.Text.ToString();

            if (tbname ==string.Empty || tbsex == string.Empty || tbage == string.Empty || tbclass ==string.Empty)

            {

               MessageBox.Show("要全部填完整");

            }

            else

            {

                string sql ="update dbo.tb_student setsex='"+tbsex+"',age='"+tbage+"', class='"+tbclass+"' where name='" + tbname + "'";

               publicsql.connectToDatabase();

               

                try

                {

                    if(publicsql.IsSuccess(sql))

                    {

                        MessageBox.Show("更改成功");

                    }

                    else

                    {

                        MessageBox.Show("更改失败");

                    }

                }

                catch(Exception ex)

                {

                   MessageBox.Show(ex.Message);

                }

               publicsql.closeDatabase();

            }

           frmMain_Load(sender, e);

        }

    }