C# sqlite syntax in ASP.NET?

Question

-edit- This is no longer relevant and the question doesnt make sense to me anymore. I think i wanted to know how to create tables or know if the syntax is the same from winform to ASP.NET

---

I am very use to sqlite http://sqlite.phxsoftware.com/ and would like to create a DB in a similar style. How do i do this? it doesnt need to be the same, just similar enough for me to enjoy.

An example of the syntax.

connection = new SQLiteConnection("Data Source=" + sz +";Version=3");
command = new SQLiteCommand(connection);

connection.Open();

command.CommandText =
   "CREATE TABLE if not exists miscInfo(key   TEXT, " +
   "value TEXT, UNIQUE (key));";
command.ExecuteNonQuery();

The @name is a symbol and command.Parameters.Add("@userDesc", DbType.String).Value = d.userDesc; replaces the symbol with escaped values/texts/blob

command.CommandText = 
   "INSERT INTO discData(rootfolderID, time, volumeName, discLabel, " + 
   "userTitle, userDesc) "+
   "VALUES(@rootfolderID, @time, @volumeName, @discLabel, " + 
   "@userTitle, @userDesc); " +
   "SELECT last_insert_rowid() AS RecordID;";

command.Parameters.Add("@rootfolderID", DbType.Int64).Value = d.rootfolderID;
command.Parameters.Add("@time", DbType.Int64).Value = d.time;
command.Parameters.Add("@volumeName", DbType.String).Value = d.volumeName;
command.Parameters.Add("@discLabel", DbType.String).Value = d.discLabel;
command.Parameters.Add("@userTitle", DbType.String).Value = d.userTitle;
command.Parameters.Add("@userDesc", DbType.String).Value = d.userDesc;

d.discID = (long)command.ExecuteScalar();

Answer 1

You want to use the "SQLite ADO.NET Provider" for this.

http://sqlite.phxsoftware.com/forums/default.aspx?GroupID=2

Answer 2

Unfortunately, that SQLite provider does not support named parameters, you must provide them using questionmarks in the SQL, and then ensure the ordering, number of parameters, and their type line up.

This also means that if you wish to use the same parameter value more than once, you need to add multiple parameters with the same value.

Your example thus becomes:

command.CommandText = 
   "INSERT INTO discData(rootfolderID, time, volumeName, discLabel, " + 
   "userTitle, userDesc) "+
   "VALUES(?, ?, ?, ?, ?, ?);" +
   "SELECT last_insert_rowid() AS RecordID;";

command.Parameters.Add("@rootfolderID", DbType.Int64).Value = d.rootfolderID;
command.Parameters.Add("@time", DbType.Int64).Value = d.time;
command.Parameters.Add("@volumeName", DbType.String).Value = d.volumeName;
command.Parameters.Add("@discLabel", DbType.String).Value = d.discLabel;
command.Parameters.Add("@userTitle", DbType.String).Value = d.userTitle;
command.Parameters.Add("@userDesc", DbType.String).Value = d.userDesc;

d.discID = (long)command.ExecuteScalar();