C-5-数据库入门指南-四-

布客飞龙
58 阅读47分钟

C#5 数据库入门指南（四）

原文：Beginning C# 5.0 Databases

协议：CC BY-NC-SA 4.0

十七、使用文本和二进制数据

有些类型的数据有特殊的格式，非常大，或者大小变化很大。在这里，我将向您展示处理文本和二进制数据的技术。在本章中，我将介绍以下内容:

  • Understanding SQL Server Text and Binary Data Types
  • Storing images in a database
  • Retrieve images from the database
  • Handling text data

我还将展示用于在tempdb数据库中创建表的 T-SQL，它旨在保存任何临时表。我将首先介绍哪些数据类型支持这些类型的数据。

了解 SQL Server 文本和二进制数据类型

SQL Server 提供了类型CHAR、NCHAR、VARCHAR、NVARCHAR、BINARY和VARBINARY，用于处理相当小的文本和二进制数据。您可以将这些用于最大 8，000 字节的文本(字符)数据(Unicode 数据为 4，000 字节，NCHAR和NVARCHAR，每个字符使用 2 个字节)。

对于较大的数据，SQL Server 2012 称之为大值数据类型，您应该使用VARCHAR(MAX)、NVARCHAR(MAX)和VARBINARY(MAX)数据类型。VARCHAR(MAX)用于非 Unicode 文本，NVARCHAR(MAX)用于 Unicode 文本，VARBINARY(MAX)用于图像和其他二进制数据。

Image 警告在 SQL Server 2000 中，使用NTEXT、TEXT和IMAGE数据类型存储大量数据。这些数据类型已被弃用，并且在 SQL Server 的较新版本中已被删除。如果您使用遗留应用，您应该考虑将NTEXT、TEXT和IMAGE分别转换为NVARCHAR(MAX)、VARCHAR(MAX)和VARBINARY(MAX)。然而，System.Data.SqlDbType枚举还不包括这些数据类型的成员，所以我们对列数据类型使用VARCHAR(MAX)和VARBINARY(MAX)，但是在为命令参数指定数据类型时使用Text和Image。

使用这些数据类型的替代方法是不将数据本身存储在数据库中，而是定义一个包含指向数据实际存储位置的路径的列。这对于访问大量数据更有效，并且通过将需求转移到文件服务器可以节省数据库服务器上的资源。它确实需要更复杂的协调，并且有可能导致数据库和数据文件不同步。我不会在这一章使用这种技术。

Image 提示如果您正在使用一个不能超过 4GB 的 SQL Server Express 数据库，或者如果您不希望您的数据库存储大量信息并超过一定的大小限制，那么对于非常大的文本和图像数据，使用 SQL Server 提供的文本和二进制数据类型可能是您唯一的选择。

在 C# 程序中，二进制数据类型映射到字节数组(byte[])，字符数据类型映射到字符串或字符数组(char[])。

Image 注意 DB2、MySQL、Oracle、SQL 标准调用此类数据类型大型对象(LOBs)；具体来说，它们是二进制大对象(BLOBs)和字符大对象(CLOBs)。但是，与许多数据库术语一样，BLOB 最初是否是任何事物的首字母缩略词仍有争议。不用说，它一直隐含着一种可以处理大量(无定形)数据的数据类型，SQL Server 文档使用 BLOB 作为大数据和数据类型的通称。

将图像存储在数据库中

让我们首先创建一个用于存储图像的数据库表，然后将一些图像加载到其中。我们将使用小图像，但使用VARBINARY(MAX)来存储它们。在示例中，我将演示如何使用代码目录路径C:\VidyaVrat\C#2012 and SQL 2012\Chapter17\Code中的图像；您可以使用计算机上一些图像所在位置的路径。

试试看:从文件加载图像二进制数据

在本例中，您将编写一个程序来创建一个数据库表，然后在其中加载和存储图像。

  1. 创建一个名为 Chapter17 的新 Windows 窗体应用项目。当解决方案资源管理器打开时，保存解决方案。

  2. 将第十七章项目重命名为文本和二进制数据。将Form1.cs文件重命名为LoadImages.cs。通过单击窗体的标题栏选择 LoadImages 窗体，并将 Size 属性的宽度设置为 439，高度设置为 178。

  3. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此 TextBox 控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtLoadImages。
    • 对于 Location 属性，将 X 设置为 12，将 Y 设置为 12。
    • 将 Multiline 属性设置为 True。
    • 对于“大小”属性，将“宽度”设置为 401，将“高度”设置为 117。
    • 将文本属性留空。

  4. Now your LoadImages form in the Design view should look like Figure 17-1. Image

    ***图 17-1。*LoadImages 表单的设计视图

  5. Navigate to Solution Explorer, select the LoadImages.cs form, right-click, and select View Code; this will take you to the code editor window. Add the code to LoadImages.cs shown in Listing 17-1.

    ***清单 17-1。*T4LoadImages.cs

    `using System.Data;        using System.Data.SqlClient;        using System.IO;         // change this path to the location of image in your computer         string imageFileLocation = @"C:\VidyaVrat\C#2012 and SQL 2012\Chapter17\Code";

            string imageFilePrefix = "SpaceNeedle";         string imageFileType = ".jpg";

            int numberImageFiles = 1;         int maxImageSize = 10000;

            SqlConnection conn = null;         SqlCommand cmd = null;

            private void LoadImages_Load(object sender, EventArgs e)         {            try             {                // Create connection                conn = new SqlConnection(@"server = .\sql2012;integrated security = true;                                       database = SQL2012Db"); // Open connection                conn.Open();

                   //Create command                cmd = new SqlCommand();                cmd.Connection = conn;

                    // Create table                 CreateImageTable();

                    // Prepare insert                 PrepareInsertImages();

                    // Loop for Inserting images                 for (int i = 1; i <= numberImageFiles; i++)                 {                     ExecuteInsertImages(i);                 }             }

                catch (SqlException ex)             {                 MessageBox.Show(ex.Message + ex.StackTrace);             }

                finally             {                 // Close connection                 conn.Close();                 txtLoadImages.AppendText(Environment.NewLine);                 txtLoadImages.AppendText("Connection Closed.");             }         }

            private void ExecuteCommand(string cmdText)         {             int cmdResult;             cmd.CommandText = cmdText;             //txtLoad.AppendText("Executing command:\n");            // txtLoad.AppendText(cmd.CommandText);             //txtLoad.AppendText(Environment.NewLine);             cmdResult = cmd.ExecuteNonQuery();         }

            private void CreateImageTable()         {             ExecuteCommand(@"if exists                               (select * from                                 INFORMATION_SCHEMA.TABLES                                  where TABLE_NAME = 'ImageTable')                                drop table ImageTable

                                   create table ImageTable                                (                                    ImageFile nvarchar(20),                                    ImageData varbinary(max)                                )");         }

            private void PrepareInsertImages()         {             cmd.CommandText = @"insert into ImageTable                                values (@ImageFile, @ImageData)";

                cmd.Parameters.Add("@imagefile", SqlDbType.NVarChar, 20);             cmd.Parameters.Add("@imagedata", SqlDbType.Image, 1000000);

                cmd.Prepare();         }

            private void ExecuteInsertImages(int imageFileNumber)         {             string imageFileName = null;             byte[] imageImageData = null;

                imageFileName = imageFilePrefix + imageFileNumber.ToString() + imageFileType;             imageImageData = LoadImageFile(imageFileName, imageFileLocation, maxImageSize);

                cmd.Parameters["@ImageFile"].Value = imageFileName;             cmd.Parameters["@ImageData"].Value = imageImageData;

                ExecuteCommand(cmd.CommandText);         }

            private byte[] LoadImageFile(string fileName,string fileLocation,int maxImageSize)         {             byte[] imagebytes = null;             string fullpath = fileLocation + fileName;             txtLoadImages.AppendText("Loading File:");             txtLoadImages.AppendText(Environment.NewLine);             txtLoadImages.AppendText(fullpath);             FileStream fs = new FileStream(fullpath, FileMode.Open, FileAccess.Read);             BinaryReader br = new BinaryReader(fs);             imagebytes = br.ReadBytes(maxImageSize);             txtLoadImages.AppendText(Environment.NewLine);

                txtLoadImages.AppendText("Imagebytes has length " +                     imagebytes.GetLength(0).ToString() + "bytes.");

                return imagebytes;          }`

  6. Build the project, and run the program by pressing Ctrl+F5. You should see output similar to that in Figure 17-2. It shows the information for loading an image into the database that you have on your computer at the specified location, and it shows the size of each image. Image

    ***图 17-2。*加载图像数据

  7. To see the image you have inserted into the database, open SQL Server Management Studio and run a SELECT query on the image table you have created in the SQL2012Db database, which was created in Chapter 3 (see Figure 17-3). Image

    ***图 17-3。*查看图像数据

它是如何工作的

在LoadImages.cs中，除了创建和打开一个连接之外，你要做三件主要的事情。

你连接到SQL2012Db，你在第三章中创建的数据库。

`     // Create connection      conn = new SqlConnection(@"server = .\sql2012;integrated security = true;                             database = SQL2012Db");

     // Open connection      conn.Open();`

您调用一个私有的类级方法来创建一个保存图像的表。

// Create table CreatelmageTable();

您调用一个私有的类级方法来准备一个命令(是的，您最终准备了一个命令，因为您期望多次运行它)来插入图像。

// Prepare insert Preparelnsertlmages();

然后循环遍历图像文件，并将它们插入到表格中。

// Loop for Inserting images for (int i = 1; i <= loader.numberlmageFiles; i++) {        ExecutelnsertImages(i); }

因为可能已经有一个表了，所以您必须先删除这个表(如果它存在的话),然后再创建它。该步骤在应用每次运行时重复。

当您创建一个包含图像文件名称和图像的简单表格时，您为imagedata列使用了VARBINARY(MAX)数据类型。

`        private void CreateImageTable()         {             ExecuteCommand(@"if exists                               (select * from                                 INFORMATION_SCHEMA.TABLES                                  where TABLE_NAME = 'ImageTable')

                               drop table ImageTable

                               create table ImageTable                                (                                    ImageFile nvarchar(20),                                    ImageData varbinary(max)                                )");                }`

但是当您配置INSERT命令时，您使用了SqlDbType枚举的Image成员，因为没有VARBINARY(MAX)数据类型的成员。您为两种可变长度数据类型都指定了长度，因为如果不这样做就无法准备命令。

private void PrepareInsertImages()         {             cmd.CommandText = @"insert into ImageTable                                values (@ImageFile, @ImageData)"; `            cmd.Parameters.Add("@imagefile", SqlDbType.NVarChar, 20);             cmd.Parameters.Add("@imagedata", SqlDbType.Image, 1000000);

            cmd.Prepare();         }`

Executelnsertlmages方法接受一个整数作为图像文件名的后缀，调用LoadlmageFile获得包含图像的字节数组，将文件名和图像分配给它们对应的命令参数，然后执行命令插入图像。

`         private void ExecuteInsertImages(int imageFileNumber)         {             string imageFileName = null;             byte[] imageImageData = null;

            imageFileName = imageFilePrefix + imageFileNumber.ToString() + imageFileType;             imageImageData = LoadImageFile(imageFileName, imageFileLocation, maxImageSize);

            cmd.Parameters["@ImageFile"].Value = imageFileName;             cmd.Parameters["@ImageData"].Value = imageImageData;

            ExecuteCommand(cmd.CommandText);         }`

LoadlmageFile方法读取图像文件，显示文件名和文件中的字节数，并以字节数组的形式返回图像。

`                 private byte[] LoadImageFile(string fileName,string fileLocation,int maxImageSize)         {             byte[] imagebytes = null;             string fullpath = fileLocation + fileName;             txtLoadImages.AppendText("Loading File:");             txtLoadImages.AppendText(Environment.NewLine);             txtLoadImages.AppendText(fullpath);             FileStream fs = new FileStream(fullpath, FileMode.Open, FileAccess.Read);             BinaryReader br = new BinaryReader(fs);             imagebytes = br.ReadBytes(maxImageSize);             txtLoadImages.AppendText(Environment.NewLine);

            txtLoadImages.AppendText("Imagebytes has length " +                   imagebytes.GetLength(0).ToString() + " bytes.");

            return imagebytes;         }`

从数据库中检索图像

现在您已经存储了一些图像，您将看到如何使用 Windows 窗体应用检索和显示它们。

试试看:显示储存的图像

要显示您存储的图像，请按照以下步骤操作:

  1. 选择文本和二进制数据项目，右键单击，并选择添加Image窗口窗体。在打开的对话框中，确保选中 Windows Form，并将Form1.cs重命名为DisplayImages.cs；单击“确定”将该表单添加到文本和二进制数据项目中。

  2. 选择 DisplayImages 窗体，并将 Size 属性的宽度设置为 330，高度设置为 332。

  3. 将 Label 控件拖到窗体上，并将其放在窗体的左上角。选择此标签控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 lblImageName。
    • 对于 Location 属性，将 X 设置为 12，将 Y 设置为 22。
    • 对于文本属性，设置为图像名称。

  4. 将 TextBox 控件拖到窗体上，并将其放置在 Label 控件旁边。选择此 TextBox 控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtImageName。
    • 对于“位置”属性，将 X 设置为 85，Y 设置为 22。
    • 将文本属性留空。

  5. 将 Button 控件拖到窗体上，并将其放置在 TextBox 控件的旁边。选择此按钮控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 btnShowImage。
    • 对于 Location 属性，将 X 设置为 277，将 Y 设置为 22。
    • 设置 Text 属性以显示图像。

  6. 将 PictureBox 控件拖到窗体上，并将其放在窗体的中央。选择此 PictureBox 控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 ptbImage。
    • 对于位置属性，将 X 设置为 44，将 Y 设置为 61。
    • 对于 Size 属性，将 Height 设置为 220，Width 设置为 221。

  7. Now your DisplayImages form in the Design view should look like Figure 17-4. Image

    ***图 17-4。*显示图像表单的设计视图

  8. Add a new class named Images to this Windows Form project. To add this, select the Text and Binary Data project, right-click, select Class… in the Add New Item dialog, name the class Images.cs, and click Add to have it listed under your project. Once it’s added, replace the code in Images.cs with the code in Listing 17-2.

    ***清单 17-2。*我mages.cs

    `using System.Data.SqlClient; using System.IO; using System.Drawing;

    namespace Text_and_Binary_Data {     public class Images     {         string imageFilename = null;         byte[] imageBytes = null;

            SqlConnection imageConnection = null;         SqlCommand imageCommand = null;         SqlDataReader imageReader = null;

            // Constructor         public Images()         {             imageConnection = new SqlConnection(@"data source = .\sql2012;                      integrated security = true; initial catalog = SQL2012db;");

                imageCommand = new SqlCommand(@" select imagefile,imagedata                                            from ImageTable", imageConnection);

                // Open connection and create data reader             imageConnection.Open();             imageReader = imageCommand.ExecuteReader();         }

            public Bitmap GetImage()         {             MemoryStream ms = new MemoryStream(imageBytes);             Bitmap bmap = new Bitmap(ms);

                return bmap;         }

            public string GetFilename()         {             return imageFilename;         }

            public bool GetRow()         {             if (imageReader.Read())             {                 imageFilename = (string)imageReader.GetValue(0);                 imageBytes = (byte[])imageReader.GetValue(1);

                    return true;             }             else             {                 return false;             }         }

            public void EndImages()         {             // Close the reader and the connection.             imageReader.Close();             imageConnection.Close();         }     } }`

  9. Next, insert the code in Listing 18-3 into Displaylmages.cs in the constructor. You can access Displaylmages.cs by right-clicking Displaylmages.cs and selecting View Code, which will take you to the Code view.

    **清单 17-3。**初始化DisplayImages构造器中的图像显示

    `        public DisplayImages()         {             InitializeComponent();

                if (images.GetRow())             {                 this.txtImageName.Text = images.GetFilename();                 this.ptbImage.Image = (Image)images.GetImage();             }             else             {                 this.txtImageName.Text = "DONE";                 this.ptbImage.Image = null;             }         }`

  10. Insert the code in Listing 18-3 into the btnShowImage button’s Click event handler. You can access the btnShowImage_Click event handler by navigating to the Design view of the DisplayImages form and double-clicking the btnShowImage Button control.

***列表 17-4。** `btnShowImage_Click`事件中的`DisplayImages.cs`*

` private void btnShowImage_Click(object sender, EventArgs e)
   {
            if (images.GetRow())
            {
                this.txtImageName.Text = images.GetFilename();
                this.ptbImage.Image = (Image)images.GetImage();
            }
            else
            {
                this.txtImageName.Text = "DONE";
                this.ptbImage.Image = null;
            }
 }`

11. To set the TypedAccessor form as the start-up form, modify the Program.cs statement. Application.Run(new LoadImages());

表现为

`Application.Run(new DisplayImages());`

构建项目，按 Ctrl+F5 运行它。你应该在图 17-5 中看到结果。

Image

***图 17-5。*显示图像

它是如何工作的

您声明一个类型Images来访问数据库，并为表单组件提供方法来轻松获取和显示图像。在它的构造函数中，您连接到数据库并创建一个数据读取器来处理检索您之前存储的所有图像的查询结果集。

`       // Constructor         public Images()         {             imageConnection = new SqlConnection(@"data source = .\sql2012;                         integrated security = true; initial catalog = SQL2012db;");

            imageCommand = new SqlCommand(@" select imagefile,imagedata                                           from ImageTable", imageConnection);

            // Open connection and create data reader             imageConnection.Open();             imageReader = imageCommand.ExecuteReader();         }`

当表单初始化时，新代码创建一个Images实例，用GetRow()查找图像，如果找到一个，分别用GetFilename和Getlmage方法将文件名和图像分配给文本框和图片框。

  images = new Images();   if (images.GetRow())   {        this.textBoxl.Text = images.GetFilename();        this.pictureBoxl.Image = (Image)images.GetImage();   }   else   {        this.textBoxl.Text = "DONE";        this.pictureBoxl.Image = null;   }

您在 Next 按钮的click事件处理程序中使用相同的if语句来查找下一个图像。如果没有找到，您在文本框中显示单词 DONE 。

图像以字节数组的形式从数据库返回。PictureBox 控件的 Image 属性可以是Bitmap、Icon或Metafile(所有Image的派生类)。Bitmap支持多种格式，包括 BMP、GIF、JPEG。这里显示的getImage方法返回一个Bitmap对象:

`public Bitmap GetImage() {     MemoryStream ms = new MemoryStream(imageBytes);     Bitmap bmap = new Bitmap(ms);

    return bmap; }`

Bitmap的构造函数不接受字节数组，但它会接受一个MemoryStream(它实际上是一个文件的内存表示)，而MemoryStream有一个接受字节数组的构造函数。因此，从字节数组创建一个内存流，然后从内存流创建一个位图。

处理文本数据

除了用于数据库列的数据类型之外，处理文本类似于处理图像。

试试看:从文件中加载文本数据

要从文件加载文本数据，请按照下列步骤操作:

  1. 选择文本和二进制数据项目，右键单击，并选择添加Image窗口窗体。在打开的对话框中，确保选中 Windows Form，并将Form1.cs重命名为LoadText.cs；单击“确定”将该表单添加到文本和二进制数据项目中。

  2. 选择 LoadText 表单，并将 Size 属性的宽度设置为 496，高度设置为 196。

  3. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此 TextBox 控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtLoadText。
    • 对于 Location 属性，将 X 设置为 12，将 Y 设置为 12。
    • 对于“大小”属性，将“宽度”设置为 456，将“高度”设置为 135。
    • 将文本属性留空。

  4. Now your LoadText form in the Design view should look like Figure 17-6. Image

    ***图 17-6。*LoadText 表单的设计视图

  5. Next, insert the code in Listing 17-5 into LoadText.cs. You can access LoadText.cs by right-clicking LoadText.cs and selecting View Code, which will take you to the Code view.

    ***清单 17-5。*T4LoadText.cs

    `using System.Data; using System.Data.SqlClient; using System.IO

    static string fileName =           @"C:\VidyaVrat\C#2012 and SQL 2012\Chapter17\Code\Text and Binary Data\LoadText.cs";

            SqlConnection conn = null;         SqlCommand cmd = null;

            public LoadText()         {             InitializeComponent();         }

            private void LoadText_Load(object sender, EventArgs e)         {             try             {                 // Create connection                 conn = new SqlConnection(@"data source = .\sql2012;                       integrated security = true;initial catalog = SQL2012Db;");

                    //Create command cmd = new SqlCommand();                 cmd.Connection = conn;

                    // Open connection                 conn.Open();

                    // Create table                 CreateTextTable();

                    // Prepare insert command                 PrepareInsertTextFile();

                    // Load text file                 ExecuteInsertTextFile(fileName);

                    txtLoadText.AppendText("Loaded "+fileName+" into TextTable.\n");

                }             catch (SqlException ex)             {                 MessageBox.Show(ex.ToString());             }             finally             {                 // Close connection                 conn.Close();             }         }

            private void CreateTextTable()         {             ExecuteCommand(@"if exists(select *                                   from INFORMATION_SCHEMA.TABLES                                   where TABLE_NAME = 'TextTable')

                                drop table TextTable ");

                ExecuteCommand(@"create table TextTable                            (                                  TextFile varchar(255),                                  TextData varchar(max)                            )");         }

            private void ExecuteCommand(string commandText)         {             cmd.CommandText = commandText;             cmd.ExecuteNonQuery();             txtLoadText.AppendText("\n");         }

            private void PrepareInsertTextFile()         {             cmd.CommandText = @"insert into TextTable                                 values (@textfile, @textdata)";

                cmd.Parameters.Add("@textfile", SqlDbType.NVarChar, 30);             cmd.Parameters.Add("@textdata", SqlDbType.Text, 1000000);         }

            private void ExecuteInsertTextFile(string textFile)         {             string textData = GetTextFile(textFile);             cmd.Parameters["@textfile"].Value = textFile;             cmd.Parameters["@textdata"].Value = textData;             ExecuteCommand(cmd.CommandText);         }

            private string GetTextFile(string textFile)         {             string textBytes = null;             txtLoadText.AppendText("Loading File: " + textFile);

                FileStream fs = new FileStream(textFile, FileMode.Open, FileAccess.Read);             StreamReader sr = new StreamReader(fs);             textBytes = sr.ReadToEnd();

                txtLoadText.AppendText("TextBytes has length" + textBytes.Length + " bytes.\n");

                return textBytes;         }`

  6. To set the LoadText form as the start-up form, modify the Program.cs statement. Application.Run(new DisplayImages());

    表现为

    Application.Run(new LoadText());

    构建项目，并通过按 Ctrl+F5 运行它。您应该在图 17-7 中看到结果。

    Image

    ***图 17-7。*将文本文件加载到表格中

它是如何工作的

您只需加载 LoadText 程序的源代码。

// change this path to the location of text in your computer         static string fileName =           @"C:\VidyaVrat\C#2012 and SQL 2012\Chapter17\Code\Text and Binary Data\LoadText.cs";

使用 insert starement 和 add 参数设置 CommandText:

`  cmd.CommandText = @"insert into TextTable                     values (@textfile, @textdata)";

  cmd.Parameters.Add("@textfile", SqlDbType.NVarChar, 30);   cmd.Parameters.Add("@textdata", SqlDbType.Text, 1000000);`

执行命令删除现有表并创建一个新表:

`ExecuteCommand(@"if exists(select *                       from INFORMATION_SCHEMA.TABLES                       where TABLE_NAME = 'TextTable')                       drop table TextTable ");

            ExecuteCommand(@"create table TextTable                            (                                  TextFile varchar(255),                                  TextData varchar(max))"                           );`

请注意，您首先检查该表是否存在。如果是这样，您可以删除它，以便重新创建它。

Image 注意information_schema.tables视图(一个命名查询)与同名的 SQL 标准INFORMATION_SCHEMA视图兼容。它将您可以看到的表限制为您可以访问的表。微软建议您使用新的目录视图来获取 SQL Server 2012 中的数据库元数据，SQL Server 本身在内部使用它们。这个查询的目录视图应该是sys.tables，列名应该是 name。我们在这里使用了INFORMATION SCHEMA视图，因为您可能仍然会经常看到它。

GetTextFile使用的是StreamReader(从System.IO派生而来)，而不是您用于图像的BinaryReader。TextReader)将文件的内容读入一个string。

