private void FormMainOrcl_Load(object sender, EventArgs e) { DataTable dt = (DbType == "Oracle")?DBconnectionOracle.ShowTableList():DBconnectionSQLServer.ShowTableList(DbName); foreach (DataRow row in dt.Rows) { int index = TableView.Rows.Add(); TableView.Rows[index].Cells[0].Value = (DbType == "Oracle") ? row["table_name"].ToString():row["name"].ToString(); } if (TableView.CurrentCell != null && TableView.CurrentCell.Value != null) { DataTable ds = (DbType == "Oracle") ? DBconnectionOracle.ShowTable(TableView.CurrentCell.Value.ToString()) : DBconnectionSQLServer.ShowTable(DbName, TableView.CurrentCell.Value.ToString()); try { DataView.DataSource = ds;//表从起始行显示在dataGridView里 } catch (Exception) { MessageBox.Show("数据未能正确获得,请重试", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); System.Threading.Thread.CurrentThread.Abort(); //DBView.CurrentCell = DBView.Rows[0].Cells[0]; } } }
/// <summary> /// SQLServer查询列名对应的索引名 /// </summary> /// <param name="DBName"></param> /// <param name="tableName"></param> /// <param name="fieldName"></param> /// <param name="indexName"></param> /// <returns></returns> public static bool DBSqlFieldToIndex(string DBName, string tableName, string fieldName, ref string indexName) { if (SQLCompareTable(DBName, tableName) == true) { string sql = @"USE " + DBName + "; SELECT indexname = a.name FROM sysindexes a JOIN sysindexkeys b ON a.id = b.id AND a.indid = b.indid JOIN sysobjects c ON b.id = c.id JOIN syscolumns d ON b.id = d.id AND b.colid = d.colid WHERE a.indid NOT IN(0, 255) AND c.name = '" + tableName + "' AND d.name = '" + fieldName + "';"; return(DBconnectionSQLServer.ExecuteSql(sql, ref indexName)); } return(false); }
/// <summary> /// 为sql数据库中某个表删除一个字段 /// </summary> /// <param name="DBName"></param> /// <param name="tableName"></param> /// <param name="fieldName"></param> /// <param name="sql"></param> /// <returns></returns> public static bool DBSQLDeleteField(string DBName, string tableName, string fieldName, ref string sql) { if (SQLCompareTable(DBName, tableName) == true) { sql = @"USE " + DBName + "; ALTER TABLE " + tableName + " DROP COLUMN " + fieldName + ";"; return(DBconnectionSQLServer.ExecuteSql(sql)); } return(false); }
/// <summary> /// 为sql数据库某个表的某个字段添加一个非聚集索引 /// </summary> /// <param name="DBName"></param> /// <param name="indexName"></param> /// <param name="tableName"></param> /// <param name="fieldName"></param> /// <param name="sql"></param> /// <returns></returns> public static bool DBSQLAddIndex(string DBName, string indexName, string tableName, string fieldName, ref string sql) { if (SQLCompareTable(DBName, tableName) == true) { sql = @"USE " + DBName + "; CREATE INDEX " + indexName + " ON " + tableName + "(" + fieldName + ");"; return(DBconnectionSQLServer.ExecuteSql(sql)); } return(false); //create index toys_color_i on toys(color); }
private void FormMainOrcl_FormClosed(object sender, FormClosedEventArgs e) { if (justBack == false) { if (DbType == "Oracle") { DBconnectionOracle.CloseConnect(); } else if (DbType == "SQLServer") { DBconnectionSQLServer.CloseConnect(); } Application.Exit(); } }
private void quit_Click(object sender, EventArgs e) { if (DbType == "Oracle") { DBconnectionOracle.CloseConnect(); } else if (DbType == "SQLServer") { DBconnectionSQLServer.CloseConnect(); } justBack = true; FormLogin login = (FormLogin)this.Tag; login.Show(); this.Close(); }
/// <summary> /// 为sql数据库中某个表修改一个字段 /// </summary> /// <param name="DBName"></param> /// <param name="tableName"></param> /// <param name="fieldName"></param> /// <param name="fieldType"></param> /// <param name="fieldLength"></param> /// <param name="sql"></param> /// <returns></returns> public static bool DBSQLModifyField(string DBName, string tableName, string fieldName, string fieldType, string fieldLength, ref string sql) { if (SQLCompareTable(DBName, tableName) == true) { if (fieldType != "VARCHAR") { sql = @"USE " + DBName + "; ALTER TABLE " + tableName + " ALTER COLUMN " + fieldName + " " + fieldType + ";"; } else { sql = @"USE " + DBName + "; ALTER TABLE " + tableName + " ALTER COLUMN " + fieldName + " " + fieldType + "(" + fieldLength + ");"; } return(DBconnectionSQLServer.ExecuteSql(sql)); } return(false); }
/// <summary> /// 读取指定数据库中所有表名 与 指定表名做对比 /// </summary> /// <param name="DBName"></param> /// <param name="tableName"></param> /// <returns></returns> public static bool SQLCompareTable(string DBName, string tableName) { //读取指定数据库中所有表名 与 配置文件中的表名作比较 DataTable dt = DBconnectionSQLServer.ShowTableList(DBName); foreach (DataRow row in dt.Rows) { if (row["name"].ToString() != "tempdb") { if (tableName == row["name"].ToString()) { return(true); } } } return(false); }
/// <summary> /// 读取指定数据库中所有表名 与 配置文件中的表名作比较 /// 如果没找到则调用连接类的方法,将配置文件中的表创建到指定数据库中 /// </summary> /// <param name="DBName"></param> /// <param name="tableName"></param> /// <param name="infoLists"></param> /// <param name="sql"></param> /// <returns></returns> public static bool DBSQlAddTable(string DBName, string tableName, List <ParsedInfo> infoLists, ref string sql) { if (SQLCompareTable(DBName, tableName) == false) { sql = @"USE " + DBName + "; CREATE TABLE " + tableName + "("; foreach (ParsedInfo info in infoLists) { string value = info.TypeInfo.Values.First().ToString(); if (info.TypeInfo.Keys.First().ToString() != "VARCHAR") { sql = sql + info.Column + " " + info.TypeInfo.Keys.First().ToString() + ","; } else { sql = sql + info.Column + " " + info.TypeInfo.Keys.First().ToString() + "(" + info.TypeInfo.Values.First().ToString() + ")" + ","; } } sql = sql.Substring(0, sql.Length - 1); sql += ");"; return(DBconnectionSQLServer.ExecuteSql(sql)); } return(false); }
private void TableView_CurrentCellChanged(object sender, EventArgs e) { if (TableView.CurrentCell != null && TableView.CurrentCell.Value != null) { DataTable ds = (DbType == "Oracle") ? DBconnectionOracle.ShowTable(TableView.CurrentCell.Value.ToString()):DBconnectionSQLServer.ShowTable(DbName, TableView.CurrentCell.Value.ToString()); try { DataView.DataSource = ds;//表从起始行显示在dataGridView里 } catch (Exception) { MessageBox.Show("数据未能正确获得,请重试", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); System.Threading.Thread.CurrentThread.Abort(); //DBView.CurrentCell = DBView.Rows[0].Cells[0]; } } }
private void FromXMLExecute_FormClosing(object sender, FormClosingEventArgs e) { //Do your stuff here. TableView.Rows.Clear(); int findIndex = 0; DataTable dt = (DbType == "Oracle") ? DBconnectionOracle.ShowTableList() : DBconnectionSQLServer.ShowTableList(DbName); foreach (DataRow row in dt.Rows) { int index = TableView.Rows.Add(); TableView.Rows[index].Cells[0].Value = (DbType == "Oracle") ? row["table_name"].ToString() : row["name"].ToString(); string tableName = formxml.tableName; if (tableName != "") { if ((string)TableView.Rows[index].Cells[0].Value == tableName) { findIndex = index; } } } TableView.CurrentCell = TableView.Rows[findIndex].Cells[0]; /* * if (TableView.CurrentCell != null && TableView.CurrentCell.Value != null) * { * DataSet ds = (DbType == "Oracle") ? DBconnectionOracle.ShowTable(TableView.CurrentCell.Value.ToString()) : DBconnectionSQLServer.ShowTable(DbName, TableView.CurrentCell.Value.ToString()); * * try * { * DataView.DataSource = ds.Tables[0].DefaultView;//表从起始行显示在dataGridView里 * } * catch (Exception) * { * MessageBox.Show("数据未能正确获得,请重试", "ERROR", * MessageBoxButtons.OK, MessageBoxIcon.Exclamation); * System.Threading.Thread.CurrentThread.Abort(); * //DBView.CurrentCell = DBView.Rows[0].Cells[0]; * } * }*/ }