数据库访问及其基本操作(sql server和添加删除查找替换)

(一)访问

方法一

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();//返回数据库中受影响的行数