public int Save(PCAxis.Query.SavedQuery query, int?id)
{
query.Stats = null;
string pxsjson = JsonConvert.SerializeObject(query);
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand(
@"insert into
SavedQueryMeta
(
DataSourceType,
DatabaseId,
DataSourceId,
[Status],
StatusUse,
StatusChange,
OwnerId,
MyDescription,
CreatedDate,
SavedQueryFormat,
SavedQueryStorage,
QueryText,
Runs,
Fails
)
values
(
@databaseType,
@databaseId,
@mainTable,
'A',
'P',
'P',
'Anonymous',
@title,
@creationDate,
'PXSJSON',
'D',
@query,
0,
0
);
SELECT @@IDENTITY AS 'Identity';", conn);
cmd.Parameters.AddWithValue("databaseType", query.Sources[0].Type);
cmd.Parameters.AddWithValue("databaseId", query.Sources[0].DatabaseId);
cmd.Parameters.AddWithValue("mainTable", GetMaintable(query.Sources[0]));
cmd.Parameters.AddWithValue("title", "");
cmd.Parameters.AddWithValue("creationDate", DateTime.Now);
cmd.Parameters.AddWithValue("query", pxsjson);
int newid = Convert.ToInt32(cmd.ExecuteScalar());
return(newid);
}
return(-1);
}
public PCAxis.Query.SavedQuery Load(int id)
{
using (var conn = new Microsoft.Data.SqlClient.SqlConnection(_connectionString))
{
conn.Open();
var cmd = new Microsoft.Data.SqlClient.SqlCommand("select QueryText from SavedQueryMeta where QueryId = @queryId", conn);
cmd.Parameters.AddWithValue("queryId", id);
string query = cmd.ExecuteScalar() as string;
PCAxis.Query.SavedQuery sq = JsonHelper.Deserialize <PCAxis.Query.SavedQuery>(query) as PCAxis.Query.SavedQuery;
return(sq);
}
return(null);
}