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 + " 条记录"; } } }