locked
Change Crystal Report Connection String issue

    Question

  • Hi All,

     We need to copy a Database from one server to another server (sometime between different network as well), during this time Crystal reports data will be there in table in Binary format. While copying the database, we need to re-point the Crystal Reports Connection string to Destination server, So that reports will fetch the data from new server, instead of old server.

    below, is the C# code to change the connection string of the Crystal Reports, but this code is not working always, only in few reports its changes the connection string but in other cases it's not. Can someone help me in this?

    Many thanks in advance.

    private Byte[] ChangeCrystalReportConnectionString(Byte[] theReportData, ConnectionString newConnectionString)

            {

               

                // Get the executing assembly

                Assembly theAssembly = System.Reflection.Assembly.GetExecutingAssembly();

     

                // Create a random filename

                Random newRand = new Random();

                string fileName = "Temp" + newRand.Next().ToString() + ".rpt";

     

                // Get the path of the application

                string path = theAssembly.Location;

     

                // Strip out the file name

                path = path.Substring(0, path.LastIndexOf("\\")) + "\\" + Program.User.UserName;

                //path = path + Program.User.UserName;

     

                if (Directory.Exists(path))

                {

                    //Delete all existing files

                    string[] files = Directory.GetFiles(path);

                    foreach (string file in files)

                        File.Delete(file);

                }

                else

                {

                    // Try to create the directory.

                    DirectoryInfo di = Directory.CreateDirectory(path);

                }

     

               

                // Create a new Crystal Report object

                CrystalDecisions.CrystalReports.Engine.ReportDocument theDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

     

                // Create a new Binary writer to write the file

                BinaryWriter theBinaryWriter = new BinaryWriter(File.Open(path + "\\" + fileName, FileMode.Create));

     

                // Write the file

                theBinaryWriter.Write(theReportData);

     

                // Close the writer

                theBinaryWriter.Close();

     

                // Load the report document

                theDocument.Load(path + "\\" + fileName);

     

                theDocument.SetDatabaseLogon(newConnectionString.Server, newConnectionString.Database, "UserID", "password");

     

     

                // Get the connections for this report

                DataSourceConnections theConnections = theDocument.DataSourceConnections;

     

                // Loop through the connections

                for (int i = 0; i < theConnections.Count; i++)

                {

                    // Change the connection string of the report

                    theConnections[i].SetConnection(newConnectionString.Server, newConnectionString.Database, "UserID", "password");

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Initial Catalog", newConnectionString.Database));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Data Source", newConnectionString.Database));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Local Identifier", "2057"));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Provider", "SQLOLEDB"));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("OLE DB Services", "-5"));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Tag with column collation when possible", "0"));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Use DSN Default Properties", "False"));

                    theConnections[i].Attributes.Collection.Add(new NameValuePair2("Use Encryption for Data", "0"));

     

                }

     

                foreach (CrystalDecisions.CrystalReports.Engine.Section sec in theDocument.ReportDefinition.Sections)

                {

                    foreach (CrystalDecisions.CrystalReports.Engine.ReportObject RptObj in sec.ReportObjects)

                    {

                        if (RptObj.Kind == ReportObjectKind.SubreportObject)

                        {

                            CrystalDecisions.CrystalReports.Engine.SubreportObject SubRptObj = (CrystalDecisions.CrystalReports.Engine.SubreportObject)RptObj;

                            CrystalDecisions.CrystalReports.Engine.ReportDocument SubRpt = SubRptObj.OpenSubreport(SubRptObj.SubreportName);

                            foreach (CrystalDecisions.CrystalReports.Engine.Table tbl in SubRpt.Database.Tables)

                            {

                                TableLogOnInfo logOnInfo = new TableLogOnInfo();

                                logOnInfo = tbl.LogOnInfo;

     

                                logOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"));

                                logOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2("QE_SQLDB", "True"));

                                logOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2("Initial Catalog", newConnectionString.Database));

                                logOnInfo.ConnectionInfo.Attributes.Collection.Add(new NameValuePair2("Data Source", newConnectionString.Database));

     

                                logOnInfo.ConnectionInfo.ServerName = newConnectionString.Server;

                                logOnInfo.ConnectionInfo.DatabaseName = newConnectionString.Database;

                                logOnInfo.ConnectionInfo.UserID = "UserID";

                                logOnInfo.ConnectionInfo.Password = "Password";

                                logOnInfo.TableName = tbl.Name;

                                tbl.ApplyLogOnInfo(logOnInfo);

                            }

                        }

                    }

                }

     

     

                // Save and close the report

                theDocument.SaveAs(path + "\\" + fileName);

                theDocument.Close();

                theDocument.Dispose();

     

                // Read the updated report to a Byte array

                FileStream fs = File.OpenRead(path + "\\" + fileName);

                byte[] theUpdatedBytes = new byte[fs.Length];

                fs.Read(theUpdatedBytes, 0, theUpdatedBytes.Length);

     

                // Clean up

                fs.Close();

                fs.Dispose();

     

                // Return the bytes

                return theUpdatedBytes;

            }


    Regards, Mohan Gowda
    Tuesday, January 12, 2010 10:34 AM