c# snippet to load table from DB - not working with views

Question

I have the snippet below that worked ok to fill a table using a SQL Stored Procedure. We changed the SQL from Stored Prcedure to a View and the snippet broke. any hint on how to use it with a view instead a SP?

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["XXXX"].ConnectionString))
            using (var cmd = new SqlCommand("MYVIEW", conn))
            using (var adapter = new SqlDataAdapter(cmd))
            {
                //tried CommandType.Text too without success.
                cmd.CommandType = CommandType.StoredProcedure;
                adapter.Fill(table);
            }

the error is:

System.Data.SqlClient.SqlException (0x80131904): The request for procedure 'MYVIEW' failed because 'MYVIEW' is a view object. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at moodleWCF_test.index.btTurmaIncluir_Click(Object sender, EventArgs e)

thanks!

Ajay Kumar Ajay Kumar · Accepted Answer · 2017-08-08T15:08:54

Views can't be called as Stored Procedures. To use view instead replace below lines:

using (var cmd = new SqlCommand("SELECT * FROM MYVIEW", conn))

cmd.CommandType = CommandType.Text;

c# snippet to load table from DB - not working with views

1 Answers