public TemplateObject Add(GetTemplateObject PostDataArrived)
{
try
{
if (PostDataArrived == null
|| PostDataArrived.Template == null)
throw new Exception("Input Error");
Sqlconn.Open();
try
{
string sqlquery;
int templateId = -1;
string sqlqueryCount = string.Format("Select [TemplateId] from Template where [tmplKey]='{0}'",
new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(
PostDataArrived.Template.Dataflow.id
+ "+" + PostDataArrived.Template.Dataflow.agency
+ "+" + PostDataArrived.Template.Dataflow.version
+ "+" + PostDataArrived.Template.Configuration.EndPoint).Replace("'", "''"));
using (SqlCommand comm = new SqlCommand(sqlqueryCount, Sqlconn))
{
var tmplId = comm.ExecuteScalar();
if (tmplId != null)
templateId = (int)tmplId;
}
if (templateId >= 0)
{
// update
sqlquery = string.Format(@"UPDATE Template SET [Title]='{0}' ,[Dataflow]='{1}' ,[Criteria]='{2}' ,[Layout]='{3}', [Configuration]='{4}',[tmplKey]='{5}',[HideDimension]='{6}',[BlockXAxe]={7},[BlockYAxe]={8},[BlockZAxe]={9},[EnableCriteria]={10},[EnableDecimal]={11},[EnableVaration]={12} WHERE [TemplateId]={13}",
PostDataArrived.Template.Title.Replace("'", "''"),
PostDataArrived.Template._DataflowString,
PostDataArrived.Template._CriteriaString,
PostDataArrived.Template._LayoutString,
PostDataArrived.Template._ConfigurationString,
PostDataArrived.Template._QueryUniqueKeyString,
PostDataArrived.Template._HideDimensionString,
(PostDataArrived.Template.BlockXAxe) ? 1 : 0,
(PostDataArrived.Template.BlockYAxe) ? 1 : 0,
(PostDataArrived.Template.BlockZAxe) ? 1 : 0,
(PostDataArrived.Template.EnableCriteria) ? 1 : 0,
(PostDataArrived.Template.EnableDecimal) ? 1 : 0,
(PostDataArrived.Template.EnableVaration) ? 1 : 0,
templateId);
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
int resAdd = comm.ExecuteNonQuery();
if (resAdd == 0)
throw new Exception("Template not update");
}
}
else {
// new
sqlquery = string.Format(@"INSERT INTO Template ([Title] ,[Dataflow] ,[Criteria] ,[Layout], [Configuration],[HideDimension],[BlockXAxe],[BlockYAxe],[BlockZAxe],[EnableCriteria],[EnableDecimal],[EnableVaration],[tmplKey])
VALUES('{0}','{1}','{2}','{3}','{4}','{5}',{6},{7},{8},{9},{10},{11},'{12}')",
PostDataArrived.Template.Title.Replace("'", "''"),
PostDataArrived.Template._DataflowString,
PostDataArrived.Template._CriteriaString,
PostDataArrived.Template._LayoutString,
PostDataArrived.Template._ConfigurationString,
PostDataArrived.Template._HideDimensionString,
(PostDataArrived.Template.BlockXAxe)?1:0,
(PostDataArrived.Template.BlockYAxe) ? 1 : 0,
(PostDataArrived.Template.BlockZAxe) ? 1 : 0,
(PostDataArrived.Template.EnableCriteria) ? 1 : 0,
(PostDataArrived.Template.EnableDecimal) ? 1 : 0,
(PostDataArrived.Template.EnableVaration) ? 1 : 0,
PostDataArrived.Template._QueryUniqueKeyString
);
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
int resAdd = comm.ExecuteNonQuery();
if (resAdd == 0)
throw new Exception("Template not insert");
}
}
sqlquery = string.Format("Select [TemplateId] from Template where [tmplKey]='{0}'",
PostDataArrived.Template._QueryUniqueKeyString.Replace("'", "''"));
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
PostDataArrived.Template.TemplateId = comm.ExecuteScalar().ToString();
}
return PostDataArrived.Template;
}
catch (Exception) { throw; }
finally
{
Sqlconn.Close();
}
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw new Exception(string.Format(ErrorOccuredMess, ex.Message));
}
}
public List<TemplateObject> Get(GetTemplateObject PostDataArrived)
{
try
{
//if (PostDataArrived == null
// || PostDataArrived.Template == null)
// throw new Exception("Input Error");
Sqlconn.Open();
try
{
List<TemplateObject> tmpListTemplateObjects = new List<TemplateObject>();
string sqlquery;
SqlDataReader dtReader;
sqlquery = "Select * from Template";
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
dtReader = comm.ExecuteReader();
while (dtReader.Read())
{
TemplateObject tmpObj = new TemplateObject();
tmpObj.TemplateId = dtReader["TemplateId"].ToString();
tmpObj.Title = (string)dtReader["Title"];
tmpObj.Configuration = new JavaScriptSerializer().Deserialize<EndpointSettings>((string)dtReader["Configuration"]);
tmpObj.Dataflow = new JavaScriptSerializer().Deserialize<MaintenableObj>((string)dtReader["Dataflow"]);
tmpObj.Criteria = new JavaScriptSerializer().Deserialize<Dictionary<string, List<string>>>((string)dtReader["Criteria"]);
tmpObj.Layout = new JavaScriptSerializer().Deserialize<LayoutObj>((string)dtReader["Layout"]);
tmpObj.HideDimension = new JavaScriptSerializer().Deserialize<List<string>>((string)dtReader["HideDimension"]);
tmpObj.BlockXAxe = (bool)dtReader["BlockXAxe"];
tmpObj.BlockYAxe = (bool)dtReader["BlockYAxe"];
tmpObj.BlockZAxe = (bool)dtReader["BlockZAxe"];
tmpObj.EnableCriteria = (bool)dtReader["EnableCriteria"];
tmpObj.EnableVaration = (bool)dtReader["EnableVaration"];
tmpObj.EnableDecimal = (bool)dtReader["EnableDecimal"];
tmpListTemplateObjects.Add(tmpObj);
}
}
dtReader.Close();
return tmpListTemplateObjects;
}
catch (Exception) { throw; }
finally
{
Sqlconn.Close();
}
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw new Exception(string.Format(ErrorOccuredMess, ex.Message));
}
}
public TemplateObject GetSingle(GetTemplateObject PostDataArrived)
{
try
{
Sqlconn.Open();
try
{
TemplateObject tmpObj = null;
SqlDataReader dtReader = null;
string sqlquery = string.Format("Select * from Template where [tmplKey]='{0}'",
new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(
PostDataArrived.Template.Dataflow.id
+ "+" + PostDataArrived.Template.Dataflow.agency
+ "+" + PostDataArrived.Template.Dataflow.version
+ "+" + PostDataArrived.Template.Configuration.EndPoint).Replace("'", "''"));
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
dtReader = comm.ExecuteReader();
if(dtReader.Read())
{
tmpObj = new TemplateObject();
tmpObj.TemplateId = dtReader["TemplateId"].ToString();
tmpObj.Title = (string)dtReader["Title"];
tmpObj.Configuration = new JavaScriptSerializer().Deserialize<EndpointSettings>((string)dtReader["Configuration"]);
tmpObj.Dataflow = new JavaScriptSerializer().Deserialize<MaintenableObj>((string)dtReader["Dataflow"]);
tmpObj.Criteria = new JavaScriptSerializer().Deserialize<Dictionary<string, List<string>>>((string)dtReader["Criteria"]);
tmpObj.Layout = new JavaScriptSerializer().Deserialize<LayoutObj>((string)dtReader["Layout"]);
tmpObj.HideDimension = new JavaScriptSerializer().Deserialize<List<string>>((string)dtReader["HideDimension"]);
tmpObj.BlockXAxe = (bool)dtReader["BlockXAxe"];
tmpObj.BlockYAxe = (bool)dtReader["BlockYAxe"];
tmpObj.BlockZAxe = (bool)dtReader["BlockZAxe"];
tmpObj.EnableCriteria = (bool)dtReader["EnableCriteria"];
tmpObj.EnableVaration = (bool)dtReader["EnableVaration"];
tmpObj.EnableDecimal = (bool)dtReader["EnableDecimal"];
}
}
dtReader.Close();
return tmpObj;
}
catch (Exception) { throw; }
finally
{
Sqlconn.Close();
}
}
catch (Exception ex)
{
Logger.Warn(ex.Message, ex);
throw new Exception(string.Format(ErrorOccuredMess, ex.Message));
}
}
public string Delete(GetTemplateObject PostDataArrived)
{
try
{
if (string.IsNullOrEmpty(PostDataArrived.TemplateId))
throw new Exception("Input Error");
string sqlquery = string.Format(@"DELETE FROM Template WHERE [TemplateId]={0}", PostDataArrived.TemplateId);
Sqlconn.Open();
try
{
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
int resAdd = comm.ExecuteNonQuery();
if (resAdd == 0)
throw new Exception("Template not delete");
}
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));
}
}