Exporting the Results of a Query as a String

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:4

Thêm vào BST

Báo xấu

85
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ Team LiB ] Recipe 5.11 Exporting the Results of a Query as a String Problem You need to export the results of a query to a string in a manner similar to the GetString( ) method of the ADO Recordset. Solution Write a routine to mimic the functionality of the ADO Recordset's GetString( ) method.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Exporting the Results of a Query as a String

[ Team LiB ] Recipe 5.11 Exporting the Results of a Query as a String Problem You need to export the results of a query to a string in a manner similar to the GetString( ) method of the ADO Recordset. Solution Write a routine to mimic the functionality of the ADO Recordset's GetString( ) method. The sample code contains an event handler and a method: Go Button.Click Sets up the sample by creating a DataTable containing the Orders table from Northwind. The GetString( ) method in this solution is called to convert the DataTable into a string similar to one that is generated by the GetString( ) method of the ADO Recordset. The string is displayed in a text box on the form. GetString( ) This method mimics the functionality of the GetString( ) method of the ADO Recordset. The method iterates over the collection of rows and columns in the table appending the field values to a string. Null values are replaced as specified and column and row delimiters are applied. The C# code is shown in Example 5-13. Example 5-13. File: AdoGetStringForm.cs // Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; // Table name constants private const String ORDERS_TABLE = "Orders"; // . . .
private void goButton_Click(object sender, System.EventArgs e) { // Fill the Order table. SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); DataTable dt = new DataTable(ORDERS_TABLE); da.Fill(dt); // Call method to convert the DataTable to a string. resultTextBox.Text = GetString(dt, -1, null, null, null); } private String GetString(DataTable dt, int numRows, String columnDelimiter, String rowDelimiter, String nullExpr) { if(numRows < 0) // Negative number of rows returns all rows numRows = dt.Rows.Count; else // Set number of rows to the lesser of the user entered // number of rows and the number of rows in the table. numRows = Math.Max(numRows, dt.Rows.Count); // Column delimiter defaults to TAB if(columnDelimiter == null) columnDelimiter = "\t"; // Row delimiter defaults to CARRIAGE RETURN if(rowDelimiter == null) rowDelimiter = "\r"; // Null expression defaults to empty string if(nullExpr == null) nullExpr = ""; StringBuilder sb = new StringBuilder( ); // Iterate over the collection of rows. for(int i = 0; i < numRows; i++) { // Iterate over the collection of columns. foreach (object col in dt.Rows[i].ItemArray)
{ // Replace null values as they occur. String colString = (col == System.DBNull.Value) ? nullExpr : col.ToString( ); // Add the column value to the string. sb.Append(colString + columnDelimiter); } // Remove the column delimiter on last field. sb.Remove(sb.Length - columnDelimiter.Length, columnDelimiter.Length); // Append row delimiter. sb.Append(rowDelimiter); } return sb.ToString( ); } Discussion ADO.NET does not contain a method that is equivalent to the GetString( ) method of the ADO Recordset or a method that converts the Recordset to a string. This solution presents an ADO.NET method, which is also called GetString( ), that duplicates the functionality of the ADO GetString( ) method. The prototype for the ADO.NET method is: String tableString = GetString(DataTable dt, Integer numRows, String columnDelimiter, String rowDelimiter, String nullExpr); Parameters tableString Returns a string corresponding to the rows selected from the table. dt The DataTable to convert to a string. numRows The number of rows in the table to convert. If this number is negative, all rows in the table are converted. If a number larger than the number of records in the table
is specified, all records are converted without error. columnDelimiter The character or characters that separate columns. The default value is the TAB character. rowDelimiter The character or characters that separate rows. The default value is the CARRIAGE RETURN character. nullExpr A string that is substituted for null column values in the table. The default value is an empty string. [ Team LiB ]