Compartir a través de

How to: Create, Read, Update, and Delete Data using ADO.NET

  • Artículo

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

This topic provides examples of using ADO.NET to programmatically create, read, update, and delete data in the SQL Server Modeling Services database.

Note

This topic uses a HumanResources model. For more information about how to create this model and install it in the Modeling Services database, see How to: Install the HumanResources Sample Model using Visual Studio.

At the end of this tutorial, you will use ADO.NET to do the following tasks.

  • Connect to the local instance of the SQL Server Modeling Services database.

  • Create a new Folder named HumanResourcesExampleFolder that contains employee instances.

  • Add employee instances to the [HumanResources].[Employees] view.

  • Query a list of employees from the database.

  • Update an employee instance.

  • Remove the HumanResourcesExampleFolder Folder and its contents.

To create an ADO.NET console application that connects to the Modeling Services database

  1. Open Visual Studio 2010.

  2. Create a new console application project. In the New Project dialog, select Visual Basic or Visual C#. Select the Console Application template, and name it RepAccess_ADONET. Use the default Location.

  3. Add two using statements (Imports in Visual Basic) to include the System.Data and System.Data.SqlClient namespaces.

    Imports System.Data
Imports System.Data.SqlClient

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

  4. Replace the Main method with the following code. This code opens a database connection to the local instance of the Modeling Services database. It also adds basic error handling.

    Sub Main()
   ' Use integrated authentication to connect to the Repository database on the local machine.
   Dim connectionString As String = "Data Source=(local); Initial Catalog=Repository; Integrated Security=true"
   Dim connection As SqlConnection = New SqlConnection(connectionString)

   Try
      connection.Open()

   Catch sqlException As SqlException
      Console.WriteLine("A SQL error occurred ({0}): {1}" & Environment.NewLine, _
                        sqlException.Number, sqlException.Message)
   Catch exception As Exception
      Console.WriteLine("An unexpected error occurred: {0}" & Environment.NewLine, exception.Message)
   Finally
      If connection.State = ConnectionState.Open Then
         connection.Close()
      End If
      Console.ReadKey()
   End Try
End Sub

    static void Main(string[] args)
{
   // Use integrated authentication to connect to the Repository database on the local machine.
   string connectionString = "Data Source=(local); Initial Catalog=Repository; Integrated Security=true";

   try
   {
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
         connection.Open();

      }
   }
   catch (SqlException sqlException)
   {
      Console.WriteLine("A SQL error occurred ({0}): {1}" + Environment.NewLine, sqlException.Number, sqlException.Message);
      Console.ReadKey();
   }
   catch (Exception exception)
   {
      Console.WriteLine("An unexpected error occurred: {0}" + Environment.NewLine, exception.Message);
   }
   finally
   {
      Console.ReadKey();
   }
}

  5. On the Build menu, click Build Solution. The project should build successfully with no errors and one warning.

To obtain a Folder identifier for a given path

  1. After the Main method, add a new method named GetFolder. This method calls the [Repository.Item].[PathsFolder] function to look for an existing Modeling Services Folder based on the path name. You will use this method to obtain the identifier of the top-level Repository folder, which will be the parent for the custom Folder in this example.

    ' Given a Folder path, returns an existing Folder identifier.
Function GetFolder(ByVal connection As SqlConnection, ByVal folderPath As String) As Integer
   Dim folderId As Integer = -1

   Dim cmd As New SqlCommand("select [Repository.Item].[PathsFolder](@path)", connection)
   cmd.CommandType = CommandType.Text
   cmd.Parameters.Add(New SqlParameter("@path", folderPath))

   Dim objReturned As Object = cmd.ExecuteScalar
   If ((Not objReturned Is Nothing) AndAlso (Not objReturned Is DBNull.Value)) Then
      folderId = CInt(objReturned)
   End If

   Return folderId
End Function

    // Given a Folder path, returns an existing Folder identifier.
