Symptom
How To set/change location to a Pervasive database using Native Btrieve driver using the Report Engine or Report Application Server API's
Environment
- Crystal Reports for Visual Studio .NET
- Pervasive ( Btrieve ) database
Resolution
Use the following C# code for RAS:
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportAppServer.ClientDoc;
using CrystalDecisions.ReportAppServer.Controllers;
using CrystalDecisions.ReportAppServer.ReportDefModel;
using CrystalDecisions.ReportAppServer.CommonControls;
using CrystalDecisions.ReportAppServer.CommLayer;
using CrystalDecisions.ReportAppServer.CommonObjectModel;
using CrystalDecisions.ReportAppServer.ObjectFactory;
using CrystalDecisions.ReportAppServer.Prompting;
using CrystalDecisions.ReportAppServer.DataSetConversion;
using CrystalDecisions.ReportAppServer.DataDefModel;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Windows.Forms;
private void SetPCDatabase_Click(object sender, EventArgs e)
{
DateTime dtStart;
TimeSpan difference;
DateTime TSTotal;
TSTotal = DateTime.Now;
//these are text boxes to enter the location
string newDataFile = btrDataFile.Text;
string newSearchPath = btrSearchPath.Text;
// RAS Btrieve - Access need to use ReplaceConnection
#region RASReplaceconnection;
if (chkUseRAS.CheckState == CheckState.Checked)
{
rptClientDoc = rpt.ReportClientDocument;
//Create a new Database Table to replace the reports current table.
CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
CrystalDecisions.ReportAppServer.DataDefModel.Table subboTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
//Get the Database Tables Collection for your report
CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
boTables = rptClientDoc.DatabaseController.Database.Tables;
// Get the old connection info
oldConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);
boTable.ConnectionInfo = boConnectionInfo;
oldConnInfo = oldConnInfos[0];
// Access
#region DAO RAS Access
if ((dynamic)oldConnInfo.Attributes["Database DLL"].ToString() == "crdb_dao.dll")
{
PropertyBag logonDetails = new PropertyBag();
PropertyBag QeDetails = new PropertyBag();
//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
PropertyBag boMainPropertyBag = new PropertyBag();
//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
//In the main property bag (boMainPropertyBag)
PropertyBag boInnerPropertyBag = new PropertyBag();
//Set the attributes for the boInnerPropertyBag
boInnerPropertyBag.Add("Database Name", newDataFile);
boInnerPropertyBag.Add("Database Type", "Access/Excel (DAO)");
//Set the attributes for the boMainPropertyBag
boMainPropertyBag.Add("Database DLL", "crdb_dao.dll");
boMainPropertyBag.Add("QE_DatabaseName", newDataFile);
boMainPropertyBag.Add("QE_DatabaseType", "Access/Excel (DAO)"); // C:\xtreme.mdb
//Add the QE_LogonProperties we set in the boInnerPropertyBag Object
boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
boMainPropertyBag.Add("QE_ServerDescription", newDataFile);
boMainPropertyBag.Add("QE_SQLDB", "True");
boMainPropertyBag.Add("SSO Enabled", "False");
// check for subreports
//loop through all the report objects to find all the subreports
foreach (string crSubreportDocument1 in rptClientDoc.SubreportController.GetSubreportNames())
{
SubreportClientDocument SubRCD = rptClientDoc.SubreportController.GetSubreport(crSubreportDocument1);
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newSubConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldSubConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldSubConnInfos;
PropertyBag SublogonDetails = new PropertyBag();
PropertyBag SubQeDetails = new PropertyBag();
oldSubConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);
btnReportObjects.Text += "\nSubreport: " + crSubreportDocument1.ToString() + "\n";
for (int I = 0; I < oldSubConnInfos.Count; I++)
{
oldSubConnInfo = oldSubConnInfos[I];
newSubConnInfo.Attributes = boMainPropertyBag;
newSubConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindDBFile;
// this works also
for (int S = 0; S < boTables.Count; S++)
{
oldSubConnInfo = oldSubConnInfos[I];
newSubConnInfo.Attributes = boMainPropertyBag;
newSubConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindDBFile;
try
{
rptClientDoc.DatabaseController.ReplaceConnection(oldSubConnInfo, newSubConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
//rptClientDoc.DatabaseController.SetTableLocationEx(oldSubConnInfo, newSubConnInfo);
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
//return;
}
}
}
//rptClientDoc.VerifyDatabase();
}
}
#endregion DAO RAS Access
// Access
// Btrieve
if ((dynamic)oldConnInfo.Attributes["Database DLL"].ToString() == "crdb_p2bbtrv.dll")
{
PropertyBag logonDetails = new PropertyBag();
PropertyBag QeDetails = new PropertyBag();
//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
PropertyBag boMainPropertyBag = new PropertyBag();
//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
//In the main property bag (boMainPropertyBag)
PropertyBag boInnerPropertyBag = new PropertyBag();
//Set the attributes for the boInnerPropertyBag
boInnerPropertyBag.Add("Data File", newDataFile);
boInnerPropertyBag.Add("Data File Search Path", newSearchPath);
//boInnerPropertyBag.Add("Table Name",
//Set the attributes for the boMainPropertyBag
boMainPropertyBag.Add("Database DLL", "crdb_p2bbtrv.dll");
boMainPropertyBag.Add("QE_DatabaseName", newSearchPath);
boMainPropertyBag.Add("QE_DatabaseType", "Btrieve");
//Add the QE_LogonProperties we set in the boInnerPropertyBag Object
boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
boMainPropertyBag.Add("QE_ServerDescription", newDataFile);
boMainPropertyBag.Add("QE_SQLDB", "False");
boMainPropertyBag.Add("SSO Enabled", "False");
// Set Location
foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table oldTable in rptClientDoc.DatabaseController.Database.Tables)
{
for (int I = 0; I < oldConnInfos.Count; I++)
{
logonDetails["Data File"] = newDataFile;
logonDetails["Data File Search Path"] = newSearchPath;
QeDetails.Add("Database DLL", "crdb_p2bbtrv.dll");
QeDetails.Add("QE_DatabaseType", "Btrieve");
QeDetails.Add("QE_LogonProperties", logonDetails);
QeDetails.Add("QE_LogonProperties", boInnerPropertyBag);
QeDetails.Add("QE_DatabaseName", newSearchPath);
QeDetails.Add("QE_ServerDescription", newDataFile);
QeDetails.Add("QE_SQLDB", "False");
QeDetails.Add("SSO Enabled", "False");
// This sets the new file name, in this case the report only used 2 tables so more coding is required
if (oldTable.Name == "MyOld Table Name")
{
QeDetails.Add("File Name", "NEWFileName");
}
else
QeDetails.Add("File Name", "OtherNEWFILEName");
newConnInfo.Attributes = QeDetails;
newConnInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
newConnInfo.UserName = "";
newConnInfo.Password = "";
CrystalDecisions.ReportAppServer.DataDefModel.Table newTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
newTable.ConnectionInfo = newConnInfo;
// this set the table name
newTable.Name = oldTable.Name;
dtStart = DateTime.Now;
rptClientDoc.DatabaseController.SetTableLocation(oldTable, newTable);
difference = DateTime.Now.Subtract(dtStart);
btnReportObjects.Text += oldTable.Name.ToString() + " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
}
# region Subreport
// check for subreports
//loop through all the report objects to find all the subreports
foreach (string crSubreportDocument1 in rptClientDoc.SubreportController.GetSubreportNames())
{
SubreportClientDocument SubRCD = rptClientDoc.SubreportController.GetSubreport(crSubreportDocument1);
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newSubConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldSubConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldSubConnInfos;
PropertyBag SublogonDetails = new PropertyBag();
PropertyBag SubQeDetails = new PropertyBag();
oldSubConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);
btnReportObjects.Text += "\nSubreport: " + crSubreportDocument1.ToString() + "\n";
for (int I = 0; I < oldSubConnInfos.Count; I++)
{
oldSubConnInfo = oldSubConnInfos[I];
newSubConnInfo.Attributes = boMainPropertyBag;
newSubConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindDBFile;
// Set Location
foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table oldSubTable in SubRCD.DatabaseController.Database.Tables)
{
for (I = 0; I < oldConnInfos.Count; I++)
{
SublogonDetails["Data File"] = newDataFile;
SublogonDetails["Data File Search Path"] = newSearchPath;
SubQeDetails.Add("Database DLL", "crdb_p2bbtrv.dll");
SubQeDetails.Add("QE_DatabaseType", "Btrieve");
SubQeDetails.Add("QE_LogonProperties", logonDetails);
SubQeDetails.Add("QE_LogonProperties", boInnerPropertyBag);
SubQeDetails.Add("QE_DatabaseName", newSearchPath);
SubQeDetails.Add("QE_ServerDescription", newDataFile);
SubQeDetails.Add("QE_SQLDB", "False");
SubQeDetails.Add("SSO Enabled", "False");
// This sets the new file name, in this case the report only used 2 tables so more coding is required
if (oldTable.Name == "MyOld Table Name")
{
QeDetails.Add("File Name", "NEWFileName");
}
else
QeDetails.Add("File Name", "OtherNEWFILEName");
newSubConnInfo.Attributes = SubQeDetails;
newSubConnInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
newSubConnInfo.UserName = "";
newSubConnInfo.Password = "";
CrystalDecisions.ReportAppServer.DataDefModel.Table newSubTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
newSubTable.ConnectionInfo = newConnInfo;
newSubTable.Name = oldSubTable.Name;
dtStart = DateTime.Now;
SubRCD.DatabaseController.SetTableLocation(oldSubTable, newSubTable);
difference = DateTime.Now.Subtract(dtStart);
btnReportObjects.Text += newSubTable.Name.ToString() + " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
}
}
# endregion Subreport
}
/// Btrieve
}
IsRpt = false;
}
#endregion RASReplaceConnection;
else
#region Engine
{
// Access
rptClientDoc = rpt.ReportClientDocument;
//Create a new Database Table to replace the reports current table.
CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
CrystalDecisions.ReportAppServer.DataDefModel.Table subboTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
//Get the Database Tables Collection for your report
CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
boTables = rptClientDoc.DatabaseController.Database.Tables;
// Get the old connection info
oldConnInfos = rptClientDoc.DatabaseController.GetConnectionInfos(null);
boTable.ConnectionInfo = boConnectionInfo;
oldConnInfo = oldConnInfos[0];
# region DAO Access
if (oldConnInfo.Attributes["Database DLL"].ToString() == "crdb_dao.dll")
{
// Engine
CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;
CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;
CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;
CrystalDecisions.CrystalReports.Engine.Database crDatabase;
CrystalDecisions.CrystalReports.Engine.Tables crTables;
CrystalDecisions.Shared.TableLogOnInfo tLogonInfo;
btnSQLStatement.Text = "";
try
{
foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in rpt.Database.Tables)
{
tLogonInfo = rptTable.LogOnInfo;
tLogonInfo.ConnectionInfo.DatabaseName = newDataFile; // D:\Atest\199019\ot_tmp88.mdb
tLogonInfo.ConnectionInfo.UserID = "";
tLogonInfo.ConnectionInfo.Password = "";
tLogonInfo.TableName = rptTable.Name;
dtStart = DateTime.Now;
try
{
rptTable.ApplyLogOnInfo(tLogonInfo);
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
//return;
}
difference = DateTime.Now.Subtract(dtStart);
//rptTable.Location = rptTable.Name;
btnSQLStatement.Text += /*rptTable.Name.ToString() +*/ " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
}
// check for subreports
//set the crSections object to the current report's sections
CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections;
//loop through all the sections to find all the report objects
foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
{
crReportObjects = crSection.ReportObjects;
//loop through all the report objects to find all the subreports
foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
{
if (crReportObject.Kind == CrystalDecisions.Shared.ReportObjectKind.SubreportObject)
{
//you will need to typecast the reportobject to a subreport
//object once you find it
crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;
//open the subreport object
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);
CrystalDecisions.CrystalReports.Engine.Database crSubDatabase;
CrystalDecisions.CrystalReports.Engine.Tables crSubTables;
//set the database and tables objects to work with the subreport
crSubDatabase = crSubreportDocument.Database;
crSubTables = crSubDatabase.Tables;
//loop through all the tables in the subreport and
//set up the connection info and apply it to the tables
try
{
//foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in crTables)
foreach (CrystalDecisions.CrystalReports.Engine.Table subrptTable in crSubreportDocument.Database.Tables)
{
tLogonInfo = subrptTable.LogOnInfo;
tLogonInfo.ConnectionInfo.DatabaseName = newDataFile;
tLogonInfo.ConnectionInfo.ServerName = newDataFile;
tLogonInfo.TableName = subrptTable.Name;
tLogonInfo.ConnectionInfo.UserID = "";
tLogonInfo.ConnectionInfo.Password = "";
dtStart = DateTime.Now;
try
{
subrptTable.ApplyLogOnInfo(tLogonInfo);
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
//return;
}
difference = DateTime.Now.Subtract(dtStart);
btnSQLStatement.Text += "Subreport Table: " + subrptTable.Name.ToString() + " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
}
catch (Exception ex)
{
MessageBox.Show("SubReport ERROR: " + ex.Message);
//return;
}
}
}
}
}
# endregion DAO Access
// Access
else
{
// Btrieve
#region Btrieve
// Engine
if (oldConnInfo.Attributes["Database DLL"].ToString() == "crdb_p2bbtrv.dll")
{
CrystalDecisions.CrystalReports.Engine.ReportObjects crReportObjects;
CrystalDecisions.CrystalReports.Engine.SubreportObject crSubreportObject;
CrystalDecisions.CrystalReports.Engine.ReportDocument crSubreportDocument;
CrystalDecisions.CrystalReports.Engine.Database crDatabase;
CrystalDecisions.CrystalReports.Engine.Tables crTables;
CrystalDecisions.Shared.TableLogOnInfo tLogonInfo;
btnSQLStatement.Text = "";
try
{
foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in rpt.Database.Tables)
{
tLogonInfo = rptTable.LogOnInfo;
tLogonInfo.ConnectionInfo.DatabaseName = newSearchPath;
tLogonInfo.ConnectionInfo.ServerName = newDataFile;
tLogonInfo.ConnectionInfo.UserID = "";
tLogonInfo.ConnectionInfo.Password = "";
tLogonInfo.TableName = rptTable.Name;
dtStart = DateTime.Now;
try
{
rptTable.ApplyLogOnInfo(tLogonInfo);
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
//return;
}
difference = DateTime.Now.Subtract(dtStart);
//rptTable.Location = rptTable.Name;
btnSQLStatement.Text += /*rptTable.Name.ToString() +*/ " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
//return;
}
// check for subreports
//set the crSections object to the current report's sections
CrystalDecisions.CrystalReports.Engine.Sections crSections = rpt.ReportDefinition.Sections;
crSections = rpt.ReportDefinition.Sections;
//loop through all the sections to find all the report objects
foreach (CrystalDecisions.CrystalReports.Engine.Section crSection in crSections)
{
crReportObjects = crSection.ReportObjects;
//loop through all the report objects to find all the subreports
foreach (CrystalDecisions.CrystalReports.Engine.ReportObject crReportObject in crReportObjects)
{
if (crReportObject.Kind == CrystalDecisions.Shared.ReportObjectKind.SubreportObject)
{
//you will need to typecast the reportobject to a subreport
//object once you find it
crSubreportObject = (CrystalDecisions.CrystalReports.Engine.SubreportObject)crReportObject;
//open the subreport object
crSubreportDocument = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName);
CrystalDecisions.CrystalReports.Engine.Database crSubDatabase;
CrystalDecisions.CrystalReports.Engine.Tables crSubTables;
//set the database and tables objects to work with the subreport
crSubDatabase = crSubreportDocument.Database;
crSubTables = crSubDatabase.Tables;
//loop through all the tables in the subreport and
//set up the connection info and apply it to the tables
try
{
//foreach (CrystalDecisions.CrystalReports.Engine.Table rptTable in crTables)
foreach (CrystalDecisions.CrystalReports.Engine.Table subrptTable in crSubreportDocument.Database.Tables)
{
tLogonInfo = subrptTable.LogOnInfo;
tLogonInfo.ConnectionInfo.DatabaseName = newDataFile;
tLogonInfo.ConnectionInfo.ServerName = newDataFile;
tLogonInfo.TableName = subrptTable.Name;
dtStart = DateTime.Now;
try
{
subrptTable.ApplyLogOnInfo(tLogonInfo);
}
catch (Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
//return;
}
difference = DateTime.Now.Subtract(dtStart);
btnSQLStatement.Text += "Subreport Table: " + subrptTable.Name.ToString() + " Set in " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
}
catch (Exception ex)
{
MessageBox.Show("SubReport ERROR: " + ex.Message);
//return;
}
}
}
}
}
#endregion Btrieve
// btrieve
}
#endregion Engine
}
if (btrVerifyDatabase.Checked)
{
if (chkUseRAS.Checked)
rptClientDoc.VerifyDatabase();
else
rpt.VerifyDatabase();
}
difference = DateTime.Now.Subtract(TSTotal);
btnSQLStatement.Text += "\nTotal time: " + difference.Minutes.ToString() + ":" + difference.Seconds.ToString() + ":" + difference.Milliseconds.ToString() + "\n";
}
Keywords
Crystal Reports for Visual Studio, Pervasive, Btrieve, SetLocation, ReplaceConnection , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem