如何将文件从C＃保存到Sql [英] How save a file into Sql From C#

问题描述

大家好。

我的文件小于10 MB

我想把它保存到sql中。我把它转换为bytes的数组。然后将它保存到sql.type中，这个字段在sql中是varbinary（max）。

当我从sql文件大小检索文件时是13字节。

哪里是我的错误？

我用于将文件转换为字节数组的代码

Hi every one.
I have file with less than 10 mbyte
I want save it into sql. I convert it to array of bytes.then save it into sql.type of this field in sql is varbinary(max).
when I retrieve file from sql size of file is 13 byte.
where is my bug?
Code that I use for Convert File to Array Of bytes

public static byte[] ConvertFileToBytes(string location, ref string FileName)
    {

        FileStream fs = new FileStream(location, FileMode.Open, FileAccess.Read);

        BinaryReader reader = new BinaryReader(fs);

        byte[] data = reader.ReadBytes((int)fs.Length);
        FileName = fs.Name.Substring(fs.Name.LastIndexOf("\\") + 1);

        fs.Close();
        return data;
    }

我用于将数据保存到Sql的代码

Code that I use for Save data into Sql

internal static void SaveAutocadFileOnDataBase(string MapCode, byte[] dataElecMap, string dataElecMapName, byte[] dataCivilMap, string dataCivilMapName, byte[] dataArchiMap, string dataArchiMapName, byte[] dataMechaMap, string dataMechaMapName)
 {
     string sqlcommand = string.Concat("INSERT INTO AutoCadFiles VALUES ('", MapCode, "' , ", "cast ('", dataElecMap, "' as varbinary(max)),", " '", dataElecMapName, "' ",
                                " , cast ('", dataCivilMap, "' as varbinary(max)) ,", " '", dataCivilMapName, "' ", " , cast ('", dataArchiMap, "' as varbinary(max)) ,", " '", dataArchiMapName, "' ",
                                " , cast ('", dataMechaMap, "' as varbinary(max)) ,", " '", dataMechaMapName, "' ", ")");
     ExecuteNonQuery(sqlcommand);
 }

我用于从Sql检索数据的代码

Code that I use for Retrieve data from Sql

private static void GetBinaryFiles(string MapNumber, string MapType)
{
    byte[] fileread = new byte[10000000];
    byte[] file = null;
    string fileName = null;
    SqlConnection SqlConnction = null;
    DataTable dt = new DataTable();
    string SqlCommand = "Select " + MapType + "Name From AutoCadFiles  Where MapCode ='" + MapNumber + "'";
    // string SqlCommand = "Select Description From PictureTable  Where PkId ='3'";
    SqlCommand sqlcom = GetConnection(SqlCommand, ref  SqlConnction);
    sqlcom.Parameters.Add("@MapCode", SqlDbType.Int).Value = MapNumber;
    fileName = (string)sqlcom.ExecuteScalar();
    sqlcom.Dispose();
    // SqlConnction.Close();
    SqlCommand = "Select   CAST(ArchitecturalMap  As varbinary(max)) From AutoCadFiles  Where MapCode ='" + MapNumber + "'";
    sqlcom = GetConnection(SqlCommand, ref  SqlConnction);
    sqlcom.Parameters.Add("@MapCode", SqlDbType.Int).Value = MapNumber;
    fileread = (byte[])sqlcom.ExecuteScalar();
    SqlConnction.Close();
   }

谢谢。

thanks.

推荐答案

哦，亲爱的，亲爱的，亲爱的。



您认为执行插入代码后会发生什么？答案：连接的SQL语句将按原样传递给SQL。全部10MB。现在，这里有一些问题：

0）当你执行INSERT时总是列出你的字段名称 - 它使你的代码更具未来性证据

1）SQL有一个最大查询长度为32K（这可能在最新版本中已更改，但我希望不会这样）

2）只要您的文件包含一个恰好是引号字符的字节，您的查询就会卷曲并且死了。

3）你对SQL Inhjection攻击持开放态度。



使用参数化查询：

Oh dear, oh dear, oh dear.

What do you think is going to happen when you execute that insert code? Answer: the concatentated SQL statement is going to be passed as is to SQL. All 10MB of it. Now, there are a few problems here:
0) Always list your field names when you do an INSERT - it makes your code more future proof
1) SQL has a maximum query length of 32K (this may have changed in the latest version, but I hope not)
2) As soon as your file contains a byte which happens to be a quote character, your query will curl up and die.
3) You are leaving yourself wide open to SQL Inhjection attacks.

Use a parameterised query:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("INSERT INTO AutoCadFiles (myColumn1) VALUES (@DMM))", con))
        {
        com.Parameters.AddWithValue("@DMM", dataMechaMap);
        com.ExecuteNonQuery();
        }
    }

而不是

Instead of

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("INSERT INTO AutoCadFiles (myColumn1) VALUES (cast ('", dataMechaMap, "' as varbinary(max))), con))
        {
        com.ExecuteNonQuery();
        }
    }

（我只做了一个字段来说明）

(I have only done one field to illustrate it)

这篇关于如何将文件从C＃保存到Sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！