Beginning C# 2008 Databases 笔记（二）

Chapter 5 Manipulating Database Data
熟练操作数据库数据

--select into:有#临时创建表 没#永久
select <column1>,<column2>
into #<table2>    --#
from <table1>

-- INSERT 语法
INSERT INTO <table>
(<column1>, <column2>, , <columnN>)
VALUES (<value1>, <value2>, , <valueN>)

-- UPDATE 语法
UPDATE <table>
SET <column1> = <value1>, <column2> = <value2>,
 , <columnN> = <valueN>
WHERE <predicate>

-- DELETE 语法
DELETE FROM <table>
WHERE <predicate>

Chapter 6 Using Stored Procedures
使用存储过程
存储过程是由一些sql语句和控制语句组成的被封装起来的过程。
约定：使用 sp_前缀的存储过程使用的是 SQL，xp_前缀使用的是其它。
但是最好别以 sp_前缀建立存储过程，因为SQL Server遇到 sp_前缀会总是会先去系统数据库中的 master 数据库中查找，然后才会去用户的数据库中查找。因此若重名就不会执行用户定义的存储过程了。
我想可能还考虑到SQL Server这样做会降低速度吧。

--创建一个简单的存储过程
-- as 后面是查询语句
create procedure sp_Select_All_Employees
as 
  select employeeid from employees
--执行
execute sp_Select_All_Employees

带输入参数和输出参数的例子，参数的缺省值可以设定。

--创建
create procedure sp_Orders_By_EmployeeId2
@employeeid int,               --输入参数
@ordercount int = 0 output --输出参数
as
select orderid,customerid
  from orders where employeeid = @employeeid;--分号
select @ordercount = count(*)
  from orders where employeeid = @employeeid
return @ordercount

--执行
Declare @return_value int,
@ordercount int
Execute @return_value=sp_Orders_By_EmployeeId2
@employeeId=2,    --传值
@ordercount=@ordercount output
Select @ordercount as '@ordercount'
Select 'Return value' =@return_value

修改存储过程使用 ALTER PROCEDURE 声明；
Drop procedure 声明为删除；
sp_helptext 查看存储过程的定义，参数；
sp_rename 作用为重命名。

Alter procedure sp_XXX as <new query>
  Drop procedure sp_XXX
Execute sp_helptext 'sp_XXX'--单引号
Execute sp_rename 'sp_Name', 'sp_NewName'

C#应用demo

demo6.2
using System;
using System.Data;
using System.Data.SqlClient;

namespace Chapter6
{
   class CallSp2
   {
      static void Main()
      {

         // create connection
         SqlConnection conn = new SqlConnection(@"
            server = .\sqlexpress;
            integrated security = true;
            database = northwind
         ");
            try
            {
                // open connection
                conn.Open();

                // create command
                SqlCommand cmd = conn.CreateCommand();

                // 指定执行的存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_orders_by_employeeid2";

                // 创建输入参数
                SqlParameter inparm = cmd.Parameters.Add(
                "@employeeid", SqlDbType.Int);
                inparm.Direction = ParameterDirection.Input;
                inparm.Value = 2;

                // 创建输出参数
                SqlParameter ouparm = cmd.Parameters.Add(
                "@ordercount", SqlDbType.Int);
                ouparm.Direction = ParameterDirection.Output;

                // 创建返回值参数
                SqlParameter retval = cmd.Parameters.Add(
                "return_value", SqlDbType.Int);
                retval.Direction = ParameterDirection.ReturnValue;

                // execute command
                SqlDataReader rdr = cmd.ExecuteReader();

               // Process the result set
                 while (rdr.Read())
                 {
                    Console.WriteLine(
                    "{0} {1}"
                    , rdr[0].ToString().PadRight(5)
                    , rdr[1].ToString());
                 }
                 rdr.Close();
              
               // display output parameter value
               Console.WriteLine(
               "The output parameter value is {0}"
             , cmd.Parameters["@ordercount"].Value);

              // display return value
              Console.WriteLine(
               "The return value is {0}"
             , cmd.Parameters["return_value"].Value);
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();
            }
      }
   }
}

Chapter 7 Using XML
Converting Relational Data to XML 的4种模式：
• FOR XML RAW
• FOR XML AUTO
• FOR XML PATH
• FOR XML EXPLICIT

FOR XML RAW 模式把查询结果的每一行转换为对应的一个XML元素。查询结果中的列名作为属性被添加到行元素中。
The FOR XML RAW mode transforms each row in the query result set into an XML element
identified as row for each row displayed in the result set. Each column name in the SELECT
statement is added as an attribute to the row element while displaying the result set.

--默认为以属性为中心 Attribute Centric
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID between 98 and 101
FOR XML RAW

结果
<row ProductModelID="98" Name="Chain" />
<row ProductModelID="99" Name="LL Crankset" />
<row ProductModelID="100" Name="ML Crankset" />
<row ProductModelID="101" Name="HL Crankset" />

--('ProductModelDetail')：重命名row为ProductModelDetail
--ELEMENTS 表示 Element Centric 以元素为中心
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID between 98 and 101
FOR XML RAW ('ProductModelDetail'),ELEMENTS

结果
<ProductModelDetail>
  <ProductModelID>98</ProductModelID>
  <Name>Chain</Name>
</ProductModelDetail>
<ProductModelDetail>
  <ProductModelID>99</ProductModelID>
  <Name>LL Crankset</Name>
</ProductModelDetail>
<ProductModelDetail>
  <ProductModelID>100</ProductModelID>
  <Name>ML Crankset</Name>
</ProductModelDetail>
<ProductModelDetail>
  <ProductModelID>101</ProductModelID>
  <Name>HL Crankset</Name>
</ProductModelDetail>

FOR XML AUTO 模式将查询结果转换为嵌套的 XML 元素。
它比较简单，没有再提供更多的控制手段。

SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader
OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO

结果
<Cust CustomerID="1" CustomerType="S">
  <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
  <OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
<Cust CustomerID="2" CustomerType="S">
  <OrderHeader CustomerID="2" SalesOrderID="46976" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="47997" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="49054" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="50216" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="51728" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="57044" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="63198" Status="5" />
  <OrderHeader CustomerID="2" SalesOrderID="69488" Status="5" />
</Cust>

用SQL SERVER创建一个存储XML的表：

create table xmltest
(
xid int not null primary key,
xdoc xml not null
)

插入xml
insert into xmltest
values(
1,
'
<states>
<state>
<abbr>CA</abbr>
<name>California</name>
<city>Berkeley</city>
<city>Los Angeles</city>
<city>Wilmington</city>
</state>
<state>
<abbr>DE</abbr>
<name>Delaware</name>
<city>Newark</city>
<city>Wilmington</city>
</state>
</states>
'
)
insert into xmltest
values(
2,
'
<states>
<state abbr="CA" name="California">
<city name="Berkeley"/>
<city name="Los Angeles"/>
<city name="Wilmington"/>
</state>
<state abbr="DE" name="Delaware">
<city name="Newark"/>
<city name="Wilmington"/>
</state>
</states>
'
)