C#简单的学籍管理系统(数据库变更由原来的SQLserver改为SqLite)

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

namespace 简单的学籍管理系统.App_Code
{
    public class SqLiteManager//数据库管理类
    {
        private SqLiteManager()
        {
            CreateDataBase();
        }
        private static SqLiteManager instance;
        private SQLiteConnection m_dbConnection;

        public static SqLiteManager GetInstance
        {
            get
            {
                if (instance == null)
                {
                    instance = new SqLiteManager();
                }
                return instance;
            }
        }
        private void CreateTable(string tableName, List<KeyValuePair<string, Type>> members)
        {
            ConnectToDataBase();
            string sql = "create table " + tableName + " ";
            sql += "(";
            for (int i = 0; i < members.Count; i++)
            {
                sql += members[i].Key + " ";
                if (members[i].Value == typeof(string))
                {
                    sql += "VARCHAR(200)";
                }
                else if (members[i].Value == typeof(int))
                {
                    sql += "INTERGER";
                }
                else if (members[i].Value == typeof(long))
                {
                    sql += "BIGINT";
                }
                else if (members[i].Value == typeof(decimal))
                {
                    sql += "DECIMAL";
                }
                else if (members[i].Value == typeof(DateTime))
                {
                    sql += "DATETIME";
                }
                else
                {
                    sql += "VARVHAR(200)";
                }
                if (i + 1 != members.Count)
                    sql += " ,";
            }
            sql += ")";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            CloseDataBase();
        }
        private void FillTabel(string tableName, List<KeyValuePair<string, object>> members)
        {
            ConnectToDataBase();
            string sql = "insert into " + tableName + " (";
            string values = "values (";
            for (int i = 0; i < members.Count; i++)
            {
                sql += members[i].Key;
                Type type = members[i].Value.GetType();
                if (type == typeof(string))
                {
                    values += "'" + members[i].Value + "'";
                }
                else if (type == typeof(int))
                {
                    values += (int)members[i].Value;
                }
                else if (type == typeof(long))
                {
                    values += (long)members[i].Value;
                }
                else if (type == typeof(decimal))
                {
                    values += (decimal)members[i].Value;
                }
                else if (type == typeof(DateTime))
                {
                    values += "'" + ((DateTime)members[i].Value).ToString("yyyy-MM-dd") + "'";
                }
                else
                {
                    values += "'" + members[i].Value + "'";
                }
                if (i + 1 != members.Count)
                {
                    sql += " ,";
                    values += " ,";
                }
            }
            values += ")";
            sql += ") ";
            sql += values;
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            CloseDataBase();
        }
        private void DelTabel(string tableName, List<KeyValuePair<string, object>> members)
        {
            ConnectToDataBase();
            string sql = "delete from " + tableName + " where";
            string values = "(";
            for (int i = 0; i < members.Count; i++)
            {
                Type type = members[i].Value.GetType();
                if (type == typeof(string))
                {
                    values += members[i].Key + "=" + "'" + members[i].Value + "'";
                }
                else if (type == typeof(int))
                {
                    values += members[i].Key + "=" + (int)members[i].Value;
                }
                else if (type == typeof(long))
                {
                    values += members[i].Key + "=" + (long)members[i].Value;
                }
                else if (type == typeof(decimal))
                {
                    values += members[i].Key + "=" + (decimal)members[i].Value;
                }
                else if (type == typeof(DateTime))
                {
                    values += members[i].Key + "=" + "'" + ((DateTime)members[i].Value).ToString("yyyy-MM-dd") + "'";
                }
                else
                {
                    values += members[i].Key + "=" + "'" + members[i].Value + "'";
                }
                if (i + 1 != members.Count)
                {
                    values += " and";
                }
            }
            values += ")";
            sql += values;
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            CloseDataBase();
        }
        private void UpdateTabel(string tableName, List<KeyValuePair<string, object>> members, string key, object value)
        {
            ConnectToDataBase();
            string sql = "update " + tableName + " set";
            string values = "(";
            for (int i = 0; i < members.Count; i++)
            {
                Type type = members[i].Value.GetType();
                if (type == typeof(string))
                {
                    values += members[i].Key + "=" + "'" + members[i].Value + "'";
                }
                else if (type == typeof(int))
                {
                    values += members[i].Key + "=" + (int)members[i].Value;
                }
                else if (type == typeof(long))
                {
                    values += members[i].Key + "=" + (long)members[i].Value;
                }
                else if (type == typeof(decimal))
                {
                    values += members[i].Key + "=" + (decimal)members[i].Value;
                }
                else if (type == typeof(DateTime))
                {
                    values += members[i].Key + "=" + "'" + ((DateTime)members[i].Value).ToString("yyyy-MM-dd") + "'";
                }
                else
                {
                    values += members[i].Key + "=" + "'" + members[i].Value + "'";
                }
                if (i + 1 != members.Count)
                {
                    values += " and";
                }
            }
            values += ")";
            sql += values;
            if (!string.IsNullOrEmpty(key))
            {
                string valuestr = "";
                if (value.GetType() == typeof(string))
                {
                    valuestr = "'" + value.ToString() + "'";
                }
                else if (value.GetType() == typeof(int))
                {
                    valuestr = value.ToString();
                }
                else if (value.GetType() == typeof(long))
                {
                    valuestr = value.ToString();
                }
                else if (value.GetType() == typeof(decimal))
                {
                    valuestr = value.ToString();
                }
                else if (value.GetType() == typeof(DateTime))
                {
                    valuestr = "'" + ((DateTime)value).ToString("yyyy-MM-dd") + "'";
                }
                else
                {
                    valuestr = "'" + value.ToString() + "'";
                }

                sql += "where " + key + "=" + valuestr;
            }

            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            CloseDataBase();
        }
        private void CreateDataBase()
        {
            try
            {
                ConnectToDataBase();
                CloseDataBase();
            }
            catch
            {
                SQLiteConnection.CreateFile("MyDatabase.sqlite");
            }
        }
        private void ConnectToDataBase()
        {
            if (m_dbConnection == null)
                m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            m_dbConnection.Open();
        }
        private void CloseDataBase()
        {
            if (m_dbConnection == null)
                m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
            m_dbConnection.Close();
        }
        public void createAllTables()
        {
            /*创建用户表*/
            List<KeyValuePair<string, Type>> members = new List<KeyValuePair<string, Type>>();
            members.Add(new KeyValuePair<string, Type>("UserName", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("PassWord", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("NickName", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("Sex", typeof(int)));
            members.Add(new KeyValuePair<string, Type>("Birthday", typeof(DateTime)));
            members.Add(new KeyValuePair<string, Type>("Nation", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("Area", typeof(string)));
            CreateTable("Users", members);
            /*创建民族表*/
            members.Clear();
            members.Add(new KeyValuePair<string, Type>("NationCode", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("NationName", typeof(string)));
            CreateTable("Nation", members);
            /*创建区域表*/
            members.Clear();
            members.Add(new KeyValuePair<string, Type>("AreaCode", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("AreaName", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("ParentAreaCode", typeof(string)));
            CreateTable("Chinastates", members);
            /*创建uspd表*/
            members.Clear();
            members.Add(new KeyValuePair<string, Type>("Name", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("Pwd", typeof(string)));
            members.Add(new KeyValuePair<string, Type>("Lastlogon", typeof(int)));
            CreateTable("uspd", members);
            /*创建jl表*/
            members.Clear();
            members.Add(new KeyValuePair<string, Type>("Jmima", typeof(int)));
            members.Add(new KeyValuePair<string, Type>("Jdenglu", typeof(int)));
            CreateTable("jl", members);
        }

        public List<object> SelectAllData(Type type)
        {
            List<object> resultList = new List<object>();
            string sql = "select *from ";
            if (type == typeof(Users))
            {
                ConnectToDataBase();
                sql += "Users";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                var dr = command.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Users u = new Users()
                        {
                            UserName = dr["UserName"].ToString(),
                            PassWord = dr["PassWord"].ToString(),
                            NickName = dr["NickName"].ToString(),
                            Sex = Convert.ToBoolean(dr["Sex"]),
                            Birthday = Convert.ToDateTime(dr["Birthday"].ToString()),
                            Nation = dr["Nation"].ToString(),
                            Area = dr["Area"].ToString()
                        };
                        resultList.Add(u);
                    }
                }
                CloseDataBase();
            }
            else if (type == typeof(Nation))
            {
                ConnectToDataBase();
                sql += "Nation";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                var dr = command.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Nation n = new Nation()
                        {
                            NationCode = dr[0].ToString(),
                            NationName = dr[1].ToString()
                        };
                        resultList.Add(n);
                    }
                }
                CloseDataBase();
            }
            else if (type == typeof(Chinastates))
            {
                ConnectToDataBase();
                sql += "Chinastates";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                var dr = command.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Chinastates c = new Chinastates()
                        {
                            AreaCode = dr[0].ToString(),
                            AreaName = dr[1].ToString(),
                            ParentAreaCode = dr[2].ToString()
                        };
                        resultList.Add(c);
                    }
                }
                CloseDataBase();
            }
            else if (type == typeof(uspd))
            {
                ConnectToDataBase();
                sql += "uspd";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                var dr = command.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        uspd u = new uspd()
                        {
                            Name = dr[0].ToString(),
                            Pwd = dr[1].ToString(),
                            Lastlogon = int.Parse(dr[2].ToString()),
                        };
                        resultList.Add(u);
                    }
                }
                CloseDataBase();
            }
            else if (type == typeof(jl))
            {
                ConnectToDataBase();
                sql += "jl";
                SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
                var dr = command.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        jl u = new jl
                        {
                            Jmima = (int)dr[0] == 1 ? true : false,
                            Jdenglu = (int)dr[0] == 1 ? true : false
                        };
                        resultList.Add(u);
                    }
                }
                CloseDataBase();
            }

