This is a migrated thread and some comments may be shown as answers.

How to save Images in Mysql Database or Image paths in database.

8 Answers 342 Views

Upload (Obsolete)

This is a migrated thread and some comments may be shown as answers.

8 Answers, 1 is accepted

answered on 26 Mar 2011, 12:32 AM

I attached the code in full for the quick solution I could put together for you. It has a single page (default.aspx) that has a Radgrid and RadUpload control on it. Selecting the file and clicking Upload causes the file to be inserted into the Mysql database and then the Grid is refreshed and the file information is displayed along with the image.

Make sure you have the Mysql.Data.MysqlClient assembly installed and referenced in your test project. The project was created with VS 2010 and targeting .NET 4.0 using latest Q1 2011 tools.

Also at the bottom is script used to create the MySql DB and table used in this example. This should be enough to get you moving forward. Let me know if you get stuck and I will do what I can to help - BUT as this is a college project, you probably need to handle the rest yourself ... remember instructors check these forums too ....

Default.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title></title>

<telerik:RadStyleSheetManager id="RadStyleSheetManager1" runat="server" />

</head>

<body>

<form id="form1" runat="server">

<telerik:RadScriptManager ID="RadScriptManager1" runat="server">

<Scripts>

<%--Needed for JavaScript IntelliSense in VS2010--%>

<%--For VS2008 replace RadScriptManager with ScriptManager--%>

<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.Core.js" />

<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQuery.js" />

<asp:ScriptReference Assembly="Telerik.Web.UI" Name="Telerik.Web.UI.Common.jQueryInclude.js" />

</Scripts>

</telerik:RadScriptManager>

<script type="text/javascript">

//Put your JavaScript code here.

</script>

<telerik:RadAjaxManager ID="RadAjaxManager1" runat="server">

</telerik:RadAjaxManager>

<div>

<telerik:RadGrid ID="RadGrid1" runat="server" CellSpacing="0"

DataSourceID="SqlDataSource1" GridLines="None" AutoGenerateColumns="False">

<MasterTableView DataSourceID="SqlDataSource1"

DataKeyNames="idfiles">

<CommandItemSettings ExportToPdfText="Export to PDF"></CommandItemSettings>

<RowIndicatorColumn FilterControlAltText="Filter RowIndicator column">

</RowIndicatorColumn>

<ExpandCollapseColumn FilterControlAltText="Filter ExpandColumn column">

</ExpandCollapseColumn>

<Columns>

<telerik:GridBoundColumn DataField="idfiles" DataType="System.Int32"

FilterControlAltText="Filter idfiles column" HeaderText="idfiles"

ReadOnly="True" SortExpression="idfiles" UniqueName="idfiles">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="filename"

FilterControlAltText="Filter filename column" HeaderText="filename"

SortExpression="filename" UniqueName="filename">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="filetype"

FilterControlAltText="Filter filetype column" HeaderText="filetype"

SortExpression="filetype" UniqueName="filetype">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="filesize"

FilterControlAltText="Filter filesize column" HeaderText="filesize"

SortExpression="filesize" UniqueName="filesize">

</telerik:GridBoundColumn>

<telerik:GridBoundColumn DataField="description"

FilterControlAltText="Filter description column" HeaderText="description"

SortExpression="description" UniqueName="description">

</telerik:GridBoundColumn>

<telerik:GridBinaryImageColumn DataField="filedata"

FilterControlAltText="Filter column column" HeaderText="filedata"

ImageHeight="200px" ImageWidth="200px" ResizeMode="Fit" UniqueName="column">

</telerik:GridBinaryImageColumn>

</Columns>

<EditFormSettings>

<EditColumn FilterControlAltText="Filter EditCommandColumn column"></EditColumn>

</EditFormSettings>

</MasterTableView>

<FilterMenu EnableImageSprites="False"></FilterMenu>

<HeaderContextMenu CssClass="GridContextMenu GridContextMenu_Default"></HeaderContextMenu>

</telerik:RadGrid>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:filetestConnectionString %>"

ProviderName="<%$ ConnectionStrings:filetestConnectionString.ProviderName %>"

SelectCommand="SELECT * FROM files"></asp:SqlDataSource>

<br />

</div>

<telerik:RadUpload ID="RadUpload1" Runat="server" InputSize="50"

MaxFileInputsCount="1">

</telerik:RadUpload>

<br />

<telerik:RadButton ID="RadButton1" runat="server" onclick="RadButton1_Click"

Text="Upload File">

</telerik:RadButton>

</form>

</body>

</html>

Default.aspx.cs:

using System;

using System.Configuration;

using Telerik.Web.UI;

using MySql.Data.MySqlClient;

public partial class Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void RadButton1_Click(object sender, EventArgs e)

