(一)访问
方法一
1、创建连接对象
SqlConnection thisConnection = new SqlConnection(@"Server=./sqlexpress;Integrated Security = true;"+"Database=northwind");
SQL Server名称:Server=./sqlexpress
windows登录的集成安全:Integrated Security = true;还可以使用指定密码如:User = sa;PWD=secret;
使用的数据库:Database=northwind;
2、打开数据库连接
thisConnection.Open();
3、创建命令对象并给它提供SQL命令,执行数据库操作
SqlCommand thisCommand=thisConnection.CreateCommand(); thisConnection有创建命令的方法 得到命令对象
thisCommand.CommandText = "SELECT CustomerID,CompanyName from Customers";设置对数据的执行的SQL语句 从Customers中获取CustomerID和CompanyName;
4、读取数据
SqlDataReader thisReader = thisCommand.ExecuteReader();datareader是只读的,从命令的方法中读取生成结果给thisReader
5、显示数据
thisReader.Read();从read方法中读取一行数据 数据里包括CustomerID列和CompanyName列一行的值
Console.WriteLine("/t{0}/t{1}", thisReader["CustomerID"], thisReader["CompanyName"]);打出来
6、关闭打开的对象
thisReader.Close();
thisConnection.Close();
方法二
1、创建连接
SqlConnection thisConnection = new SqlConnection(@"Server=./sqlexpress;Integrated Security = true;"+"Database=northwind");
2、使用连接创建sqlDataAdapter对象
SqlDataAdapter thisDataAdapter = new SqlDataAdapter("SELECT CustomerID,ContactName FROM Customers", thisConnection);
3、创建DataSet
DataSet thisDataSet = new DataSet();
4、填充DataSet中DataTable
thisDataAdapter.Fill(thisDataSet,"Customers");//此处的customers并不是数据库中的customers,而是被填充的DataSet中DataTable的名称
5、对每个DataRow提取CustomerID和ContactName列的值
foreach(DataRow theRow in thisDataSet.Tables["Customers"].Rows)
{
Console.WriteLine(theRow["CustomerID"]+"/t"+theRow["ContactName"]);
}
6、关闭连接(此例中没有明确的打开和关闭连接 因为DataAdapter对象完成了这个工作 数据会在需要打开连接 在完成后关闭 DataAdapter不改变连接状态 开始工作到工作结束连接一直是打开的)
thisConnection.Close();
数据库操作
(二)操作(添加删除查找)(红色字体是显示与访问的区别)
1、添加
SqlConnection thisConnection = new SqlConnection(@"Server = ./sqlexpress;Integrated Security = true;" + "Database = northwind");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID,CompanyName FROM Customers", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
thisAdapter.Fill(thisDataSet,"Customers");
DataRow thisDataRow = thisDataSet.Tables["Customers"].NewRow();//建立一个新行
thisDataRow["CustomerID"] = "bbb";
thisDataRow["CompanyName"] = "ccc";//此处两列值 成sqldataAdapter选择的列相同,如要添加更多列值,需要在建立sqldataAdapter添加列
thisDataSet.Tables["Customers"].Rows.Add(thisDataRow);
thisAdapter.Update(thisDataSet, "Customers");
thisConnection.Close();
2、查找
SqlConnection thisConnection = new SqlConnection(@"Server = ./sqlexpress;Integrated Security = true;" + "Database = northwind");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID,CompanyName FROM Customers", thisConnection);
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
//thisAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;此处方法可替换绿色字程序段 效果相同 这是直接从数据库中加载主键 要在填充前完成
thisAdapter.Fill(thisDataSet,"Customers");
DataColumn[] keys = new DataColumn[1];
keys[0] = thisDataSet.Tables["Customers"].Columns["CustomerID"];
thisDataSet.Tables["Customers"].PrimaryKey = keys;//将主键设为CustomerID
DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("bbb");//查找CustomerID为bbb的行
if (findRow != null)
Console.WriteLine("{0}", thisDataSet.Tables["Customers"].Rows.IndexOf(findRow));
thisAdapter.Update(thisDataSet, "Customers");
thisConnection.Close();
3、删除
只需要在DataRow findRow = thisDataSet.Tables["Customers"].Rows.Find("bbb");后加入
findRow.Delete();
注:DataSet中添加删除,并不是在数据库表中直接操作,而是相当于在数据库表中标记,必须要配合DataAdapter.Update()才能真正添加删除数据库
4、查找替换(数据库大范围)
SqlConnection thisConnection = new SqlConnection(@"Server = ./sqlexpress;Integrated Security = true;"+"Database=northwind");
thisConnection.Open();
SqlCommand thisCommand = thisConnection.CreateCommand();
thisCommand.CommandText = "UPDATE Products SET UnitPrice = UnitPrice*1.05 WHERE SupplierID=12";//查找supplierID=12的记录 替换UnitPrice = UnitPrice*1.05
int rowsAffected = thisCommand.ExecuteNonQuery();//返回数据库中受影响的行数