Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
Document Management System using ASP.Net & Sql Server
Posted Date: 12 Nov 2004 Resource Type: Articles Category: Web Applications
|
Posted By: Atal Bihari Upadhyay Member Level: Gold
Rating:  Points: 10
|
Document management System.
In this article, I am going to explain how we can upload/delete any type of file in Sql Server i.e use sql server as repository of documents.
Step 1:
Create a table in sql server database as
Table structure.
CREATE TABLE [dbo].[FileData] (
[colid] [int] IDENTITY (1, 1) NOT NULL ,
[FileType] [varchar] (50) NULL ,
[fileLength] [int] NULL ,
[description] [varchar] (25) NULL ,
[FileData] [image] NULL
)
Step 2:
A web page created for displaying uploaded files and for giving option to upload more files. Note that you need to change the connectionstring of sql server to point to your sql server.
Open a new project in VS.net and paste the code in the HTML view mode (Use paste as HTML):
<%@ Import Namespace="System.Data" %>
<%@ Page language="c#" ContentType="image/gif" Codebehind="WebForm2.aspx.cs" AutoEventWireup="false" Inherits="FileUpload.WebForm2" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm2</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" enctype="multipart/form-data" runat="server">
<asp:label id="Label1" style="Z-INDEX: 101; LEFT: 152px; POSITION: absolute; TOP: 0px" runat="server"
Width="225px" Font-Bold="True">Document Manager</asp:label><br>
<asp:label id="Label2" style="Z-INDEX: 103; LEFT: 16px; " runat="server"
Width="128px">Select File to upload</asp:label><INPUT id="myFile" style="Z-INDEX: 102; LEFT: 168px;" type="file"
name="myFile" runat="server"><asp:button id="Button1" style="Z-INDEX: 104; LEFT: 424px;" runat="server"
Width="112px" Text="Upload"></asp:button><asp:label id="MessageText" style="Z-INDEX: 105; LEFT: 200px; "
runat="server" Width="208px"></asp:label>
<hr style="Z-INDEX: 106; LEFT: 8px; ">
<asp:repeater id="rptFileData" runat="server">
<HeaderTemplate>
<table border="1" cellpadding="4" width="100%">
<tr bgcolor="#eeeeee">
<th>
Id</th>
<th>
Content type</th>
<th>
FileName</th>
<th>
Open</th>
<th>
Delete</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# DataBinder.Eval(Container.DataItem, "colid")%></td>
<td><%# DataBinder.Eval(Container.DataItem, "filetype") %></td>
<td><%# DataBinder.Eval(Container.DataItem, "description") %></td>
<td><a href="openfile.aspx?action=open&id=<%# DataBinder.Eval(Container.DataItem, "colid")%>">Open Document</a></td>
<td><a href="openfile.aspx?action=delete&id=<%# DataBinder.Eval(Container.DataItem, "colid")%>">Delete Document</a></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:repeater></form>
</body>
</HTML>
Paste the following code in code behind file.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
namespace FileUpload
{
/// <summary>
/// Summary description for WebForm2.
/// </summary>
public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Label MessageText;
protected System.Web.UI.WebControls.Repeater rptFileData;
protected System.Web.UI.HtmlControls.HtmlInputFile myFile;
private void Page_Load(object sender, System.EventArgs e)
{
populatedata();
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
private void Button1_Click(object sender, System.EventArgs e)
{
SqlConnection conn=new SqlConnection("Server=alfa_server;UID=app_user;PWD=app;Database=Pubs");
SqlCommand comm=new SqlCommand("Insert into filedata(filetype,description,filelength,filedata) values(@filetype,@description,@fileLength,@filedata)",conn);
SqlParameter param1=new SqlParameter("@filetype",SqlDbType.NVarChar);
param1.Value=myFile.PostedFile.ContentType;
comm.Parameters.Add(param1);
SqlParameter param2=new SqlParameter("@description",SqlDbType.NVarChar);
param2.Value=Path.GetFileName(myFile.PostedFile.FileName);
comm.Parameters.Add(param2);
SqlParameter param3=new SqlParameter("@fileLength",SqlDbType.Int);
param3.Value=myFile.PostedFile.ContentLength;
comm.Parameters.Add(param3);
SqlParameter param4=new SqlParameter("@filedata",SqlDbType.Image);
int datalength=myFile.PostedFile.ContentLength;
Byte[] content=new byte[datalength];
myFile.PostedFile.InputStream.Read(content,0,datalength);
param4.Value=content;
comm.Parameters.Add(param4);
conn.Open();
comm.ExecuteNonQuery();
conn.Close();
populatedata();
}
private void populatedata()
{
SqlConnection conPubs;
SqlCommand cmdSelect;
SqlDataReader dtrMyUploadedData;
// Retrieve records from database
conPubs = new SqlConnection( "Server=alfa_server;UID=app_user;PWD=app;Database=Pubs" );
cmdSelect = new SqlCommand( "Select colid,filetype,description From Filedata", conPubs );
conPubs.Open();
dtrMyUploadedData = cmdSelect.ExecuteReader();
// Bind to Repeater
rptFileData.DataSource = dtrMyUploadedData;
rptFileData.DataBind();
dtrMyUploadedData.Close();
conPubs.Close();
}
}
}
Step 3:
Create a web page for uploding/deleting the file to/from sql server database.
Paste the following code in inline of aspx file.
<%@ Page language="c#" Codebehind="Actionfile.aspx.cs" AutoEventWireup="false" Inherits="FileUpload.Actionfile" %>
As this file is used for uploading/deleting the files, there is no user interfact.
In the code behind file, paste the following code:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace FileUpload
{
/// <summary>
/// Summary description for openfile.
/// </summary>
public class openfile : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection conPubs = new SqlConnection( "Server=alfa_server;UID=app_user;PWD=app;Database=Pubs" );
conPubs.Open();
string colid=Request.QueryString["id"];
string strSql;
if (Request.QueryString["action"]=="delete")
{
strSql= "delete From Filedata where colid=" +colid;
SqlCommand cmdDelete = new SqlCommand(strSql, conPubs );
cmdDelete.ExecuteNonQuery();
conPubs.Close();
Response.Redirect("webform2.aspx");
}
SqlDataReader dtrMyUploadedData;
// Retrieve records from database
strSql= "Select filetype,description,fileLength,FileData From Filedata where colid=" +colid;
SqlCommand cmdSelect = new SqlCommand(strSql, conPubs );
dtrMyUploadedData=cmdSelect.ExecuteReader();
dtrMyUploadedData.Read();
Response.ContentType=dtrMyUploadedData["FileType"].ToString();
Response.OutputStream.Write((byte[])dtrMyUploadedData["Filedata"],0,(int)dtrMyUploadedData["FileLength"]);
// Bind to Repeater
conPubs.Close();
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
Step 4:
Build the project.
That's it. By now, your application is ready for use. Just set the startup page of the project as first page created and run the project.
How to Upload Large Files:
ASP.NET will permits only files that are 4,096 kilobytes (KB) (or 4 megabytes [MB]) or less to be uploaded to the Web server.
For uploading larger files, you must change the maxRequestLength parameter of the <httpRuntime> section in the Web.config file as below:
<httpRuntime
executionTimeout="100"
maxRequestLength="4096"
useFullyQualifiedRedirectUrl="false"
appRequestQueueLimit="100"
/>
Hope this is useful all for storing documents. Please post your queries and comments for this articles.
|
Responses
|
| Author: Sandeep 28 Dec 2004 | Member Level: Bronze Points : 0 | Hello,
I need some more idea on this topic. What is benifit to store data into SQL server (means into BLOB chartype in SQL). Why can not we just store file on to PC and just add link name into SQL.
Please can anybody give me this ans.
Thanks
| | Author: jean-noel staub 18 Feb 2005 | Member Level: Bronze Points : 0 | i don't recommand to store documents in a sql database. A compagny has to store documents for twenty years at least and during this time software are upgraded or disappeared.it's very difficult to maintain this kind of storage embedded in an application.
The simplest is the best , store the links to the files strored in a secured and backuped repository. The best way is to store the raw format files for the authors and to generate life a reading document for other people (pdf,tiff ,...).this is based on 30 years of drawing office management with scanned drawings, document management and internet applications.
| | Author: Kenny GOH 02 Mar 2005 | Member Level: Bronze Points : 0 | how can I do a search on the documents content (doc, pdf, etc) that stores in the SQL Server?
cheers,
kennyGOH
| | Author: Siddesh Kapadi 21 Apr 2005 | Member Level: Bronze Points : 0 | the link for open document and delete document doesnot work as page cannot be displayed error is displayed
| | Author: Raul Juarez 18 Jun 2005 | Member Level: Bronze Points : 0 | I was trying to display the file stored on SQL server with out success. Please if some one help me with this..
private void ShowTheFile()
{
// Define SQL select statement
string SQL = "SELECT FileType, fileLength, FileData FROM FILEDATA WHERE colid =4 ";
// Create Connection object
SqlConnection connect = new SqlConnection( "Server=JUAN\\NETSDK;UID=sa;PWD=user1;Database=Professional" );
// Create Command Object
SqlCommand dbComm = new SqlCommand(SQL, connect );
// Open Connection
connect.Open();
// Execute command and receive DataReader
SqlDataReader dbRead = dbComm.ExecuteReader();
// Read row
dbRead.Read();
// Clear Response buffer
Response.Clear();
// Set ContentType to the ContentType of our file
Response.ContentType = (string)dbRead["description"];
// Write data out of database into Output Stream
Response.OutputStream.Write((byte[])dbRead["FileData"], 0, (int)dbRead["fileLength"]);
// Close database connection
connect.Close();
// End the page
Response.End();
}
| | Author: ashokkuamr 05 Sep 2008 | Member Level: Bronze Points : 2 | using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
#region Private Variables
private DataSet ds;
private SqlDataAdapter da;
private SqlConnection con;
DataView dv;
#endregion
#region EventHandlers
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//First time request
//Get the data from database and move it to session
FillDataSet();
Session.Add("data", ds);
FillDropDown();
FillDisConnectedDropdown();
}
else
{
//this is not a first time request
//Get the data from session
ds = (DataSet)Session["data"];
}
CreateDataView();
myGrid.DataSource = dv;
myGrid.DataBind();
}
#endregion
#region Private Methods
private void FillDataSet()
{
CreateConnectionObject();
CreateAdapter();
ds = new DataSet();
da.Fill(ds);
}
private void CreateConnectionObject()
{
con = new SqlConnection();
con.ConnectionString = "server=servername;database=pubs;uid=train;pwd=pass";
}
private void CreateAdapter()
{
da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.CommandText = "SELECT [au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract] FROM [authors]";
da.SelectCommand.Connection = con;
}
private void CreateDataView()
{
dv = new DataView();
dv.Table = ds.Tables[0];
//dv.RowFilter = "au_fname = 'John'";
//dv.Sort = "au_id";
//if (dv.Count > 0)
//{
// dv[0]["au_lname"] = "David";
//}
}
private void FillDropDown()
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT [au_lname] FROM [authors]";
cmd.Connection = con;
try
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string strLastName = dr["au_lname"].ToString();
DropDownList1.Items.Add(strLastName);
}
}
catch (Exception ex)
{
}
finally
{
con.Close();
}
}
private void FillDisConnectedDropdown()
{
ddlDisconnected.DataSource = ds;
ddlDisconnected.DataTextField = "au_lname";
ddlDisconnected.DataValueField = "au_id";
ddlDisconnected.DataBind();
}
#endregion
}
|
|