locked
SSRS Custom Data Processing Extension Error in c# RRS feed

  • Question

  • User-1068268269 posted

    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.(Details-Object reference not set to an instance of an object.)

    Here is my code

    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;
    
    
    namespace DataBaseDPE
    {  
        public class DBConnection:Microsoft.ReportingServices.DataProcessing.IDbConnectionExtension
        {
            private string mconnstring;
            private string localname = "Database Connection";
            private ConnectionState mState = ConnectionState.Open;
    
    
            public DBConnection()
            {
                Debug.WriteLine("DataSetConnection: Default Constructor");
            }
            public DBConnection(string Dconnection)
            {
                Debug.WriteLine("DataSetConnection Constructor overloaded with Connection String ");
                mconnstring = Dconnection;
    
            }
    
            public Microsoft.ReportingServices.DataProcessing.IDbTransaction BeginTransaction()
            {
                return (null);
            }
    
    
            public string ConnectionString
            {
                get
                {
                    return mconnstring;
    
                }
                set
                {
                    mconnstring = value;
                }
            }
    
            public int ConnectionTimeout
            {
                get
                {
                    return 0;
                }
            }
    
            public ConnectionState State
            {
                get
                {
                    return mState;
                }
            }
    
            public void Open()
            {
                mState = ConnectionState.Open;
                return;
            }
    
    
            public void Close()
            {
                mState = ConnectionState.Closed;
                return;
            }
    
    
            public Microsoft.ReportingServices.DataProcessing.IDbCommand CreateCommand()
            {
                return new DBCommand(this);
            }
    
    
            public string LocalizedName
            {
                get 
                { 
                    return localname; 
                }
                set 
                { 
                    localname = value; 
                }
            }
    
            public void SetConfiguration(string configuration)
            {
                try
                {
                    SqlConnection sqlconn = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks2000;Integrated Security=True;");
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message, null);
                }
            }
            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;
    
    namespace DataBaseDPE
    {
        public class DBCommand : Microsoft.ReportingServices.DataProcessing.IDbCommand
        {
            DBConnection mconnection = null;
            private string mCmdText;
            private int mCmdTimeOut = 30;
            private CommandType CmdType;
    
    
    
            public DBCommand()
            {
    
            }
            public DBCommand(string CmdText)
            {
                mCmdText = CmdText;
            }
            public DBCommand(DBConnection aConnection)
            {
                mconnection = aConnection;
            }
    
            public void Cancel()
            {
                throw new NotImplementedException();
            }
    
            public string CommandText
            {
                get
                {
                    return mCmdText;
                }
                set
                {
                    mCmdText = value;
                }
            }
    
            public int CommandTimeout
            {
                get
                {
                    return mCmdTimeOut;
                }
                set
                {
                    mCmdTimeOut = value;
                }
            }
    
            public CommandType CommandType
            {
                get
                {
                    return CmdType;
                }
                set
                {
                    CmdType = value;
                }
            }
    
            public IDataParameter CreateParameter()
            {
                return (null);
            }
    
            public class MySqlDataReader:Microsoft.ReportingServices.DataProcessing.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=AdventureWorks2000;Integrated Security=SSPI");
                SqlCommand readercmd = new SqlCommand(query);
    
    
                    readerconn.Open();
                    readercmd = readerconn.CreateCommand();
                    readercmd.CommandText = query;
                    readercmd.CommandType = System.Data.CommandType.StoredProcedure;
    
                    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()
            {
    
            }
    
        }
    }

    Please help me, Thanks in advance

    Tuesday, May 5, 2015 8:55 AM

Answers

  • User-1068268269 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2015 12:45 AM

All replies

  • User2053451246 posted

    With all of that code telling us which line throws the error would be useful.  Are we supposed to guess?

    Tuesday, May 5, 2015 12:16 PM
  • User-1910946339 posted

    verify that you can connect to the data source

    Have you done that?  How did you do it?

    and that your query syntax is correct

    Have you done that?  How did you do it?

    Tuesday, May 5, 2015 7:17 PM
  • User-1068268269 posted

    i can connect to the datasource and the query also correct

    Friday, May 8, 2015 12:46 AM
  • User-1068268269 posted

    With all of that code telling us which line throws the error would be useful.  Are we supposed to guess?

    i dont know where the error is coming. The project is a class library(dll)

    Friday, May 8, 2015 12:47 AM
  • User-1910946339 posted

    How do you know?

    Friday, May 8, 2015 12:47 AM
  • User-1910946339 posted

    Also, debug your extension before you try to use it in the designer.  That is, create a test program which exercises your extension so that you can easily debug any problems.

    Friday, May 8, 2015 1:03 AM
  • User-1068268269 posted

    In the report designer i am able to see the data coming from the query, but when we try to create a dataset using that query i am getting the above error what i have mentioned previously

    Friday, May 8, 2015 1:20 AM
  • User-1068268269 posted

    i am new to this programming, i dont know how to debug and how to create a test program Frown

    Friday, May 8, 2015 1:42 AM
  • User-1910946339 posted

    i am able to see the data coming from the query
    But is it in the format that is expected?  For example, does the query return multiple datasets?

    I strongly suspect that this is nothing to do with C#.  Create a question and post your actual query in a forum like

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlreportingservices

    Friday, May 8, 2015 1:46 AM
  • User-1068268269 posted

    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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 12, 2015 12:45 AM