C# Sqlite 帮助类 SqliteHelper

作者:雨辰 发布于:2016-3-18 10:18 Friday 分类:C#

using System;
using System.Collections.Generic;
using Mono.Data.Sqlite;
using System.Data;

public class SqlLiteHelper
{
    // 这儿的数据库路径需要改成自己的存放路径
    private static string connectionString = "Data Source=E:\\Library.db";

    protected static SqliteConnection CreateConnection()
    {
        try
        {
            return new SqliteConnection(connectionString);
        }
        catch (Exception ex)
        {
            return null;
        }
    }

    protected static SqliteCommand CreateCommand(string sql, SqliteConnection connection, CommandType commandType, Dictionary<string, object> paramList)
    {
        if (string.IsNullOrEmpty(sql) || connection == null) return null;

        if (connection.State != ConnectionState.Open) connection.Open();

        SqliteCommand command = new SqliteCommand();
        command.Connection = connection;
        command.CommandText = sql;
        command.CommandType = commandType;

        if(paramList != null && paramList.Count > 0)
        {
            foreach(KeyValuePair<string, object> keyValuePair in paramList)
            {
                SqliteParameter parameter = new SqliteParameter("@" + keyValuePair.Key, keyValuePair.Value);
                command.Parameters.Add(parameter);
            }
        }

        return command;
    }

    public static int ExecuteNonQuery(string sql, CommandType commandType, Dictionary<string, object> paramList)
    {
        int result = 0;
        try
        {
            using(SqliteConnection connection = CreateConnection())
            { 
                SqliteCommand command = CreateCommand(sql, connection, commandType, paramList);
                result = command.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {

        }
        return result;
    }

    public static SqliteDataReader ExecuteReader(string sql, CommandType commandType, Dictionary<string, object> paramList)
    {
        try
        {
            SqliteConnection connection = CreateConnection();
            SqliteCommand command = CreateCommand(sql, connection, commandType, paramList);
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {

        }

        return null;
    }

    public static object ExecuteScalar(string sql, CommandType commandType, Dictionary<string, object> paramList)
    {
        object result = null;
        try
        {
            using (SqliteConnection connection = CreateConnection())
            {
                SqliteCommand command = CreateCommand(sql, connection, commandType, paramList);
                result = command.ExecuteScalar();
            }
        }
        catch (Exception ex)
        {

        }
        return result;
    }
}
// 创建表
string sql = "create table t_user(userid int, username varchar(20))";
SqlLiteHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, null);
// 添加数据
sql = "insert into t_user(userid, username)values(1, 'name_1')";
result = SqlLiteHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, null);

Response.Write("添加数据(不使用参数)结果:" + (result > 0) + "<br />");
// 更新数据
sql = "update t_user set username = 'name_3' where userid = 1";
result = SqlLiteHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, null);

Response.Write("更新数据(不使用参数)结果:" + (result > 0) + "<br />");

Dictionary<string, object> paramList = new Dictionary<string, object>();
paramList.Add("userid", "2");
paramList.Add("username", "name_2");
// 添加数据
sql = "insert into t_user(userid, username)values(@userid, @username)";
result = SqlLiteHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, paramList);

Response.Write("添加数据(使用参数)结果:" + (result > 0) + "<br />");

Response.Write("查询结果:<br />");
// 查询数据
sql = "select userid, username from t_user";
SqliteDataReader reader = SqlLiteHelper.ExecuteReader(sql, System.Data.CommandType.Text, null);
while(reader.Read())
{
    Response.Write("userid : " + reader["userid"].ToString() + ", username : " + reader["username"].ToString() + "<br />");
}
reader.Close();

sql = "select count(userid) from t_user";
result = int.Parse(SqlLiteHelper.ExecuteScalar(sql, System.Data.CommandType.Text, null).ToString());

Response.Write("删除之前用户总数:" + result.ToString() + "<br />");
// 删除数据
sql = "delete from t_user where userid = 1";
result = SqlLiteHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, null);

Response.Write("删除结果:" + (result > 0) + "<br />");

sql = "select count(userid) from t_user";
result = int.Parse(SqlLiteHelper.ExecuteScalar(sql, System.Data.CommandType.Text, null).ToString());

Response.Write("删除之后用户总数:" + result.ToString() + "<br />");
本文转自 :http://www.omuying.com/article/153.aspx
 

标签: C#

发表评论:

雨辰 joyimp|@2011-2018 京ICP备16030765号