c# 查询SQL SERVER测试数据
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Threading;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;
namespace InQuireTestData_V1._00
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public void InitialControl(ComboBox Key)//设置查询关键字项目内容
{
Key.Items.Add("SN");
Key.Items.Add("Mac_Address");
Key.Items.Add("JobNumber");
Key.Items.Add("Model");
this.textBox1.Enabled = false;
}
private void Form1_Load(object sender, EventArgs e)
{
comboBox2.Enabled = false;
InitialControl(comboBox1);
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.Text != string.Empty)
textBox1.Enabled = true;
textBox1.Text = "";
comboBox2.Items.Clear();
comboBox2.Enabled = false;
textBox2.Text = "";
textBox2.Enabled = false;
}
public bool IsSatisfyCondition(string KeyValues1,int KeyValues2)
{
string[] KeyStr = { "SN", "Mac_Address", "JobNumber", "Model"};
int[] KeyValues = { 13, 12, 6, 5 };
int n = 0;
foreach(string str in KeyStr)
{
int s;
s = KeyValues[n];
if(n==1)
{
if (KeyValues1 == str && KeyValues2 == KeyValues[n]) return true;
}
else
{
if (KeyValues1 == str && KeyValues2 >= KeyValues[n]) return true;
}
n++;
}
return false;
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
comboBox2.Enabled = false;
comboBox2.Items.Clear();
if ((IsSatisfyCondition(comboBox1.Text, textBox1.Text.Length))== true)
{
string[] KeyStr = { "SN", "Mac_Address", "JobNumber", "Model" };
foreach(string str in KeyStr)
{
if (str != comboBox1.Text.Trim())
comboBox2.Items.Add(str);
}
textBox2.Text = "";
comboBox2.Enabled = true;
}
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
if(comboBox2.Text!=string.Empty)
{
textBox2.Enabled = true;
}
}
private void AutoSizeColumn(DataGridView dgViewFiles)
{
int width = 0;
//使列自使用宽度
//对于DataGridView的每一个列都调整
for (int i = 0; i < dgViewFiles.Columns.Count; i++)
{
//将每一列都调整为自动适应模式
dgViewFiles.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells);
//记录整个DataGridView的宽度
width += dgViewFiles.Columns[i].Width;
}
//判断调整后的宽度与原来设定的宽度的关系，如果是调整后的宽度大于原来设定的宽度，
//则将DataGridView的列自动调整模式设置为显示的列即可，
//如果是小于原来设定的宽度，将模式改为填充。
if (width > dgViewFiles.Size.Width)
{
dgViewFiles.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
}
else
{
dgViewFiles.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
//冻结某列 从左开始 0，1，2
dgViewFiles.Columns[1].Frozen = true;
}
public void OneKeyValuesReadData(string Key1,string Key2,DataGridView Key3)
{
SqlConnection conn = new SqlConnection("server=pe01;database=E_TestData;uid=sa;pwd=system");
try
{
conn.Open();
string command = @"select * from TDATA where " + Key1 + "=" + "'" + Key2 + "'";
SqlCommand cmd = new SqlCommand(command, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "cs");
Key3.DataSource = ds.Tables[0];
Key3.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
Key3.ReadOnly = true;
Key3.DefaultCellStyle.SelectionBackColor = Color.YellowGreen;
//for (int i = 0; i < Key3.Columns.Count; i++)
// Key3.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
this.label1.Text = "已查询到数据:" + (Key3.Rows.Count-1).ToString() + "条";
//this.label1.BackColor = Color.YellowGreen;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
public void TwoKeyValuesReadData(string Key1,string Key2,string Key3,string Key4,DataGridView Key5)
{
SqlConnection conn = new SqlConnection("server=pe01;database=E_TestData;uid=sa;pwd=system");
try
{
conn.Open();
string command = @"select * from TDATA where " + Key1 + "=" + "'" + Key2 + "'" + " and " + Key3 + "=" + "'" + Key4 + "'";
SqlCommand cmd = new SqlCommand(command,conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
sda.Fill(ds,"cs");
Key5.DataSource = ds.Tables[0];
Key5.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
Key5.ReadOnly = true;
Key5.DefaultCellStyle.SelectionBackColor = Color.YellowGreen;
//for (int i = 0; i < Key5.ColumnCount; i++)
// Key5.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
this.label1.Text = "已查询到数据:" + (Key5.Rows.Count-1).ToString() + "条";
//this.label1.BackColor = Color.YellowGreen;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (IsSatisfyCondition(comboBox2.Text, textBox2.Text.Length) == true)
{
TwoKeyValuesReadData(comboBox1.Text.Trim(),textBox1.Text.Trim(),comboBox2.Text.Trim(),textBox2.Text.Trim(),dataGridView1);
AutoSizeColumn(dataGridView1);
button2.Enabled = true;
}
else if ((IsSatisfyCondition(comboBox1.Text, textBox1.Text.Length)) == true)
{
OneKeyValuesReadData(comboBox1.Text.Trim(),textBox1.Text.Trim(),dataGridView1);
AutoSizeColumn(dataGridView1);
button2.Enabled = true;
}
else
{
MessageBox.Show("请录入正确的关键字格式","系统提示");
}
}
private void button2_Click(object sender, EventArgs e)
{
string ExampleExcelFileName = DateTime.Now.ToString("yyyyMMddHHmmss");
ExportExcel(ExampleExcelFileName,this.dataGridView1);
}
//数据导入EXCEL
public static void ExportExcel(string fileName,DataGridView myDGV)
{
if(myDGV.Rows.Count>0)//存在测试数据
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return;//被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if(xlApp==null)
{
MessageBox.Show("无法创建Excel对象,可能您的系统未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入标题
for(int i=0;i<myDGV.ColumnCount;i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for(int r=0;r<myDGV.Rows.Count;r++)
{
for(int i=0;i<myDGV.ColumnCount;i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if(saveFileName!="")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch(Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n"+ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("导出文件成功","提示",MessageBoxButtons.OK);
}
else
{
MessageBox.Show("报表为空,无表格需要导出","提示",MessageBoxButtons.OK);
}
}
}
}