一、整体配置：“增、删、改、查”四操作
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.SqlClient;namespace SQL_Operate{class Dao{SqlConnection con;public SqlConnection connect(){string str = "server=DESKTOP-UU260NN;uid=sa;pwd=Zw9898;database=test;";con = new SqlConnection(str);con.Open();return con;}public SqlCommand command(string sql){SqlCommand cmd = new SqlCommand(sql, connect());return cmd;}public int Execute(string sql){return command(sql).ExecuteNonQuery();}public SqlDataReader read(string sql){return command(sql).ExecuteReader();}public void DaoClose(){con.Close();}}}
查询【额外做法】
string constr = "Server=数据名;uid=用户名;pwd=密码;DataBase=Management";// 建立SqlConnection对象SqlConnection con = new SqlConnection(constr);con.Open();//执行con对象的函数，返回一个SqlCommand类型的对象SqlCommand cmd = con.CreateCommand();//把输入的数据拼接成sql语句，并交给cmd对象mandText = "select*from tb_User";SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()){//构建一个ListView的数据，存入数据库数据，以便添加到listView1的行数据中ListViewItem lt = new ListViewItem();//将数据库数据转变成ListView类型的一行数据 lt.Text = dr["UserName"].ToString().Trim();lt.SubItems.Add(dr["Userpwd"].ToString().Trim());lt.SubItems.Add(dr["Note"].ToString().Trim());listView1.Items.Add(lt);}dr.Close();con.Close();
二、C#操作SQL常用对象有SqlConnection，SqlAdapter，SqlCommand、Dataset、DataGrid和DataReader等。
1.新建数据代码：
string MyConn="server=数据名;uid=用户名;pwd=密码;database=数据库名;Trusted_Connection=no";SqlConnection MyConnection=new SqlConnection(MyConn);string MyInsert="insert into Categories(CategoryName, Description)values('"+Convert.ToString(TextBox2.Text)+"','"+Convert.ToString(TextBox3.Text)+"')";SqlCommand MyCommand=new SqlCommand(MyInsert,MyConnection);try{MyConnection.Open();MyCommand.ExecuteNonQuery();MyConnection.Close();}catch(Exception ex){MessageBox.Show(ex.Message);}
2.修改数据代码:
string MyConn="server=数据名;uid=用户名;pwd=密码;database=数据库名;Trusted_Connection=no";string categoryName=TextBox2.Text;string categoryDescription=TextBox3.Text;SqlConnection MyConnection=new SqlConnection(MyConn);string MyUpdate="Update Categories set CategoryName='"+categoryName+"',Description='"+categoryDescription+"' where CategoryID="+TextBox1.Text;SqlCommand MyCommand=new SqlCommand(MyUpdate,MyConnection);try{MyConnection.Open();MyCommand.ExecuteNonQuery();MyConnection.Close();TextBox1.Text="";}catch(Exception ex){MessageBox.Show(ex.Message);}
3.查询数据代码：
string MyConn="server=数据名;uid=用户名;pwd=密码;database=数据库名;Trusted_Connection=no";//定义数据库连接参数SqlConnection MyConnection=new SqlConnection(MyConn);//定义一个数据连接实例SqlCommand MyCommand=new SqlCommand("SELECT CategoryID, CategoryName, Description FROM Categories",MyConnection); //定义一个数据库操作指令SqlDataAdapter SelectAdapter=new SqlDataAdapter();//定义一个数据适配器SelectAdapter.SelectCommand=MyCommand;//定义数据适配器的操作指令DataSet MyDataSet=new DataSet();//定义一个数据集MyConnection.Open();//打开数据库连接SelectAdapter.SelectCommand.ExecuteNonQuery();//执行数据库查询指令MyConnection.Close();//关闭数据库SelectAdapter.Fill(MyDataSet);//填充数据集DataGrid1.DataSource=MyDataSet;DataGrid1.DataBind();//将数据表格用数据集中的数据填充
4.删除数据代码：
string MyConn="server=数据名;uid=用户名;pwd=密码;database=数据库名;Trusted_Connection=no";SqlConnection MyConnection=new SqlConnection(MyConn);string MyDelete="Delete from Categories where CategoryID="+TextBox1.Text;SqlCommand MyCommand=new SqlCommand(MyDelete,MyConnection);try{MyConnection.Open();MyCommand.ExecuteNonQuery();MyConnection.Close();TextBox1.Text="";}catch(Exception ex){MessageBox.Show(ex.Message)}
