PS:
类写的并不算很好,网上找了找都是零碎,没有现成的库,也许是没找到吧。
自己瞎写了一个,拿走记得点赞。
功能索引、增删改查:
- 是否存在数据库
- 是否存在字段
- 是否表存在
- 创建数据库
- 创建表
- 删除表
- 删除行
- 删除数据库
- 删除列
- 获取表中多少列
- 获取数据中表数量
- 获取表中字段有哪些
- 获取所有表名
- 获取表中一列
- 获取表中行
- 获取表所有内容
- 更新表中某行某值,如名单表中,姓名为小明,修改其性别为女
- 更新列数据,强制为同一个
- 插入一列
- 插入一行
- 重命名表名
- 重命名字段
- 重命名数据库
代码:
namespace ConsoleApp
{
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
public class SQLite : IDisposable
{
private SQLiteConnection _connection;
private SQLiteDataReader _reader;
private SQLiteCommand _command;
private string queryString;
public SQLite(string path)
{
if (File.Exists(path))
{
_connection = new SQLiteConnection("data source = " + path);
_connection.Open();
}
else
{
throw new Exception("db数据库不存在:" + path);
}
}
#region IsExists
/// <summary>
/// 是否存在数据库
/// </summary>
/// <param name="path">目录</param>
/// <returns></returns>
public static bool IsExistsDateBase(string path)
{
if (Path.GetExtension(path).Equals(".db"))
{
throw new SQLiteException("不是.db后缀,你查什么!!!");
}
return File.Exists(path);
}
/// <summary>
/// 表中是否存在字段
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="key">区分大小写</param>
/// <returns></returns>
public bool IsExisTableFile(string tableName, string key)
{
bool isExis = false;
foreach (var item in GetFiles(tableName))
{
{
isExis = true;
break;
}
}
return isExis;
}
/// <summary>
/// 查询表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public bool IsExistsTable(string tableName)
{
queryString = "SELECT name FROM sqlite_master WHERE name='" + tableName + "'";
ExecuteQuery();
if (!_reader.Read())
{
return false;
}
_reader.Close();
return true;
}
#endregion
#region Create
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="path">目录</param>
public static void CreateDateBase(string path)
{
if (Path.GetExtension(path).Equals(".db"))
{
SQLiteConnection.CreateFile(path);
}
else
{
throw new Exception("要创建数据库,则文件后缀必须为.db");
}
}
#endregion
#region Delete
/// <summary>
/// 删除一张表
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>是否成功删除</returns>
public bool DeleteTable(string tableName)
{
if (IsExistsTable(tableName))
{
queryString = "DROP TABLE " + tableName;
ExecuteQuery();
}
if (IsExistsTable(tableName))
{
return false;
}
return true;
}
/// <summary>
/// 删除一行数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="key">key</param>
/// <param name="value">值</param>
public void DeleteLine(string tableName, string key, string value)
{
queryString = "delete from " + tableName + " where " + key + " = " + "'" + value + "'";
ExecuteQuery();
}
/// <summary>
/// 删除数据库
/// </summary>
/// <param name="path"></param>
public void DeleteDateBase(string path)
{
if (File.Exists(path))
{
if (Path.GetExtension(path).Equals(".db"))
{
File.Delete(path);
}
else
{
throw new FileNotFoundException("让你删除数据库文件,删啥呢");
}
}
}
#endregion
#region Get
/// <summary>
/// 表中多少列
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>列数</returns>
public int GetRowsCount(string tableName)
{
queryString = "Select count(*) From " + tableName;
ExecuteQuery();
_reader.Read();
return _reader.GetInt32(0);
}
/// <summary>
/// 获取数据库中表数量
/// </summary>
/// <returns>返回数据库中表数量</returns>
public int GetTablesCount()
{
return GetTablesName().Length;
}
/// <summary>
/// 获取表中字段都有那些
/// </summary>
/// <param name="tableName">表名</param>
/// <returns>字段数组</returns>
public string[] GetFiles(string tableName)
{
queryString = "Pragma Table_Info(" + tableName + ")";
ExecuteQuery();
List<string> tablesFiles = new List<string>();
while (_reader.Read())
{
tablesFiles.Add(_reader["Name"].ToString());
}
return tablesFiles.ToArray();
}
/// <summary>
/// 获取数据库中所有表名
/// </summary>
/// <returns>所有表名数组</returns>
public string[] GetTablesName()
{
queryString = "select name from sqlite_master where type='table' order by name";
ExecuteQuery();
List<string> tablesName = new List<string>();
while (_reader.Read())
{
// 表名
tablesName.Add(_reader["Name"].ToString());
}
return tablesName.ToArray();
}
/// <summary>
/// 获取表中一列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="fileName">字段名</param>
/// <returns>列数组</returns>
public string[] GetRows(string tableName, string fileName)
{
if (!IsExistsTable(tableName))
{
throw new FileNotFoundException("表不存在:" + tableName);
}
if (!IsExisTableFile(tableName, fileName))
{
throw new Exception("表中不存在字段:" + fileName);
}
SQLiteDataAdapter sda = new SQLiteDataAdapter("select " + fileName + " from " + tableName, _connection);
DataTable dt = new DataTable();
sda.Fill(dt);
string[] rows = new string[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
rows[i] = dt.Rows[i][fileName].ToString();
}
sda.Dispose();
dt.Dispose();
return rows;
}
/// <summary>
/// 获取行数据,可能会有同名字段,会返回多条数据
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="key"></param>
/// <param name="keyValue"></param>
/// <returns></returns>
public List<string[]> GetLines(string tableName,string key,string keyValue)
{
queryString = string.Format("select * from {0} where {1} = '{2}'", tableName, key, keyValue);
ExecuteQuery();
string[] array = new string[_reader.FieldCount];
List<string[]> result = new List<string[]>();
while (_reader.Read())
{
for (int i = 0; i < _reader.FieldCount -1; i++)
{
array[i] = Convert.ToString( _reader[i]);
}
result.Add(array);
}
return result;
}
public List<string[]> GetAllLines(string tableName)
{
queryString = string.Format("select * from {0}", tableName);
ExecuteQuery();
string[] array = new string[_reader.FieldCount];
List<string[]> result = new List<string[]>();
while (_reader.Read())
{
for (int i = 0; i < _reader.FieldCount - 1; i++)
{
array[i] = Convert.ToString(_reader[i]);
}
result.Add(array);
}
return result;
}
#endregion
#region Update
/// <summary>
/// 更新某一行的,某一个值
/// </summary>
/// <param name="tableName"></param>
/// <param name="key"></param>
/// <param name="keyVale"></param>
/// <param name="updateKey">更新key</param>
/// <param name="updateValue"></param>
public void UpdatePropety(string tableName,string key,string keyVale,string updateKey,string updateValue)
{
queryString = string.Format("UPDATE {0} SET {1} = '{2}' WHERE {3} = '{4}'", tableName, updateKey, updateValue, key, keyVale);
ExecuteQuery();
}
/// <summary>
/// 列数据为同一个
/// </summary>
/// <param name="tableName"></param>
/// <param name="key"></param>
/// <param name="value"></param>
public bool UpdateColumns(string tableName,string updateKey, string UpdateValue)
{
if (!IsExistsTable(tableName))
{
throw new FileNotFoundException("没有表: " + tableName);
}
if (!IsExisTableFile(tableName, updateKey))
{
throw new Exception("没有关键key:" + updateKey);
}
queryString = "UPDATE " + tableName + " SET " + updateKey + " = '" + UpdateValue + "'";
ExecuteQuery();
foreach (var item in GetRows(tableName,updateKey))
{
if (!item.Equals(UpdateValue))
{
return false;
}
}
return true;
}
#endregion
#region Insert
/// <summary>
/// 表中插入一列
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="key">key</param>
/// <returns>是否操作成功</returns>
public bool InsertRows(string tableName, string key, SQLiteType sQLiteType)
{
if (IsExistsTable(tableName))
{
if (IsExisTableFile(tableName, key))
{
throw new Exception("key值已存在,不可重复添加 :" + key);
}
else
{
queryString = "alter table " + tableName + " add column " + key + " " + sQLiteType.ToString();
ExecuteQuery();
}
}
else
{
throw new NullReferenceException("不存在表名:" + tableName);
}
if (IsExisTableFile(tableName, key))
{
return true;
}
else
{
return false;
}
}
#endregion
#region Rename
/// <summary>
/// 重命名表名
/// </summary>
/// <param name="tableName">要修改的表名</param>
/// <param name="newName">新表名</param>
/// <returns>是否重命名成功</returns>
public bool RenameTable(string tableName, string newName)
{
if (IsExistsTable(tableName))
{
queryString = "ALTER TABLE " + tableName + " RENAME TO " + newName;
ExecuteQuery();
}
else
{
throw new FileNotFoundException("无法重命名不存在的表:" + tableName);
}
if (IsExistsTable(newName))
{
return true;
}
else
{
return false;
}
}
/// <summary>
/// 重命名字段
/// </summary>
/// <param name="tableName"></param>
/// <param name="fileName"></param>
/// <param name="newFileName"></param>
/// <returns></returns>
public bool RenameTableFile(string tableName, string fileName, string newFileName)
{
if (!IsExistsTable(tableName))
{
throw new FileNotFoundException("表不存在:" + tableName);
}
return false;
}
#endregion
#region Todo
//创建表
//插入行
//删除列
//获取第N行、第一行、最后一行数据
public void CreateTables(string tables)
{
}
public void InsertLines(string tablesName,string[] values)
{
}
/// <summary>
/// 删除列
/// </summary>
/// <param name="key"></param>
public void DeleteRows(string key)
{
}
#endregion
public void Dispose()
{
_connection?.Close();
_connection?.Dispose();
_reader?.Dispose();
_command?.Dispose();
}
private void ExecuteQuery()
{
_command = _connection.CreateCommand();
_command.CommandText = queryString;
_reader = _command.ExecuteReader();
}
public enum SQLiteType
{
blob,
integer,
varchar,
text,
}
}
}