C#实现Oracle的交互(数据库备份与管理)

using system;
using oracle.dataaccess.client;

namespace csharporacleexample
{
    class program
    {
        static void main(string[] args)
        {
            string connectionstring = "data source=oracle_db;user id=your_username;password=your_password;";
            oracleconnection conn = new oracleconnection(connectionstring);
            try
            {
                conn.open();
                string sql = "select * from your_table";
                oraclecommand cmd = new oraclecommand(sql, conn);
                oracledatareader reader = cmd.executereader();
                while (reader.read())
                {
                    // 处理每一行数据
                }
                reader.close();
            }
            catch (exception ex)
            {
                console.writeline("error: " + ex.message);
            }
            finally
            {
                if (conn.state == system.data.connectionstate.open)
                {
                    conn.close();
                }
            }
        }
    }
}

<connectionstrings>
    <add name="oracledb" 
         connectionstring="user id=your_username;password=your_password;data source=your_database_service_name;" 
         providername="oracle.dataaccess.client" />
</connectionstrings>

using oracle.dataaccess.client;

class program
{
    static void main(string[] args)
    {
        string constr = configurationmanager.connectionstrings["oracledb"].connectionstring;
        using (oracleconnection con = new oracleconnection(constr))
        {
            try
            {
                con.open();
                console.writeline("connection is established");
            }
            catch (exception ex)
            {
                console.writeline("error: " + ex.message);
            }
        }
    }
}

using oracle.dataaccess.client;

oracleconnection con = new oracleconnection();
con.connectionstring = "user id=your_username;password=your_password;data source=your_database_service_name;";
try
{
    con.open();
    console.writeline("database connection is successfully opened.");
}
catch (exception ex)
{
    console.writeline("error: " + ex.message);
}
finally
{
    con.close();
}

oraclecommand cmd = new oraclecommand("select * from employees", con);
oracledatareader reader = cmd.executereader();

while (reader.read())
{
    console.writeline(reader.getstring(0)); // 假设第一列是字符串类型
}
reader.close();

oraclecommand cmd = new oraclecommand("getemployeedetails", con);
cmd.commandtype = commandtype.storedprocedure;

oracleparameter outputparameter = cmd.parameters.add("empname", oracledbtype.varchar2);
outputparameter.direction = parameterdirection.output;

cmd.executenonquery();

console.writeline("employee name: " + outputparameter.value);

using oracle.dataaccess.client;
using oracle.dataaccess.types;

// 创建连接和oraclebulkcopy对象
using (oracleconnection conn = new oracleconnection("data source=your_data_source;user id=your_username;password=your_password;"))
{
    conn.open();
    oraclebulkcopy bulkcopy = new oraclebulkcopy(conn);

    // 设置目标表名和批量操作选项
    bulkcopy.destinationtablename = "your_target_table_name";
    bulkcopy.batchsize = 100; // 批量操作的大小

    // 准备要批量插入的数据
    datatable table = new datatable("your_table_name");
    table.columns.add("column1", typeof(int));
    table.columns.add("column2", typeof(string));

    // 添加数据行
    for (int i = 1; i <= 1000; i++)
    {
        table.rows.add(i, "data " + i);
    }

    // 执行批量插入
    bulkcopy.writetoserver(table);
    conn.close();
}

oracleconnection con = new oracleconnection("user id=your_username;password=your_password;data source=your_data_source;");
con.open();

oracletransaction tx = con.begintransaction();

try
{
    oraclecommand cmd = con.createcommand();
    cmd.transaction = tx;

    // 执行sql操作
    cmd.commandtext = "insert into employees (id, name) values (1, 'john doe')";
    cmd.executenonquery();
    cmd.commandtext = "update employees set salary = 5000 where id = 1";
    cmd.executenonquery();

    // 提交事务
    tx.commit();
    console.writeline("transaction is committed.");
}
catch (exception ex)
{
    // 回滚事务
    tx.rollback();
    console.writeline("transaction is rolled back.");
}
finally
{
    con.close();
}

-- 文件名: backup.sql
-- 备份目的: 逻辑备份示例
-- 作者: your_name
-- 创建日期: 2023-03-10

/* 创建表结构 */
create table backup_table (
    id int primary key,
    data varchar(255)
);

/* 插入测试数据 */
insert into backup_table values (1, 'sample data');

