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

概述成功的道理有千万条，但如果意志薄弱，一切的道理都没有用。 SQLiteHelper.cs代码: public class SQLiteHelper { private static string connectionstring = "Data Source=" + HttpRuntime.AppDomainAppPath +System.Configuration.Con 成功的道理有千万条，但如果意志薄弱，一切的道理都没有用。 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,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,params sqliteParameter[] parameters)        {            sqliteCommand command = GetCommand(cmdText,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,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,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},{2})","null","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,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>