我的简易SQL查询分析器

原来一直搞ASP.NET，现在工作又主要搞WinFrom，真的很不习惯，一个控件那么多事件和属性，做什么都要不停地查MSDN

今天工作没事情做，为了能熟悉一下Winform里的控件，自己就做点小小东西来熟悉一下。

鄙人不才，不是很会写博客，今天深圳下好大的雨，都淋湿了，一回来就想和大家分享一下自己的成果，知识来自于网路，服务于网络，大家不要喷，谢谢！

先是一个简单的输入服务和帐号信息的登录窗体，其实可以把服务换成CommBox控件绑定本地电脑的SQL服务，鄙人不才，没有弄出来，如果你知道怎么搞，给点意见：

登录示例图如下：

如果数据量大，获取每个数据库中表以及字段信息，会有点慢，所以TreeView每点击一个节点就加载一个节点下的内容，但是没加载的节点前不是+号，感觉不知道怎么解决，纠结……

所有的数据库、表以及字段可以拖拉到TextBox空间中，本来想用RichTextBox的，因为可以变色，但是好像没有TextBox支持拖拉的事件以及属性，可能还有其他的办法吧，知道的也可以告诉一下我，查询效果图如下所示：

主要代码如下：

SQLHelper.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Data;
 6 using System.Data.SqlClient;
 7
 8 namespace DBManager
 9 {
 10 public abstract class SQLHelper
 11  {
 12 /// <summary>
 13 /// Get SqlDataReader object
 14 /// </summary>
 15 /// <param name="strSql"></param>
 16 /// <param name="dataBaseName"></param>
 17 /// <returns></returns>
 18 public static SqlDataReader ExecuteReader(string connectionString,string strSql,string dataBaseName)
 19  {
 20 try
 21  {
 22 SqlConnection conn = new SqlConnection(string.Format(connectionString, dataBaseName));
 23  conn.Open();
 24 SqlCommand cmd = new SqlCommand(strSql, conn);
 25 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
 26  }
 27 catch (Exception ex)
 28  {
 29 throw new Exception(ex.Message);
 30  }
 31  }
 32
 33 /// <summary>
 34 /// Get DataTable
 35 /// </summary>
 36 /// <param name="strSql"></param>
 37 /// <param name="dataBaseName"></param>
 38 /// <returns></returns>
 39 public static DataTable GetDataTable(string connectionString, string strSql, string dataBaseName)
 40  {
 41 using (SqlConnection conn = new SqlConnection(string.Format(connectionString, dataBaseName)))
 42  {
 43  conn.Open();
 44 SqlDataAdapter sda = new SqlDataAdapter(strSql, conn);
 45 DataSet ds = new DataSet();
 46 sda.Fill(ds,"table");
 47 return ds.Tables["table"];
 48  }
 49  }
 50
 51 /// <summary>
 52 /// Check SQL
 53 /// </summary>
 54 /// <param name="strSql"></param>
 55 /// <param name="dataBaseName"></param>
 56 /// <param name="?"></param>
 57 /// <returns></returns>
 58 public static bool CheckSQL(string connectionString, string strSql, string dataBaseName, out string errorMsg)
 59  {
 60 bool bReturn = true;
 61 SqlConnection conn = new SqlConnection(string.Format(connectionString, dataBaseName));
 62  conn.Open();
 63 try
 64  {
 65 SqlCommand cmd = new SqlCommand(strSql, conn);
 66  cmd.ExecuteNonQuery();
 67 errorMsg = string.Empty;
 68  }
 69 catch (Exception ex)
 70  {
 71 bReturn = false;
 72 errorMsg = ex.Message;
 73  }
 74 return bReturn;
 75  }
 76
 77 /// <summary>
 78 /// Check Connection
 79 /// </summary>
 80 /// <param name="connectionSql"></param>
 81 /// <param name="errorMsg"></param>
 82 /// <returns></returns>
 83 public static bool CheckConnection(string connectionString,out string errorMsg)
 84  {
 85 bool bReturn = true;
 86 try
 87  {
 88 SqlConnection conn = new SqlConnection(connectionString);
 89  conn.Open();
 90 errorMsg = string.Empty;
 91  conn.Close();
 92 return bReturn;
 93  }
 94 catch (Exception ex)
 95  {
 96 bReturn = false;
 97 errorMsg = ex.Message;
 98 return bReturn;
 99  }
100  }
101  }
102 }