private static int GetFolder(SqlConnection connection, string folderPath)
{
   int folderId = -1;

   SqlCommand cmd = new SqlCommand("select [Repository.Item].[PathsFolder](@path)", connection);
   cmd.CommandType = CommandType.Text;
   cmd.Parameters.Add(new SqlParameter("@path", folderPath));

   object objReturned = cmd.ExecuteScalar();
   if (objReturned != null && objReturned != DBNull.Value)
      folderId = (int)objReturned;

   return folderId;
}

To create a new Modeling Services Folder

  1. Add a new method named AddFolder. This method inserts a new Folder in the [Repository.Item].[Folders] view.

    ' Creates a new Folder in the [Repository.Item].[Folders] view.
Function AddFolder(ByVal connection As SqlConnection, ByVal Name As String, _
                   ByVal ParentFolder As Long) As Long
   Dim folderId As Long = -1

   ' Setup the SqlParameter variables, accounting for possible NULL values
   Dim nameParam As New SqlParameter("@Name", Name)
   Dim parentParam As New SqlParameter("@ParentFolder", ParentFolder)
   If (ParentFolder <= 0) Then
      parentParam.Value = DBNull.Value
   End If

   Dim cmd As New SqlCommand
   cmd.CommandText = "insert into [Repository.Item].[Folders] " & _
      "(Name, Folder) values (@Name, @ParentFolder)"
   cmd.CommandType = CommandType.Text
   cmd.Connection = connection

   cmd.Parameters.Add(nameParam)
   cmd.Parameters.Add(parentParam)

   cmd.ExecuteNonQuery()

   ' Query to obtain the identifier for the new Folder. Reuse parameters.
   cmd.CommandText = "select Id from [Repository.Item].[Folders] where Name = @Name and " & _
      "(Folder = @ParentFolder or (@ParentFolder is null and Folder is null))"
   Dim folderObject As Object = cmd.ExecuteScalar()
   If (folderObject <> Nothing) Then
      folderId = CLng(folderObject)
   End If

   Return folderId
End Function

    // Creates a new Folder in the [Repository.Item].[Folders] view.
private static long AddFolder(SqlConnection connection, string Name, long ParentFolder)
{
   long folderId = -1;

   // Setup the SqlParameter variables, accounting for possible NULL values
   SqlParameter nameParam = new SqlParameter("@Name", Name);
   SqlParameter parentParam = new SqlParameter("@ParentFolder", ParentFolder);
   if (ParentFolder <= 0)
      parentParam.Value = DBNull.Value;

   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "insert into [Repository.Item].[Folders] (Name, Folder) " +
       "values (@Name, @ParentFolder)";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = connection;

   cmd.Parameters.Add(new SqlParameter("@Id", folderId));
   cmd.Parameters.Add(nameParam);
   cmd.Parameters.Add(parentParam);

   cmd.ExecuteNonQuery();

   // Query to obtain the identifier for the new Folder. Reuse parameters.
   cmd.CommandText = "select Id from [Repository.Item].[Folders] where Name = @Name and " +
      "(Folder = @ParentFolder or (@ParentFolder is null and Folder is null))";
   object folderObject = cmd.ExecuteScalar();
   if (folderObject != null)
      folderId = (int)folderObject;

   return folderId;
}

To create a new employee in the [HumanResources].[Employees] table

  1. Add a new method named AddEmployee. This method inserts a new employee into the [HumanResources].[Employees] table. It also uses the SQL Server @@identity function to get the identifier of the new employee row.

    Note

    Note that this new employee instance is associated with the Folder identifier passed into the Folder parameter.

    ' Adds an employee to the [HumanResources].[Employees] table.
