상속: ISeriesCatalogRepository
예제 #1
0
        public void UpdateSeriesCatalog(string instanceIdentifier, string entityConnectionstring)
        {
            //var s = Ressources.EFMODELDEF_IN_CONNECTIONSTRING;
            //var constring = "metadata=res://*/ODM_1_1_1EFModel.csdl|res://*/ODM_1_1_1EFModel.ssdl|res://*/ODM_1_1_1EFModel.msl;provider=System.Data.SqlClient;provider connection string=" Data Source=tcp:bhi5g2ajst.database.windows.net,1433;Initial Catalog=HydroServertest2;User ID=HisCentralAdmin@bhi5g2ajst; Password=f3deratedResearch; Persist Security Info=true; MultipleActiveResultSets=True;App=EntityFramework";
            //var constring = "metadata=res://*/ODM_1_1_1EFModel.csdl|res://*/ODM_1_1_1EFModel.ssdl|res://*/ODM_1_1_1EFModel.msl;provider=System.Data.SqlClient;provider connection string="data source=mseul-cuahsi;initial catalog=HydroSample2;integrated security=true; MultipleActiveResultSets=True;App=EntityFramework";
            //var cleanedConnectionString = entityConnectionstring.Replace(s, string.Empty);
            BusinessObjectsUtils.UpdateCachedprocessStatusMessage(instanceIdentifier, CacheName, Ressources.IMPORT_STATUS_TIMESERIES);

            var ec = new EntityConnectionStringBuilder(entityConnectionstring);
            //clear SeriesCatolog table
            var repo = new SeriesCatalogRepository();
            repo.deleteAll(entityConnectionstring);

            SqlConnection sqlConnection1 = new SqlConnection(ec.ProviderConnectionString);

            var adp = new SqlDataAdapter();
            var cmd = new SqlCommand();
            var dataTable = new DataTable();
            var sb = new StringBuilder();
            sb.Append("SELECT dv.SiteID, s.SiteCode, s.SiteName, s.SiteType, dv.VariableID, v.VariableCode, v.VariableName, ");
            sb.Append("v.Speciation, v.VariableUnitsID, u.UnitsName AS VariableUnitsName, v.SampleMedium, ");
            sb.Append("v.ValueType, v.TimeSupport, v.TimeUnitsID, u1.UnitsName AS TimeUnitsName, v.DataType, ");
            sb.Append(" v.GeneralCategory, dv.MethodID, m.MethodDescription, dv.SourceID, so.Organization, ");
            sb.Append("so.SourceDescription, so.Citation, dv.QualityControlLevelID, qc.QualityControlLevelCode, dv.BeginDateTime, ");
            sb.Append("dv.EndDateTime, dv.BeginDateTimeUTC, dv.EndDateTimeUTC, dv.ValueCount ");
            sb.Append("FROM  (");
            sb.Append("SELECT SiteID, VariableID, MethodID, QualityControlLevelID, SourceID, MIN(LocalDateTime) AS BeginDateTime,");
            sb.Append("MAX(LocalDateTime) AS EndDateTime, MIN(DateTimeUTC) AS BeginDateTimeUTC, MAX(DateTimeUTC) AS EndDateTimeUTC, ");
            sb.Append("COUNT(DataValue) AS ValueCount ");
            sb.Append("FROM DataValues ");
            sb.Append("GROUP BY SiteID, VariableID, MethodID, QualityControlLevelID, SourceID) dv ");
            sb.Append("INNER JOIN dbo.Sites s ON dv.SiteID = s.SiteID ");
            sb.Append("INNER JOIN dbo.Variables v ON dv.VariableID = v.VariableID ");
            sb.Append("INNER JOIN dbo.Units u ON v.VariableUnitsID = u.UnitsID ");
            sb.Append("INNER JOIN dbo.Methods m ON dv.MethodID = m.MethodID ");
            sb.Append("INNER JOIN dbo.Units u1 ON v.TimeUnitsID = u1.UnitsID ");
            sb.Append("INNER JOIN dbo.Sources so ON dv.SourceID = so.SourceID ");
            sb.Append("INNER JOIN dbo.QualityControlLevels qc ON dv.QualityControlLevelID = qc.QualityControlLevelID ");
            sb.Append("GROUP BY   dv.SiteID, s.SiteCode, s.SiteName, s.SiteType, dv.VariableID, v.VariableCode, v.VariableName, v.Speciation, ");
            sb.Append("v.VariableUnitsID, u.UnitsName, v.SampleMedium, v.ValueType, v.TimeSupport, v.TimeUnitsID, u1.UnitsName, ");
            sb.Append("v.DataType, v.GeneralCategory, dv.MethodID, m.MethodDescription, dv.SourceID, so.Organization, ");
            sb.Append("so.SourceDescription, so.Citation, dv.QualityControlLevelID, qc.QualityControlLevelCode, dv.BeginDateTime, ");
            sb.Append("dv.EndDateTime, dv.BeginDateTimeUTC, dv.EndDateTimeUTC, dv.ValueCount ");
            sb.Append("ORDER BY   dv.SiteID, dv.VariableID, v.VariableUnitsID");

            cmd.CommandText = sb.ToString();

            cmd.CommandType = CommandType.Text;
            cmd.Connection = sqlConnection1;
            adp.SelectCommand = cmd;
            adp.Fill(dataTable);

            //List<SeriesCatalogDBModel> list = AutoMapper.Mapper.DynamicMap<IDataReader, List<SeriesCatalogDBModel>>(dataTable.CreateDataReader());

            //List<SeriesCatalogDBModel> list = dataTable.AsEnumerable().ToList();
            //                                   .Select(row=> new SeriesCatalogDBModel)
            // open the destination data
            using (SqlConnection destinationConnection =
                            new SqlConnection(ec.ProviderConnectionString))
            {
                // open the connection
                destinationConnection.Open();

                using (SqlBulkCopy bulkCopy =
                            new SqlBulkCopy(destinationConnection.ConnectionString, SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.CheckConstraints))
                {
                    //bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsTransfer);
                    //bulkCopy.NotifyAfter = 100;
                    bulkCopy.BatchSize = 50;

                    foreach (DataColumn dc in dataTable.Columns)
                    {
                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                    }

                    bulkCopy.DestinationTableName = "SeriesCatalog";
                    bulkCopy.WriteToServer(dataTable);
                }
            }
        }
        public JsonResult SeriesCatalogSearch(JQueryDataTablesModel jQueryDataTablesModel, string identifier)
        {
            int totalRecordCount;
            int searchRecordCount;

            //Get Connection string
            var entityConnectionString = HydroServerToolsUtils.BuildConnectionStringForUserName(HttpContext.User.Identity.Name);
            //var entityConnectionString = HydroServerToolsUtils.GetDBEntityConnectionStringByName(connectionName);

            if (String.IsNullOrEmpty(entityConnectionString))
            {
                ModelState.AddModelError(String.Empty, Ressources.HYDROSERVER_USERLOOKUP_FAILED);

            }

            var repository = new SeriesCatalogRepository();
            var items = repository.GetSeriesCatalog(entityConnectionString, startIndex: jQueryDataTablesModel.iDisplayStart,
                pageSize: jQueryDataTablesModel.iDisplayLength, sortedColumns: jQueryDataTablesModel.GetSortedColumns(),
                totalRecordCount: out totalRecordCount, searchRecordCount: out searchRecordCount, searchString: jQueryDataTablesModel.sSearch);

            var result = from c in items
                         select new[] {
                            //c.SeriesID,
                            //c.SiteID,
                            c.SiteCode,
                            c.SiteName,
                            c.SiteType,
                            //c.VariableID,
                            c.VariableCode,
                            c.VariableName,
                            c.Speciation,
                            //c.VariableUnitsID,
                            c.VariableUnitsName,
                            c.SampleMedium,
                            c.ValueType,
                            c.TimeSupport,
                            //c.TimeUnitsID,
                            c.TimeUnitsName,
                            c.DataType,
                            c.GeneralCategory,
                            //c.MethodID,
                            c.MethodDescription,
                            //c.SourceID,
                            c.Organization,
                            c.SourceDescription,
                            c.Citation,
                            //c.QualityControlLevelID,
                            c.QualityControlLevelCode,
                            c.BeginDateTime,
                            c.EndDateTime,
                            //c.BeginDateTimeUTC,
                            //c.EndDateTimeUTC,
                            c.ValueCount
                };

            return this.DataTablesJson(items: result,
                totalRecords: totalRecordCount,
                totalDisplayRecords: searchRecordCount,
                sEcho: jQueryDataTablesModel.sEcho);
        }