Bisakah kita menyimpan file excel di sql server?

Simpan file Excel (atau jenis lainnya) ke SQL Server, lalu baca lagi dan buat agar dapat diunduh melalui tautan di ASP. Halaman .NET Web API

Table of Contents Show

Jadikan File Lokal Semu-Seketika "Global" (Publik)
Buat Tabel SQL Server
Siapkan Metode REST di Proyek API Web Anda
Baca File Metadata dari Tabel
Baca Data Excel dari Tabel dan Jadikan Dapat Diunduh
Permohonan Sedih untuk Orang Programmer yang Menyenangkan
Bisakah kita menyimpan file Excel di database SQL Server?
Bagaimana cara menyimpan file Excel di tabel SQL?
Bisakah kita menyimpan dokumen di SQL Server?
Apakah Excel kompatibel dengan SQL?

Jadikan File Lokal Semu-Seketika "Global" (Publik)

Artikel ini akan menunjukkan cara menyimpan file Excel ke database SQL Server, kemudian membaca data Excel kembali dari database, dan mendownloadnya ke mesin pengguna melalui metode REST di ASP. Aplikasi .NET Web API. Apa yang tidak ditunjukkan kepada Anda bagaimana melakukannya adalah membuat file Excel itu sendiri; . Excel Interop bagus, tetapi Spreadsheet Light tampaknya jauh lebih mudah. Untuk menguji kode ini, Anda dapat menggunakan an. xlsx dibuat secara manual/dengan cara biasa

Perhatikan juga, bahwa ini tidak benar-benar terbatas pada file Excel, meskipun artikel ini membahasnya secara khusus. Anda seharusnya dapat dengan mudah bekerja dengan berbagai jenis file, selama Anda menentukan jenis konten yang benar (ditampilkan nanti)

Buat Tabel SQL Server

Untuk berakhir di cawan suci pengunduhan, kita harus memulai perjalanan epik ini dengan langkah pertama;

SQL

CREATE TABLE ReportsGenerated
(
RptGenID int IDENTITY(1,1) PRIMARY KEY,
FileBaseName varchar(100) NOT NULL,
ContentType varchar(50),
BinaryData varbinary(MAX)
);

Bidang "FileBaseName" adalah bagian yang akan Anda gunakan untuk membantu menyiapkan perutean di proyek API Web Anda;

Isi Tabel

Inilah cara Anda memasukkan catatan yang mewakili file Excel ke dalam tabel yang baru saja dibuat

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename);

Nilai yang disimpan dalam bidang

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename);

5 tabel FileBaseName (

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename);

7 di atas) dapat berupa apa pun yang Anda perlukan untuk kebutuhan perutean REST Anda; . Berikut adalah kode penyisipan catatan aktual yang disebut di atas

internal static void SaveReportDataToDB(string filename, string RESTFilename)
{
    if (RecordAlreadyExists(RESTFilename)) return;
    string EXCEL_FILE = "application/vnd.ms-excel"; // if not an Excel file, 
    	//use something else, such as "application/vnd.ms-word" or 
    	//"image/jpeg" or "application/pdf"
    string EXCEL_EXTENSION = ".xlsx";

    // Read the file and convert it to Byte Array
    FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read); 
    BinaryReader br = new BinaryReader(fs);
    Byte[] bytes = br.ReadBytes((Int32)fs.Length);
    br.Close();
    fs.Close();

    // and store it in the DB, along with other necessary identifying info
    using (var sqlConn = new SqlConnection(ReportRunnerConstsAndUtils.CPSConnStr))
    {
        var insertStr = "INSERT INTO ReportsGenerated (FileBaseName, ContentType, BinaryData) " +
                        "VALUES (@FileBaseName, @ContentType, @BinaryData)";

        using (var insertRptsGenerated = new SqlCommand(insertStr))
        {
            insertRptsGenerated.Connection = sqlConn;
            insertRptsGenerated.Parameters.Add
            ("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            insertRptsGenerated.Parameters.Add
            ("@ContentType", SqlDbType.VarChar, 50).Value = EXCEL_FILE;
            insertRptsGenerated.Parameters.Add
            ("@BinaryData", SqlDbType.Binary).Value = bytes;
            sqlConn.Open();
            insertRptsGenerated.ExecuteNonQuery();
        }
    }
}