Function AddEmployee(ByVal connection As SqlConnection, ByVal Name As String, ByVal Folder As Long) As Long
   Dim employeeId As Long = -1

   Dim cmd As New SqlCommand("insert into [HumanResources].[Employees] (Name, Folder) " & _
          "values(@Name, @Folder)", connection)
   cmd.Parameters.Add(New SqlParameter("@Name", Name))
   cmd.Parameters.Add(New SqlParameter("@Folder", Folder))

   cmd.ExecuteNonQuery()

   ' Query to obtain the identifier for the new Employee.
   cmd.CommandText = "select cast(@@identity as bigint)"
   Dim employeeObject As Object = cmd.ExecuteScalar()
   If (employeeObject <> Nothing) Then
      employeeId = CLng(employeeObject)
   End If

   Return employeeId
End Function

    // Adds an employee to the [HumanResources].[Employees] table.
private static long AddEmployee(SqlConnection connection, string Name, long Folder)
{
   long employeeId = -1;

   SqlCommand cmd = new SqlCommand("insert into [HumanResources].[Employees] (Name, Folder) " +
       "values(@Name, @Folder)", connection);
   cmd.Parameters.Add(new SqlParameter("@Name", Name));
   cmd.Parameters.Add(new SqlParameter("@Folder", Folder));

   cmd.ExecuteNonQuery();

   // Query to obtain the identifier for the new Employee.
   cmd.CommandText = "select cast(@@identity as bigint)";
   object employeeObject = cmd.ExecuteScalar();
   if (employeeObject != null)
      employeeId = (long)employeeObject;

   return employeeId;
}

To query the contents of the [HumanResources].[Employees] table

  1. Add a new method named DisplayEmployeeDetails. This method performs a simple select against the [HumanResources].[Employees] view. A SqlDataReader displays the information for the employee whose Id matches the employeeId parameter.

    Sub DisplayEmployeeDetails(ByVal connection As SqlConnection, ByVal employeeId As Long)
   Dim cmd As New SqlCommand
   cmd.CommandText = "select Id, Name, Folder from " & _
      "[HumanResources].[Employees] where Id = @EmployeeId"
   cmd.CommandType = CommandType.Text
   cmd.Connection = connection

   cmd.Parameters.Add(New SqlParameter("@EmployeeId", employeeId))

   Dim reader As SqlDataReader = cmd.ExecuteReader
   Do While reader.Read
      Console.WriteLine("--------------------------------------------------------")
      Console.WriteLine("Id:          {0}", reader("Id").ToString())
      Console.WriteLine("Name:        {0}", reader("Name").ToString())
      Console.WriteLine("Folder:      {0}", reader("Folder").ToString())
      Console.WriteLine(Environment.NewLine)
   Loop

   reader.Close()
End Sub

    private static void DisplayEmployeeDetails(SqlConnection connection, long employeeId)
{
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "select Id, Name, Folder " +
       "from [HumanResources].[Employees] where Id = @EmployeeId";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = connection;

   cmd.Parameters.Add(new SqlParameter("@EmployeeId", employeeId));

   SqlDataReader reader = cmd.ExecuteReader();
   while (reader.Read())
   {
      Console.WriteLine("--------------------------------------------------------");
      Console.WriteLine("Id:          {0}", reader["Id"].ToString());
      Console.WriteLine("Name:        {0}", reader["Name"].ToString());
      Console.WriteLine("Folder:      {0}", reader["Folder"].ToString());
      Console.WriteLine(Environment.NewLine);
   }

   reader.Close();
}

To update the [HumanResources].[Employees] table

  1. Add a new method named ChangeEmployeeName. This method updates the name of an employee in the [HumanResources].[Employees] view.

    Sub ChangeEmployeeName(ByVal connection As SqlConnection, ByVal employeeId As Long, ByVal newName As String)
   Dim cmd As New SqlCommand
   cmd.CommandText = "update [HumanResources].[Employees] set Name = @NewName " & _
      " where Id = @EmployeeId"
   cmd.CommandType = CommandType.Text
   cmd.Connection = connection

   cmd.Parameters.Add(New SqlParameter("@EmployeeId", employeeId))
   cmd.Parameters.Add(New SqlParameter("@NewName", newName))

   cmd.ExecuteNonQuery()
