using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using GeoScene.Globe;
using DevComponents.DotNetBar.Controls;
using GeoScene.Engine;
using GeoScene.Data;
using System.Collections;
namespace PipeLine.Forms
{
public partial class Frm_QuerySQL : DevComponents.DotNetBar.Office2007Form
{
private GSOGlobeControl m_globeControl;
private DataGridViewX m_dataGridViewX;
private GSODataset ds;
string sql;
List<string> newLayer;
public string currentLayerName;
//List<string> list = new List<string>(){"热力管线", "排水管线", "给水管线", "燃气管线", "电力管线", "通信管线", "热力阀门", "给水阀门", "热力工井", "给水工井", "排水工井", "燃气工井", "电力工井", "通信工井","给水表"};
private string username;
public Frm_QuerySQL(GSOGlobeControl _ctl, DataGridViewX _dgv,string userName,List<string> newLayer1)
{
InitializeComponent();
m_globeControl = _ctl;
m_dataGridViewX = _dgv;
username = userName;
newLayer = newLayer1;
}
private void btn_equal_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " =";
}
private void btn_Notequal_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " <>";
}
private void btn_Big_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " >";
}
private void btn_BigEqual_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " >=";
}
private void btn_Small_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " <";
}
private void btn_Smallequal_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " <=";
}
private void btn_Brace_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " ()";
}
private void btn_Like_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " Like";
}
private void btn_And_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " AND";
}
private void btn_Or_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " OR";
}
private void btn_IS_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " IS";
}
private void btn_Not_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " NOT";
}
private void btn_What_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " ?";
}
private void btn_All_Click(object sender, EventArgs e)
{
textBox_WhereClause.Text += " *";
}
private void btn_Clear_Click(object sender, EventArgs e)
{
textBox_WhereClause.Clear();//清空条件
}
//添加字段到
private void Frm_QuerySQL_Load(object sender, EventArgs e)
{
//cbox_Layers.Items.Clear();
//for (int i = 0; i < m_globeControl.Globe.Layers.Count; i++)//获取所有图层
//{
// GSOLayer layer = m_globeControl.Globe.Layers[i];
// cbox_Layers.Items.Add(layer.Caption);
//}
//cbox_Layers.DataSource = list;
for (int i = 0; i < m_globeControl.Globe.Layers.Count; i++)//获取树节点后中所有的layer图层
{
GSOLayer layer = m_globeControl.Globe.Layers[i];
if (layer != null && layer.Dataset != null && layer.Dataset.IsFeatureDataset)
{
string name = layer.Name;
if (layer.Type == EnumLayerType.FeatureLayer && !name.Contains("\\"))
{
cbox_Layers.Items.Add(layer.Caption);
}
}
}
//foreach (string field1 in list)
//{
// cbox_Layers.Items.Add(field1);
//}
foreach (string field in newLayer)
{
cbox_Layers.Items.Add(field);
}
cbox_Layers.SelectedIndex = 0;
string queryByusername = "select sqlname from querySql where username='"+username+"'";
DataTable dt = OledbHelper.ExecuteDataTable(queryByusername);
foreach (DataRow row in dt.Rows)
{
comboBox1.Items.Add(row[0].ToString());
}
//foreach (object temp_record in arrList_Record)//获取记录
//{
// comboBox1.Items.Add(temp_record.ToString());
//}
}
//获取唯一值添加到listbox_Value中
string str1;
string str2;
private void btn_GetValue_Click(object sender, EventArgs e)
{
try
{
listBox_Value.Items.Clear();
for (int j = 0; j < sourcefDataset.FieldCount; j++)
{
GSOFieldAttr fieldef = sourcefDataset.GetField(j);
if (listBox_Field.SelectedItem.ToString() == fieldef.Name && fieldef.Type == EnumFieldType.Text)
{
str1 = "'";
str2 = "'";
}
}
string sqltype = "select distinct " + listBox_Field.SelectedItem.ToString() + " from " + ds.Name.ToString() +" order by "+listBox_Field.SelectedItem.ToString()+"";
DataTable table = OledbHelper.QueryTable(sqltype);
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow dr = table.Rows[i];
string colString1 = dr[0].ToString();
if (colString1 == null || colString1.Trim() == "")
{
continue;
}
string col = str1 + colString1 + str2;
listBox_Value.Items.Add(col);
}
str1 = "";
str2 = "";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//双击添加到查询列中
private void listBox_Field_DoubleClick(object sender, EventArgs e)
{
textBox_WhereClause.Text += " " + listBox_Field.Text;
btn_GetValue_Click(sender, e);
}
private void listBox_Value_DoubleClick(object sender, EventArgs e)
{
textBox_WhereClause.Text += " " + listBox_Value.Text;
}
//开始查询
private void btn_Apply_Click(object sender, EventArgs e)
{
m_dataGridViewX.Visible = true;
if (textBox_WhereClause.Text != "")
{
string fields = "";
for (int i = 0; i < listBox_Field.Items.Count; i++)
{
if (i < listBox_Field.Items.Count - 1)
{
fields += listBox_Field.Items[i] + ",";
}
else
fields += listBox_Field.Items[i];
}
sql = "select " + fields + " from " + ds.Name.ToString() + "";
//switch (ds.Name.ToString())
//{
// //sql条件，判断管线类型
// case "热力管线":
// sql = "select " + Utility.Query_Fields["热力"] + " from 热力管线";
// break;
// case "排水管线":
// sql = "select " + Utility.Query_Fields["排水"] + " from 排水管线";
// break;
// case "给水管线":
// sql = "select " + Utility.Query_Fields["给水"] + " from 给水管线";
// break;
// case "电力管线":
// sql = "select " + Utility.Query_Fields["电力"] + " from 电力管线";
// break;
// case "通信管线":
// sql = "select " + Utility.Query_Fields["通信"] + " from 通信管线";
// break;
// case "燃气管线":
// sql = "select " + Utility.Query_Fields["燃气"] + " from 燃气管线";
// break;
// case "热力阀门":
// sql = "select " + Utility.Query_Fields["阀门"] + " from 热力阀门";
// break;
// case "给水阀门":
// sql = "select " + Utility.Query_Fields["阀门"] + " from 给水阀门";
// break;
// case "电力工井":
// sql = "select " + Utility.Query_Fields["电力工井"] + " from 电力工井";
// break;
// case "燃气工井":
// sql = "select " + Utility.Query_Fields["燃气工井"] + " from 燃气工井";
// break;
// case "热力工井":
// sql = "select " + Utility.Query_Fields["热力工井"] + " from 热力工井";
// break;
// case "排水工井":
// sql = "select " + Utility.Query_Fields["排水工井"] + " from 排水工井";
// break;
// case "给水工井":
// sql = "select " + Utility.Query_Fields["给水工井"] + " from 给水工井";
// break;
// case "通信工井":
// sql = "select " + Utility.Query_Fields["通信工井"] + " from 通信工井";
// break;
//}
}
if (textBox_WhereClause.Text != "")
{
sql += " where " + textBox_WhereClause.Text;
//arrList_Record.Add(sql);//存储查询记录内容
try
{
DataTable table = OledbHelper.QueryTable(sql);
if (table == null)
{
return;
}
if (txtSqlName.Text != "")
{
string sql1 = sql.Replace("'", "|");
string isExistsql = "select count(*) from querySql where sqlname='" + txtSqlName.Text + "'";
int count = OledbHelper.ExecuteScalar(isExistsql);
if (count == 0)
{
string querysql = "insert into querySql(username,querysql,sqlname) values('" + username + "','" + sql1 + "','" + txtSqlName.Text + "')";
OledbHelper.sqlExecuteNonQuery(querysql);
}
else
{
MessageBox.Show("输入别名已存在，请重新输入！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
}
}
catch (Exception ex)
{
LogError.PublishError(ex);
}
}
try
{
DataTable table = OledbHelper.QueryTable(sql);
if (table == null)
{
return;
}
int start = sql.IndexOf("from") + 4;
int end = sql.IndexOf("where");
currentLayerName = sql.Substring(start, end - start).Trim();
m_dataGridViewX.DataSource = table;
m_dataGridViewX.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
MainFrm frm = (MainFrm)this.Owner;
int count = m_dataGridViewX.Rows.Count;
if (count != 0)
{
if (currentLayerName.Contains("管线"))
{
double featurelength = frm.PipeLength(currentLayerName, 0);// 统计管线的里程数
frm.statusStrip2.Items[0].Text = " 管线类型：" + currentLayerName + " | 共有：" + Convert.ToString(count) + "条记录";
frm.statusStrip2.Items[1].Text = " 管线里程：" + featurelength.ToString("0.00") + " 米";
}
else
{
frm.statusStrip2.Items[0].Text = " 类型：" + currentLayerName + " | 共有：" + Convert.ToString(count) + "条记录"; ;
frm.statusStrip2.Items[1].Text = " ";
}
MainFrm.currentQueryLayer = currentLayerName;
}
else
{
frm.statusStrip2.Items[0].Text = " 管线类型： " + currentLayerName + "|| 共有：" + 0 + " 条记录 || ";
}
frm.expandableSplitter2.Expanded = true;
frm.panelEx6.Visible = true;
}
catch (Exception ex)
{
LogError.PublishError(ex);
return;
}
}
private void btn_Cancel_Click(object sender, EventArgs e)
{
this.Close();
}
GSOFeatureDataset sourcefDataset;
private void cbox_Layers_SelectedIndexChanged(object sender, EventArgs e)
{
//GSOLayer m_layer = m_globeControl.Globe.Layers.GetLayerByID((int)(Utility.LayerLabel_LayerIDs[cbox_Layers.SelectedItem.ToString().Trim()]));//获取当前选择的layer图层
listBox_Field.Items.Clear();
GSOLayer m_layer = m_globeControl.Globe.Layers.GetLayerByCaption(cbox_Layers.SelectedItem.ToString().Trim());//获取当前选择的layer图层
if (m_layer == null)
return;
GSOFeatureLayer flayer = m_layer as GSOFeatureLayer;
ds = m_layer.Dataset as GSODataset;
sourcefDataset = ds as GSOFeatureDataset;
sourcefDataset.Open();
for (int j = 0; j < sourcefDataset.FieldCount; j++)
{
GSOFieldAttr fieldef = sourcefDataset.GetField(j);
listBox_Field.Items.Add(fieldef.Name);
}
//设置当前选择字段为第一个
listBox_Field.SelectedIndex = 0;
//将描述信息修改
label3.Text = "SELECT * FROM " + ds.Name.ToString() + " WHERE:";
//currentLayerName = cbox_Layers.SelectedItem.ToString();
}
private void button1_Click(object sender, EventArgs e)//清空存储记录
{
if (comboBox1.SelectedItem != null)
{
string deleteSql = comboBox1.SelectedItem.ToString();
deleteSql = deleteSql.Replace("'", "|");
string delsql = "delete from querySql where sqlname='" + deleteSql + "'";
try
{
OledbHelper.sqlExecuteNonQuery(delsql);
comboBox1.Items.RemoveAt(comboBox1.SelectedIndex);
richTextBox1.Text = "";
MessageBox.Show("删除完成！！");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
if (comboBox1.SelectedItem != null)
{
string selSql = "select querysql from querySql where sqlname='"+comboBox1.SelectedItem.ToString()+"'";
try
{
DataTable dt = OledbHelper.ExecuteDataTable(selSql);
foreach (DataRow row in dt.Rows)
{
sql = row[0].ToString().Replace("|", "'");
string last ="select * "+ row[0].ToString().Substring(row[0].ToString().IndexOf("from"));
richTextBox1.Text = last.Replace("|", "'");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
}
private void Frm_QuerySQL_HelpButtonClicked(object sender, CancelEventArgs e)
{
Help.ShowHelp((MainFrm)this.Owner, Application.StartupPath + "/北京建筑大学“地下管网信息管理系统”用户使用手册.chm", "/47.html");
}
}
}
