Ado.net to connect sql server DB.

对象	OLEDB对象	SQL对象
Connection	OleDbConnection	SqlConnection
Command	OleDbCommand	SqlCommand
DataReader	OleDbDataReader	SqlDataReader
DataAdapter	OleDbDataAdapter	SqlDataAdapter
Dataset	Dataset	Dataset

Table "TestAdo":             

id	name
1	Bobo

 

访问SQL Code:

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

namespace TestAdo1.net
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        string connectString = "data source=Louis-MSFT;initial catalog=Louis;persist security info=True;user id=sa;password=happy`123;";

        protected void Update_Click(object sender, EventArgs e)
        {
            string sql = "Update TestAdo SET name = 'HONGHONG' WHERE name = 'Louis1'";

            //SqlCommand
            SqlConnection con = new SqlConnection(connectString);
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();

            // SqlDataAdapter && DataSet 
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connectString);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
        }

        protected void Insert_Click(object sender, EventArgs e)
        {
            string sql = "Insert Into TestAdo Values(2,'Louis1')";

            //SqlCommand
            SqlConnection con = new SqlConnection(connectString);
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();

            // SqlDataAdapter && DataSet 
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connectString);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);

        }

        protected void Delete_Click(object sender, EventArgs e)
        {
            string sql = "Delete from TestAdo where name = 'HONGHONG' OR name = 'Louis1'";

            //SqlCommand
            SqlConnection con = new SqlConnection(connectString);
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            command.ExecuteNonQuery();
            con.Close();

            // SqlDataAdapter && DataSet 
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connectString);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
        }

        protected void Query_Click(object sender, EventArgs e)
        {
            string sql = "select id, name from TestAdo";

            //SqlCommand && SqlDataReader && SqlConnection
            SqlConnection con = new SqlConnection(connectString);
            con.Open();
            SqlCommand command = new SqlCommand(sql, con);
            SqlDataReader reader = command.ExecuteReader();
            do
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader[0]);
                }
            }
            while (reader.NextResult());
            reader.Close();
            con.Close();

            // SqlDataAdapter && DataSet 
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, connectString);
            DataSet dataSet = new DataSet();
            dataAdapter.FillSchema(dataSet, SchemaType.Mapped);
            dataAdapter.Fill(dataSet);
            foreach (DataRow row in dataSet.Tables[0].Rows)
            {
                for (int i = 0; i < dataSet.Tables[0].Columns.Count; i++)
                {
                    Console.WriteLine(row[i].ToString());
                }
            }

        }
    }
}