End Sub

    private static void ChangeEmployeeName(SqlConnection connection, long employeeId, string newName)
{
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "update [HumanResources].[Employees] set Name = @NewName " +
       " where Id = @EmployeeId";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = connection;

   cmd.Parameters.Add(new SqlParameter("@EmployeeId", employeeId));
   cmd.Parameters.Add(new SqlParameter("@NewName", newName));

   cmd.ExecuteNonQuery();
}

To remove a Folder and all of its associated data

  1. Add a new method named RemoveFolder. This method deletes a Folder in the [Repository.Item].[Folders] view.

    Sub RemoveFolder(ByVal connection As SqlConnection, ByVal targetFolder As Long)
   Dim cmd As New SqlCommand
   cmd.CommandText = "delete [Repository.Item].[Folders] where Id = @FolderId"
   cmd.CommandType = CommandType.Text
   cmd.Connection = connection

   cmd.Parameters.Add(New SqlParameter("@FolderId", targetFolder))

   cmd.ExecuteNonQuery()
End Sub

    private static void RemoveFolder(SqlConnection connection, long targetFolder)
{
   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "delete [Repository.Item].[Folders] where Id = @FolderId";
   cmd.CommandType = CommandType.Text;
   cmd.Connection = connection;

   cmd.Parameters.Add(new SqlParameter("@FolderId", targetFolder));

   cmd.ExecuteNonQuery();
}

To modify the Main method to create, read, update, and delete Modeling Services data

  1. In the Main method, add the following code inside the try block, immediately following the call to open the database connection. This code uses the GetFolder and AddFolder methods described earlier to create a new Folder, HumanResourcesFolder. The code creates the new Folder as a subfolder Folder of the system-provided Repository Folder.

    ' Check to see if the target Folder for this example already exists.
Dim targetFolder As Long = GetFolder(connection, "Repository/HumanResourcesFolder")
If (targetFolder = -1) Then
   Dim RepositoryFolder As Long = GetFolder(connection, "Repository")

   ' Create a Folder named HumanResourcesFolder that is a
   ' subfolder of the top-level Repository Folder.
   targetFolder = AddFolder(connection, "HumanResourcesFolder", RepositoryFolder)

   If (targetFolder = -1) Then
      Throw New Exception(Environment.NewLine & _
         "Unable to add target Folder: Repository/HumanResourcesFolder" & Environment.NewLine)
   End If
End If

    // Check to see if the target Folder for this example already exists.
long targetFolder = GetFolder(connection, "Repository/HumanResourcesFolder");
if (targetFolder == -1)
{
   long RepositoryFolder = GetFolder(connection, "Repository");

   // Create a Folder named HumanResourcesFolder that is a
   // subfolder of the top-level Repository Folder.
   targetFolder = AddFolder(connection, "HumanResourcesFolder", RepositoryFolder);

   if (targetFolder == -1)
      throw (new Exception(Environment.NewLine + "Unable to add target Folder: Repository/HumanResourcesFolder" +
         Environment.NewLine));
}

  2. After the previous code, add a call to the AddEmployee method to add a new employee belonging to the HumanResourcesFolder Folder.

    ' Now add an employee to the [HumanResources].[Employees] table. This new item
' is owned by the Repository/HumanResourcesFolder Folder.
Dim employeeId As Long
employeeId = AddEmployee(connection, "John", targetFolder)

    // Now add an employee to the [HumanResources].[Employees] table. This new item
// is owned by the Repository/HumanResourcesFolder Folder.
long employeeId = AddEmployee(connection, "John", targetFolder);

  3. Next add a call to the DisplayEmployeeDetails method to verify that the new row was added and to display part of its information.

    ' Confirm the data was added to the Repository.
Console.WriteLine("Added employee:")
DisplayEmployeeDetails(connection, employeeId)

    // Confirm the data was added to the Repository.
