C# Code Listings

The C# code listings are equivalent to the VB.NET code listings.

Listing 17-2. Creating an XML schema file from a dataset object.
//Write a populated dataset object to an XML schema file
//The XmlFileName should have ".ds" file extension
public void CreateXMLFile(string XmlFileName, DataSet XmlDataSet)
{
XmlDataSet.WriteXmlSchema(XmlFileName);
}
Listing 17-3. Populating a dataset with a MS Access table.
public void FillDataSet(ref DataSet MyDataSet)
{
OleDbDataAdapter MyDataAdapter;
string MyConnectionString;
string SQL = "SELECT * FROM Customer";
MyDataSet = new DataSet();
MyConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=C\\Xtreme.mdb;User Id=Admin;" +
"Jet OLEDB:Database Password=;Jet OLEDB:System database=" +
"C:\\ProgramFiles\\Office2000\\Office\\System.mdw";
MyDataAdapter = new OleDbDataAdapter(SQL, MyConnectionString);
MyDataAdapter.Fill(MyDataSet, "Customer");
}
Listing 17-4. Populating a dataset with a SQL Server table.
public void FillDataSet(ref DataSet MyDataSet)
{
string MyConnectionString;
System.Data.SqlClient.SqlDataAdapter MyDataAdapter;
MyDataSet = new DataSet();
string MySql = "SELECT Customers.*, Orders.* " +
"FROM Customers INNER Join Orders " +
"ON Customers.CustomerId = Orders.CustomerId";
MyConnectionString = "Data Source=(local);UID=sa;pwd=pw;Database=Northwind";
MyDataAdapter = new SqlDataAdapter(MySql, MyConnectionString);
MyDataAdapter.Fill(MyDataSet, "Customers");
}
Listing 17-5. Populating a dataset with an XML file.
public void FillDataset(DataSet MyDataSet, string XmlFileName)
{
MyDataSet.ReadXml(XmlFileName);
}
Listing 17-6. Manually populating a dataset.
public void FillDataSet17_6(ref DataSet MyDataSet)
{
MyDataSet = new DataSet();
string[,] Customers = new string[,]{{"123","Jones"},{"456","Smith"}};
//Create a new Data Table
DataTable MyDataTable = new DataTable("TableName");
DataColumn MyDataColumn;
DataRow MyDataRow;
//Create first column
MyDataColumn = new DataColumn();
MyDataColumn.DataType = System.Type.GetType("System.String");
MyDataColumn.ColumnName = "CustomerId";
MyDataTable.Columns.Add(MyDataColumn);
//Create the second column
MyDataColumn = new DataColumn();
MyDataColumn.DataType = System.Type.GetType("System.String");
MyDataColumn.ColumnName = "LastName";
MyDataTable.Columns.Add(MyDataColumn);
MyDataSet.Tables.Add(MyDataTable);
//Copy the array into the datatable
int ixCustomers;
for (ixCustomers=0; ixCustomers<=1; ixCustomers++)
{
MyDataRow = MyDataTable.NewRow();
MyDataRow["CustomerId"] = Customers[ixCustomers, 0];
MyDataRow["LastName"] = Customers[ixCustomers, 1];
MyDataTable.Rows.Add(MyDataRow);
}
}
Listing 17-7. Getting the dataset object from a datagrid control.
public void FillDataset(DataSet MyDataSet)
{
MyDataSet = (DataSet)DataGrid1.DataSource;
}
Listing 17-8. Printing from a DataView object.
public void PrintDataView(DataSet MyDataSet)
{
DataView MyDataView;
CrystalReport1 MyReport = new CrystalReport1();
MyDataView = new DataView(MyDataSet.Tables["TableName"],
"CompanyName > 'b'","", DataViewRowState.CurrentRows);
MyReport.SetDataSource(MyDataView);
crystalReportViewer1.ReportSource = MyReport;
}
Listing 17-9. Convert the DataView object into a DataTable
public DataTable ConvertViewToTable(DataView MyDataView)
{
int Col, Row;
Object[] NewRow;
DataTable NewTable = new DataTable(MyDataView.Table.TableName);
//Copy the column objects into the table
for (Col=0; Col
{
NewTable.Columns.Add(
MyDataView.Table.Columns[Col].ColumnName,
MyDataView.Table.Columns[Col].DataType);
}
//Create each new row and copy the column data into
//a new row object
for (Row=0; Row < MyDataView.Count; Row++)
{
NewRow = new Object[MyDataView.Table.Columns.Count];
for (Col=0; Col < MyDataView.Table.Columns.Count; Col++)
{
NewRow[Col] = MyDataView[Row][Col];
}
NewTable.Rows.Add(NewRow);
}
return NewTable;
}
Listing 17-10. Linking the dataset file to the report and previewing it.
private void mnuCh17_10_Click(object sender, System.EventArgs e)
{
CrystalReport1 MyReport = new CrystalReport1();
DataSet MyDataSet = null;
//Use the appropriate FillDataSet() method
//from the previous sections
FillDataSet(ref MyDataSet);
//Uncomment the following line if you need to create
//a new dataset file
//CreateXMLFile("C:\\FileName.ds", MyDataSet);
MyReport.SetDataSource(MyDataSet);
crystalReportViewer1.ReportSource = MyReport;
}
Listing 17-11. Logging in to multiple tables.
public void LoginToTables(string UserId, string Password)
{
CrystalReport1 MyReport = new CrystalReport1();
CrystalDecisions.Shared.TableLogOnInfo MyLogonInfo;
//Change the logon info
foreach (CrystalDecisions.CrystalReports.Engine.Table MyTable in MyReport.Database.Tables)
{
MyLogonInfo = MyTable.LogOnInfo;
if (MyTable.Name == "CorpStandards")
{
MyLogonInfo.ConnectionInfo.UserID = G_UserId;
MyLogonInfo.ConnectionInfo.Password = G_Password;
}
else
{
MyLogonInfo.ConnectionInfo.UserID = UserId;
MyLogonInfo.ConnectionInfo.Password = Password;
}
MyTable.ApplyLogOnInfo(MyLogonInfo);
//Test the user credentials
if (!MyTable.TestConnectivity())
{
MessageBox.Show("Invalid user credentials for table: " + MyTable.Name);
}
}
crystalReportViewer1.ReportSource = MyReport;
}
Listing 17-12. Setting the Logon credentials with the viewer control.
public void PrintPreview(string UserId, string Password)
{
CrystalReport1 MyReport = new CrystalReport1();
CrystalDecisions.Shared.TableLogOnInfo MyLogonInfo;
crystalReportViewer1.LogOnInfo = new CrystalDecisions.Shared.TableLogOnInfos();
//Create the Customer table and set its properties
MyLogonInfo = new CrystalDecisions.Shared.TableLogOnInfo();
MyLogonInfo.TableName = "Customer";
MyLogonInfo.ConnectionInfo.UserID = UserId;
MyLogonInfo.ConnectionInfo.Password = Password;
crystalReportViewer1.LogOnInfo.Add(MyLogonInfo);
//Create the Orders table and set its properties
MyLogonInfo = new CrystalDecisions.Shared.TableLogOnInfo();
MyLogonInfo.TableName = "Orders";
MyLogonInfo.ConnectionInfo.UserID = UserId;
MyLogonInfo.ConnectionInfo.Password = Password;
crystalReportViewer1.LogOnInfo.Add(MyLogonInfo);
//Show the report
crystalReportViewer1.ReportSource = MyReport;
}
Listing 17-13. Connecting to a stored procedure using parameters.
public void SpWithViewer(string UserId, string Password, string spParameter)
{
//Login to the server
CrystalReport1 MyReport = new CrystalReport1();
crystalReportViewer1.ReportSource = MyReport;
CrystalDecisions.Shared.TableLogOnInfo MyLogonInfo;
crystalReportViewer1.LogOnInfo = new CrystalDecisions.Shared.TableLogOnInfos();
//Create the Customer table and set its properties
MyLogonInfo = new CrystalDecisions.Shared.TableLogOnInfo();
MyLogonInfo.TableName = "spCustomers;1";
MyLogonInfo.ConnectionInfo.UserID = UserId;
MyLogonInfo.ConnectionInfo.Password = Password;
crystalReportViewer1.LogOnInfo.Add(MyLogonInfo);
//Create the parameter
CrystalDecisions.Shared.ParameterFields ParameterFields;
CrystalDecisions.Shared.IParameterField ParameterField;
CrystalDecisions.Shared.ParameterDiscreteValue spValue;
ParameterFields = crystalReportViewer1.ParameterFieldInfo;
ParameterField = ParameterFields["@CustPattern "];
spValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
spValue.Value = spParameter;
ParameterField.CurrentValues.Add(spValue);
crystalReportViewer1.ReportSource = MyReport;
}
Listing 17-14. Changing the location of a data source.
public void LoginToTables(string UserId, string Password, string ServerName, string DatabaseName)
{
CrystalReport1 MyReport = new CrystalReport1();
CrystalDecisions.Shared.TableLogOnInfo MyLogonInfo;
CrystalDecisions.Shared.ConnectionInfo MyConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();
if (ServerName != "")
{
MyConnectionInfo.ServerName = ServerName;
MyConnectionInfo.DatabaseName = DatabaseName;
}
MyConnectionInfo.UserID = UserId;
MyConnectionInfo.Password = Password;
foreach (CrystalDecisions.CrystalReports.Engine.Table MyTable in MyReport.Database.Tables)
{
MyLogonInfo = MyTable.LogOnInfo;
MyLogonInfo.ConnectionInfo = MyConnectionInfo;
MyTable.ApplyLogOnInfo(MyLogonInfo);
//Note: The next line is only necessary for SQL Server
if (ServerName != "")
{
MyTable.Location = MyTable.Location.Substring(MyTable.Location.LastIndexOf(".")+1);
}
}
crystalReportViewer1.ReportSource = MyReport;
}
Listing 17-15. Set subreport credentials
CrystalReport1 MyReport = new CrystalReport1();
DataSet ds0=null, ds1=null, ds2=null;
FillDataSet(ds0);
FillDataSet(ds1);
FillDataSet(ds2);
MyReport.SetDataSource(ds0);
MyReport.OpenSubreport("subreport1").SetDataSource(ds1);
MyReport.OpenSubreport("subreport2").SetDataSource(ds2);
crystalReportViewer1.ReportSource = MyReport;
Listing 17-16. Printing dynamic images with a DataSet object.
DataSet MyDataSet = new DataSet();
DataTable MyDataTable;
DataRow MyDataRow;
DataColumn MyDataColumn;
MyDataTable = new DataTable("ImageTable");
//Create the first column
MyDataColumn = new DataColumn("PicNumber", Type.GetType("System.Int32"));
MyDataTable.Columns.Add(MyDataColumn);
//Field that points to the image file
MyDataColumn = new DataColumn("ImagePath",Type.GetType("System.String"));
MyDataTable.Columns.Add(MyDataColumn);
//Populate the tabel with dummy data
//Make sure your C Drive has two files called Image1.jpg and Image2.jpg
MyDataRow = MyDataTable.NewRow();
MyDataRow["PicNumber"] = 1;
MyDataRow["ImagePath"] = "C:\\Image1.jpg";
MyDataTable.Rows.Add(MyDataRow);
MyDataRow = MyDataTable.NewRow();
MyDataRow["PicNumber"] = 2;
MyDataRow["ImagePath"] = "C:\\Image2.jpg";
MyDataTable.Rows.Add(MyDataRow);
MyDataSet.Tables.Add(MyDataTable);
//Add the image column to the table
AddImageColumn(MyDataTable, "Image");
//Only do this when you first design the report
MyDataSet.WriteXmlSchema(@"c:\ImageTable.xsd");
//Load the images into the datatable
LoadAllImages(MyDataTable,"ImagePath", "Image");
//Open the report and preview it
CrystalReport1 MyReport = new CrystalReport1();
MyReport.SetDataSource(MyDataSet);
crystalReportViewer1.ReportSource = MyReport;
Listing 17-17. Create a table column for the image.
public void AddImageColumn(DataTable MyDataTable, string FieldName)
{
//Create the column to hold the binary image
DataColumn MyDataColumn = new DataColumn(FieldName, Type.GetType("System.Byte[]"));
MyDataTable.Columns.Add(MyDataColumn);
}
Listing 17-18. Process each row in the table.
public void LoadAllImages(DataTable MyDataTable, string FilePathField, string ImageField)
{
//Loop through the rows and load the images
foreach(DataRow MyDataRow in MyDataTable.Rows)
{
LoadImage(MyDataRow, ImageField, MyDataRow[FilePathField].ToString());
}
}
Listing 17-19. Load a single image into a DataRow.
public void LoadImage(DataRow MyDataRow, string ImageField, string FilePath)
{
System.IO.FileStream fs = new System.IO.FileStream(FilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);
Byte[] Image = new Byte[fs.Length];
fs.Read(Image, 0, (int)fs.Length);
fs.Close();
MyDataRow[ImageField] = Image;
}