This article describes the C# operation of SQLite database help class. Share it for your reference, as follows:
Recently, WPF is a client that needs to operate offline to store data. She considers using Sqlite embedded database in the project. She found a lot of information online and finally sorted out a public help class.
Sqlite is a very small database, which basically has most of the functions of relational database operations, and the Sql syntax is similar. Here is the help class code I sorted out:
1. Get the SQLiteConnection object and pass it in the database with an address.
/// <summary>
/// Get the connection object/// </summary>
/// <returns>SQLiteConnection</returns>
public static SQLiteConnection GetSQLiteConnection()
{
//Sqlite database address string str = ;
var con = new SQLiteConnection("Data Source=" + str + "DataBass\\");
return con;
}
2. Prepare the operation command parameters and construct the SQLiteCommand object:
/// <summary>
/// Prepare operation command parameters/// </summary>
/// <param name="cmd">SQLiteCommand</param>
/// <param name="conn">SQLiteConnection</param>
/// <param name="cmdText">Sql command text</param>/// <param name="data">parameter array</param>private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, Dictionary<String, String> data)
{
if ( != )
();
();
= conn;
= cmdText;
= ;
= 30;
if (data!=null&& >= 1)
{
foreach (KeyValuePair<String, String> val in data)
{
(, );
}
}
}
3. Query, return DataSet
/// <summary>
/// Query, return DataSet/// </summary>
/// <param name="cmdText">Sql command text</param>/// <param name="data">parameter array</param>/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(string cmdText, Dictionary<string, string> data)
{
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
(ds);
}
return ds;
}
4. Query and return DataTable
/// <summary>
/// Query, return DataTable/// </summary>
/// <param name="cmdText">Sql command text</param>/// <param name="data">parameter array</param>/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(string cmdText, Dictionary<string, string> data)
{
var dt = new DataTable();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = ();
(reader);
}
return dt;
}
5. Return a row of data DataRow
/// <summary>
/// Return a row of data/// </summary>
/// <param name="cmdText">Sql command text</param>/// <param name="data">parameter array</param>/// <returns>DataRow</returns>
public static DataRow ExecuteDataRow(string cmdText, Dictionary<string, string> data)
{
DataSet ds = ExecuteDataset(cmdText, data);
if (ds != null && > 0 && [0]. > 0)
return [0].Rows[0];
return null;
}
6. Perform database operations
/// <summary>
/// Perform database operations/// </summary>
/// <param name="cmdText">Sql command text</param>/// <param name="data">Incoming parameters</param>/// <returns>Returns the number of affected rows</returns>public static int ExecuteNonQuery(string cmdText, Dictionary<string, string> data)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
return ();
}
}
7. Return SqlDataReader object
/// <summary>
/// Return the SqlDataReader object/// </summary>
/// <param name="cmdText">Sql command text</param>/// <param name="data">Incoming parameters</param>/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader ExecuteReader(string cmdText, Dictionary<string, string> data)
{
var command = new SQLiteCommand();
SQLiteConnection connection = GetSQLiteConnection();
try
{
PrepareCommand(command, connection, cmdText, data);
SQLiteDataReader reader = ();
return reader;
}
catch
{
();
();
throw;
}
}
8. Return the first row and first column in the result set, ignoring other rows or columns
/// <summary>
/// Return the first row and first column in the result set, ignoring other rows or columns/// </summary>
/// <param name="cmdText">Sql command text</param>/// <param name="data">Incoming parameters</param>/// <returns>object</returns>
public static object ExecuteScalar(string cmdText, Dictionary<string, string> data)
{
using (SQLiteConnection connection = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
PrepareCommand(cmd, connection, cmdText, data);
return ();
}
}
9. Pagination query
/// <summary>
/// Pagination query/// </summary>
/// <param name="recordCount">Total record count</param>/// <param name="pageIndex">Page pull</param>/// <param name="pageSize">Page Size</param>/// <param name="cmdText">Sql command text</param>/// <param name="countText">Query the total number of records Sql text</param>/// <param name="data">Command Parameters</param>/// <returns>DataSet</returns>
public static DataSet ExecutePager(ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, Dictionary<string, string> data)
{
if (recordCount < 0)
recordCount = (ExecuteScalar(countText, data).ToString());
var ds = new DataSet();
using (SQLiteConnection connection = GetSQLiteConnection())
{
var command = new SQLiteCommand();
PrepareCommand(command, connection, cmdText, data);
var da = new SQLiteDataAdapter(command);
(ds, (pageIndex - 1) * pageSize, pageSize, "result");
}
return ds;
}
10. Reorganize the database
When you delete data from the SQLite database, unused disk space will be added to an internal "free list".
This part of the space can be reused the next time you insert data. Disk space is not lost, but it is not returned to the operating system.
If you delete a large amount of data and want to reduce the space occupied by the database file, execute the VACUUM command. VACUUM will reorganize the database from scratch
You can perform a time interval in your program to reorganize the database to save space
public void ResetDataBass()
{
using (SQLiteConnection conn = GetSQLiteConnection())
{
var cmd = new SQLiteCommand();
if ( != )
();
();
= conn;
= "vacuum";
= ;
= 30;
();
}
}
For more information about C# related content, please check out the topic of this site:Summary of common database operation techniques in C#》、《Tutorial on the usage of common C# controls》、《Summary of C# form operation skills》、《C# data structure and algorithm tutorial》、《Introduction to C# object-oriented programming tutorial"and"Summary of thread usage techniques for C# programming》
I hope this article will be helpful to everyone's C# programming.