Summary of issues with the. NET EF access to Oracle

Since recent projects on hand require the use of Oracle databases, I have built a three-tier framework for ASP. NET MVC + EF + bootstrap + log4net + unity, as shown in:

　　

One unit test using Microsoft's own, personal feeling light, so did not use NUnit.

During the use of Ef+oracle, I struggled with some problems, because I had been using Ef+sql SERVER before, as follows:

1. To prevent injection, filter using parameters, SQL SERVER is @, and Oracle uses:, as follows:

　　　　

        /// <summary>        ///test function/// </summary>         PublicDataTable Testsql (stringuserName) {            varFpmcustomsqldao = servicelocator.getservice<ifpmcustomsqldao>(); stringsql =@"SELECT * from Fpm_user where InStr (name,: userName) > 0"; varPara =NewOracleParameter ("UserName", UserName); //para. Value = UserName;            returnDB.        Executedatatable (Sql,para); }

and SQL Server is as follows:

　　

        /// <summary>        ///test function/// </summary>         PublicDataTable Testsql (stringuserName) {            varFpmcustomsqldao = servicelocator.getservice<ifpmcustomsqldao>(); stringsql =@"SELECT * from Fpm_user where name like '% @userName% ' > 0"; varPara =NewSqlParameter ("@userName", UserName); //para. Value = UserName;            returnDB.        Executedatatable (Sql,para); }

Also, it is important to note that although the syntax like ' percent-percent ' can be executed on Oracle clients, it is not possible to use InStr (name,: userName) > 0 instead, as for why not, there is no time to explore, wish to know a friend message to enlighten.

The SQL that the 2.Oracle client executes can be at the end of the table, and when multiple statements are executed together, it must be added at the end of each sentence, but in C # code, it is not allowed, otherwise the Ora-00933:sql command does not end correctly. As follows:

　　

            string @" SELECT * from                        fpm_user                        where  InStr (name,: userName) > 0";

If the above is not a problem, and

　　

            string @" SELECT * from                        fpm_user                        where  InStr (name,: UserName) > 0; ";

The "Ora-00933:sql command did not end correctly" issue occurs.

Temporarily discovered as above two and EF operation SQL Server not the same problem, continue to explore, then add!

Summary of issues with the. NET EF access to Oracle