Console.WriteLine("Added employee:");
DisplayEmployeeDetails(connection, employeeId);

  4. Add a call to the ChangeEmployeeName method to change the employee name. Then call DisplayEmployeeDetails to verify the change.

    ' Change the data
Console.WriteLine("Changing the employee's name:")
ChangeEmployeeName(connection, employeeId, "Jonathan")
DisplayEmployeeDetails(connection, employeeId)

    // Change the employee's name.
Console.WriteLine("Changing the employee's name:");
ChangeEmployeeName(connection, employeeId, "Jonathan");
DisplayEmployeeDetails(connection, employeeId);

  5. Finally, use the RemoveFolder method to delete the Folder from the database. Folder design specifies that the contents of the Folder are also removed. As a result, this deletes the previously added row in the [HumanResources].[Employees] view.

    ' Clean up by deleting the Folder. Deleting the Folder deletes the rows associated
' with the Folder. In this case the row added to the Employees view is removed.
Console.WriteLine(("Deleting Folder and Folder contents..." & Environment.NewLine))
RemoveFolder(connection, targetFolder)
Console.WriteLine(("Done." & Environment.NewLine))

    // Clean up by deleting the Folder. Deleting the Folder deletes the rows associated
// with the Folder. In this case the row added to the Employees view is removed.
Console.WriteLine("Deleting Folder and Folder contents..." + Environment.NewLine);
RemoveFolder(connection, targetFolder);
Console.WriteLine("Done." + Environment.NewLine);

  6. To test this code, run the console application and observe the output. On the Debug menu, click Start without Debugging.

Example

The following is the complete source code for the RepAccess_ADONET project.

Imports System.Data
Imports System.Data.SqlClient

