C# SqlServer操作辅助类(SqlServerHelper.cs)
开发小型软件过程中,为了节约开发时间,数据库操作的代码自己都封装了。上篇介绍了SqlCeHelper这个关于操作本地数据库sdf的例子。现在介绍一下在Sqlserver中的代码。 因为Sqlserver数据库有多种登录方式,所以在构造函数中: #region [构造函数] /// summar
开发小型软件过程中,为了节约开发时间,数据库操作的代码自己都封装了。上篇介绍了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; } } } }

Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

AI Hentai Generator
Menjana ai hentai secara percuma.

Artikel Panas

Alat panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas



Untuk objek dengan nama yang sama yang telah wujud dalam pangkalan data SQL Server, langkah berikut perlu diambil: Sahkan jenis objek (jadual, paparan, prosedur tersimpan). IF NOT EXISTS boleh digunakan untuk melangkau penciptaan jika objek kosong. Jika objek mempunyai data, gunakan nama lain atau ubah suai struktur. Gunakan DROP untuk memadam objek sedia ada (berhati-hati, sandaran disyorkan). Semak perubahan skema untuk memastikan tiada rujukan kepada objek yang dipadam atau dinamakan semula.

Langkah import adalah seperti berikut: Salin fail MDF ke direktori data SQL Server (biasanya C:\Program Files\Microsoft SQL Server\MSSQL\DATA). Dalam SQL Server Management Studio (SSMS), buka pangkalan data dan pilih Lampirkan. Klik butang Tambah dan pilih fail MDF. Sahkan nama pangkalan data dan klik butang OK.

Apabila perkhidmatan SQL Server gagal dimulakan, berikut ialah beberapa langkah untuk diselesaikan: Semak log ralat untuk menentukan punca. Pastikan akaun perkhidmatan mempunyai kebenaran untuk memulakan perkhidmatan. Semak sama ada perkhidmatan pergantungan sedang berjalan. Lumpuhkan perisian antivirus. Membaiki pemasangan SQL Server. Jika pembaikan tidak berjaya, pasang semula SQL Server.

Untuk melihat nombor port SQL Server: Buka SSMS dan sambung ke pelayan. Cari nama pelayan dalam Object Explorer, klik kanan padanya dan pilih Properties. Dalam tab Sambungan, lihat medan Port TCP.

Fail pangkalan data SQL Server biasanya disimpan di lokasi lalai berikut: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data Lokasi fail pangkalan data boleh disesuaikan dengan mengubah suai laluan fail pangkalan data tetapan.

Jika anda secara tidak sengaja memadamkan pangkalan data SQL Server, anda boleh mengambil langkah-langkah berikut untuk memulihkan: hentikan aktiviti pangkalan data periksa log pangkalan data: pulihkan daripada sandaran menggunakan DBCC; alatan parti. Sila sandarkan pangkalan data anda dengan kerap dan dayakan pengelogan transaksi untuk mengelakkan kehilangan data.

Jika pemasangan SQL Server gagal, anda boleh membersihkannya dengan mengikuti langkah berikut: Nyahpasang SQL Server Padam kekunci pendaftaran Padam fail dan folder Mulakan semula komputer

Pemasangan Bahasa Inggeris Pelayan SQL boleh ditukar kepada bahasa Cina dengan mengikuti langkah berikut: muat turun pek bahasa yang sepadan dengan memasang pek bahasa;
