Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

ASP Schnell auf Daten Zugreifen ohne das Daten-Model in EntityFrameworkCore

22.02.2023 (👁15181)


Schneller Datenbank Zugriff in Asp.Net Core .Net7 mit Entity Framework über ADO ohne Tabellen Model

Wie kann man auf eine Datenbank in Asp.Net zugreifen, wenn man das Datenmodell nicht anbinden möchte und schnelle Operationen ausführen will?

Hierzu kann man direkt mit dem ADO Modell auf SQL Server per SQL Syntax zugreifen.

Als Datenbank Connection kann man aus der ApplicationDBContext den dbContext.Connection verwenden.

Ein Bild, das Text, Screenshot, drinnen, Monitor enthält.

Automatisch generierte Beschreibung

Hier ein Code-Beispiel eines Service Controllers, der nur für Bearbeitungszwecke auf System Dateien zugreift

using Microsoft.AspNetCore.Authorization;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Data.SqlClient;

using Microsoft.EntityFrameworkCore;

using System.Data;

using System.Security.Claims;

using webapi_codedocu.Helpers;

using webapp_codedocu.Data;

 

namespace webapi_codedocu.Controllers

{

    [Route("api/[controller]")]

    [ApiController]

    public class ServiceController : ControllerBase

    {

        //--< Variables >--

        private readonly ILogger<ArticlesController> _logger;

        private readonly ApplicationDbContext _dbContext;

        //--</ Variables >--

 

        public ServiceController(ApplicationDbContext dbContext, ILogger<ArticlesController> logger)

        {

            //----< Init >----

            _dbContext = dbContext;

            _logger = logger;

            //----</ Init >----   

        }

 

        /// <summary>

        /// extracts Text from HTML

        /// </summary>

        /// <returns></returns>

        //[Authorize (Roles ="Service")] //only logged in

        [Authorize] //*only logged, but check ClaimTypes.Role in Code

        [HttpGet("correct_Text")]

        public async Task<ActionResult<string>>Correct_Text()

        {

            //--------< correct_Text() >--------

            _logger.LogWarning("---< Correkt_Text >----");

 

            //< check_owner >

            //get client User to check if is owner

            var roles = HttpContext.User.FindFirstValue(ClaimTypes.Role);

            if (roles == "")

            {

                return BadRequest("no rights");

            }

            else if (roles.Contains("Service")==false)

            {

                return BadRequest("no rights for service");

            }

            //</ check_owner >

 

            int i = 0;

 

            //load articles from db

            //load some columns

            //var articles = await _dbContext.tbl_Articles.Where(x=>x.Content_Text==null).Take(100).Select(a=>new Article_Html{ GuidArticle= a.GuidArticle,Content_Html= a.Content_Html ?? ""}).ToListAsync();

            //load full articles

            var articles = await _dbContext.tbl_Articles.Where(x=>x.Content_Text==null).Take(4000).ToListAsync();

            foreach( var article in articles)

            {

                //----< @Foreach: Articles >----

                try

                {

                    i++;

                    string sHtml = article.Content_Html ?? "";

                   

                    string sText = Html_Helper.HTML_to_Text(sHtml);                   

                    sText = sText.Trim();

                        //--< Save_new_Text >--

                        if (sText.Length > 0) {

                        if (i % 10 == 0) {

                            int i20 = 20;

                            if (sText.Length < 20) i20 = sText.Length;

                            _logger.LogDebug("-- Correkt_Text: " + i++ + " : " + sText?.Substring(0, i20));

                        }

                        //< update Server >

                        //Update only Field

                        //_dbContext.tbl_Articles.Attach(article).Property(x=>x.Content_Text).IsModified=true;

                        //await _dbContext.SaveChangesAsync();

                   

                        //Update only Field EntityFrameworkCore.7

                        _dbContext.tbl_Articles.Where(a => a.GuidArticle == article.GuidArticle)

                        .ExecuteUpdate(b => b.SetProperty(u => u.Content_Text, sText)    );

                        //</ update Server >

                        //--</ Save_new_Text >--

                    }

                }

                catch (Exception ex)

                {

                    return BadRequest("error: " + ex.Message);

                    throw;

                }

                //----</ @Foreach: Articles >----

            }

           

            //-< Final >-

            _logger.LogWarning("---</ Correkt_Text >----");

            string sResult = "---- < Fertig >----";

            return Ok(sResult);  //👍 better loading

            //-</ Final >-

            //--------</ correct_Text() >--------

        }

 

 

        /// <summary>

        /// extracts Text from HTML

        /// </summary>

        /// <returns></returns>

        //[Authorize (Roles ="Service")] //only logged in

        //[Authorize] //*only logged, but check ClaimTypes.Role in Code

        [HttpGet("correct_folders")]

        public async Task<ActionResult<string>> Correct_Folders()

