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 Cyberpipe
{
public partial class FrmQuerySQL : 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;
static FrmQuerySQL frm;
public static void ShowForm(GSOGlobeControl _ctl, DataGridViewX _dgv, string userName, List<string> newLayer1)
{
if (frm == null)
{
frm = new FrmQuerySQL(_ctl, _dgv, userName, newLayer1);
frm.Show(_ctl.Parent);
}
else
{
if (frm.WindowState == FormWindowState.Minimized)
{
frm.WindowState = FormWindowState.Normal;
}
}
}
public FrmQuerySQL(GSOGlobeControl _ctl, DataGridViewX _dgv,string userName,List<string> newLayer1)
{
InitializeComponent();
m_globeControl = _ctl;
m_dataGridViewX = _dgv;
m_dataGridViewX.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
//DataGridViewAutoSizeColumnsModedataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
username = userName;
newLayer = newLayer1;
}
#region 符号
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 += " *";
}
#endregion
/// <summary>
/// 清空按钮事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Clear_Click(object sender, EventArgs e)
{
textBox_WhereClause.Clear();//清空条件
}
/// <summary>
/// 窗体初始化事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Frm_QuerySQL_Load(object sender, EventArgs e)
{
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("\\") && !name.Contains("/"))
{
cbox_Layers.Items.Add(layer.Caption);
}
}
}
foreach (string field in newLayer)
{
cbox_Layers.Items.Add(field);
}
cbox_Layers.SelectedIndex = 0;
}
//获取唯一值添加到listbox_Value中
string str1;
string str2;
/// <summary>
/// 获取唯一值按钮事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
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 +" 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;
}
private void listBox_Value_DoubleClick(object sender, EventArgs e)
{
textBox_WhereClause.Text += " " + listBox_Value.Text;
}
/// <summary>
/// 应用按钮事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Apply_Click(object sender, EventArgs e)
{
m_dataGridViewX.Visible = true;
string fields = "";
int itemCount = listBox_Field.Items.Count;
for (int i = 0; i < itemCount; i++)
{
string fieldName = listBox_Field.Items[i]+"";
if (fieldName.Contains("高程") || fieldName.Contains("埋深")) {
fieldName = "round(" + fieldName + ",2) as " + fieldName + "_米";
}
if (i < itemCount - 1)
{
fields += fieldName + ",";
}
else
{
fields += fieldName;
}
}
sql = "select " + fields + " from " + ds.Name + "";
if (textBox_WhereClause.Text != "")
{
sql += " where " + textBox_WhereClause.Text;
}
try
{
this.Cursor = Cursors.WaitCursor;
DataTable table = OledbHelper.QueryTable(sql);
if (table == null)
{
this.Cursor = Cursors.Default;
MessageBox.Show("sql语句不合法或者没有查询到符合条件的数据！","提示");
return;
}
currentLayerName = cbox_Layers.SelectedItem.ToString();
m_dataGridViewX.DataSource = table;
//m_dataGridViewX.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
MainFrm frm = (MainFrm)this.Owner;
//显示表格
frm.expandableSplitter2.Expanded = true;
frm.panelOfTable.Visible = true;
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.m_CurrentQueryLayer = currentLayerName;
}
else
{
frm.statusStrip2.Items[0].Text = " 管线类型： " + currentLayerName + "|| 共有：" + 0 + " 条记录 || ";
}
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return;
}
}
/// <summary>
/// 取消按钮事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_Cancel_Click(object sender, EventArgs e)
{
this.Close();
}
GSOFeatureDataset sourcefDataset;
/// <summary>
/// 图层下拉框选中项改变事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cbox_Layers_SelectedIndexChanged(object sender, EventArgs e)
{
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_Value.Items.Clear();
}
/// <summary>
/// 列表项单击事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void listBox_Field_Click(object sender, EventArgs e)
{
btn_GetValue_Click(sender, e);
}
/// <summary>
/// 窗体关闭事件处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Frm_QuerySQL_FormClosing(object sender, FormClosingEventArgs e)
{
MainFrm mfrm = (MainFrm)this.Owner;
mfrm.panelOfTable.Visible = false;
mfrm.statusStrip2.Items[0].Text = " 类型：";
mfrm.statusStrip2.Items[1].Text = " ";
m_dataGridViewX.DataSource = null;
m_dataGridViewX.Refresh();
frm = null;
}
}
}
