C#访问SQLServer以及数据操作.doc

C#访问SQL Server以及数据操作C# 2010-01-14 14:59:43 阅读594 评论1 字号：大中小 订阅a. 和数据库连接问题（感觉和SQL Server 2000的连接配置有很大不同，据说是为了增强安全性方面的考虑） Disable 防火墙我们假定在4（机器名为Andrew）上安装了SQL Server 2005 Standardard Editioin，数据库的实例名为Andrew（在安装的时候确定。在安装的时候也可以选择为确省的）。在该数据库服务器上，建立一个叫Flora的数据库，在这个数据库中建立一个叫Customer的数据表：CustomerID int not nullCustomerName varchar(50) not nullCompany varchar(50) not null其中CustomerID是Primary Key。在其中加入3条记录：1 Patrick PNFTechnologies, Inc.2 Flora PNFTechnologies, Inc.3 Andrew Born Global Kindergarten该机器安装的操作系统为Windows XP SP2，确省的有XP自带的防火墙。那么下面的方式配置客户端连接，在数据库服务器上的防火墙未被关闭时，连接将是失败的，如果关闭服务器上的防火墙则测试连接成功：上图是在ASP.NET的设计界面中，增加一个SqlDataSource，然后点击其上的配置数据库得到对话框）。点击确定，如果选择的是Provider是OleDB，那么连接串将有如下形式：“Provider=SQLOLEDB;Data Source=AndrewAndrew;Password=sysadmin123;User ID=sa;Initial Catalog=Flora”如果选择的Provider是SqlClient的话，那么连接串的形式为：“Data Source=4Andrew;Initial Catalog=Flora;User ID=sa;Password=sysadmin123”当然也可以直接使用IP地址的方式，如下图所示：那么相应的连接串分别为：如果Provider是OleDB:“Provider=SQLOLEDB;Data Source=4Andrew;Password=sysadmin123;User ID=sa;Initial Catalog=Flora” 如果Provider是SqlClient:“Data Source=4Andrew;Initial Catalog=Flora;User ID=sa;Password=sysadmin123” Enable 防火墙在这种情况下一般要使用别名（Alias）的机制。即在客户端使用SQL Server Configuration Manager建立一个数据库服务器的别名：上图中我们建立了一个别名，其属性如下：别名： Andrew端口号： 1443服务器： 4Andrew协议： TCP/IP 现在我们要在服务器上设定数据库服务器的监听端口，使其也为1443（见IPALL部分）。然后我们打开数据库服务器上的防火墙（在cmd命令行中运行firewall.cpl）的端口1443，如下图：这样SQL Server客户端就可以通过防火墙访问服务器了。 我们现在在客户端添加数据库服务器的连接如下图所示：点击”测试连接”按钮，证明是成功，点击”确定”按钮，我们得到的连接串为：“Data Source=Andy;Initial Catalog=Flora;User ID=sa;Password=sysadmin123” 下面我们在VS2005中使用CSharp建立一个Console项目，代码如下：using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient; namespace AccessDatabase class Program static void Main(string args) /string source = Data Source=4Andrew;Initial Catalog=Flora;User ID=sa;Password=sysadmin123; /上面的连接串也是OK的 string source = Data Source=Andy;Initial Catalog=Flora;User ID=sa;Password=sysadmin123; string select = SELECT CustomerName, Company FROM Customer; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read() Console.WriteLine(Contact : 0, -20 Company : 1, reader0, reader1); conn.Close(); Console.Read(); 运行上面程序，发现结果是正确的，输出如下：Patrick PNFTechnologies, Inc.Flora PNFTechnologies, Inc.Andrew Born Global Kindergarten b. 数据库连接关闭的问题确保数据库连接被关闭的方法一：象下面的例子使用trycatchfinally：try / Open the connection conn.Open() / Do something usefulcatch(Exception ex) / Do something about the exceptionfinally / Ensure that the connection is freed conn.Close(); 确保数据库连接被关闭的方法二：下面的程序代码说明了如何使用using语句来确保事先了IDisposable的对象在程序块退出后，将被立即清除。string source = “server=(local)NetSDK; integrated security=SSPI; database=Northwind”using(SqlConnection conn = new SqlConnection(source) conn.Open(); / Do something useful在上面的例子中，using语句将确保数据库连接被关闭，不管上述程序块是如何退出的（比如Exception）。 确保数据库连接被关闭的方法三： The Best Practice is: try using( SqlConnection conn = new SqlConnection(source) / Open the connection conn.Open(); / Do something useful / Close it myself conn.Close();catch(Exception e) / Do something with the exception here c. 交易 string source = “server=(local)NetSDK;” + “integrated security=SSPI;” + “database=Northwind” SqlConnection conn = new SqlConnection(source); conn.Open(); SqlTransaction tx = conn.BeginTransaction(); / Execute some commands, then commit the transaction tx.Commit(); conn.Close(); 交易有4个隔离级别： ReadCommitted（确省的） ReadUncommitted RepeatableRead Serializable d. SQL命令string source = “server=(local)NetSDK;” + “integrated security=SSPI;” + “database=Northwind” string select = “SELECT ContactName, CompanyName FROM Customers”; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); 上面带下划线的部分就是SQL命令。SqlCommand有一个CommandType属性非常重要，它被用来确定命令是一条普通的语句，或是调用存储过程，或是整表语句（将一个给定的数据表中的所有的记录都select出来）： CommandType 例子 Text(default) string select = “SELECT ContacName FROM Customers”; SqlCommand cmd = new SqlCommand(select, conn); = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + StoredProcedure SqlCommand cmd = new SqlCommand(“CustOrderHist”, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameeters.Add(“CustomerID”, “QUICK”); = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + = + TableDirect OleDbCommand cmd = new OleDbCommand(“Categories”, conn); cmd.CommandType = CommandType.TableDirect; 说明：TableDirect命令类型只是针对OleDb provider而言的。 e. 执行命令 在定义好SQL命令后，有需要执行它。根据需要返回的结果，你可以选择不同的方法来执行命令。 ExecuteNonQuery()执行命令，返回受该命令影响的记录的行数。这个方法通常被用在UPDATE, INSERT或者DELETE语句中。下面是例子：using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient; namespace AccessDatabase class Program static void Main(string args) string source = server=(local)NetSDK; + integrated security=SSPI; + database=Northwind; string select = UPDATE Customers SET ContactName = Bob WHERE ContactName = Bill; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); int rowsReturned = cmd.ExecuteNonQuery(); Console.WriteLine(0 rows returned., rowsReturned); conn.Close(); Console.Read(); ExecuteNonQuery()返回的是这条命令所影响的记录的数量(int) ExecuteReader() 执行命令，同时返回一个IDataReader类型的结果。这个方法根据不同的provider返回一个IDataReader对象，该对象可包含返回的记录。下面是例子程序：using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient; namespace AccessDatabase class Program static void Main(string args) string source = server=(local)NetSDK; integrated security=SSPI; database=Northwind; string select = SELECT ContacName, CompanyName FROM Customers; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read() Console.WriteLine(Contact : 0, -20 Company : 1, reader0, reader1); conn.Close(); Console.Read(); ExecuteScalar() 执行命令并返回一个标量值。在需要返回如一个数据表里的记录行数、最大值、最小值、平均值或者数据库服务器上的日期或时间，就需要用到这个方法。下面是一个例子程序：using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient; namespace AccessDatabase class Program static void Main(string args) string source = server=(local)NetSDK; integrated security=SSPI; database=Northwind; string select = SELECT COUNT(*) FROM Customers; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); object o = cmd.ExecuteScalar(); Console.WriteLine(o); conn.Close(); Console.Read(); ExecuteXmlReader() 仅针对SqlCommand类。执行命令并返回一个XmlReader对象。有下面三个不同的选项： FOR XML AUTO：Builds a tree based on the tables in the FROM clause. FOR XML RAW: Maps result set rows to elements, with columns mapped to attributes. FOR XML EXPLICIT: Requires that you specify the shape of the XML tree to be returned. 下面是个例子程序：using System;using System.Collections.Generic;using System.Text;using System.Data.SqlClient;using System.Xml; namespace AccessDatabase class Program static void Main(string args) string source = server=(local)NetSDK; integrated security=SSPI; database=Northwind; string select = SELECT COUNT(*) FROM Customers FOR XML AUTO; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); XmlReader xr = cmd.ExecuteXmlReader(); do s = xr.ReadOuterXml(); if (s != ) Console.WriteLine(s); while(s != ); conn.Close(); Console.Read(); f. 执行存储过程 不返回结果。假定存储过程定义如下： CREATE PROCEDURE RegionUpdate (RegionID INTEGER, RegionDescription NCHAR(50) AS SET NOCOUNT OFF UPDATE Region SET RegionDescription = RegionDescription WHERE RegionID = RegionID GO 要执行这个存储过程，大致的方法如下： SqlCommand aCommand = new SqlCommand(RegionUpdate, conn); aCommand.CommandType = CommandType.StoredProcedure; aCommand.Parameters.Add(new SqlParameter(RegionID, SqlDbType.Int, 0, RegionID);aCommand.Parameters.Add(new SqlParameter(RegionDescription, SqlDbType.NChar, 50, RegionDescription); aCommand.UpdatedRowSource = UpdateRowSource.None; 以上即把相关的命令定义好了，下面就执行： aCommand.Parameters0.Value = 999; aCommand.Parameters1.Value = South Western England; aCommand.ExecuteNonQuery(); 带返回参数。假定存储过程定义如下： CREATE PROCEDURE RegionInsert(RegionDescription NCHAR(50), RegionID INTEGER OUTPUT) AS SET NOCOUNT OFF SELECT RegionID = MAX(RegionID) + 1; FROM Region INSERT INTO Region(RegionID, RegionDescription) VALUES(RegionID, RegionDescription) GO 要执行这个存储过程，大致方法如下： 上面定义好了命令，下面执行： g. DataReader DataReader是从数据原select数据最简单同时也是最快的方式。但是不能直接实例化一个DataReader对象。 OleDbConnection conn = new OleDbConnection(source); Conn.Open(); OleDbCommand cmd = new OleDbCommand(select, conn); OleDbDataReader aReader = cmd.ExecuteReader(); while(aReader.Read() aReader.Close(); conn.Close(); 注意：OleDbDataReader将一直保持和数据库的连接直到被显式关闭。 Provider=SQL Server Provider=Other including SQL Server SqlConnection OleDbConnection SqlCommand OleDbCommand SqlDataReader OleDbDataReader h. 操作XML可以使用MSXML6.0对XML文档进