`private string GetTextFile(string textFile)         {             string textBytes = null;             txtLoadText.AppendText("Loading File: " + textFile);

            FileStream fs = new FileStream(textFile, FileMode.Open, FileAccess.Read);             StreamReader sr = new StreamReader(fs);             textBytes = sr.ReadToEnd();             txtLoadText.AppendText("TextBytes has length" + textBytes.Length + " bytes.\n");

            return textBytes;         }`

否则，处理逻辑基本上与您在整本书中多次看到的一样:打开一个连接，访问一个数据库，然后关闭连接。

现在让我们检索您刚刚存储的文本。

从文本列中检索数据

从文本列中检索数据就像从较小的字符数据类型中检索数据一样。现在，您将编写一个简单的控制台程序来看看这是如何工作的。

试试看:检索文本数据

要从文本列中检索数据，请按照下列步骤操作:

  1. 选择文本和二进制数据项目，右键单击，并选择添加Image窗口窗体。在打开的对话框中，确保选择了 Windows 窗体，并将Form1.cs重命名为RetrieveText.cs；单击“确定”将该表单添加到文本和二进制数据项目中。

  2. 选择 RetrieveText 表单，并将 Size 属性的宽度设置为 438，高度设置为 334。

  3. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此 TextBox 控件，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtRetrieveText。
    • 对于 Location 属性，将 X 设置为 12，将 Y 设置为 12。
    • 对于“大小”属性，将“宽度”设置为 401，将“高度”设置为 269。
    • 将文本属性留空。

  4. Now your LoadText form in the Design view should look like Figure 17-8. Image

    ***图 17-8。*检索文本表单的设计视图

  5. Next, insert the code in Listing 17-6 into RetrieveText.cs. You can access RetrieveText.cs by right-clicking RetrieveText.cs and selecting View Code, which will take you to the Code view.

    清单 17-6。 RetrieveText.cs

    `using System.Data;     using System.Data.SqlClient;

            string textFile = null;         char[] textChars = null;         SqlConnection conn = null;         SqlCommand cmd = null;         SqlDataReader dr = null;

            public RetrieveText()         {             InitializeComponent();

                // Create connection             conn = new SqlConnection(@"data source = .\sql2012;integrated security = true;                                              initial catalog = SQL2012Db;");

                // Create command             cmd = new SqlCommand(@"select textfile, textdata                                   from TextTable", conn); // Open connection             conn.Open();

                // Create data reader             dr = cmd.ExecuteReader();         }

            public void RetrieveText_Load(object sender, EventArgs e)         {             try             {                while (GetRow() == true)                 {                     txtRetrieveText.AppendText ("----- end of file\n");                     txtRetrieveText.AppendText(textFile);                     txtRetrieveText.AppendText("\n============================\n");                 }             }             catch (SqlException ex)             {                 Console.WriteLine(ex.ToString());             }

                finally             {                 // Close the reader and the connection.                 dr.Close();                 conn.Close();             }         }

            public bool GetRow()         {             long textSize;             int bufferSize = 100;             long charsRead;             textChars = new Char[bufferSize];

                if (dr.Read())             {                 // Get file name                 textFile = dr.GetString(0);                 txtRetrieveText.AppendText("------ start of file\n");                 txtRetrieveText.AppendText(textFile);                 txtRetrieveText.AppendText("\n");                 textSize = dr.GetChars(1, 0, null, 0, 0);                 txtRetrieveText.AppendText("--- size of text: " + textSize + " characters --- ");

                    txtRetrieveText.AppendText("\n--- first 100 characters in text -----\n");                 charsRead = dr.GetChars(1, 0, textChars, 0, 100);                 txtRetrieveText.AppendText(new String(textChars));                 txtRetrieveText.AppendText("\n");                 txtRetrieveText.AppendText("\n--- last 100 characters in text -----\n");                 charsRead = dr.GetChars(1, textSize - 100, textChars, 0, 100);                 txtRetrieveText.AppendText(new String(textChars));

                    return true;             }             else             {                 return false;             }         }`

  6. To set the LoadText form as the start-up form, modify the Program.cs statement. Application.Run(new LoadText());

    表现为

    Application.Run(new RetrieveText());

    构建项目，并通过按 Ctrl+F5 运行它。您应该在图 17-9 中看到结果。

    Image

    ***图 17-9。*从表格中检索文本

它是如何工作的

查询数据库后，像这样:

`// Create connection             conn = new SqlConnection(@"data source = .\sql2012;integrated security = true;                                       initial catalog = SQL2012Db;");

            // Create command             cmd = new SqlCommand(@"select textfile, textdata                                    from TextTable", conn);

            // Open connection             conn.Open();

            // Create data reader             dr = cmd.ExecuteReader();`

您遍历结果集(但是这里只有一行)，从带有GetString()的表中获取文件名，并打印它以显示显示的是哪个文件。然后用一个空字符数组调用GetCharsQ来获得VARCHAR(MAX)列的大小。

`            if (dr.Read())             {                 // Get file name                 textFile = dr.GetString(0);                 txtRetrieveText.AppendText("------ start of file\n");                 txtRetrieveText.AppendText(textFile);                 txtRetrieveText.AppendText("\n");                 textSize = dr.GetChars(1, 0, null, 0, 0);                 txtRetrieveText.AppendText("--- size of text: " + textSize + " characters --- ");

                txtRetrieveText.AppendText("\n--- first 100 characters in text -----\n");                 charsRead = dr.GetChars(1, 0, textChars, 0, 100);                 txtRetrieveText.AppendText(new String(textChars));                 txtRetrieveText.AppendText("\n");                 txtRetrieveText.AppendText("\n--- last 100 characters in text -----\n");                 charsRead = dr.GetChars(1, textSize - 100, textChars, 0, 100);                 txtRetrieveText.AppendText(new String(textChars));

                return true;             }             else             {                   return false;             }`

不是打印整个文件，而是显示前 100 个字节，使用GetChars()提取一个子串。对最后 100 个字符做同样的事情。

除此之外，这个程序就像其他检索和显示数据库字符数据的程序一样。

总结

在本章中，您了解了 SQL Server 的文本和二进制数据类型。您还练习了使用 SQL Server 大型对象和 ADO.NET 的数据类型来存储和检索二进制和文本数据。

在下一章，你将学习另一种数据库查询技术，称为语言集成查询(LINQ)。

十八、使用 LINQ

编写软件意味着你需要有一个位于后端的数据库，并且你的大部分时间都花在编写查询来检索和操作数据上。每当有人谈到数据时，我们往往会想到包含在关系数据库或 XML 文档中的信息。

在发布之前的数据访问类型。NET 3.5 只适用于驻留在传统数据源中的数据，就像刚才提到的两个数据源。但是随着。NET 3.5 和更新的版本，如。NET 4.0 和。NET 4.5 中集成了语言集成查询(LINQ)，现在可以处理驻留在传统信息存储之外的数据。例如，您可以查询一个包含几百个整数值的泛型List类型，并编写一个 LINQ 表达式来检索满足您的标准的子集，比如偶数或奇数。

你可能已经知道，LINQ 是。NET 3.0 和。净 3.5。它是 Visual Studio 2012 中的一组功能，将强大的查询功能扩展到 C# 和 VB .NET 的语言语法中。

LINQ 引入了一种标准的、统一的、易于学习的方法来查询和修改数据，并且可以扩展以支持潜在的任何类型的数据存储。Visual Studio 2012 包括 LINQ 提供程序程序集，这些程序集支持对各种类型的数据源(包括关系数据、XML 和内存中数据结构)使用 LINQ 查询。

