优化的简单方法 插入、删除、更新 SQL Server 表包括具有图像数据类型的列，无需实体框架，分 3 步 (C#)

Question

>我有一个优化使用调用SQL查询命令从表中获取数据的想法，包括使用C#image数据类型的列，而无需实体框架，分为3个步骤：

步骤 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 个构造函数，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 步：使用ParseUser函数从数据库中获取Users：

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表中。我将在以后的帖子中发布该帖子。

喜欢你的代码并给我一些反馈。

非常感谢 ！

Answer 1

ntext，text和图像数据类型将在将来的SQL Server版本中删除。避免在新的开发工作中使用这些数据类型，并计划修改当前使用这些数据类型的应用程序。请改用 nvarchar(max(、varchar(max( 和 varbinary(max(。 只需为您解决。

Answer 2

假设我有很多表，如下所示，我想插入到一个特定的表中：

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;
}
}

做。