C# SqlServer操作辅助类(SqlServerHelper.cs)-tutoriel mysql-php.cn

Maison

base de données

tutoriel mysql

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:19 PM

sqlserver 操作辅助

开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了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>

Copier après la connexion

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

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

C# SqlServer操作辅助类(SqlServerHelper.cs) 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>

Copier après la connexion

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

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

C# SqlServer操作辅助类(SqlServerHelper.cs) 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;
            }
        }
    }
}

Copier après la connexion

Déclaration de ce site Web

Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn

Outils d'IA chauds

Undresser.AI Undress

Application basée sur l'IA pour créer des photos de nu réalistes

AI Clothes Remover

Outil d'IA en ligne pour supprimer les vêtements des photos.

Undress AI Tool

Images de déshabillage gratuites

Clothoff.io

Dissolvant de vêtements AI

AI Hentai Generator

Générez AI Hentai gratuitement.

Afficher plus

Article chaud

Assassin's Creed Shadows: Solution d'énigmes de coquille

3 Il y a quelques semaines By DDD

Quoi de neuf dans Windows 11 KB5054979 et comment résoudre les problèmes de mise à jour

2 Il y a quelques semaines By DDD

Où trouver la courte de la grue à atomide atomique

3 Il y a quelques semaines By DDD

Économie dans R.E.P.O. Expliqué (et enregistrer des fichiers)

1 Il y a quelques mois By 尊渡假赌尊渡假赌尊渡假赌

Assassin's Creed Shadows - Comment trouver le forgeron et déverrouiller les armes et la personnalisation des armes

4 Il y a quelques semaines By DDD

Afficher plus

Outils chauds

Bloc-notes++7.3.1

Éditeur de code facile à utiliser et gratuit

SublimeText3 version chinoise

Version chinoise, très simple à utiliser

Envoyer Studio 13.0.1

Puissant environnement de développement intégré PHP

Dreamweaver CS6

Outils de développement Web visuel

SublimeText3 version Mac

Logiciel d'édition de code au niveau de Dieu (SublimeText3)

Afficher plus

Sujets chauds

Où se trouve l'entrée de connexion pour la messagerie Gmail ?

7569

Tutoriel CakePHP

1386

Quel est le format du nom de compte de Steam

Clé d&amp;amp;amp;amp;amp;amp;#39;activation Win11 permanent

NYT Connexions Indices et réponses

107

Afficher plus

Related knowledge

Comment importer un fichier mdf dans sqlserver Apr 08, 2024 am 11:41 AM

Les étapes d'importation sont les suivantes : Copiez le fichier MDF dans le répertoire de données de SQL Server (généralement C:\Program Files\Microsoft SQL Server\MSSQL\DATA). Dans SQL Server Management Studio (SSMS), ouvrez la base de données et sélectionnez Attacher. Cliquez sur le bouton Ajouter et sélectionnez le fichier MDF. Confirmez le nom de la base de données et cliquez sur le bouton OK.

Comment résoudre le problème selon lequel l'objet nommé existe déjà dans la base de données sqlserver Apr 05, 2024 pm 09:42 PM

Pour les objets du même nom qui existent déjà dans la base de données SQL Server, les étapes suivantes doivent être suivies : Confirmez le type d'objet (table, vue, procédure stockée). IF NOT EXISTS peut être utilisé pour ignorer la création si l'objet est vide. Si l'objet contient des données, utilisez un nom différent ou modifiez la structure. Utilisez DROP pour supprimer des objets existants (soyez prudent, sauvegarde recommandée). Vérifiez les modifications de schéma pour vous assurer qu'il n'y a aucune référence à des objets supprimés ou renommés.

Que faire si le service sqlserver ne peut pas être démarré Apr 05, 2024 pm 10:00 PM

Lorsque le service SQL Server ne parvient pas à démarrer, voici quelques étapes à résoudre : Consultez le journal des erreurs pour déterminer la cause première. Assurez-vous que le compte de service est autorisé à démarrer le service. Vérifiez si les services de dépendance sont en cours d'exécution. Désactivez le logiciel antivirus. Réparez l'installation de SQL Server. Si la réparation ne fonctionne pas, réinstallez SQL Server.

Comment vérifier le numéro de port sqlserver Apr 05, 2024 pm 09:57 PM

Pour afficher le numéro de port SQL Server : Ouvrez SSMS et connectez-vous au serveur. Recherchez le nom du serveur dans l'Explorateur d'objets, cliquez dessus avec le bouton droit et sélectionnez Propriétés. Dans l'onglet Connexion, affichez le champ Port TCP.

Comment récupérer une base de données supprimée accidentellement dans SQLserver Apr 05, 2024 pm 10:39 PM

Si vous supprimez accidentellement une base de données SQL Server, vous pouvez suivre les étapes suivantes pour la récupérer : arrêter l'activité de la base de données ; sauvegarder les fichiers journaux ; vérifier les options de récupération : restaurer à partir d'une sauvegarde ; utiliser DBCC CHECKDB ; outils de fête. Veuillez sauvegarder régulièrement votre base de données et activer la journalisation des transactions pour éviter toute perte de données.

Où est la base de données sqlserver ? Apr 05, 2024 pm 08:21 PM

Les fichiers de base de données SQL Server sont généralement stockés à l'emplacement par défaut suivant : Windows : C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux : /var/opt/mssql/data L'emplacement du fichier de base de données peut être personnalisé en modifiant le chemin du fichier de base de données. paramètre.

Comment supprimer sqlserver si l'installation échoue ? Apr 05, 2024 pm 11:27 PM

Si l'installation de SQL Server échoue, vous pouvez la nettoyer en suivant ces étapes : Désinstaller SQL Server Supprimer les clés de registre Supprimer les fichiers et dossiers Redémarrer l'ordinateur

Comment changer l'installation anglaise de sqlserver en chinois Apr 05, 2024 pm 10:21 PM

L'installation de SQL Server en anglais peut être modifiée en chinois en suivant les étapes suivantes : téléchargez le module linguistique correspondant ; arrêtez le service SQL Server ; modifiez la langue de l'instance ; modifiez la langue de l'interface utilisateur ;

See all articles