如果您想了解SQLServer资源调控器的相关知识，那么本文是一篇不可错过的文章，我们将对sqlserver资源管理器怎么打开进行全面详尽的解释，并且为您提供关于C#SqlServer操作辅助类(Sq
如果您想了解SQLServer 资源调控器的相关知识，那么本文是一篇不可错过的文章，我们将对sql server资源管理器怎么打开进行全面详尽的解释，并且为您提供关于C# SqlServer操作辅助类(SqlServerHelper.cs)、C#访问SqlServer的工具类SqlServerHelper、DBSyncer 中间件 SQLServer同步SQLServer 配置教程、SQL Server 2014使用资源调控器对IOPS控制的有价值的信息。
本文目录一览：- SQLServer 资源调控器（sql server资源管理器怎么打开）
- C# SqlServer操作辅助类(SqlServerHelper.cs)
- C#访问SqlServer的工具类SqlServerHelper
- DBSyncer 中间件 SQLServer同步SQLServer 配置教程
- SQL Server 2014使用资源调控器对IOPS控制
SQLServer 资源调控器（sql server资源管理器怎么打开）
SQL Server 资源调控器是一项可用于管理 sql Server 工作负荷和系统资源使用情况的功能。您可使用资源调控器指定传入应用程序请求可以使用的 cpu、物理 IO 和内存的数量限制。
创建新的资源池步骤如下：
1. 创建资源池
2.创建工作负荷组
3.创建分类器函数
4.分类器函数注册到资源调控器
资源概念：
资源池：
资源池表示服务器的物理资源。您可以将池看作 sql Server 实例内部的一个虚拟 sql Server 实例。当安装 sql Server 时，会创建两个资源池（内部资源池和默认资源池）。
工作负荷组：
工作负荷组充当具有相似分类标准的会话请求的容器。工作负荷允许对会话进行聚合监视，并定义会话的策略。每个工作负荷组都处于一个资源池中。当安装 sql Server 时，会创建两个工作负荷组（内部工作负荷组和默认工作负荷组）并将其映射到对应的资源池。资源调控器还支持用户定义的工作负荷组。
分类：
分类过程会根据传入会话的特征将其分配给工作负荷组。您可以通过编写用户定义函数（称为分类器函数）来定制分类逻辑。资源调控器还支持用于实现分类规则的分类器用户定义的函数。
【资源调控器】
--【资源调控器】
-- https://msdn.microsoft.com/zh-cn/library/bb933866.aspx
USE master
GO
-- 启用资源调控器，或者重新刷新配置
ALTER RESOURCE GOVERnor RECONfigURE;
GO
/*【资源池】*/
-- 资源调控器预定义两个资源池：内部池和默认池。
-- https://msdn.microsoft.com/zh-cn/library/hh510189.aspx
-- 创建资源池
CREATE RESOURCE POOL [poolAdhoc]
WITH(
min_cpu_percent=0,--较低优先级的查询无法争用cpu，此处可设置
max_cpu_percent=50,--如果有可用 cpu 容量，可用至 100%
min_memory_percent=0,--授予查询执行的内存，不可用于任何其他池
max_memory_percent=100
)
GO
ALTER RESOURCE GOVERnor RECONfigURE;
GO
-- 更改资源池设置
ALTER RESOURCE POOL [poolAdhoc]
WITH(
min_cpu_percent=0,max_cpu_percent=40,min_memory_percent=0,max_memory_percent=100
)
GO
ALTER RESOURCE GOVERnor RECONfigURE;
GO
--(新参数:适用范围：sql Server 2012 及后期版本)
ALTER RESOURCE POOL [poolAdhoc]
WITH(
cap_cpu_percent=60 --限制最高使用 cpu 的60%（resource pool stats\cpu usage target\poolAdhoc）,affinity scheduler = auto --指定资源池在某cpu运行，避免争用
--,min_iops_per_volume=20 --IOPS，设置IO饱和时避免争用（2014才可以次功能）
--,max_iops_per_volume=100
)
ALTER RESOURCE GOVERnor RECONfigURE;
GO
-- 删除资源池(先删除其工作组）。此时测试先不删除！
DROP RESOURCE POOL poolAdhoc;
GO
ALTER RESOURCE GOVERnor RECONfigURE;
GO
/*【工作负荷组】*/
-- https://msdn.microsoft.com/zh-cn/library/hh510228.aspx
-- 创建工作负荷组
CREATE WORKLOAD GROUP [groupAdhoc]
WITH(
group_max_requests=0,importance=Medium,--相对重要性{ LOW | MEDIUM | HIGH }
request_max_cpu_time_sec=0,--请求可以使用的最长 cpu 时间，以秒为单位
request_max_memory_grant_percent=25,--单个请求可以从池中获取的最大内存量（对资源池的MAX_MEMORY_PERCENT 而言）
request_memory_grant_timeout_sec=0,--查询等待内存授予（工作缓冲区内存）变为可用的最长时间（以秒为单位）。
max_dop=0 --并行请求的最大并行度 (DOP)，（0 到 255）
) USING [poolAdhoc]--指定工作组
GO
ALTER RESOURCE GOVERnor RECONfigURE;
GO
-- 更改工作负荷组设置
ALTER WORKLOAD GROUP [groupAdhoc]
WITH(
group_max_requests=0,request_max_cpu_time_sec=0,request_max_memory_grant_percent=25,request_memory_grant_timeout_sec=0,max_dop=0
) USING [poolAdhoc]
GO
ALTER RESOURCE GOVERnor RECONfigURE;
GO
-- 删除工作负荷组，此时测试先不删除！
DROP WORKLOAD GROUP groupAdhoc;
GO
ALTER RESOURCE GOVERnor RECONfigURE;
GO
/*【分类器函数】*/
-- https://msdn.microsoft.com/zh-cn/library/hh510208.aspx
-- 创建分类器函数(如果是 sysadmin 角色，则分配工作组groupAdhoc ，否则使用默认工作组 default)
-- DROP FUNCTION dbo.fn_Classifier
USE master
GO
CREATE FUNCTION dbo.fn_Classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
IF (IS_SRVROLEMEMBER('sysadmin',SUSER_SNAME()) = 0)
RETURN N'groupAdhoc'
RETURN N'default'
END
GO
/*
若函数已经注册到资源调控器，更改函数定义则错误！
Msg 10920,Level 16,State 2,Procedure fn_Classifier,Line 1
Cannot alter user-defined function 'fn_Classifier'. It is being used as a resource governor classifier.
解决：先把资源调控器的分类函数设置为NULL。
ALTER RESOURCE GOVERnor WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERnor RECONfigURE
GO
*/
-- 分类器函数注册到资源调控器
ALTER RESOURCE GOVERnor WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier)
GO
ALTER RESOURCE GOVERnor RECONfigURE
GO
【测试】
-- 1. 打开性能监视器,监控所有资源池的 cpu 使用百分比情况
\Processor information(_Total)\% Processor Time
\sqlServer:Resource Pool Stats(poolAdhoc)\cpu usage %
\sqlServer:Resource Pool Stats(default)\cpu usage %
\sqlServer:Resource Pool Stats(internal)\cpu usage %
-- 2. 现在可以创建登录名 [kk],不授予服务器角色 sysadmin
-- EXEC sp_addsrvrolemember @loginame= 'kk',@rolename = 'sysadmin'
EXEC sp_dropsrvrolemember @loginame = 'kk',@rolename = 'sysadmin'
-- 3. 用该账号[kk]登录数据库,执行循环语句消耗 cpu
SELECT SUSER_SNAME(),IS_SRVROLEMEMBER('sysadmin',SUSER_SNAME())
SET NOCOUNT ON
DECLARE @I INT
WHILE 1=1
BEGIN
SELECT @I=COUNT(*) FROM sys.objects
END
SET NOCOUNT OFF
-- 4. 再用当前的系统管理员账号依次打开 2 个查询窗口，执行以下循环语句消耗cpu
SELECT SUSER_SNAME(),SUSER_SNAME()),dbo.fn_Classifier()
SET NOCOUNT ON
DECLARE @I INT
WHILE 1=1
BEGIN
SELECT @I=COUNT(*) FROM sys.objects
END
SET NOCOUNT OFF
可以看到，当 sysadmin 角色的用户执行时，默认池 default 的cpu 使用率升高，资源池 poolAdhoc 的则下降，但总体 cpu 是不变的，cpu 处理时间仍然是 100% 。
【资源调控器设置】
/*【资源调控器设置】*/ -- https://msdn.microsoft.com/zh-cn/library/bb934013(v=sql.120).aspx -- 启用资源调控器，或者重新刷新配置 ALTER RESOURCE GOVERnor RECONfigURE; GO -- 禁用资源调控器 ALTER RESOURCE GOVERnor disABLE; GO -- 重置有关所有工作负荷组和资源池的统计信息 ALTER RESOURCE GOVERnor RESET STATISTICS; GO -- 分类器函数注册到资源调控器 ALTER RESOURCE GOVERnor WITH (CLASSIFIER_FUNCTION = dbo.fn_Classifier); GO -- 设置每个磁盘卷的最大排队 I/O 操作数 (最大100 ，不是百分比；sql Server 2014 及后期版本使用) ALTER RESOURCE GOVERnor WITH (MAX_OUTSTANDING_IO_PER_VOLUME = 100 ); GO
【资源调控器目录视图】
/*【资源调控器目录视图】*/ -- https://msdn.microsoft.com/zh-cn/library/bb934093(v=sql.120).aspx SELECT * FROM sys.dm_resource_governor_resource_pools SELECT * FROM sys.dm_resource_governor_workload_groups SELECT * FROM sys.dm_resource_governor_configuration SELECT * FROM sys.resource_governor_resource_pools SELECT * FROM sys.resource_governor_workload_groups SELECT * FROM sys.resource_governor_configuration
参考：资源调控器
C# SqlServer操作辅助类(SqlServerHelper.cs)
开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了sqlcehelper这个关于操作本地数据库sdf的例子。现在介绍一下在
开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了sqlcehelper这个关于操作本地数据库sdf的例子。现在介绍一下在sqlserver中的代码。
因为Sqlserver数据库有多种登录方式，所以在构造函数中：
<span>#region</span>[构造函数]
<span>///</span> <span><summary></summary></span>
<span>///</span><span> 构造函数，初始化
</span><span>///</span> <span></span>
<span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span>
<span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
<span>public</span> SqlServerHelper(<span>string</span> ConStr, <span>int</span><span> TimeOut)
{
ConnectString </span>=<span> ConStr;
connect </span>= <span>new</span><span> SqlConnection(ConnectString);
CommandTimeOut </span>=<span> TimeOut;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构造函数，初始化
</span><span>///</span> <span></span>
<span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span>
<span>public</span> SqlServerHelper(<span>string</span><span> ConStr)
{
ConnectString </span>=<span> ConStr;
connect </span>= <span>new</span><span> SqlConnection(ConnectString);
CommandTimeOut </span>= <span>30</span><span>;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构造函数，初始化
</span><span>///</span> <span></span>
<span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span>
<span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span>
<span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span>
<span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span>
<span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
<span>public</span> SqlServerHelper(<span>string</span> DataServer, <span>string</span> DataBase, <span>string</span> UserID, <span>string</span> Pwd,<span>int</span><span> TimeOut)
{
ConnectString </span>= <span>string</span>.Format(<span>"</span><span>Data Source={0};Initial Catalog={1};User ID={2};pwd={3}</span><span>"</span><span>, DataServer, DataBase, UserID, Pwd);
connect </span>= <span>new</span><span> SqlConnection(ConnectString);
CommandTimeOut </span>=<span> TimeOut;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构造函数，初始化
</span><span>///</span> <span></span>
<span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span>
<span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span>
<span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span>
<span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span>
<span>public</span> SqlServerHelper(<span>string</span> DataServer, <span>string</span> DataBase, <span>string</span> UserID, <span>string</span><span> Pwd)
{
ConnectString </span>= <span>string</span>.Format(<span>"</span><span>Data Source={0};Initial Catalog={1};User ID={2};pwd={3}</span><span>"</span><span>, DataServer, DataBase, UserID, Pwd);
connect </span>= <span>new</span><span> SqlConnection(ConnectString);
CommandTimeOut </span>= <span>30</span><span>;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构造函数，初始化
</span><span>///</span> <span></span>
<span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span>
<span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span>
<span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
<span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span> isLocal,<span>int</span><span> TimeOut)
{
</span><span>if</span> (!<span>isLocal)
{
</span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>));
}
connectstring </span>= <span>string</span>.Format(<span>"</span><span>Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True</span><span>"</span><span>, database);
connect </span>= <span>new</span><span> SqlConnection(ConnectString);
CommandTimeOut </span>=<span> TimeOut;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 构造函数，初始化
</span><span>///</span> <span></span>
<span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span>
<span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span>
<span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span><span> isLocal)
{
</span><span>if</span> (!<span>isLocal)
{
</span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>));
}
connectstring </span>= <span>string</span>.Format(<span>"</span><span>Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True</span><span>"</span><span>, database);
connect </span>= <span>new</span><span> SqlConnection(ConnectString);
CommandTimeOut </span>= <span>30</span><span>;
}
</span><span>#endregion</span>登录后复制
这样，可以方便地构造出自己的连接字符串。
同时这里用到几个比较有用的有关数据库服务器及数据库结构的函数。比如查询局域网中所有数据库实例，获取目标实例所有数据库，获取指定数据库的所有表，获取指定表所有行：
View Code
<span>#region</span> 供使用API方式时使用<span>
[DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
</span><span>private</span> <span>static</span> <span>extern</span> <span>short</span> SQLAllocHandle(<span>short</span> hType, IntPtr inputHandle, <span>out</span><span> IntPtr outputHandle);
[DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
</span><span>private</span> <span>static</span> <span>extern</span> <span>short</span> SQLSetEnvAttr(IntPtr henv, <span>int</span> attribute, IntPtr valuePtr, <span>int</span><span> strLength);
[DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
</span><span>private</span> <span>static</span> <span>extern</span> <span>short</span> SQLFreeHandle(<span>short</span><span> hType, IntPtr handle);
[DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span>, CharSet =<span> System.Runtime.InteropServices.CharSet.Ansi)]
</span><span>private</span> <span>static</span> <span>extern</span> <span>short</span><span> SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
</span><span>short</span> inStringLength, System.Text.StringBuilder outString, <span>short</span><span> outStringLength,
</span><span>out</span> <span>short</span><span> outLengthNeeded);
</span><span>private</span> <span>const</span> <span>short</span> SQL_HANDLE_ENV = <span>1</span><span>;
</span><span>private</span> <span>const</span> <span>short</span> SQL_HANDLE_DBC = <span>2</span><span>;
</span><span>private</span> <span>const</span> <span>int</span> SQL_ATTR_ODBC_VERSION = <span>200</span><span>;
</span><span>private</span> <span>const</span> <span>int</span> SQL_OV_ODBC3 = <span>3</span><span>;
</span><span>private</span> <span>const</span> <span>short</span> SQL_SUCCESS = <span>0</span><span>;
</span><span>private</span> <span>const</span> <span>short</span> SQL_NEED_DATA = <span>99</span><span>;
</span><span>private</span> <span>const</span> <span>short</span> DEFAULT_RESULT_SIZE = <span>1024</span><span>;
</span><span>private</span> <span>const</span> <span>string</span> SQL_DRIVER_STR = <span>"</span><span>DRIVER=SQL SERVER</span><span>"</span><span>;
</span><span>#endregion</span>
<span>///</span> <span><summary></summary></span>
<span>///</span><span> 获取网内的数据库服务器名称(API方式)
</span><span>///</span> <span></span>
<span>///</span> <span><returns></returns></span><span>服务器名称数组</span><span></span>
<span>public</span> <span>static</span> <span>string</span><span>[] GetServers()
{
</span><span>string</span> list = <span>string</span><span>.Empty;
IntPtr henv </span>=<span> IntPtr.Zero;
IntPtr hconn </span>=<span> IntPtr.Zero;
System.Text.StringBuilder inString </span>= <span>new</span><span> System.Text.StringBuilder(SQL_DRIVER_STR);
System.Text.StringBuilder outString </span>= <span>new</span><span> System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
</span><span>short</span> inStringLength = (<span>short</span><span>)inString.Length;
</span><span>short</span> lenNeeded = <span>0</span><span>;
</span><span>try</span><span>
{
</span><span>if</span> (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, <span>out</span><span> henv))
{
</span><span>if</span> (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, <span>0</span><span>))
{
</span><span>if</span> (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, <span>out</span><span> hconn))
{
</span><span>if</span> (SQL_NEED_DATA ==<span> SQLBrowseConnect(hconn, inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, </span><span>out</span><span> lenNeeded))
{
</span><span>if</span> (DEFAULT_RESULT_SIZE lenNeeded)
{
outString.Capacity =<span> lenNeeded;
</span><span>if</span> (SQL_NEED_DATA !=<span> SQLBrowseConnect(hconn, inString, inStringLength, outString,
lenNeeded, </span><span>out</span><span> lenNeeded))
{
</span><span>throw</span> <span>new</span> ApplicationException(<span>"</span><span>Unabled to aquire SQL Servers from ODBC driver.</span><span>"</span><span>);
}
}
list </span>=<span> outString.ToString();
</span><span>int</span> start = list.IndexOf(<span>"</span><span>{</span><span>"</span>) + <span>1</span><span>;
</span><span>int</span> len = list.IndexOf(<span>"</span><span>}</span><span>"</span>) -<span> start;
</span><span>if</span> ((start > <span>0</span>) && (len > <span>0</span><span>))
{
list </span>=<span> list.Substring(start, len);
}
</span><span>else</span><span>
{
list </span>= <span>string</span><span>.Empty;
}
}
}
}
}
}
</span><span>catch</span><span>
{
list </span>= <span>string</span><span>.Empty;
}
</span><span>finally</span><span>
{
</span><span>if</span> (hconn !=<span> IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
</span><span>if</span> (henv !=<span> IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}
</span><span>string</span>[] array = <span>null</span><span>;
</span><span>if</span> (list.Length > <span>0</span><span>)
{
array </span>= list.Split(<span>''</span><span>,</span><span>''</span><span>);
}
</span><span>return</span><span> array;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 获取网内的数据库服务器名称(qlClientFactory方式)
</span><span>///</span> <span></span>
<span>///</span> <span><returns></returns></span><span>服务器名称数组</span><span></span>
<span>public</span> <span>static</span> <span>string</span><span>[] GetServersBySqlClientFactory()
{
DataTable dataSources </span>=<span> SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
DataColumn column2 </span>= dataSources.Columns[<span>"</span><span>ServerName</span><span>"</span><span>];
DataColumn column </span>= dataSources.Columns[<span>"</span><span>InstanceName</span><span>"</span><span>];
DataRowCollection rows </span>=<span> dataSources.Rows;
</span><span>string</span>[] array = <span>new</span> <span>string</span><span>[rows.Count];
</span><span>for</span> (<span>int</span> i = <span>0</span>; i )
{
<span>string</span> str2 = rows[i][column2] <span>as</span> <span>string</span><span>;
</span><span>string</span> str = rows[i][column] <span>as</span> <span>string</span><span>;
</span><span>if</span> (((str == <span>null</span>) || (str.Length == <span>0</span>)) || (<span>"</span><span>MSSQLSERVER</span><span>"</span> ==<span> str))
{
array[i] </span>=<span> str2;
}
</span><span>else</span><span>
{
array[i] </span>= str2 + <span>@"</span><span>\</span><span>"</span> +<span> str;
}
}
Array.Sort</span>string><span>(array);
</span><span>return</span><span> array;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 根据不带数据库的连接字符串，遍历查找出所有数据库实例
</span><span>///</span> <span></span>
<span>///</span> <span><returns></returns></span><span>指定服务器的所有数据库</span><span></span>
<span>public</span> <span>string</span><span>[] GetDataBases()
{
List</span>string> list = <span>new</span> Liststring><span>();
SqlConnection sqlConnection </span>= <span>new</span><span> SqlConnection(ConnectString);
</span><span>string</span><span>[] result;
</span><span>try</span><span>
{
sqlConnection.Open();
SqlCommand sqlCommand </span>= <span>new</span> SqlCommand(<span>"</span><span>select name AS 数据库 from master..sysdatabases</span><span>"</span><span>, sqlConnection);
SqlDataReader sqlDataReader </span>=<span> sqlCommand.ExecuteReader();
</span><span>while</span><span> (sqlDataReader.Read())
{
list.Add(sqlDataReader.GetString(</span><span>0</span><span>));
}
sqlDataReader.Close();
</span><span>string</span>[] array = <span>new</span> <span>string</span><span>[]
{
</span><span>"</span><span>master</span><span>"</span><span>,
</span><span>"</span><span>tempdb</span><span>"</span><span>,
</span><span>"</span><span>model</span><span>"</span><span>,
</span><span>"</span><span>msdb</span><span>"</span><span>
};
</span><span>string</span>[] array2 =<span> array;
</span><span>for</span> (<span>int</span> i = <span>0</span>; i )
{
<span>string</span> item =<span> array2[i];
</span><span>try</span><span>
{
list.Remove(item);
}
</span><span>catch</span><span>
{
}
}
result </span>=<span> list.ToArray();
}
</span><span>catch</span><span>
{
result </span>=<span> list.ToArray();
}
</span><span>finally</span><span>
{
sqlConnection.Close();
}
</span><span>return</span><span> result;
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 获取SqlServer指定数据库的所有表
</span><span>///</span> <span></span>
<span>///</span> <span><returns></returns></span><span>表集合,出错则产生异常</span><span></span>
<span>public</span> <span>string</span><span>[] GetTables()
{
</span><span>string</span> sql = <span>"</span><span>select object_name (id) from sysobjects where xtype = ''u'' and objectproperty (id,''IsMSShipped'') = 0</span><span>"</span><span>;
DataTable dt </span>=<span> ReturnDataTable(sql);
List</span>string> Ls = <span>new</span> Liststring><span>();
</span><span>for</span> (<span>int</span> i = <span>0</span>; i )
{
Ls.Add(dt.Rows[i][<span>0</span><span>].ToString());
}
</span><span>return</span><span> Ls.ToArray();
}
</span><span>///</span> <span><summary></summary></span>
<span>///</span><span> 获取指定表的所有列
</span><span>///</span> <span></span>
<span>///</span> <span><param name="TableName"></span><span>表名</span><span></span>
<span>///</span> <span><returns></returns></span><span>列集合,出错则产生异常</span><span></span>
<span>public</span> <span>string</span>[] GetColumns(<span>string</span><span> TableName)
{
</span><span>string</span> sql = <span>string</span>.Format(<span>"</span><span>select name from syscolumns where id=object_id(''{0}'')</span><span>"</span><span>,TableName);
</span><span>try</span><span>
{
List</span>string> Ls = <span>new</span> Liststring><span>();
DataTable dt </span>=<span> ReturnDataTable(sql);
</span><span>for</span> (<span>int</span> i = <span>0</span>; i )
{
Ls.Add(dt.Rows[i][<span>0</span><span>].ToString());
}
</span><span>return</span><span> Ls.ToArray();
}
</span><span>catch</span><span>
{
</span><span>throw</span><span>;
}
}</span>登录后复制
接下来就是许多比较常用的增删改查的操作了。就不分开写了。
为了查看方便，贴出自己所有代码：
View Code
using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Data; using System.Collections; using System.Runtime.InteropServices; namespace MyTool.DataBase { ////// SQL Server 数据库的操作类库。代码原创。 /// public class SqlServerHelper { #region[字段] private string connectstring = "Data Source=127.0.0.1;Initial Catalog=AdventureWorksDW;Integrated Security=True"; SqlConnection connect = null; SqlCommand command = null; private int CommandTimeOut = 30; #endregion #region[属性] /// /// 数据库连接字符串 /// public string ConnectString { get { return connectstring; } set { connectstring = value; } } #endregion <span>#region</span>[构造函数] <span>///</span> <span><summary></summary></span> <span>///</span><span> 构造函数，初始化 </span><span>///</span> <span></span> <span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span> <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span> <span>public</span> SqlServerHelper(<span>string</span> ConStr, <span>int</span><span> TimeOut) { ConnectString </span>=<span> ConStr; connect </span>= <span>new</span><span> SqlConnection(ConnectString); CommandTimeOut </span>=<span> TimeOut; } </span><span>///</span> <span><summary></summary></span> <span>///</span><span> 构造函数，初始化 </span><span>///</span> <span></span> <span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span> <span>public</span> SqlServerHelper(<span>string</span><span> ConStr) { ConnectString </span>=<span> ConStr; connect </span>= <span>new</span><span> SqlConnection(ConnectString); CommandTimeOut </span>= <span>30</span><span>; } </span><span>///</span> <span><summary></summary></span> <span>///</span><span> 构造函数，初始化 </span><span>///</span> <span></span> <span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span> <span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span> <span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span> <span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span> <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span> <span>public</span> SqlServerHelper(<span>string</span> DataServer, <span>string</span> DataBase, <span>string</span> UserID, <span>string</span> Pwd,<span>int</span><span> TimeOut) { ConnectString </span>= <span>string</span>.Format(<span>"</span><span>Data Source={0};Initial Catalog={1};User ID={2};pwd={3}</span><span>"</span><span>, DataServer, DataBase, UserID, Pwd); connect </span>= <span>new</span><span> SqlConnection(ConnectString); CommandTimeOut </span>=<span> TimeOut; } </span><span>///</span> <span><summary></summary></span> <span>///</span><span> 构造函数，初始化 </span><span>///</span> <span></span> <span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span> <span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span> <span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span> <span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span> <span>public</span> SqlServerHelper(<span>string</span> DataServer, <span>string</span> DataBase, <span>string</span> UserID, <span>string</span><span> Pwd) { ConnectString </span>= <span>string</span>.Format(<span>"</span><span>Data Source={0};Initial Catalog={1};User ID={2};pwd={3}</span><span>"</span><span>, DataServer, DataBase, UserID, Pwd); connect </span>= <span>new</span><span> SqlConnection(ConnectString); CommandTimeOut </span>= <span>30</span><span>; } </span><span>///</span> <span><summary></summary></span> <span>///</span><span> 构造函数，初始化 </span><span>///</span> <span></span> <span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span> <span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span> <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span> <span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span> isLocal,<span>int</span><span> TimeOut) { </span><span>if</span> (!<span>isLocal) { </span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>)); } connectstring </span>= <span>string</span>.Format(<span>"</span><span>Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True</span><span>"</span><span>, database); connect </span>= <span>new</span><span> SqlConnection(ConnectString); CommandTimeOut </span>=<span> TimeOut; } </span><span>///</span> <span><summary></summary></span> <span>///</span><span> 构造函数，初始化 </span><span>///</span> <span></span> <span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span> <span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span> <span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span><span> isLocal) { </span><span>if</span> (!<span>isLocal) { </span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>)); } connectstring </span>= <span>string</span>.Format(<span>"</span><span>Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True</span><span>"</span><span>, database); connect </span>= <span>new</span><span> SqlConnection(ConnectString); CommandTimeOut </span>= <span>30</span><span>; } </span><span>#endregion</span> #region[私有函数] private void Open() { try { if (connect.State != System.Data.ConnectionState.Open) { connect.Open(); } } catch (Exception ex) { throw (new Exception(ex.Message)); } } private void Close() { try { if (connect.State != System.Data.ConnectionState.Closed) { connect.Close(); } } catch (Exception ex) { throw (new Exception(ex.Message)); } } #endregion /// /// 测试是否能够连通 /// /// 布尔值 public bool ConnectTest() { try { connect.Open(); } catch { connect.Close(); return false; } return true; } /// /// 执行无返回的Sql语句，如插入，删除，更新 /// /// SQL语句 /// 受影响的条数 public int ExecuteNonQuery(string sqlstr) { try { Open(); command = new SqlCommand(sqlstr, connect); int num = command.ExecuteNonQuery(); command.Parameters.Clear(); Close(); return num; } catch { throw; } } /// /// 执行查询语句，返回DataSet /// /// Sql /// DataSet数据集 public DataSet ReturnDataSet(string sqlstr) { DataSet ds = new DataSet(); try { Open(); SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, connect); adapter.Fill(ds, "Obj"); } catch (Exception) { throw; } return ds; } /// /// 执行查询语句，返回DataTable /// /// Sqk /// DataTable数据表 public DataTable ReturnDataTable(string sqlstr) { return ReturnDataSet(sqlstr).Tables[0]; } /// /// 执行查询语句，返回DataReader /// /// Sql /// DataReader public SqlDataReader ReturnDataReader(string sqlstr) { try { Open(); command = new SqlCommand(sqlstr, connect); SqlDataReader myReader = command.ExecuteReader(); command.Parameters.Clear(); Close(); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } } /// /// 执行事务 /// /// public void ExecuteSqlTran(ArrayList SQLStringList) { Open(); command = new SqlCommand(); command.Connection = connect; SqlTransaction tx = connect.BeginTransaction(); command.Transaction = tx; try { for (int n = 0; n ) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { command.CommandText = strsql; command.ExecuteNonQuery(); } } tx.Commit(); } catch (Exception) { tx.Rollback(); throw; } } #region 供使用API方式时使用 [DllImport("odbc32.dll")] private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle); [DllImport("odbc32.dll")] private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength); [DllImport("odbc32.dll")] private static extern short SQLFreeHandle(short hType, IntPtr handle); [DllImport("odbc32.dll", CharSet = System.Runtime.InteropServices.CharSet.Ansi)] private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString, short inStringLength, System.Text.StringBuilder outString, short outStringLength, out short outLengthNeeded); private const short SQL_HANDLE_ENV = 1; private const short SQL_HANDLE_DBC = 2; private const int SQL_ATTR_ODBC_VERSION = 200; private const int SQL_OV_ODBC3 = 3; private const short SQL_SUCCESS = 0; private const short SQL_NEED_DATA = 99; private const short DEFAULT_RESULT_SIZE = 1024; private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER"; #endregion /// /// 获取网内的数据库服务器名称(API方式) /// /// 服务器名称数组 public static string[] GetServers() { string list = string.Empty; IntPtr henv = IntPtr.Zero; IntPtr hconn = IntPtr.Zero; System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR); System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE); short inStringLength = (short)inString.Length; short lenNeeded = 0; try { if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv)) { if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0)) { if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn)) { if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString, DEFAULT_RESULT_SIZE, out lenNeeded)) { if (DEFAULT_RESULT_SIZE lenNeeded) { outString.Capacity = lenNeeded; if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString, lenNeeded, out lenNeeded)) { throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver."); } } list = outString.ToString(); int start = list.IndexOf("{") + 1; int len = list.IndexOf("}") - start; if ((start > 0) && (len > 0)) { list = list.Substring(start, len); } else { list = string.Empty; } } } } } } catch { list = string.Empty; } finally { if (hconn != IntPtr.Zero) { SQLFreeHandle(SQL_HANDLE_DBC, hconn); } if (henv != IntPtr.Zero) { SQLFreeHandle(SQL_HANDLE_ENV, hconn); } } string[] array = null; if (list.Length > 0) { array = list.Split('',''); } return array; } /// /// 获取网内的数据库服务器名称(qlClientFactory方式) /// /// 服务器名称数组 public static string[] GetServersBySqlClientFactory() { DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources(); DataColumn column2 = dataSources.Columns["ServerName"]; DataColumn column = dataSources.Columns["InstanceName"]; DataRowCollection rows = dataSources.Rows; string[] array = new string[rows.Count]; for (int i = 0; i ) { string str2 = rows[i][column2] as string; string str = rows[i][column] as string; if (((str == null) || (str.Length == 0)) || ("MSSQLSERVER" == str)) { array[i] = str2; } else { array[i] = str2 + @"\" + str; } } Array.Sortstring>(array); return array; } /// /// 根据不带数据库的连接字符串，遍历查找出所有数据库实例 /// /// 指定服务器的所有数据库 public string[] GetDataBases() { Liststring> list = new Liststring>(); SqlConnection sqlConnection = new SqlConnection(ConnectString); string[] result; try { sqlConnection.Open(); SqlCommand sqlCommand = new SqlCommand("select name AS 数据库 from master..sysdatabases", sqlConnection); SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(); while (sqlDataReader.Read()) { list.Add(sqlDataReader.GetString(0)); } sqlDataReader.Close(); string[] array = new string[] { "master", "tempdb", "model", "msdb" }; string[] array2 = array; for (int i = 0; i ) { string item = array2[i]; try { list.Remove(item); } catch { } } result = list.ToArray(); } catch { result = list.ToArray(); } finally { sqlConnection.Close(); } return result; } /// /// 获取SqlServer指定数据库的所有表 /// /// 表集合,出错则产生异常 public string[] GetTables() { string sql = "select object_name (id) from sysobjects where xtype = ''u'' and objectproperty (id,''IsMSShipped'') = 0"; DataTable dt = ReturnDataTable(sql); Liststring> Ls = new Liststring>(); for (int i = 0; i ) { Ls.Add(dt.Rows[i][0].ToString()); } return Ls.ToArray(); } /// /// 获取指定表的所有列 /// /// 表名 /// 列集合,出错则产生异常 public string[] GetColumns(string TableName) { string sql = string.Format("select name from syscolumns where id=object_id(''{0}'')",TableName); try { Liststring> Ls = new Liststring>(); DataTable dt = ReturnDataTable(sql); for (int i = 0; i ) { Ls.Add(dt.Rows[i][0].ToString()); } return Ls.ToArray(); } catch { throw; } } } }
登录后复制
C#访问SqlServer的工具类SqlServerHelper
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
class MySqlHelper:IDisposable
{
private MySqlConnection m_conn = null;
private MySqlTransaction m_trans = null;
private bool m_tran_enabled = false;
public MySqlHelper()
{
m_conn = new MySqlConnection();
m_conn.ConnectionString = "Server=localhost;Port=3301;Uid=sa;Pwd=000";
m_conn.Open();
}
public void BeginTrans()
{
m_trans = m_conn.BeginTransaction();
m_tran_enabled = true;
}
public void Commit()
{
if (m_trans != null && m_tran_enabled)
{
m_tran_enabled = false;
m_trans.Commit();
}
}
public void Rollback()
{
if (m_trans != null && m_tran_enabled)
{
m_tran_enabled = false;
m_trans.Rollback();
}
}
public object QuerySome(string sql,int fieldindex)
{
using (MySqlCommand cmd = new MySqlCommand(sql, m_conn))
{
using (MySqlDataReader sr = cmd.ExecuteReader())
{
if (sr.Read())
{
return sr.GetValue(fieldindex);
}
}
}
return null;
}
public delegate void FillValues(MySqlDataReader sr);
public void QuerySomes(string sql, FillValues fill)
{
using (MySqlCommand cmd = new MySqlCommand(sql, m_conn))
{
using (MySqlDataReader sr = cmd.ExecuteReader())
{
fill(sr);
}
}
}
public DataTable Source(string sql)
{
DataTable dt = null;
MySqlCommand cmd = null;
MySqlDataAdapter ad = null;
try
{
lock (dt = new DataTable())
{
cmd = new MySqlCommand(sql, m_conn);
ad = new MySqlDataAdapter((MySqlCommand)cmd);
dt.Clear();
ad.Fill(dt);
}
}
catch (Exception e)
{
throw e;
}
return dt;
}
public void ExecProc(string proc, params MySqlParameter[] ps)
{
using (MySqlCommand cmd = new MySqlCommand(proc, m_conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
foreach (MySqlParameter p in ps)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
}
void IDisposable.Dispose()
{
m_conn.Close();
m_conn.Dispose();
if (m_trans != null)
{
m_trans.Dispose();
}
}
}登录后复制
DBSyncer 中间件 SQLServer同步SQLServer 配置教程
DBSyncer 项目地址：https://gitee.com/ghi/dbsyncer
SqlServer 配置
原理：SQL Server 2008提供了内建的方法变更数据捕获（Change Data Capture 即CDC）以实现异步跟踪用户表的数据修改。
要求2008版本以上, 启动代理服务（Agent服务）, 连接账号具有 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员身份。对于所有其他用户，具有源表SELECT 权限；如果已定义捕获实例的访问控制角色，则还要求具有该数据库角色的成员身份。
打开CDC功能
windows环境
快捷键windows + r，输入 services.msc 进入系统服务窗口
services.msc
启动代理服务，默认也会启动数据库服务
服务状态为正在运行
Linux环境也是开启上面的2个服务
SqlServer服务配置修改完成了！接下来配置驱动
准备2张表，分别是MY_USER和MY_ORG，计划将MY_USER的数据同步到MY_ORG中
1、进入dbs页面，添加SqlServer连接
2、因为我的2张表都是一个库里，所以这里我只需创建1个连接
3、添加驱动
4、添加表映射关系，进入表字段映射页面查看一下，点击保存
5、保存返回到驱动管理页面
6、启动驱动，开始全量同步
执行完成
7、检查一下表数据是否都同步过来了
准备测试增量同步
1、切换为增量同步，返回
2、启动驱动
3、手动修改数据，验证增删改事件
验证插入数据
INSERT INTO [dbo].[MY_USER]([id], [name], [age], [money], [create_date], [remark], [time], [d_time], [img])
VALUES (1000006, ''验证增量同步'', 9, 99.99, ''2022-07-24'', ''hello'', ''14:23:39'', ''2022-07-24 14:23:41'', NULL);
查看一下MY_ORG表，有新记录
查看监控，也能看到日志
继续验证修改数据
查看一下MY_ORG表，有新变更
验证删除数据
最后再看一下监控
恭喜您，学完了 SQLServer同步SQLServer 配置教程！
下期见! AE86 ☕
SQL Server 2014使用资源调控器对IOPS控制
在SQLServer2008中引入了ResourceGovernor，通过对一些语句的CPU和内存的资源限制瞻性的对多租户的数据库环境进行有效管理。这个特性允许客户实现数据库的整合或
GO--CreateclassifierfunctionASIFIFRETURNRETURNEND;ALTERRESOURCEGOVERNORWITHGO--SetdefaultvaluesfortheresourcepoolssothatIORGisenabled.GO
GO
原文来自：
，今天关于SQLServer 资源调控器和sql server资源管理器怎么打开的讲解已经结束，谢谢您的阅读，如果想了解更多关于C# SqlServer操作辅助类(SqlServerHelper.cs)、C#访问SqlServer的工具类SqlServerHelper、DBSyncer 中间件 SQLServer同步SQLServer 配置教程、SQL Server 2014使用资源调控器对IOPS控制的相关知识，请在本站搜索。
本文标签：
