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());
}