Binding command-based SQLite to a data source?
I'm trying to use SQLite in my application, and it's been bumpy. A few things, first off.
- Due to having VS 2008 Express, SQLite design-time support is nonexistent. I've done some reading and i'm rather confused about how to use command-based sql connections with standard data controls, ie gridview.开发者_C百科 If the views ask for data sources, what data source do I choose to use my custom SQL statements with? And how do I choose it, given that I can't use the design time support?
Thank you,
Cameron
I've never used design support in Visual Studio and with SQLite I wonder if it is possible at all so I would suggest you to get into coding :-) Here's a sample illustrating the basic ideas. You start off by creating your database:
Global.asax:
public class Global : System.Web.HttpApplication
{
public string GetDbFile()
{
return Path.Combine(Server.MapPath("~/App_Data"), "data.db3");
}
public string GetConnectionString()
{
return "Data Source=" + GetDbFile() + ";Version=3;";
}
protected void Application_Start(object sender, EventArgs e)
{
var dbFile = GetDbFile();
if (File.Exists(dbFile))
{
File.Delete(dbFile);
}
ExecuteCommand("create table users (usr_id integer primary key, usr_name string)");
ExecuteCommand("insert into users (usr_id, usr_name) values (1, 'user 1')");
ExecuteCommand("insert into users (usr_id, usr_name) values (2, 'user 2')");
}
public void ExecuteCommand(string sql)
{
using (var connection = new SQLiteConnection(GetConnectionString()))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
}
Of course you could skip this step if you already have an SQLite database file. You won't need to recreate it every time your application starts :-)
Once you have the database filled with data you could show it in a grid.
default.aspx:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data.SQLite" %>
<script type="text/C#" runat="server">
private class User
{
public int Id { get; set; }
public string Name { get; set; }
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
usersGrid.DataSource = GetUsers();
usersGrid.DataBind();
}
}
private IEnumerable<User> GetUsers()
{
using (var connection = new SQLiteConnection(ApplicationInstance.GetConnectionString()))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "select usr_id, usr_name from users";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
yield return new User { Id = reader.GetInt32(0), Name = reader.GetString(1) };
}
}
}
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="usersGrid" runat="server" />
</div>
</form>
</body>
</html>
加载中,请稍侯......
精彩评论