Oscar.EntityFrameworkCore.Sql 7.0.0

.NET 6.0

dotnet add package Oscar.EntityFrameworkCore.Sql --version 7.0.0

NuGet\Install-Package Oscar.EntityFrameworkCore.Sql -Version 7.0.0

This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.

<PackageReference Include="Oscar.EntityFrameworkCore.Sql" Version="7.0.0" />

For projects that support PackageReference, copy this XML node into the project file to reference the package.

paket add Oscar.EntityFrameworkCore.Sql --version 7.0.0

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

#r "nuget: Oscar.EntityFrameworkCore.Sql, 7.0.0"

#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.

// Install Oscar.EntityFrameworkCore.Sql as a Cake Addin
#addin nuget:?package=Oscar.EntityFrameworkCore.Sql&version=7.0.0

// Install Oscar.EntityFrameworkCore.Sql as a Cake Tool
#tool nuget:?package=Oscar.EntityFrameworkCore.Sql&version=7.0.0

The NuGet Team does not provide support for this client. Please contact its maintainers for support.

快速示例

DEMO

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.DependencyInjection;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.OscarClient;

namespace ConsoleTest;

internal class Program
{
    static async Task Main(string[] args)
    {
        var connectStrings = "Server=10.1.1.66;Port=2003;User Id=SYSDBA;Password=szoscar55;Database=OSRDB;";
        var optionsBuilder = new DbContextOptionsBuilder<UserContext>();
        optionsBuilder.UseSql(connectStrings, optionBuilder => { /* 选项配置 */ }).EnableSensitiveDataLogging();

        using (var conn = new OscarConnection(connectStrings))
        {
            conn.Open();
            using (var command = conn.CreateCommand())
            {
                command.ExecuteNonQuery("DROP TABLE IF EXISTS USERS CASCADE;");
                command.ExecuteNonQuery("DROP TABLE IF EXISTS BLOGS CASCADE;");
                command.ExecuteNonQuery("DROP TABLE IF EXISTS Requirements CASCADE;");
                command.ExecuteNonQuery("CREATE TABLE Users (UserId INT8 AUTO_INCREMENT PRIMARY KEY, Age INT, Name VARCHAR(128), BRITHDATE TIMESTAMP, GenId INT AS (Age + 5));");
                command.ExecuteNonQuery("CREATE TABLE BLOGS (ID INT AUTO_INCREMENT PRIMARY KEY, USERID INT, URL VARCHAR(128));");
                command.ExecuteNonQuery("CREATE TABLE Requirements (PKID INT8 PRIMARY KEY, AttributeList TEXT, Code TEXT, CreateTime TIMESTAMP, CreatorID INT8, Description TEXT, FileList TEXT, Name TEXT," +
                    " ParentID INT8, PhysicalID TEXT, ProjectID INT8, RequirementType INT8, RequirementValidatingMethod INT8, Source TEXT, TreeID LONG);");
            }

        }
        // 

        //using (var conn = new OscarConnection(connectStrings))
        //{
        //    conn.Open();
        //    using (var command = conn.CreateCommand())
        //    {
        //        // command.CommandText = "declare rowcount int := 0; begin  update users set name = 'user' where userid > 1; rowcount := SQL%ROWCOUNT; select rowcount; end;";
        //        command.CommandText = "update users set name = 'user' where userid > 2;";
        //        using (var reader = command.ExecuteReader())
        //        {
        //            while (reader.Read())
        //            {
        //                Console.WriteLine(reader.GetInt32(0));
        //            }
        //        }
        //    }
        //}

        //using (var conn = new OscarConnection(connectStrings))
        //{
        //    conn.Open();
        //    using (var command = conn.CreateCommand())
        //    {
        //        command.CommandText = "INSERT INTO SYSDBA.Users (UserId, Age, Name, BrithDate) VALUES (?, ?, ?, ?) RETURN UserId";
        //        command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.BigInt, Value = 7201755323293077600L});
        //        command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.Int, Value = 0 });
        //        command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.Text, Value = "u1" });
        //        command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.TimeStamp, Value = DateTime.Parse("2024-12-05 10:15:00.000") });
        //        using (var reader = command.ExecuteReader())
        //        {
        //            while (reader.Read())
        //            {
        //                Console.WriteLine(reader.GetString(0));
        //            }
        //        }
        //    }
        //}

        //        using (var conn = new OscarConnection(connectStrings))
        //        {
        //            conn.Open();
        //            using (var command = conn.CreateCommand())
        //            {
        //                command.CommandText = @"
        //begin
        //INSERT INTO SYSDBA.Users (Name,BrithDate) VALUES (?, ?),(?, ?),(?, ?),(?, ?) RETURN UserId;
        //end;
        //";
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.Text, Value = "u1" });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.TimeStamp, Value = DateTime.Parse("2024-12-05 10:15:00.000") });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.Text, Value = "u2" });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.TimeStamp, Value = DateTime.Parse("2024-12-15 10:15:00.000") });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.Text, Value = "u3" });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.TimeStamp, Value = DateTime.Parse("2024-12-25 10:15:00.000") });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.Text, Value = "u4" });
        //                command.Parameters.Add(new OscarParameter() { OscarDbType = OscarDbType.TimeStamp, Value = DateTime.Parse("2024-12-26 10:15:00.000") });
        //                using (var reader = command.ExecuteReader())
        //                {
        //                    while (reader.Read())
        //                    {
        //                        Console.WriteLine(reader.GetInt32(0));
        //                    }
        //                }
        //            }
        //        }



        using (var context = new UserContext(optionsBuilder.Options))
        {
            // to do
            // 情况一
            //var user1 = new User() { UserId = 1, Name = "u1", BrithDate = DateTime.Parse("2024-12-25 10:15:00.000") };
            //var user2 = new User() { UserId = 2, Name = "u2", BrithDate = DateTime.Parse("2024-10-25 10:15:00.000") };
            //var user3 = new User() { UserId = 3, Name = "u3", BrithDate = DateTime.Parse("2024-11-25 10:15:00.000") };
            //var user4 = new User() { UserId = 4, Name = "u4", BrithDate = DateTime.Parse("2023-11-25 10:15:01.010") };

            //context.Users.Add(user1);
            //context.Users.Add(user2);
            //context.Users.Add(user3);
            //context.Users.Add(user4);
            //context.SaveChanges();
            //await context.SaveChangesAsync();


            // 情况二
            var user1 = new User() { Name = "u1", Age = 12, BrithDate = DateTime.Parse("2024-12-25 10:15:00.000") };
            var user2 = new User() { Name = "u2", Age = 13, BrithDate = DateTime.Parse("2024-10-25 10:15:00.000") };
            var user3 = new User() { Name = "u3", Age = 14, BrithDate = DateTime.Parse("2024-11-25 10:15:00.000") };
            var user4 = new User() { Name = "u4", Age = 15, BrithDate = DateTime.Parse("2023-11-25 10:15:01.010") };

            var blog1 = new Blog() { UserId = user1.UserId, Url = "blog01" };
            var blog2 = new Blog() { UserId = user2.UserId, Url = "blog02" };
            var blog3 = new Blog() { UserId = user3.UserId, Url = "blog03" };
            var blog4 = new Blog() { UserId = user4.UserId, Url = "blog04" };

            context.Users.Add(user1);
            context.Users.Add(user2);
            context.Users.Add(user3);
            context.Users.Add(user4);
            context.Blogs.Add(blog1);
            context.Blogs.Add(blog2);
            context.Blogs.Add(blog3);
            context.Blogs.Add(blog4);
            context.SaveChanges();
            await context.SaveChangesAsync();

            

            var user = context.Users.FirstOrDefault(o => o.UserId == 3);
            if (user != null)
            {
                // context.Users.Where(o=> o.UserId > 3).ExecuteDelete();

                context.Users.Where(o => o.UserId < 3).ExecuteUpdate(sets => sets.SetProperty(u => u.Age, 20).SetProperty(u => u.Name, "name"));
            }

            user = context.Users.FirstOrDefault(o => o.UserId == 3);
            if (user != null)
            {
                user.BrithDate = DateTime.Parse("1991-08-27 12:00:01.101000");
                // context.Attach<User>(user);

                await context.SaveChangesAsync();
            }

            // 情况三
            //var user1 = new User() { Name = "u1", BrithDate = DateTime.Parse("2024-12-25 10:15:00.000") };
            //context.Users.Add(user1);

            //// context.SaveChanges();
            //await context.SaveChangesAsync();

            //var users = context.Users.Where(b => b.UserId > 0)
            //    .OrderBy(o => o.UserId)
            //    .Skip(1).Take(5)
            //    .ToList();






            // 情况5
            //var users = new List<User>();
            //var blogs = new List<Blog>();
            //IEnumerable<Requirement> requirements;
            //for (var i = 0; i < 50; i++)
            //{
            //    for (var j = 1; j < 21; j++)
            //    {
            //        var user = new User() { UserId = i*20 + j, Name = "u1", Age = 1, BrithDate = DateTime.Parse("2024-12-25 10:15:00.000") };
            //        users.Add(user);

            //        var blog = new Blog() { Id = i*20 + j, UserId = user.UserId, Url = "blog01" };
            //        blogs.Add(blog);
            //    }
            //}
            //long initid = 7201755323293077590L;
            //requirements = Enumerable.Range(1, 5000).Select(id => new Requirement()
            //{
            //    PKID = id + initid,
            //    AttributeList = "110000005B5D",
            //    Code = $"node{id}",
            //    CreateTime = DateTime.Now,
            //    CreatorID = -2,
            //    Description = "11000000",
            //    FileList = "node-",
            //    Name = "",
            //    ParentID = 1,
            //    PhysicalID = "",
            //    ProjectID = 2,
            //    RequirementType = 3,
            //    RequirementValidatingMethod = 4,
            //    Source = "",
            //    TreeID = 5
            //});


            //try
            //{

            //    var keys = users.GroupBy(u => u.UserId).Where(g => g.Count() > 1);

            //    foreach (var item in keys)
            //    {
            //        Console.WriteLine(item.Key);
            //    }

            //    context.Users.AddRange(users);

            //    context.Requirements.AddRange(requirements);

            //    var count = requirements.Where(u => u.PKID == 7201755323293077855L).Count();

            //    var num = context.SaveChanges();
            //}
            //catch (Exception ex)
            //{
            //    var count = requirements.Where(u => u.PKID == 7201755323293077855L).Count();
            //}



        }

        Console.WriteLine("Hello, World!");
    }