{

// Pull Saved Connection String from web.config. Make sure to verify the connection string is correct

// in the web.config. It is important it is correct as if it is not, the datagrid will not show

// the items correctly.

string strConnection = ConfigurationManager.ConnectionStrings["filetestConnectionString"].ConnectionString;

// Setup the Mysql.Data Conneciton - Note you need to have installed the MySql

// .NET and Referenced the MySQL.NET Assembly

MySqlConnection conn = new MySqlConnection(strConnection);

string qry = "Insert into Files (filename,filedata,filetype,filesize) values (@filename, @filedata, @filetype, @filesize)";

MySqlCommand cmd = new MySqlCommand(qry, conn);

// Iterate through the array of files returned by RadUpload and process them

foreach (UploadedFile f in RadUpload1.UploadedFiles)

{

// Read the binary data into the bytes variable. Found that since the InputStream will not take

// a long variable, had to convert the file length to an int which is reason for extra line

// of code. If this is wrong - let me know ... works fine for me

int l = (int)f.InputStream.Length;

byte[] bytes = new byte[l];

f.InputStream.Read(bytes, 0, l);

// Add the parameters to the MySqlCommand and match it up to variables. If not using the latest

// .NET MySql, the named parameters may have a problem - not sure - haven't tested. We use Telerik

// Openaccess for all of our Datalayers now - but this just one method - could also use ODBC...

cmd.Parameters.Add(new MySqlParameter("@filename", MySqlDbType.VarChar, 45)).Value = f.GetName();

cmd.Parameters.Add(new MySqlParameter("@filedata", MySqlDbType.Binary)).Value = bytes;

cmd.Parameters.Add(new MySqlParameter("@filetype", MySqlDbType.VarChar, 45)).Value = f.ContentType;

cmd.Parameters.Add(new MySqlParameter("@filesize", MySqlDbType.VarChar, 45)).Value = f.ContentLength;

// Open Connection, execute query, close connection

conn.Open();

cmd.ExecuteNonQuery();

conn.Close();

}

// Refresh the Data Grid with Newly Uploaded items

RadGrid1.Rebind();

}

web.config

<?xml version="1.0"?>

<configuration>

<connectionStrings>

<add name="filetestConnectionString" connectionString="server=localhost;User Id=root;password=!mysql;Persist Security Info=True;database=filetest" providerName="MySql.Data.MySqlClient"/>

</connectionStrings>

<appSettings>

<add key="Telerik.ScriptManager.TelerikCdn" value="Disabled"/>

<add key="Telerik.StyleSheetManager.TelerikCdn" value="Disabled"/>

</appSettings>

<system.web>

<compilation debug="true" targetFramework="4.0"/>

<pages>

<controls>

<add tagPrefix="telerik" namespace="Telerik.Web.UI" assembly="Telerik.Web.UI"/>

</controls>

</pages>

<httpHandlers>

<add path="ChartImage.axd" type="Telerik.Web.UI.ChartHttpHandler" verb="*" validate="false"/>

<add path="Telerik.Web.UI.SpellCheckHandler.axd" type="Telerik.Web.UI.SpellCheckHandler" verb="*" validate="false"/>

<add path="Telerik.Web.UI.DialogHandler.aspx" type="Telerik.Web.UI.DialogHandler" verb="*" validate="false"/>

<add path="Telerik.RadUploadProgressHandler.ashx" type="Telerik.Web.UI.RadUploadProgressHandler" verb="*" validate="false"/>

<add path="Telerik.Web.UI.WebResource.axd" type="Telerik.Web.UI.WebResource" verb="*" validate="false"/>

</httpHandlers>

<httpModules>

<add name="RadUploadModule" type="Telerik.Web.UI.RadUploadHttpModule"/>

<add name="RadCompression" type="Telerik.Web.UI.RadCompression"/>

</httpModules>

</system.web>

<system.webServer>

<validation validateIntegratedModeConfiguration="false"/>

<modules runAllManagedModulesForAllRequests="true">

<remove name="RadUploadModule"/>

<add name="RadUploadModule" type="Telerik.Web.UI.RadUploadHttpModule" preCondition="integratedMode"/>

<remove name="RadCompression"/>

<add name="RadCompression" type="Telerik.Web.UI.RadCompression" preCondition="integratedMode"/>

</modules>

<handlers>

<remove name="ChartImage_axd"/>

<add name="ChartImage_axd" path="ChartImage.axd" type="Telerik.Web.UI.ChartHttpHandler" verb="*" preCondition="integratedMode"/>

<remove name="Telerik_Web_UI_SpellCheckHandler_axd"/>

<add name="Telerik_Web_UI_SpellCheckHandler_axd" path="Telerik.Web.UI.SpellCheckHandler.axd" type="Telerik.Web.UI.SpellCheckHandler" verb="*" preCondition="integratedMode"/>

