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];
* }
* }*/
}
