数据库连接字符串

数据库连接

1、在文件App.config内添加连接

 1 <?xml version="1.0" encoding="utf-8" ?>
 2 <configuration>
 3   <connectionStrings>
 4     <!--数据库认证-->
 5     <add name="Database1ConnectionString"
 6          connectionString="Data Source=DBServerName1;Initial Catalog=AdventureWorks2008;User ID=sa;Password=abc123"
 7          providerName="System.Data.SqlClient" />
 8     <!--Windows集成认证-->
 9     <add name="Database2ConnectionString"
10          connectionString="Data Source=DBServerName2;Initial Catalog=Northwind;Integrated Security=True"
11          providerName="System.Data.SqlClient" />



12 </configuration>

2、添加 dll 引用

3、获取数据库连接字符串

string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ToString();

在代码中直接使用连接字符串

//Windows 集成验证
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = @"Data Source=(localdb)v11.0;Initial Catalog=Pubs;Integrated Security=SSPI";

//or
string connectionString = @"Data Source=(localdb)v11.0;Initial Catalog=Pubs;Integrated Security=SSPI";
SqlConnection myConnection2 = new SqlConnection(connectionString);

创建命令对象Command向数据库发送sql语句

SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
 myCommand.CommandText = "SELECT * FROM Authors ORDER BY au_lname ";

//or
SqlCommand myCommand2 = new SqlCommand("SELECT * FROM Authors ORDER BY au_lname ", myConnection);

使用那个DataReader读取数据

while (myReader.Read())
{
       lstNames.Items.Add(myReader["au_lname"] + ", " + myReader["au_fname"]);
}

myReader.Close();

结果：

完整示例

page1.aspx

<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="lstAuthor" runat="server"></asp:DropDownList>
        <asp:Literal ID="lblResults" runat="server"></asp:Literal>
    </div>
    </form>
</body>

page1.aspx.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;//

namespace WebApplication1
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        private string connectionString = @"Data Source=OCEAN;Initial Catalog=Pubs;Integrated Security=SSPI";
        protected void Page_Load(Object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                FillAuthorList();
            }
        }
        private void FillAuthorList()
        {
            lstAuthor.Items.Clear();

            // Deﬁne the Select statement.
            // Three pieces of information are needed: the unique id
            // and the ﬁrst and last name.
            string selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors";

            // Deﬁne the ADO.NET objects.
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(selectSQL, con);
            SqlDataReader reader;

            // Try to open database and read information.
            try
            {
                con.Open();
                reader = cmd.ExecuteReader();

                // For each item, add the author name to the displayed
                // list box text, and store the unique ID in the Value property.
                while (reader.Read())
                {
                    ListItem newItem = new ListItem();//表示数据绑定列表控件中的数据项。此类不能被继承。
                    newItem.Text = reader["au_lname"] + ", " + reader["au_fname"];
                    newItem.Value = reader["au_id"].ToString();
                    lstAuthor.Items.Add(newItem);
                }
                reader.Close();
            }
            catch (Exception err)
            {
                lblResults.Text = "Error reading list of names. ";
                lblResults.Text += err.Message;
            }
            finally
            {
                con.Close();
            }
        }
    }
}

Access数据库连接（ms office 2007+）

private void button1_Click(object sender, EventArgs e)
        {
            string conStr = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source = D:accessDB.accdb;";

            OleDbConnection con = new OleDbConnection(conStr);
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
                MessageBox.Show("数据库连接成功！Access.accdb");
            }
            if (con.State == ConnectionState.Open)
            {
                con.Close();
                MessageBox.Show("数据连接成功关闭！");
            }

        }

如果什么连接有问题，可能是你编辑没安装Provider

需要安装这个： http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe