none
Using Views with tabledirect commandtype? RRS feed

  • Question

  • I had an issue I was researching regarding using views with the tabledirect commandtype.  I wanted to know is it possible to utilize a view as the command text for a SQLcommand with a commandtype of tabledirect.  SQL Server documentation encourages you to believe that you can use a view in most instances just like you use a table, but the tabledirect documentation specifically refers to tables with no mention of views.  This is a point of concern because usually documentation of this sort would say table or view.  This did not. Hence my question.

    However while researching this issue I ran into a bigger source of confusion in that this site http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/8a69e0c7-d839-450f-85bf-1942858766ba/ said about he TableDirect command type "SqlCommand does not support that option.  Honestly though I've never seen a case where this particular option is ever used anyway.  In fact out of the provided classes only OleDbCommand supports this option."

    But this site http://forums.asp.net/t/1496592.aspx reports that he got the following code from a book

     private bool DBConnection(string txtUser, string txtPass)
            {
                SqlConnection myConn = new SqlConnection(ConfigurationManager.AppSettings["strConn"]);
                SqlCommand myCmd = new SqlCommand("Usertbl ", myConn);=
                myCmd.CommandType = CommandType.TableDirect;
                SqlParameter objParam1;
                SqlParameter objParam2;
                SqlParameter returnParam;
                objParam1 = myCmd.Parameters.Add("@UserName", SqlDbType.VarChar);
                objParam2 = myCmd.Parameters.Add("@Password", SqlDbType.VarChar);
                returnParam = myCmd.Parameters.Add("@Num_of_User", SqlDbType.Int);
                objParam1.Direction = ParameterDirection.Input;
                objParam2.Direction = ParameterDirection.Input;
                returnParam.Direction = ParameterDirection.ReturnValue;

    In addition, I've seen a number of entries where people are using the commandtype of tabledirect with the SQLCECommand.

    Hence my confusion.  So my questions

    1. Can you or can you not use the TableDirect CommandType with a sQLCommand?

    2.  If you can then can you call a view using Tabledirect CommandType?

    Thanks in advance for you help.

    I am trying to put this into the ASP.net forum but it does not seem to be available from the drop down.  So I am putting it into the SQL Server Data access forum. 

    ("It shouldn't be this hard.")


    Edward R. Joell MCSD MCDBA
    • Moved by KJian_ Friday, August 26, 2011 2:29 AM (From:SQL Server Data Access)
    Tuesday, August 23, 2011 2:58 PM

All replies

  • Hi joeller,

    The CommandType. TableDirect is not supported by the .Net Framework SqlClient Data Provider. I think you can go to ADO.NET Managed Providers for more help.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    Thursday, August 25, 2011 6:50 AM
  • Really?  Then how was the code above from the book cited above able to work? 

    In additon, another book, Mastering Visual Basic 2010 by Evangelos Petroutsos states on page 667:

    Quote

    Finally, you can retrieve all the rows of the Customers table by setting up a Command object like the following:

    Dim CMD as New SqlCommand
    CMD.Connection = CN
    CMD.CommandText = "Customers"
    CMD.CommandType = CommandType.TableDirect

     End Quote  (emphesis added)

    Also I was not to add this question to that specified forum as it was not listed in the "ask a question" drop down and I don't think I have rights to move the question to that forum.  If is not possible to get an answer in this forum could a moderate please move this question to the ASP.Net forum where I tried to put it originally?


     

    Edward R. Joell MCSD MCDBA

    • Edited by joeller Thursday, August 25, 2011 2:07 PM additional information
    Thursday, August 25, 2011 1:32 PM
  • Hi,

    >>1. Can you or can you not use the TableDirect CommandType with a sQLCommand?

    CommandType.TableDirect: Should be used when you want all records from a table returned. You would specify only the name of the table as the command. This type can only be used with the OLEDB command object, OleDbCommand.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    
    namespace ConnectionString
    {
      class Program
      {
        static void Main(string[] args)
        {
          var connection = new OleDbConnection(@"Provider=SQLOLEDB;Data Source=.\sqlexpress;Integrated Security=SSPI;Initial Catalog=EFTest");
          connection.Open();
          string name = "Employees";
          OleDbCommand cmd = new OleDbCommand(name, connection);
          cmd.CommandType = System.Data.CommandType.TableDirect;
          OleDbDataReader objDR = cmd.ExecuteReader();
          while (objDR.Read())
          {
            string test = objDR[1].ToString();
          }
        }
      }
    }
    
    

    The code works ok on my computer.
    -------------------------------------------------

    The SqlCommand object doesn't support the CommandType.TableDirect command type, so we have to use the System.Data.OleDb namespace and classes. Remember that SQL Server supports the SQLOLEDB provider, so we add this to our connection string as well.

    ------------------------------------------------

    The more information, you can refer here: A Practical Comparison of ADO and ADO.NET

    >>2.  If you can then can you call a view using Tabledirect CommandType?

    When I create a view like:

    create view SelectView
    as 
    select * from Employees
    


    Change the name in my code, it works.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 1, 2011 9:27 AM
    Moderator
  • Thanks Alan for the confirmation that views can be called via tabledirect.  But why would the book, Mastering Visual Basic 2010 by Evangelos Petroutsos state that it can be used in SQLCommand.  Aren't these things checked before information goes out?  It it possible that it can been used in VB.net but not C#. (I would find that very hard to believe since they are of the same assembly regardless of language. )   Did you actually try using a sqlcommand in your code in ASP.Net 4.0?
    Edward R. Joell MCSD MCDBA
    Thursday, September 1, 2011 5:14 PM