private void Frm_LogManager_Load(object sender, EventArgs e)
{
dataGridView1.ReadOnly = true;
this.datetime1.Checked = false;
this.datetime2.Checked = false;
comboBoxDataSourceList.Items.Clear();
foreach (DatabaseConnectParams connectParams in Utility.connectParamsOfDatabase)
{
if (connectParams != null && (connectParams.databaseType == EnumDataSourceType.SqlServer || connectParams.databaseType == EnumDataSourceType.Oracle))
{
comboBoxDataSourceList.Items.Add(connectParams.dataSourceFullName);
}
}
if (comboBoxDataSourceList.Items.Count > 0)
{
comboBoxDataSourceList.SelectedIndex = 0;
connectParams = Utility.getConnectParamsByDatasourceFullName(globeControl1, comboBoxDataSourceList.SelectedItem.ToString().Trim());
if (connectParams != null)
{
string sql = "use " + connectParams.databaseName + " select * from 日志管理;";
DataTable table = OledbHelper.QueryTable(sql, connectParams);
if (table == null)
{
MessageBox.Show("数据库中没有日志信息！", "提示");
return;
}
dataGridView1.DataSource = table;
}
}
}
public static int ExecuteScalar(string sql, DatabaseConnectParams connectParams)
{
SqlConnection conn = OledbHelper.getSqlConnection(connectParams);
if (conn == null)
{
return(0);
}
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = sql;
int num = 0;
try
{
conn.Open();
num = Convert.ToInt32(sqlCmd.ExecuteScalar().ToString());
}
catch (Exception ex)
{
Log.PublishTxt(ex);
num = -1;
MessageBox.Show(ex.Message, "提示");
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(num);
}
public static int sqlExecuteNonQuery(string sql, DatabaseConnectParams connectParams)
{
SqlConnection conn = OledbHelper.getSqlConnection(connectParams);
if (conn == null)
{
return(0);
}
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = sql;
int rowCount = 0;
try
{
conn.Open();
rowCount = sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message, "提示");
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(rowCount);
}
//查询
private void btn_Query_Click(object sender, EventArgs e)
{
if (comboBoxDataSourceList.SelectedItem == null)
{
MessageBox.Show("请选择一个目标数据源！", "提示");
return;
}
if (connectParams == null)
{
MessageBox.Show("选择的目标数据源为空！", "提示");
return;
}
string sql = "use " + connectParams.databaseName + " select * from 日志管理";
ArrayList filters = new ArrayList();
if (datetime1.Checked == true || datetime2.Checked == true)
{
if (datetime1.Checked == true && datetime2.Checked == false)
{
string sql2 = " 异常日期>='" + datetime1.Value.ToString("yyyy-MM-dd") + "'";
filters.Add(sql2);
}
else if (datetime2.Checked == true && datetime1.Checked == false)
{
string sql3 = " 异常日期<='" + datetime2.Value.ToString("yyyy-MM-dd") + "'";
filters.Add(sql3);
}
else
{
string sql4 = " 异常日期 between '" + datetime1.Value.ToString("yyyy-MM-dd") + "' and '" + datetime2.Value.ToString("yyyy-MM-dd") + "'";
filters.Add(sql4);
}
}
string[] aa = (string[])(filters.ToArray(typeof(string)));
if (aa.Length > 0)
{
sql += " where " + string.Join(" and ", aa);
}
try
{
DataTable table = OledbHelper.QueryTable(sql, connectParams);
if (table != null)
{
dataGridView1.DataSource = table;
dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
}
else
{
MessageBox.Show("没有找到任何数据！", "提示");
}
}
catch (Exception ex)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message);
}
}
private bool backupDB()
{
DatabaseConnectParams connectParams = Utility.getConnectParamsByDatasourceFullName(globeControl1, comboBoxDataSourceList.SelectedItem.ToString().Trim());
if (connectParams == null)
{
return(false);
}
conn = OledbHelper.getSqlConnection(connectParams);
SqlCommand cmdBK = new SqlCommand();
cmdBK.CommandType = CommandType.Text;
cmdBK.Connection = conn;
DateTime currentTime = DateTime.Now;
string Dtime = currentTime.GetDateTimeFormats('D')[0].ToString();
string Htime = DateTime.Now.ToString("HH时mm分ss秒").Trim();
string fileName = Dtime + "(" + Htime + ")";
string DBBackupPath = Application.StartupPath + "\\" + comboBoxDataSourceList.SelectedItem.ToString().Trim();
if (Directory.Exists(DBBackupPath) == false)
{
Directory.CreateDirectory(DBBackupPath);
}
string pathName = DBBackupPath + "\\" + fileName + ".bak";
string databaseName = connectParams.databaseName;
cmdBK.CommandText = "backup database " + databaseName + " to disk='" + pathName + "' with init";
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
cmdBK.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message, "提示");
return(false);
}
finally
{
conn.Close();
}
}
private void btn_Apply_Click(object sender, EventArgs e)
{
string caption = cbox_Layers.Text;
if (caption == "")
{
MessageBox.Show("请先添加图层", "提示");
return;
}
GSOLayer layer = globeControl1.Globe.Layers.GetLayerByCaption(caption);
if (layer == null || connectParams == null)
{
return;
}
string sql = "select * from " + cbox_Layers.Text.Trim();
if (textBox_WhereClause.Text != "")
{
sql += " where " + textBox_WhereClause.Text;
try
{
DataTable table = OledbHelper.QueryTable(sql, connectParams);
if (table == null)
{
return;
}
for (int i = 0; i < 3; i++)
{
table.Columns.RemoveAt(0);
}
frm_editor = FrmShowFeatureAttributesByTable.GetForm(table, layer, globeControl1);
frm_editor.SetDataTable();
if (!frm_editor.isShowFirst)
{
frm_editor.Show(this);
}
}
catch (Exception ex)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message, "提示");
return;
}
}
}
private void btn_GetValue_Click(object sender, EventArgs e)
{
if (connectParams == null)
{
return;
}
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 " + sourcefDataset.Name + " order by " + listBox_Field.SelectedItem.ToString() + "";
DataTable table = OledbHelper.QueryTable(sqltype, connectParams);
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)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message);
}
}
public static DataTable QueryTable(string cmdText, DatabaseConnectParams connectParams)
{
SqlConnection conn = OledbHelper.getSqlConnection(connectParams);
if (conn == null)
{
return(null);
}
try
{
conn.Open();
SqlCommand sqlCmd = conn.CreateCommand();
sqlCmd.CommandText = cmdText;
SqlDataReader sqlReader = sqlCmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(sqlReader);
sqlReader.Close();
conn.Close();
return(table);
}
catch (Exception ex)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message, "提示");
return(null);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
private void buttonDelete_Click(object sender, EventArgs e)
{
string filter = "";
int selectedRowCount = dataGridView1.SelectedRows.Count;
if (selectedRowCount <= 0)
{
MessageBox.Show("请选中要删除的行", "提示");
return;
}
if (connectParams == null)
{
MessageBox.Show("选择的目标数据源为空！", "提示");
return;
}
ArrayList list = new ArrayList();
for (int i = selectedRowCount - 1; i >= 0; i--)
{
string datetime = dataGridView1.SelectedRows[i].Cells["异常日期"].Value.ToString().Trim();
filter += "'" + datetime + "',";
list.Add(dataGridView1.SelectedRows[i]);
}
filter = filter.Substring(0, filter.Length - 1);
string sql = "use " + connectParams.databaseName + " delete from 日志管理 where 异常日期 in (" + filter + ");";
int countDeleted = OledbHelper.sqlExecuteNonQuery(sql, connectParams);
if (countDeleted != 0)
{
for (int i = list.Count - 1; i >= 0; i--)
{
dataGridView1.Rows.Remove(dataGridView1.SelectedRows[i]);
}
}
MessageBox.Show("共删除" + countDeleted.ToString() + "条记录!", "提示");
}
//开始查询
private void btn_Apply_Click(object sender, EventArgs e)
{
string caption = cbox_Layers.Text;
if (caption == "")
{
MessageBox.Show("请先添加图层", "提示");
return;
}
GSOLayer layer = globeControl1.Globe.Layers.GetLayerByCaption(caption);
if (layer == null)
{
return;
}
if (!checkBox1.Checked)
{
GSOFeatures features = layer.GetAllFeatures();
for (int i = 0; i < features.Length; i++)
{
GSOFeature newfeature = features[i];
GSOGeoPolyline3D line = newfeature.Geometry as GSOGeoPolyline3D;
if (line == null)
{
continue;
}
GSOPipeLineStyle3D pipeLineStyle = line.Style as GSOPipeLineStyle3D;
// 管线暂不支持依地模式
if (pipeLineStyle == null)
{
pipeLineStyle = new GSOPipeLineStyle3D();
}
pipeLineStyle.LineColor = buttonPipelineColor.BackColor;
line.Style = pipeLineStyle;
}
}
else
{
string sql = "select * from " + cbox_Layers.Text.Trim();
if (textBox_WhereClause.Text != "")
{
sql += " where " + textBox_WhereClause.Text;
try
{
if (layer.Dataset == null || layer.Dataset.DataSource == null ||
(layer.Dataset.DataSource.Type != EnumDataSourceType.SqlServer && layer.Dataset.DataSource.Type != EnumDataSourceType.Oracle))
{
return;
}
DatabaseConnectParams connectParams = Utility.getConnectParamsByDatasourceName(globeControl1, layer.Dataset.DataSource.Name);
if (connectParams == null)
{
return;
}
DataTable table = OledbHelper.QueryTable(sql, connectParams);
if (table == null)
{
return;
}
for (int i = 0; i < table.Rows.Count; i++)
{
int id = Convert.ToInt32(table.Rows[i]["LSSYS_ID"]);
GSOFeature newfeature = layer.GetFeatureByID(id);
GSOGeoPolyline3D line = newfeature.Geometry as GSOGeoPolyline3D;
if (line == null)
{
continue;
}
GSOPipeLineStyle3D pipeLineStyle = line.Style as GSOPipeLineStyle3D;
// 管线暂不支持依地模式
if (pipeLineStyle == null)
{
pipeLineStyle = new GSOPipeLineStyle3D();
}
pipeLineStyle.LineColor = buttonPipelineColor.BackColor;
line.Style = pipeLineStyle;
}
}
catch (Exception ex)
{
Log.PublishTxt(ex);
MessageBox.Show(ex.Message, "提示");
return;
}
}
}
globeControl1.Refresh();
return;
}
private void btnEnter_Click(object sender, EventArgs e)
{
int n = 0;
layer = globeControl1.Globe.Layers.GetLayerByCaption(cmbPipeShow.Text.Trim());
GSOFeatureLayer flayer = layer as GSOFeatureLayer;
if (flayer == null)
{
return;
}
GSOFeatureDataset fdataset = flayer.Dataset as GSOFeatureDataset;
if (fdataset == null || fdataset.DataSource == null)
{
return;
}
connectParams = Utility.getConnectParamsByDatasourceName(globeControl1, fdataset.DataSource.Name);
if (connectParams == null)
{
return;
}
GSOFeatures feats = flayer.GetAllFeatures();
string type = layer.Caption.Substring(0, 2);
string sql = "select * " + " from " + layer.Caption + " where";
for (int i = 0; i < feats.Length; i++)
{
GSOFeature f = feats[i];
if (f.Geometry.Type == EnumGeometryType.GeoPolyline3D)
{
GSOGeoPolyline3D line = f.Geometry as GSOGeoPolyline3D;
GSOPipeLineStyle3D style = line.Style as GSOPipeLineStyle3D;
if (style != null)
{
GSOPoint3ds pnts = getAllPointInPipeline(line);
for (int j = 0; j < pnts.Count; j++)
{
GSOPoint3d pt = pnts[j];
if (Math.Abs(pt.Z) < style.Radius)
{
n++;
sql += " 编号='" + f.Name + "' or ";
break;
}
}
}
}
}
if (n > 0)
{
sql = sql.Substring(0, sql.Length - 3);
DataTable dt = new DataTable();
dt = OledbHelper.QueryTable(sql, connectParams);
if (dt == null)
{
MessageBox.Show("没有地上管线！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (dt.Columns.Count > 3)
{
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(0);
dt.Columns.RemoveAt(0);
}
dataGridView1.ReadOnly = !layer.Editable;
dataGridView1.DataSource = dt;
if (statusStrip1.Items.Count > 0)
{
statusStrip1.Items[0].Text = " 共有 " + dt.Rows.Count + " 条记录";
}
globeControl1.Globe.Refresh();
}
}
else
{
MessageBox.Show("没有地上管线！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
dataGridView1.DataSource = null;
if (statusStrip1.Items.Count > 0)
{
statusStrip1.Items[0].Text = " 共有 " + 0 + " 条记录";
}
}
}
