当前位置:网站首页>Access数据库练习

Access数据库练习

2022-01-15 02:02:47 罗迪尼亚的熔岩

写入配置文件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();

        }
    }
}

版权声明
本文为[罗迪尼亚的熔岩]所创,转载请带上原文链接,感谢
https://blog.csdn.net/helldoger/article/details/121915735

随机推荐