Module Module1

   Sub Main()
      ' Use integrated authentication to connect to the Repository database on the local machine.
      Dim connectionString As String = "Data Source=(local); Initial Catalog=Repository; Integrated Security=true"
      Dim connection As SqlConnection = New SqlConnection(connectionString)

      Try
         connection.Open()

         ' Check to see if the target Folder for this example already exists.
         Dim targetFolder As Long = GetFolder(connection, "Repository/HumanResourcesFolder")
         If (targetFolder = -1) Then
            Dim RepositoryFolder As Long = GetFolder(connection, "Repository")

            ' Create a Folder named HumanResourcesFolder that is a
            ' subfolder of the top-level Repository Folder.
            targetFolder = AddFolder(connection, "HumanResourcesFolder", RepositoryFolder)

            If (targetFolder = -1) Then
               Throw New Exception(Environment.NewLine & _
                  "Unable to add target Folder: Repository/HumanResourcesFolder" & Environment.NewLine)
            End If
         End If

         ' Now add an employee to the [HumanResources].[Employees] table. This new item
         ' is owned by the Repository/HumanResourcesFolder Folder.
         Dim employeeId As Long
         employeeId = AddEmployee(connection, "John", targetFolder)

         ' Confirm the data was added to the Repository.
         Console.WriteLine("Added employee:")
         DisplayEmployeeDetails(connection, employeeId)

         ' Change the data
         Console.WriteLine("Changing the employee's name:")
         ChangeEmployeeName(connection, employeeId, "Jonathan")
         DisplayEmployeeDetails(connection, employeeId)

         ' Clean up by deleting the Folder. Deleting the Folder deletes the rows associated
         ' with the Folder. In this case the row added to the Employees view is removed.
         Console.WriteLine(("Deleting Folder and Folder contents..." & Environment.NewLine))
         RemoveFolder(connection, targetFolder)
         Console.WriteLine(("Done." & Environment.NewLine))
      Catch sqlException As SqlException
         Console.WriteLine("A SQL error occurred ({0}): {1}" & Environment.NewLine, _
                           sqlException.Number, sqlException.Message)
      Catch exception As Exception
         Console.WriteLine("An unexpected error occurred: {0}" & Environment.NewLine, exception.Message)
      Finally
         If connection.State = ConnectionState.Open Then
            connection.Close()
         End If
         Console.ReadKey()
      End Try
   End Sub

   ' Given a Folder path, returns an existing Folder identifier.
   Function GetFolder(ByVal connection As SqlConnection, ByVal folderPath As String) As Integer
      Dim folderId As Integer = -1

      Dim cmd As New SqlCommand("select [Repository.Item].[PathsFolder](@path)", connection)
      cmd.CommandType = CommandType.Text
      cmd.Parameters.Add(New SqlParameter("@path", folderPath))

      Dim objReturned As Object = cmd.ExecuteScalar
      If ((Not objReturned Is Nothing) AndAlso (Not objReturned Is DBNull.Value)) Then
         folderId = CInt(objReturned)
      End If

      Return folderId
   End Function

   ' Creates a new Folder in the [Repository.Item].[Folders] view.
   Function AddFolder(ByVal connection As SqlConnection, ByVal Name As String, _
                      ByVal ParentFolder As Long) As Long
      Dim folderId As Long = -1

      ' Setup the SqlParameter variables, accounting for possible NULL values
      Dim nameParam As New SqlParameter("@Name", Name)
      Dim parentParam As New SqlParameter("@ParentFolder", ParentFolder)
      If (ParentFolder <= 0) Then
         parentParam.Value = DBNull.Value
      End If

      Dim cmd As New SqlCommand
      cmd.CommandText = "insert into [Repository.Item].[Folders] " & _
         "(Name, Folder) values (@Name, @ParentFolder)"
      cmd.CommandType = CommandType.Text
      cmd.Connection = connection

      cmd.Parameters.Add(nameParam)
      cmd.Parameters.Add(parentParam)

      cmd.ExecuteNonQuery()

      ' Query to obtain the identifier for the new Folder. Reuse parameters.
      cmd.CommandText = "select Id from [Repository.Item].[Folders] where Name = @Name and " & _
         "(Folder = @ParentFolder or (@ParentFolder is null and Folder is null))"
      Dim folderObject As Object = cmd.ExecuteScalar()
      If (folderObject <> Nothing) Then
         folderId = CLng(folderObject)
      End If

      Return folderId
   End Function

   ' Adds an employee to the [HumanResources].[Employees] table.
   Function AddEmployee(ByVal connection As SqlConnection, ByVal Name As String, ByVal Folder As Long) As Long
      Dim employeeId As Long = -1

      Dim cmd As New SqlCommand("insert into [HumanResources].[Employees] (Name, Folder) " & _
             "values(@Name, @Folder)", connection)
      cmd.Parameters.Add(New SqlParameter("@Name", Name))
      cmd.Parameters.Add(New SqlParameter("@Folder", Folder))

      cmd.ExecuteNonQuery()

      ' Query to obtain the identifier for the new Employee.
      cmd.CommandText = "select cast(@@identity as bigint)"
      Dim employeeObject As Object = cmd.ExecuteScalar()
      If (employeeObject <> Nothing) Then
         employeeId = CLng(employeeObject)
      End If

      Return employeeId
   End Function

   Sub DisplayEmployeeDetails(ByVal connection As SqlConnection, ByVal employeeId As Long)
      Dim cmd As New SqlCommand
      cmd.CommandText = "select Id, Name, Folder from " & _
         "[HumanResources].[Employees] where Id = @EmployeeId"
      cmd.CommandType = CommandType.Text
      cmd.Connection = connection

      cmd.Parameters.Add(New SqlParameter("@EmployeeId", employeeId))

      Dim reader As SqlDataReader = cmd.ExecuteReader
      Do While reader.Read
         Console.WriteLine("--------------------------------------------------------")
         Console.WriteLine("Id:          {0}", reader("Id").ToString())
         Console.WriteLine("Name:        {0}", reader("Name").ToString())
         Console.WriteLine("Folder:      {0}", reader("Folder").ToString())
         Console.WriteLine(Environment.NewLine)
      Loop

      reader.Close()
   End Sub

   Sub ChangeEmployeeName(ByVal connection As SqlConnection, ByVal employeeId As Long, ByVal newName As String)
      Dim cmd As New SqlCommand
      cmd.CommandText = "update [HumanResources].[Employees] set Name = @NewName " & _
         " where Id = @EmployeeId"
      cmd.CommandType = CommandType.Text
      cmd.Connection = connection

      cmd.Parameters.Add(New SqlParameter("@EmployeeId", employeeId))
      cmd.Parameters.Add(New SqlParameter("@NewName", newName))

      cmd.ExecuteNonQuery()
   End Sub

   Sub RemoveFolder(ByVal connection As SqlConnection, ByVal targetFolder As Long)
      Dim cmd As New SqlCommand
      cmd.CommandText = "delete [Repository.Item].[Folders] where Id = @FolderId"
      cmd.CommandType = CommandType.Text
      cmd.Connection = connection

      cmd.Parameters.Add(New SqlParameter("@FolderId", targetFolder))

      cmd.ExecuteNonQuery()
   End Sub

