【转】C# 对sqlite基本操作，带批量插入
2024-10-21 14:44:56
原文地址：https://download.csdn.net/download/mic_gary/10154869
public class SQLiteHelper
{
//数据库连接字符串
public static string connectionString; public SQLiteHelper() { } #region 公用方法 public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return ;
}
else
{
return int.Parse(obj.ToString());
}
} public static bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = ;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == )
{
return false;
}
else
{
return true;
}
} public static bool Exists(string strSql, params SQLiteParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = ;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == )
{
return false;
}
else
{
return true;
}
} #endregion #region 执行简单SQL语句 /// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SQLite.SQLiteException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
} /// <summary>
/// 执行SQL语句，设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static int ExecuteSqlByTime(string SQLString, int Times)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SQLite.SQLiteException E)
{
connection.Close();
throw new Exception(E.Message);
}
}
}
} /// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
SQLiteTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = ; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > )
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.SQLite.SQLiteException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
} /// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
} /// <summary>
/// 执行带一个存储过程参数的的SQL语句。
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
/// <returns>影响的记录数</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(SQLString, connection);
SQLiteParameter myParameter = new SQLiteParameter("@content", DbType.String);
myParameter.Value = content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
} /// <summary>
/// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
SQLiteParameter myParameter = new SQLiteParameter("@fs", DbType.Binary);
myParameter.Value = fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
} /// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SQLite.SQLiteException e)
{
connection.Close();
throw new Exception(e.Message);
}
}
}
} /// <summary>
/// 执行查询语句，返回SQLiteDataReader(使用该方法切记要手工关闭SQLiteDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader ExecuteReader(string strSQL)
{
SQLiteConnection connection = new SQLiteConnection(connectionString);
SQLiteCommand cmd = new SQLiteCommand(strSQL, connection);
try
{
connection.Open();
SQLiteDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (System.Data.SQLite.SQLiteException e)
{
throw new Exception(e.Message);
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.Dispose();
// connection.Close();
//}
} /// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
} public static DataSet Query(string SQLString, string TableName)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
command.Fill(ds, TableName);
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
} /// <summary>
/// 执行查询语句，返回DataSet,设置命令的执行等待时间
/// </summary>
/// <param name="SQLString"></param>
/// <param name="Times"></param>
/// <returns></returns>
public static DataSet Query(string SQLString, int Times)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SQLiteDataAdapter command = new SQLiteDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = Times;
command.Fill(ds, "ds");
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
} #endregion #region 执行带参数的SQL语句 /// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SQLite.SQLiteException E)
{
throw new Exception(E.Message);
}
}
}
} /// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SQLiteParameter[]）</param>
public static bool ExecuteSqlTran(Hashtable SQLStringList)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{ conn.Open();
SQLiteTransaction trans=null; SQLiteCommand cmd = new SQLiteCommand();
try
{
int num = ;
//循环
trans = conn.BeginTransaction();
foreach (DictionaryEntry myDE in SQLStringList)
{ string cmdText = myDE.Key.ToString();
SQLiteParameter[] cmdParms = (SQLiteParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
num++;
}
trans.Commit();
return true;
}
catch(Exception ex)
{
//trans.Rollback();
string a = ex.ToString();
//throw; return false;
} }
} /// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, params SQLiteParameter[] cmdParms)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SQLite.SQLiteException e)
{
throw new Exception(e.Message);
}
}
}
} /// <summary>
/// 执行查询语句，返回SQLiteDataReader (使用该方法切记要手工关闭SQLiteDataReader和连接)
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SQLiteDataReader</returns>
public static SQLiteDataReader ExecuteReader(string SQLString, params SQLiteParameter[] cmdParms)
{
SQLiteConnection connection = new SQLiteConnection(connectionString);
SQLiteCommand cmd = new SQLiteCommand();
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
SQLiteDataReader myReader = cmd.ExecuteReader();
cmd.Parameters.Clear();
cmd.Dispose();
connection.Close();
return myReader;
}
catch (System.Data.SQLite.SQLiteException e)
{
throw new Exception(e.Message);
}
//finally //不能在此关闭，否则，返回的对象将无法使用
//{
// cmd.Dispose();
// connection.Close();
//} } /// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SQLiteParameter[] cmdParms)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
SQLiteCommand cmd = new SQLiteCommand();
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SQLite.SQLiteException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
} public static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{ foreach (SQLiteParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
} #endregion #region 参数转换
/// <summary>
/// 放回一个SQLiteParameter
/// </summary>
/// <param name="name">参数名字</param>
/// <param name="type">参数类型</param>
/// <param name="size">参数大小</param>
/// <param name="value">参数值</param>
/// <returns>SQLiteParameter的值</returns>
public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, int size, object value)
{
SQLiteParameter parm = new SQLiteParameter(name, type, size);
parm.Value = value;
return parm;
} public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, object value)
{
SQLiteParameter parm = new SQLiteParameter(name, type);
parm.Value = value;
return parm;
} #endregion
}
ublic partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
SQLiteHelper.connectionString = "Data Source=" + Application.StartupPath + @"\InfoDataBase.db3;BinaryGUID=False";
//button4_Click(null, null);
} private void AddRows(List<StudentClass> stuList)
{
dataGridView1.Rows.Clear();
for (int i = ; i < stuList.Count; i++)
{
DataGridViewRow row = new DataGridViewRow();
row.CreateCells(dataGridView1);
row.Cells[].Value = stuList[i].Number.ToString();
row.Cells[].Value = stuList[i].Name.ToString();
row.Cells[].Value = stuList[i].Sex.ToString();
row.Cells[].Value = stuList[i].Address.ToString();
dataGridView1.Rows.Add(row);
}
} /// <summary>
/// 查询按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button4_Click(object sender, EventArgs e)
{
AddRows(LoadInfo());
} private List<StudentClass> LoadInfo()
{
List<StudentClass> list = new List<StudentClass>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select stuID,stuName,stuSex,stuAddress from studentInfo order by stuID ASC");
foreach (System.Data.DataTable table in SQLiteHelper.Query(strSql.ToString()).Tables)
{
foreach (DataRow row in table.Rows)
{
StudentClass stu = new StudentClass();
stu.Number = int.Parse(row[].ToString());
stu.Name = row[].ToString();
stu.Sex = row[].ToString();
stu.Address = row[].ToString();
list.Add(stu);
}
break;
}
return list;
} /// <summary>
/// 增加一条记录按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(textBox1.Text) || string.IsNullOrEmpty(textBox2.Text)
|| string.IsNullOrEmpty(textBox3.Text) || string.IsNullOrEmpty(textBox4.Text))
{
MessageBox.Show("不能为空");
return;
} StudentClass stu = new StudentClass();
stu.Number = int.Parse(textBox1.Text);
stu.Name = (textBox2.Text);
stu.Sex = (textBox3.Text);
stu.Address = (textBox4.Text);
if (!InsertInfo(stu))
MessageBox.Show("插入记录失败");
else
button4_Click(null,null);
} /// <summary>
/// 插入一条记录
/// </summary>
/// <param name="stu">一个学生的实例</param>
/// <returns>true为成功</returns>
private bool InsertInfo(StudentClass stu)
{
Hashtable ht = new Hashtable(); StringBuilder strSql = new StringBuilder();
strSql.Append("insert into studentInfo(");
strSql.Append("stuID,stuName,stuSex,stuAddress)");
strSql.Append(" values (");
strSql.Append("@id,@name,@sex,@address)");
SQLiteParameter[] parameters = {
SQLiteHelper.MakeSQLiteParameter("@id", DbType.Int32,stu.Number),
SQLiteHelper.MakeSQLiteParameter("@name", DbType.String,,stu.Name),
SQLiteHelper.MakeSQLiteParameter("@sex", DbType.String,stu.Sex),
SQLiteHelper.MakeSQLiteParameter("@address",DbType.String,stu.Address),
}; ht.Add(strSql, parameters); return SQLiteHelper.ExecuteSqlTran(ht); } /// <summary>
/// 删除按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count != )
{
int number = int.Parse(dataGridView1.SelectedRows[].Cells[].Value.ToString());
if (delete(number) < )
MessageBox.Show("删除失败");
else
button4_Click(null, null);
}
}
/// <summary>
/// 删除一条记录
/// </summary>
/// <param name="stuID">数据库学生表的主键，唯一</param>
/// <returns>删除的记录条数，等于0为删除失败</returns>
private int delete(int stuID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from studentInfo where stuID='");
strSql.Append(stuID);
strSql.Append("'"); return SQLiteHelper.ExecuteSql(strSql.ToString());
} /// <summary>
/// 修改一条记录，也就是更新一条记录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(textBox1.Text) || string.IsNullOrEmpty(textBox2.Text)
|| string.IsNullOrEmpty(textBox3.Text) || string.IsNullOrEmpty(textBox4.Text))
{
MessageBox.Show("不能为空");
return;
}
StudentClass stu = new StudentClass();
stu.Number = int.Parse(textBox1.Text);
stu.Name = (textBox2.Text);
stu.Sex = (textBox3.Text);
stu.Address = (textBox4.Text);
if (update(stu) < )
MessageBox.Show("更新记录失败");
else
button4_Click(null, null);
} private int update(StudentClass stu)
{
StringBuilder strSql = new StringBuilder();
//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
strSql.Append(@"update studentInfo set stuName='" + stu.Name + "',stuSex='" + stu.Sex +
"', stuAddress='" + stu.Address + "' where stuID=" + stu.Number);
return SQLiteHelper.ExecuteSql(strSql.ToString());
} private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridView1.SelectedRows.Count != )
{
try
{
textBox1.Text = dataGridView1.SelectedRows[].Cells[].Value.ToString();
textBox2.Text = dataGridView1.SelectedRows[].Cells[].Value.ToString();
textBox3.Text = dataGridView1.SelectedRows[].Cells[].Value.ToString();
textBox4.Text = dataGridView1.SelectedRows[].Cells[].Value.ToString();
}
catch { }
}
} private void button5_Click(object sender, EventArgs e)
{
//StringBuilder sb = new StringBuilder();
//for (int i = 20000; i < 30000; i++)
//{
// sb.Append("insert into studentInfo values('女','马渼凯','凯迪" + i + "'," + i+");");
//}
//SQLiteHelper.ExecuteSql(sb.ToString());
InserPatch(); } private void InserPatch()
{
using (SQLiteConnection conn = new SQLiteConnection("Data Source=" + Application.StartupPath + @"\InfoDataBase.db3;BinaryGUID=False"))
{ using (SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
{
insertRngCmd.CommandText = @"INSERT INTO studentInfo VALUES (@sex, @adr, @name, @id)";
conn.Open();
var transaction = conn.BeginTransaction(); for (int s = ; s < ; s++)
{
insertRngCmd.Parameters.AddWithValue("@sex", "male");
insertRngCmd.Parameters.AddWithValue("@adr", "clanbian");
insertRngCmd.Parameters.AddWithValue("@name", "Great" + s);
insertRngCmd.Parameters.AddWithValue("@id", s); insertRngCmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
}
}
调用例子
最新文章
- Hbuilder MUI 选择器（Picker）设置默认选项
- 浅谈 HTTPS 和 SSL/TLS 协议的背景与基础
- google map javascript api v3 例子
- EF架构~通过EF6的DbCommand拦截器来实现数据库读写分离~终结~配置的优化和事务里读写的统一
- centos启用ftp功能
- iOS文件存储路径规定
- Mac显示和隐藏文件的命令
- C语言排序算法
- 使用Python脚本强化LLDB调试器
- 新版的DEV RichEdit很强悍，兼容docx，排版更强
- 使用redis做mybaties的二级缓存(2)-Mybatis 二级缓存小心使用
- Linux SHELL中sh和bash的区别
- pyspider爬虫框架
- Unity资源内存管理--webstream控制
- Python04（基础语法）
- 20165220 实验三 敏捷开发与XP实践 实验报告
- joi库 学习笔记
- Active Directoty域服务安装
- PSR编码规范
- Java的Calendar类