What is the purpose of using CommandType.Tabledirect
CommandType contains names that specifies how a command string is interpreted.
CommandType.Text
for an SQL text command. (Default.)CommandType.StoredProcedure
for the name of a stored procedure.CommandType.TableDirect
for the name of a table.
All rows and columns of the named table will be returned when you call one of the Execute methods.
NOTE: TableDirect is only supported by the .NET Framework Data Provider for OLE DB. Multiple table access is not supported when CommandType is set to 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)); }