禁用启用oracle数据库中所有约束（c#实现）

最新推荐文章于 2025-06-10 14:41:22 发布

原创最新推荐文章于 2025-06-10 14:41:22 发布 · 722 阅读

0 ·

CC 4.0 BY-SA版权

C# 同时被 2 个专栏收录

3 篇文章

订阅专栏

oracle

2 篇文章

订阅专栏

本文介绍了一种使用C#实现批量禁用与重新启用Oracle数据库中约束的方法。通过构造特定SQL语句并利用OracleClient API执行这些语句，可以有效地管理数据库表上的外键约束。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方法说明：先用sql语句将启用/禁用各个约束的语句查询出来，然后逐条执行。以下为C#源代码：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace SynDBDll
{
public class DBOprtMgr
{
OracleConnection m_conn;
public OracleConnection Conn { get { return m_conn; } }
public DBOprtMgr(string connStr)
{
m_conn = new OracleConnection(connStr);
}
public bool ForbidAllConstants()
{
List<string> forbidStrs = new List<string>();
if (!OpenConn()) return false;
string forbitSqlStr = "select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R'";//该语句能查询出很多条sql语句，每条语句禁用一个约束
OracleCommand command = new OracleCommand(forbitSqlStr, m_conn);
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string onsStr = reader.GetValue(0).ToString();
if (onsStr[onsStr.Length - 1] == ';')
onsStr = onsStr.Substring(0, onsStr.Length - 1);
forbidStrs.Add(onsStr);
}
reader.Close();
OracleTransaction theTransaction = Conn.BeginTransaction();
try
{
foreach (string oneSqlStr in forbidStrs)
ExecuteCommand(oneSqlStr, theTransaction);
}
catch
{
theTransaction.Rollback();
// 关闭数据库连接
m_conn.Close();
return false;
}
theTransaction.Commit();
// 关闭数据库连接
m_conn.Close();
return true;
}
public bool RestartAllConstants()
{
List<string> restartStrs = new List<string>();
if (!OpenConn()) return false;
string restartSqlStr = "select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'";//该语句能查询出很多条sql语句，每条语句启用一个约束
OracleCommand command = new OracleCommand(restartSqlStr, m_conn);
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string onsStr = reader.GetValue(0).ToString();
if (onsStr[onsStr.Length - 1] == ';')
onsStr = onsStr.Substring(0, onsStr.Length - 1);
restartStrs.Add(onsStr);
}
reader.Close();

OracleTransaction theTransaction = Conn.BeginTransaction();
try
{
foreach (string oneSqlStr in restartStrs)
ExecuteCommand(oneSqlStr, theTransaction);
}
catch
{
theTransaction.Rollback();
// 关闭数据库连接
m_conn.Close();
return false;
}
theTransaction.Commit();
// 关闭数据库连接
m_conn.Close();
return true;
}

public void ExecuteCommand(string sqlStr, OracleTransaction theTransaction)
{
if (!OpenConn()) return;
OracleCommand command = new OracleCommand(sqlStr, m_conn);
command.Transaction = theTransaction;
command.ExecuteNonQuery();
}

public bool OpenConn()
{
if (m_conn.State != ConnectionState.Open)
m_conn.Open();
if (m_conn.State != ConnectionState.Open)
{
Console.WriteLine("无法打开数据库连接!");
return false;
}
return true;
}

}
}