Name表字段设置为 varbinary(MAX)
存储:
SqlConnection connection = new SqlConnection("Initial Catalog=TestDB;User ID=sa;Password=sa;Data Source=.");
Dictionary<string, bool> dk = new Dictionary<string, bool>();
dk.Add("tsFileName",true);
dk.Add("tsclose", false);
// 转换操作员权限集合为数据库可存取的 Byte[] 数组
MemoryStream ms = new MemoryStream();
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(ms, dk);
byte[] rigthsByteArray = new byte[(int)(ms.Length)];
ms.Position = 0;
ms.Read(rigthsByteArray, 0, (int)(ms.Length));
ms.Close();
// 拼接 SQL 命令
string sqlTxt = "insert into Test (Name) values (@OperatorName)";
SqlCommand cmd = new SqlCommand(sqlTxt, connection);
SqlParameter prm3 = new SqlParameter("@OperatorName", SqlDbType.VarBinary, rigthsByteArray.Length,
ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, rigthsByteArray);
cmd.Parameters.AddRange(new SqlParameter[] { prm3});
try
{
connection.Open();
if (cmd.ExecuteNonQuery() >= 1)
MessageBox.Show("cheng");
else
MessageBox.Show("bai");
connection.Close();
}
catch
{
}
读取:
//SQL命令
string sqltxt = "Select Name From Test";
//创建操作员实体集合
Dictionary<string, bool> operatorCollection = new Dictionary<string, bool>();
// 执行 SQL 命令
using (SqlConnection conn = new SqlConnection("Initial Catalog=TestDB;User ID=sa;Password=sa;Data Source=."))
{
SqlCommand cmd = new SqlCommand(sqltxt, conn);
conn.Open();
using (SqlDataReader myReader = cmd.ExecuteReader(
CommandBehavior.CloseConnection))
{
while (myReader.Read())
{
// 读取权限集合
System.Data.SqlTypes.SqlBytes bytes = myReader.GetSqlBytes(0); // 只能指定列序号
// 将流反序列化为权限集合对象
BinaryFormatter bf = new BinaryFormatter();
if (!bytes.IsNull)
operatorCollection = (bf.Deserialize(bytes.Stream) as Dictionary<string, bool>);
}
}
}