End Module

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

namespace RepAccess_ADONET
{
   class Program
   {
      static void Main(string[] args)
      {
         // Use integrated authentication to connect to the Repository database on the local machine.
         string connectionString = "Data Source=(local); Initial Catalog=Repository; Integrated Security=true";

         try
         {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
               connection.Open();

               // Check to see if the target Folder for this example already exists.
               long targetFolder = GetFolder(connection, "Repository/HumanResourcesFolder");
               if (targetFolder == -1)
               {
                  long RepositoryFolder = GetFolder(connection, "Repository");

                  // Create a Folder named HumanResourcesFolder that is a
                  // subfolder of the top-level Repository Folder.
                  targetFolder = AddFolder(connection, "HumanResourcesFolder", RepositoryFolder);

                  if (targetFolder == -1)
                     throw (new Exception(Environment.NewLine + "Unable to add target Folder: Repository/HumanResourcesFolder" +
                        Environment.NewLine));
               }

               // Now add an employee to the [HumanResources].[Employees] table. This new item
               // is owned by the Repository/HumanResourcesFolder Folder.
               long employeeId = AddEmployee(connection, "John", targetFolder);

               // Confirm the data was added to the Repository.
               Console.WriteLine("Added employee:");
               DisplayEmployeeDetails(connection, employeeId);

               // Change the employee's name.
               Console.WriteLine("Changing the employee's name:");
               ChangeEmployeeName(connection, employeeId, "Jonathan");
               DisplayEmployeeDetails(connection, employeeId);

               // Clean up by deleting the Folder. Deleting the Folder deletes the rows associated
               // with the Folder. In this case the row added to the Employees view is removed.
               Console.WriteLine("Deleting Folder and Folder contents..." + Environment.NewLine);
               RemoveFolder(connection, targetFolder);
               Console.WriteLine("Done." + Environment.NewLine);
            }
         }
         catch (SqlException sqlException)
         {
            Console.WriteLine("A SQL error occurred ({0}): {1}" + Environment.NewLine, sqlException.Number, sqlException.Message);
            Console.ReadKey();
         }
         catch (Exception exception)
         {
            Console.WriteLine("An unexpected error occurred: {0}" + Environment.NewLine, exception.Message);
         }
         finally
         {
            Console.ReadKey();
         }
      }

      // Given a Folder path, returns an existing Folder identifier.
      private static int GetFolder(SqlConnection connection, string folderPath)
      {
         int folderId = -1;

         SqlCommand cmd = new SqlCommand("select [Repository.Item].[PathsFolder](@path)", connection);
         cmd.CommandType = CommandType.Text;
         cmd.Parameters.Add(new SqlParameter("@path", folderPath));

         object objReturned = cmd.ExecuteScalar();
         if (objReturned != null && objReturned != DBNull.Value)
            folderId = (int)objReturned;

         return folderId;
      }