在本章中，我们将介绍以下内容:

  • Introduction to LINQ
  • LINQ architecture
  • LINQ project structure
  • Working with LINQ objects
  • Use LINQ to SQL
  • Use LINQ to XML

LINQ 简介

LINQ 是微软在发布 Visual Studio 2008 和。NET Framework 版承诺彻底改变开发人员处理数据的方式。微软通过最近发布的继续改进 LINQ。NET 4.0/4.5 和 Visual Studio 2012。如上所述，LINQ 允许您查询各种类型的数据源，包括关系数据库、XML 文档，甚至内存中的数据结构。借助 C# 2012 中一级语言构造的 LINQ 查询表达式，LINQ 支持所有这些类型的数据存储。LINQ 具有以下优势:

  • linq provides a general syntax for querying any type of data source; For example, you can query XML documents like SQL database, ADO.NET dataset, memory collection, or any other remote or local data source that you choose to use LINQ to connect and access.
  • LINQ has built a bridge between relational data and object-oriented world, and strengthened the connection. LINQ speeds up development by catching many errors at compile time and including intellisense and debugging support.
  • LINQ query expression (different from traditional SQL statement) is strongly typed.

Image 注意强类型表达式确保在编译时以正确的类型访问值，从而防止在编译代码时而不是在运行时捕获类型不匹配错误。

LINQ 程序集在一个保护伞下提供了访问各种类型的数据存储的所有功能。表 18-1 列出了核心 LINQ 组件。

Image

Image 注虽然叫语言集成查询，但是 LINQ 可以用来更新数据库数据。这里我们将只讨论简单的查询，让您对 LINQ 有个初步的了解，但是 LINQ 是一个访问数据的通用工具。

LINQ 的建筑

LINQ 由三个主要部分组成:

  • [LINQ] to the object
  • To ADO.NET [linq], including
    • linq！linq 你好 SQL(唉哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟哟德列奈)
    • LINQ to data set (formerly known as LINQ over data set)
    • LINQ to entity
  • 查询表达式到 XML(原名 XLinq)

图 18-1 描述了 LINQ 架构，它清楚地显示了 LINQ 的各种组件及其相关的数据存储。

Image

图 18-1。 LINQ 建筑

对象的 LINQ 处理内存中的数据。任何实现了IEnumerable<T>接口(在System.Collections.Generic名称空间中)的类都可以用标准查询操作符(sqo)来查询。

Image 注sqo 是形成 LINQ 模式的方法集合。SQO 方法对序列进行操作，其中一个序列表示一个对象，其类型实现接口IEnumerable<T>或接口IOueryable<T>。SQO 提供的查询功能包括过滤、投影、聚合、排序等。

ADO.NET LINQ(也称为 LINQ 支持的 ADO。NET)处理来自外部来源的数据，基本上是 ADO.NET 可以连接到的任何东西。任何实现了IEnumerable<T>或IOueryable<T>(在System.Linq命名空间中)的类都可以用 SQOs 查询。通过使用System.Data.Linq名称空间可以实现从 LINQ 到 ADO.NET 的功能。

LINQ 到 XML 是一个全面的内存 XML 编程 API。像 LINQ 的其他部分一样，它包括 SQOs，也可以与 ADO.NET 的 LINQ 协同使用，但它的主要目的是统一和简化不同的 XML 工具(如 XQuery、XPath 和 XSLT)通常用来做的事情。LINQ 到 XML 的功能可以通过使用System.Xml.Linq名称空间来实现。

Image 注 LINQ。NET Compact Framework 包括桌面 LINQ 功能的一个子集。LINQ 和。NET 框架和 LINQ。NET Compact Framework 是在。NET Compact Framework，只支持 sqo。支持 LINQ 到数据集和 LINQ 到数据表，也支持 LINQ 到 XML，但 XPath 扩展除外。

在本章中，我们将使用 LINQ 到对象、LINQ 到 SQL 和 LINQ 到数据集的技术，因为它们与我们在本书中讨论的 C# 2012 数据库编程最密切相关。

LINQ 项目结构

Visual Studio 2012 允许您使用 LINQ 查询。要创建 LINQ 项目，请按照下列步骤操作:

  1. 打开 Visual Studio 2012，选择文件Image新建Image项目。

  2. 默认情况下，在出现的“新建项目”对话框中。在可用列表中选择了. NET Framework 4.5。Visual Studio 2012 支持的. NET Framework 版本。选择希望 LINQ 功能包含在其中的项目类型。对于这个例子，我们将使用一个 Windows 窗体应用项目。

  3. 为选择的项目指定名称 Chapter18 ，并单击 OK。一个名为 Chapter18 的新的 Windows 窗体应用将被创建。选择解决方案下名为 Chapter18 的项目，并将其重命名为 Linq。保存所有更改。

  4. Open the References folder in Solution Explorer. You should see Linq-related assembly references added by default, as shown in Figure 18-2. Image

    图 18-2。 LINQ 推荐人

现在，您已经准备好使用 LINQ 项目，您需要做的就是将代码功能和所需的名称空间添加到项目中，并测试应用。让我们开始使用 LINQ。

用 LINQ 来物件

术语对象 LINQ 指的是使用 LINQ 查询来访问内存中的数据结构。可以查询任何支持IEnumerable<T>的类型。这意味着 LINQ 查询不仅可以用于用户定义的列表、数组、字典等，还可以与。返回集合的 NET Framework APIs。例如，您可以使用System.Reflection类返回存储在指定程序集中的类型信息，然后使用 LINQ 过滤这些结果。或者，您可以将文本文件导入到可枚举的数据结构中，并将内容与其他文件进行比较，提取行或部分行，将几个文件中的匹配行组合到一个新集合中，等等。与传统的foreach循环相比，LINQ 查询有三个主要优势:

  • They are more concise and readable, especially when filtering multiple conditions.
  • They provide powerful filtering, sorting and grouping functions with minimal application code.
  • They can be transplanted to other data sources with little modification.

一般来说，您想要对数据执行的操作越复杂，与传统的迭代技术相比，使用 LINQ 的好处就越大。

试试看:编写一个简单的 LINQ 对象查询

在本练习中，您将创建一个包含一个文本框的 Windows 窗体应用。应用将使用 Linq to Objects 从 TextBox 控件中的字符串数组中检索并显示一些名称。

  1. 右键单击 Chapter18 解决方案中的Form1.cs，选择重命名选项，将表单重命名为 LinqToObjects。

  2. 通过单击窗体的标题栏选择 LinqToObjects 窗体，并将 Size 属性的宽度设置为 308，高度设置为 311。

  3. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此文本框，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtDisplay。
    • 对于 Size 属性，将 Width 设置为 244，Height 设置为 216。
    • 将 Multiline 属性设置为 True。

  4. Now your LinqToObjects form in the Design view should look like Figure 18-3. Image

    ***图 18-3。*LinqToObjects 表单的设计视图

  5. Double-click the empty surface of the LinqToObjects.cs form, and it will open the code editior window, showing the LinqToObject_Load event. Place the code in Listing 18-1 in the LinqToObjects_Load event.

    清单 18-1。LinqToObjects.cs

    `//Define string array string[] names = { "Life is Beautiful",                  "Arshika Agarwal",                  "Seven Pounds",                  "Rupali Agarwal",                  "Pearl Solutions",                  "Vamika Agarwal",                  "Vidya Vrat Agarwal",                  "Lionbridge Technologies"               };

    //Linq query IEnumerable namesOfPeople = from name in names                              where name.Length <= 16                              select name;

    foreach (var name in namesOfPeople) {     txtDisplay.AppendText(name+"\n"); }`

  6. Run the program by pressing Ctrl+F5, and you should see the results shown in Figure 18-4. Image

    ***图 18-4。*使用对象的 LINQ 从字符串数组中检索名字

它是如何工作的

您声明了一个名为names的字符串数组。

string[] names = {"Life is Beautiful",                "Arshika Agarwal",                "Seven Pounds",                "Rupali Agarwal",                                    "Pearl Solutions",                 "Vamika Agarwal",                 "Vidya Vrat Agarwal",                 "Lionbridge Technologies" };

要从字符串数组中检索名称，可以使用IEnumerable<string>查询字符串数组，并在foreach的帮助下使用 LINQ 到对象查询语法遍历names数组。

IEnumerable<string> namesOfPeople = from name in names                              where name.Length <= 16                              select name; foreach (var name in namesOfPeople)

使用 LINQ 到 SQL

LINQ 到 SQL 是一种将关系数据作为对象进行管理和访问的工具。在某些方面，它在逻辑上类似于 ADO.NET，但它从更抽象的角度来看待数据，简化了许多操作。它连接到数据库，将 LINQ 结构转换为 SQL，提交 SQL，将结果转换为对象，甚至跟踪更改并自动请求数据库更新。

一个简单的 LINQ 查询需要三样东西:

  • Entity class
  • A data context
  • A LINQ query
试试看:编写一个简单的 LINQ 到 SQL 查询

在本练习中，您将使用 LINQ 到 SQL 检索来自AdventureWorks个人的所有联系信息。联系表。

  1. 导航到解决方案资源管理器，右击 Linq 项目，并选择添加Image Windows 窗体。在打开的添加新项对话框中，确保选中 Windows Form，然后将Form1.cs重命名为 LinqToSql。单击添加。

  2. 通过单击窗体的标题栏选择 LinqToSql 窗体，并将 Size 属性的宽度设置为 355，高度设置为 386。

  3. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此文本框，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtLinqToSql。
    • 将 Multiline 属性设置为 True。
    • 将 ScrollBars 属性设置为垂直。

  4. For the Size property, set Width to 315 and Height to 320. Now your LinqToSql form in the Design view should look like Figure 18-5. Image

    ***图 18-5。*LinqToSql 表单的设计视图

  5. Before you begin coding the functionality, you must add the required assembly references. LinqToSql will require an assembly reference of System.Data.Linq to be added to the Linq project. To do so, select References, right-click, and choose Add Reference. From the opened Reference Manager dialog, scroll down to the assembly list, and select System.Data.Linq, as shown in Figure 18-6. Click OK. Image

    ***图 18-6。*添加对 Linq 程序集的引用。

  6. Open the newly added form LinqToSql.cs in the Code view. Add the code shown in Listing 18-2 in LinqToSql.cs.

    ***清单 18-2。*T4LinqToSql.cs

    `// Must add these two namespaces for LinqToSql using System.Data.Linq; using System.Data.Linq.Mapping;

    [Table(Name = "Person.Person")]         public class Contact         {             [Column]             public string Title;             [Column]             public string FirstName;             [Column]             public string LastName;         }

             private void LinqToSql_Load(object sender, EventArgs e)         {             // connection string             string connString = @"server = .\sql2012;integrated security = true;                             database = AdventureWorks";

                try             {                 // Create data context                 DataContext db = new DataContext(connString);

                    // Create typed table                 Table contacts = db.GetTable();

                    // Query database                 var contactDetails =                    from c in contacts                    where c.Title == "Mr."                    orderby c.FirstName                    select c;

                    // Display contact details                 foreach (var c in contactDetails)                 {                     txtLinqtoSql.AppendText(c.Title);                     txtLinqtoSql.AppendText("\t");                     txtLinqtoSql.AppendText(c.FirstName);                     txtLinqtoSql.AppendText("\t");                     txtLinqtoSql.AppendText(c.LastName);                     txtLinqtoSql.AppendText("\n");                 }             }

                catch (Exception ex)             {                 MessageBox.Show(ex.Message);             }         }`

  7. 现在，要将 LinqToSql 表单设置为启动表单，请在代码编辑器中打开Program.cs，并将Application.Run(new LinqToObjects());修改为Application.Run(new LinqToSql());。

  8. Build the solution, and then run the program by pressing Ctrl+F5; you should see the results shown in Figure 18-7. Image

    ***图 18-7。*将 LINQ 的详细联系信息检索到 SQL

