public void InsertRandomValuesWithParameter() { SqliteParameter textP = new SqliteParameter(); textP.ParameterName = "textP"; textP.SourceColumn = "t"; SqliteParameter floatP = new SqliteParameter(); floatP.ParameterName = "floatP"; floatP.SourceColumn = "nu"; SqliteParameter integerP = new SqliteParameter(); integerP.ParameterName ="integerP"; integerP.SourceColumn = "i"; SqliteParameter blobP = new SqliteParameter(); blobP.ParameterName = "blobP"; blobP.SourceColumn = "b"; Random random = new Random(); StringBuilder builder = new StringBuilder(); for (int k=0; k < random.Next(0,100); k++) { builder.Append((char)random.Next(65536)); } SqliteCommand insertCmd = new SqliteCommand("DELETE FROM t1; INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)",_conn); insertCmd.Parameters.Add(textP); insertCmd.Parameters.Add(floatP); insertCmd.Parameters.Add(blobP); insertCmd.Parameters.Add(integerP); textP.Value=builder.ToString(); floatP.Value=Convert.ToInt64(random.Next(999)); integerP.Value=random.Next(999); blobP.Value=System.Text.Encoding.UTF8.GetBytes("\u05D0\u05D1\u05D2" + builder.ToString()); SqliteCommand selectCmd = new SqliteCommand("SELECT * from t1", _conn); using(_conn) { _conn.Open(); int res = insertCmd.ExecuteNonQuery(); Assert.AreEqual(res,1); using (IDataReader reader = selectCmd.ExecuteReader()) { Assert.AreEqual(reader.Read(), true); Assert.AreEqual(reader["t"], textP.Value); Assert.AreEqual(reader["f"], floatP.Value); Assert.AreEqual(reader["i"], integerP.Value); Assert.AreEqual(reader["b"], blobP.Value); Assert.AreEqual(reader.Read(), false); } } }
static SqliteDataAdapter PrepareDataAdapter() { SqliteCommand select = new SqliteCommand("SELECT t, f, i, b FROM t1",_conn); SqliteCommand update = new SqliteCommand("UPDATE t1 SET t = :textP, f = :floatP, i = :integerP, n=:blobP WHERE t = :textP "); update.Connection=_conn; SqliteCommand delete = new SqliteCommand("DELETE FROM t1 WHERE t = :textP"); delete.Connection=_conn; SqliteCommand insert = new SqliteCommand("INSERT INTO t1 (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)"); insert.Connection=_conn; SqliteDataAdapter custDA = new SqliteDataAdapter(select); SqliteParameter textP = new SqliteParameter(); textP.ParameterName = "textP"; textP.SourceColumn = "t"; SqliteParameter floatP = new SqliteParameter(); floatP.ParameterName = "floatP"; floatP.SourceColumn = "f"; SqliteParameter integerP = new SqliteParameter(); integerP.ParameterName ="integerP"; integerP.SourceColumn = "i"; SqliteParameter blobP = new SqliteParameter(); blobP.ParameterName = "blobP"; blobP.SourceColumn = "b"; update.Parameters.Add(textP); update.Parameters.Add(floatP); update.Parameters.Add(integerP); update.Parameters.Add(blobP); delete.Parameters.Add(textP); insert.Parameters.Add(textP); insert.Parameters.Add(floatP); insert.Parameters.Add(integerP); insert.Parameters.Add(blobP); custDA.UpdateCommand = update; custDA.DeleteCommand = delete; custDA.InsertCommand = insert; return custDA; }
public static void AddItem(string feeduri, string title, string uri, string date, string last_updated, string author, string tags, string content, string encuri, string read, string flagged) { string generated_name = GetGeneratedName(feeduri); IDbCommand dbcmd = db.CreateCommand(); dbcmd.CommandText = @"insert into "+generated_name+" values (null, :title, :uri, :date, :lastup, :author, :tags, :content, :encuri, :read, :flagged)"; SqliteParameter title_parameter = new SqliteParameter(); if ( title != null ) { title_parameter.Value = EscapeParam(title); } else { title_parameter.Value = ""; } title_parameter.ParameterName = @":title"; dbcmd.Parameters.Add(title_parameter); SqliteParameter uri_parameter = new SqliteParameter(); if ( uri != null ) { uri_parameter.Value = EscapeParam(uri); } else { uri_parameter.Value = ""; } uri_parameter.ParameterName = @":uri"; dbcmd.Parameters.Add(uri_parameter); SqliteParameter date_parameter = new SqliteParameter(); if ( date != null ) { date_parameter.Value = EscapeParam(date); } else { date_parameter.Value = ""; } date_parameter.ParameterName = @":date"; dbcmd.Parameters.Add(date_parameter); SqliteParameter lu_parameter = new SqliteParameter(); if ( last_updated != null ) { lu_parameter.Value = EscapeParam(last_updated); } else { lu_parameter.Value = ""; } lu_parameter.ParameterName = @":lastup"; dbcmd.Parameters.Add(lu_parameter); SqliteParameter author_parameter = new SqliteParameter(); if ( author != null ) { author_parameter.Value = EscapeParam(author); } else { author_parameter.Value = ""; } author_parameter.ParameterName = @":author"; dbcmd.Parameters.Add(author_parameter); SqliteParameter tags_parameter = new SqliteParameter(); if ( tags != null ) { tags_parameter.Value = EscapeParam(tags); } else { tags_parameter.Value = ""; } tags_parameter.ParameterName = @":tags"; dbcmd.Parameters.Add(tags_parameter); SqliteParameter content_parameter = new SqliteParameter(); if ( content!= null ) { content_parameter.Value = EscapeParam(content); } else { content_parameter.Value = ""; } content_parameter.ParameterName = @":content"; dbcmd.Parameters.Add(content_parameter); SqliteParameter encuri_parameter = new SqliteParameter(); if ( encuri != null ) { encuri_parameter.Value = EscapeParam(encuri); } else { encuri_parameter.Value = ""; } encuri_parameter.ParameterName = @":encuri"; dbcmd.Parameters.Add(encuri_parameter); SqliteParameter read_parameter = new SqliteParameter(); if ( read != null ) { read_parameter.Value = EscapeParam("False"); } else { read_parameter.Value = ""; } read_parameter.ParameterName = @":read"; dbcmd.Parameters.Add(read_parameter); SqliteParameter flagged_parameter = new SqliteParameter(); if ( flagged != null ) { flagged_parameter.Value = EscapeParam("False"); } else { flagged_parameter.Value = ""; } flagged_parameter.ParameterName = @":flagged"; dbcmd.Parameters.Add(flagged_parameter); dbcmd.ExecuteNonQuery(); dbcmd.Dispose(); dbcmd = null; AddedEventArgs args = new AddedEventArgs(); args.Uri = uri; args.FeedUri = feeduri; ItemAdded("", args); }
public static string[] GetFeed(string uri) { string[] feed = new string[13]; IDbCommand dbcmd = db.CreateCommand(); dbcmd.CommandText = "select * from Feeds where uri=:uri"; SqliteParameter param = new SqliteParameter(); param.Value = EscapeParam(uri); param.ParameterName = @":uri"; dbcmd.Parameters.Add(param); IDataReader reader = dbcmd.ExecuteReader(); while(reader.Read()) { feed[0] = UnescapeParam(reader.GetString(0).ToString()); // integer primary key feed[1] = UnescapeParam(reader.GetString(1)); // uri feed[2] = UnescapeParam(reader.GetString(2)); // generated_name feed[3] = UnescapeParam(reader.GetString(3)); // name feed[4] = UnescapeParam(reader.GetString(4)); // author feed[5] = UnescapeParam(reader.GetString(5)); // subtitle feed[6] = UnescapeParam(reader.GetString(6)); // image feed[7] = UnescapeParam(reader.GetString(7)); // license feed[8] = UnescapeParam(reader.GetString(8)); // etag feed[9] = UnescapeParam(reader.GetString(9)); // hmodified feed[10] = UnescapeParam(reader.GetString(10)); // status feed[11] = UnescapeParam(reader.GetString(11)); // tags feed[12] = UnescapeParam(reader.GetString(12)); //favicon } reader.Close(); reader = null; dbcmd.Dispose(); dbcmd = null; return feed; }
public static string CreateFeed(string uri, string name, string author, string subtitle, string image, string license, string etag, string hmodified, string status, string tags, string favicon) { string generated_name = GenerateRandomName(); IDbCommand dbcmd = db.CreateCommand(); dbcmd.CommandText = @"insert into Feeds values (null, :uri, :genname, :name, :author, :subtitle, :image, :license, :etag, :hmodified, :status, :tags, :favicon)"; SqliteParameter uri_parameter = new SqliteParameter(); uri_parameter.Value = EscapeParam(uri); uri_parameter.ParameterName = @":uri"; dbcmd.Parameters.Add(uri_parameter); SqliteParameter genname_parameter = new SqliteParameter(); if ( generated_name != null ) { genname_parameter.Value = EscapeParam(generated_name); } else { genname_parameter.Value = ""; } genname_parameter.ParameterName = @":genname"; dbcmd.Parameters.Add(genname_parameter); SqliteParameter name_parameter = new SqliteParameter(); if ( name != null ) { name_parameter.Value = EscapeParam(name); } else { name_parameter.Value = ""; } name_parameter.ParameterName = @":name"; dbcmd.Parameters.Add(name_parameter); SqliteParameter author_parameter = new SqliteParameter(); if ( author != null ) { author_parameter.Value = EscapeParam(author); } else { author_parameter.Value = ""; } author_parameter.ParameterName = @":author"; dbcmd.Parameters.Add(author_parameter); SqliteParameter sub_parameter = new SqliteParameter(); if ( subtitle != null ) { sub_parameter.Value = EscapeParam(subtitle); } else { sub_parameter.Value = ""; } sub_parameter.ParameterName = @":subtitle"; dbcmd.Parameters.Add(sub_parameter); SqliteParameter image_parameter = new SqliteParameter(); if ( image != null ) { image_parameter.Value = EscapeParam(image); } else { image_parameter.Value = ""; } image_parameter.ParameterName = @":image"; dbcmd.Parameters.Add(image_parameter); SqliteParameter license_parameter = new SqliteParameter(); if ( license!= null ) { license_parameter.Value = EscapeParam(license); } else { license_parameter.Value = ""; } license_parameter.ParameterName = @":license"; dbcmd.Parameters.Add(license_parameter); SqliteParameter etag_parameter = new SqliteParameter(); if ( etag != null ) { etag_parameter.Value = EscapeParam(etag); } else { etag_parameter.Value = ""; } etag_parameter.ParameterName = @":etag"; dbcmd.Parameters.Add(etag_parameter); SqliteParameter hmodified_parameter = new SqliteParameter(); if ( hmodified != null ) { hmodified_parameter.Value = EscapeParam(hmodified); } else { hmodified_parameter.Value = ""; } hmodified_parameter.ParameterName = @":hmodified"; dbcmd.Parameters.Add(hmodified_parameter); SqliteParameter status_parameter = new SqliteParameter(); if ( status != null ) { status_parameter.Value = EscapeParam(status); } else { status_parameter.Value = ""; } status_parameter.ParameterName = @":status"; dbcmd.Parameters.Add(status_parameter); SqliteParameter tags_parameter = new SqliteParameter(); if ( tags != null ) { tags_parameter.Value = EscapeParam(tags); } else { tags_parameter.Value = ""; } tags_parameter.ParameterName = @":tags"; dbcmd.Parameters.Add(tags_parameter); SqliteParameter fav_parameter = new SqliteParameter(); if ( favicon != null ) { fav_parameter.Value = EscapeParam(favicon); } else { fav_parameter.Value = ""; } fav_parameter.ParameterName = @":favicon"; dbcmd.Parameters.Add(fav_parameter); dbcmd.ExecuteNonQuery(); dbcmd.Dispose(); dbcmd = null; NonQueryCommand("create table "+generated_name+" (id INTEGER PRIMARY KEY, title VARCHAR(50), uri VARCHAR(50), date VARCHAR(50), last_updated VARCHAR(50), author VARCHAR(50), tags VARCHAR(50), content VARCHAR(50), encuri VARCHAR(50), read VARCHAR(50), flagged VARCHAR(50))"); GeneratedNames.Add(uri, generated_name); AddedEventArgs args = new AddedEventArgs(); args.Uri = uri; FeedAdded("", args); return generated_name; }
/// <summary> /// Inserts one TrackInfo into the TrackData table. /// </summary> /// <param name="ti"> /// The <see cref="TrackInfo"/> to be inserted /// </param> /// <returns> /// True if the TrackInfo was successfully inserted. False otherwise. /// </returns> public bool InsertTrackInfo(TrackData ti) { IDbCommand dbcmd = null; try { dbcon.Open(); dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = "INSERT INTO TrackData (banshee_id, artist, title, album, duration)" + " VALUES (@bid, @artist, @title, @album, @duration)"; SqliteParameter id = new SqliteParameter("@bid", ti.ID); SqliteParameter artist = new SqliteParameter("@artist", ti.Artist); SqliteParameter title = new SqliteParameter("@title", ti.Title); SqliteParameter album = new SqliteParameter("@album", ti.Album); SqliteParameter duration = new SqliteParameter("@duration", ti.Duration); dbcmd.Parameters.Add(id); dbcmd.Parameters.Add(artist); dbcmd.Parameters.Add(title); dbcmd.Parameters.Add(album); dbcmd.Parameters.Add(duration); dbcmd.Prepare(); dbcmd.ExecuteNonQuery (); } catch (Exception e) { Log.Exception("NoNoise/DB - TrackInfo insert failed for TI: " + ti, e); return false; } finally { if (dbcmd != null) dbcmd.Dispose(); dbcmd = null; if (dbcon != null) dbcon.Close(); } return true; }
//---------------------------------------------------------------------------------------------------- /// <summary> /// 쿼리 파라메타 추가 : 출력 /// </summary> /// <param name="name">필드명</param> /// <param name="type">데이터타입</param> /// <param name="size">데이터크기(bytes)</param> //---------------------------------------------------------------------------------------------------- public void AddOutputParameter( string name, object type, int size ) { DbParameter param = null; switch(m_type) { case eType.SQLite: param = new SqliteParameter( name, type ); break; case eType.MySQL: param = new MySqlParameter( name, type ); break; case eType.MSSQL: param = new SqlParameter( name, type ); break; case eType.Oracle: param = new OracleParameter( name, type ); break; } param.Size = size; param.Direction = ParameterDirection.Output; m_command.Parameters.Add( param ); }
/// <summary> /// /// </summary> /// <param name="dt">Data Table</param> /// <returns></returns> // private static string defineTable(DataTable dt) // { // string sql = "create table " + dt.TableName + "("; // string subsql = String.Empty; // foreach (DataColumn col in dt.Columns) // { // if (subsql.Length > 0) // { // // a map function would rock so much here // subsql += ",\n"; // } // subsql += col.ColumnName + " " + sqliteType(col.DataType); // if (dt.PrimaryKey.Length > 0 && col == dt.PrimaryKey[0]) // { // subsql += " primary key"; // } // } // sql += subsql; // sql += ")"; // return sql; // } /*********************************************************************** * * Database Binding functions * * These will be db specific due to typing, and minor differences * in databases. * **********************************************************************/ ///<summary> /// This is a convenience function that collapses 5 repetitive /// lines for defining SqliteParameters to 2 parameters: /// column name and database type. /// /// It assumes certain conventions like :param as the param /// name to replace in parametrized queries, and that source /// version is always current version, both of which are fine /// for us. ///</summary> ///<returns>a built sqlite parameter</returns> private static SqliteParameter createSqliteParameter(string name, Type type) { SqliteParameter param = new SqliteParameter(); param.ParameterName = ":" + name; param.DbType = dbtypeFromType(type); param.SourceColumn = name; param.SourceVersion = DataRowVersion.Current; return param; }
public void RemoveAt(SqliteParameter param) { RemoveAt(param.ParameterName); }
public int IndexOf(SqliteParameter param) { return IndexOf(param.ParameterName); }
public bool Contains(SqliteParameter param) { return Contains(param.ParameterName); }
public SqliteParameter Add(SqliteParameter param) { Add((object)param); return param; }
void SetParameter(string parameterName, SqliteParameter parameter) #endif { if (this.Contains(parameterName)) numeric_param_list[(int)named_param_hash[parameterName]] = parameter; else if (parameterName.Length > 1 && this.Contains(parameterName.Substring(1))) numeric_param_list[(int)named_param_hash[parameterName.Substring(1)]] = parameter; else throw new IndexOutOfRangeException("The specified name does not exist: " + parameterName); }
void SetParameter(int parameterIndex, SqliteParameter parameter) #endif { if (this.Count >= parameterIndex + 1) numeric_param_list[parameterIndex] = parameter; else throw new IndexOutOfRangeException("The specified parameter index does not exist: " + parameterIndex.ToString()); }