public QueryObject GetSigleQuery(int queryId, string userCode)
{
try
{
GetQueryObject PostDataArrived = new GetQueryObject()
{
QueryId = queryId.ToString(),
UserCode = userCode
};
ConnectionStringSettings connectionStringSetting = ConfigurationManager.ConnectionStrings["ISTATWebClientConnection"];
if (connectionStringSetting == null || string.IsNullOrEmpty(connectionStringSetting.ConnectionString))
{
throw new Exception("ConnectionString not set");
}
QueryWidget qw = new QueryWidget(connectionStringSetting.ConnectionString);
List <QueryObject> res = qw.Get(PostDataArrived);
if (res != null && res.Count == 1)
{
return(res[0]);
}
else
{
return(null);
}
}
catch (Exception)
{
return(null);
}
}
public List <QueryObject> Get(GetQueryObject PostDataArrived)
{
try
{
if (PostDataArrived == null || string.IsNullOrEmpty(PostDataArrived.UserCode))
{
throw new Exception("Input Error");
}
string sqlquery = string.Format("Select * from Query where UserCode='{0}'", PostDataArrived.UserCode.Replace("'", "''"));
if (!string.IsNullOrEmpty(PostDataArrived.QueryId))
{
sqlquery += string.Format(" and QueryId={0}", PostDataArrived.QueryId);
}
Sqlconn.Open();
try
{
DataTable dtres = new DataTable();
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
da.Fill(dtres);
}
}
List <QueryObject> queries = new List <QueryObject>();
foreach (DataRow query in dtres.Rows)
{
queries.Add(new QueryObject()
{
QueryId = query["QueryId"].ToString(),
Title = query["Title"].ToString(),
_DataflowString = query["Dataflow"].ToString(),
_CriteriaString = query["Criteria"].ToString(),
_ConfigurationString = query["Configuration"].ToString(),
_LayoutString = query["Layout"].ToString(),
});
}
return(queries);
}
catch (Exception) { throw; }
finally
{
Sqlconn.Close();
}
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw new Exception(string.Format(ErrorOccuredMess, ex.Message));
}
}
public QueryObject Add(GetQueryObject PostDataArrived)
{
try
{
if (PostDataArrived == null || string.IsNullOrEmpty(PostDataArrived.UserCode) || PostDataArrived.Query == null)
{
throw new Exception("Input Error");
}
string sqlquery = string.Format(@"INSERT INTO Query ([UserCode] ,[Title] ,[Dataflow] ,[Criteria] ,[Layout], [Configuration])
VALUES('{0}','{1}','{2}','{3}','{4}','{5}')",
PostDataArrived.UserCode.Replace("'", "''"), PostDataArrived.Query.Title.Replace("'", "''"), PostDataArrived.Query._DataflowString, PostDataArrived.Query._CriteriaString, PostDataArrived.Query._LayoutString, PostDataArrived.Query._ConfigurationString);
Sqlconn.Open();
try
{
string sqlqueryCount = string.Format("Select count(*) from Query where UserCode='{0}' And Title='{1}'", PostDataArrived.UserCode.Replace("'", "''"), PostDataArrived.Query.Title.Replace("'", "''"));
using (SqlCommand comm = new SqlCommand(sqlqueryCount, Sqlconn))
{
int count = (int)comm.ExecuteScalar();
if (count > 0)
{
throw new Exception("Already exist Query for this user with this title");
}
}
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
int resAdd = comm.ExecuteNonQuery();
if (resAdd == 0)
{
throw new Exception("Query not insert");
}
}
sqlquery = string.Format("Select QueryId from Query where UserCode='{0}' And Title='{1}'", PostDataArrived.UserCode.Replace("'", "''"), PostDataArrived.Query.Title.Replace("'", "''"));
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
PostDataArrived.Query.QueryId = comm.ExecuteScalar().ToString();
}
return(PostDataArrived.Query);
}
catch (Exception) { throw; }
finally
{
Sqlconn.Close();
}
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw new Exception(string.Format(ErrorOccuredMess, ex.Message));
}
}
public ActionResult Del()
{
try
{
GetQueryObject PostDataArrived = CS.GetPostData <GetQueryObject>(this.Request);
ConnectionStringSettings connectionStringSetting = ConfigurationManager.ConnectionStrings["ISTATWebClientConnection"];
if (connectionStringSetting == null || string.IsNullOrEmpty(connectionStringSetting.ConnectionString))
{
throw new Exception("ConnectionString not set");
}
QueryWidget qw = new QueryWidget(connectionStringSetting.ConnectionString);
return(CS.ReturnForJQuery(qw.Delete(PostDataArrived)));
}
catch (Exception ex)
{
return(CS.ReturnForJQuery(ex.Message));
}
}
public string Delete(GetQueryObject PostDataArrived)
{
try
{
if (string.IsNullOrEmpty(PostDataArrived.QueryId))
{
throw new Exception("Input Error");
}
string sqlquery = string.Format(@"DELETE FROM Query WHERE QueryId={0}", PostDataArrived.QueryId);
Sqlconn.Open();
try
{
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
int resAdd = comm.ExecuteNonQuery();
if (resAdd == 0)
{
throw new Exception("Query not insert");
}
}
return("{\"DeleteResult\" : true, }");
}
catch (Exception) { throw; }
finally
{
Sqlconn.Close();
}
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw new Exception(string.Format(ErrorOccuredMess, ex.Message));
}
}
