Probably I use direct queries to retrieve records from a table and also Stored procedure some times. I mention the command type as CommandType.StoredProcedure
while 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 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));
}