优化INSERT，DELETE，UPDATE的简单方法SQL Server表包括3个步骤（C＃），其

我有一个优化使用调用SQL查询命令来从表中获取数据的想法，该表image使用3个步骤使用不带Entity Framework的C＃从具有数据类型的表中获取数据：

步骤0：（默认）创建一个新表。这是我的桌子TBUsers：

CREATE TABLE [dbo].[TBUsers]
(
    [STT] [BIGINT] IDENTITY(1,1) NOT NULL,
    [HoTen] [NVARCHAR](MAX) NOT NULL,
    [MaSo] [NVARCHAR](50) NOT NULL,
    [MatKhau] [NVARCHAR](MAX) NOT NULL,
    [KhoaLop] [NVARCHAR](MAX) NOT NULL,
    [MaTheGui] [NVARCHAR](50) NOT NULL,
    [PhanQuyen] [INT] NOT NULL,
    [ChoPhepHoatDong] [BIT] NOT NULL,
    [NguoiThem] [NVARCHAR](MAX) NOT NULL,
    [NgayThem] [DATETIME] NOT NULL,
    [SoDuKhaDung] [BIGINT] NOT NULL,
    [DangGui] [BIT] NOT NULL,
    [TruyCapLanCuoi] [DATETIME] NULL,
    [ThoiGianGuiCuoi] [DATETIME] NULL,
    [HinhAnh] [IMAGE] NULL,
    [DonGia] [BIGINT] NULL,

    CONSTRAINT [PK_TBUsers] 
        PRIMARY KEY CLUSTERED ([MaTheGui] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

步骤1：创建一个与上表相对应的新类，并创建2个构造函数```js Users：

public class Users
{
    public Users()
    { }

    public Users(object sTT, object hoTen, object maSo, object matKhau, object khoaLop, object maTheGui, object phanQuyen, object choPhepHoatDong,
        object nguoiThem, object ngayThem, object soDuKhaDung, object dangGui, object truyCapLanCuoi, object thoiGianGuiCuoi, object hinhAnh,object donGia)
    {
        STT = sTT.ToString();
        HoTen = hoTen.ToString();
        MaSo = maSo.ToString();
        MatKhau = matKhau.ToString();
        KhoaLop = khoaLop.ToString();
        MaTheGui = maTheGui.ToString();
        PhanQuyen = phanQuyen.ToString();
        ChoPhepHoatDong = choPhepHoatDong.ToString();
        NguoiThem = nguoiThem.ToString();
        NgayThem = ngayThem.ToString();
        SoDuKhaDung = soDuKhaDung.ToString();
        DangGui = dangGui.ToString();
        TruyCapLanCuoi = truyCapLanCuoi.ToString();
        ThoiGianGuiCuoi = thoiGianGuiCuoi.ToString();

        HinhAnh = hinhAnh==System.DBNull.Value?null: (byte[])hinhAnh;
        DonGia = donGia.ToString();
        Color = (bool)choPhepHoatDong;
    }

    public string STT { get; set; }
    public string HoTen { get; set; }
    public string MaSo { get; set; }
    public string MatKhau { get; set; }
    public string KhoaLop { get; set; }
    public string MaTheGui { get; set; }
    public string PhanQuyen { get; set; }
    public string ChoPhepHoatDong { get; set; }
    public string NguoiThem { get; set; }
    public string NgayThem { get; set; }
    public string SoDuKhaDung { get; set; }
    public string DangGui { get; set; }
    public string TruyCapLanCuoi { get; set; }
    public string ThoiGianGuiCuoi { get; set; }
    public byte[] HinhAnh { get; set; }
    public string DonGia { get; set; }
    public bool Color { get; set; }
}

}

步骤2：Users使用以下ParseUser功能从数据库获取：
```js
public static Users ParseUser(DataRow row)
    {
        var stt = row["STT"];
        var hoTen = row["HoTen"];
        var maSo = row["MaSo"];
        var matKhau = row["MatKhau"];
        var khoaLop = row["KhoaLop"];
        var maTheGui = row["MaTheGui"];
        var phanQuyen = row["PhanQuyen"];
        var choPhepHoatDong = row["ChoPhepHoatDong"];
        var nguoiThem = row["ChoPhepHoatDong"];
        var ngayThem = row["ChoPhepHoatDong"];
        var soDuKhaDung = row["SoDuKhaDung"];
        var dangGui = row["DangGui"];
        var truyCapLanCuoi = row["TruyCapLanCuoi"];
        var guiLanCuoi = row["ThoiGianGuiCuoi"];
        var hinhAnh = row["HinhAnh"] == System.DBNull.Value ? null : row["HinhAnh"];
        var donGia = row["DonGia"];

        return new Users(stt, hoTen, maSo, matKhau, khoaLop, maTheGui, phanQuyen, choPhepHoatDong, nguoiThem, ngayThem, soDuKhaDung, dangGui,
            truyCapLanCuoi, guiLanCuoi, hinhAnh, donGia);
    }

第3步：轻松使用具有ParseUser功能的Users类的实例：

Users user = Table.ParseUser(sqlUtility.GetDataTable($"SELECT * FROM [dbo].[TBUsers] WHERE MaSo = 'xxx' AND ChoPhepHoatDong=1;").Rows[0]);

正在更新... 假设我有许多下表，并且我想插入到特定表中：

public enum TableName
    {
        Users = 0,
        Registration,
        TheTamThoi
    }
创建一个函数来做到这一点InsertWithImage：

        public string InsertWithImage(TableName tableName, string[] fields, string[] values)
        {
            try
            {
                Connect();
                SqlCommand command;
                SqlDataAdapter adapter;
                string commandText = string.Empty;
                string table = string.Empty;
                switch (tableName)
                {
                    case TableName.Users:
                        table = "TBUsers";
                        break;
                    case TableName.Registration:
                        table = "TBRegistration";
                        break;
                    case TableName.TheTamThoi:
                        table = "TBTheTamThoi";
                        break;
                    default:
                        break;
                }
                StringBuilder builder = new StringBuilder($"INSERT INTO [dbo].[{table}](");

                for (int i = 0; i < fields.Length; i++)
                {
                    builder.Append(i == fields.Length - 1 ? $"[{fields[i]}]) VALUES(" : $"[{fields[i]}],");
                }
                for (int i = 0; i < values.Length; i++)
                {
                    builder.Append(i != values.Length - 1 ? $"@{fields[i]}, " : $"@{fields[i]})");
                }
                commandText = builder.ToString();
                command = new SqlCommand(commandText, sqlConnection);

                for (int i = 0; i < values.Length; i++)
                {
                    if (fields[i] == "HinhAnh")
                        command.Parameters.AddWithValue(fields[i], string.IsNullOrEmpty(values[i]) ? (object)DBNull.Value : GetData(values[i])).SqlDbType = SqlDbType.Image;
                    else
                        command.Parameters.AddWithValue(fields[i], values[i]);
                }

                adapter = new SqlDataAdapter(command);
                adapter.InsertCommand = new SqlCommand(commandText, sqlConnection);
                command.ExecuteNonQuery();
                command.Dispose();
                sqlConnection.Close();
                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

提示

并假设我想从数据库中的表中获取数据以插入到另一个表中：

创建一个函数来做到这一点InsertWithImageFromDB：

public string InsertWithImageFromDB(TableName tableName, string[] fields, object[] values)
        {
            try
            {
                Connect();
                SqlCommand command;
                SqlDataAdapter adapter;
                string commandText = string.Empty;
                string table = string.Empty;
                switch (tableName)
                {
                    case TableName.Users:
                        table = "TBUsers";
                        break;
                    case TableName.Registration:
                        table = "TBRegistration";
                        break;
                    case TableName.TheTamThoi:
                        table = "TBTheTamThoi";
                        break;
                    default:
                        break;
                }
                StringBuilder builder = new StringBuilder($"INSERT INTO [DBBaiDoXe].[dbo].[{table}](");

                for (int i = 0; i < fields.Length; i++)
                {
                    builder.Append(i == fields.Length - 1 ? $"[{fields[i]}]) VALUES(" : $"[{fields[i]}],");
                }
                for (int i = 0; i < values.Length; i++)
                {
                    builder.Append(i != values.Length - 1 ? $"@{fields[i]}, " : $"@{fields[i]})");
                }
                commandText = builder.ToString();
                command = new SqlCommand(commandText, sqlConnection);

                for (int i = 0; i < values.Length; i++)
                {
                    command.Parameters.AddWithValue(fields[i], values[i]);
                }

                adapter = new SqlDataAdapter(command);
                adapter.InsertCommand = new SqlCommand(commandText, sqlConnection);
                command.ExecuteNonQuery();
                command.Dispose();
                sqlConnection.Close();
                return string.Empty;
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

做完了

这是一个好的解决方案吗？

我将继续编写一个简单的解决方案，以将TBUsers图像数据类型的字段插入表中。我将在以后的文章中发布。