Effect:
1. Foreword
On the Winform platform, controls such as DataGridView can be used to display the form data of the database, but in the C# console project, if the database query is useful, it is more difficult for us to see the effect of the query statement. For example, I write at will A console output, the code is as follows:
using System. Data;
namespace CSharpConnectMySQL
{
internal class Program
{
static void Main(string[] args)
{
string sql = "SELECT * FROM goods_type";
DataSet dataSet = MySqlHelper. GetDataSet(sql);
DataTable dt = dataSet. Tables[0];
if(dt. Rows. Count > 0)
{
// print all column names
string columnName = string. Empty;
for (int i = 0; i < dt. Columns. Count; i ++ )
{
columnName + = dt.Columns[i].ColumnName + " | ";
}
Console. WriteLine(columnName);
Console.WriteLine("-------------------------");
//Print the data of each row
foreach (DataRow row in dt.Rows)
{
string columnStr = string. Empty;
foreach (DataColumn column in dt.Columns)
{
columnStr + = row[column] + " | ";
}
Console. WriteLine(columnStr);
}
}
Console. ReadKey();
}
}
}
Effect:
Query results in Navicat 16 for MySQL software
Since there is no automatic alignment function, the printed ones are particularly ugly, so I thought, can I write a method to print the database on the console, and it can be automatically aligned, although it is not very effective, at least it is comfortable to look at, then the next step Let’s implement this function.
2. Realize the effect
Since the latter effect is useful to the database, you can install mysql without installing it and add some data, or manually write a DataTable entity and add some data.
Create a new C# console project, add some classes needed to connect to the database, and necessary plug-ins according to my previous post
C# connect to MySQL database_c# mysql_Xiong Siyu’s blog-CSDN blog
Add a class ConsoleTool
using System;
using System.Collections.Generic;
using System. Linq;
using System. Text;
using System. Threading. Tasks;
using System.Data;
public class ConsoleTool
{
/// <summary>
/// Print DataTable on the console
/// </summary>
/// <param name="dt"></param>
public static void PrintDataTablele(DataTable dt)
{
if (dt == null)
{
Console.WriteLine("DataTable cannot be empty");
return;
}
// Get the maximum length of each column
float[] lenList = GetColumnsMaxFontLength(dt);
if (IsExceedMaxValue(lenList))
{
Console.WriteLine("The total length of the DataTable column is too long, the console cannot fully display");
return;
}
// How many spaces to add at the end of the string
int endAddEmptyCount = 2;
// print title
string columnName = string. Empty;
for (int i = 0; i < dt. Columns. Count; i ++ )
{
string value = dt.Columns[i].ColumnName;
float emptyCount = lenList[i] - GetFontLenght(value);
value = AppendEmpty(value, emptyCount + endAddEmptyCount);
columnName += value;
}
string hengang = GetPrintBar(GetFontLenght(columnName));
Console. WriteLine(hengang);
Console. WriteLine(columnName);
Console. WriteLine(hengang);
// print content
for (int i = 0; i < dt. Rows. Count; i ++ )
{
string columnStr = string. Empty;
for (int j = 0; j < dt.Columns.Count; j++ )
{
string? value = dt.Rows[i][j].ToString();
if (string.IsNullOrEmpty(value)) value = "null";
float emptyCount = lenList[j] - GetFontLenght(value);
value = AppendEmpty(value, emptyCount + endAddEmptyCount);
columnStr += value;
}
Console. WriteLine(columnStr);
}
Console. WriteLine(hengang);
}
/// <summary>
/// Is the specified maximum value exceeded?
/// </summary>
/// <param name="lenList"></param>
/// <returns></returns>
private static bool IsExceedMaxValue(float[] lenList)
{
int singleMax = 100;
int totalLength = 90;
float value = 0;
for (int i = 0; i < lenList. Length; i ++ )
{
value + = lenList[i];
}
if (value > totalLength)
{
return true;
}
for (int i = 0; i < lenList. Length; i ++ )
{
if (lenList[i] > singleMax)
{
return true;
}
}
return false;
}
/// <summary>
/// Get the maximum length of the string in the column
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
private static float[] GetColumnsMaxFontLength(DataTable dataTable)
{
if (dataTable.Columns.Count == 0) return new float[0];
if (dataTable.Rows.Count == 0) return new float[0];
float[] columnLength = new float[dataTable.Columns.Count];
//Add title first
for (int i = 0; i < dataTable. Columns. Count; i ++ )
{
columnLength[i] = GetFontLenght(dataTable.Columns[i].ColumnName);
}
// Add specific columns
for (int i = 0; i < dataTable. Rows. Count; i ++ )
{
for (int j = 0; j < dataTable.Columns.Count; j++ )
{
string? columnsCon = dataTable.Rows[i][j].ToString();
if (string.IsNullOrEmpty(columnsCon)) columnsCon = "null";
float columnsConLen = GetFontLenght(columnsCon);
if (columnsConLen > columnLength[j])
{
columnLength[j] = columnsConLen;
}
}
}
return columnLength;
}
/// <summary>
/// Get the length of the font
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private static float GetFontLenght(string? value)
{
float nCount = 0;
for (int i = 0; i < value?. Length; i ++ )
{
char val = value[i];
//if (val >= 0x4e00 & amp; & amp; val <= 0x9fbb)//Chinese characters
// nCount += 1;
if (IsChinese(val))
nCount += 1;
else
nCount += 0.5f;
}
return nCount;
}
/// <summary>
/// Add spaces to the string
/// </summary>
/// <param name="value"></param>
/// <param name="count"></param>
/// <returns></returns>
private static string AppendEmpty(string? value, float count)
{
if (string.IsNullOrEmpty(value))
return string.Empty;
if (count == 0)
return value + " ";
string[] arr = count.ToString().Split('.');
int integer = 0;
int decimals = 0;
if (arr. Length == 1)
{
if (!int. TryParse(arr[0], out integer))
return string. Empty;
}
if (arr. Length == 2)
{
if (!int. TryParse(arr[0], out integer))
return string. Empty;
if (!int. TryParse(arr[1], out decimals))
return string. Empty;
}
for (int i = 0; i < integer; i ++ )
{
value + = " ";
}
if (decimals == 5)
{
value + = " ";
}
return value;
}
/// <summary>
/// Get the bar
/// </summary>
/// <param name="count"></param>
/// <returns></returns>
private static string GetPrintBar(float count)
{
string bar = string. Empty;
string[] arr = count.ToString().Split('.');
int integer = 0;
int decimals = 0;
if (arr. Length == 1)
{
if (!int. TryParse(arr[0], out integer))
return string. Empty;
}
if (arr. Length == 2)
{
if (!int. TryParse(arr[0], out integer))
return string.Empty;
if (!int. TryParse(arr[1], out decimals))
return string.Empty;
}
for (int i = 0; i < integer; i ++ )
{
bar + = "--";
}
if (decimals == 5)
{
bar + = "-";
}
return bar;
}
/// <summary>
/// Whether it is Chinese
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
private static bool IsChinese(char value)
{
UnicodeEncoding unicodeencoding = new UnicodeEncoding();
byte[] unicodebytearray = unicodeencoding. GetBytes(value. ToString());
bool isChina = false;
for (int i = 0; i < unicodebytearray. Length; i++ )
{
i + + ;
//If it is a Chinese character, the high bit is not 0
if (unicodebytearray[i] != 0)
{
isChina = true;
break;
}
}
return is China;
}
}
transfer:
using System. Data;
namespace CSharpConnectMySQL
{
internal class Program
{
static void Main(string[] args)
{
string sql = "SELECT * FROM goods_type";
DataSet dataSet = MySqlHelper. GetDataSet(sql);
if (dataSet. Tables. Count == 0) return;
DataTable dt = dataSet. Tables[0];
if(dt. Rows. Count > 0)
{
ConsoleTool.PrintDataTablele(dt);
}
Console. ReadKey();
}
}
}
run:
Since the data is relatively small, the effect is not obvious, and the effect will be better if there is more data.
end