本文操作是测试数据库的其中一张表,其中包括清空sqlite数据库MyGroup表中的数据,清空sqlite数据库sqlite_sequence表中的自增变量,sqlite数据库MyGroup直接插入数据 40000000,sqlite数据库MyGroup 参数插入数据 80000000
using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Net;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace SupremeConsole
{
class Program
{
static void Main(string[] args)
{
#region 测试sqlite数据库
//GetSqliteData();
GetSqliteData2();
Thread.Sleep(TimeSpan.FromSeconds(10));
GetSqliteData3();
Thread.Sleep(TimeSpan.FromSeconds(10));
GetSqliteData4();
Thread.Sleep(TimeSpan.FromSeconds(10));
GetSqliteData5();
#endregion
Console.ReadLine();
}
/// <summary>
/// 使用sqlite获取数据
/// </summary>
public static void GetSqliteData()
{
SQLiteConnection sqLiteConn = new SQLiteConnection("Data source=" + Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"), "mysqlite.db"));
sqLiteConn.Open();
SQLiteCommand sqLiteCmd = new SQLiteCommand
{
Connection = sqLiteConn,
CommandType = CommandType.Text,
//CommandText = "select * from mydb"//DELETE FROM MyGroup;UPDATE sqlite_sequence SET seq = 0 WHERE name = 'MyGroup';
};
SQLiteDataAdapter sqLiteDataAdapter = new SQLiteDataAdapter
{
SelectCommand = sqLiteCmd
};
DataSet dataSet = new DataSet();
sqLiteDataAdapter.Fill(dataSet);
sqLiteConn.Close();
foreach (DataRow item in dataSet.Tables[0].Rows)
{
Console.WriteLine($"db_name:{item.ItemArray[2].ToString()},hash_value:{item.ItemArray[3].ToString()}");
}
//string ss =$"编号:{dataSet.Tables[0].Rows[0].ItemArray[2].ToString()},姓名:{dataSet.Tables[0].Rows[0].ItemArray[3].ToString()}";
//return ss;
}
/// <summary>
/// 清空sqlite数据库MyGroup表中的数据
/// </summary>
public static void GetSqliteData2()
{
using (SQLiteConnection sqLiteConn = new SQLiteConnection("Data source=" + Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"), "mysqlite.db")))
{
sqLiteConn.Open();
SQLiteTransaction sqLiteTransaction = sqLiteConn.BeginTransaction();
SQLiteCommand sqLiteCmd = new SQLiteCommand
{
Connection = sqLiteConn,
CommandType = CommandType.Text,
Transaction = sqLiteTransaction,
CommandText = "DELETE FROM MyGroup"//DELETE FROM MyGroup;UPDATE sqlite_sequence SET seq = 0 WHERE name = 'MyGroup';
};
try
{
int s = sqLiteCmd.ExecuteNonQuery();
Console.WriteLine($"清空sqlite数据库MyGroup表中的数据 ok");
sqLiteTransaction.Commit();
}
catch (Exception)
{
Console.WriteLine($"清空sqlite数据库MyGroup表中的数据 error");
sqLiteTransaction.Rollback();
//throw;
}
finally
{
sqLiteConn.Close();
}
}
}
/// <summary>
/// 清空sqlite数据库sqlite_sequence表中的自增变量
/// </summary>
public static void GetSqliteData3()
{
SQLiteConnection sqLiteConn = new SQLiteConnection("Data source=" + Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"), "mysqlite.db"));
sqLiteConn.Open();
SQLiteTransaction sqLiteTransaction = sqLiteConn.BeginTransaction();
try
{
SQLiteCommand sqLiteCmd = new SQLiteCommand
{
Connection = sqLiteConn,
CommandType = CommandType.Text,
//CommandText = "select * from mydb"
Transaction = sqLiteTransaction,
CommandText = "UPDATE sqlite_sequence SET seq = 0 WHERE name = 'MyGroup'"//DELETE FROM MyGroup;UPDATE sqlite_sequence SET seq = 0 WHERE name = 'MyGroup';
};
sqLiteCmd.ExecuteNonQuery();
sqLiteTransaction.Commit();
Console.WriteLine($"清空sqlite数据库sqlite_sequence表中的自增变量 ok");
}
catch (Exception)
{
sqLiteTransaction.Rollback();
Console.WriteLine($"清空sqlite数据库sqlite_sequence表中的自增变量 error");
}
finally
{
sqLiteConn.Close();
}
}
/// <summary>
/// sqlite数据库MyGroup直接插入数据 40000000
/// </summary>
public static void GetSqliteData4()
{
SQLiteConnection sqLiteConn = new SQLiteConnection("Data source=" + Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"), "mysqlite.db"));
sqLiteConn.Open();
SQLiteTransaction sqLiteTransaction = sqLiteConn.BeginTransaction();
try
{
SQLiteCommand sqLiteCmd = new SQLiteCommand
{
Connection = sqLiteConn,
CommandType = CommandType.Text,
//CommandText = "select * from mydb"
Transaction = sqLiteTransaction,
CommandText = "INSERT INTO MyGroup (group_name, start_id, end_id) VALUES ('group01', 0, 40000000)"//DELETE FROM MyGroup;UPDATE sqlite_sequence SET seq = 0 WHERE name = 'MyGroup';
};
sqLiteCmd.ExecuteNonQuery();
sqLiteTransaction.Commit();
Console.WriteLine($"sqlite数据库MyGroup插入数据 40000000 ok");
}
catch (Exception)
{
sqLiteTransaction.Rollback();
Console.WriteLine($"sqlite数据库MyGroup插入数据 40000000 error");
}
finally
{
sqLiteConn.Close();
}
}
/// <summary>
/// sqlite数据库MyGroup参数插入数据 80000000
/// </summary>
public static void GetSqliteData5()
{
SQLiteConnection sqLiteConn = new SQLiteConnection("Data source=" + Path.Combine(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Data"), "mysqlite.db"));
sqLiteConn.Open();
SQLiteTransaction sqLiteTransaction = sqLiteConn.BeginTransaction();
try
{ //INSERT INTO MyGroup (group_name, start_id, end_id) VALUES ("group02", 40000000, 80000000);
SQLiteCommand sqLiteCmd = new SQLiteCommand
{
Connection = sqLiteConn,
CommandType = CommandType.Text,
Transaction = sqLiteTransaction,
CommandText = "INSERT INTO MyGroup (group_name, start_id, end_id) VALUES (@group_name, @start_id, @end_id)"//DELETE FROM MyGroup;UPDATE sqlite_sequence SET seq = 0 WHERE name = 'MyGroup';
};
SQLiteParameter sQLiteParameter = new SQLiteParameter("@group_name", DbType.String);
sQLiteParameter.Value = "group02";
sqLiteCmd.Parameters.Add(sQLiteParameter);
SQLiteParameter sQLiteParameter1 = new SQLiteParameter("@start_id", DbType.Int32);
sQLiteParameter1.Value = 40000000;
sqLiteCmd.Parameters.Add(sQLiteParameter1);
SQLiteParameter sQLiteParameter2 = new SQLiteParameter("@end_id", DbType.Int32);
sQLiteParameter2.Value = 80000000;
sqLiteCmd.Parameters.Add(sQLiteParameter2);
sqLiteCmd.ExecuteNonQuery();
sqLiteTransaction.Commit();
Console.WriteLine($"sqlite数据库MyGroup插入数据 80000000 ok");
}
catch (Exception)
{
sqLiteTransaction.Rollback();
Console.WriteLine($"sqlite数据库MyGroup插入数据 80000000 error");
}
finally
{
sqLiteConn.Close();
}
}
}
}
以上仅仅是根据本作者的sqlite数据库来写的操作,具体情况需要具体修改。
附加数据库:mysqlite.db