/// <summary>
/// CREATE TABLE Users (UserId INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(128), BRITHDATE TIMESTAMP);
/// </summary>
public class UserContext : DbContext
{
    public UserContext(DbContextOptions<UserContext> options) : base(options)
    {
    }

    public DbSet<User> Users { get; set; }

    public DbSet<Blog> Blogs { get; set; }

    public DbSet<Requirement> Requirements { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasSequence<int>("OrderNumbers", schema: "SYSDBA").StartsAt(1000).IncrementsBy(5);

        modelBuilder.Entity<User>(b =>
        {
            b.ToTable("Users", "SYSDBA");
            b.HasKey(u => u.UserId);
            b.Property(u => u.Age).HasColumnType("int");
            b.Property(u => u.Name).HasColumnType("varchar(128)");
            b.Property(u => u.BrithDate).HasColumnType("timestamp");
            b.Property(u => u.GenId).HasDefaultValueSql("NEXTVAL(OrderNumbers)");
        });

        modelBuilder.Entity<Blog>(b =>
        {
            b.ToTable("Blogs", "SYSDBA");
            b.HasKey(u => u.Id);
            b.Property(u => u.UserId).HasColumnType("int");
            b.Property(u => u.Url).HasColumnType("varchar(128)");
        });

        modelBuilder.Entity<Requirement>(b =>
        {
            b.ToTable("Requirements", "SYSDBA");
            b.HasKey(u => u.PKID);
        });
    }
}

