C#中对sqlserver进行增删改查

• 添加引用

using System.Data;using System.Data.SqlData;
1
2
1
2

• 建立于数据库的连接。

string sqlconnection = "data source = "注解1";database = 注解2;uid = "注解3";pwd ="注解4"";（其实还有很多种写法）参考：http://www.cnblogs.com/delphinet/archive/2011/06/24/2088765.html
1
2
1
2

• 经常用到的对象有：SqlConnection，SqlAdapter，SqlCommand、Dataset、DataGrid和 DataReader等，以SqlConnection，SqlAdapter，SqlCommand、Dataset、DataGrid对象，操作 SQL的实例数据库Northwind中的Categorys表为例说明（假定数据库在本地，数据库访问的用户名为user，密码为123456）： 
  接下来做一下四个操作的实现

• 查询数据：

string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;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();//将数据表格用数据集中的数据填充
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12

• 添加数据

string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;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){Console.WriteLine("{0} Exception caught.", ex);}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 修改数据

string categoryName=TextBox2.Text;string categoryDescription=TextBox3.Text;string MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;Trusted_Connection=no";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){Console.WriteLine("{0} Exception caught.", ex);}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 删除数据

string  MyConn="server=127.0.0.1;uid=user;pwd=123456;database=Northwind;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){Console.WriteLine("{0} Exception caught.", ex);}