Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I am using Custom Data Processing Extension to call a stored procedure. Iam getting following error when creating a dataset in report designer using the extension. I wrote the code in c#.

could not update a list of fields for the query. verify that you can connect to the data source and that your query syntax is correct.(Detailed Error-Object reference not set to an instance of an object.)

Here is my code

C#
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ReportingServices.DataProcessing;
using System.Diagnostics;
using System.Text.RegularExpressions;
using System.Configuration;

namespace DataBaseDPE
{  
    public class DBConnection:Microsoft.ReportingServices.DataProcessing.IDbConnectionExtension
    {
        //private string mconnstring;
        private string localname = "Database Connection";
        //private ConnectionState mState = ConnectionState.Open;
        private string m_sqlConnection1;
        public System.Data.SqlClient.SqlConnection sqlConnection1;
        
        public DBConnection()
        {
            Debug.WriteLine("DataSetConnection: Default Constructor");
        }
        public DBConnection(string Dconnection)
        {
            Debug.WriteLine("DataSetConnection Constructor overloaded with Connection String ");
            m_sqlConnection1 = Dconnection;

        }

        public Microsoft.ReportingServices.DataProcessing.IDbTransaction BeginTransaction()
        {
            throw new NotImplementedException();
        }

        
        public string ConnectionString
        {
            get
            {
                return m_sqlConnection1;
                
            }
            set
            {
                m_sqlConnection1 = "Data Source=localhost;Initial Catalog=test;Integrated Security=SSPI;";
            }
        }

        public int ConnectionTimeout
        {
            get
            {
                return 0;
            }
        }

        public Microsoft.ReportingServices.DataProcessing.IDbCommand CreateCommand()
        {
            return (new DBCommand(this));
        }

        public void Open()
        {
            Debug.WriteLine("IDBConnection.Open");

            sqlConnection1 = new SqlConnection(m_sqlConnection1);
            sqlConnection1.Open();
        }


        public void Close()
        {
            Debug.WriteLine("IDBConnection.Close()");

            sqlConnection1.Close();
        }
             
               
        public string LocalizedName
        {
            get 
            { 
                return localname; 
            }
            set 
            { 
                localname = value; 
            }
        }

        public void SetConfiguration(string configuration)
        {
            
                SqlConnection sqlconn = new SqlConnection("Data Source=localhost;Initial Catalog=test;Integrated Security=SSPI;");
            
            
        }
        public void Dispose()
        {

        }
        public string Impersonate
        { get; set; }

        public bool IntegratedSecurity
        { get; set; }

        public string Password
        { get; set; }

        public string UserName
        { get; set; }
    }

}

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.ReportingServices.DataProcessing;
using System.Data.SqlClient;
using System.Diagnostics;

namespace DataBaseDPE
{
    public class DBCommand : Microsoft.ReportingServices.DataProcessing.IDbCommand
    {
        readonly DBConnection mconnection;
        private string mCmdText = "select id,name,location from test";
        private int mCmdTimeOut = 30;

        public DBCommand(DBConnection aConnection)
        {
            Debug.WriteLine("Command : Entering  constructor DataSetCommand(DataSetConnection aConnection)");
            mconnection = aConnection;
        }
        
               
        public DBCommand(string CmdText)
        {
            mCmdText = CmdText;
        }
        
        
        public void Cancel()
        {
            Debug.WriteLine("IDBCommand.Cancel");
            throw (new NotSupportedException("IDBCommand.Cancel currently not supported"));
        }

        public string CommandText
        {
            get
            {
                return mCmdText;
            }
            set
            {
                mCmdText = value;
            }
        }

        public int CommandTimeout
        {
            get
            {
                return mCmdTimeOut;
            }
            set
            {
                mCmdTimeOut = value;
            }
        }

        public CommandType CommandType
        {
            get
            {
                Debug.WriteLine("IDBCommand.CommandType: Get : ");
                return (CommandType.StoredProcedure);
            }
            set
            {
                Debug.WriteLine("IDBCommand.CommandType: Set");
                if (value != CommandType.StoredProcedure)
                {
                    throw (new NotSupportedException("Only CommandType.StoredProcedure is supported."));
                }
            }
        }

        public IDataParameter CreateParameter()
        {
            return (null);
        }

        public class MySqlDataReader:IDataReader
        {
            private System.Data.IDataReader sourceDataReader;
            private System.Data.DataTable dt;
            private System.Data.DataSet ds;
            private int fieldCount = 0;
            private string fieldName;
            private int fieldOrdinal;
            private Type fieldType;
            private object fieldValue;
            private int currentRow = 0;

            public MySqlDataReader(System.Data.IDataReader datareader)
            {
                this.sourceDataReader = datareader;
            }

            public MySqlDataReader(System.Data.DataTable dt)
            {
                // TODO: Complete member initialization
                
                this.dt = dt;
                
            }

            public MySqlDataReader(System.Data.DataSet ds)
            {
                // TODO: Complete member initialization
                this.ds = ds;
            }
            public int FieldCount
            {
                get
                {
                    fieldCount = ds.Tables[0].Columns.Count;
                    return fieldCount;
                }
                
            }

            public Type GetFieldType(int i)
            {
                fieldType =
               ds.Tables[0].Columns[i].DataType;
                return fieldType;
            }

            public string GetName(int i)
            {
                fieldName = ds.Tables[0].Columns[i].ColumnName;
                return fieldName;
            }

            public int GetOrdinal(string name)
            {
                fieldOrdinal =
               ds.Tables[0].Columns[name].Ordinal;
                return fieldOrdinal;
            }

            public object GetValue(int i)
            {
                fieldValue =
               ds.Tables[0].Rows[this.currentRow][i];
                return fieldValue;
            }

            public bool Read()
            {
                currentRow++;
                if (currentRow >= ds.Tables[0].Rows.Count)
                {
                    return (false);
                }
                else
                {
                    return (true);
                }
            }

            public void Dispose()
            {
                
            }
        
        }


        public IDataReader ExecuteReader(CommandBehavior behavior)
        {
            string query = "SampleSP";
            SqlConnection readerconn = new SqlConnection("Data Source=localhost;Initial Catalog=test;Integrated Security=SSPI");
            SqlCommand readercmd = new SqlCommand(query);

            
                readerconn.Open();
                readercmd = readerconn.CreateCommand();
                readercmd.CommandText = query;
                readercmd.CommandType = System.Data.CommandType.StoredProcedure;
                //SqlDataReader TestReader;
                //TestReader = readercmd.ExecuteReader();
                //TestReader.Close();
                readerconn.Close();
               
                SqlDataAdapter adapter = new SqlDataAdapter(query,readerconn);
                
                readerconn.Open();
                adapter.SelectCommand = readercmd;
                
                System.Data.DataTable dt = new System.Data.DataTable();
                adapter.Fill(dt);
                System.Data.DataSet ds = new System.Data.DataSet();
                adapter.Fill(ds);                
                return new MySqlDataReader(ds);

               
        }

        public IDataParameterCollection Parameters
        {
            get { return (null); }
        }

        public IDbTransaction Transaction
        {
            get
            {
                return (null);
            }
            set
            {
                throw new NotImplementedException();
            }
        }

        public void Dispose()
        {
            
        }
       
    }
}
Posted
Updated 16-Feb-16 19:16pm
v2

1 solution

I got the solution, in the above code i did not implemented the IDataParameter and IDataParameterCollection interfaces. After implementing them my code is working and now i am able to get the dataset for the SSRS report.
 
Share this answer
 
Comments
SRS(The Coder) 17-Feb-16 1:18am    
Can you please share the latest code sample, so that it will be helpful.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900