initFrom

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Windows.Forms;
 9 using System.Xml;
10 using System.IO;
11
12 namespace DBManager
13 {
14 public partial class initFrom : Form
15  {
16 public mainFrom mf;
17 public initFrom()
18  {
19  InitializeComponent();
20  }
21
22 /// <summary>
23 /// 创建xml
24 /// </summary>
25 /// <param name="sender"></param>
26 /// <param name="e"></param>
27 private void btnOK_Click(object sender, EventArgs e)
28  {
29 if (this.CheckInput())
30  {
31 string connectionString = "Data Source={0}; Database={1};UID={2};PWD={3};";
32 string errorMsg = string.Empty;
33 if (SQLHelper.CheckConnection(string.Format(connectionString,this.txtDataSource.Text.Trim(),
34 "master",
35 this.txtUserID.Text.Trim(),
36 this.txtPassword.Text.Trim()), out errorMsg))
37  {
38 this.mf = new mainFrom(this.txtDataSource.Text.Trim(), this.txtUserID.Text.Trim(), this.txtPassword.Text.Trim());
39  mf.Show();
40 this.Hide();
41  }
42 else
43  {
44  MessageBox.Show(errorMsg);
45  }
46  }
47 else
48  {
49 MessageBox.Show("Please input TextBox!");
50  }
51  }
52
53 private bool CheckInput()
54  {
55 foreach (Control control in this.Controls)
56  {
57 if (control is TextBox)
58  {
59 TextBox txtBox = control as TextBox;
60 if (string.IsNullOrEmpty(txtBox.Text.Trim()))
61 return false;
62  }
63  }
64 return true;
65  }
66  }
67 }

