检索二进制数据从SQL数据库与asp.net
本文关键字:asp net 数据库 SQL 二进制 数据 检索 | 更新日期: 2023-09-27 17:50:26
我想从SQL数据库与asp.net检索二进制数据,但在输出中显示的数据与我插入的数据不匹配。这是我的代码:
string EQuery = "SELECT * FROM Ph_Tbl_Contacts WHERE (Contact_ID =" + Contact_ID + ")";
DataSet DSs = new DataSet();
DataTable dt = new DataTable();
DataRow dr = dt.NewRow();
DSs = DB.ExecuteQueryData(EQuery);
dt = DSs.Tables[0];
// dr = dt.NewRow();
dr = dt.Rows[0];
byte[] pic;
byte[] raw = (byte[])dr["Contact_CardImage"];
// Session[OpenDialog.STORED_IMAGE] = raw ;
,这是插入部分:
byte[] IMAGEbYTE ;
IMAGEbYTE = (byte[])(Session["SessionImage"]);
string Query = "INSERT INTO Ph_Tbl_Contacts (Contact_Name, Contact_LName, " +
"Contact_Company, Contact_Email, Contact_Tel, " +
"Contact_Mobile,Contact_CardImage,Is_Public,User_ID,Save_Date)" +
"VALUES (N'" + Txt_Name.Text + "', N'" + Txt_LastName.Text + "', N'" +
Txt_CompanyName.Text + "', N'" + Txt_Mail.Text + "', N'" +
Txt_Telephone.Text + "', N'" + Txt_Mobile.Text + "','" +
IMAGEbYTE + "','" + CheckValue + "'," +
Session["User_ID"] + ", N'" + DateStr + "')";
DB.ExecuteQueryNoData(Query) ;
好了,让我们从清理代码开始。第一件事是修复INSERT代码,因为现在它很容易受到SQL注入的攻击。您需要使用参数化查询:
using (var conn = new SqlConnection("Your ConnectionString comes here"))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText =
@"INSERT INTO Ph_Tbl_Contacts
(Contact_Name,
Contact_LName,
Contact_Company,
Contact_Email,
Contact_Tel,
Contact_Mobile,
Contact_CardImage,
Is_Public,
User_ID,
Save_Date)
VALUES
(@Contact_Name,
@Contact_LName,
@Contact_Company,
@Contact_Email,
@Contact_Tel,
@Contact_Mobile,
@Contact_CardImage,
@Is_Public,
@User_ID,
@Save_Date)
";
cmd.Parameters.AddWithValue("@Contact_Name", Txt_Name.Text);
cmd.Parameters.AddWithValue("@Contact_LName", Txt_LastName.Text);
cmd.Parameters.AddWithValue("@Contact_Company", Txt_CompanyName.Text);
cmd.Parameters.AddWithValue("@Contact_Email", Txt_Mail.Text);
cmd.Parameters.AddWithValue("@Contact_Tel", Txt_Telephone.Text);
cmd.Parameters.AddWithValue("@Contact_Mobile", Txt_Mobile.Text);
cmd.Parameters.AddWithValue("@Contact_CardImage", IMAGEbYTE);
cmd.Parameters.AddWithValue("@Is_Public", CheckValue);
cmd.Parameters.AddWithValue("@User_ID", Session["User_ID"]);
cmd.Parameters.AddWithValue("@Save_Date", DateStr);
cmd.ExecuteNonQuery();
}
这里值得一提的是,如果您的数据库中的Save_Date
列是datetime
,您应该传递DateTime
的参数实例,而不是试图将其转换为字符串=> DateStr
必须是DateTime。
好了,现在您已经正确地将记录插入到数据库中,您可以读取它:
using (var conn = new SqlConnection("Your ConnectionString comes here"))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText =
@"SELECT
Contact_CardImage
FROM
Ph_Tbl_Contacts
WHERE
Contact_ID = @Contact_ID
";
cmd.Parameters.AddWithValue("@Contact_ID", Txt_Name.Text);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
byte[] raw = (byte[])reader.Items["Contact_CardImage"];
// TODO: do something with the raw data
}
}
}