            return resultList;
        }
        public bool AddData(Type type, object data)
        {
            bool ok = false;
            List<KeyValuePair<string, object>> members = new List<KeyValuePair<string, object>>();
            try
            {
                if (type == typeof(Users))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("UserName", ((Users)data).UserName));
                    members.Add(new KeyValuePair<string, object>("PassWord", ((Users)data).PassWord));
                    members.Add(new KeyValuePair<string, object>("NickName", ((Users)data).NickName));
                    members.Add(new KeyValuePair<string, object>("Sex", ((Users)data).Sex));
                    members.Add(new KeyValuePair<string, object>("Birthday", ((Users)data).Birthday));
                    members.Add(new KeyValuePair<string, object>("Nation", ((Users)data).Nation));
                    members.Add(new KeyValuePair<string, object>("Area", ((Users)data).Area));
                    FillTabel("Users", members);
                }
                else if (type == typeof(Nation))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("NationCode", ((Nation)data).NationCode));
                    members.Add(new KeyValuePair<string, object>("NationName", ((Nation)data).NationName));

                    FillTabel("Nation", members);
                }
                else if (type == typeof(Chinastates))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("AreaCode", ((Chinastates)data).AreaCode));
                    members.Add(new KeyValuePair<string, object>("AreaName", ((Chinastates)data).AreaName));
                    members.Add(new KeyValuePair<string, object>("ParentAreaCode", ((Chinastates)data).ParentAreaCode));

                    FillTabel("Chinastates", members);
                }
                else if (type == typeof(uspd))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("Name", ((uspd)data).Name));
                    members.Add(new KeyValuePair<string, object>("Pwd", ((uspd)data).Pwd));
                    members.Add(new KeyValuePair<string, object>("Lastlogon", ((uspd)data).Lastlogon));

                    FillTabel("uspd", members);
                }
                else if (type == typeof(jl))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("UserName", ((jl)data).Jdenglu));
                    members.Add(new KeyValuePair<string, object>("PassWord", ((jl)data).Jmima));

                    FillTabel("jl", members);
                }
                ok = true;
            }
            catch
            {

            }
            return ok;
        }
        public bool DelData(Type type, object data)
        {
            bool ok = false;
            List<KeyValuePair<string, object>> members = new List<KeyValuePair<string, object>>();
            try
            {
                if (type == typeof(Users))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("UserName", ((Users)data).UserName));
                    members.Add(new KeyValuePair<string, object>("PassWord", ((Users)data).PassWord));
                    members.Add(new KeyValuePair<string, object>("NickName", ((Users)data).NickName));
                    members.Add(new KeyValuePair<string, object>("Sex", ((Users)data).Sex));
                    members.Add(new KeyValuePair<string, object>("Birthday", ((Users)data).Birthday));
                    members.Add(new KeyValuePair<string, object>("Nation", ((Users)data).Nation));
                    members.Add(new KeyValuePair<string, object>("Area", ((Users)data).Area));
                    DelTabel("Users", members);
                }
                else if (type == typeof(Nation))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("NationCode", ((Nation)data).NationCode));
                    members.Add(new KeyValuePair<string, object>("NationName", ((Nation)data).NationName));

                    DelTabel("Nation", members);
                }
                else if (type == typeof(Chinastates))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("AreaCode", ((Chinastates)data).AreaCode));
                    members.Add(new KeyValuePair<string, object>("AreaName", ((Chinastates)data).AreaName));
                    members.Add(new KeyValuePair<string, object>("ParentAreaCode", ((Chinastates)data).ParentAreaCode));

                    DelTabel("Chinastates", members);
                }
                else if (type == typeof(uspd))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("Name", ((uspd)data).Name));
                    members.Add(new KeyValuePair<string, object>("Pwd", ((uspd)data).Pwd));
                    members.Add(new KeyValuePair<string, object>("Lastlogon", ((uspd)data).Lastlogon));

                    DelTabel("uspd", members);
                }
                else if (type == typeof(jl))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("UserName", ((jl)data).Jdenglu));
                    members.Add(new KeyValuePair<string, object>("PassWord", ((jl)data).Jmima));

                    DelTabel("jl", members);
                }
                ok = true;
            }
            catch
            {

            }
            return ok;
        }
        public bool UpdateData(Type type, string name, object value, object data)
        {
            bool ok = false;
            List<KeyValuePair<string, object>> members = new List<KeyValuePair<string, object>>();
            try
            {
                if (type == typeof(Users))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("UserName", ((Users)data).UserName));
                    members.Add(new KeyValuePair<string, object>("PassWord", ((Users)data).PassWord));
                    members.Add(new KeyValuePair<string, object>("NickName", ((Users)data).NickName));
                    members.Add(new KeyValuePair<string, object>("Sex", ((Users)data).Sex));
                    members.Add(new KeyValuePair<string, object>("Birthday", ((Users)data).Birthday));
                    members.Add(new KeyValuePair<string, object>("Nation", ((Users)data).Nation));
                    members.Add(new KeyValuePair<string, object>("Area", ((Users)data).Area));
                    UpdateTabel("Users", members, name, value);
                }
                else if (type == typeof(Nation))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("NationCode", ((Nation)data).NationCode));
                    members.Add(new KeyValuePair<string, object>("NationName", ((Nation)data).NationName));

                    UpdateTabel("Nation", members, name, value);
                }
                else if (type == typeof(Chinastates))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("AreaCode", ((Chinastates)data).AreaCode));
                    members.Add(new KeyValuePair<string, object>("AreaName", ((Chinastates)data).AreaName));
                    members.Add(new KeyValuePair<string, object>("ParentAreaCode", ((Chinastates)data).ParentAreaCode));

                    UpdateTabel("Chinastates", members, name, value);
                }
                else if (type == typeof(uspd))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("Name", ((uspd)data).Name));
                    members.Add(new KeyValuePair<string, object>("Pwd", ((uspd)data).Pwd));
                    members.Add(new KeyValuePair<string, object>("Lastlogon", ((uspd)data).Lastlogon));

                    UpdateTabel("uspd", members, name, value);
                }
                else if (type == typeof(jl))
                {
                    members.Clear();
                    members.Add(new KeyValuePair<string, object>("UserName", ((jl)data).Jdenglu));
                    members.Add(new KeyValuePair<string, object>("PassWord", ((jl)data).Jmima));

                    UpdateTabel("jl", members, name, value);
                }
                ok = true;
            }
            catch
            {

            }
            return ok;
        }
    }
}