C#测试SQL Server数据库连接程序

参考文献：

Sql Server的JDBC测试程序与远程连接

前言：

之前写过一篇博客：Sql Server的JDBC测试程序与远程连接，现在来讲讲在.net 平台下如何连接sql server数据库。有多种方式可以连接数据库，分别是ODBC,OLEDB,ADO,ADO.NET。每一种连接所使用的驱动程序以及连接字符串都不相同，具体如何写数据库连接字符串可以参考这个网站http://www.connectionstrings.com/。上面列出了大多数数据源的数据库连接字符串。

ADO.NET

首先我们将如何使用ado.net来连接数据库，这也是使用最频繁的方法了。参考程序如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;


namespace SQLServerConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            //默认实例
            //string connectionString = "Data Source=SERVER_NAME;Initial Catalog=AdventureWorks2008R2;User ID=sa;Password=sa12345";
            //命名实例
            string connectionString = "Data Source=SERVER_NAME\\SQL2012;Initial Catalog=AdventureWorks2012;User Id=sa;Password=sa12345";
       
            // Assumes connectionString is a valid connection string.
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //打开连接
                connection.Open();
                // Do work here.
                SqlCommand sqlcmd = connection.CreateCommand();
                //sqlcmd.CommandText = "select top 10 * from Person.Person;";

                sqlcmd.CommandText = "select top 10 * from Person;";

                SqlDataReader sqlreader = sqlcmd.ExecuteReader();
                while (sqlreader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}",sqlreader["firstname"],sqlreader["lastname"]);
                }
                sqlreader.Close();
                connection.Close();//因为使用了using，所以这一条语句可以不写，因为当离开using代码块以后，connection自动关闭
            }
        }
    }
}

 常见的SQLClient连接字符串有:

Integrated Security=Yes;Data Source=MyServer; Initial Catalog=MyDatabase;

Data Source=ServerName ;User ID=UserName;Password=UserPassword;

Data Source=ServerName\InstanceName;Integrated Security=Yes

之所以说是使用ADO.NET是因为使用了SqlConnection这个连接类

OLEDB

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace OleConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Provider=SQLOLEDB;Data Source=T-WEIXU-W7\\SQL2012;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;";

            // Assumes connectionString is a valid connection string.
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                //打开连接
                connection.Open();
                // Do work here.
                OleDbCommand sqlcmd = connection.CreateCommand();
                sqlcmd.CommandText = "select top 10 * from Person.Person;";

                //sqlcmd.CommandText = "select top 10 * from Person;";

                OleDbDataReader sqlreader = sqlcmd.ExecuteReader();
                while (sqlreader.Read())
                {
                    Console.WriteLine("\t{0}\t{1}", sqlreader["firstname"], sqlreader["lastname"]);
                }

                sqlreader.Close();
                connection.Close();

            }
        }
    }
}

下面是常见的OLEDB连接字符串：

Provider=SQLOLEDB;Data Source=ServerName;Integrated Security=SSPI;

Provider=SQLOLEDB;Data Source=ServerName;User Id=UserName;Pasword=UserPassword;

Provider=SQLOLEDB;Data Source=ServerName\InstanceName;Integrated Security=SSPI;

Provider=SQLNCLI11;Data Source=ServerName\InstanceName;Integrated Security=SSPI;

打开D:\Program Files (x86)\Microsoft Data Access SDK 2.8\Tools\x86\RowsetViewer.exe中可以查看本机的oledb驱动程序。我的机器上就有比如

1. SQLOLEDB
2. SQLNCLI10
3. SQLNCLI11

这样的三类oledb驱动程序，