Upload Data to Sql Server From Word Format

I take already explained how to save and call back files in SQL Server database using ASP.Cyberspace in my article Save and Call back Files from SQL Server Database using ASP.Net

This article is an extension of the same since was frequently asked on how to save file directly to database using the ASP.Internet FileUpload Control

Y'all can upload whatsoever files like images, Word document. Excel document, Portable Certificate Format (PDF), Text Files sand save them to Database

Database Design

Here I have created a Database called dbFiles and information technology has a table called tblFiles.

It has 4 Fields. The complete description is available in the Figure beneath

As you can see above for the id field I have set Identity Specification true, so that it automatically increments itself.

Field	Relevance
id	Identification Number
Proper noun	File Proper noun
Content Type	Content Blazon for the file
Data	File stored as Binary Data

Connection Cord

Beneath is the connection string to the database. You lot can modify information technology to adapt yours

< connectionStrings >

< add together proper noun = "conString" connectionString ="Information Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>

</ connectionStrings >

To start with I have added a FileUpload control, a push and a Label to prove messages

< asp : FileUpload ID ="FileUpload1" runat ="server" />

< asp : Button ID ="btnUpload" runat ="server" Text ="Upload"

OnClick ="btnUpload_Click" />

< br />

< asp : Label ID ="lblMessage" runat ="server" Text =""

Font-Names = "Arial"></ asp : Label >

And here is the snippet which is called on the Upload Button Click outcome

C#

protected void btnUpload_Click(object sender, EventArgs east)

{

// Read the file and catechumen it to Byte Array

string filePath = FileUpload1.PostedFile.FileName;

string filename = Path.GetFileName(filePath);

string ext = Path.GetExtension(filename);

cord contenttype = String.Empty;

//Set the contenttype based on File Extension

switch(ext)

{

instance ".doctor":

contenttype = "application/vnd.ms-give-and-take";

suspension;

case ".docx":

contenttype = "application/vnd.ms-word";

break;

case ".xls":

contenttype = "application/vnd.ms-excel";

suspension;

case ".xlsx":

contenttype = "awarding/vnd.ms-excel";

break;

case ".jpg":

contenttype = "image/jpg";

break;

example ".png":

contenttype = "image/png";

break;

instance ".gif":

contenttype = "prototype/gif";

pause;

example ".pdf":

contenttype = "application/pdf";

interruption;

}

if (contenttype != String.Empty)

{

Stream fs = FileUpload1.PostedFile.InputStream;

BinaryReader br = new BinaryReader(fs);

Byte[] bytes = br.ReadBytes((Int32)fs.Length);

//insert the file into database

string strQuery = "insert into tblFiles(Proper name, ContentType, Data)" +

" values (@Proper noun, @ContentType, @Information)";

SqlCommand cmd = new SqlCommand(strQuery);

cmd.Parameters.Add("@Proper noun", SqlDbType.VarChar).Value = filename;

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value

= contenttype;

cmd.Parameters.Add together("@Data", SqlDbType.Binary).Value = bytes;

InsertUpdateData(cmd);

lblMessage.ForeColor = System.Drawing.Color.Dark-green;

lblMessage.Text = "File Uploaded Successfully";

}

else

{

lblMessage.ForeColor = System.Drawing.Color.Red;

lblMessage.Text = "File format not recognised." +

" Upload Paradigm/Word/PDF/Excel formats";

}

}

VB.Cyberspace

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e Equally EventArgs)

' Read the file and convert it to Byte Array

Dim filePath As Cord = FileUpload1.PostedFile.FileName

Dim filename Equally Cord = Path.GetFileName(filePath)

Dim ext Equally String = Path.GetExtension(filename)

Dim contenttype As String = String.Empty

'Gear up the contenttype based on File Extension

Select Case ext

Case ".doc"

contenttype = "awarding/vnd.ms-word"

Exit Select

Example ".docx"

contenttype = "application/vnd.ms-word"

Exit Select

Case ".xls"

contenttype = "application/vnd.ms-excel"

Exit Select

Case ".xlsx"

contenttype = "awarding/vnd.ms-excel"

Exit Select

Example ".jpg"

contenttype = "image/jpg"

Leave Select

Case ".png"

contenttype = "image/png"

Exit Select

Case ".gif"

contenttype = "prototype/gif"

Leave Select

Case ".pdf"

contenttype = "awarding/pdf"

Go out Select

End Select

If contenttype <> String.Empty Then

Dim fs Every bit Stream = FileUpload1.PostedFile.InputStream

Dim br Equally New BinaryReader(fs)

Dim bytes As Byte() = br.ReadBytes(fs.Length)

'insert the file into database

Dim strQuery As Cord = "insert into tblFiles" _

& "(Name, ContentType, Data)" _

& " values (@Proper noun, @ContentType, @Information)"

Dim cmd As New SqlCommand(strQuery)

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _

= contenttype

cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes

InsertUpdateData(cmd)

lblMessage.ForeColor = Organization.Drawing.Color.Green

lblMessage.Text = "File Uploaded Successfully"

Else

lblMessage.ForeColor = Organisation.Drawing.Color.Red

lblMessage.Text = "File format non recognised." _

& " Upload Image/Word/PDF/Excel formats"

End If

End Sub

The above code simply reads the uploaded File as Stream and then converts the Stream to Byte array using Binary Reader and then the finally the byte arrays is saved to the database InsertUpdateData method executes the query to salvage the data in database

The InsertUpdateData function is given beneath

C#

private Boolean InsertUpdateData(SqlCommand cmd)

{

String strConnString = System.Configuration.ConfigurationManager

.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

cmd.CommandType = CommandType.Text;

cmd.Connectedness = con;

endeavour

{

con.Open up();

cmd.ExecuteNonQuery();

return true;

}

catch (Exception ex)

{

Response.Write(ex.Bulletin);

return false;

}

finally

{

con.Shut();

con.Dispose();

}

}

VB.Internet

Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean

Dim strConnString Equally String = System.Configuration.

ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con Every bit New SqlConnection(strConnString)

cmd.CommandType = CommandType.Text

cmd.Connection = con

Try

con.Open()

cmd.ExecuteNonQuery()

Render True

Catch ex Equally Exception

Response.Write(ex.Message)

Return Imitation

Finally

con.Shut()

con.Dispose()

Stop Effort

End Function

This completes the article. You lot can download the source code in VB.Net and C# from the link below.

UploadFilesDataBase.zip (1.31 mb)

priceblospas.blogspot.com

Source: https://www.aspsnippets.com/Articles/Insert-Word-Document-Files-into-SQL-Server-Database-Table-using-C-and-VBNet.aspx