当前位置：Gxlcms > mysql > C#SqlServer操作辅助类(SqlServerHelper.cs)

C#SqlServer操作辅助类(SqlServerHelper.cs)

时间：2021-07-01 10:21:17 帮助过：39人阅读

开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了SqlCeHelper这个关于操作本地数据库sdf的例子。现在介绍一下在Sqlserver中的代码。 因为Sqlserver数据库有多种登录方式，所以在构造函数中： #region [构造函数] /// summar

开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了SqlCeHelper这个关于操作本地数据库sdf的例子。现在介绍一下在Sqlserver中的代码。

因为Sqlserver数据库有多种登录方式，所以在构造函数中：

  1.   <span>#region</span>[构造函数]
  2.         <span>///</span> <span><summary>
  3.         <span>///</span><span> 构造函数，初始化
  4.         </span><span>///</span> <span></span></summary></span>
  5.         <span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span>
  6.         <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
  7.         <span>public</span> SqlServerHelper(<span>string</span> ConStr, <span>int</span><span> TimeOut)
  8.         {
  9.             ConnectString </span>=<span> ConStr;
  10.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  11.             CommandTimeOut </span>=<span> TimeOut;
  12.         }
  13.         </span><span>///</span> <span><summary>
  14.         <span>///</span><span> 构造函数，初始化
  15.         </span><span>///</span> <span></span></summary></span>
  16.         <span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span>
  17.         <span>public</span> SqlServerHelper(<span>string</span><span> ConStr)
  18.         {
  19.             ConnectString </span>=<span> ConStr;
  20.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  21.             CommandTimeOut </span>= <span>30</span><span>;
  22.         }
  23.         </span><span>///</span> <span><summary>
  24.         <span>///</span><span> 构造函数，初始化
  25.         </span><span>///</span> <span></span></summary></span>
  26.         <span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span>
  27.         <span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span>
  28.         <span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span>
  29.         <span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span>
  30.         <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
  31.         <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)
  32.         {
  33.             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);
  34.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  35.             CommandTimeOut </span>=<span> TimeOut;
  36.         }
  37.         </span><span>///</span> <span><summary>
  38.         <span>///</span><span> 构造函数，初始化
  39.         </span><span>///</span> <span></span></summary></span>
  40.         <span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span>
  41.         <span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span>
  42.         <span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span>
  43.         <span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span>
  44.         <span>public</span> SqlServerHelper(<span>string</span> DataServer, <span>string</span> DataBase, <span>string</span> UserID, <span>string</span><span> Pwd)
  45.         {
  46.             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);
  47.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  48.             CommandTimeOut </span>= <span>30</span><span>;          
  49.         }
  50.         </span><span>///</span> <span><summary>
  51.         <span>///</span><span> 构造函数，初始化
  52.         </span><span>///</span> <span></span></summary></span>
  53.         <span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span>
  54.         <span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span>
  55.         <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
  56.         <span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span> isLocal,<span>int</span><span> TimeOut)
  57.         {
  58.             </span><span>if</span> (!<span>isLocal)
  59.             {
  60.                 </span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>));
  61.             }
  62.             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);
  63.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  64.             CommandTimeOut </span>=<span> TimeOut;
  65.         }
  66.         </span><span>///</span> <span><summary>
  67.         <span>///</span><span> 构造函数，初始化
  68.         </span><span>///</span> <span></span></summary></span>
  69.         <span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span>
  70.         <span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span>
  71.         <span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span><span> isLocal)
  72.         {
  73.               </span><span>if</span> (!<span>isLocal)
  74.             {
  75.                 </span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>));
  76.             }
  77.             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);
  78.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  79.             CommandTimeOut </span>= <span>30</span><span>;
  80.         }
  81.         </span><span>#endregion</span>

这样，可以方便地构造出自己的连接字符串。

同时这里用到几个比较有用的有关数据库服务器及数据库结构的函数。比如查询局域网中所有数据库实例，获取目标实例所有数据库，获取指定数据库的所有表，获取指定表所有行：