它是如何工作的

你定义了一个实体类，Contact。

[Table(Name = "Person.Person")]         public class Contact         {             [Column]             public string Title;             [Column]             public string FirstName;             [Column]             public string LastName;         }

实体类提供了 LINQ 存储来自数据源的数据的对象。它们就像任何其他 C# 类一样，但是 LINQ 定义了告诉它如何使用这个类的属性。

属性将该类标记为一个实体类，并有一个可选的属性Name,该属性可用于给出表名，默认为类名。这就是为什么你把这个类命名为Contact而不是 Person.Contact

[Table(Name = "Person.Contact")] public class Contact

接下来，您必须将类型化表定义改为

Table<Contact> contacts = db.GetTable<Contact>();

为了保持一致，[Column]属性将一个字段标记为保存表中数据的字段。可以在实体类中声明不映射到表列的字段，LINQ 会忽略它们，但是用[Column]属性修饰的字段的类型必须与它们映射到的表列兼容。(请注意，由于 SQL Server 表名和列名不区分大小写，默认名称不必与数据库中使用的名称大小写相同。)

您创建了一个数据上下文。

//  Create data context DataContext db = new DataContext(connString);

数据上下文做 ADO.NET 连接做的事情，但它也做数据提供者处理的事情。它不仅管理到数据源的连接，还将 LINQ 请求(用 SQO 表示)转换成 SQL，将 SQL 传递给数据库服务器，并从结果集中创建对象。

您创建了一个类型的表。

//  Create typed table Table<Contact> contacts = db.GetTable<Contact>();

类型化表是一个集合(类型为System.Data.Linq.Table<T>)，它的元素具有特定的类型。DataContext类的GetTable方法告诉数据上下文访问结果，并指示将它们放在哪里。在这里，您从 Person 获得所有行(但只有三列)。Contact 表，数据上下文为 contacts 类型表中的每一行创建一个对象。

你声明了一个 C# 2012 隐式类型化的局部变量， contactDetails，类型为var。

// Query database var contactDetails =

隐式类型的局部变量顾名思义。当 C# 看到var类型时，它会根据=符号右边的initializer中的表达式类型来推断局部变量的类型。

您用一个查询表达式初始化本地变量*。*

   from c in contacts    where c.Title == "Mr."    orderby c.FirstName    select c;

查询表达式由一个from子句和一个*查询体组成。*您在这里使用查询体中的WHERE条件。from子句声明了一个迭代变量c，用于迭代表达式contacts的结果(即之前创建和加载的类型化表)。在每次迭代中，它将选择满足WHERE子句的行(这里，标题必须是“Mr .”)。

最后，遍历custs集合并显示每个客户。

// Display contact details foreach (var c in contactDetails) {    txtLinqtoSql.AppendText(c.Title);    txtLinqtoSql.AppendText("\t");    txtLinqtoSql.AppendText(c.FirstName);    txtLinqtoSql.AppendText("\t");    txtLinqtoSql.AppendText(c.LastName);    txtLinqtoSql.AppendText("\n"); }

尽管有了新的 C# 2008 特性和术语，但这种感觉还是很熟悉。一旦掌握了窍门，这是一种很有吸引力的查询编码替代方法。您基本上是编写一个查询表达式而不是 SQL 来填充一个集合，您可以用一个foreach语句遍历这个集合。但是，您提供了一个连接字符串，但没有显式地打开或关闭连接。此外，不需要命令、数据读取器或索引器。您甚至不需要使用System.Data或System.Data.SqlClient名称空间来访问 SQL Server。

很酷，不是吗？

使用 LINQ 到 XML

LINQ 到 XML 提供了一个内存中的 XML 编程 API，该 API 将 XML 查询功能集成到 C# 2012 中，以利用 LINQ 框架并添加特定于 XML 的查询扩展。LINQ 到 XML 提供了集成到. NET 中的 XQuery 和 XPath 的查询和转换能力

从另一个角度来看，您也可以将 LINQ 到 XML 看作是一个全功能的 XML API，相当于现代化的、重新设计的 SystemXml API 加上 XPath 和 XSLT 的一些关键特性。LINQ 到 XML 提供了在内存中编辑 XML 文档和元素树的工具，以及流工具。图 18-8 显示了一个样本 XML 文档。

Image

图 18-8。 XML 文档

试试看:编写一个简单的 LINQ 到 XML 的查询

在本练习中，您将使用 LINQ 到 XML 从 XML 文档中检索元素值。

  1. 导航到解决方案资源管理器，右击 Linq 项目，然后选择“Windows 窗体”。在打开的添加新项对话框中，确保选中 Windows Form，然后将Form1.cs重命名为 LinqToXml。单击添加。

  2. 通过单击窗体的标题栏选择 LinqToXml 窗体，并将 Size 属性的宽度设置为 377，高度设置为 356。

  3. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此文本框，导航到“属性”窗口，并设置以下属性:

    • 将 Name 属性设置为 txtLinqToXml。
    • 将 Multiline 属性设置为 True。
    • 将 ScrollBars 属性设置为垂直。
    • 对于“大小”属性，将“宽度”设置为 340，将“高度”设置为 298。

  4. Now your LinqToXml form in the Design view should look like Figure 18-9. Image

    ***图 18-9。*LinqToXml 表单的设计视图

  5. Open the newly added form LinqToXml.cs in code view. Add the code shown in Listing 18-3 in LinqToXml.cs.

    ***清单 18-3。*T4LinqToXml.cs

    `            using System.Xml.Linq;             //Load the productstable.xml in memory             XElement doc = XElement.Load(@"C:\VidyaVrat\C#2012 and SQL             2012\Chapter18\Code\Linq\productstable.xml");

                //Query xml doc             var products = from prodname in doc.Descendants("products")                            select prodname.Value;

                //Display details             foreach (var prodname in products)             {                 txtLinqToXml.AppendText("Product's Detail= ");                 txtLinqToXml.AppendText(prodname);                 txtLinqToXml.AppendText("\n");             }`

    Image 注意我们已经指定了productstable.xml文件，它位于我们机器上的特定位置；根据您的计算机和 XML 文件的可用性，您可以使用另一个 XML 文件路径。本章的源代码中也提供了productstable.xml文件。

  6. Now, to set the LinqToSql form as the start-up form, open Program.cs in the code editor, and modify the Application.Run(new LinqToSql());

    表现为:

    Application.Run(new LinqToXml());.

    生成解决方案，然后按 Ctrl+F5 运行程序。您应该会看到如图 18-10 所示的结果。

    Image

    ***图 18-10。*使用 LINQ 到 XML 检索产品详细信息

它是如何工作的

使用System.Linq.Xml中的XElement指定下面的语句，将 XML 文档加载到内存中。

XElement doc = XElement.Load(@"C:\VidyaVrat\C#2012 and SQL 2012\Chapter18\Code\Linq\productstable.xml ");

您还可以编写以下语句来查询 XML 文档，其中Descendents方法将返回 XML 文档中指定元素的后代元素的值。

var products = from prodname in doc.Descendants("products") select prodname.Value;

总结

在本章中，我们讲述了使用 LINQ 进行简单查询的要点。我向您介绍了 LINQ 的三种风格，主要是对象的 LINQ、SQL 的 LINQ 和 XML 的 LINQ。在下一章，我将介绍 ADO.NET 实体框架。

十九、使用 ADO.NET 实体框架

许多数据库开发人员认为随着 ADO.NET 2.0 和 LINQ 的发布，数据库 API 已经足够成熟了，但是这些数据访问 API 还在继续发展。数据访问 API 使用起来相当简单，它们允许您模拟关系数据库中存在的相同类型的数据结构和关系。

但是，与数据集或数据表中的数据交互的方式不同于与数据库表中的数据交互的方式。数据的关系模型和编程的面向对象模型之间的差异是相当大的，ADO.NET 2.0 和 LINQ 在减少这两种模型之间的阻抗方面做得相对较少。

随着的发布。NET 框架 4.5 和 Visual Studio 2011，推出了新版本的 ADO.NET 实体框架 5.0。本章将向您介绍 ADO.NET 实体框架 5.0 数据模型，也称为实体数据模型(EDM)。

EDM 是微软实现对象关系映射(ORM)的方式。ORM 是一种处理关系数据并将关系数据映射到对象集合的方法，这些对象称为实体。在这一章中，你将会学到更多的知识，包括这种方法的优点。

在本章中，我将介绍以下内容:

  • Understanding ADO.NET Entity Framework 5.0
  • Understanding entity data model
  • Use entity data model

了解 ADO.NET 实体框架 5.0

ADO.NET 实体框架(EF) 5.0 背后的愿景是扩展数据库编程的抽象级别，并完全消除程序员用来编写面向数据库的软件应用的数据模型和开发语言之间的阻抗不匹配。

ADO.NET EF 5.0 允许开发人员通过对象模型而不是通过传统的逻辑/关系数据模型来关注数据，帮助将逻辑数据模式抽象为概念模型、映射层和逻辑层，以允许通过名为EntityClient的新数据提供者与该模型进行交互。

在本章中，我将回顾每一层的用途。

ADO.NET EF 5.0 允许开发人员编写更少的数据访问代码，减少维护，并将数据的结构抽象成一种更加业务友好的方式。它还可以帮助减少编译时错误的数量，因为它从概念模型生成强类型类。

如前所述，ADO.NET EF 5.0 生成了一个概念模型，开发者可以使用名为EntityClient的新数据提供者来编写代码。EntityClient遵循类似于熟悉的 ADO.NET 对象的模型，使用EntityConnection和EntityCommand对象返回一个EntityDataReader。

