Changing the Crystal Report Database credential and Logon values at runtime

Introduction

This article  is to  help the beginners in one of there most frequntly asked question .How to change the  DB logon values of a crystal report at runtime in a winform application

Background

I had been working in a winform application where there are more than 100 crystal reports .Which is displayed in the winform using crystalreportviewer.My Db was on production machine .but at the time of the Deployement I was forced to change the DB servername.So I developed this code block

Using the code

Step 1  :   I had placed all the Logon credential in the program.cs as static  variables

static class Program
    {   public static String ConnStr = "";
        public static String OurReportSource = "";
        public static String OurLogSource = "";
        public static String OurImagelocation = "";
        public static String database = "";
        public static String  Server = "";
        public static String dbUsername = "";
        public static String dbPassword = ""; 
 public static int USERPK;
        public static String UserType;
        public static int LOCTNPK;
        public static String UserName;
        public static String LOCATIONCODE;
        public static String EmpName;
        public static DateTime Datetoday=DateTime.Now ;
 public static Transactions.DatabasePicker databasepcker = null;
        public static int usernampk;
[STAThread] 
/// <summary>
        /// The main entry point for the application.
        /// </summary>
        static void Main()
        {
            databasepcker = new Transactions.DatabasePicker();
 Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            databasepcker.SetConnctionString();
           Application.Run(new MainForm());
        
        }  
}

Step 2  :      Then created a Textfile named DBKey which is stored in the C drive of the client machine and created a class to read the values from that db key

class DatabasePicker
    {

        private const string FILE_NAME = "C:\\DBkey.txt";

        public void SetConnctionString()
        {

            if (!File.Exists(FILE_NAME))
            {

                DialogResult dialogResult = MessageBox.Show(
                    "Database configuration file not found  Do you want to Reset it ? ", 
                    "Database Key not Found", MessageBoxButtons.YesNo);
                if (dialogResult == DialogResult.Yes)
                {
                    OpenFileDialog openFileDialog1 = new OpenFileDialog();

                    openFileDialog1.InitialDirectory = "c:\\";
                    openFileDialog1.Filter = "Text|*.txt|All|*.*";
                    openFileDialog1.RestoreDirectory = true;

                    if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        try
                        {
                            String Sourcefile = openFileDialog1.FileName;
                            File.Copy(Sourcefile, "c:\\DBkey.txt");
                            MessageBox.Show("Data Key Recieved", "Data Key");
                            SetConnctionString();
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message, "Data Key");
                        }

                    }
                }
                else if (dialogResult == DialogResult.No)
                {
                    Application.Exit();
                }

                              
            }

            else
            {


                string line;

                // Read the file and display it line by line.
                System.IO.StreamReader file =
                   new System.IO.StreamReader("c:\\DBkey.txt");

                string[] lines = File.ReadAllLines("c:\\DBkey.txt"); //File is in System.IO
               
                string firstLine = lines[1]; //constring
                string secondline = lines[2]; //reportlocation
                string ThirdLine = lines[3]; //log
                string FourthLine = lines[4]; //images
                string fifthline = lines[5]; //servername
                string sixthline = lines[6]; //databasename
                string seventhline = lines[7]; //userid
                string eightline = lines[8];//password         
               

                           Program.ConnStr = firstLine;
                Program.OurReportSource = secondline;
                Program.OurLogSource = ThirdLine;
                Program.OurImagelocation = FourthLine;
                Program.Server = fifthline;
                Program.database = sixthline;
                Program.dbUsername = seventhline;
                Program.dbPassword = eightline;
            }

        }

The code above is  clearly explained in my previous Tip  fr reading the connection string from Txt file http://www.codeproject.com/Tips/796507/ConnectionString-for-Winform-and-Reportsource-from

Step 3 :  The Imporrtant step    >> Created a class for setting the Logon values for the Crystal report with a function  getreport()  which returns a reportdocument of a  crystal report in a given report location 

public static class Logonvalues
   {




       public static ReportDocument getpeport(String ReportLocation)
       {
            ConnectionInfo crconnectioninfo = new ConnectionInfo();
           ReportDocument cryrpt = new ReportDocument();
           TableLogOnInfos crtablelogoninfos = new TableLogOnInfos();
           TableLogOnInfo crtablelogoninfo = new TableLogOnInfo();

           Tables CrTables;
           cryrpt.Load(ReportLocation);
           cryrpt.DataSourceConnections.Clear();


           crconnectioninfo.ServerName = Program.Server;
           crconnectioninfo.DatabaseName = Program.database;
           crconnectioninfo.UserID = Program.dbUsername ;
           crconnectioninfo.Password = Program.dbPassword;

           CrTables = cryrpt.Database.Tables;

           foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
           {
               crtablelogoninfo = CrTable.LogOnInfo;
               crtablelogoninfo.ConnectionInfo = crconnectioninfo;
               CrTable.ApplyLogOnInfo(crtablelogoninfo);
           }

           return cryrpt;
       }

Pl,ease note that its important to clear the datasource location of the report document before it is assigned with the new logon values using 

cryrpt.DataSourceConnections.Clear();

Step 4 : Finally we will  call the logon value from the form consisting of the crystal reportviewer 

public void loadreport()
       {

               Crvt_ApplicationReport.ReportSource = Logonvalues..getpeport(Program.OurReportSource + \\LeaveApplicationForm");


                Crvt_ApplicationReport.SelectionFormula= "{LeaveApplicationMaster.LeaveAppPk}=" +selectionid ;

              Crvt_ApplicationReport.RefreshReport();

       }

Now final thing you have to do is to make sure that the crystal report redistributable pack is installed on the client system and the DbKey of your system points to the  correct credential 

Points of Interest

Further code redduction can done by putting the loadreport function also on a class and passing the crystalreportvierwer name and the required report and selction formulla as the parameter  Also insead of textfile we can use any other method for assigning the logon values like XML entity objects etc

History

Keep a running update of any changes or improvements you've made here.