// CREATE TABLE Users (UserId INT AUTO_INCREMENT PRIMARY KEY, Age INT, Name VARCHAR(128), BRITHDATE TIMESTAMP, GenId INT AS (Age + 5));
// CREATE TABLE Users (UserId INT AUTO_INCREMENT PRIMARY KEY, Age INT, Name VARCHAR(128), BRITHDATE TIMESTAMP, GenId INT DEFAULT (NEXTVAL('OrderNumbers')));
public class User
{
    public int UserId { get; set; }

    public int Age { get; set; }

    public string Name { get; set; }

    public DateTime BrithDate { get; set; }

    public int GenId { get; set; }
}

/// <summary>
/// CREATE TABLE BLOGS (ID INT AUTO_INCREMENT PRIMARY KEY, USERID INT, URL VARCHAR(128));
/// </summary>
public class Blog
{
    public int Id { get; set; }

    public int UserId { get; set; }

    public string Url { get; set; }
}

// CREATE TABLE Requirement
// (PKID INT8 PRIMARY KEY, AttributeList TEXT, Code TEXT, CreateTime TIMESTAMP, CreatorID INT8, Description TEXT, FileList TEXT, Name TEXT,
// ParentID INT8, PhysicalID TEXT, ProjectID INT8, RequirementType INT8, RequirementValidatingMethod INT8, Source TEXT, TreeID LONG);
public class Requirement
{
    public long PKID { get; set; }

    [Column(TypeName = "text")]
    public string AttributeList { get; set; }

    [Column(TypeName = "text")]
    public string Code { get; set; }

    [Column(TypeName = "timestamp")]
    public DateTime CreateTime { get; set; }

    public long CreatorID { get; set; }
    [Column(TypeName = "text")]
    public string Description { get; set; }
    [Column(TypeName = "text")]
    public string FileList { get; set; }
    [Column(TypeName = "text")]
    public string Name { get; set; }
    public long ParentID { get; set; }
    [Column(TypeName = "text")]
    public string PhysicalID { get; set; }
    public long ProjectID { get; set; }
    public long RequirementType { get; set; }
    public long RequirementValidatingMethod { get; set; }
    [Column(TypeName = "text")]
    public string Source { get; set; }
    public long TreeID { get; set; }

}

Product	Compatible and additional computed target framework versions.
.NET	net6.0 is compatible. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed.

Product

.NET