了解实体数据模型

ADO.NET EF 3.5 的核心是它的实体数据模型。ADO.NET EF 3.5 支持逻辑存储模型，该模型表示来自数据库的关系模式。关系数据库通常以不同于应用可以使用的格式存储数据。这通常会迫使开发人员以与数据库中包含的数据相同的结构来检索数据。然后，开发人员通常将数据提供给更适合处理业务规则的业务实体。ADO.NET EF 5.0 使用地图图层在数据模型之间架起了一座桥梁。在 ADO.NET EF 5.0 的模型中有三个活跃的层次。

  • Concept layer
  • Mapping layer
  • Logical layer

这三层允许数据从关系数据库映射到更加面向对象的业务模型。ADO.NET EF 3.5 使用 XML 文件定义了这些层。这些 XML 文件提供了一个抽象层次，因此开发人员可以根据 OO 概念模型而不是传统的关系数据模型进行编程。

使用概念模式定义语言(CSDL)在 XML 文件中定义概念模型。CSDL 定义了应用的业务层所知道的实体和关系。表示数据库模式的逻辑模型是使用存储模式定义语言(SSDL)在 XML 文件中定义的。使用映射模式语言(MSL)定义的映射层映射其他两层。这种映射允许开发人员根据概念模型进行编码，并将这些指令映射到逻辑模型中。

使用实体数据模型

今天运行的大多数应用都离不开后端数据库。应用和数据库高度相互依赖；也就是说，它们是紧密耦合的，因此显而易见，应用或数据库中的任何更改都会对另一端产生巨大影响；紧密耦合总是双向的，改变一侧需要与另一侧同步。如果更改没有正确地反映出来，应用将不会以期望的方式运行，系统将会崩溃。

让我们通过考虑下面的代码段来看看紧耦合，你在第十三章中使用它作为清单 13-3 的一部分:

`// Create connection             SqlConnection conn = new SqlConnection(@"                                  server = .\sql2012;                                  integrated security = true;                                  database = AdventureWorks");

            // Create command             string sql = @"select Name,ProductNumber                         from Production.Product";             SqlCommand cmd = new SqlCommand(sql, conn);             txtReader.AppendText("Command created and connected.\n\n"); try             {                 // Open connection                 conn.Open();

                // Execute query via ExecuteReader                 SqlDataReader rdr = cmd.ExecuteReader();             }`

假设您已经将上述代码与数据库一起部署到生产环境中，该数据库具有 select 查询中指定的列名。稍后，数据库管理员(DBA)决定更改所有表中的列名，以实现新的数据库策略:DBA 修改生产。Product 表，并将 Name 列更改为 ProductName，将 ProductNumber 列更改为 ProductSerialNumber。

完成这些数据库更改后，防止应用崩溃的唯一方法是修改源代码中引用 Name 和 ProductName 列的所有代码段，重新构建、重新测试并再次部署整个应用。因此，前面代码中修改后的代码段将如下所示:

            // Create command             string sql = @"select ProductName, ProductSerialNumber                         from Production.Product";

虽然从表面上看，进行这样的更改并不困难，但是如果考虑到可能有许多与数据库相关的代码段需要根据新的列命名方案修改列名，那么升级应用以使其能够与修改后的数据库一起工作可能会是一种繁琐而困难的方法。

有了 ADO.NET EF 5.0 的实体数据模型，微软使得实体关系建模变得可执行。微软通过结合 XML 模式文件和 ADO.NET EF 5.0 API 实现了这一点。架构文件用于定义概念层，以公开数据存储的架构(例如，SQL Server 2012 数据库的架构)并在两者之间创建映射。ADO.NET EF 5.0 允许你根据概念模式生成的类来编写程序。然后，当您从数据库中提取数据时，EDM 会通过允许您以面向对象的方式与关系数据库进行交互来处理所有的翻译。

EDM 使得客户端应用和数据库模式能够以松散耦合的方式独立发展，而不会相互影响或破坏。

ADO.NET 的 EDM 5.0 实体框架提供了应用所使用的数据库模式的概念视图。这个概念视图在应用中被描述为一个 XML 映射文件。XML 映射文件将实体属性和关联关系映射到数据库表。

这种映射是将应用从对关系数据库模式的更改中抽象出来的魔棒。因此，不必修改应用中所有面向数据库的代码段来适应数据库模式中的更改，只需修改 XML 映射文件，使其反映对数据库模式的所有更改。换句话说，ADO.NET 5.0 EDM 提供的解决方案是在不改变任何源代码的情况下修改 XML 映射文件来反映模式的变化。

试试看:创建实体数据模型

在本练习中，您将了解如何创建 EDM。

  1. 创建一个名为 Chapter19 的新 Windows 窗体应用项目。当解决方案资源管理器打开时，保存解决方案。

  2. 将 Chapter19 项目重命名为 EntityFramework。

  3. Right-click the project and select Add Image New Item; from the provided Visual Studio templates, choose ADO.NET Entity Data Model, and name it AWCurrencyModel.edmx; your screen should look like Figure 19-1. Click Add. Image

    ***图 19-1。*添加 ADO.NET 实体数据模型

  4. The Entity Data Model Wizard will start, with the Choose Model Contents screen appearing first. Select the “Generate from database” option, as shown in Figure 19-2. Click Next. Image

    ***图 19-2。*实体数据模型向导—选择模型内容屏幕

  5. The Choose Your Data Connection screen appears next, as shown in Figure 19-3. Click the New Connection button. Image

    ***图 19-3。*实体数据模型向导—选择您的数据连接屏幕

  6. The Choose Data Source dialog box appears. Select Microsoft SQL Server from the “Data source” list, as shown in Figure 19-4. Click Continue. Image

    ***图 19-4。*实体数据模型向导—选择数据源对话框

  7. Next, the Connection Properties dialog box appears. Enter .\SQL2012 in the “Server name” list box and ensure that the Use Windows Authentication radio button is selected. From the list box provided below the “Select or enter a database name” radio button, select Northwind. Your dialog box should look like Figure 19-5. Click the Test Connection button. Image

    ***图 19-5。*实体数据模型向导—连接属性对话框

  8. 消息框应该闪烁，显示消息“测试连接成功”单击确定。现在，在连接属性对话框中单击确定。

  9. The Choose Your Data Connection window appears, again displaying all the settings you’ve made so far. Ensure the check box “Save entity connection settings in App.config as” is selected and has AWCurrencyEntities as a value entered in it, as shown in Figure 19-6. Click Next. Image

    ***图 19-6。*实体数据模型向导——选择显示设置的数据连接屏幕

  10. The Choose Your Database Objects screen now appears. Expand the Tables node. If any of the table or tables are selected, remove all the check marks except for the ones beside the Sales.Currency table. Also, remove the check marks from the Views and Stored Procedures node. The screen will look like Figure 19-7. Click Finish. Image

***图 19-7。**实体数据模型向导—选择您的数据库对象屏幕*
  1. Navigate to Solution Explorer, and you will see that a new AWCurrencyModel.edmx object has been added to the project, as shown in Figure 19-8. Image
***图 19-8。**显示生成的实体数据模型的解决方案浏览器*
  1. Double-click AWCurrencyModel.edmx to view the generated Entity Data Model in the Design view. It should look like Figure 19-9. Image
***图 19-9。**设计视图中的实体数据模型*
  1. The generated Entity Data Model also has an XML mapping associated with it especially for its EntityContainer and EntitySets. To view the XML mapping, navigate to Solution Explorer, right-click AWCurrencyModel.edmx, and choose the Open With option. From the dialog box that appears, select XML (Text) Editor, and click OK. Notice the highlighted text in the mapping shown in Figure 19-10. Image
***图 19-10。**与实体数据模型相关联的 XML 映射*
  1. 切换到解决方案资源管理器，并将 Form1 重命名为PublishCurrency.cs。
  2. 将 TextBox 控件拖到窗体上，并将其放在窗体的中央。选择此 TextBox 控件，导航到“属性”窗口，并设置以下属性: * 将 Name 属性设置为 txtCurrency。 * 对于 Location 属性，将 X 设置为 12，将 Y 设置为 12。 * 将 Multiline 属性设置为 True。 * 将 ScrollBars 属性设置为垂直。 * 对于“大小”属性，将“宽度”设置为 518，将“高度”设置为 247。 * 将文本属性留空。
  3. Now your PublishCurrency form in the Design view should like Figure 19-11. Image
***图 19-11。**发行货币表单的设计视图*
  1. Double-click the empty surface of the form, and it will open the code editor window, showing the PublishCurrency_Load event. Place the code listed in Listing 19-1 into the load event code template.
***清单 19-1。**使用实体数据模型*

`                AWCurrencyEntities currContext = new AWCurrencyEntities();

                foreach (var cr in currContext.Currencies)
                {
                    txtCurrency .AppendText(cr.ModifiedDate.ToString());

                    txtCurrency.AppendText("\t\t");
                    txtCurrency.AppendText(cr.CurrencyCode.ToString());

                    txtCurrency.AppendText("\t\t");
                    txtCurrency.AppendText(cr.Name.ToString());
                    txtCurrency.AppendText("\t");
                    txtCurrency.AppendText("\n");
                }`

18. Build the solution, and run the project. When the PublishCurrency form appears, you will see all the currencies listed in the TextBox. The screen shown in Figure 19-12 should display. Image

***图 19-12。**显示发行货币表单*
它是如何工作的

因为您正在使用一个实体数据模型，所以您不需要处理SqlConnection、SqlCommand等等。在这里，您创建一个引用名为AWCurrencyEntities的EntityContainer的对象，该对象引用存储在App.config文件中的整个连接字符串。

AWCurrencyEntities currContext = new AWCurrencyEntities();

将对象指定给EntityContainer后，就该遍历由EntityContainer.EntitySet组成的对象集了，这样就包含了 EntityContainer 对象的名称，它代表了后缀为EntitySet的EntityContainer。

Image 注意EntityContainer元素以数据库模式命名，所有逻辑上应该组合在一起的“实体集”都包含在一个EntityContainer元素中。一个EntitySet代表数据库中相应的表。您可以在.edmx文件的ConceptualModel元素下浏览您的EntityModel对象的名称，如图图 19-10 所示。

`foreach (var cr in currContext.Currencies)                 {                     txtCurrency .AppendText(cr.ModifiedDate.ToString());

                    txtCurrency.AppendText("\t\t");                     txtCurrency.AppendText(cr.CurrencyCode.ToString());

                    txtCurrency.AppendText("\t\t");                     txtCurrency.AppendText(cr.Name.ToString());                     txtCurrency.AppendText("\t");                     txtCurrency.AppendText("\n");                 }`

如您所见，EntityContainer对象通过智能感知公开了列名。或者，如果你放一个.(点)，你会看到销售的所有领域。货币表，这比您在前一章中试验的 DataReader 的rdr[0], rdr[1]技术更简单。换句话说，实体框架已经“映射”了来自销售的每个记录。货币表转换为对象。这些属性与表中的列同名，但是使用对象符合面向对象的编码风格。

试试看:使用实体数据模型的模式抽象

在前面的练习中，您创建了一个名为 AWCurrencyModel 的实体数据模型(因为这是您的.edmx文件的名称)；在本练习中，您将看到该实体数据模型如何帮助开发人员实现模式抽象和修改数据库，而无需在整个项目中或数据访问层(DAL)中接触数据访问代码。也就是说，开发人员可以简单地从模型中移除表引用，然后再将它添加回来。这些列将被重新对齐，然后可以更新代码来引用相应的属性。

  1. 启动 SQL Server Management Studio，展开“数据库”节点，展开“AdventureWorks 数据库”节点，然后展开“表”节点。在表列表中，展开 Sales。货币节点，然后展开Columns文件夹。

  2. 选择“名称”列，右键单击，然后选择“重命名”选项。将 name 列重命名为 CurrencyName。

  3. 选择“修改日期”列，右键单击，然后选择“重命名”选项。将 ModifiedDate 列重命名为 ModifiedCurrencyDate。

  4. 所以，基本上，我们在这两列中添加了货币术语。现在，通过选择文件Image退出，退出 SQL Server Management Studio。

  5. As you can imagine, our PublishCurrency form and the database have a column name mismatch, so we will now view the exception that the application will report because of this recent column name change. To do so, we will add a TRY…CATCH block to report the issue. Modify the PublishCurrency.cs code to look like Listing 19-2.

    清单 19-2*。*将TRY…CATCH添加到PublishCurrency.cs以显示异常详情

    `try    {      AWCurrencyEntities currContext = new AWCurrencyEntities();

                   foreach (var cr in currContext.Currencies)                {                      txtCurrency .AppendText(cr.ModifiedDate.ToString());

                         txtCurrency.AppendText("\t\t");                      txtCurrency.AppendText(cr.CurrencyCode.ToString());

                         txtCurrency.AppendText("\t\t");                      txtCurrency.AppendText(cr.Name.ToString());                      txtCurrency.AppendText("\t");                      txtCurrency.AppendText("\n");        }      }

          catch(Exception ex)       {                MessageBox.Show(ex.Message + ex.StackTrace +                    ex.InnerException);       }`

  6. 现在，通过按 Ctrl+F5 构建并运行 PublishCurrency。PublishCurrency 详细信息表单应该加载并引发一个异常窗口，显示以下消息:“执行命令定义时出错。有关详细信息，请参见内部异常。

  7. If you look at InnerException, you will see a message that indicates the cause of this exception; it’s because you have just renamed the Name and ModifiedDate columns of the Sales.Currency table. The exception details should look like Figure 19-13. Image

    ***图 19-13。*反映最近重命名后无效列名的异常详细信息

  8. 单击确定关闭异常窗口，并关闭打开的表单，该表单将是空的，因为数据因发生异常而未加载。

  9. Now you will see the advantage of entity data modeling. Assume the same issue occurred in the code you wrote in previous chapters; the only solution is to modify the column name in each and every SQL statement that maps to the table we modified. In a real-world scenario, this will not be possible, because database schema updates changes are invisible and so the Entity Data Model comes to rescue.

    要修复这个应用，您必须修改由实体数据模型创建的 XML 映射文件，也就是您在本章前面创建的AWCurrencyModel.edmx文件。要查看 XML 映射，导航到 Solution Explorer，右键单击AWCurrencyModel.edmx，并选择 Open With 选项。从提供的对话框中，选择 XML(文本)编辑器，然后单击确定。你会看到 XML 映射，如前面的图 19-10 所示。

    Image 注意在打开的 XML 映射文件中，导航到<!-- SSDL content -->部分，将<Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" /> XML 标签中的名称修改为CurrencyName；修改后标签应该显示为<Property Name="CurrencyName" Type="nvarchar" Nullable="false" MaxLength="50" />。

    Image 注意表示数据库模式的逻辑模型是使用 SSDL 在 XML 文件中定义的。这就是为什么您需要修改列名来映射数据库模式。

  10. Also, modify the <Property Name="ModifiedDate" Type="datetime" Nullable="false" /> XML tag to ModifiedCurrencyDate <Property Name="ModifiedCurrencyDate" Type="datetime" Nullable="false" /> XML tag to appear as <Property Name="ModifiedCurrencyDate" Type="datetime" Nullable="false" />. The modified SSDL content section with the CurrencyName and ModifiedCurrencyDate values will look like Figure 19-14. Image

***图 19-14。**修改 SSDL 内容部分*
  1. Now look for the <!-- C-S mapping content --> section and modify the <ScalarProperty Name="Name" ColumnName="Name" /> tag to be <ScalarProperty Name="Name" ColumnName="CurrencyName" />.•
![Image](https://p3-xtjj-sign.byteimg.com/tos-cn-i-73owjymdk6/a45145ffad6749c0aafd0b3c7609089b~tplv-73owjymdk6-jj-mark-v1:0:0:0:0:5o6Y6YeR5oqA5pyv56S-5Yy6IEAg5biD5a6i6aOe6b6Z:q75.awebp?rk3s=f64ab15b&x-expires=1771672825&x-signature=mxVg8Fl%2FeXgeyhBr3A%2B9VWWE8n4%3D) **注意**概念模型是使用 CSDL 在 XML 文件中定义的。CSDL 定义了应用的业务层所知道的实体和关系。这就是为什么您需要修改列名，使其可读并易于被实体找到。
  1. Next, modify the <ScalarProperty Name="ModifiedDate" ColumnName="ModifiedDate" /> tag to appear as <ScalarProperty Name="ModifiedDate" ColumnName="ModifiedCurrencyDate" />. The modified C-S mapping content section with the CurrencyName and ModifiedCurrencyDate values will look like Figure 19-15. Image
***图 19-15。**修改`C-S`映射内容段*
  1. 现在保存并构建 Chapter19 解决方案，并运行应用。当 PublishCurrency 表单打开时，应该用ModifiedDate、Name和CurrencyCode值填充文本框，如前面图 19-12 中的所示。
  2. 用foreach循环切换回PublishCurrency.cs代码，如清单 19-2 所示。即使您已经修改了 AdventureWorks 数据库的 Sales 中的列名，您仍然应该看到文本框中显示的相同的列名，其中的EntityContainer为cr.ModifiedDate和cr.Name。货币表。但是通过利用实体数据模型的模式抽象特性，您只需要在 XML 映射文件中的 SSDL 内容和C-S映射内容部分下指定更新的列名。

总结

在本章中，您了解了 ADO.NET 5.0 实体数据模型的特性。

您还了解了模式抽象是如何工作的，以及它将如何帮助您实现数据库和数据访问代码或数据访问层之间的松散耦合。在下一章，你将学习如何使用 SQL CLR 对象。

二十、在 SQL Server 中使用 CLR

多年来，编写业务逻辑一直是特定于技术和软件的，尤其是在数据库方面。例如，如果您想创建一个需要复杂 SQL 代码的存储过程或其他数据库对象，唯一的方法就是在数据库中编写 T-SQL 逻辑，并用 C# 之类的编程语言编写调用代码，如前几章所示，我们在 SQL Server 中使用 T-SQL 创建了一个存储过程，然后用 C# 编写了调用代码。

这种方法仍然非常流行，但是有一种更简单的方法，它允许 C# 程序员控制和编码所有面向数据库的对象，例如。NET 语言，比如 C#，而不像以前那样使用 T-SQL。

在本章中，我将介绍以下内容:

  • 引入 sql clr
  • oot-SQL SQL clr
  • Select enable SQL CLR integration between
  • Create SQL CLR stored procedure
  • Deploy SQL CLR stored procedures to SQL Server
  • Execute SQL CLR stored procedures

SQL CLR 简介

SQL 公共语言运行库(CLR)是。集成到 SQL Server 2005 和更高版本中的. NET CLR。SQL CLR 为开发人员在处理与数据库相关的复杂业务逻辑时提供了一种选择，尤其是当 T-SQL 使这种处理变得不那么愉快时。

SQL CLR 是。NET CLR，它主要通过为已部署的内存管理和代码执行提供支持来实现运行时执行引擎的目的。NET SQL CLR 程序集。一个程序集是一个. NET 术语，指的是由元数据(关于数据的数据)和清单(关于程序集的数据)组成的 DLL 或 EXE 文件。

可以使用 SQL CLR 集成创建以下类型的对象:

  • stored procedure
  • Custom aggregation
  • trigger
  • Custom type

在 T-SQL 和 SQL CLR 之间选择

当您有两种选择来实现相同的功能时，根据您的场景和需求，一种可能比另一种更有优势。这里有几个要点可以帮助您决定在什么情况下选择 T-SQL 还是 SQL CLR:

  • T-SQL is best used to perform declarative, set-based operations (select, insert, update and delete).
  • T-SQL works within a database connection, and SQL CLR must get the connection.
  • T-SQL also has a procedural ability. In other words, it can perform procedural operations such as WHILE, but T-SQL is not the best choice when it comes to rich or more complex logic. In this case, the SQL CLR with C# allows the programmer to better control the functions.
  • T-SQL is interpreted while SQL CLR is compiled. Therefore, interpreted code is slower than compiled procedure code.
  • Before executing the SQL CLR code, it needs the CLR to be loaded by SQL Server, and T-SQL will not generate any such overhead.
  • When any T-SQL code executes, it shares the same stack frame in memory, and each SQLCLR code needs its own stack frame, which will lead to larger memory allocation but better concurrency. T-SQL consists of a library full of data-centric functions, so it is more suitable for collection-based operations. SQL CLR is more suitable for operations of recursive, mathematical and string operation types.

启用 SQL CLR 集成

使用 C# 创建数据库对象后，必须在 SQL Server 2012 中启用 SQL CLR，以便使用或部署它。默认情况下，该功能是关闭的(config_value设为 0)；要启用它(config_value设置为 1)，请遵循以下步骤:

  1. 打开 SQL Server 2012 Management Studio 根据您的安装类型，使用 Windows 或 SQL 身份验证进行连接。

  2. Once connected, click the New Query button, which will open a query window. Enter the following text in the query window, and notice the value of the config_value column, as shown in Figure 20-1. Image

    ***图 20-1。*显示 SQL CLR 的默认行为(禁用)

  3. Next, you need to enable SQL CLR, to do so; modify the code to look like Figure 20-2, and it will enable the SQL CLR integration. Image

    ***图 20-2。*显示 SQL CLR 已启用

现在，您的 SQL Server 已经准备好执行使用 C# 编程语言构建的数据库对象，这与 T-SQL 不同。您将在本章的稍后部分执行此操作。

创建 SQL CLR 存储过程

Microsoft Visual Studio 2012 为各种 SQL Server 对象(如存储过程、触发器、函数等)提供了项目模板和类文件，您可以用 C# 将这些对象编码为动态链接库(DLL)等程序集。

试试看:使用 C# 创建 SQL CLR 存储过程

在本练习中，您将通过向 SQL Server 数据库项目中添加一个 SQL CLR C# 存储过程项模板来创建一个 SQL 存储过程。您将创建的 SQL CLR C# 存储过程将帮助您将货币数据插入 AdventureWorks。Sales.Currency 表，就像你在第十三章和清单 13-4 中所做的那样。但是这里您使用了不同的技术来完成相同的任务(货币插入)。

  1. Create a new Windows Forms Application project named Chapter13. When Solution Explorer opens, save the solution, as shown in Figure 20-3. Image

    ***图 20-3。*展示 SQL Server 数据库项目模板

  2. This will load an empty project, that is, one without any .cs class file in it, as shown in Figure 20-4. Image

    ***图 20-4。*解决方案资源管理器中列出的空项目

  3. Right-click the Chapter20 project, choose Add Image New Item, and in the Add New Item dialog select SQL CLR Stored Procedure on the SQL CLR C# tab. Name it SQLCLRStoredProcedure.cs, as shown in Figure 20-5. Click Add. Image

    ***图 20-5。*将一个 SQL CLR C# 存储过程作为一个新项添加到项目中

  4. Your Visual Studio environment will now look like Figure 20-6. Image

    ***图 20-6。*显示添加 SQL CLR C# 存储过程后的 Visual Studio

  5. Replace the code in the StoredProcedure class with the code in Listing 20-1.

    ***清单 20-1。*T4SQLCLRStoredProcedure.cs

    `[Microsoft.SqlServer.Server.SqlProcedure()]     public static void InsertCurrency_CS(SqlString currencyCode, SqlString currencyName)     {         SqlConnection conn = null;

            try         {             conn = new SqlConnection(@"server = .\sql2012;integrated security = true;                    database = AdventureWorks");

                SqlCommand cmdInsertCurrency = new SqlCommand();             cmdInsertCurrency.Connection = conn;

                SqlParameter parmCurrencyCode = new SqlParameter                               ("@CCode", SqlDbType.NVarChar, 3);             SqlParameter parmCurrencyName = new SqlParameter                               ("@Name", SqlDbType.NVarChar, 50);             parmCurrencyCode.Value = currencyCode;             parmCurrencyName.Value = currencyName;

                cmdInsertCurrency.Parameters.Add(parmCurrencyCode);             cmdInsertCurrency.Parameters.Add(parmCurrencyName);

                cmdInsertCurrency.CommandText =                 "INSERT Sales.Currency (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +                 " VALUES(@CCode, @Name, GetDate())";

                conn.Open();

                cmdInsertCurrency.ExecuteNonQuery();        }

            catch (SqlException ex)         {             SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);         }

            finally         {             conn.Close();         }     }`

  6. 保存项目，并生成解决方案。成功构建后，它将在项目的\bin\debug 文件夹下生成一个 Chapter20.dll 文件。

它是如何工作的

因为这是用 C# 编写的存储过程，所以它将把货币数据插入到 AdventureWorks 中。Sales.Currency 表，它有三列。其中，您将传递两个值作为输入参数。

      [Microsoft.SqlServer.Server.SqlProcedure()]         public static void InsertCurrency_CS(SqlString currencyCode, SqlString currencyName)

任何数据库应用最重要的部分是创建连接和命令。

            SqlConnection conn = null;             conn = new SqlConnection(@"server = .\sql2012;integrated security = true;                                  database = AdventureWorks");             SqlCommand cmdInsertCurrency = new SqlCommand();             cmdInsertCurrency.Connection = conn;

一旦有了连接和命令对象，就需要设置这个存储过程将接受的参数。

`SqlParameter parmCurrencyCode = new SqlParameter("@CCode", SqlDbType.NVarChar, 3);             SqlParameter parmCurrencyName = new SqlParameter                        ("@Name", SqlDbType.NVarChar, 50);

            parmCurrencyCode.Value = currencyCode;             parmCurrencyName.Value = currencyName;

            cmdInsertCurrency.Parameters.Add(parmCurrencyCode);             cmdInsertCurrency.Parameters.Add(parmCurrencyName);`

设置完参数后，您将设置INSERT语句，该语句将执行实际的任务，但是因为您只为这个销售选择了两个参数。货币表，对于第三列，即日期列，您将传递GetDate()函数。

      cmdInsertCurrency.CommandText ="INSERT Sales.Currency                                 (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +                                 " VALUES(@CCode, @Name, GetDate())";

接下来，打开连接并执行命令。

            conn.Open();             cmdInsertCurrency.ExecuteNonQuery();

需要记住的最重要的一点是，这段代码实际上是从 SQL Server Management Studio 内部调用的，因此异常处理catch块需要特别注意。

       catch (SqlException ex)        {          SqlContext.Pipe.Send("An error occured" + ex.Message + ex.StackTrace);       }

SqlContext类允许您调用函数在 SQL Server 的错误窗口中显示错误。

将 SQL CLR 存储过程部署到 SQL Server 中

一旦为特定类型的数据库对象创建了 SQL CLR C# 类型的程序集，就需要将其部署到 SQL Server 中。部署后，SQL Server 可以像使用任何其他 T-SQL 数据库对象一样使用它。

尝试一下:在 SQL Server 中部署 SQL CLR C# 存储过程

在本练习中，您将把创建的程序集部署到SQL2012Db数据库中，在执行时，这将把货币插入 AdventureWorks 中。销售。货币表。

  1. 打开 SQL Server 2012 Management Studio，并连接到 SQL Server。

  2. 选择 SQL2012 数据库(如果您没有这个数据库，您可以使用您选择的任何数据库)，然后单击 New Query，这将打开一个新的空白查询窗口。

  3. In the opened query window, insert the code in Listing 20-2.

    ***清单 20-2。*将程序集部署到 SQL Server 中

    `Create Assembly SQLCLR_StoredProcedure From --change this path to reflect your database assebmly location 'C:\VidyaVrat\C#2012 and SQL 2012\Chapter20\Code\Chapter20\bin\Debug\Chapter20.dll' WITH PERMISSION_SET = UNSAFE GO

    CREATE PROCEDURE dbo.InsertCurrency_CS (   @currCode nvarchar(3),   @currName nvarchar(50) ) AS EXTERNAL NAME SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS;`

  4. Once code is added, click Execute or press F5. This should execute the command successfully. Then go to the Object Browser, select SQL2012DB, right-click, and choose Refresh. This will show the objects under Programmability and Assemblies in the Object Browser, as shown in Figure 20-7. Image

    ***图 20-7。*在 SQL Server 中部署程序集并在对象浏览器中显示对象

工作原理

此部署过程分为两步。首先，您必须在 SQL Server 中用自己的名字注册一个程序集(您用 C# 创建的)。

Create Assembly SQLCLR_StoredProcedure from 'C:\VidyaVrat\C#2012 and SQL 2012\Chapter20\Code\Chapter20\bin\Debug\Chapter20.dll' WITH PERMISSION_SET = UNSAFE GO

这个PERMISSION_SET属性允许用户执行具有特定代码访问权限的程序集。UNSAFE允许此程序集在 SQL Server 中拥有不受限制的访问权限。

其次，您必须创建存储过程，这将基本上调用您从 C# 程序集创建的存储过程。

CREATE PROCEDURE dbo.InsertCurrency_CS (   @currCode nvarchar(3),   @currName nvarchar(50) ) AS EXTERNAL NAME SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS; GO

CREATE PROCEDURE中使用的名字是你在 C# 类中赋予函数的名字(InsertCurrency_CS)(参考清单 20-1 )。接下来你要设置传递给 C# 函数的输入参数(参见清单 20-1 )。

外部名称实际上在<SQL registered assembly>.<CS class name>.<CS function name>的语法中，所以结果如下:

      SQLCLR_StoredProcedure.StoredProcedures.InsertCurrency_CS

参考清单 20-1 和清单 20-2 中的类名、程序集名等等，这些都在这里使用。

执行 SQL CLR 存储过程

部署程序集并创建存储过程后，您就可以从 SQL 2012 执行此过程，并将货币插入 AdventureWorks。销售。货币表。

试试看:执行 SQL CLR 存储过程

在本练习中，您将执行InsertCurrency_CS存储过程。

  1. 打开 SQL Server Management Studio(如果尚未打开)，选择 SQL2012db 并单击“新建查询”按钮。
  2. 在查询窗口中，添加清单 20-3 中所示的代码来执行该过程并添加一种货币。

***清单 20-3。*执行存储过程插入货币

Exec dbo.InsertCurrency_CS 'ABC','United States of America'

Image 注意你必须为货币代码指定一个唯一的值。例如，我使用 ABC，因为我知道美国没有这样的货币。但是如果您尝试输入 USD 或重复值，您将会收到系统错误。如果你给销售额增加了一个重复的值。对于 CurrencyCode 列，你会得到一个异常，如图图 20-8 所示。

Image

***图 20-8。*出现重复条目时显示参数异常

工作原理

如清单 20-1 中的 C# 代码所示，insert 语句接受两个输入参数，每次执行时会自动传递用于ModifiedCurrencyDate列的GetDate()方法。

              cmdInsertCurrency.CommandText ="INSERT Sales.Currency               (CurrencyCode, CurrencyName, ModifiedCurrencyDate)" +                 " VALUES(@CCode, @Name, GetDate())";

因此，存储过程执行语句将看起来像清单 20-3 ，它为输入参数CurrencyCode和CurrencyName传递值。

Exec dbo.InsertCurrency_CS 'ABC','United States of America'

总结

在本章中，我介绍了 SQL CLR 集成的要点、它的优点以及开发人员可以创建的对象类型。您还了解了如何选择 SQL CLR 而不是 T-SQL，反之亦然。最后，您使用硬编码的 C# 逻辑和关键字(如 try catc h)作为 C# 程序集创建了一个 SQL CLR C# 存储过程。然后部署并执行它来插入一种货币。相当酷！

第一部分：了解工具和基础数据库

第二部分：使用数据库和 XML

第三部分：使用 ADO.NET 处理数据

第四部分：使用高级 ADO.NET 相关功能

avatar
文章
阅读
粉丝