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

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

---

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>

运行结果如图:

---