        {

            //--------< correct_Text() >--------

            _logger.LogInformation("---< Correct_Folders >----");

 

            //< check database >

            if (_dbContext==null) return BadRequest("Database is not connected");

            SqlConnection dbConnection = (SqlConnection) _dbContext.Database.GetDbConnection()  ;

            if (dbConnection == null) return BadRequest("Database Connection is not ok");

            //</ check database >

 

            ////< check_permission >

            ////get client User to check if is owner

            //var roles = HttpContext.User.FindFirstValue(ClaimTypes.Role);

            //if (roles == null)

            //{

            //    return BadRequest("no roles, not logged in");

            //}

            //else if (roles == "")

            //{

            //    return BadRequest("no rights");

            //}

            //else if (roles.Contains("Service") == false)

            //{

            //    return BadRequest("no rights for service");

            //}

            ////</ check_permission >

 

            int i = 0;

 

            if(dbConnection.State!=ConnectionState.Open) dbConnection.Open();

 

 

            //--< Daten holen >--

            string sSQL = "SELECT TOP 5000 * FROM tbl_Articles WHERE Folder IS NULL";

            var command = new SqlCommand(sSQL, dbConnection);

            var adapter = new SqlDataAdapter(command);

            var dataset = new DataSet();

 

            adapter.Fill(dataset, sSQL);

            var table = dataset.Tables[0];

            //--</ Daten holen >--

           

            foreach (DataRow row in table.Rows)

            {

                //----< @Foreach: Articles >----

                try

                {

                    i++;

                    string guidArticle = (string)row["guidArticle"] ?? "";

                    _logger.LogInformation("xx i=" + i + " guid=" + guidArticle);

                    string sFolderPath = "";

 

                    //< Area >

                    string sArea = "";

                    if (row["IDArea"] is not System.DBNull) {

                        int IDArea= Convert.ToInt32(row["IDArea"].ToString());

                        sArea= get_Area(IDArea) ?? "";

                        if (sArea != "") sFolderPath = "/" + sArea;

                    }                   

                   

                    //</ Area >

 

                    //< Folders >

                    if (row["IDFolder"] is not System.DBNull){

                        int IDFolder_Article = Convert.ToInt32((row["IDFolder"]).ToString());

                        string sFolders = get_FolderPath(IDFolder_Article) ?? "";

                        if (sFolders != "") sFolderPath += sFolders; //* kommt als /folder1/folder2

                    }

                    //</ Folders >

 

                   

 

                  

 

                    if (sFolderPath != "")

                    {

                        //---< update folders >--

                   

                        string sHTML = sFolderPath;

                        //< remove Charaters >

                        sHTML = Html_Helper.remove_Brackets_Characters (sHTML); //sonderzeichen und Hochkomma loeschen

                        sHTML = Html_Helper.remove_script(sHTML);

                        sHTML = Html_Helper.remove_Head(sHTML);

                        sHTML = Html_Helper.remove_Tags(sHTML);

                        sHTML = Html_Helper.replace_HTML_Umlaute(sHTML);

                        sHTML = Html_Helper.remove_Control_Characters(sHTML);

                        sHTML = Html_Helper.remove_HTML_Characters(sHTML);

                        sHTML = Html_Helper.remove_Punctuation_Mark_Characters(sHTML);

                        sHTML = Html_Helper.remove_Brackets_Characters(sHTML);

                        //</ remove Charaters >

                        sFolderPath=sHTML;

 

                        string sUpdate = "UPDATE [tbl_Articles] SET [Folder] = '" + sFolderPath + "' FROM tbl_Articles WHERE [GuidArticle]='" + guidArticle + "'";// WHERE Folder = NULL";

                        var cmdUpdate = new SqlCommand(sUpdate, dbConnection);

                        cmdUpdate.ExecuteNonQuery();

                    }

 

 

                   

                    //--< Save_new_Text >--

                    if (i % 10 == 0)

                    {

                        if (sFolderPath.Length > 0)

                        {                           

                            _logger.LogDebug("-- Correct_Folders: " + i++ + " : " + sFolderPath );

                        }

                        //< update Server >

                       

                        //--</ Save_new_Text >--

                    }

                }

                catch (Exception ex)

                {

                    //*either error or sub throw exception

                    return BadRequest("error: " + ex.Message);

                    throw;

                }

                //----</ @Foreach: Articles >----

            }

 

            //-< Final >-

            _logger.LogInformation("---</ Correct_Folders >----");

            string sResult = "---- < Fertig >----";

            return Ok(sResult);  //👍 better loading

            //-</ Final >-

            //--------</ correct_Text() >--------

        }

 

 

        private string? get_FolderPath(int IDStartfolder)

        {

            //----< get_FolderPath() >----

            //< check database >

            if (_dbContext == null) { throw new Exception("Database is not connected");}

 

            SqlConnection dbConnection = (SqlConnection)_dbContext.Database.GetDbConnection();

            if (dbConnection == null)

            {

                throw new Exception("Database Connection is not ok");

            }

            //</ check database >

 

            string sFolderPath = "";

            int IDParent = IDStartfolder;

            while (IDParent != 0)

            {

                //< lade Sys-daten >

                string sql = "SELECT TOP 1 IDFolder,IDParent, IDArea,Title0 FROM tblSYS_Folders WHERE IDFolder=" + IDParent;

                var command = new SqlCommand(sql, dbConnection);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())

                {

                    IDParent = Convert.ToInt32(reader["IDParent"]);

                    string? sFolder = reader["Title0"].ToString();

                    if (sFolder != null)

                    {

                        sFolderPath += "/" + sFolder;

                    }

                }

                else {

                    break;

                }

                //< lade Sys-daten >

            }

 

            return sFolderPath;

            //----</ get_FolderPath() >----

        }

 

        private string? get_Area(int IDArea)

        {

            //------< get_Area() >------

            //< check database >

            if (_dbContext == null) { throw new Exception("Database is not connected"); }

 

            SqlConnection dbConnection = (SqlConnection)_dbContext.Database.GetDbConnection();

            if (dbConnection == null)

            {

                throw new Exception("Database Connection is not ok");

            }

            //</ check database >

 

            string? sArea = "";

                //< lade Sys-daten >

                string sql = "SELECT TOP 1 Area FROM tblSYS_Areas WHERE IDArea=" + IDArea;

                var command = new SqlCommand(sql, dbConnection);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())

                {

                    sArea = reader["Area"].ToString();                   

                }

                //< lade Sys-daten >

            return sArea;

            //------</ get_Area() >------

        }

    }

}