C# 使用 CommandType.Tabledirect 的目的是什么

What is the purpose of using CommandType.Tabledirect

提问by Venil

Probably I use direct queries to retrieve records from a table and also Stored procedure some times. I mention the command type as CommandType.StoredProcedurewhile using SP. Also am seeing another option named CommandType.Tabledirect, searched some where else, but not clear about it. Could any one help me to get idea about it? Please give me some sample codes.

可能我有时使用直接查询从表和存储过程中检索记录。我CommandType.StoredProcedure在使用 SP 时提到了命令类型。还看到另一个名为 的选项CommandType.Tabledirect，在其他地方搜索过，但不清楚。任何人都可以帮助我了解它吗？请给我一些示例代码。

采纳答案by Vishal Suthar

CommandTypecontains names that specifies how a command string is interpreted.

CommandType包含指定如何解释命令字符串的名称。

1. CommandType.Textfor an SQL text command. (Default.)
2. CommandType.StoredProcedurefor the name of a stored procedure.
3. CommandType.TableDirectfor the name of a table.

1. CommandType.Text用于 SQL 文本命令。（默认。）
2. CommandType.StoredProcedure用于存储过程的名称。
3. CommandType.TableDirect为表名。

All rows and columns of the named table will be returned when you call one of the Execute methods.

当您调用 Execute 方法之一时，将返回命名表的所有行和列。

NOTE: TableDirectis only supported by the .NET Framework Data Provider for OLE DB. Multiple table access is not supported when CommandType is set to TableDirect.

注意：TableDirect仅受OLE DB的 .NET Framework 数据提供程序支持。当 CommandType 设置为TableDirect时，不支持多表访问。

Sample example how it is been used:

示例示例如何使用它：

OleDbConnection myOleDbConnection =new OleDbConnection("provider=sqloledb;server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI");
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();

myOleDbCommand.CommandType = CommandType.TableDirect;

myOleDbCommand.CommandText = "Employee";

myOleDbConnection.Open();

OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();

for (int count = 1; count <= 2; count++)
{
  myOleDbDataReader.Read();
  Console.WriteLine("myOleDbDataReader[\" ID\"] = " +
    myOleDbDataReader["ID"]);
  Console.WriteLine("myOleDbDataReader[\" FirstName\"] = " +
    myOleDbDataReader["FirstName"]);
  Console.WriteLine("myOleDbDataReader[\" LastName\"] = " +
    myOleDbDataReader["LastName"]);
}
myOleDbDataReader.Close();
myOleDbConnection.Close();

Insert/Update

插入/更新

        try
        {
            using (SqlCeCommand command = conn.CreateCommand())
            {
                command.CommandText = "Holdings";
                command.CommandType = CommandType.TableDirect;
                using (SqlCeResultSet rs = command.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
                {
                    SqlCeUpdatableRecord record = rs.CreateRecord();
                    foreach (var r in _commitBatch)
                    {
                        int index=0;
                        record.SetValue(index++, r.TryGetValueOrDefault("IdentifierFromImportSource",string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("SecurityID", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("SecurityName", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("SecurityType", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("AllocationAmount", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("Position", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("AnnualFeePercent", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("MarginAmount", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("Price", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("MorningstarSecId", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("MorningstarSecType", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("UserID", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("MorningstarPrice", string.Empty));
                        record.SetValue(index++, string.Empty);
                        record.SetValue(index++, r.TryGetValueOrDefault("AnnualFeeFrequency", string.Empty));
                        record.SetValue(index++, r.TryGetValueOrDefault("TrackingMethod", "1"));
                        rs.Insert(record);
                    }
                }

            }

        }
        catch (Exception e)
        {
            NotifyError(this, new ImportErrorEventArgs(e.Message + e.StackTrace, ErrorLevel.Application));
        }