-- 清理环境
drop table backup_table;

-- 授予用户对备份表的select权限
grant select on backup_table to user_name;

-- 收回用户对备份表的delete权限
revoke delete on backup_table from user_name;

using (oracleconnection conn = new oracleconnection(connectionstring))
{
    conn.open();
    using (oraclecommand cmd = conn.createcommand())
    {
        cmd.commandtext = "select * from users where user_id = :userid";
        cmd.parameters.addwithvalue(":userid", 1);
        using (oracledatareader reader = cmd.executereader())
        {
            while (reader.read())
            {
                string username = reader.getstring(0);
                int age = reader.getint32(1);
                console.writeline($"user name: {username}, age: {age}");
            }
        }
    }
}

try
{
    // 执行数据库命令
}
catch (oracleexception ex)
{
    // 错误处理代码
}

try
{
    // 尝试执行的代码
}
catch (oracleexception ex)
{
    // 处理oracle相关的异常
}
catch (exception ex)
{
    // 处理其他所有异常
}
finally
{
    // 不管是否发生异常都会执行的代码，通常用于清理资源
}

catch (oracleexception ex)
{
    console.writeline("oracle exception: " + ex.message);
    console.writeline("stack trace: " + ex.stacktrace);
}

using system.io;
using system.io.compression;
using system.security.cryptography;
using system.text;

public void backupandencrypt(string sourcefolder, string destinationfile)
{
    // 假设已经从sourcefolder获取了所有需要备份的文件
    using (filestream originalfilestream = new filestream(sourcefolder, filemode.open, fileaccess.read))
    using (filestream compressedfilestream = new filestream(destinationfile, filemode.create))
    using (gzipstream compressionstream = new gzipstream(compressedfilestream, compressionmode.compress))
    {
        originalfilestream.copyto(compressionstream);
    }

    // 加密压缩后的备份文件
    string key = "your-encryption-key";
    using (aes myaes = aes.create())
    {
        myaes.key = encoding.utf8.getbytes(key);
        myaes.iv = new byte[16];
        using (filestream filetoencrypt = file.openread(destinationfile + ".gz"))
        using (filestream encryptedfilestream = new filestream(destinationfile + ".gz.enc", filemode.create))
        using (cryptostream cryptostream = new cryptostream(encryptedfilestream, myaes.createencryptor(), cryptostreammode.write))
        {
            filetoencrypt.copyto(cryptostream);
        }
    }
}

using system;
using system.threading;

public class backuptask
{
    private timer _timer;

    public backuptask()
    {
        // 创建定时器，定时器回调方法为backupmethod，首次触发时间为30秒后，之后每1小时触发一次
        _timer = new timer(backupmethod, null, timespan.fromseconds(30), timespan.fromhours(1));
    }

    private void backupmethod(object state)
    {
        // 此处是备份逻辑，可以调用备份函数 backupdatabase()
        backupdatabase();
    }

    private void backupdatabase()
    {
        // 执行备份的代码逻辑
        console.writeline("backup is running...");
    }
}

public class program
{
    public static void main()
    {
        new backuptask();
        // 让主线程继续运行，否则程序会立即退出
        console.writeline("press any key to exit...");
        console.readkey();
    }
}

using system;
using system.io;
using system.threading;

public class databasebackuptask
{
    public void startbackupschedule()
    {
        // 假定备份脚本路径和参数
        string backupscriptpath = @"c:\backupscripts\backup.sql";
        string backupfilepath = @"d:\backups\backup.bak";

        // 创建定时器
        timer backuptimer = new timer(backupdatabase, null, timespan.zero, timespan.fromhours(1));

        console.writeline("backup schedule started, press ctrl+c to stop.");
        console.readkey();
    }

    private void backupdatabase(object state)
    {
        // 执行备份命令
        string argument = $"-s servername -d databasename -u username -p password -f \"{backupfilepath}\"";
        processstartinfo processinfo = new processstartinfo("sqlcmd.exe", argument)
        {
            redirectstandardoutput = true,
            redirectstandarderror = true,
            useshellexecute = false,
            createnowindow = true
        };

        process process = process.start(processinfo);
        process.waitforexit();
    }
}

public class program
{
    public static void main()
    {
        new databasebackuptask().startbackupschedule();
    }
}