concept dbdatareader in category sql

appears as: DbDataReader
SQL Server MVP Deep Dives

This is an excerpt from Manning's book SQL Server MVP Deep Dives.

ADO.NET and SQL Server collaborate to perform the conversions necessary to create a new TABLE type that is usable by T-SQL. The DataTable object is probably the easiest to use and familiar to most .NET database application developers. The DbDataReader object is part of ADO.NET’s alternative factory-method-based objects. SqlDataRecord is used within SQL CLR code, which is the .NET code that executes within the SQL Server process.

Figure 4. Results of passing a DbDataReader as a TVP
Listing 2. Using a DbDataReader object as a TVP
protected void DbDataReaderButton_Click(object sender, EventArgs e) { DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection cnnAW = factory.CreateConnection(); cnnAW.ConnectionString = ConfigurationManager .ConnectionStrings["AWLT"].ConnectionString; DbCommand dbCmd = cnnAW.CreateCommand(); dbCmd.CommandText = "SELECT Productid, [Name] FROM SalesLT.Product"; dbCmd.CommandType = CommandType.Text; cnnAW.Open(); DbDataReader ddr = dbCmd.ExecuteReader(); using (SqlConnection cnn = new SqlConnection(ConfigurationManager .ConnectionStrings["TVPs"].ConnectionString)) { SqlCommand cmd = new SqlCommand("dkSelectFromTVP", cnn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = cmd.Parameters.AddWithValue("@TVParam", ddr); param.TypeName = "dbo.MyTbl"; param.SqlDbType = SqlDbType.Structured; cnn.Open(); SqlDataReader dr = cmd.ExecuteReader(); GridView1.DataSource = dr; GridView1.DataBind(); } }