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