当前位置:网站首页>Exercice de base de données d'accès

Exercice de base de données d'accès

2022-01-15 02:08:20 La lave de Rodinia

Écrire un profilApp.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>

Écrire un fichier d'aide à la communicationHelper

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 Format d'encapsulationSQL Les différentes méthodes d'exécution des déclarations 

        public static int Update(string sql)
        {
    
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
    
                conn.Open();
                return cmd.ExecuteNonQuery();//Ajouter, supprimer et modifier
            }
            catch (Exception ex)
            {
    
                // Écrire des informations d'exception dans le journal 
                //WriteLog(ex.Message);
                //throw new Exception("Appelezpublic static int Update(string sql) Erreur de méthode :" + ex.Message);
                string errorInfo = "Appelezpublic static int Update(string sql) Erreur de méthode :" + 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)
            {
    
                // Écrire des informations d'exception dans le journal  
                string errorInfo = "Appelezpublic static object GetSingleResult(string sql) Erreur de méthode :" + 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();
                // Écrire des informations d'exception dans le journal  
                string errorInfo = "AppelezSqlDataReader GetReader(string sql) Erreur de méthode :" + 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);// Créer un objet adaptateur de données 
            DataSet ds = new DataSet();// Créer un ensemble de données de mémoire 
            try
            {
    
                conn.Open();
                da.Fill(ds);// Remplir l'ensemble de données avec un adaptateur de données 
                return ds;
            }
            catch (Exception ex)
            {
    
                // Écrire des informations d'exception dans le journal  
                string errorInfo = "Appelez public static DataSet GetDataSet(string sql) Erreur de méthode :" + 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();//Ouvrir la transaction 
                foreach (string sql in sqlList)
                {
    
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//Soumettre une transaction
                return true;
            }
            catch (Exception ex)
            {
    
                if (cmd.Transaction != null)
                {
    
                    cmd.Transaction.Rollback();//Opérations de rollback
                }
                string errorInfo = "AppelezUpdateByTran(List<string> sqlList) Erreur de méthode :" + ex.Message;
                WriteLog(errorInfo);
                throw new Exception(errorInfo);
            }
            finally
            {
    
                if (cmd.Transaction != null)
                {
    
                    cmd.Transaction = null;// Vider la transaction 
                }
                conn.Close();
            }
        }

        #endregion

        #region Autres méthodes

        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
    }
}

Documents de service pour chaque table

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);
        }
    }
}

Par nom de colonne pour chaque tableau Définir les classes pour chaque tableau

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; }
    }
}

Exécuter le programme principal

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)
        {
    
            // Ligne de requête 
            //string sql = "select Count(*) from Students";
            //object obj = AccessHelper.GetSingleResult(sql);
            //Console.WriteLine(obj.ToString());
            //Console.ReadKey();

            // Rechercher toutes les colonnes d'anniversaire , Besoin d'écrirewhile
            //string sql = "select * from Students";
            //OleDbDataReader obj = AccessHelper.GetReader(sql);

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

            #region Ajouter un élément
            //Students stu = new Students()
            //{
    
            // StudentName = "Li Nan",
            // Gender = "Femme",
            // Birthday = Convert.ToDateTime("1989-01-01"),
            // StudentIdNo = 120223199885532427,
            // Age = 28,
            // PhoneNumber = "023-33233122",
            // StudentAddress = " Henan débordement Nord 222No.",
            // ClassId = 2,
            // //StudentId = 100031,
            //};


            //StudentService objStu = new StudentService();

            //int result = objStu.AddStudent(stu);//Si ça marche,La valeur de retour est1
            //Console.WriteLine(result);
            //Console.ReadKey();
            #endregion

            StudentClassService objStuClass = new StudentClassService();

            // Une seule table a été interrogée , Student, Alors c'estTables[0].  Lors de l'interrogation des lignes ,  Traversez encore les colonnes ,item[0]C'est la première colonne
            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();

        }
    }
}

版权声明
本文为[La lave de Rodinia]所创,转载请带上原文链接,感谢
https://chowdera.com/2022/01/202201080558307441.html

随机推荐