ADO.NET 3.5 Cookbook by Bill Hamilton [O'Reilly]

If you are familiar at all with the "Cookbook" series from O'Reilly, then this is a book that will surely not disappoint you. The nice thing about this book is that it is near 955 pages covering all aspects of ADO.NET from simple things like connection strings for beginners, all the way through advanced LINQ queries for more experienced programmers, and just about everything in - between.

Each "mini-chapter" presents a problem, a solution, detailed graphics and screen captures where appropriate, and sample code. THere are more than 200 coding solutions and best practices for real problems using Visual Studio 2008 and the .NET 3.5 platform. The book is organized to help you find the topic and specific recipe you need quickly and easily.

The solutions are broken down into 12 main chapters to make it easy to know what you are looking for and where to find it. You can check this out on the book site here:  http://www.oreilly.com/catalog/9780596101404/toc.html, and even examine sample content.



The recipes in this book cover subjects like:

  • Connecting to data
  • Retrieving and managing data
  • Transforming and analyzing data
  • Modifying data
  • Binding data to .NET user interfaces
  • Optimizing .NET data access
  • Enumerating and maintaining database objects
  • Maintaining database integrity

Here is a short excerpt from Chapter 3, recipe 21 - Using Table - Valued types as parameters:

You need to pass data to a stored procedure table-valued parameter.

Use the AddWithValue() method of the ParameterCollection for the Command to assign a value to the table-value parameter and specify the SqlDbType of the parameter as Structured from the SqlDbType enumeration.

The solution needs a table named TVPTable in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:
 

USE AdoDotNet35Cookbook
    GO
    CREATE TABLE TVPTable(
        Id int NOT NULL PRIMARY KEY,
        Field1 nvarchar(50) NULL,
        Field2 nvarchar(50) NULL )Execute the following T-SQL statement to create the user-defined table type named TVPType used in this solution:
    USE AdoDotNet35Cookbook
    GO
    CREATE TYPE TVPType AS TABLE (
        Id int,
        Field1 nvarchar(50),
        Field2 nvarchar(50))The solution also uses a stored procedure named InsertTVPTable that takes a table-valued parameter and adds the records in it to the table TVPTable. Execute the following T-SQL statement to create the stored procedure:
    USE AdoDotNet35Cookbook
    GO
    CREATE PROCEDURE InsertTVPTable (
        @tvp TVPType READONLY)
    AS
        SET NOCOUNT ON

        INSERT INTO TVPTable
        SELECT Id, Field1, Field2 FROM @tvp

The solution creates a DataTable named dvTVP that is used to pass a table value into a parameter into the stored procedure InsertTVPTable that inserts a record into the table TVPTable in the AdoDotNet35Cookbook database. A Command object is created for the stored procedure InsertTVPTable and the value of the table-valued parameter is set using the AddWithValue() method of the Parameter object in the ParameterCollection of the Command. The stored procedure is executed using the ExecuteNonQuery() method of the Command object. The contents of the TVPTable table are loaded into a DataTable and output to the console before and after the stored procedure InsertTVPTable is executed.

The C# code in Program.cs in the project TableValuedParameter is shown in .
Example . File: Program.cs for TableValuedParameter solution

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

namespace TableValuedParameter
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = @"Data Source=(local);
                Integrated security=SSPI;Initial Catalog=AdoDotNet35Cookbook;";

            string sqlSelect = "SELECT * FROM TVPTable";

            // Output the contents of the table in the database
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            DataTable dt = new DataTable( );
            da.Fill(dt);
            Console.WriteLine("---INITIAL---");
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("ID = {0}\tField1 = {1}\tField2 = {2}",
                    row["ID"], row["Field1"], row["Field2"]);
            }
            // Create the DataTable that will be used to pass a table
            // into the table-valued parameter
            DataTable dtTVP = new DataTable( );
            dtTVP.Columns.Add("Id", typeof(int));
            dtTVP.Columns.Add("Field1", typeof(string)).MaxLength = 50;
            dtTVP.Columns.Add("Field2", typeof(string)).MaxLength = 50;
            // Add data to the DataTable
            dtTVP.Rows.Add(new object[] { 1, "Field1.1", "Field2.1" });
            dtTVP.Rows.Add(new object[] { 2, "Field1.2", "Field2.2" });
            dtTVP.Rows.Add(new object[] { 3, "Field1.3", "Field2.3" });

            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand("InsertTVPTable", connection);
            command.CommandType = CommandType.StoredProcedure;
            SqlParameter param = command.Parameters.AddWithValue("@tvp", dtTVP);
            param.SqlDbType = SqlDbType.Structured;
            connection.Open( );
            command.ExecuteNonQuery( );
            connection.Close( );
            Console.WriteLine("\n=> Stored procedure with TVP executed.");

            // Output the contents of the table in the database
            dt.Clear( );
            da.Fill(dt);
            Console.WriteLine("\n---FINAL---");
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("ID = {0}\tField1 = {1}\tField2 = {2}",
                    row["ID"], row["Field1"], row["Field2"]);
            }

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey( );
        }
    }
}
Overall this is a book that can be considered quite "feature complete" and is suitable for virtually any developer from the very beginner up to the most advanced senior-level .NET programmer. Recommended! Sticker price, $54.99, with 45 day free online Safari version.
By Peter Bromberg   Popularity  (1880 Views)