public HttpResponseMessage SaveAssetModelHierarchy(AssetModelDetail a) { //connect to database SqlConnection conn = new SqlConnection(); try { //connetionString="Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["EES_DB_ConnectionString"].ToString(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "InsDelAssetModelHieararchy"; cmd.Connection = conn; conn.Open(); //@AssetModelID int,@ParentID int=null,@ObjTypeID int,@Change char(1),@ID int=null, @ParentObjTypeId int SqlParameter aGid = new SqlParameter("@id", SqlDbType.Int); aGid.Value = a.Id; cmd.Parameters.Add(aGid); SqlParameter Gid = new SqlParameter("@AssetModelID", SqlDbType.Int); Gid.Value = a.AssetModelId; cmd.Parameters.Add(Gid); SqlParameter pid = new SqlParameter("@ParentID", SqlDbType.Int); pid.Value = a.ParentId; cmd.Parameters.Add(pid); SqlParameter oid = new SqlParameter("@ObjTypeID", SqlDbType.Int); oid.Value = a.ObjTypeId; cmd.Parameters.Add(oid); SqlParameter poid = new SqlParameter("@ParentObjTypeId", SqlDbType.Int); poid.Value = a.ParentObjTypeId; cmd.Parameters.Add(poid); SqlParameter flag = new SqlParameter("@Change", SqlDbType.VarChar); flag.Value = a.insupddelflag; cmd.Parameters.Add(flag); cmd.ExecuteScalar(); conn.Close(); //Logger.Trace(LogCategory.WebApp, "InsDelAssetModelHieararchy stored procedure is executed successfully", LogLevel.Information, null); return(new HttpResponseMessage(HttpStatusCode.OK)); } catch (Exception ex) { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } string str = ex.Message; //Logger.Error(ex, LogCategory.WebApp, "An error occured in SaveAssetModel() procedure", LogLevel.Error, null); return(Request.CreateErrorResponse(HttpStatusCode.NotFound, ex)); } }
public AssetModelDetail GetAssetModelHierarchy(int assetmodelId) { DataTable Tbl = new DataTable(); AssetModelDetail root = new AssetModelDetail(); try { //connect to database SqlConnection conn = new SqlConnection(); //connetionString="Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["EES_DB_ConnectionString"].ToString(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GetAssetModelDetail"; cmd.Connection = conn; SqlParameter Gid = new SqlParameter("@assetmodelID", SqlDbType.Int); Gid.Value = assetmodelId; cmd.Parameters.Add(Gid); DataSet ds = new DataSet(); SqlDataAdapter db = new SqlDataAdapter(cmd); db.Fill(ds); Tbl = ds.Tables[0]; //Logger.Trace(LogCategory.WebApp, "DataTable in GetAssetModels() procedure is loaded", LogLevel.Information, null); IEnumerable <AssetModelDetail> list = Tbl.DataTableToList <AssetModelDetail>(); Dictionary <int, AssetModelDetail> dict = list.ToDictionary(loc => loc.Id); foreach (AssetModelDetail loc in dict.Values) { if (loc.ParentId == 0) { continue; } AssetModelDetail parent = dict.Values.First(l => l.Id == loc.ParentId); parent.__children__.Add(loc); //if (loc.ParentId != loc.ObjTypeId) //{ // AssetModelDetail parent = dict[loc.ParentId];//(loc.ParentId == 0) ? dict.Values.First(l => l.ParentId == 0) : dict[loc.ParentId]; // parent.__children__.Add(loc); //} } if (dict.Count > 0) { root = dict.Values.First(loc => loc.ParentId == 0); } JsonSerializerSettings settings = new JsonSerializerSettings { ContractResolver = new CamelCasePropertyNamesContractResolver(), Formatting = Formatting.Indented }; //Logger.Trace(LogCategory.WebApp, "AssetModelDetail is set", LogLevel.Information, null); } catch (Exception ex) { //Logger.Error(ex, LogCategory.WebApp, "An error occured in GetAssetModels() procedure", LogLevel.Error, null); } return(root); }