SQL Server数据库数据搜索工具

[c#]代码库

/*
该数据搜索工具可以在SQL Server数据库中搜索字符串、数字和日期类型的数据，并返回相应的列和列的类型，而且可以在运行中随时取消，在运行完成后可以将结果复制到剪贴板或导出为Excel 2007格式文件。适合新接手项目的开发人员理解和研究已有的软件系统。
*/

//frmMain.cs
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Threading;
using System.Windows.Forms;

namespace DatabaseAnalysisTool
{
    public partial class frmMain : Form
    {
        public frmMain(SMO smoSearchDatabaseData, string DatabaseName)
        {
            InitializeComponent();
            this.smoSearchDatabaseData = smoSearchDatabaseData;
            this.DatabaseName = DatabaseName;
        }

        private bool isSearchExact;
        private DateTime dtSpending;
        private string DatabaseName;
        private bool isEmptyValues;

        private SMO smoSearchDatabaseData;

        private DataTable dt = null;
        private bool isBusy = false;

        private void timer3_Tick(object sender, EventArgs e)
        {
            dtSpending = dtSpending.AddSeconds(1);
            label3.Text = dtSpending.Hour.ToString().PadLeft(2, '0') + ":" + dtSpending.ToString("mm:ss");
        }

        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (this.WindowState == FormWindowState.Minimized)
            {
                Show();
                WindowState = FormWindowState.Normal;
                notifyIcon1.Visible = false;
                ShowInTaskbar = true;
            }
        }

        private void searchDatabaseValueWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            string[] strVals = (string[])e.Argument;
            List<object> lsValues = new List<object>();
            short shortVal;
            int intVal;
            long longVal;
            float floatVal;
            double doubleVal;
            DateTime dtVal;
            foreach (string str in strVals)
            {
                if (str.Trim() != string.Empty)
                {
                    if (short.TryParse(str, out shortVal))
                        lsValues.Add(shortVal);
                    else if (int.TryParse(str, out intVal))
                        lsValues.Add(intVal);
                    else if (long.TryParse(str, out longVal))
                        lsValues.Add(longVal);
                    else if (float.TryParse(str, out floatVal))
                        lsValues.Add(floatVal);
                    else if (double.TryParse(str, out doubleVal))
                        lsValues.Add(doubleVal);
                    else if (DateTime.TryParse(str, out dtVal))
                        lsValues.Add(dtVal);
                    else
                        lsValues.Add(str);
                }
            }
            isEmptyValues = lsValues.Count == 0;
            if (isEmptyValues)
                return;
            else
                dt = smoSearchDatabaseData.DatabaseSearchValue(lsValues, true, isSearchExact);
            e.Cancel = searchDatabaseValueWorker.CancellationPending;
        }

        private void searchDatabaseValueWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            SearchDatabaseDataDone(e);
        }

        private void searchDatabaseKeywordWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            dt = smoSearchDatabaseData.DatabaseSearchString((string[])e.Argument, true, isSearchExact);
            e.Cancel = searchDatabaseKeywordWorker.CancellationPending;
        }

        private void searchDatabaseKeywordWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            SearchDatabaseDataDone(e);
        }

        private void SearchDatabaseDataDone(RunWorkerCompletedEventArgs e)
        {
            isBusy = false;
            timer3.Enabled = false;
            btnDBsearchData.Enabled = true;
            btnCancel.Enabled = false;
            if (isEmptyValues)
            {
                notifyIcon1.BalloonTipTitle = "数据库内容搜索数据";
                MessageBox.Show("没有找到可供查询的数据！");
                return;
            }
            else if (e.Cancelled)
            {
                dgSearchValueResult.DataSource = null;
                dgSearchValueResult.Refresh();
                notifyIcon1.BalloonTipTitle = "数据库内容搜索数据（已取消）";
                MessageBox.Show("数据库搜索已取消！");
            }
            else
            {
                dt.Columns[0].ColumnName = "数据库列名";
                dt.Columns[1].ColumnName = "列类型";
                dt.Columns[2].ColumnName = "值";
                dgSearchValueResult.DataSource = dt;
                dgSearchValueResult.Refresh();
                bool success = dt != null && dt.Rows.Count > 0;
                if (success)
                    dgSearchValueResult.AutoResizeColumns();
                btnExportExcel.Enabled = success;
                btnCopySelectedData.Enabled = success;
                btnCopyAllData.Enabled = success;
            }
            if (!ShowInTaskbar)
            {
                notifyIcon1.ShowBalloonTip(1000, notifyIcon1.BalloonTipTitle, "搜索关键字已完成！", notifyIcon1.BalloonTipIcon);
                Thread.Sleep(3000);
                Show();
                WindowState = FormWindowState.Normal;
                notifyIcon1.Visible = false;
                ShowInTaskbar = true;
            }
        }

        private void btnDBsearchData_Click(object sender, EventArgs e)
        {
            isBusy = true;
            btnDBsearchData.Enabled = false;
            btnCancel.Enabled = true;
            isSearchExact = chkExact.Checked;
            dtSpending = new DateTime(1900, 1, 1, 0, 0, 0);
            label3.Text = dtSpending.Hour.ToString().PadLeft(2, '0') + ":" + dtSpending.ToString("mm:ss");
            timer3.Enabled = true;
            if (chkSearchDataAsString.Checked)
                searchDatabaseKeywordWorker.RunWorkerAsync(txtValues.Lines);
            else
                searchDatabaseValueWorker.RunWorkerAsync(txtValues.Lines);
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            if (chkSearchDataAsString.Checked)
                searchDatabaseKeywordWorker.CancelAsync();
            else
                searchDatabaseValueWorker.CancelAsync();
            smoSearchDatabaseData.CancelCommand();
        }

        private void btnExportExcel_Click(object sender, EventArgs e)
        {
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                ConvertToExcel(saveFileDialog1.FileName, dt, DatabaseName);
        }

        public Stream RenderDataTableToExcel(DataTable SourceTable, string sheetName, bool dateWithTime)
        {
            XSSFWorkbook workbook = null;
            MemoryStream ms = null;
            ISheet sheet = null;
            XSSFRow headerRow = null;
            try
            {
                workbook = new XSSFWorkbook();
                IDataFormat dateFormat = workbook.CreateDataFormat();
                ICellStyle dateStyle = workbook.CreateCellStyle();
                dateStyle.DataFormat = dateFormat.GetFormat("yyyy/m/d" + (dateWithTime ? " h:mm;@" : string.Empty));
                IDataFormat decimalFormat1 = workbook.CreateDataFormat();
                ICellStyle decimalStyle1 = workbook.CreateCellStyle();
                decimalStyle1.DataFormat = decimalFormat1.GetFormat("#0.0");
                IDataFormat decimalFormat2 = workbook.CreateDataFormat();
                ICellStyle decimalStyle2 = workbook.CreateCellStyle();
                decimalStyle2.DataFormat = decimalFormat2.GetFormat("#,##0.00");
                ms = new MemoryStream();
                sheet = workbook.CreateSheet(sheetName);
                headerRow = (XSSFRow)sheet.CreateRow(0);
                foreach (DataColumn column in SourceTable.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                int rowIndex = 1;
                foreach (DataRow row in SourceTable.Rows)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in SourceTable.Columns)
                    {
                        if (row[column] is int)
                            dataRow.CreateCell(column.Ordinal).SetCellValue((int)row[column]);
                        else if (row[column] is decimal)
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal);
                            cell.SetCellValue((double)(decimal)row[column]);
                            cell.CellStyle = column.Ordinal == 10 ? decimalStyle2 : decimalStyle1;
                        }
                        else if (row[column] is float)
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal);
                            cell.SetCellValue((double)(float)row[column]);
                        }
                        else if (row[column] is double)
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal);
                            cell.SetCellValue((double)row[column]);
                        }
                        else if (row[column] is DateTime)
                        {
                            ICell cell = dataRow.CreateCell(column.Ordinal);
                            cell.SetCellValue((DateTime)row[column]);
                            cell.CellStyle = dateStyle;
                        }
                        else
                            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    ++rowIndex;
                }
                //列宽自适应，只对英文和数字有效
                for (int i = 0; i <= SourceTable.Columns.Count; ++i)
                    sheet.AutoSizeColumn(i);
                workbook.Write(ms);
                ms.Flush();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                ms.Close();
                sheet = null;
                headerRow = null;
                workbook = null;
            }
            return ms;
        }

        public void ConvertToExcel(string xlsSaveFileName, DataTable SourceTable, string sheetName, bool dateWithTime = true)
        {
            FileStream fs = null;
            try
            {
                using (fs = new FileStream(xlsSaveFileName, FileMode.Create, FileAccess.Write))
                {
                    using (BinaryWriter bw = new BinaryWriter(fs))
                    {
                        MemoryStream ms = RenderDataTableToExcel(SourceTable, sheetName, dateWithTime) as MemoryStream;
                        bw.Write(ms.ToArray());
                        bw.Flush();
                        bw.Close();
                    }
                }
            }
            catch (System.Exception ex)
            {
                throw new System.Exception("转换数据到Excel失败：" + ex.Message);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }
        }

        private void btnCopySelectedData_Click(object sender, EventArgs e)
        {
            try
            {
                Clipboard.SetText(dgSearchValueResult.GetClipboardContent().GetText());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnCopyAllData_Click(object sender, EventArgs e)
        {
            try
            {
                dgSearchValueResult.SelectAll();
                Clipboard.SetText(dgSearchValueResult.GetClipboardContent().GetText());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            smoSearchDatabaseData.DatabaseName = DatabaseName;
            Text += "（" + DatabaseName + "）";
            notifyIcon1.BalloonTipTitle += "（" + DatabaseName + "）";
            notifyIcon1.Text += "（" + DatabaseName + "）";
            toolTip1.SetToolTip(this.txtValues, "可以输入文本、数字和日期类型的数据，工具可以自动识别它们的类型");
        }

        private void frmMain_Resize(object sender, EventArgs e)
        {
            if (this.WindowState == FormWindowState.Minimized) //判断是否最小化
            {
                this.ShowInTaskbar = false; //不显示在系统任务栏
                notifyIcon1.Visible = true; //托盘图标可见
                if (isBusy)
                    notifyIcon1.ShowBalloonTip(1000);
                else
                    notifyIcon1.ShowBalloonTip(1000, notifyIcon1.BalloonTipTitle, "双击还原搜索工具！", notifyIcon1.BalloonTipIcon);
            }
        }

        private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }

        private void lnkReturn_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            if (isBusy)
            {
                if (MessageBox.Show("正在执行关键字搜索任务，是否需要取消当前任务重新选择数据库？", Text, MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    btnCancel_Click(sender, e);
                    Dispose();
                    new frmConnect().ShowDialog();
                }
            }
            else
            {
                Dispose();
                new frmConnect().ShowDialog();
            }
        }
    }
}

[代码运行效果截图]

[源代码打包下载]