开发者社区> 问答> 正文

优化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图像数据类型的字段插入表中。我将在以后的文章中发布。

展开
收起
心有灵_夕 2019-12-22 14:24:43 926 0
0 条回答
写回答
取消 提交回答
问答排行榜
最热
最新

相关电子书

更多
SQL Server在电子商务中的应用与实践 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载