Ctyun.Teledb.Sqlserver 1.0.4

本文提供C# 语言的SQL Server SDK用户手册，包含SDK的基本配置和使用。

接口汇总

SDK调用的Openapi接口文档，参见 Openai接口汇总。 接口文档，包含所有Openapi接口的定义和说明，SDK的调用方法与openapi的英文名称保持一致。

使用前提

• SQL Server SDK适用于：
  • .NET STANDARD 2.0
  • .NET Framework 4.6.1
• 需要提前获取天翼云用户的Access Key（AK）, Secret Access Key（SK）以及实例资源池的regionId。

SDK调用

SDK的获取和安装

通过如下方式来安装SQL Server的SDK：

• 使用Visual Studio的NuGet管理工具
  • 选中预添加SDK的项目，右击<管理NuGet程序包>；
  • 搜索 Ctyun.Teledb.Sqlserver，点击安装即可。
• 使用 Package Manager

Install-Package Ctyun.Teledb.Sqlserver -Version 1.0.3

• 使用 .NET CLI 工具

dotnet add package Ctyun.Teledb.Sqlserver --version 1.0.3

认证信息配置

认证必须填写AK，SK和regionId，可选填projectId。

普通变量代码示例：

string ak = "**********";
string sk = "**********";
string regionId = "**********";
string projectId = null;
var auth = new BasicCredentials(ak, sk, regionId);
//var auth = new BasicCredentials(ak, sk, regionId, projectId);
var sqlClient = MSSQLClient.NewBuilder().WithCredential(auth).Build();

具体方法调用

说明:

• SDK 方法名与Openapi接口名称一致，方法的请求参数名称是方法（首字母大写）拼接Request，方法返回结果是方法（首字母大写）拼接Response。例如，getInstancePageList是方法名，GetInstancePageListRequest是请求参数，GetInstancePageListResponse是请求结果。

• 天翼云sqlserver开放接口请参考： 接口名称汇总。

  下面列举一部分接口调用代码，仅供参考。

using Ctyun.Teledb.SqlServer.SDK;
using System;
using System.Text;


namespace SQLServerClientApp { 

 class Program
{

    private static void Main(string[] args)
    {
            //accessKey和secretKey
            string accessKeyId = "***********";
            string secretAccessKey = "********";
             // 比如华东1
            string regionId = "bb9fdb42056f11eda1610242ac110002";
                     
            var auth = new BasicCredentials(accessKeyId, secretAccessKey, regionId);

      
            //创建Client
        MSSQLClient sqlClient = MSSQLClient.NewBuilder()
                .WithCredential(auth)
                .Build();

            //实例管理
            //InstanceManageTest(sqlClient);

            //账号管理
            //UserManageTest(sqlClient);

            //数据库管理
            //DatabaseManageTest(sqlClient);

            //监控告警
            //QueryMetricsTest(sqlClient);

            //日志管理（不包含下载堵塞扩展报告）
            //XBlockEventManageTest(sqlClient);

            //日志管理-下载堵塞扩展报告
            //downloadXEventFileAsync(sqlClient);

            //备份恢复
            //backup1(sqlClient);

            //backup2(sqlClient);
            //backupConfig(sqlClient);

            //restoreTest1(sqlClient);
        }

