[大数据]Access数据库练习

写入配置文件App.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<connectionStrings>
		<add name="connString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DB\Acctest11.mdb"/>
	</connectionStrings>
</configuration>

写通信帮助文件Helper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
using System.IO;

namespace Access_test2
{
    public class AccessHelper
    {
        private static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString();

        #region 封装格式化SQL语句执行的各种方法

        public static int Update(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();//增删改
            }
            catch (Exception ex)
            {
                //将异常信息写入日志
                //WriteLog(ex.Message);
                //throw new Exception("调用public static int Update(string sql)方法时发生错：" + ex.Message);
                string errorInfo = "调用public static int Update(string sql)方法时发生错：" + ex.Message;
                WriteLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        public static object GetSingleResult(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //将异常信息写入日志              
                string errorInfo = "调用public static object  GetSingleResult(string sql)方法时发生错：" + ex.Message;
                WriteLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        public static OleDbDataReader GetReader(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                //将异常信息写入日志              
                string errorInfo = "调用SqlDataReader GetReader(string sql)方法时发生错：" + ex.Message;
                WriteLog(errorInfo);
                throw new Exception(errorInfo);
            }
        }
        public static DataSet GetDataSet(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);//创建数据适配器对象
            DataSet ds = new DataSet();//创建一个内存数据集
            try
            {
                conn.Open();
                da.Fill(ds);//使用数据适配器填充数据集
                return ds;
            }
            catch (Exception ex)
            {
                //将异常信息写入日志              
                string errorInfo = "调用  public static DataSet GetDataSet(string sql)方法时发生错：" + ex.Message;
                WriteLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
                conn.Close();
            }
        }
        public static bool UpdateByTran(List<string> sqlList)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务          
                foreach (string sql in sqlList)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                string errorInfo = "调用UpdateByTran(List<string> sqlList)方法时发生错：" + ex.Message;
                WriteLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction = null;//清空事务
                }
                conn.Close();
            }
        }

        #endregion

        #region 其他方法

        private static void WriteLog(string log)
        {
            FileStream fs = new FileStream("sqlhelper.log", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(DateTime.Now.ToString() + "   " + log);
            sw.Close();
            fs.Close();
        }

        #endregion
    }
}

各表服务文件

1,StudentService

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace Access_test2
{
    public class StudentService
    {
        public int AddStudent(Students objStudent)
        {
            string sql = "insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)";
            sql += $" values('{objStudent.StudentName}','{ objStudent.Gender}','{objStudent.Birthday}',{objStudent.StudentIdNo},{objStudent.Age},'{objStudent.PhoneNumber}','{objStudent.StudentAddress}',{objStudent.ClassId})";
            return AccessHelper.Update(sql);
        }



    }
}

2, StudentClassService

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;
using System.IO;

namespace Access_test2
{
    public class StudentClassService
    {
        public DataSet GetAllClass()
        {
            //string sql = "select ClassId, ClassName from StudentClass";
            string sql = "select * from Students";
            return AccessHelper.GetDataSet(sql);
        }
    }
}

按照各个表的列名 定义各表的类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Access_test2
{
    public class ScoreList
    {
        public int StudentId { get; set; }
        public int Id { get; set; }
        public int CSharp { get; set; }
        public int SQLServerDB { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Access_test2
{
    public class StudentClass
    {
        public int ClassId { get; set; }
        public string ClassName { get; set; }
        public ScoreList ObjScore { get; set; }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Access_test2
{
    public class Students
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public string Gender { get; set; }
        public DateTime Birthday { get; set; }
        public decimal StudentIdNo { get; set; }
        public int Age { get; set; }
        public string PhoneNumber { get; set; }
        public string StudentAddress { get; set; }
        public int ClassId { get; set; }
    }
}

执行主程序

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

namespace Access_test2
{
    class Program
    {
        static void Main(string[] args)
        {
            //查询行
            //string sql = "select Count(*) from Students";
            //object obj = AccessHelper.GetSingleResult(sql);
            //Console.WriteLine(obj.ToString());
            //Console.ReadKey();

            //查询所有生日列, 需要写while
            //string sql = "select * from Students";
            //OleDbDataReader obj = AccessHelper.GetReader(sql);

            //while (obj.Read())
            //{
            //    Console.WriteLine(obj["birthday"]);
            //}
            //obj.Close();
            //Console.ReadKey();

            #region 增加元素
            //Students stu = new Students()
            //{
            //    StudentName = "李南",
            //    Gender = "女",
            //    Birthday = Convert.ToDateTime("1989-01-01"),
            //    StudentIdNo = 120223199885532427,
            //    Age = 28,
            //    PhoneNumber = "023-33233122",
            //    StudentAddress = "河南溢水路北222号",
            //    ClassId = 2,
            //    //StudentId = 100031,
            //};


            //StudentService objStu = new StudentService();

            //int result = objStu.AddStudent(stu);//若成功,则返回值为1
            //Console.WriteLine(result);
            //Console.ReadKey();
            #endregion

            StudentClassService objStuClass = new StudentClassService();

            //只查询了一个表, Student, 所以是Tables[0]. 查询各行时候, 再次遍历各个列,item[0]就是第一列
            foreach (DataRow item in objStuClass.GetAllClass().Tables[0].Rows)
            {
                for (int i = 0; i < objStuClass.GetAllClass().Tables[0].Columns.Count; i++)
                {
                    Console.WriteLine(item[i]);
                }
                
            }
            //Console.WriteLine(objStuClass.GetAllClass().Tables[0].Rows.Count);
            Console.ReadKey();

        }
    }
}