C # store and read Word files in SQL Server

To read and write a word file in SQL Server, you must add an image column to the table to be accessed. The structure of the example table is as follows:

CREATE TABLE CONTRACTS (    ID VARCHAR (50),    CONTRACT_FILE IMAGE);

To store the Word file in the contract_file field of the database, you must convert the file to a byte array. The Code is as follows:

Convert a file to a byte array /// <summary> /// convert the file to bytes /// </Summary> /// <Param name = "FILENAME"> </ param> // <returns> </returns> Public static byte [] file2bytes (string filename) {filestream FS = new filestream (filename, filemode. openorcreate, fileaccess. read); byte [] filedatas = new byte [FS. length]; FS. read (filedatas, 0, system. convert. toint32 (FS. length); FS. close (); Return filedatas ;}

Then, the converted byte [] is stored in the corresponding field of the database:

Store the file to the database // <summary> // update the contract file // </Summary> /// <Param name = "ID"> </param>/ // <Param name = "filebytes"> </param> // <returns> </returns> Public bool updatecontractfile (string ID, byte [] filebytes) {string SQL = "Update contracts set contract_file = @ contract_file where id = @ ID"; using (sqlconnection conn = new sqlconnection (this. m_dataaccess.connectstring) {Conn. open (); Using (sqlcommand cmd = New sqlcommand () {cmd. connection = conn; cmd. commandtext = SQL; cmd. parameters. clear (); cmd. parameters. add (New sqlparameter ("@ contract_file", sqldbtype. image); cmd. parameters ["@ contract_file"]. value = filebytes; cmd. parameters. add (New sqlparameter ("@ ID", sqldbtype. varchar); cmd. parameters ["@ ID"]. value = ID; return cmd. executenonquery ()> 0? True: false ;}}}

To read the word files stored in the database, you must first convert the field of the image type to bytes []. The specific code is as follows:

Get the byte array by ID /// <summary> /// obtain the contract file /// </Summary> /// <Param name = "ID"> </param> /// <returns> </returns> Public byte [] getcontractfile (string ID) {string SQL = "select contract_file from contracts where id = '{0}'"; SQL = string. format (SQL, ID); object contractfile; contractfile = This. m_dataaccess.executescalar (SQL); If (contractfile = NULL) {return New byte [0];} else {return (byte []) contractfile ;}}

After obtaining the object's byte [], store the object as a Word file through the file stream operation. The Code is as follows:

Store byte [] arrays as the Word file byte [] filebytes = This. m_contractsbusiness.getcontractfile (ID); If (filebytes. length = 0) {xmessagebox. showerror ("contract file not found! "); Return;} savefiledialog SFD = new savefiledialog (); SFD. filter = "Word file (*. doc) | *. doc "; if (SFD. showdialog () = system. windows. forms. dialogresult. OK) {try {string savefilename = SFD. filename; int arraysize = new int (); // note that arraysize = filebytes. getupperbound (0); filestream FS = new filestream (savefilename, filemode. openorcreate, fileaccess. write); FS. write (filebytes, 0, arraysize); FS. clo Se (); If (xmessagebox. showquestion ("the file is successfully downloaded. Do you want to open the file now? ") = System. Windows. Forms. dialogresult. Yes) {process. Start (savefilename) ;}} catch (exception ex) {xmessagebox. showerror (" An error occurred while downloading the file! ");}