SQLite.Net使用入门(二)【结合Asp.Net MVC】

成功的道理有千万条，但如果意志薄弱，一切的道理都没有用。

---

SQLiteHelper.cs代码:

public class SQLiteHelper    {        private static string connectionstring = "Data Source=" + HttpRuntime.AppDomainAppPath +System.Configuration.ConfigurationManager.ConnectionStrings["conStr2"].ConnectionString;        public static string Connectionstring        {            get { return SQLiteHelper.connectionstring; }        }        public static int ExecuteQuery(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = GetCommand(cmdText, cmdType, parameters);            int result = cmd.ExecuteNonQuery();            cmd.Parameters.Clear();            cmd.Connection.Close();            return result;        }        public static Object ExecuteScalar(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = GetCommand(cmdText, cmdType, parameters);            object result = cmd.ExecuteScalar();            cmd.Parameters.Clear();            cmd.Connection.Close();            return result;        }        public static DataTable ExecuteDatatable(string cmdtxt, CommandType cmdtype, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = GetCommand(cmdtxt, cmdtype, parameters);            SQLiteDataAdapter adap = new SQLiteDataAdapter(cmd);            DataTable dt = new DataTable();            adap.Fill(dt);            cmd.Parameters.Clear();            cmd.Connection.Close();            return dt;        }        public static DataSet ExecuteDataset(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand command = GetCommand(cmdText, cmdType, parameters);            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);            DataSet dataSet = new DataSet();            adapter.Fill(dataSet);            command.Parameters.Clear();            command.Connection.Close();            return dataSet;        }        public static SQLiteDataReader ExecuteDataReader(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = GetCommand(cmdText, cmdType, parameters);            SQLiteDataReader result = cmd.ExecuteReader(CommandBehavior.CloseConnection);            cmd.Parameters.Clear();            cmd.Connection.Close();            return result;        }        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.SqlClient.SqlException ex)                {                    throw new Exception(ex.Message);                }                return ds;            }        }        private static SQLiteCommand GetCommand(string cmdText, CommandType cmdType, params SQLiteParameter[] parameters)        {            SQLiteCommand cmd = new SQLiteCommand();            //SQLiteConnectionStringBuilder scs = new SQLiteConnectionStringBuilder();            //scs.DataSource = connectionstring;            //scs.Password = "";            cmd.Connection = new SQLiteConnection(connectionstring);            cmd.CommandText = cmdText;            cmd.CommandType = cmdType;            if (parameters != null)                foreach (SQLiteParameter p in parameters)                    cmd.Parameters.Add(p);            cmd.Connection.Open();            //cmd.Connection.ChangePassword("pwd");//给SQLite设置密码            //cmd.Connection.SetPassword("pwd");//打开带密码的SQLite            return cmd;        }    }

SQLiteDeController.cs控制器：

 public class SQLiteDeController : Controller    {        public ActionResult Index()        {            //HCLUtility.MyJsonResultMessageEntity jms = new HCLUtility.MyJsonResultMessageEntity();            //jms.Message = "成功";            //jms.IsSuccess = true;            //ViewBag.Message = JsonConvert.SerializeObject(jms);            #region 测试            //string strSQL = string.Format("INSERT INTO  customers VALUES({0},'{1}','{2}','{3}','{4}','{5}',{6})", 8, "Joe", "上海", "潜在客户", DateTime.Now, "admin", 9);            //string message = "失败";            //try            //{            //    int i = SQLiteHelper.ExecuteQuery(strSQL, CommandType.Text);            //    if (i > 0)            //    {            //        message = "成功";            //    }            //}            //catch (Exception ex)            //{            //    message = ex.ToString();            //}            //ViewBag.Message = message + "";            //SQLiteConnection conn = null;            //string dbPath = "Data Source =" + Server.MapPath("App_Data/test.db");            //conn = new SQLiteConnection(dbPath);//创建数据库实例，指定文件位置              //conn.Open();//打开数据库，若文件不存在会自动创建              //string sql = "CREATE TABLE IF NOT EXISTS student(id integer, name varchar(20), sex varchar(2));";//建表语句              //SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn);            //cmdCreateTable.ExecuteNonQuery();//如果表不存在，创建数据表              //SQLiteCommand cmdInsert = new SQLiteCommand(conn);            //cmdInsert.CommandText = "INSERT INTO student VALUES(1, '小红', '男')";//插入几条数据              //cmdInsert.ExecuteNonQuery();            //cmdInsert.CommandText = "INSERT INTO student VALUES(2, '小李', '女')";            //cmdInsert.ExecuteNonQuery();            //cmdInsert.CommandText = "INSERT INTO student VALUES(3, '小明', '男')";            //cmdInsert.ExecuteNonQuery();            //conn.Close();              #endregion            DataSet ds = SQLiteHelper.ExecuteDataset("select id,name,createdate from demo order by id desc", CommandType.Text);            return View(ds);        }        [HttpPost]        [ValidateAntiForgeryToken]        public ActionResult Index(FormCollection form)        {            string name = form["name"];            //SQLite date函数datetime('now','localtime')  当前的本地时间            string strSQL =string.Format("INSERT INTO demo VALUES({0},'{1}',{2})","null", name,"datetime('now','localtime')");            int result=SQLiteHelper.ExecuteQuery(strSQL,CommandType.Text);            string message = "失败";            if (result > 0)            {                message = "成功";            }            ViewBag.Message = message;            DataSet ds = SQLiteHelper.ExecuteDataset("select id,name,createdate from demo order by createdate desc", CommandType.Text);            return View(ds);        }     }

Index.cshtml视图：

@{    Layout = null;}<!DOCTYPE html><html><head>    <meta name="viewport" content="width=device-width" />    <title></title></head><body>    <div>        <form method="post" action="/SQLiteDe/Index">            @Html.AntiForgeryToken()            <input id="name" name="name" /><br />            <input id="createdate" name="createdate" value="@DateTime.Now" />            <br />            <input type="submit" value="提交" />            @ViewBag.Message        </form>    </div>    <hr />    @if (Model.Tables.Count > 0 && Model != null)    {        for (int i = 0; i < Model.Tables[0].Rows.Count; i++)        {            <p>@Model.Tables[0].Rows[i]["name"].ToString()|@Model.Tables[0].Rows[i]["createdate"].ToString()</p>        }    }</body></html>

运行结果如图:

---