在net和mono中都可用的sqlite数据库连接类

asp.net在net和mono中都可用的sqlite数据库连接类,并执行参数化查询

using System;

using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Data.SQLite;
using System.IO;
using System.Data;
using System.Text;
using System.Collections;


/// <summary>
/// neirong 的摘要说明
/// </summary>
public class MyClass
{
    public string DBPath = "";
    public string WebPath = "";
public MyClass()
{
        //
        // TODO: 在此处添加构造函数逻辑
        //
        DBPath = System.Web.HttpContext.Current.Server.MapPath("/mydb.db");//数据库路径
        WebPath = System.Web.HttpContext.Current.Server.MapPath("");//网站路径


}
/// <summary>
/// 参数化查询
/// </summary>
/// <returns>返回二维数组，当第一维两个值为空时没有查到</returns>
/// <param name="sqlText">要查询的sql语句</param>
/// <param name="sqlItem">sql语句中的参数项数组，包含@</param>
/// <param name="valItem">参数值数组，均为字符串类型</param>
/// <param name="valType">参数类型数组，现在支持string、int、bigint、datatime四种类型</param>
public string[,] SelectValue(string sqlText, string[] sqlItem, string[] valItem, string[] valType)
{
string[,] returnval;
ArrayList val = new ArrayList();
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBPath);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(sqlText, conn);
int i = 0, j = 0, h = 0;
for (i = 0; i < sqlItem.Length; i++)
{
if (valType[i] == "string")
cmd.Parameters.AddWithValue(sqlItem[i], valItem[i]);
else if (valType[i] == "int")
cmd.Parameters.AddWithValue(sqlItem[i], Convert.ToInt32(valItem[i]));
else if (valType[i] == "bigint")
cmd.Parameters.AddWithValue(sqlItem[i], Convert.ToInt64(valItem[i]));
else if (valType[i] == "datatime")
cmd.Parameters.AddWithValue(sqlItem[i], Convert.ToDateTime(valItem[i]));
}


SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
for (i = 0; i < reader.FieldCount; i++)
{
val.Add(reader[i].ToString().Trim());
}
}
if (val.Count != 0)
{
int lieshu = reader.FieldCount;//列数
int hangshu = val.Count / lieshu;//行数
string[,] zhi1 = new string[hangshu, lieshu];
for (i = 0; i < hangshu; i++)
for (j = 0; j < lieshu; j++)
{
zhi1[i, j] = val[h].ToString();
h++;
}
returnval = zhi1;
}
else
{
string[,] zhi1 = { { "", "" } };
returnval = zhi1;
}
conn.Close();
return returnval;
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <param name="sqltext">待执行的sql语句</param>
public void RunSQL(string sqltext)
    {
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBPath);
conn.Open();
SQLiteCommand com = new SQLiteCommand(conn);
com.CommandText = sqltext;
com.ExecuteNonQuery();
conn.Close();
    }
/// <summary>
/// 参数化执行sql语句
/// </summary>
/// <param name="sqlText">要执行的sql语句</param>
/// <param name="sqlItem">sql语句中的参数项数组，包含@</param>
/// <param name="valItem">参数值数组，均为字符串类型</param>
/// <param name="valType">参数类型数组，现在支持string、int、bigint、datatime四种类型</param>
public void RunSQL(string sqlText, string[] sqlItem, string[] valItem, string[] valType)
{
SQLiteConnection conn = new SQLiteConnection("Data Source=" + DBPath);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(sqlText, conn);
int i = 0, j = 0, h = 0;
for (i = 0; i < sqlItem.Length; i++)
{
if (valType[i] == "string")
cmd.Parameters.AddWithValue(sqlItem[i], valItem[i]);
else if (valType[i] == "int")
cmd.Parameters.AddWithValue(sqlItem[i], Convert.ToInt32(valItem[i]));
else if (valType[i] == "bigint")
cmd.Parameters.AddWithValue(sqlItem[i], Convert.ToInt64(valItem[i]));
else if (valType[i] == "datatime")
cmd.Parameters.AddWithValue(sqlItem[i], Convert.ToDateTime(valItem[i]));
}
cmd.ExecuteNonQuery();
conn.Close();
}

}

调用方法

MyClass a = new MyClass();
string sqltext = "select * from User where Name=@name and PassWord=@password";
string[] valstring1 = { "@name", "@password" };
string[] valstring2 = { "yy", "123" };
string[] valstring3 = { "string", "string" };
string[,] val = a.SelectValue(sqltext,valstring1,valstring2,valstring3);