{"id":742,"date":"2008-01-04T19:56:57","date_gmt":"2008-01-05T02:56:57","guid":{"rendered":"http:\/\/members.crystalreportsbook.com\/crystal-reports-xi\/1727-c-code-listings\/"},"modified":"2008-01-04T19:56:57","modified_gmt":"2008-01-05T02:56:57","slug":"1727-c-code-listings","status":"publish","type":"post","link":"http:\/\/www.crystalreportsonlinetraining.com\/training\/1727-c-code-listings\/","title":{"rendered":"17.27 C# Code Listings"},"content":{"rendered":"

C# Code Listings<\/h1>\n

The C# code listings are equivalent to the VB.NET code listings.<\/p>\n

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

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37,2],"tags":[],"class_list":["post-742","post","type-post","status-publish","format-standard","hentry","category-chapter-17-dynamic-data-sources","category-crystal-reportsnet-2003","entry"],"_links":{"self":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts\/742","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/comments?post=742"}],"version-history":[{"count":0,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/posts\/742\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/media?parent=742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/categories?post=742"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.crystalreportsonlinetraining.com\/training\/wp-json\/wp\/v2\/tags?post=742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}