        private static void downloadXEventFileAsync(MSSQLClient sqlClient)
        {
            // 直接下载文件，返回byte[]，如果文件大注意设置client的http超时
            var request1 = new DownloadXEventFileRequest()
            {
                prodInstId = "*******",
                eventType = "block",
                filename = "blocked_process_report_0_133637536753180000.xel"
            };

            try
            {
                var response1 = sqlClient.downloadXEventFile(request1);
                if (response1 != null && response1.isSuccess())
                {
                    byte[] contents = response1.ReturnObj;
                    // 可以写入文件之类的，这里直接转成字符串
                    Console.WriteLine(Encoding.UTF8.GetString(contents));
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

        private static void XBlockEventManageTest(MSSQLClient sqlClient)
    {

            // 创建（开启）阻塞事件
            var request2 = new CreateXEventRequest()
            {
                prodInstId = "*******",
                eventType = "block",
                timeThresh = 10
            };

            try
            {
                var response1 = sqlClient.createXEvent(request2);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }


            //修改阻塞事件阈值
           var request3 = new ModifyXEventRequest()
           {
               prodInstId = "*******",
               eventType = "block",
               timeThresh = 20
           };

            try
            {
                var response1 = sqlClient.modifyXEvent(request3);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            var request1 = new QueryXEventLogsRequest()
            {
                prodInstId = "*******",
                eventType = "block",
            };

            string fn = "";
            try
            {
                QueryXEventLogsResponse response1 = sqlClient.queryXEventLogs(request1);
                Console.WriteLine(response1);
                // 获取第一个
                if (response1 != null && response1.ReturnObj != null && response1.ReturnObj.Length > 0)
                {
                    fn = response1.ReturnObj[0].name;
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 获取日志下载链接（注意需要在开启后几分钟，因为不是实时上传到oss）
            var eventLogDownloadLinkRequest = new EventLogDownloadLinkRequest()
            {
                prodInstId = "*******",
                eventType = "block",
                filename = fn
            };
            try
            {
                var response1 = sqlClient.getEventLogDownloadLink(eventLogDownloadLinkRequest);
                Console.Write(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 删除（关闭）阻塞事件
            var deleteXEventRequest = new DeleteXEventRequest()
            {
                prodInstId = "*******",
                eventType = "block"
            };

            try
            {
                var response1 = sqlClient.deleteXEvent(deleteXEventRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

        }


    private static void QueryMetricsTest(MSSQLClient sqlClient)
    {
        var request1 = new QueryMetricsRequest()
        {
            prodInstId = "*******",
            query = "CPU_RATE",
            // startTime = "1709707385",
            // endTime = "1710497717"
        };

        try
        {
            var response1 = sqlClient.queryMetrics(request1);
            Console.WriteLine(response1);
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }

    private static void restoreTest1(MSSQLClient sqlClient)
    {

            // 根据key恢复（需要调用上传链接接口生成链接并上传完整备份后，再调用该接口，注意是备份空间为对象存储的实例）
            //var dispatchRestoreKeyTaskRequest = new DispatchRestoreKeyTaskRequest()
            //{
            //    prodInstId = "*******",
            //    objKey = "aaaa",
            //    dbName = "db7"
            //};

            //try
            //{
            //    var response1 = sqlClient.dispatchRestoreKeyTask(dispatchRestoreKeyTaskRequest);
            //    Console.WriteLine(response1);
            //}
            //catch (Exception e)
            //{
            //    Console.WriteLine(e.Message);
            //}

            // 创建恢复任务
            string id = "";
            var dispatchRestoreTaskRequest = new DispatchRestoreTaskRequest()
            {
                prodInstId = "*******",
                srcProdInstId = "*******",
                restorePolicy = "backupset",
                //  restoreTime = "2024-04-06T00:00:00Z",
                srcBackupId = "1805166339195584514",
                dbList = new RestoreDbListObject[]{
                            new RestoreDbListObject(){
                                srcDbName = "db1",
                                dstDbName = "db1_bak"
                            }
                        },
                replaceDb = 0
            };
            try
            {
                var response1 = sqlClient.dispatchRestoreTask(dispatchRestoreTaskRequest);
                Console.WriteLine(response1);
                if (response1 != null && response1.isSuccess())
                {
                    id = response1.ReturnObj.id;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询恢复任务详情
            var getRecoveryRequest = new GetRecoveryRequest()
            {
                restoreId = id,
            };

            try
            {
                var response1 = sqlClient.getRecovery(getRecoveryRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 根据oss下载链接恢复数据（发起恢复任务）
            long oid = 0L;
            var RecoveryFromOssRequest = new RecoveryFromOssRequest()
            {
                prodInstId = "*******",
                taskName = "test1",
                fileUrl = "填入天翼云对象存储zos的临时分享链接url,有效期建议不少于30分钟",
                replaceDb = 1,
                dbName = "a1_bak"
            };

            try
            {
                var response1 = sqlClient.recoveryFromOss(RecoveryFromOssRequest);
                Console.WriteLine(response1);
                if (response1 != null && response1.isSuccess())
                {
                    oid = response1.ReturnObj.ossRestoreId != null ? response1.ReturnObj.ossRestoreId.Value : 0;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 获取oss恢复任务历史记录
            var GetOssRecoveryTasksRequest = new GetOssRecoveryTasksRequest()
            {
                prodInstId = "*******",
                pageNum = 1,
                pageSize = 10,
            };

            try
            {
                var response1 = sqlClient.getOssRecoveryTasks(GetOssRecoveryTasksRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询oss恢复任务详情
            var GetOssRecoveryTaskDetailRequest = new GetOssRecoveryTaskDetailRequest()
            {
                ossRestoreId = oid,
            };

            try
            {
                var response1 = sqlClient.getOssRecoveryTaskDetail(GetOssRecoveryTaskDetailRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
                       

    }

        private static void backupConfig(MSSQLClient sqlClient)
        {
            // 查询备份策略
            var queryBackupConfigRequest = new QueryBackupConfigRequest()
            {
                prodInstId = "*******",
            };
            try
            {
                var response1 = sqlClient.queryBackupConfig(queryBackupConfigRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询允许开启跨域备份的资源池列表（目标资源池）
            var queryRemoteRegionsRequest = new QueryRemoteRegionsRequest()
            {

            };
            try
            {
                var response1 = sqlClient.queryRemoteRegions(queryRemoteRegionsRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 修改备份策略
            var alterBackupConfigRequest = new AlterBackupConfigRequest()
            {
                prodInstId = "*******",
                enableIncremental = 0,
                dataCycleList = new int[] { 1, 2, 3 },
                dataRetainDays = 7,
                dataPeriod = "01:00-02:00",
                enableSchedule = 1,
                scheduleRetainDays = 30,
                scheduleCycleList = new int[] { 1, 2, 3 },
                //enableRemote = 0,
                //remoteRegionId = "",
                //copyHistory = 0,
            };
            try
            {
                var response1 = sqlClient.alterBackupConfig(alterBackupConfigRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e) {
                Console.WriteLine(e.Message);
            }
        }

        private static void backup1(MSSQLClient sqlClient)
    {
            // 创建手动备份
            var dispatchManualBackupTaskRequest = new DispatchManualBackupTaskRequest()
            {
                prodInstId = "*******",
                backupName = "aaa1",
                backupMethod = 1,
                backupPolicy = "instance",
                //backupPolicy = "database",
                //dbs = new string[] { "db7", "db8" },
            };

            string id = "";
            try
            {
                var response1 = sqlClient.dispatchManualBackupTask(dispatchManualBackupTaskRequest);
                Console.WriteLine(response1);
                    if (response1 != null && response1.isSuccess()) {
                        id = response1.ReturnObj.id;
                    }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询备份详情
            var queryBackupDetailRequest = new QueryBackupDetailRequest()
            {
                backupId = id,
            };
            try
            {
                var response1 = sqlClient.queryBackupDetail(queryBackupDetailRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询备份列表
            var pageQueryDataBackupRequest = new PageQueryDataBackupRequest()
            {
                prodInstId = "*******",
                pageNum = 1,
                pageSize = 10,
            };

            try
            {
                var response1 = sqlClient.pageQueryDataBackup(pageQueryDataBackupRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }



    }

        private static void backup2(MSSQLClient sqlClient)
        {
            // 获取备份下载链接
            var getBackupDownloadLinkRequest = new GetBackupDownloadLinkRequest()
            {
                // 可以从查询备份列表的结果集取id
                backupId = "1805166339195584514",
                pageNum = 1, pageSize = 10,
               
            };
            try
            {
                var response1 = sqlClient.getBackupDownloadLink(getBackupDownloadLinkRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

          

            // 生成一个上传链接
            var getBackupUploadLinkRequest = new GetBackupUploadLinkRequest()
            {
                prodInstId = "*******",

            };
            try
            {
                var response1 = sqlClient.getBackupUploadLink(getBackupUploadLinkRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询备份中包含哪些库
            var getBackupContainedDbsRequest = new GetBackupContainedDbsRequest()
            {
                backupId = "1805166339195584514",
            };
            try
            {
                var response1 = sqlClient.getBackupContainedDbs(getBackupContainedDbsRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            var deleteBackupRequest = new DeleteBackupRequest()
            {
                backupId = "1805438578285527041",
            };
            try
            {
                var response1 = sqlClient.deleteBackup(deleteBackupRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }

    private static void UserManageTest(MSSQLClient sqlClient)
    {
            // 查询数据库账号列表
            PageQueryLoginsRequest pageQueryLoginsRequest = new PageQueryLoginsRequest()
            {
                prodInstId = "*******",
                pageNum = 1,
                pageSize = 10
            };

            try
            {
                var response1 = sqlClient.pageQueryLogins(pageQueryLoginsRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询账号权限
            QueryDbRoleMemberListRequest queryDbRoleMemberListRequest = new QueryDbRoleMemberListRequest()
            {
                prodInstId = "*******",
                loginName = "root",
            };

            try
            {
                var response1 = sqlClient.queryDbRoleMemberList(queryDbRoleMemberListRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 创建账号
            var createLoginRequest = new CreateLoginRequest()
            {
                prodInstId = "*******",
                loginName = "test1",
                loginPassword = "Evb256812!2a",
                loginType = "1",
            };
            try
            {
                var response1 = sqlClient.createLogin(createLoginRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 修改账号权限
            var updateDbRoleMemberListRequest = new UpdateDbRoleMemberListRequest()
            {
                prodInstId = "*******",
                loginName = "test1",
                userMappingList = new UserMappingList[]{
                        new UserMappingList(){
                            databaseName = "db1",
                            userType = 1
                        }
                    }
            };

            try
            {
                var response1 = sqlClient.updateDbRoleMemberList(updateDbRoleMemberListRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }


            DeleteLoginRequest deleteLoginRequest = new DeleteLoginRequest()
            {
                prodInstId = "*******",
                loginName = "test1"
            };

            try
            {
                var response1 = sqlClient.deleteLogin(deleteLoginRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

    }

    private static void DatabaseManageTest(MSSQLClient sqlClient)
    {
            //创建库
            var request1 = new CreateDatabaseRequest()
            {
                prodInstId = "*******",
                databaseName = "testdb",
                collate = "Chinese_PRC_CI_AS"
            };

            try
            {
                var response1 = sqlClient.createDatabase(request1);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 查询库列表
            var request2 = new PageQueryDbListRequest()
            {
                pageNum = 1,
                pageSize = 10,
                prodInstId = "*******"
            };

            try
            {
                var response2 = sqlClient.pageQueryDbList(request2);
                Console.WriteLine(response2);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            // 重命名库
            var renameDatabaseRequest = new RenameDatabaseRequest()
            {
                prodInstId = "*******",
                oldDBName = "testdb",
                newDBName = "test1"
            };

            try
            {
                var response1 = sqlClient.renameDatabase(renameDatabaseRequest);
                Console.WriteLine(response1);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

            //删除库
            var deleteDatabaseRequest = new DeleteDatabaseRequest()
            {
                prodInstId = "*******",
                databaseName = "test1"
            };
            try
            {
                var response = sqlClient.deleteDatabase(deleteDatabaseRequest);
                Console.WriteLine(response);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

    }

    private static void InstanceManageTest(MSSQLClient sqlClient)
    {


        var request1 = new GetInstancePageListRequest()
        {
            pageNum = 2,
            pageSize = 10,
        };

        try
        {
            var response1 = sqlClient.getInstancePageList(request1);
                Console.WriteLine(response1);
             
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }
}
}