C# 数据库访问

数据库

数据库 Database
数据库管理系统 DBMS

• Oracle，MySql，SQL Server，Access

优点：共享性，独立性，完整性，冗余数据少
功能：数据定义，操作，完整，安全，并发

RDBMS

关系型数据库：二维表形式组织数据

DataTable -> DataRow -> DataColumn -> primary key

ADO.NET

具有标准CRUD接口
不同数据库统一的访问接口

技术发展：
ODBC -> DAO -> ADO -> ADO.NET

数据访问层：

1. 源代码
2. ADO.NET(Adapter，Command)
3. Connection
4. 供应程序 Provider
5. 数据库

主要对象：

Connection
Command
DataReader
DataAdapter

Provider:

System.Data.SqlClient
System.Data.OleDb
System.Data.Odbc
System.Data.OracleClient

访问数据方式

• DataAdapter + DataSet
  自动建立 Command 对象

• DataReader
  适用只读数据，高效

Connection 重要方法，属性：

• Open();
• ConnectionSting 常用连接串

Command 重要方法：

• ExecuteReader(); //只读，单纯查询数据
• ExecuteScalar(); //返回聚合函数的结果
• ExecuteNonQuery(); //Update/Delete/Insert 返回受影响的行数

Command.CommandText = "Insert Into xxx(a,b,c) VALUES (@ID,@NAME,@PRICE)";
Command.Connection = connectionSting;
Command.Parameters.Add("@Name", SqlDbType.VarChar, 60).Value = "aaaaa";
/*使用参数比拼接字符串更安全，Sql注入*/

using System;
using System.Data;
using System.Data.OleDb;
class Test 
{
    public static void Main()
    {
        TestAdapter();
    }
    public static void TestAdapter()
    {
        string strSql = "SELECT * FROM [Publishers]";
        string strConn =
            @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            @"Data Source=D:\CsExample\ch10\BIBLIO.MDB";
        OleDbConnection MyConn = new OleDbConnection(strConn);
        OleDbCommand MyCommand = new OleDbCommand(strSql, MyConn);
        MyConn.Open();
        //使用DataReader
        OleDbDataReader MyReader = MyCommand.ExecuteReader();
        while( MyReader.Read() )
        {
            Console.WriteLine( 
                MyReader.GetString(1)+
                MyReader["Name"].ToString() + 
                MyReader[2].ToString() 
                );
        }
        MyReader.Close();
        //使用Command的ExecuteScalar
        MyCommand.CommandText = "Select Count(*) From [Publishers]";
        int cnt = (int) MyCommand.ExecuteScalar();
        Console.WriteLine( cnt );
        //使用Command的ExecuteNonQuery
        MyCommand.CommandText = "Delete From [Publishers] Where [Name]='T'";
        int cntDeleted = MyCommand.ExecuteNonQuery();
        Console.WriteLine( cntDeleted );
        MyConn.Close();
    }
}

using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;
class Test 
{
    public static void Main()
    {
        TestAdapter();
    }
    public static void TestAdapter()
    {
        string strSql = "SELECT * FROM [Publishers]";
        // 连接串
        string strConn =
            @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            @"Data Source=D:\CsExample\ch10\BIBLIO.MDB";
        //注：如果是accdb文件,则用下面的方式
        //  @"Provider=Microsoft.ACE.OLEDB.12.0;" +
        //  @"Data Source="+Environment.CurrentDirectory+@"\english.accdb";
        // 连接
        OleDbConnection conn = new OleDbConnection( strConn );
        // 适配器
        OleDbDataAdapter daAdapter = new OleDbDataAdapter(strSql, conn);
        // 命令建立器
        OleDbCommandBuilder cmdbld = new OleDbCommandBuilder( daAdapter );       
        // 数据集
        DataSet dsMyData = new DataSet();
        // 填充数据
        daAdapter.Fill(dsMyData);
        // 访问数据
        foreach( DataTable table in dsMyData.Tables )
        {
            foreach( DataRow row in table.Rows )
            {
                foreach( object field in row.ItemArray )
                {
                    Console.Write(field);
                }
                Console.WriteLine();
            }
        }
        Console.WriteLine( dsMyData.Tables[0].Rows[0][1] );
        // 在内存中选择、计算数据
        DataTable myTable = dsMyData.Tables[0];
        string strExpr = "Name Like 'T*'";
        DataRow [] foundRows = myTable.Select(strExpr);
        for(int i = 0; i < foundRows.Length; i ++){
          Console.WriteLine(foundRows[i][1]);
        }
        // 在内存中修改数据
        DataRow row1 = dsMyData.Tables[0].Rows[0];
        row1.BeginEdit();
        row1[1] = "Tang";
        row1[2] = "Peking Univ.";
        row1[3] = "Beijing";
        row1.EndEdit();
        row1.AcceptChanges();
        // 提交数据的改变
        daAdapter.Update(dsMyData);
        // 将数据标为已改变
        dsMyData.AcceptChanges();
        myTable.AcceptChanges();
        // DataSet与XML操作
        dsMyData.WriteXml("test.xml");
        dsMyData.ReadXml("test.xml");
    }
}

AccessDB 简单实现

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace KYL.DAL
{
    public class AccessDB
    {
        //数据库,
        //项目中添加引用 System.Configuration
        //在配置文件中的 <configuration>下面的 <appSettings>中加上以下的配置
        //   <add key="AccessDB" value="Demo.accdb"/>
        private static string _AccessDB = System.Configuration.ConfigurationManager.AppSettings["AccessDB"];
        //private static string _DBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Environment.CurrentDirectory + "\\" + _AccessDB; //这是早期的Access数据库
        private static string _DBConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + "\\" + _AccessDB; //这是2007以后的Access数据库
        public static string DBConnString
        {
            get
            {
                return _DBConnString;
            }
        }
        public static DataSet GetDataSet(string sql)
        {
            if (DBConnString == null || DBConnString == "") return null;
            OleDbDataAdapter daAdapter = new OleDbDataAdapter(sql, DBConnString);
            //OleDbCommandBuilder cmdbld = new OleDbCommandBuilder( daAdapter );
            DataSet dsMyData = new DataSet();
            daAdapter.Fill(dsMyData);
            return dsMyData;
        }
        public static int ExecuteNonQuery(string sql)
        {
            if (DBConnString == null || DBConnString == "") return 0;
            OleDbConnection con = new OleDbConnection(DBConnString);
            OleDbCommand cmd = new OleDbCommand(sql, con);
            con.Open();
            int n = cmd.ExecuteNonQuery();
            con.Close();
            return n;
        }
        public static string ExecuteScalar(string sql)
        {
            if (DBConnString == null || DBConnString == "") return null;
            OleDbConnection myConn = new OleDbConnection(DBConnString);
            OleDbCommand myCommand = new OleDbCommand(sql, myConn);
            myConn.Open();
            object ret = myCommand.ExecuteScalar();
            myConn.Close();
            if (ret == null) return null;
            return ret.ToString();
        }
    }
}

SqlServerDB 实现

//如下类型替换掉即可
SqlDataAdapter daAdapter = new SqlDataAdapter(sql, DBConnString);
SqlCommandBuilder cmdbld = new SqlCommandBuilder( daAdapter );
SqlConnection con = new SqlConnection(DBConnString);
SqlCommand cmd = new SqlCommand(sql, con);