public static bool AddNewElement(ElementShort element, out string error)
{
error = String.Empty;
try
{
using (SQLiteConnection sqlConn = new SQLiteConnection(ConnectionManager.ConnectionStringSQLite))
{
string query = String.Format(@"insert
into data(parent_id, name)
values(@parent_id, @name); select last_insert_rowid();");
sqlConn.Open();
using (SQLiteCommand command = new SQLiteCommand(query, sqlConn))
{
command.Parameters.Add(new SQLiteParameter()
{
ParameterName = "@parent_id", DbType = DbType.Int64, Value = element.ParentId
});
command.Parameters.Add(new SQLiteParameter()
{
ParameterName = "@name", DbType = DbType.AnsiString, Value = element.Name
});
element.Id = (long)command.ExecuteScalar();
}
}
}
catch (Exception ex)
{
error = ex.Message;
return(false);
}
return(true);
}
public static List <ElementShort> GetElementsByParentId(long?parend_id)
{
var result = new List <ElementShort>();
SQLiteDataReader rdr = null;
try
{
using (SQLiteConnection sqlConn = new SQLiteConnection(ConnectionManager.ConnectionStringSQLite))
{
sqlConn.Open();
String query = "select count(*) from data";
//using (SQLiteCommand command = new SQLiteCommand(query, sqlConn))
//{
// var c = command.ExecuteScalar();
//};
query = "select id, parent_id, name, position from data where parent_id == " + parend_id;
if (parend_id == null)
{
//query = "select id, parent_id, name, position from data where parent_id is null";
query = "select * from view_root";
}
using (SQLiteCommand command = new SQLiteCommand(query, sqlConn))
{
command.CommandTimeout = 3;
rdr = command.ExecuteReader();
while (rdr.Read())
{
var r = new ElementShort()
{
Id = (long)rdr["id"],
ParentId = rdr["parent_id"] == DBNull.Value ? null : (long?)rdr["parent_id"],
Name = rdr["name"].ToString(),
Position = (long)rdr["position"]
};
result.Add(r);
}
//DataSet ds = new DataSet();
//SQLiteDataAdapter ret = new SQLiteDataAdapter();
//ret.SelectCommand = command;
//ret.Fill(ds);
//if (ds != null && ds.Tables[0].Rows.Count > 0)
// return (from rows in ds.Tables[0].AsEnumerable()
// select new ElementShort()
// {
// Id = (long)rows["id"],
// ParentId = rows.Field<long?>("parent_id"),
// Name = rows["name"].ToString(),
// Position = (long)rows["position"]
// }).ToList();
}
}
}
catch (Exception ex)
{
return(result);
}
return(result);
}
public static bool AddNewElement(ElementShort element, out string error)
{
error = String.Empty;
try
{
using (SQLiteConnection sqlConn = new SQLiteConnection(ConnectionManager.ConnectionStringSQLite))
{
sqlConn.Open();
long position = 1;
string maxPosition = "select max(position) from data where parent_id = @parent_id";
if (element.ParentId == null)
{
maxPosition = "select max(position) from data where parent_id is null";
}
using (SQLiteCommand command = new SQLiteCommand(maxPosition, sqlConn))
{
command.Parameters.Add(new SQLiteParameter()
{
ParameterName = "@parent_id", DbType = DbType.Int64, Value = element.ParentId
});
var max = command.ExecuteScalar();
try
{
position = (long)max + 1;
}
catch { }
}
string query = String.Format(@"insert
into data(parent_id, name, position)
values(@parent_id, @name, @position); select last_insert_rowid();");
using (SQLiteCommand command = new SQLiteCommand(query, sqlConn))
{
command.Parameters.Add(new SQLiteParameter()
{
ParameterName = "@parent_id", DbType = DbType.Int64, Value = element.ParentId
});
command.Parameters.Add(new SQLiteParameter()
{
ParameterName = "@position", DbType = DbType.Int64, Value = position
});
command.Parameters.Add(new SQLiteParameter()
{
ParameterName = "@name", DbType = DbType.AnsiString, Value = element.Name
});
element.Id = (long)command.ExecuteScalar();
}
}
}
catch (Exception ex)
{
error = ex.Message;
return(false);
}
return(true);
}