mainFrom

 1 using System;
 2 using System.Collections.Generic;
 3 using System.ComponentModel;
 4 using System.Data;
 5 using System.Drawing;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Windows.Forms;
 9 using System.Data.SqlClient;
 10
 11 namespace DBManager
 12 {
 13 public partial class mainFrom : Form
 14  {
 15 private string connectionString = string.Empty;
 16 private string dataSource = string.Empty;
 17 private string userID = string.Empty;
 18 public mainFrom()
 19  {
 20  InitializeComponent();
 21 this.splitContainer1.Panel1.SizeChanged += new System.EventHandler(this.splitContainer1_Panel1_SizeChanged);
 22 this.toolStripStatusLabel1.Text = string.Format("系统当前时间：{0}", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
 23 this.timer1.Interval = 1000;
 24 this.timer1.Start();
 25 this.connectionString = "Data Source=XU_HAPPY_YOU-PC; Database={0};UID=sa;PWD=616888521;";
 26 this.dataSource = "XU_HAPPY_YOU-PC";
 27 this.userID = "sa";
 28  }
 29
 30 public mainFrom(string dataSource, string userID, string password)
 31  {
 32  InitializeComponent();
 33 this.splitContainer1.Panel1.SizeChanged += new System.EventHandler(this.splitContainer1_Panel1_SizeChanged);
 34 this.toolStripStatusLabel1.Text = string.Format("系统当前时间：{0}", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
 35 this.timer1.Interval = 1000;
 36 this.timer1.Start();
 37 this.connectionString = string.Format("Data Source={0}; Database={1};UID={2};PWD={3};", dataSource, "{0}", userID, password);
 38 this.dataSource = dataSource;
 39 this.userID = userID;
 40  }
 41
 42 private void mainFrom_Load(object sender, EventArgs e)
 43  {
 44 this.InitTreeView();
 45  }
 46
 47 private void InitTreeView()
 48  {
 49 IList<string> listDataBaseName = this.GetDataBaseNameList();
 50 this.treeViewDataBase.ImageList = this.GetImageList();
 51 this.treeViewDataBase.Nodes.Add("root", string.Format("{0}(DataBaseManager {1}))", this.dataSource, this.userID), "server");
 52 this.treeViewDataBase.Nodes[0].Nodes.Add("database", "DataBases", "directory", "directory");
 53 foreach (string dataBaseName in listDataBaseName)
 54  {
 55 this.treeViewDataBase.Nodes[0].Nodes[0].Nodes.Add(dataBaseName, dataBaseName, "database", "database");
 56  }
 57  }
 58
 59 /// <summary>
 60 /// get image list
 61 /// </summary>
 62 /// <returns></returns>
 63 private ImageList GetImageList()
 64  {
 65 ImageList myImageList = new ImageList();
 66 myImageList.Images.Add("server", global::DBManager.Properties.Resources.server);
 67 myImageList.Images.Add("directory", global::DBManager.Properties.Resources.directory);
 68 myImageList.Images.Add("database", global::DBManager.Properties.Resources.database);
 69 myImageList.Images.Add("table", global::DBManager.Properties.Resources.table);
 70 myImageList.Images.Add("column", global::DBManager.Properties.Resources.column);
 71 return myImageList;
 72  }
 73
 74 /// <summary>
 75 /// get database name list
 76 /// </summary>
 77 /// <returns></returns>
 78 private IList<string> GetDataBaseNameList()
 79  {
 80 IList<string> list = new List<string>();
 81 string strSql = "SELECT name FROM master..sysdatabases WHERE name NOT IN('master','model','msdb','tempdb','northwind','pubs')";
 82 SqlDataReader sdr = SQLHelper.ExecuteReader(this.connectionString,strSql, "master");
 83 while (sdr.Read())
 84  {
 85 list.Add(sdr[0].ToString());
 86  }
 87  sdr.Close();
 88 return list;
 89  }
 90
 91 /// <summary>
 92 /// get database table name
 93 /// </summary>
 94 /// <param name="dataBaseName"></param>
 95 /// <returns></returns>
 96 private IList<string> GetDataBaseTableNameList(string dataBaseName)
 97  {
 98 IList<string> list = new List<string>();
 99 string strSql = "SELECT name FROM sysobjects WHERE xtype='U' AND name NOT IN('sysdiagrams') AND status>=0 ORDER BY name";
100 SqlDataReader sdr = SQLHelper.ExecuteReader(this.connectionString,strSql, dataBaseName);
101 while (sdr.Read())
102  {
103 list.Add(sdr[0].ToString());
104  }
105  sdr.Close();
106 return list;
107  }
108
109 /// <summary>
110 /// get field info
111 /// </summary>
112 /// <param name="tableName"></param>
113 /// <param name="dataBaseName"></param>
114 /// <returns></returns>
115 private DataTable GetFieldInfoDataTable(string tableName, string dataBaseName)
116  {
117 StringBuilder strSql = new StringBuilder("SELECT sc.name fieldName,");
118 strSql.Append("st.name typeName,st.length length ");
119 strSql.Append("FROM syscolumns sc LEFT JOIN systypes st ");
120 strSql.Append("ON sc.xtype=st.xtype ");
121 strSql.Append("WHERE st.name NOT IN('sysname') ");
122 strSql.AppendFormat("AND sc.id=object_id('{0}')", tableName);
123 return SQLHelper.GetDataTable(this.connectionString,strSql.ToString(), dataBaseName);
124  }
125
126 private IList<string> GetFieldInfoList(string tableName, string dataBaseName)
127  {
128 IList<string> list = new List<string>();
129 DataTable dt = this.GetFieldInfoDataTable(tableName, dataBaseName);
130 for (int i = 0; i < dt.Rows.Count; i++)
131  {
132 list.Add(string.Format("{0}({1}({2}))", dt.Rows[i]["fieldName"].ToString(), dt.Rows[i]["typeName"].ToString(), dt.Rows[i]["length"].ToString()));
133  }
134 return list;
135  }
136
137 /// <summary>
138 /// node click
139 /// </summary>
140 /// <param name="sender"></param>
141 /// <param name="e"></param>
142 private void treeViewDataBase_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
143  {
144 if (e.Node.Level == 2)
145  {
146 IList<string> list = this.GetDataBaseTableNameList(e.Node.Text);
147 this.treeViewDataBase.Nodes[0].Nodes[0].Nodes[e.Node.Index].Nodes.Clear();
148 foreach (string tableName in list)
149  {
150 this.treeViewDataBase.Nodes[0].Nodes[0].Nodes[e.Node.Index].Nodes.Add(tableName, tableName, "table", "table");
151  }
152  }
153 else if (e.Node.Level == 3)
154  {
155 IList<string> list = this.GetFieldInfoList(e.Node.Text, e.Node.Parent.Text);
156 this.treeViewDataBase.Nodes[0].Nodes[0].Nodes[e.Node.Parent.Index].Nodes[e.Node.Index].Nodes.Clear();
157 foreach (string fieldInfo in list)
158  {
159 this.treeViewDataBase.Nodes[0].Nodes[0].Nodes[e.Node.Parent.Index].Nodes[e.Node.Index].Nodes.Add(fieldInfo, fieldInfo, "column", "column");
160  }
161  }
162  }
163
164 private void splitContainer1_Panel1_SizeChanged(object sender, EventArgs e)
165  {
166 this.treeViewDataBase.Width = this.splitContainer1.Panel1.Width;
167  }
168
169 private void timer1_Tick(object sender, EventArgs e)
170  {
171 this.toolStripStatusLabel1.Text = string.Format("系统当前时间：{0}", DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss"));
172  }
173
174 /// <summary>
175 /// execute
176 /// </summary>
177 /// <param name="sender"></param>
178 /// <param name="e"></param>
179 private void toolStripButton1_Click(object sender, EventArgs e)
180  {
181 string dataBaseName = this.GetDataBaseName(this._selectednode);
182 string strSql = this.txtSql.Text.Trim();
183 if (string.IsNullOrEmpty(strSql))
184  {
185 MessageBox.Show("Please input SQL!");
186  }
187 else
188  {
189 string errorMsg=string.Empty;
190 if (SQLHelper.CheckSQL(this.connectionString,strSql, dataBaseName, out errorMsg))
191  {
192 try
193  {
194 DataTable dt = SQLHelper.GetDataTable(this.connectionString,strSql, dataBaseName);
195 this.dataGVSQL.DataSource = dt;
196 this.toolStripStatusLabel2.Visible = true;
197 this.toolStripStatusLabel2.Image = global::DBManager.Properties.Resources.smallsuccess;
198 this.toolStripStatusLabel2.Text = "Query execulted successfully.";
199 this.toolStripStatuslblTotal.Visible = true;
200 this.toolStripStatuslblTotal.Text = string.Format(" Total:{0}", dt.Rows.Count);
201  }
202 catch (Exception)
203  {
204 DialogResult dr = MessageBox.Show("SQL Execute error!", "Error", MessageBoxButtons.YesNo, MessageBoxIcon.Error);
205 this.toolStripStatusLabel2.Visible = true;
206 this.toolStripStatusLabel2.Image = global::DBManager.Properties.Resources.smallfail;
207 this.toolStripStatusLabel2.Text = "Query completed with errors.";
208 if (dr == DialogResult.Yes || dr == DialogResult.No)
209  {
210 this.Close();
211  Application.Exit();
212  }
213  }
214  }
215 else
216  {
217 this.toolStripStatusLabel2.Visible = true;
218 this.toolStripStatusLabel2.Image = global::DBManager.Properties.Resources.smallfail;
219 this.toolStripStatusLabel2.Text = "Query completed with errors.";
220  MessageBox.Show(errorMsg);
221  }
222  }
223  }
224
225 /// <summary>
226 /// get database name
227 /// </summary>
228 /// <param name="level"></param>
229 /// <returns></returns>
230 private string GetDataBaseName(TreeNode node)
231  {
232 string dataBaseName = string.Empty;
233 switch (node.Level)
234  {
235 case 2:
236 dataBaseName = node.Text;
237 break;
238 case 3:
239 dataBaseName = node.Parent.Text;
240 break;
241 case 4:
242 dataBaseName = node.Parent.Parent.Text;
243 break;
244 default:
245 break;
246  }
247 return dataBaseName;
248  }
249
250 #region Drag
251 //定义被选择的项
252 private TreeNode _selectednode;
253 private void treeViewDataBase_AfterSelect(object sender, TreeViewEventArgs e)
254  {
255 try
256  {
257 _selectednode = e.Node;
258  }
259 catch { }
260  }
261 /// <summary>
262 /// 对选中项指定Move的“开始拖动操作”
263 /// </summary>
264 /// <param name="sender"></param>
265 /// <param name="e"></param>
266 private void treeViewDataBase_ItemDrag(object sender, ItemDragEventArgs e)
267  {
268 if (!(this._selectednode.Level == 0 || this._selectednode.Level == 1))
269  {
270 this.DoDragDrop(this._selectednode, DragDropEffects.Move);
271  }
272  }
273 /// <summary>
274 /// 添加MouseDown是因为,MouseDown先执行AfterSelect，所以在没有AfterSelect时，MouseDown要先选中被选取项
275 /// </summary>
276 /// <param name="sender"></param>
277 /// <param name="e"></param>
278 private void treeViewDataBase_MouseDown(object sender, MouseEventArgs e)
279  {
280 try
281  {
282 if ((e.Button & MouseButtons.Left) == MouseButtons.Left)
283  {
284 this._selectednode = this.treeViewDataBase.GetNodeAt(e.X, e.Y);
285 this.treeViewDataBase.SelectedNode = this._selectednode;
286  }
287  }
288 catch { }
289  }
290 private void textSql_DragEnter(object sender, DragEventArgs e)
291  {
292 try
293  {
294 TreeNode node = (TreeNode)e.Data.GetData(typeof(TreeNode));
295 if (node != null)
296  {
297 e.Effect = DragDropEffects.Move;
298  }
299 else
300  {
301 Cursor = Cursors.No;
302  }
303  }
304 catch { }
305 finally { Cursor = Cursors.Default; }
306  }
307 private void textSql_DragDrop(object sender, DragEventArgs e)
308  {
309 try
310  {
311 TreeNode node = (TreeNode)e.Data.GetData(typeof(TreeNode));
312 if (node != null)
313  {
314 if (!(node.Level == 0 || node.Level == 1))
315  {
316 if (node.Level == 4)
317  {
318 this.txtSql.Text += " " + node.Text.Substring(0, node.Text.IndexOf('('));
319  }
320 else
321  {
322 this.txtSql.Text += " " + node.Text;
323  }
324  }
325  }
326 else
327  {
328 Cursor = Cursors.No;
329  }
330  }
331 catch { }
332 finally { Cursor = Cursors.Default; }
333  }
334 #endregion
335
336 /// <summary>
337 /// 给第一列数据加上索引
338 /// </summary>
339 /// <param name="sender"></param>
340 /// <param name="e"></param>
341 private void dataGVSQL_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
342  {
343 Rectangle rectangle = new Rectangle(e.RowBounds.Location.X,
344  e.RowBounds.Location.Y,
345 this.dataGVSQL.RowHeadersWidth - 4,
346  e.RowBounds.Height);
347 TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
348 this.dataGVSQL.RowHeadersDefaultCellStyle.Font,
349  rectangle,
350 this.dataGVSQL.RowHeadersDefaultCellStyle.ForeColor,
351 TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
352  }
353  }
354 }

由于时间仓促，可能有一些问题，请不要见怪，谢谢！

下载地址：下载地址1 下载地址2

一切伟大的行动和思想，都有一个微不足道的开始。微不足道的我，正在吸取知识的土壤，希望能取得成功！不嫌弃我微不足道的，愿交天下好友！

posted @ 2012-04-13 20:05 xu_happy_you 阅读(7060) 评论(27) 编辑收藏举报

刷新页面返回顶部

我的简易SQL查询分析器

公告