View Code 

  1.  <span>#region</span> 供使用API方式时使用<span>
  2.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
  3.         </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);
  4.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
  5.         </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);
  6.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
  7.         </span><span>private</span> <span>static</span> <span>extern</span> <span>short</span> SQLFreeHandle(<span>short</span><span> hType, IntPtr handle);
  8.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span>, CharSet =<span> System.Runtime.InteropServices.CharSet.Ansi)]
  9.         </span><span>private</span> <span>static</span> <span>extern</span> <span>short</span><span> SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
  10.             </span><span>short</span> inStringLength, System.Text.StringBuilder outString, <span>short</span><span> outStringLength,
  11.             </span><span>out</span> <span>short</span><span> outLengthNeeded);
  12.         </span><span>private</span> <span>const</span> <span>short</span> SQL_HANDLE_ENV = <span>1</span><span>;
  13.         </span><span>private</span> <span>const</span> <span>short</span> SQL_HANDLE_DBC = <span>2</span><span>;
  14.         </span><span>private</span> <span>const</span> <span>int</span> SQL_ATTR_ODBC_VERSION = <span>200</span><span>;
  15.         </span><span>private</span> <span>const</span> <span>int</span> SQL_OV_ODBC3 = <span>3</span><span>;
  16.         </span><span>private</span> <span>const</span> <span>short</span> SQL_SUCCESS = <span>0</span><span>;
  17.         </span><span>private</span> <span>const</span> <span>short</span> SQL_NEED_DATA = <span>99</span><span>;
  18.         </span><span>private</span> <span>const</span> <span>short</span> DEFAULT_RESULT_SIZE = <span>1024</span><span>;
  19.         </span><span>private</span> <span>const</span> <span>string</span> SQL_DRIVER_STR = <span>"</span><span>DRIVER=SQL SERVER</span><span>"</span><span>;
  20.         </span><span>#endregion</span>
  21.         <span>///</span> <span><summary>
  22.         <span>///</span><span> 获取网内的数据库服务器名称(API方式)
  23.         </span><span>///</span> <span></span></summary></span>
  24.         <span>///</span> <span><returns></returns></span><span>服务器名称数组</span><span></span>
  25.         <span>public</span> <span>static</span> <span>string</span><span>[] GetServers()
  26.         {
  27.             </span><span>string</span> list = <span>string</span><span>.Empty;
  28.             IntPtr henv </span>=<span> IntPtr.Zero;
  29.             IntPtr hconn </span>=<span> IntPtr.Zero;
  30.             System.Text.StringBuilder inString </span>= <span>new</span><span> System.Text.StringBuilder(SQL_DRIVER_STR);
  31.             System.Text.StringBuilder outString </span>= <span>new</span><span> System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
  32.             </span><span>short</span> inStringLength = (<span>short</span><span>)inString.Length;
  33.             </span><span>short</span> lenNeeded = <span>0</span><span>;
  34.             </span><span>try</span><span>
  35.             {
  36.                 </span><span>if</span> (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, <span>out</span><span> henv))
  37.                 {
  38.                     </span><span>if</span> (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, <span>0</span><span>))
  39.                     {
  40.                         </span><span>if</span> (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, <span>out</span><span> hconn))
  41.                         {
  42.                             </span><span>if</span> (SQL_NEED_DATA ==<span> SQLBrowseConnect(hconn, inString, inStringLength, outString,
  43.                                  DEFAULT_RESULT_SIZE, </span><span>out</span><span> lenNeeded))
  44.                             {
  45.                                 </span><span>if</span> (DEFAULT_RESULT_SIZE <<span> lenNeeded)
  46.                                 {
  47.                                     outString.Capacity </span>=<span> lenNeeded;
  48.                                     </span><span>if</span> (SQL_NEED_DATA !=<span> SQLBrowseConnect(hconn, inString, inStringLength, outString,
  49.                                          lenNeeded, </span><span>out</span><span> lenNeeded))
  50.                                     {
  51.                                         </span><span>throw</span> <span>new</span> ApplicationException(<span>"</span><span>Unabled to aquire SQL Servers from ODBC driver.</span><span>"</span><span>);
  52.                                     }
  53.                                 }
  54.                                 list </span>=<span> outString.ToString();
  55.                                 </span><span>int</span> start = list.IndexOf(<span>"</span><span>{</span><span>"</span>) + <span>1</span><span>;
  56.                                 </span><span>int</span> len = list.IndexOf(<span>"</span><span>}</span><span>"</span>) -<span> start;
  57.                                 </span><span>if</span> ((start > <span>0</span>) && (len > <span>0</span><span>))
  58.                                 {
  59.                                     list </span>=<span> list.Substring(start, len);
  60.                                 }
  61.                                 </span><span>else</span><span>
  62.                                 {
  63.                                     list </span>= <span>string</span><span>.Empty;
  64.                                 }
  65.                             }
  66.                         }
  67.                     }
  68.                 }
  69.             }
  70.             </span><span>catch</span><span>
  71.             {
  72.                 list </span>= <span>string</span><span>.Empty;
  73.             }
  74.             </span><span>finally</span><span>
  75.             {
  76.                 </span><span>if</span> (hconn !=<span> IntPtr.Zero)
  77.                 {
  78.                     SQLFreeHandle(SQL_HANDLE_DBC, hconn);
  79.                 }
  80.                 </span><span>if</span> (henv !=<span> IntPtr.Zero)
  81.                 {
  82.                     SQLFreeHandle(SQL_HANDLE_ENV, hconn);
  83.                 }
  84.             }
  85.             </span><span>string</span>[] array = <span>null</span><span>;
  86.             </span><span>if</span> (list.Length > <span>0</span><span>)
  87.             {
  88.                 array </span>= list.Split(<span>'</span><span>,</span><span>'</span><span>);
  89.             }
  90.             </span><span>return</span><span> array;
  91.         }
  92.         </span><span>///</span> <span><summary>
  93.         <span>///</span><span> 获取网内的数据库服务器名称(qlClientFactory方式)
  94.         </span><span>///</span> <span></span></summary></span>
  95.         <span>///</span> <span><returns></returns></span><span>服务器名称数组</span><span></span>
  96.         <span>public</span> <span>static</span> <span>string</span><span>[] GetServersBySqlClientFactory()
  97.         {
  98.             DataTable dataSources </span>=<span> SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
  99.             DataColumn column2 </span>= dataSources.Columns[<span>"</span><span>ServerName</span><span>"</span><span>];
  100.             DataColumn column </span>= dataSources.Columns[<span>"</span><span>InstanceName</span><span>"</span><span>];
  101.             DataRowCollection rows </span>=<span> dataSources.Rows;
  102.             </span><span>string</span>[] array = <span>new</span> <span>string</span><span>[rows.Count];
  103.             </span><span>for</span> (<span>int</span> i = <span>0</span>; i < array.Length; i++<span>)
  104.             {
  105.                 </span><span>string</span> str2 = rows[i][column2] <span>as</span> <span>string</span><span>;
  106.                 </span><span>string</span> str = rows[i][column] <span>as</span> <span>string</span><span>;
  107.                 </span><span>if</span> (((str == <span>null</span>) || (str.Length == <span>0</span>)) || (<span>"</span><span>MSSQLSERVER</span><span>"</span> ==<span> str))
  108.                 {
  109.                     array[i] </span>=<span> str2;
  110.                 }
  111.                 </span><span>else</span><span>
  112.                 {
  113.                     array[i] </span>= str2 + <span>@"</span><span>\</span><span>"</span> +<span> str;
  114.                 }
  115.             }
  116.             Array.Sort</span><<span>string</span>><span>(array);
  117.             </span><span>return</span><span> array;
  118.         }
  119.         </span><span>///</span> <span><summary>
  120.         <span>///</span><span> 根据不带数据库的连接字符串，遍历查找出所有数据库实例
  121.         </span><span>///</span> <span></span></summary></span>
  122.         <span>///</span> <span><returns></returns></span><span>指定服务器的所有数据库</span><span></span>
  123.         <span>public</span> <span>string</span><span>[] GetDataBases()
  124.         {
  125.             List</span><<span>string</span>> list = <span>new</span> List<<span>string</span>><span>();
  126.             SqlConnection sqlConnection </span>= <span>new</span><span> SqlConnection(ConnectString);
  127.             </span><span>string</span><span>[] result;
  128.             </span><span>try</span><span>
  129.             {
  130.                 sqlConnection.Open();
  131.                 SqlCommand sqlCommand </span>= <span>new</span> SqlCommand(<span>"</span><span>select  name AS 数据库 from master..sysdatabases</span><span>"</span><span>, sqlConnection);
  132.                 SqlDataReader sqlDataReader </span>=<span> sqlCommand.ExecuteReader();
  133.                 </span><span>while</span><span> (sqlDataReader.Read())
  134.                 {
  135.                     list.Add(sqlDataReader.GetString(</span><span>0</span><span>));
  136.                 }
  137.                 sqlDataReader.Close();
  138.                 </span><span>string</span>[] array = <span>new</span> <span>string</span><span>[]
  139.                 {
  140.                     </span><span>"</span><span>master</span><span>"</span><span>, 
  141.                     </span><span>"</span><span>tempdb</span><span>"</span><span>, 
  142.                     </span><span>"</span><span>model</span><span>"</span><span>, 
  143.                     </span><span>"</span><span>msdb</span><span>"</span><span>
  144.                 };
  145.                 </span><span>string</span>[] array2 =<span> array;
  146.                 </span><span>for</span> (<span>int</span> i = <span>0</span>; i < array2.Length; i++<span>)
  147.                 {
  148.                     </span><span>string</span> item =<span> array2[i];
  149.                     </span><span>try</span><span>
  150.                     {
  151.                         list.Remove(item);
  152.                     }
  153.                     </span><span>catch</span><span>
  154.                     {
  155.                     }
  156.                 }
  157.                 result </span>=<span> list.ToArray();
  158.             }
  159.             </span><span>catch</span><span>
  160.             {
  161.                 result </span>=<span> list.ToArray();
  162.             }
  163.             </span><span>finally</span><span>
  164.             {
  165.                 sqlConnection.Close();
  166.             }
  167.             </span><span>return</span><span> result;
  168.         }
  169.         </span><span>///</span> <span><summary>
  170.         <span>///</span><span> 获取SqlServer指定数据库的所有表
  171.         </span><span>///</span> <span></span></summary></span>
  172.         <span>///</span> <span><returns></returns></span><span>表集合,出错则产生异常</span><span></span>
  173.         <span>public</span> <span>string</span><span>[] GetTables()
  174.         {
  175.             </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>;
  176.             DataTable dt </span>=<span> ReturnDataTable(sql);
  177.             List</span><<span>string</span>> Ls = <span>new</span> List<<span>string</span>><span>();
  178.             </span><span>for</span> (<span>int</span> i = <span>0</span>; i < dt.Rows.Count; i++<span>)
  179.             {
  180.                 Ls.Add(dt.Rows[i][</span><span>0</span><span>].ToString());
  181.             }
  182.             </span><span>return</span><span> Ls.ToArray();           
  183.         }
  184.         </span><span>///</span> <span><summary>
  185.         <span>///</span><span> 获取指定表的所有列
  186.         </span><span>///</span> <span></span></summary></span>
  187.         <span>///</span> <span><param name="TableName"></span><span>表名</span><span></span>
  188.         <span>///</span> <span><returns></returns></span><span>列集合,出错则产生异常</span><span></span>
  189.         <span>public</span> <span>string</span>[] GetColumns(<span>string</span><span> TableName)
  190.         {
  191.             </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);
  192.             </span><span>try</span><span>
  193.             {
  194.                 List</span><<span>string</span>> Ls = <span>new</span> List<<span>string</span>><span>();
  195.                 DataTable dt </span>=<span> ReturnDataTable(sql);
  196.                 </span><span>for</span> (<span>int</span> i = <span>0</span>; i < dt.Rows.Count; i++<span>)
  197.                 {
  198.                     Ls.Add(dt.Rows[i][</span><span>0</span><span>].ToString());
  199.                 }
  200.                 </span><span>return</span><span> Ls.ToArray();
  201.             }
  202.             </span><span>catch</span><span>
  203.             {
  204.                 
  205.                 </span><span>throw</span><span>;
  206.             }
  207.         }</span>