      // Creates a new Folder in the [Repository.Item].[Folders] view.
      private static long AddFolder(SqlConnection connection, string Name, long ParentFolder)
      {
         long folderId = -1;

         // Setup the SqlParameter variables, accounting for possible NULL values
         SqlParameter nameParam = new SqlParameter("@Name", Name);
         SqlParameter parentParam = new SqlParameter("@ParentFolder", ParentFolder);
         if (ParentFolder <= 0)
            parentParam.Value = DBNull.Value;

         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "insert into [Repository.Item].[Folders] (Name, Folder) " +
         "values (@Name, @ParentFolder)";
         cmd.CommandType = CommandType.Text;
         cmd.Connection = connection;

         cmd.Parameters.Add(new SqlParameter("@Id", folderId));
         cmd.Parameters.Add(nameParam);
         cmd.Parameters.Add(parentParam);

         cmd.ExecuteNonQuery();

         // Query to obtain the identifier for the new Folder. Reuse parameters.
         cmd.CommandText = "select Id from [Repository.Item].[Folders] where Name = @Name and " +
         "(Folder = @ParentFolder or (@ParentFolder is null and Folder is null))";
         object folderObject = cmd.ExecuteScalar();
         if (folderObject != null)
            folderId = (int)folderObject;

         return folderId;
      }

      // Adds an employee to the [HumanResources].[Employees] table.
      private static long AddEmployee(SqlConnection connection, string Name, long Folder)
      {
         long employeeId = -1;

         SqlCommand cmd = new SqlCommand("insert into [HumanResources].[Employees] (Name, Folder) " +
         "values(@Name, @Folder)", connection);
         cmd.Parameters.Add(new SqlParameter("@Name", Name));
         cmd.Parameters.Add(new SqlParameter("@Folder", Folder));

         cmd.ExecuteNonQuery();

         // Query to obtain the identifier for the new Employee.
         cmd.CommandText = "select cast(@@identity as bigint)";
         object employeeObject = cmd.ExecuteScalar();
         if (employeeObject != null)
            employeeId = (long)employeeObject;

         return employeeId;
      }

      private static void DisplayEmployeeDetails(SqlConnection connection, long employeeId)
      {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "select Id, Name, Folder " +
         "from [HumanResources].[Employees] where Id = @EmployeeId";
         cmd.CommandType = CommandType.Text;
         cmd.Connection = connection;

         cmd.Parameters.Add(new SqlParameter("@EmployeeId", employeeId));

         SqlDataReader reader = cmd.ExecuteReader();
         while (reader.Read())
         {
            Console.WriteLine("--------------------------------------------------------");
            Console.WriteLine("Id:          {0}", reader["Id"].ToString());
            Console.WriteLine("Name:        {0}", reader["Name"].ToString());
            Console.WriteLine("Folder:      {0}", reader["Folder"].ToString());
            Console.WriteLine(Environment.NewLine);
         }

         reader.Close();
      }

      private static void ChangeEmployeeName(SqlConnection connection, long employeeId, string newName)
      {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "update [HumanResources].[Employees] set Name = @NewName " +
         " where Id = @EmployeeId";
         cmd.CommandType = CommandType.Text;
         cmd.Connection = connection;

         cmd.Parameters.Add(new SqlParameter("@EmployeeId", employeeId));
         cmd.Parameters.Add(new SqlParameter("@NewName", newName));

         cmd.ExecuteNonQuery();
      }

      private static void RemoveFolder(SqlConnection connection, long targetFolder)
      {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = "delete [Repository.Item].[Folders] where Id = @FolderId";
         cmd.CommandType = CommandType.Text;
         cmd.Connection = connection;

         cmd.Parameters.Add(new SqlParameter("@FolderId", targetFolder));

         cmd.ExecuteNonQuery();
      }
   }
}

See Also

Other Resources

Loading Domain Models with Data
Getting Started with "Oslo" Tutorial