<remove name="Telerik_Web_UI_DialogHandler_aspx"/>

<add name="Telerik_Web_UI_DialogHandler_aspx" path="Telerik.Web.UI.DialogHandler.aspx" type="Telerik.Web.UI.DialogHandler" verb="*" preCondition="integratedMode"/>

<remove name="Telerik_RadUploadProgressHandler_ashx"/>

<add name="Telerik_RadUploadProgressHandler_ashx" path="Telerik.RadUploadProgressHandler.ashx" type="Telerik.Web.UI.RadUploadProgressHandler" verb="*" preCondition="integratedMode"/>

<remove name="Telerik_Web_UI_WebResource_axd"/>

<add name="Telerik_Web_UI_WebResource_axd" path="Telerik.Web.UI.WebResource.axd" type="Telerik.Web.UI.WebResource" verb="*" preCondition="integratedMode"/>

</handlers>

</system.webServer>

</configuration>

Sql Creation Script:

delimiter $$

CREATE DATABASE `filetest` /*!40100 DEFAULT CHARACTER SET latin1 */$$

delimiter $$

CREATE TABLE `files` (

`idfiles` int(11) NOT NULL AUTO_INCREMENT,

`filename` varchar(45) NOT NULL,

`filedata` longblob NOT NULL,

`filetype` varchar(45) NOT NULL,

`filesize` varchar(45) DEFAULT NULL,

`description` varchar(45) DEFAULT NULL,

PRIMARY KEY (`idfiles`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1$$

answered on 26 Mar 2011, 02:28 PM

Thanks. Brain.. :) :) You Rockz,,,, the Code is working fine.. but i have a another small issue ..how to retrive the images from db and make it as gallery... ?? you are using SqlDataSource..for RadGridview. ? na .. when i tried the same for Mysql in Visual studio2010 it says some error. i have the latest dot net connector for mysql. still it's not allowing to add datasource in design time.. can u tell me how to display the images from db like a gallery.. Which controls should suit for this scenario. . and also how to use lightbox.. please this is the last help. from u .. rest i'll manage plz.. :( :(

answered on 29 Mar 2011, 04:53 AM

Note that to use the SQLDataSource, you have to edit the query that is used to pull the information. The designer interface does not produce the same query syntax that the MySql Datasource provider is expecting.

As far as a gallery goes, I imagine it would be easiest to use the RadGrid control and perhaps use a custom item template to display the images. In the case of doing something else, the RadBinaryImage control bound to the correct column should provide you an easy way to display the image.

For downloading the image to the browser, I would have to look at the code I have at the office rather than rewrite it, just easier, but the upload was adapted from the following codeproject article, you can probably look at it and adapt the download code as well.

http://www.codeproject.com/KB/aspnet/image_asp.aspx

It should be pretty straight forward with a little tweaking, but again if you seem to still have trouble after the above article I will look at what I have at the office in the evening.

A little more info on the SQLDataSource. To get the MySQL to work as a Datasource for your project, I used the following in the web.config (Pay special attention to the providerName:

<?xml version="1.0"?>

<configuration>

<connectionStrings>

<add name="filetestConnectionString" connectionString="server=localhost;User Id=root;password=!mysql;Persist Security Info=True;database=filetest" providerName="MySql.Data.MySqlClient"/>

</connectionStrings>

This will allow you to reference the Connection String when dropping the MySQL Datasource onto the page, but again the query syntax the desgner uses is still geared towards Microsoft SQL instead of mySQL - and while they are close, they are not exact. So to get the SQLDatasource to work, you have to go into the ASPX page and edit the query in the code of the SQLDatasource to correctly pull the data from MySQL. Open the ASPX page and look for the SQLDataSource tags:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:filetestConnectionString %>"

ProviderName="<%$ ConnectionStrings:filetestConnectionString.ProviderName %>"

SelectCommand="SELECT * FROM files"></asp:SqlDataSource>

Notice the Select command needs to be modifed to fit syntax needed for MySQL.. Suggest testing the Query in the MySQL client just to verify it is correct.

answered on 07 Apr 2011, 07:46 AM

Hello ravi,

You can use either GridImageColumn or GridBinaryImageColumn for this purpose instead of a template column. Review the demos below for more info:

http://demos.telerik.com/aspnet-ajax/grid/examples/generalfeatures/columntypes/defaultcs.aspx
http://demos.telerik.com/aspnet-ajax/controls/examples/integration/raduploadinajaxifiedgrid/defaultcs.aspx?product=grid

Regards,
Sebastian
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Support and Learning

Productivity and Design Tools

How to save Images in Mysql Database or Image paths in database.

8 Answers, 1 is accepted