接下来就是许多比较常用的增删改查的操作了。就不分开写了。

为了查看方便，贴出自己所有代码：

View Code 

  1. <span>using</span><span> System;
  2. </span><span>using</span><span> System.Collections.Generic;
  3. </span><span>using</span><span> System.Data.SqlClient;
  4. </span><span>using</span><span> System.Data;
  5. </span><span>using</span><span> System.Collections;
  6. </span><span>using</span><span> System.Runtime.InteropServices;
  7. </span><span>namespace</span><span> MyTool.DataBase
  8. {
  9.     </span><span>///</span> <span><summary>
  10.     <span>///</span><span> SQL Server 数据库的操作类库。代码原创。
  11.     </span><span>///</span> <span></span></summary></span>
  12.     <span>public</span> <span>class</span><span> SqlServerHelper
  13.     {
  14.         </span><span>#region</span>[字段]
  15.         <span>private</span> <span>string</span> connectstring = <span>"</span><span>Data Source=127.0.0.1;Initial Catalog=AdventureWorksDW;Integrated Security=True</span><span>"</span><span>;      
  16.         SqlConnection connect </span>= <span>null</span><span>;
  17.         SqlCommand command </span>= <span>null</span><span>;
  18.         </span><span>private</span> <span>int</span> CommandTimeOut = <span>30</span><span>;
  19.         </span><span>#endregion</span>
  20.         <span>#region</span>[属性]
  21.         <span>///</span> <span><summary>
  22.         <span>///</span><span> 数据库连接字符串
  23.         </span><span>///</span> <span></span></summary></span>
  24.         <span>public</span> <span>string</span><span> ConnectString
  25.         {
  26.             </span><span>get</span> { <span>return</span><span> connectstring; }
  27.             </span><span>set</span> { connectstring =<span> value; }
  28.         }
  29.         </span><span>#endregion</span>
  30.         <span>#region</span>[构造函数]
  31.         <span>///</span> <span><summary>
  32.         <span>///</span><span> 构造函数，初始化
  33.         </span><span>///</span> <span></span></summary></span>
  34.         <span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span>
  35.         <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
  36.         <span>public</span> SqlServerHelper(<span>string</span> ConStr, <span>int</span><span> TimeOut)
  37.         {
  38.             ConnectString </span>=<span> ConStr;
  39.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  40.             CommandTimeOut </span>=<span> TimeOut;
  41.         }
  42.         </span><span>///</span> <span><summary>
  43.         <span>///</span><span> 构造函数，初始化
  44.         </span><span>///</span> <span></span></summary></span>
  45.         <span>///</span> <span><param name="ConStr"></span><span>正确的数据库连接字符串</span><span></span>
  46.         <span>public</span> SqlServerHelper(<span>string</span><span> ConStr)
  47.         {
  48.             ConnectString </span>=<span> ConStr;
  49.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  50.             CommandTimeOut </span>= <span>30</span><span>;
  51.         }
  52.         </span><span>///</span> <span><summary>
  53.         <span>///</span><span> 构造函数，初始化
  54.         </span><span>///</span> <span></span></summary></span>
  55.         <span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span>
  56.         <span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span>
  57.         <span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span>
  58.         <span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span>
  59.         <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
  60.         <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)
  61.         {
  62.             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);
  63.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  64.             CommandTimeOut </span>=<span> TimeOut;
  65.         }
  66.         </span><span>///</span> <span><summary>
  67.         <span>///</span><span> 构造函数，初始化
  68.         </span><span>///</span> <span></span></summary></span>
  69.         <span>///</span> <span><param name="DataServer"></span><span>数据库服务器名称或地址</span><span></span>
  70.         <span>///</span> <span><param name="DataBase"></span><span>访问的数据库</span><span></span>
  71.         <span>///</span> <span><param name="UserID"></span><span>用户名</span><span></span>
  72.         <span>///</span> <span><param name="Pwd"></span><span>密码</span><span></span>
  73.         <span>public</span> SqlServerHelper(<span>string</span> DataServer, <span>string</span> DataBase, <span>string</span> UserID, <span>string</span><span> Pwd)
  74.         {
  75.             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);
  76.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  77.             CommandTimeOut </span>= <span>30</span><span>;          
  78.         }
  79.         </span><span>///</span> <span><summary>
  80.         <span>///</span><span> 构造函数，初始化
  81.         </span><span>///</span> <span></span></summary></span>
  82.         <span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span>
  83.         <span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span>
  84.         <span>///</span> <span><param name="TimeOut"></span><span>超时时间</span><span></span>
  85.         <span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span> isLocal,<span>int</span><span> TimeOut)
  86.         {
  87.             </span><span>if</span> (!<span>isLocal)
  88.             {
  89.                 </span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>));
  90.             }
  91.             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);
  92.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  93.             CommandTimeOut </span>=<span> TimeOut;
  94.         }
  95.         </span><span>///</span> <span><summary>
  96.         <span>///</span><span> 构造函数，初始化
  97.         </span><span>///</span> <span></span></summary></span>
  98.         <span>///</span> <span><param name="database"></span><span>访问的数据库</span><span></span>
  99.         <span>///</span> <span><param name="isLocal"></span><span>是否为本地数据库？若False，则抛出异常</span><span></span>
  100.         <span>public</span> SqlServerHelper(<span>string</span> database, <span>bool</span><span> isLocal)
  101.         {
  102.               </span><span>if</span> (!<span>isLocal)
  103.             {
  104.                 </span><span>throw</span> (<span>new</span> Exception(<span>"</span><span>不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。</span><span>"</span><span>));
  105.             }
  106.             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);
  107.             connect </span>= <span>new</span><span> SqlConnection(ConnectString);
  108.             CommandTimeOut </span>= <span>30</span><span>;
  109.         }
  110.         </span><span>#endregion</span>
  111.         <span>#region</span>[私有函数]
  112.         <span>private</span> <span>void</span><span> Open()
  113.         {
  114.             </span><span>try</span><span>
  115.             {
  116.                 </span><span>if</span> (connect.State !=<span> System.Data.ConnectionState.Open)
  117.                 {
  118.                     connect.Open();
  119.                 }
  120.             }
  121.             </span><span>catch</span><span> (Exception ex)
  122.             {
  123.                 </span><span>throw</span> (<span>new</span><span> Exception(ex.Message));
  124.             }
  125.         }
  126.         </span><span>private</span> <span>void</span><span> Close()
  127.         {
  128.             </span><span>try</span><span>
  129.             {
  130.                 </span><span>if</span> (connect.State !=<span> System.Data.ConnectionState.Closed)
  131.                 {
  132.                     connect.Close();
  133.                 }
  134.             }
  135.             </span><span>catch</span><span> (Exception ex)
  136.             {
  137.                 </span><span>throw</span> (<span>new</span><span> Exception(ex.Message));
  138.             }
  139.         }
  140.         </span><span>#endregion</span>
  141.         <span>///</span> <span><summary>
  142.         <span>///</span><span> 测试是否能够连通
  143.         </span><span>///</span> <span></span></summary></span>
  144.         <span>///</span> <span><returns></returns></span><span>布尔值</span><span></span>
  145.         <span>public</span> <span>bool</span><span> ConnectTest()
  146.         {
  147.             </span><span>try</span><span>
  148.             {
  149.                 connect.Open();
  150.             }
  151.             </span><span>catch</span><span>
  152.             {
  153.                 connect.Close();
  154.                 </span><span>return</span> <span>false</span><span>;
  155.             }
  156.             </span><span>return</span> <span>true</span><span>;
  157.         }
  158.         </span><span>///</span> <span><summary>
  159.         <span>///</span><span> 执行无返回的Sql语句，如插入，删除，更新
  160.         </span><span>///</span> <span></span></summary></span>
  161.         <span>///</span> <span><param name="sqlstr"></span><span>SQL语句</span><span></span>
  162.         <span>///</span> <span><returns></returns></span><span>受影响的条数</span><span></span>
  163.         <span>public</span> <span>int</span> ExecuteNonQuery(<span>string</span><span> sqlstr)
  164.         {
  165.             </span><span>try</span><span>
  166.             {
  167.                 Open();
  168.                 command </span>= <span>new</span><span> SqlCommand(sqlstr, connect);
  169.                 </span><span>int</span> num =<span> command.ExecuteNonQuery();
  170.                 command.Parameters.Clear();
  171.                 Close();
  172.                 </span><span>return</span><span> num;
  173.             }
  174.             </span><span>catch</span><span>
  175.             {
  176.                 </span><span>throw</span><span>;
  177.             }
  178.         }
  179.         </span><span>///</span> <span><summary>
  180.         <span>///</span><span> 执行查询语句，返回DataSet
  181.         </span><span>///</span> <span></span></summary></span>
  182.         <span>///</span> <span><param name="sqlstr"></span><span>Sql</span><span></span>
  183.         <span>///</span> <span><returns></returns></span><span>DataSet数据集</span><span></span>
  184.         <span>public</span> DataSet ReturnDataSet(<span>string</span><span> sqlstr)
  185.         {
  186.             DataSet ds </span>= <span>new</span><span> DataSet();
  187.             </span><span>try</span><span>
  188.             {
  189.                 Open();
  190.                 SqlDataAdapter adapter </span>= <span>new</span><span> SqlDataAdapter(sqlstr, connect);
  191.                 adapter.Fill(ds, </span><span>"</span><span>Obj</span><span>"</span><span>);
  192.             }
  193.             </span><span>catch</span><span> (Exception)
  194.             {
  195.                 </span><span>throw</span><span>;
  196.             }
  197.             </span><span>return</span><span> ds;
  198.         }
  199.         </span><span>///</span> <span><summary>
  200.         <span>///</span><span> 执行查询语句，返回DataTable
  201.         </span><span>///</span> <span></span></summary></span>
  202.         <span>///</span> <span><param name="sqlstr"></span><span>Sqk</span><span></span>
  203.         <span>///</span> <span><returns></returns></span><span>DataTable数据表</span><span></span>
  204.         <span>public</span> DataTable ReturnDataTable(<span>string</span><span> sqlstr)
  205.         {
  206.             </span><span>return</span> ReturnDataSet(sqlstr).Tables[<span>0</span><span>];
  207.         }
  208.         </span><span>///</span> <span><summary>
  209.         <span>///</span><span> 执行查询语句，返回DataReader
  210.         </span><span>///</span> <span></span></summary></span>
  211.         <span>///</span> <span><param name="sqlstr"></span><span>Sql</span><span></span>
  212.         <span>///</span> <span><returns></returns></span><span>DataReader</span><span></span>
  213.         <span>public</span> SqlDataReader ReturnDataReader(<span>string</span><span> sqlstr)
  214.         {
  215.             </span><span>try</span><span>
  216.             {
  217.                 Open();
  218.                 command </span>= <span>new</span><span> SqlCommand(sqlstr, connect);
  219.                 SqlDataReader myReader </span>=<span> command.ExecuteReader();
  220.                 command.Parameters.Clear();
  221.                 Close();
  222.                 </span><span>return</span><span> myReader;
  223.             }
  224.             </span><span>catch</span><span> (System.Data.SqlClient.SqlException e)
  225.             {
  226.                 </span><span>throw</span> <span>new</span><span> Exception(e.Message);
  227.             }
  228.         }
  229.         </span><span>///</span> <span><summary>
  230.         <span>///</span><span> 执行事务
  231.         </span><span>///</span> <span></span></summary></span>
  232.         <span>///</span> <span><param name="SQLStringList"></span>
  233.         <span>public</span> <span>void</span><span> ExecuteSqlTran(ArrayList SQLStringList)
  234.         {
  235.             Open();
  236.             command </span>= <span>new</span><span> SqlCommand();
  237.             command.Connection </span>=<span> connect;
  238.             SqlTransaction tx </span>=<span> connect.BeginTransaction();
  239.             command.Transaction </span>=<span> tx;
  240.             </span><span>try</span><span>
  241.             {
  242.                 </span><span>for</span> (<span>int</span> n = <span>0</span>; n < SQLStringList.Count; n++<span>)
  243.                 {
  244.                     </span><span>string</span> strsql =<span> SQLStringList[n].ToString();
  245.                     </span><span>if</span> (strsql.Trim().Length > <span>1</span><span>)
  246.                     {
  247.                         command.CommandText </span>=<span> strsql;
  248.                         command.ExecuteNonQuery();
  249.                     }
  250.                 }
  251.                 tx.Commit();
  252.             }
  253.             </span><span>catch</span><span> (Exception)
  254.             {
  255.                 tx.Rollback();
  256.                 </span><span>throw</span><span>;
  257.             }
  258.         }
  259.         </span><span>#region</span> 供使用API方式时使用<span>
  260.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
  261.         </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);
  262.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
  263.         </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);
  264.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span><span>)]
  265.         </span><span>private</span> <span>static</span> <span>extern</span> <span>short</span> SQLFreeHandle(<span>short</span><span> hType, IntPtr handle);
  266.         [DllImport(</span><span>"</span><span>odbc32.dll</span><span>"</span>, CharSet =<span> System.Runtime.InteropServices.CharSet.Ansi)]
  267.         </span><span>private</span> <span>static</span> <span>extern</span> <span>short</span><span> SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
  268.             </span><span>short</span> inStringLength, System.Text.StringBuilder outString, <span>short</span><span> outStringLength,
  269.             </span><span>out</span> <span>short</span><span> outLengthNeeded);
  270.         </span><span>private</span> <span>const</span> <span>short</span> SQL_HANDLE_ENV = <span>1</span><span>;
  271.         </span><span>private</span> <span>const</span> <span>short</span> SQL_HANDLE_DBC = <span>2</span><span>;
  272.         </span><span>private</span> <span>const</span> <span>int</span> SQL_ATTR_ODBC_VERSION = <span>200</span><span>;
  273.         </span><span>private</span> <span>const</span> <span>int</span> SQL_OV_ODBC3 = <span>3</span><span>;
  274.         </span><span>private</span> <span>const</span> <span>short</span> SQL_SUCCESS = <span>0</span><span>;
  275.         </span><span>private</span> <span>const</span> <span>short</span> SQL_NEED_DATA = <span>99</span><span>;
  276.         </span><span>private</span> <span>const</span> <span>short</span> DEFAULT_RESULT_SIZE = <span>1024</span><span>;
  277.         </span><span>private</span> <span>const</span> <span>string</span> SQL_DRIVER_STR = <span>"</span><span>DRIVER=SQL SERVER</span><span>"</span><span>;
  278.         </span><span>#endregion</span>
  279.         <span>///</span> <span><summary>
  280.         <span>///</span><span> 获取网内的数据库服务器名称(API方式)
  281.         </span><span>///</span> <span></span></summary></span>
  282.         <span>///</span> <span><returns></returns></span><span>服务器名称数组</span><span></span>
  283.         <span>public</span> <span>static</span> <span>string</span><span>[] GetServers()
  284.         {
  285.             </span><span>string</span> list = <span>string</span><span>.Empty;
  286.             IntPtr henv </span>=<span> IntPtr.Zero;
  287.             IntPtr hconn </span>=<span> IntPtr.Zero;
  288.             System.Text.StringBuilder inString </span>= <span>new</span><span> System.Text.StringBuilder(SQL_DRIVER_STR);
  289.             System.Text.StringBuilder outString </span>= <span>new</span><span> System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
  290.             </span><span>short</span> inStringLength = (<span>short</span><span>)inString.Length;
  291.             </span><span>short</span> lenNeeded = <span>0</span><span>;
  292.             </span><span>try</span><span>
  293.             {
  294.                 </span><span>if</span> (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, <span>out</span><span> henv))
  295.                 {
  296.                     </span><span>if</span> (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, <span>0</span><span>))
  297.                     {
  298.                         </span><span>if</span> (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, <span>out</span><span> hconn))
  299.                         {
  300.                             </span><span>if</span> (SQL_NEED_DATA ==<span> SQLBrowseConnect(hconn, inString, inStringLength, outString,
  301.                                  DEFAULT_RESULT_SIZE, </span><span>out</span><span> lenNeeded))
  302.                             {
  303.                                 </span><span>if</span> (DEFAULT_RESULT_SIZE <<span> lenNeeded)
  304.                                 {
  305.                                     outString.Capacity </span>=<span> lenNeeded;
  306.                                     </span><span>if</span> (SQL_NEED_DATA !=<span> SQLBrowseConnect(hconn, inString, inStringLength, outString,
  307.                                          lenNeeded, </span><span>out</span><span> lenNeeded))
  308.                                     {
  309.                                         </span><span>throw</span> <span>new</span> ApplicationException(<span>"</span><span>Unabled to aquire SQL Servers from ODBC driver.</span><span>"</span><span>);
  310.                                     }
  311.                                 }
  312.                                 list </span>=<span> outString.ToString();
  313.                                 </span><span>int</span> start = list.IndexOf(<span>"</span><span>{</span><span>"</span>) + <span>1</span><span>;
  314.                                 </span><span>int</span> len = list.IndexOf(<span>"</span><span>}</span><span>"</span>) -<span> start;
  315.                                 </span><span>if</span> ((start > <span>0</span>) && (len > <span>0</span><span>))
  316.                                 {
  317.                                     list </span>=<span> list.Substring(start, len);
  318.                                 }
  319.                                 </span><span>else</span><span>
  320.                                 {
  321.                                     list </span>= <span>string</span><span>.Empty;
  322.                                 }
  323.                             }
  324.                         }
  325.                     }
  326.                 }
  327.             }
  328.             </span><span>catch</span><span>
  329.             {
  330.                 list </span>= <span>string</span><span>.Empty;
  331.             }
  332.             </span><span>finally</span><span>
  333.             {
  334.                 </span><span>if</span> (hconn !=<span> IntPtr.Zero)
  335.                 {
  336.                     SQLFreeHandle(SQL_HANDLE_DBC, hconn);
  337.                 }
  338.                 </span><span>if</span> (henv !=<span> IntPtr.Zero)
  339.                 {
  340.                     SQLFreeHandle(SQL_HANDLE_ENV, hconn);
  341.                 }
  342.             }
  343.             </span><span>string</span>[] array = <span>null</span><span>;
  344.             </span><span>if</span> (list.Length > <span>0</span><span>)
  345.             {
  346.                 array </span>= list.Split(<span>'</span><span>,</span><span>'</span><span>);
  347.             }
  348.             </span><span>return</span><span> array;
  349.         }
  350.         </span><span>///</span> <span><summary>
  351.         <span>///</span><span> 获取网内的数据库服务器名称(qlClientFactory方式)
  352.         </span><span>///</span> <span></span></summary></span>
  353.         <span>///</span> <span><returns></returns></span><span>服务器名称数组</span><span></span>
  354.         <span>public</span> <span>static</span> <span>string</span><span>[] GetServersBySqlClientFactory()
  355.         {
  356.             DataTable dataSources </span>=<span> SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
  357.             DataColumn column2 </span>= dataSources.Columns[<span>"</span><span>ServerName</span><span>"</span><span>];
  358.             DataColumn column </span>= dataSources.Columns[<span>"</span><span>InstanceName</span><span>"</span><span>];
  359.             DataRowCollection rows </span>=<span> dataSources.Rows;
  360.             </span><span>string</span>[] array = <span>new</span> <span>string</span><span>[rows.Count];
  361.             </span><span>for</span> (<span>int</span> i = <span>0</span>; i < array.Length; i++<span>)
  362.             {
  363.                 </span><span>string</span> str2 = rows[i][column2] <span>as</span> <span>string</span><span>;
  364.                 </span><span>string</span> str = rows[i][column] <span>as</span> <span>string</span><span>;
  365.                 </span><span>if</span> (((str == <span>null</span>) || (str.Length == <span>0</span>)) || (<span>"</span><span>MSSQLSERVER</span><span>"</span> ==<span> str))
  366.                 {
  367.                     array[i] </span>=<span> str2;
  368.                 }
  369.                 </span><span>else</span><span>
  370.                 {
  371.                     array[i] </span>= str2 + <span>@"</span><span>\</span><span>"</span> +<span> str;
  372.                 }
  373.             }
  374.             Array.Sort</span><<span>string</span>><span>(array);
  375.             </span><span>return</span><span> array;
  376.         }
  377.         </span><span>///</span> <span><summary>
  378.         <span>///</span><span> 根据不带数据库的连接字符串，遍历查找出所有数据库实例
  379.         </span><span>///</span> <span></span></summary></span>
  380.         <span>///</span> <span><returns></returns></span><span>指定服务器的所有数据库</span><span></span>
  381.         <span>public</span> <span>string</span><span>[] GetDataBases()
  382.         {
  383.             List</span><<span>string</span>> list = <span>new</span> List<<span>string</span>><span>();
  384.             SqlConnection sqlConnection </span>= <span>new</span><span> SqlConnection(ConnectString);
  385.             </span><span>string</span><span>[] result;
  386.             </span><span>try</span><span>
  387.             {
  388.                 sqlConnection.Open();
  389.                 SqlCommand sqlCommand </span>= <span>new</span> SqlCommand(<span>"</span><span>select  name AS 数据库 from master..sysdatabases</span><span>"</span><span>, sqlConnection);
  390.                 SqlDataReader sqlDataReader </span>=<span> sqlCommand.ExecuteReader();
  391.                 </span><span>while</span><span> (sqlDataReader.Read())
  392.                 {
  393.                     list.Add(sqlDataReader.GetString(</span><span>0</span><span>));
  394.                 }
  395.                 sqlDataReader.Close();
  396.                 </span><span>string</span>[] array = <span>new</span> <span>string</span><span>[]
  397.                 {
  398.                     </span><span>"</span><span>master</span><span>"</span><span>, 
  399.                     </span><span>"</span><span>tempdb</span><span>"</span><span>, 
  400.                     </span><span>"</span><span>model</span><span>"</span><span>, 
  401.                     </span><span>"</span><span>msdb</span><span>"</span><span>
  402.                 };
  403.                 </span><span>string</span>[] array2 =<span> array;
  404.                 </span><span>for</span> (<span>int</span> i = <span>0</span>; i < array2.Length; i++<span>)
  405.                 {
  406.                     </span><span>string</span> item =<span> array2[i];
  407.                     </span><span>try</span><span>
  408.                     {
  409.                         list.Remove(item);
  410.                     }
  411.                     </span><span>catch</span><span>
  412.                     {
  413.                     }
  414.                 }
  415.                 result </span>=<span> list.ToArray();
  416.             }
  417.             </span><span>catch</span><span>
  418.             {
  419.                 result </span>=<span> list.ToArray();
  420.             }
  421.             </span><span>finally</span><span>
  422.             {
  423.                 sqlConnection.Close();
  424.             }
  425.             </span><span>return</span><span> result;
  426.         }
  427.         </span><span>///</span> <span><summary>
  428.         <span>///</span><span> 获取SqlServer指定数据库的所有表
  429.         </span><span>///</span> <span></span></summary></span>
  430.         <span>///</span> <span><returns></returns></span><span>表集合,出错则产生异常</span><span></span>
  431.         <span>public</span> <span>string</span><span>[] GetTables()
  432.         {
  433.             </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>;
  434.             DataTable dt </span>=<span> ReturnDataTable(sql);
  435.             List</span><<span>string</span>> Ls = <span>new</span> List<<span>string</span>><span>();
  436.             </span><span>for</span> (<span>int</span> i = <span>0</span>; i < dt.Rows.Count; i++<span>)
  437.             {
  438.                 Ls.Add(dt.Rows[i][</span><span>0</span><span>].ToString());
  439.             }
  440.             </span><span>return</span><span> Ls.ToArray();           
  441.         }
  442.         </span><span>///</span> <span><summary>
  443.         <span>///</span><span> 获取指定表的所有列
  444.         </span><span>///</span> <span></span></summary></span>
  445.         <span>///</span> <span><param name="TableName"></span><span>表名</span><span></span>
  446.         <span>///</span> <span><returns></returns></span><span>列集合,出错则产生异常</span><span></span>
  447.         <span>public</span> <span>string</span>[] GetColumns(<span>string</span><span> TableName)
  448.         {
  449.             </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);
  450.             </span><span>try</span><span>
  451.             {
  452.                 List</span><<span>string</span>> Ls = <span>new</span> List<<span>string</span>><span>();
  453.                 DataTable dt </span>=<span> ReturnDataTable(sql);
  454.                 </span><span>for</span> (<span>int</span> i = <span>0</span>; i < dt.Rows.Count; i++<span>)
  455.                 {
  456.                     Ls.Add(dt.Rows[i][</span><span>0</span><span>].ToString());
  457.                 }
  458.                 </span><span>return</span><span> Ls.ToArray();
  459.             }
  460.             </span><span>catch</span><span>
  461.             {
  462.                 
  463.                 </span><span>throw</span><span>;
  464.             }
  465.         }
  466.     }
  467. }</span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！