Skip to content

Instantly share code, notes, and snippets.

@kakari2

kakari2/ASP.NET_FileUpload_SQLServer

Last active August 31, 2015 16:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kakari2/191fd3a2017e17ce190f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kakari2/191fd3a2017e17ce190f to your computer and use it in GitHub Desktop.
Download ZIP
ASP.net - FileUpload
Raw
ASP.NET_FileUpload_SQLServer
CREATE TABLE [r_file](
[ID] [int] IDENTITY(1,1) NOT NULL,
[title] [nvarchar](50) NOT NULL,
[filename] [nvarchar](50) NOT NULL,
[type] [nvarchar](100) NOT NULL,
[orders] [int] NOT NULL,
[bin] [varbinary](max) NOT NULL,
[insert_datetime] [datetime] NOT NULL,
[update_datetime] [datetime] NULL,
CONSTRAINT [PK_r_file] PRIMARY KEY CLUSTERED
(
[ID] ASC
)) ON [PRIMARY]
Raw
ASP.NET_FileUpload_WebForm1.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="WebForm1.aspx.vb" Inherits="FileUpload.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server" enctype="multipart/form-data">
<div runat ="server" id="divMessage">
<span runat="server" id="spanMessage"></span>
</div>
<div>
<h1>FileUpload</h1>
<div>
<span>Link Name : </span>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</div>
<div>
<span>Explanation : </span>
<span runat="server" id="fileType"></span>
</div>
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
</div>
<div>
<asp:Button ID="Button_Update" runat="server" Text="Update" />
<asp:Button ID="Button_Add" runat="server" Text="Add" />
</div>
</div>
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="ID">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField InsertVisible="False" ShowHeader="False" HeaderText="title">
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Download" CommandArgument='<%# Bind("ID") %>'>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("title") %>'></asp:Label>
</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ID" InsertVisible="False" SortExpression="ID">
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="filename" SortExpression="filename">
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("filename") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="type" SortExpression="type">
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("type") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="orders">
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Bind("orders") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="insert_datetime" SortExpression="insert_datetime">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("insert_datetime") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="update_datetime" SortExpression="update_datetime">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("update_datetime") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:Button ID="ButtonUp" runat="server" CausesValidation="false" CommandName="Up" CommandArgument='<%# Bind("ID") %>' Text="UP" />
<asp:Button ID="ButtonDown" runat="server" CausesValidation="false" CommandName="Down" CommandArgument='<%# Bind("ID") %>' Text="Down" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CausesValidation="false" CommandName="EditFile" CommandArgument='<%# Bind("ID") %>' Text="Edit" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:Button ID="Button2" runat="server" CausesValidation="false" CommandName="DeleteFile" CommandArgument='<%# Bind("ID") %>' Text="Delete" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EmptyDataTemplate>
<span>File does not exist</span>
</EmptyDataTemplate>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
</asp:GridView>
</div>
</form>
</body>
</html>
Raw
ASP.NET_FileUpload_WebForm1.aspx.vb
Imports System.Data.SqlClient
Imports System.IO
Public Class WebForm1
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not IsPostBack Then
If ViewState("FileID") IsNot Nothing Then
Me.ViewState("FileID").clear
End If
Button_Update.Enabled = False
GridView1.DataSource = getFiles()
GridView1.DataBind()
If Session("message") IsNot Nothing Then
spanMessage.InnerText = Session("message")
Session.Remove("message")
spanMessage.Visible = True
End If
End If
End Sub
Private Sub GridView1_RowCommand(sender As Object, e As GridViewCommandEventArgs) Handles GridView1.RowCommand
Select Case e.CommandName
Case "Download"
Dim dbCon As SqlConnection = New SqlClient.SqlConnection
' Get ID
Dim intId As Integer = CInt(e.CommandArgument)
' Open DB
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString
dbCon.Open()
' データの読込み
Dim sql As String = "SELECT filename, type, bin FROM r_file WHERE ID=@ID"
Dim command As SqlCommand = New SqlCommand(sql, dbCon)
command.Parameters.Add("@ID", SqlDbType.Int).Value = intId
Dim datatable As New DataTable
datatable.Load(command.ExecuteReader())
Response.HeaderEncoding = Text.Encoding.GetEncoding("Shift-JIS")
Response.ContentType = datatable.Rows(0).Item("type").ToString
Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(datatable.Rows(0).Item("filename")))
Response.Flush()
Response.BinaryWrite(CType(datatable.Rows(0).Item("bin"), Byte()))
Response.End()
command.Connection.Close()
Case "EditFile"
'MsgBox(e.CommandArgument)
Me.ViewState.Add("FileID", e.CommandArgument)
Dim dt As DataTable = getFiles(CInt(e.CommandArgument))
TextBox1.Text = dt.Rows(0).Item(1).ToString()
Button_Update.Enabled = True
Case "Down"
Call orderdown(CInt(e.CommandArgument))
Case "Up"
Call OrderUp(CInt(e.CommandArgument))
Case "DeleteFile"
Call DeleteFile(CInt(e.CommandArgument))
Case Else
End Select
End Sub
Protected Sub Button_Add_Click(sender As Object, e As EventArgs) Handles Button_Add.Click
If FileUpload1.HasFile Then
' Add New file
Dim dbCon As SqlConnection = New SqlClient.SqlConnection
Dim dbTrans As SqlTransaction
Try
'SQL Connection
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString
dbCon.Open()
'Transaction
dbTrans = dbCon.BeginTransaction(IsolationLevel.ReadCommitted)
Dim sql As String = "INSERT INTO r_file (title, filename, type, orders, bin, insert_datetime,update_datetime) VALUES (@title,@filename,@type,@orders,@bin,@insert_datetime, @update_datetime)"
Dim command As SqlCommand = New SqlCommand(sql, dbCon, dbTrans)
' first order is 1
Dim maxorder As Integer = getMaxOrder() + 1
'Read File as a Byte Data
Dim binaryData(FileUpload1.PostedFile.InputStream.Length) As Byte
FileUpload1.PostedFile.InputStream.Read(binaryData, 0, FileUpload1.PostedFile.InputStream.Length)
command.Parameters.Add("@title", SqlDbType.NVarChar).Value = Me.TextBox1.Text
command.Parameters.Add("@filename", SqlDbType.NVarChar).Value = Path.GetFileName(FileUpload1.PostedFile.FileName)
command.Parameters.Add("@type", SqlDbType.NVarChar).Value = FileUpload1.PostedFile.ContentType
command.Parameters.Add("@orders", SqlDbType.Int).Value = maxorder
command.Parameters.Add("@bin", SqlDbType.VarBinary).Value = binaryData
command.Parameters.Add("@insert_datetime", SqlDbType.DateTime).Value = Now()
command.Parameters.Add("@update_datetime", SqlDbType.DateTime).Value = DBNull.Value
' Run SQL
Dim result As String
result = command.ExecuteNonQuery()
dbTrans.Commit()
'Close SQL Connection
dbCon.Close()
Session("message") = "OK"
Response.Redirect("~/WebForm1.aspx", False)
'Response.Write("OK")
Catch ex As Exception
dbCon.Close()
Response.Write("NG...")
End Try
' Update
Session("message") = "OK"
Response.Redirect("~/WebForm1.aspx", False)
Else
Session("message") = "Select file..."
Response.Redirect("~/WebForm1.aspx", False)
End If
End Sub
Private Sub Button_Update_Click(sender As Object, e As EventArgs) Handles Button_Update.Click
If FileUpload1.HasFile Then
' file selected
Else
' file not selected
' UPDATE
Response.Redirect("~/WebForm1.aspx", False)
End If
End Sub
Private Sub GridView1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GridView1.RowDataBound
GridView1.Columns(1).Visible = False
GridView1.Columns(4).Visible = False
End Sub
Private Function getMaxOrder() As Integer
Dim sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString)
Dim sql As New SqlCommand
sql.CommandText = "select Max(orders) from r_file"
Dim cmd As SqlCommand = New SqlCommand(sql.CommandText, sc)
cmd.Connection.Open()
Dim datatable As New DataTable
datatable.Load(cmd.ExecuteReader())
cmd.Connection.Close()
If IsDBNull(datatable.Rows(0)(0)) Then
Return 0
Else
Return CInt(datatable.Rows(0)(0))
End If
End Function
Private Function OrderDown(ByVal ID As Integer) As Integer
Dim myOrder As Integer = getMyOrder(ID)
Dim higherOrder As Integer = getHigherOrder(myOrder)
If higherOrder < 0 Then
Return 0
Exit Function
End If
'Call updateMyOrder(ID, higherOrder)
Call SwapOrder(ID, myOrder, higherOrder)
Return 0
End Function
Private Function SwapOrder(ByVal ID As Integer,
ByVal myOrder As Integer,
ByVal OtherOrder As Integer) As Integer
Dim dbCon As SqlConnection = New SqlClient.SqlConnection
Dim dbTrans As SqlTransaction
Dim result As Integer = 0
'SQL Connection
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString
dbCon.Open()
'Transaction
dbTrans = dbCon.BeginTransaction(IsolationLevel.ReadCommitted)
Try
Dim sql As String = "Update r_file set orders = @myOrder where orders = @OtherOrder"
Dim command As SqlCommand = New SqlCommand(sql, dbCon, dbTrans)
command.Parameters.Add("@myOrder", SqlDbType.Int).Value = myOrder
command.Parameters.Add("@OtherOrder", SqlDbType.Int).Value = OtherOrder
' Run SQL
result = command.ExecuteNonQuery()
sql = "Update r_file set orders = @OtherOrder where ID = @ID"
command = New SqlCommand(sql, dbCon, dbTrans)
command.Parameters.Add("@ID", SqlDbType.Int).Value = ID
command.Parameters.Add("@OtherOrder", SqlDbType.Int).Value = OtherOrder
' Run SQL
result = command.ExecuteNonQuery()
dbTrans.Commit()
'Close SQL Connection
dbCon.Close()
Session("message") = "OK"
Response.Redirect("~/WebForm1.aspx", False)
'Response.Write("OK")
Catch ex As Exception
dbTrans.Rollback()
dbCon.Close()
Response.Write("NG...")
End Try
Return result
End Function
Private Function OrderUp(ByVal ID As Integer) As Integer
Dim myOrder As Integer = getMyOrder(ID)
Dim lowerOrder As Integer = getLowerOrder(myOrder)
If lowerOrder < 0 Then
Return 0
Exit Function
End If
'Call updateMyOrder(ID, lowerOrder)
Call SwapOrder(ID, myOrder, lowerOrder)
Return 0
End Function
Private Function DeleteFile(ByVal ID As Integer) As Integer
Dim dbCon As SqlConnection = New SqlClient.SqlConnection
Dim dbTrans As SqlTransaction
Dim result As Integer = 0
'SQL Connection
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString
dbCon.Open()
'Transaction
dbTrans = dbCon.BeginTransaction(IsolationLevel.ReadCommitted)
Try
Dim sql As String = "Delete from r_file where ID = @ID"
Dim command As SqlCommand = New SqlCommand(sql, dbCon, dbTrans)
command.Parameters.Add("@ID", SqlDbType.Int).Value = ID
' Run SQL
result = command.ExecuteNonQuery()
dbTrans.Commit()
'Close SQL Connection
dbCon.Close()
Session("message") = "OK"
Response.Redirect("~/WebForm1.aspx", False)
'Response.Write("OK")
Catch ex As Exception
dbTrans.Rollback()
dbCon.Close()
Response.Write("NG...")
End Try
Return result
End Function
Private Function updateMyOrder(ByVal ID As Integer, ByVal lowerOrder As Integer) As Integer
Dim dbCon As SqlConnection = New SqlClient.SqlConnection
Dim dbTrans As SqlTransaction
Try
'SQL Connection
dbCon.ConnectionString = ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString
dbCon.Open()
'Transaction
dbTrans = dbCon.BeginTransaction(IsolationLevel.ReadCommitted)
Dim sql As String = "Update r_file set orders = @lowerOrder where ID = @ID"
Dim command As SqlCommand = New SqlCommand(sql, dbCon, dbTrans)
command.Parameters.Add("@ID", SqlDbType.Int).Value = ID
command.Parameters.Add("@lowerOrder", SqlDbType.Int).Value = lowerOrder
' Run SQL
Dim result As String
result = command.ExecuteNonQuery()
dbTrans.Commit()
'Close SQL Connection
dbCon.Close()
Session("message") = "OK"
Response.Redirect("~/WebForm1.aspx", False)
'Response.Write("OK")
Catch ex As Exception
dbCon.Close()
Response.Write("NG...")
End Try
Return 0
End Function
Private Function getLowerOrder(ByVal myOrder As Integer) As Integer
Dim sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString)
Dim sql As New SqlCommand
sql.CommandText = "select Max(orders) from r_file where orders < @myOrder"
Dim cmd As SqlCommand = New SqlCommand(sql.CommandText, sc)
cmd.Parameters.Add("@myOrder", SqlDbType.Int).Value = myOrder
cmd.Connection.Open()
Dim datatable As New DataTable
datatable.Load(cmd.ExecuteReader())
cmd.Connection.Close()
If IsDBNull(datatable.Rows(0)(0)) Then
Return -1
Else
Return datatable.Rows(0)(0)
End If
End Function
Private Function getHigherOrder(ByVal myOrder As Integer) As Integer
Dim sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString)
Dim sql As New SqlCommand
sql.CommandText = "select Min(orders) from r_file where orders > @myOrder"
Dim cmd As SqlCommand = New SqlCommand(sql.CommandText, sc)
cmd.Parameters.Add("@myOrder", SqlDbType.Int).Value = myOrder
cmd.Connection.Open()
Dim datatable As New DataTable
datatable.Load(cmd.ExecuteReader())
cmd.Connection.Close()
If IsDBNull(datatable.Rows(0)(0)) Then
Return -1
Else
Return datatable.Rows(0)(0)
End If
End Function
Private Function getMyOrder(ByVal ID As String) As Integer
Dim sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString)
Dim sql As New SqlCommand
sql.CommandText = "select orders from r_file where ID = @ID"
Dim cmd As SqlCommand = New SqlCommand(sql.CommandText, sc)
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID
cmd.Connection.Open()
Dim datatable As New DataTable
datatable.Load(cmd.ExecuteReader())
cmd.Connection.Close()
If datatable.Rows.Count > 0 Then
Return datatable.Rows(0)(0)
Else
Return -1
End If
End Function
Private Function getFiles() As DataTable
Dim sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString)
Dim sql As New SqlCommand
sql.CommandText = "select ID, title, filename, type, orders, insert_datetime, update_datetime from r_file order by orders, ID"
Dim cmd As SqlCommand = New SqlCommand(sql.CommandText, sc)
cmd.Connection.Open()
Dim datatable As New DataTable
datatable.Load(cmd.ExecuteReader())
cmd.Connection.Close()
Return datatable
End Function
Private Function getFiles(ByVal ID As Integer) As DataTable
Dim sc As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("FileUploadConnectionString").ConnectionString)
Dim sql As New SqlCommand
sql.CommandText = "select ID, title, filename, type, orders, insert_datetime, update_datetime from r_file where ID = @ID order by orders, ID"
Dim cmd As SqlCommand = New SqlCommand(sql.CommandText, sc)
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID
cmd.Connection.Open()
Dim datatable As New DataTable
datatable.Load(cmd.ExecuteReader())
cmd.Connection.Close()
Return datatable
End Function
End Class
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment