Пошаговое руководство. Доступ к базе данных SQL с помощью поставщиков типов (F#)

В этом пошаговом руководстве объясняется, как использовать поставщика типов SqlDataConnection (LINQ-SQL), который доступен в F# версии 3.0 для создания типов данных в базе данных SQL при условии, чо существует активное соединение с базой данных.При отсутствии активных соединений с базой данных, но имеется файл схемы LINQ-SQL (DBML файл), см. раздел Пошаговое руководство. Создание типов F# из файла схемы DBML (F#).

В данном пошаговом руководстве рассмотрены следующие задачи.Эти задачи должны выполняться в следующем порядке, указанном в пошаговом руководстве, для успешного выполнения.

• Подготовка тестовой базы данных

• Создание проекта

• Настройка поставщика типов

• Извлечение данных

• Работа с полями nullable

• Вызов хранимой процедуры

• Обновление базы данных

• Выполнение кода Transact-SQL

• Использование полного контекста данных

• Удаление данных

• Создание тестовой базы данных

Подготовка тестовой базы данных

На сервере, на котором запущен SQL Server, создайте базу данных для тестирования.Можно использовать базу данных, для чего необходимо создать скрипт в нижней части данной страницы в разделе Создание тестовой базы данных.

Подготовка тестовой базы данных

• Чтобы запустить Создание тестовой базы данных, откройте меню Вид, а затем выберите Обозреватель объектов SQL Server или нажмите Ctrl+\, Ctrl+S.В окне Обозреватель объектов SQL Server откройте контекстное меню для соответствующего экземпляра, выберите Новый запрос, скопируйте скрипт в нижней части этой страницы, а затем вставьте скрипт в редактор.Чтобы выполнить скрипт SQL, выберите значок панели инструментов с треугольным символом или нажмите Ctrl+Q.Для получения дополнительных сведений о Обозревателя объектов SQL Server, см. Разработка подключенной базы данных.

Создание проекта

Далее следует создать проект приложения F#.

Чтобы создать и настроить проект

1. Создайте новый проект приложения MFC.

2. Добавьте ссылки на .FSharp.Data.TypeProviders, а также System.Data и System.Data.Linq.

3. Откройте соответствующие пространства имен, добавив следующие строки кода в начало файла кода F# Program.fs.

   open System
   open System.Data
   open System.Data.Linq
   open Microsoft.FSharp.Data.TypeProviders
   open Microsoft.FSharp.Linq
   

4. Как и для большинства программ F#, код в данном пошаговом руководстве можно выполнять как компилируемую программу, или можно запускать в интерактивном режиме как скрипт.Если вы предпочитаете использовать скрипты, чтобы открыть контекстное меню для узла проекта, выберите Добавление нового элемента, добавьте файл скрипта F# и добавления кода в каждом шаге к скрипту.Нужно добавить следующие строки в верхнюю часть файла для загрузки ссылки на сборку.

   #r "System.Data.dll"
   #r "FSharp.Data.TypeProviders.dll"
   #r "System.Data.Linq.dll"
   

   Затем при добавлении каждого блока можно выбрать блок и нажать клавиши Alt+Enter, чтобы запустить его в F# Interactive.

Настройка поставщика типов

На этом шаге создается поставщик типа для данной схемы базы данных.

Настройка поставщика типов прямого подключения к базе данных

• Две выжные строки кода, необходимые для создания типов, которые можно использовать для запроса к базе данных SQL с помощью поставщика типов.Вначале нужно создать экземпляр поставщика типов.Для этого создайте то, что напоминает сокращение типа для SqlDataConnection со статическим универсальным параметром.SqlDataConnection является поставщиком типов SQL, и его не следует путать с типом SqlConnection, который используется при программировании ADO.NET.Если имеется база данных, к которой требуется подключиться, и имеется строка подключения, используйте следующий код для вызова поставщика типов.Замените собственную строку подключения для данной строки.Например, если сервер MYSERVER и экземпляр базы данных INSTANCE, имя базы данных MyDatabase и необходимо использовать проверку подлинности Windows для доступа к базе данных, то строка соединения будет получен в следующем примере кода.

  type dbSchema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;">
  let db = dbSchema.GetDataContext()
  
  // Enable the logging of database activity to the console.
  db.DataContext.Log <- System.Console.Out
  

  Теперь существует тип, dbSchema, который является родительским типом, содержащим все созданные типы, представляющие таблицы базы данных.Также существет объект, db, который имеет в качестве своих членов все таблицы в базе данных.Имена таблиц являются свойствами и тип этих свойств создается компилятором F#.Сами типы отображаются как вложенные типы в dbSchema.ServiceTypes.Поэтому все данные, полученные для строк этих таблиц экземпляр соответствующего типа, который был создан для этой таблицы.Этот тип называется ServiceTypes.Table1.

  Чтобы ознакомиться с тем, как язык F# интерпретирует запросы в запросы SQL, просмотрите строку, которая задает свойство Log контекста данных.

  Для дальнейшего изучения типов созданных поставщиком типов добавьте следующий код.

  let table1 = db.Table1
  

  Наведите указатель мыши на table1, чтобы просмотреть его тип.Его типом является System.Data.Linq.Table<dbSchema.ServiceTypes.Table1> и базовый аргумент подразумевает, что тип каждой строки является созданным типом, dbSchema.ServiceTypes.Table1.Компилятор создает схожий тип для каждой таблицы в базе данных.

Извлечение данных

На этом этапе происходит написание запроса с помощью выражения запроса F#.

Запрос данных

1. Теперь создайте запрос для таблицы в базе данных.Добавьте следующий код.

   let query1 =
           query {
               for row in db.Table1 do
               select row
           }
   query1 |> Seq.iter (fun row -> printfn "%s %d" row.Name row.TestData1)
   

   Представление слова query указывает, что это является выражением запроса, тип выражения вычисления, который создает коллекцию результатов схожих с типичным запросом к базе данных.При наведении указателя мыши на запросом, можно увидеть, что оно является экземпляром Класс Linq.QueryBuilder (F#), тип, который определяет выражение вычисления запроса.При наведении указателя мыши на query1, можно увидеть, что оно является экземпляром IQueryable<T>.Как видо из названия, IQueryable<T> представляет данные, которые можно запрашивать, а не результат запроса.Запрос является объектом для отложенного вычисления, что означает, что база данных запрошена только в том случае, если запрос выполняется.Последняя строка направлять запрос через Seq.iter.Запросы являются перечисляемыми и могут быть доступными для итерации аналогично последовательностям.Дополнительные сведения см. в разделе Выражения запросов (F#).

2. Теперь добавьте оператор запроса к запросу.Существует несколько доступных операторов запросов, которые можно использовать для создания более сложных запросов.В этом примере также показано, что можно исключить переменную запроса и использовать взамен оператор конвейера.

   query {
           for row in db.Table1 do
           where (row.TestData1 > 2)
           select row
         }
   |> Seq.iter (fun row -> printfn "%d %s" row.TestData1 row.Name)
   

3. Добавьте более сложный запрос с соединением двух таблиц.

   query {
           for row1 in db.Table1 do
           join row2 in db.Table2 on (row1.Id = row2.Id)
           select (row1, row2)
   }
   |> Seq.iteri (fun index (row1, row2) ->
        if (index = 0) then printfn "Table1.Id TestData1 TestData2 Name Table2.Id TestData1 TestData2 Name"
        printfn "%d %d %f %s %d %d %f %s" row1.Id row1.TestData1 row1.TestData2 row1.Name
            row2.Id (row2.TestData1.GetValueOrDefault()) (row2.TestData2.GetValueOrDefault()) row2.Name)
   

4. В реальном коде параметры в запросе обычно являются значениями или переменными, но не являются константами во время компиляции.Добавьте следующий код, который упаковывает запрос в функцию, которая принимает параметр, а затем вызовает эту функцию со значением 10.

   let findData param =
       query {
           for row in db.Table1 do
           where (row.TestData1 = param)
           select row
           }
   findData 10 |> Seq.iter (fun row -> printfn "Found row: %d %d %f %s" row.Id row.TestData1 row.TestData2 row.Name)
   

Работа с полями nullable

В базах данных поля обычно допускают значения NULL.В системе типов .NET нельзя использовать обычные типы числовых данных для данных, допускающих значение null, так как для этих типов значения null являются недопустимыми.Поэтому эти значения представлены экземплярами типа Nullable<T>.Вместо обращения к значению таких полей непосредственно с именем поля, необходимо добавить некоторые дополнительные шаги.Можно использовать свойство Value для доступа к базовому значению тип, допускающий значение null.Свойство Value вызывает исключение, если объект имеет значение null.Можно использовать метод HasValue, возвращающий логическое значение, для определения наличия значения или использовать GetValueOrDefault для того, чтобы быть уверенными, что во всех случаях имеется фактическое значение.При использовании GetValueOrDefault и при существовании null в базе данных, оно заменяется значением пустой строки для строковых типов, 0 для целочисленных типов или 0.0 для типов с плавающей запятой.

Если нужно выполнить тесты проверки на равенство или сравнения на равенство значению null в конструкции where, можно использовать операторы обнуляемого типа, которые находятся в Модуль Linq.NullableOperators (F#).Эти схожи с регулярными операторами сравнения =, >, <= и т. д., за исключением того, что вопросительный знак слева или справа от оператора означает, что значение является null.Например, оператор >? больше чем оператор допускающий нулевое значение справа.Способ работы этих операторов заключается в том, что если какая-либо сторона выражения имеет значение null, то выражение имеет значение false.В конструкции where, обычно это означает, что строки, которые содержат поля со значением null не выбраны, а не то, что они возвращаются в результатах запроса.

Работа с полями, допускающими null

• В следующем коде показана работа со значениями null; предположим, что TestData1 целое поле, которое допускает значения null.

  query {
          for row in db.Table2 do
          where (row.TestData1.HasValue && row.TestData1.Value > 2)
          select row
        }
  |> Seq.iter (fun row -> printfn "%d %s" row.TestData1.Value row.Name)
  
  query {
          for row in db.Table2 do
          // Use a nullable operator ?>
          where (row.TestData1 ?> 2)
          select row
        }
  |> Seq.iter (fun row -> printfn "%d %s" (row.TestData1.GetValueOrDefault()) row.Name)
  

Вызов хранимой процедуры

Все хранимые процедуры в базе данных можно вызывать из языка F#.Необходимо задать статический параметр StoredProcedures значением true при создании экземпляра поставщика типов.Поставщик типов SqlDataConnection содержит несколько статических методов, которые можно использовать для настройки созданных типов.Полное описание см. в разделе Поставщик типов SqlDataConnection (F#).Метод типа контекста данных создается для каждой хранимой процедуры.

Вызов хранимой процедуры

• Если хранимые процедуры используют параметры, которые допускает значение null, необходимо передать соответствующее значение Nullable<T>.Возвращаемое значение метода хранимой процедуры, которая возвращает скалярное значение или таблицу, является ISingleResult<T>, содержащий свойства, которые позволяют получить доступ к возвращенным данным.Аргумент типа для ISingleResult<T> зависит от специальной процедуры, а также является одним из типов, которые создаются поставщиком типов.Для именованной хранимой процедуры Procedure1, тип Procedure1Result.Тип Procedure1Result содержит имена столбцов в возвращаемой таблице или для хранимой процедуры, возвращающей скалярное значение, оно представляется возвращаемыс значением.

  В следующем примере предполагается, что в базе данных существует процедура Procedure1, которая принимает два целочисленных значения, допускающих значения null, в качестве параметров, запускает сценарий, который возвращает столбец с именем TestData1, и возвращает целое число.

  
  
  type schema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;",
                                  StoredProcedures = true>
  
  let testdb = schema.GetDataContext()
  
  let nullable value = new System.Nullable<_>(value)
  
  let callProcedure1 a b =
      let results = testdb.Procedure1(nullable a, nullable b)
      for result in results do
          printfn "%d" (result.TestData1.GetValueOrDefault())
      results.ReturnValue :?> int
  
  printfn "Return Value: %d" (callProcedure1 10 20)
  

Обновление базы данных

Тип DataContext LINQ содержит методы, облегчающие выполнение транзакционных обновлений баз данных в полностью типизированном виде с созданными типами.

Обновление базы данных

1. В следующем коде к базе данных добавлены несколько строк.Если только добавить строки, можно использовать InsertOnSubmit, чтобы определить новую строку для добавления.При вставке нескольких строк, необходимо поместить их в коллекцию и вызать InsertAllOnSubmit<TSubEntity>.При вызове любого из этих методов, база данных моментально не изменяется.Необходимо вызвать SubmitChanges , чтобы фактически зафиксировать изменения.По умолчанию все, что необходимо сделать до вызова SubmitChanges является неявно частью одной и той же транзакции.

   let newRecord = new dbSchema.ServiceTypes.Table1(Id = 100,
                                                    TestData1 = 35, 
                                                    TestData2 = 2.0,
                                                    Name = "Testing123")
   let newValues =
       [ for i in [1 .. 10] ->
             new dbSchema.ServiceTypes.Table3(Id = 700 + i,
                                              Name = "Testing" + i.ToString(),
                                              Data = i) ]
   // Insert the new data into the database.
   db.Table1.InsertOnSubmit(newRecord)
   db.Table3.InsertAllOnSubmit(newValues)
   try
       db.DataContext.SubmitChanges()
       printfn "Successfully inserted new rows."
   with
      | exn -> printfn "Exception:\n%s" exn.Message
   

2. Теперь очистка строки путем вызова операции удаления.

   // Now delete what was added.
   db.Table1.DeleteOnSubmit(newRecord)
   db.Table3.DeleteAllOnSubmit(newValues)
   try
       db.DataContext.SubmitChanges()
       printfn "Successfully deleted all pending rows."
   with
      | exn -> printfn "Exception:\n%s" exn.Message
   

Выполнение кода Transact-SQL

Также можно указать Transact-SQL непосредственно с помощью метода ExecuteCommand в классе DataContext.

Выполнение настраиваемых команд SQL

• Следующий код показывает как отправлять команды SQL для вставки в таблицу, а также как удалить записи из таблицы.

  try
     db.DataContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (102, 'Testing', 55)") |> ignore
  with
     | exn -> printfn "Exception:\n%s" exn.Message
  try //AND Name = 'Testing' AND Data = 55
     db.DataContext.ExecuteCommand("DELETE FROM Table3 WHERE Id = 102 ") |> ignore
  with
     | exn -> printfn "Exception:\n%s" exn.Message
  

Использование полного контекста данных

В предыдущих примерах, метод GetDataContext использовался для получения того, что называется упрощенный контекст данных для схемы базы данных.Упрощенный контекст данных легче использовать при построении запросов, так как не существует такого большого количества доступных членов.Поэтому при просмотре свойства в IntelliSense можно сфокусироваться на структуре базы данных, например на таблицах и хранимых процедурах.Однако существуют ограничения в том, что можно сделать с упрощенным контекстом данных.Полный контекст данных, который предоставляет возможность выполнять другие действия.Он размещен в ServiceTypes и имеет имя статического параметра DataContext в случае его предоставления.Если оно не предоставляется, то имя типа контекста данных создается автоматически при помощи SqlMetal.exe на основе другом входа.Полный контекст данных наследуется от DataContext и предоставляет доступ к членам базового класса, включая ссылки на типы данных ADO.NET такие, как объект Connection, методы такие, как ExecuteCommand и ExecuteQuery, который можно использовать для создания запросов SQL, а также способы работы с транзакциями в явном виде.

Использование полного контекста данных

• Следующий код демонстрирует получение полного объект контекста данных и использование его для выполнения команды непосредственно в отношении базы данных.В данном случае две команды выполняются в рамках одной и той же транзакции.

  let dbConnection = testdb.Connection
  let fullContext = new dbSchema.ServiceTypes.MyDatabase(dbConnection)
  dbConnection.Open()
  let transaction = dbConnection.BeginTransaction()
  fullContext.Transaction <- transaction
  try
      let result1 = fullContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (102, 'A', 55)")
      printfn "ExecuteCommand Result: %d" result1
      let result2 = fullContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (103, 'B', -2)")
      printfn "ExecuteCommand Result: %d" result2
      if (result1 <> 1 || result2 <> 1) then
          transaction.Rollback()
          printfn "Rolled back creation of two new rows."
      else
          transaction.Commit()
          printfn "Successfully committed two new rows."
  with
      | exn -> transaction.Rollback()
               printfn "Rolled back creation of two new rows due to exception:\n%s" exn.Message
  
  dbConnection.Close()
  

Удаление данных

Этот шаг демонстрирует, как удалять строки из таблицы данных.

Удаление строк из базы данных (LINQ-SQL)

• Теперь очищает все добавленные строки путем написания функции, которая удаляет строки из указанной таблицы, экземпляра класса Table<TEntity>.Затем следует написать запрос для нахождения всех строк, которые необходимо удалить, и передать результат запроса в функцию deleteRows.Этот код использует преимущества возможности обеспечения частичного применения аргументов функции.

  let deleteRowsFrom (table:Table<_>) rows =
      table.DeleteAllOnSubmit(rows)
  
  query {
      for rows in db.Table3 do
      where (rows.Id > 10)
      select rows
      }
  |> deleteRowsFrom db.Table3
  
  db.DataContext.SubmitChanges()
  printfn "Successfully deleted rows with Id greater than 10 in Table3."
  

Создание тестовой базы данных

Этот раздел показывает, как настроить тестовую базу данных для использования в данном пошаговом руководстве.

Обратите внимание, что при редактировании базы данных каким-либо образом, можно сбросить поставщика типов.Сброс поставщика типов, повторное построение или очиститка проект, в котором содержится поставщик типа.

Создание тестовой базы данных

1. В Обозреватель серверов откройте контекстное меню для узла Подключения данных и выберите Добавить подключение.Откроется диалоговое окно Добавить подключение.

2. В окне Имя сервера укажите имя экземпляра SQL Server, который будет иметь административный доступ или, при отсутствии доступа к серверу, укажите (localdb\v11.0).SQL Express LocalDB предоставляет упрощенный сервер базы данных для разработки и тестирования на компьютере.Новый узел создан в Обозреватель серверов в Подключения данных.Дополнительные сведения о LocalDB см. в разделе Пошаговое руководство. Создание локальной базы данных.

3. Откройте контекстное меню для нового узла подключения и выберите Новый запрос.

5. Скопируйте следующий скрипт SQL, вставьте его в редактор запросов и нажмите кнопку Выполнить на панели инструментов или нажмите Ctrl+Shift+E.

   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   USE [master];
   GO
   
   IF EXISTS (SELECT * FROM sys.databases WHERE name = 'MyDatabase')
                   DROP DATABASE MyDatabase;
   GO
   
   -- Create the MyDatabase database.
   CREATE DATABASE MyDatabase;
   GO
   
   -- Specify a simple recovery model 
   -- to keep the log growth to a minimum.
   ALTER DATABASE MyDatabase 
                   SET RECOVERY SIMPLE;
   GO
   
   USE MyDatabase;
   GO
   
   -- Create the Table1 table.
   CREATE TABLE [dbo].[Table1] (
       [Id]        INT        NOT NULL,
       [TestData1] INT        NOT NULL,
       [TestData2] FLOAT (53) NOT NULL,
       [Name]      NTEXT      NOT NULL,
       PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   
   --Create Table2.
   CREATE TABLE [dbo].[Table2] (
       [Id]        INT        NOT NULL,
       [TestData1] INT        NULL,
       [TestData2] FLOAT (53) NULL,
       [Name]      NTEXT      NOT NULL,
       PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   
   
   --     Create Table3.
   CREATE TABLE [dbo].[Table3] (
       [Id]   INT           NOT NULL,
       [Name] NVARCHAR (50) NOT NULL,
       [Data] INT           NOT NULL,
       PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   GO
   
   CREATE PROCEDURE [dbo].[Procedure1]
          @param1 int = 0,
          @param2 int
   AS
          SELECT TestData1 FROM Table1
   RETURN 0
   GO
   
   -- Insert data into the Table1 table.
   USE MyDatabase
   
   INSERT INTO Table1 (Id, TestData1, TestData2, Name)
   VALUES(1, 10, 5.5, 'Testing1');
   INSERT INTO Table1 (Id, TestData1, TestData2, Name)
   VALUES(2, 20, -1.2, 'Testing2');
   
   --Insert data into the Table2 table.
   INSERT INTO Table2 (Id, TestData1, TestData2, Name)
   VALUES(1, 10, 5.5, 'Testing1');
   INSERT INTO Table2 (Id, TestData1, TestData2, Name)
   VALUES(2, 20, -1.2, 'Testing2');
   INSERT INTO Table2 (Id, TestData1, TestData2, Name)
   VALUES(3, NULL, NULL, 'Testing3');
   
   INSERT INTO Table3 (Id, Name, Data)
   VALUES (1, 'Testing1', 10);
   INSERT INTO Table3 (Id, Name, Data)
   VALUES (2, 'Testing2', 100);
   

См. также

Задачи

Пошаговое руководство. Создание типов F# из файла схемы DBML (F#)

Ссылки

Поставщик типов SqlDataConnection (F#)

Выражения запросов (F#)

SqlMetal.exe (средство создания кода)

Другие ресурсы

Поставщики типов

LINQ to SQL