private static bool RecordAlreadyExists(string RESTFilename)
{
    int countAmount = 0;
    string qry = "SELECT COUNT(FileBaseName) FROM ReportsGenerated WHERE FileBaseName = @FileBaseName";
    using (SqlConnection sqlConn = new SqlConnection(CPSConnStr))
    {
        using (SqlCommand cmd = new SqlCommand(qry, sqlConn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            sqlConn.Open();
            countAmount = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
    return countAmount > 0;
}

Catatan. Di atas adalah kode Winforms. Kode lainnya adalah ASP. Hal-hal API Web .NET

Jadi sekarang, ada catatan di tabel

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename);

5. Sekarang kita dapat beralih ke bagian membaca dan merender kode

Siapkan Metode REST di Proyek API Web Anda

Tujuan utamanya adalah untuk memberi pengguna tautan di halaman web, dari mana mereka dapat mengunduh file Excel yang terdapat dalam bidang catatan "

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename);

4". Begini caranya

Tambahkan metode dalam

internal static void SaveReportDataToDB(string filename, string RESTFilename)
{
    if (RecordAlreadyExists(RESTFilename)) return;
    string EXCEL_FILE = "application/vnd.ms-excel"; // if not an Excel file, 
    	//use something else, such as "application/vnd.ms-word" or 
    	//"image/jpeg" or "application/pdf"
    string EXCEL_EXTENSION = ".xlsx";

    // Read the file and convert it to Byte Array
    FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read); 
    BinaryReader br = new BinaryReader(fs);
    Byte[] bytes = br.ReadBytes((Int32)fs.Length);
    br.Close();
    fs.Close();

    // and store it in the DB, along with other necessary identifying info
    using (var sqlConn = new SqlConnection(ReportRunnerConstsAndUtils.CPSConnStr))
    {
        var insertStr = "INSERT INTO ReportsGenerated (FileBaseName, ContentType, BinaryData) " +
                        "VALUES (@FileBaseName, @ContentType, @BinaryData)";

        using (var insertRptsGenerated = new SqlCommand(insertStr))
        {
            insertRptsGenerated.Connection = sqlConn;
            insertRptsGenerated.Parameters.Add
            ("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            insertRptsGenerated.Parameters.Add
            ("@ContentType", SqlDbType.VarChar, 50).Value = EXCEL_FILE;
            insertRptsGenerated.Parameters.Add
            ("@BinaryData", SqlDbType.Binary).Value = bytes;
            sqlConn.Open();
            insertRptsGenerated.ExecuteNonQuery();
        }
    }
}

private static bool RecordAlreadyExists(string RESTFilename)
{
    int countAmount = 0;
    string qry = "SELECT COUNT(FileBaseName) FROM ReportsGenerated WHERE FileBaseName = @FileBaseName";
    using (SqlConnection sqlConn = new SqlConnection(CPSConnStr))
    {
        using (SqlCommand cmd = new SqlCommand(qry, sqlConn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            sqlConn.Open();
            countAmount = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
    return countAmount > 0;
}

_0 (menyiapkan

internal static void SaveReportDataToDB(string filename, string RESTFilename)
{
    if (RecordAlreadyExists(RESTFilename)) return;
    string EXCEL_FILE = "application/vnd.ms-excel"; // if not an Excel file, 
    	//use something else, such as "application/vnd.ms-word" or 
    	//"image/jpeg" or "application/pdf"
    string EXCEL_EXTENSION = ".xlsx";

    // Read the file and convert it to Byte Array
    FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read); 
    BinaryReader br = new BinaryReader(fs);
    Byte[] bytes = br.ReadBytes((Int32)fs.Length);
    br.Close();
    fs.Close();

    // and store it in the DB, along with other necessary identifying info
    using (var sqlConn = new SqlConnection(ReportRunnerConstsAndUtils.CPSConnStr))
    {
        var insertStr = "INSERT INTO ReportsGenerated (FileBaseName, ContentType, BinaryData) " +
                        "VALUES (@FileBaseName, @ContentType, @BinaryData)";

        using (var insertRptsGenerated = new SqlCommand(insertStr))
        {
            insertRptsGenerated.Connection = sqlConn;
            insertRptsGenerated.Parameters.Add
            ("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            insertRptsGenerated.Parameters.Add
            ("@ContentType", SqlDbType.VarChar, 50).Value = EXCEL_FILE;
            insertRptsGenerated.Parameters.Add
            ("@BinaryData", SqlDbType.Binary).Value = bytes;
            sqlConn.Open();
            insertRptsGenerated.ExecuteNonQuery();
        }
    }
}

private static bool RecordAlreadyExists(string RESTFilename)
{
    int countAmount = 0;
    string qry = "SELECT COUNT(FileBaseName) FROM ReportsGenerated WHERE FileBaseName = @FileBaseName";
    using (SqlConnection sqlConn = new SqlConnection(CPSConnStr))
    {
        using (SqlCommand cmd = new SqlCommand(qry, sqlConn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            sqlConn.Open();
            countAmount = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
    return countAmount > 0;
}

0 berada di luar cakupan artikel ini/latihan yang diserahkan kepada pembaca) untuk menyiapkan halaman dengan tautan ke file yang akan diunduh (satu tautan untuk setiap rekaman terkait di tabel

string filename = "C:\\Misc\\Bla.xlsx"; 
. . . // The file in filename must exist before the following is called
string RESTFilename = string.Format("deliveryperformance/{0}/{1}/{2}", _unit, fromDate, toDate);
SaveReportDataToDB(filename, RESTFilename);

5). Di sini, ditampilkan dalam beberapa konteks, termasuk metode pembantu yang dipanggil oleh metode REST

namespace PlatypusWebReports.Controllers
{
    [RoutePrefix("api/deliveryperformance")]
    public class DeliveryPerformanceController : ApiController
    {
        // In this case (an URL like "/api/deliveryperformance/Gramps"), display
        // links for all available reports for this Unit
        [Route("{unit}")]
        public HttpResponseMessage Get(string unit)
        {
            String HtmlToDisplay = GetHTMLAsStrForUnit(unit);

            return new HttpResponseMessage()
            {
                Content = new StringContent(
                    HtmlToDisplay,
                    Encoding.UTF8,
                    "text/html"
                )
            };
        }

        private string GetHTMLAsStrForUnit(string unit)
        {
            // Get data from reportsgenerated table to generate a set of links; when any of *those*
            // links are selected, the REST method called will read that table's BinaryData field
            // and download it
            string query = string.Format(
                "SELECT FileBaseName FROM reportsgenerated WHERE 
                FileBaseName LIKE 'deliveryperformance/{0}/%'", unit);
            try
            {
                DataTable dtReportsGeneratedResults =
                    SQLDBHelper.ExecuteSQLReturnDataTable(
                        query,
                        CommandType.Text,
                        new SqlParameter()
                        {
                            ParameterName = "@Unit",
                            SqlDbType = SqlDbType.VarChar,
                            Value = unit
                        });
                if (dtReportsGeneratedResults.Rows.Count <= 0) return string.Empty;
                var _FileBaseNameList = (from DataRow reaux in dtReportsGeneratedResults.Rows 
                select reaux ["FileBaseName"].ToString()).ToList();
                var htmlStr = ConvertDelPerfFileBaseNameListToHtml(_FileBaseNameList, unit);
                return htmlStr;
            }
            catch (Exception ex)
            {
                // Catch code left as an exercise to the reader
            }
            return string.Empty;
        }

Baca File Metadata dari Tabel

Berikut adalah metode (disebut di atas) untuk membaca dari tabel

public static DataTable ExecuteSQLReturnDataTable
(string sql, CommandType cmdType, params SqlParameter[] 
    parameters)
{
    using (DataSet ds = new DataSet())
    using (SqlConnection connStr = new SqlConnection(ConnStr))
    using (SqlCommand cmd = new SqlCommand(sql, connStr))
    {
        cmd.CommandType = cmdType;
        cmd.CommandTimeout = EXTENDED_TIMEOUT;
        foreach (var item in parameters)
        {
            cmd.Parameters.Add(item);
        }

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
        }
        catch (SqlException sqlex)
        {
      	    // Catch code left as an exercise to the reader
        }
        return ds.Tables[0];
    }
}

dan metode yang dipanggil untuk menghasilkan halaman html dengan tautan

internal static string ConvertDelPerfFileBaseNameListToHtml
(List<string> _FileBaseNameList, string unit)
{
    string startDateYYYYMMDD;
    string endDateYYYYMMDD;

    StringBuilder builder = new StringBuilder();

    builder.Append(String.Format("<h1>{0}</h1>", unit.ToUpper()));

    // Create links for each report
    foreach (String fileBaseName in _FileBaseNameList)
    {
        startDateYYYYMMDD = GetElement(3, fileBaseName);
        endDateYYYYMMDD = GetElement(4, fileBaseName);
        builder.Append("<p></p>");
        builder.Append(string.Format("<a href=\"api/deliveryperformance/excel/{0}/{1}/{2}\">{3}</a>", unit, startDateYYYYMMDD, endDateYYYYMMDD, fileBaseName));
        builder.Append("<p></p>");
    }

    builder.Append("</table>");
    builder.Append("</body>");
    builder.Append("</html>");

    return builder.ToString();
}

Perhatikan bahwa setiap tautan href kain jangkar adalah panggilan metode REST lainnya. Pertama, saya akan menambahkan metode utilitas "

internal static void SaveReportDataToDB(string filename, string RESTFilename)
{
    if (RecordAlreadyExists(RESTFilename)) return;
    string EXCEL_FILE = "application/vnd.ms-excel"; // if not an Excel file, 
    	//use something else, such as "application/vnd.ms-word" or 
    	//"image/jpeg" or "application/pdf"
    string EXCEL_EXTENSION = ".xlsx";

    // Read the file and convert it to Byte Array
    FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read); 
    BinaryReader br = new BinaryReader(fs);
    Byte[] bytes = br.ReadBytes((Int32)fs.Length);
    br.Close();
    fs.Close();

    // and store it in the DB, along with other necessary identifying info
    using (var sqlConn = new SqlConnection(ReportRunnerConstsAndUtils.CPSConnStr))
    {
        var insertStr = "INSERT INTO ReportsGenerated (FileBaseName, ContentType, BinaryData) " +
                        "VALUES (@FileBaseName, @ContentType, @BinaryData)";

        using (var insertRptsGenerated = new SqlCommand(insertStr))
        {
            insertRptsGenerated.Connection = sqlConn;
            insertRptsGenerated.Parameters.Add
            ("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            insertRptsGenerated.Parameters.Add
            ("@ContentType", SqlDbType.VarChar, 50).Value = EXCEL_FILE;
            insertRptsGenerated.Parameters.Add
            ("@BinaryData", SqlDbType.Binary).Value = bytes;
            sqlConn.Open();
            insertRptsGenerated.ExecuteNonQuery();
        }
    }
}

private static bool RecordAlreadyExists(string RESTFilename)
{
    int countAmount = 0;
    string qry = "SELECT COUNT(FileBaseName) FROM ReportsGenerated WHERE FileBaseName = @FileBaseName";
    using (SqlConnection sqlConn = new SqlConnection(CPSConnStr))
    {
        using (SqlCommand cmd = new SqlCommand(qry, sqlConn))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@FileBaseName", SqlDbType.VarChar, 100).Value = RESTFilename;
            sqlConn.Open();
            countAmount = Convert.ToInt32(cmd.ExecuteScalar());
        }
    }
    return countAmount > 0;
}

_3" di bawah, lalu metode REST yang dipanggil oleh tautan href yang dihasilkan secara dinamis

Metode pembantu adalah sebagai berikut

// When called like this: GetElement(3, "deliveryperformance/ABUELOS/20160324/20160331"),
// "20160324" is returned.
private static string GetElement(int Nth, string fileBaseName)
{
    string[] fbnElements = fileBaseName.Split('/');
    return fbnElements[Nth - 1];
}

Baca Data Excel dari Tabel dan Jadikan Dapat Diunduh

Bulu di bagian atas artikel ini adalah metode yang membaca data Excel dari tabel dan kemudian mengunduhnya ke pengguna (yang dipanggil saat pengguna mengklik tautan)

// A typical URL might be, "api/deliveryperformance/Excel/ABUELOS/20160324/20160331"
[Route("api/deliveryperformance/{filetype}/{unit}/{begindate}/{enddate}")]
public HttpResponseMessage Get(string filetype, string unit, string begindate, string enddate)
{
    // adapted from 
    // http://stackoverflow.com/questions/11176066/how-do-i-insert-retrieve-excel-files-to-varbinarymax-column-in-sql-server-2008
    byte[] excelContents;

    string selectStmt = "SELECT BinaryData FROM ReportsGenerated WHERE FileBaseName = @fileBaseName";
    string fbn = string.Format("deliveryperformance/{0}/{1}/{2}", unit, begindate, enddate);
    using (SqlConnection connection = new SqlConnection(PlatypusWebReportsConstsAndUtils.CPSConnStr))
    using (SqlCommand cmdSelect = new SqlCommand(selectStmt, connection))
    {
        cmdSelect.Parameters.Add("@fileBaseName", SqlDbType.VarChar).Value = fbn;
        connection.Open();
        excelContents = (byte[])cmdSelect.ExecuteScalar();
        connection.Close();
    }

    // Got this code from 
    // http://stackoverflow.com/questions/36345052/how-can-i-download-a-file-to-a-remote-machine-the-same-or-similarly-to-how-im-d
    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK)
    {
        Content = new ByteArrayContent(excelContents)
    };
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = string.Format("{0}.xlsx", fbn)
    };
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");

    return result;
}

Permohonan Sedih untuk Orang Programmer yang Menyenangkan

Jika artikel ini benar-benar membantu Anda dalam pengejaran profesional atau pribadi Anda, yang saya minta adalah Anda dengan tajam merenungkan kemungkinan Platypi yang bebas racun yang damai dipasangkan dengan Landak yang tidak menusuk dengan bangga berparade melewati istana Persia ungu sambil mengupas buah delima dan berpose dalam piyama kotak-kotak (cantik

Lisensi

Artikel ini, bersama dengan kode sumber dan file terkait, dilisensikan di bawah The Code Project Open License (CPOL)

Bisakah kita menyimpan file Excel di database SQL Server?

Impor data langsung dari file Excel dengan menggunakan Wisaya Impor dan Ekspor SQL Server . Anda juga memiliki opsi untuk menyimpan pengaturan sebagai paket SQL Server Integration Services (SSIS) yang dapat Anda sesuaikan dan gunakan kembali nanti. Di SQL Server Management Studio, sambungkan ke instance SQL Server Database Engine.

Bagaimana cara menyimpan file Excel di tabel SQL?

Pertama, salin data dari Excel, lalu tempelkan ke tabel SQL Server menggunakan opsi menu Database > Table > Edit 200 baris teratas. Always start by copying and pasting a single row of data to validate the data types.

Bisakah kita menyimpan dokumen di SQL Server?

FILESTREAM memungkinkan aplikasi berbasis SQL Server untuk menyimpan data tidak terstruktur, seperti dokumen dan gambar, pada sistem file .

Apakah Excel kompatibel dengan SQL?

Meskipun tindakan Excel dapat menangani sebagian besar skenario otomatisasi Excel, kueri SQL dapat mengambil dan memanipulasi sejumlah besar data Excel dengan lebih efisien .