1. 添加引用
using System.Data
using System.Data.SqlData
string sqlconnection = "data source = "注解1";database = 注解2;uid = "注解3";pwd ="注解4"";(其实还有很多种写法)
参考:http:
- 经常用到的对象有:SqlConnection,SqlAdapter,SqlCommand、Dataset、DataGrid和 DataReader等,以SqlConnection,SqlAdapter,SqlCommand、Dataset、DataGrid对象,操作 SQL的实例数据库Northwind中的Categorys表为例说明(假定数据库在本地,数据库访问的用户名为user,密码为123456):
接下来做一下四个操作的实现
2. 查询数据:
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();
3. 添加数据
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)
}
4. 修改数据
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);
}
5. 删除数据
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);
}