ansaurus

Question

Binding command-based SQLite to a data source?

Answer 1

+1 A:

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"&gt;
<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>

Darin Dimitrov 2009-11-01 21:11:27

ansaurus

tags:

views:

answers:

Binding command-based SQLite to a data source?

related questions