I am moving my blog

Dear Friends,

I am moved my blog to http://www.dotnetthoughts.net. Thanks to Srinivasan A Paul Joseph, for the migration and hosting support. I will keep this site up, but it will not be updated any more. I am still using WordPress as my blogging software.

If you are subscribing to my feed or if you have linked to this blog, you can change that to use the new feed. See you at dotnetthoughts.net

In VB.Net style.

<Obsolete("This blog is obsolute. Use http://www.dotnetthoughts.net instead")> _
https://anuraj.wordpress.com
Posted in Personal

Covert PowerPoint Slides to Images

Today one of my colleague comes with a problem; he want to display a Powerpoint presentation in his Sharepoint site. But we didn’t got any direct way to display it. They we tweaked the code, to export the Slides to images and display it using the a custom slideshow web part, like the AjaxToolkit slideshow control.

I am attaching the code to export all the slides from a Power Point to Images

string ExportLocation = "C:\\Sample";
Microsoft.Office.Interop.PowerPoint.Application ppApp = new Microsoft.Office.Interop.PowerPoint.Application();
ppApp.Visible = MsoTriState.msoTrue;
ppApp.WindowState = PpWindowState.ppWindowMinimized;
Microsoft.Office.Interop.PowerPoint.Presentations oPresSet = ppApp.Presentations;
Microsoft.Office.Interop.PowerPoint._Presentation oPres = oPresSet.Open("C:\\ScreenShots.ppt",
            MsoTriState.msoFalse, MsoTriState.msoFalse,
            MsoTriState.msoFalse);
ppApp.ShowWindowsInTaskbar = MsoTriState.msoFalse;	//Hiding the application; But it will be displayed always:)
try
{
    Slides objSlides = oPres.Slides;	//Getting all the slides
    for (int i = 1; i < objSlides.Count - 1; i++)
    {
        string file = Path.Combine(ExportLocation, string.Format("{0}.{1}", objSlides[i].Name, "jpg"));
        oPres.Slides[i].Export(file, "jpg", 800, 600);
    }
}
finally
{
    ppApp.Quit();	//Closing the Powerpoint application. Sometimes it won't work too.
}

Thanks to ArunKumar, for providing the code snippet.

Posted in .Net, .Net 3.0 / 3.5, Office Interoperability | Tagged , , , , , , , | Leave a comment

How to Store and Retrieve files from SQL Server Database

The forum I joined recently got lot of queries like How to Save Images in the Database, How to save files in SQL Server, How read files from Database etc. So I thought of writing a post regarding this. Even though I am part of a Web project, I am doing some Windows applications for the client. So I thought it will nice to brush-up the ASP.Net skills.

Here is the code. I am using SQL Server 2008, but I am not using FileStream for the current project, I already wrote a post to how to
manage files with FileStream feature. In this post I am using nvarchar(MAX) datatype for storing the file content.

Table Design

CREATE TABLE [dbo].[tblFiles](
	[FileId] [uniqueidentifier] NOT NULL,
	[Filename] [nvarchar](255) NOT NULL,
	[FileContent] [varbinary](max) NULL
)

And the I set FileId default to newId() and FileContent default to NULL

ALTER TABLE [dbo].[tblFiles] ADD  CONSTRAINT [DF_tblFiles_FileId]  DEFAULT (newid()) FOR [FileId]
GO
ALTER TABLE [dbo].[tblFiles] ADD  CONSTRAINT [DF_tblFiles_FileContent]  DEFAULT (NULL) FOR [FileContent]
GO

I wrote the code in C#. I am having a Asp FileUpload control and a button to upload the file, and a Repeater control with two controls, a label for displaying the filename and hyper link control for downloading the file.

<asp:FileUpload runat="server" ID="fileUploadImage" />
<asp:Button runat="server" ID="cmdUpload" Text="Upload File" OnClick="cmdUpload_Click" />
<asp:Repeater runat="server" ID="rptrFiles">
    <HeaderTemplate>
        <table>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <asp:Label runat="server" ID="lblFilename" Text='<%# Eval("FileName")%>' />
            </td>
            <td>
              <asp:HyperLink runat="server" Target="_blank" ID="lbtDownload" Text="Download" NavigateUrl='<%# "Download.aspx?File="  + Eval("FileId").ToString() %>' />
            </td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>

Uploading the File to the Database
Code behind

protected void cmdUpload_Click(object sender, EventArgs e)
{
    string fileName = Path.GetFileName(this.fileUploadImage.FileName);
    byte[] fileContent = this.fileUploadImage.FileBytes;
    using (SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=sampledb"))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand("INSERT INTO tblFiles(Filename, FileContent) VALUES(@Filename, @FileContent)", connection))
        {
            SqlParameter fileNameParameter = new SqlParameter("@Filename", System.Data.SqlDbType.NVarChar, 255);
            fileNameParameter.Value = fileName;
            SqlParameter fileContentParameter = new SqlParameter("@FileContent", System.Data.SqlDbType.VarBinary);
            fileContentParameter.Value = fileContent;
            command.Parameters.AddRange(new SqlParameter[] { fileNameParameter, fileContentParameter });
            command.ExecuteNonQuery();
        }
    }
}

And here is code to bind the repeater from the Database

DataTable dtFiles = new DataTable("Files");
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT FileId, FileName FROM tblFiles", "Server=.\\SQLEXPRESS;User Id=sa;Password=sapassword;Database=sampledb"))
{
    adapter.Fill(dtFiles);
}
this.rptrFiles.DataSource = dtFiles;
this.rptrFiles.DataBind();

Download / Read the file from Database
And to download / read the file from Database, I am passing the File unique id to another page(download.aspx).I this page I am checking for the File querysting and based on that reading filecontent from Sql and writing it to Asp.net output stream. You can get more information about how to download files from IIS in this post.

protected void Page_Load(object sender, EventArgs e)
{
    if (Request.QueryString["File"] != null)
    {
        string fileId = Request.QueryString["File"];
        using (SqlConnection connection = new SqlConnection("Server=.\\SQLEXPRESS;User Id=sa;Password=assyst@123;Database=Work"))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("SELECT Filename, FileContent FROM tblFiles WHERE FileId = @FileId", connection))
            {
                command.Parameters.AddWithValue("@FileId", fileId);
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.HasRows)
                {
                    reader.Read();
                    byte[] content = reader["FileContent"] as byte[];
                    string filename = reader["FileName"].ToString();
                    Response.Clear();
                    Response.ClearContent();
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);
                    Response.AddHeader("Content-Length", content.Length.ToString());
                    Response.OutputStream.Write(content, 0, content.Length);
                    Response.End();
                }
            }
        }
    }
}

Please write to me if I missed something. Happy Programming:)

Posted in .Net, .Net 3.0 / 3.5, ASP.Net, SQL Server, Visual Studio, Windows Forms, WPF | Tagged , , , , , , , , , , | Leave a comment

Microsoft Community Tech Days

Kerala Microsoft Users Group is organizing the Microsoft Community Tech Day in Trivandrum on 24th Oct 2009.

K-MUG Logo

Agenda

  1. 08:30AM – 09:15AM – Registration
  2. 09:15AM – 09:25AM – Welcome Speech
  3. 09:25AM – 09:45AM – Key Note Session
  4. 09:45AM – 05:00PM – Technical sessions

Technical Sessions

  1. Windows Azure & Clouds
  2. SQL Server tips
  3. Coding for Fun (Twitter, Orkut, Facebook, GTalk etc from .NET C#)
  4. SketchFlow for prototyping
  5. Windows Presentation Foundation (WPF)
  6. LINQ
  7. Writing Secure Code

For more details visit
http://k-mug.org/content/ctd09.aspx

Free Registration either at http://communitytechdays.com/(if you have live id)
or
http://www.eventbrite.com/event/418804656

Note: There can be some changes in Agenda based on the last minute updates

Posted in Miscellaneous, User Group Activities | Tagged , , , , , | Leave a comment

Convert Image to Icon using C#

Sometimes we will get nice Images from Web as Icons. But we can’t use these Images as application icons in .Net, because the .Net supports *.ico(Icon) format only. This code will convert an Image to Icon using C#. It is written .Net Framework 3.5, but it should work in .Net 2.0. It supports Images upto size 128×128. And supports various image formats(*.jpg,*.gif, *.png. *.bmp).


using System;
using System.Drawing;
using System.IO;

string fileName, newFileName;
fileName = "C:\Sample.jpg";
newFileName = Path.ChangeExtension(fileName, ".ico");
using (Bitmap bitmap = Image.FromFile(fileName, true) as Bitmap)
{
    using (Icon icon = Icon.FromHandle(bitmap.GetHicon()))
    {
        using (Stream imageFile = File.Create(newFileName))
        {
            icon.Save(imageFile);
            Console.WriteLine("Converted - {0}", newFileName);
        }
    }
}

Code it pretty self explanatory. Let me know if you have faced any issues.

Posted in .Net, .Net 3.0 / 3.5, ASP.Net, Visual Studio, Win 32 API, Windows Forms | Tagged , , , , , , , | 6 Comments

Import / export data in MS Excel using C#

Sometimes we may require to generate Excel file from our reports,read from excel files to import data etc. This can be achieved using Office Interop (Office Automation) assemblies, but Office Automation in Web servers,got some issues;(More details: http://support.microsoft.com/kb/257757). The alternative is using OleDb provider. You may need to add one more attribute to connection string to connect to the Excel file. And connection string will be

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"

Please note the “Extended Properties” attribute. This attribute helps us to query the excel file.

Exporting Data from Data Table to Excel File.

string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"
// Establish a connection to the data source.
using(OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open()
//creating a new Sheet with name sample and three columns with Heading firstname, lastname and email
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "CREATE TABLE [Sample$](FirstName Char(255), LastName char(255), Email char(255))";
command.ExecuteNonQuery();
}
//Adding records to the Sample Worksheet
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "INSERT INTO TABLE [Sample$](FirstName,LastName,Email) VALUES('Anuraj','P','anuraj.p@example.com')";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO TABLE [Sample$](FirstName,LastName,Email) VALUES('sreekumar','vn','sreekumar.vn@example.com')";
command.ExecuteNonQuery();
}
}

Import the Data from Excel

DataTable dt;
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=C:\Sample.xls; Extended Properties=Excel 8.0;"
// Establish a connection to the data source.
using(OleDbConnection Connection = new OleDbConnection(connectionString))
{
Connection.Open()
//reading data from excel to Data Table
using(OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "SELECT * FROM [Sample]";
using(OleDbDataAdapter adapter =new OleDbDataAdapter())
{
adapter.SelectCommand = command;
adapter.Fill(dt);
}
}
}

You can get more information from Microsoft support site

http://support.microsoft.com/kb/306023 – How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET

Posted in .Net, .Net 3.0 / 3.5, ASP.Net | Tagged , , , , , , , | 5 Comments

Adding and Reading files from SQL Server 2008 Filestream

Few days back I wrote a Post about FileStream feature in SQL 2008.(Filestream in SQL Server 2008). In this post I am trying to write the how to manage or use the FileStream feature from .Net managed code.

For adding a File to FileStream enabled table, we are using a new Data Type, SqlFileStream, which comes with .Net 3.5.

I am using the Table structure as in the Previous post, as the example, that Table contains 3 columns, FileID(PK), FileName and FileContents.
For inserting a File you need to begin a transaction, insert an row in to the Table, without the contents of the File, for reading the Transaction context. Then using FileStream, write the contents of the File to the Row. After writting the stream, need to commit the transaction to update the file to the Database. I am writing the code in VB.Net.

Code for saving the File to the Database

Private Sub AddFile(ByVal fileName As String)
	Dim _Transaction As SqlTransaction
	Dim _Command As SqlCommand
	Dim _DataReader As SqlDataReader
	Dim _Connection As SqlConnection
	Dim _FileId As String
	Dim _FilePath As String
	Dim _FileNameParameter As SqlParameter
	Dim _FileIdParameter As SqlParameter
	Dim _FilePathParameter As SqlParameter

	Dim _SQLFileStream As SqlTypes.SqlFileStream
	Dim _Context As Byte()

	Try
		_Connection = New SqlConnection("Server=.\SQL2008;Integrated Authentication=SSPI;Database=FileSystemExample;")
		_Connection.Open()
		_Transaction = _Connection.BeginTransaction()
		_Command = New SqlCommand("xsp_InsertFile", _Connection, _Transaction)
		_Command.CommandType = CommandType.StoredProcedure
		_Command.Parameters.AddRange(New Object() {_FileIdParameter, _FileNameParameter, _FilePath})
		_FileId = Guid.NewGuid().ToString

		_FileIdParameter = New SqlParameter("@FileId", SqlDbType.UniqueIdentifier)
		_FileIdParameter.Value = _FileId

		_FileNameParameter = New SqlParameter("@FileName", SqlDbType.VarChar)
		_FileNameParameter.Value = fileName

		_FilePathParameter = New SqlParameter("@FilePath", SqlDbType.VarChar)
		_FilePathParameter.Direction = ParameterDirection.Output

		_DataReader = _Command.ExecuteReader(CommandBehavior.SingleRow)
		If _DataReader.HasRows Then
			_FilePath = _DataReader("FilePath").ToString
		End If
		If Not _DataReader.IsClosed Then
			_DataReader.Close()
		End If

		_Command = New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() FROM SQLFileSystem", _Connection, _Transaction)
		_Context = _Command.ExecuteScalar()

		_SQLFileStream = New SqlFileStream(_FilePath, _Context, FileAccess.Write)
		_SQLFileStream.Write(_Context, 0, _Context.Length)
		_SQLFileStream.Close()

		_Transaction.Commit()
	Catch ex As Exception
		If _Transaction IsNot Nothing Then
			_Transaction.Rollback()
		End If
	Finally
		If _Connection IsNot Nothing Then
			_Connection.Close()
		End If
	End Try
End Sub

Code for reading the File from the Database

Private Function ReadFile(ByVal fileId As String) As Byte()
	Dim _Transaction As SqlTransaction
	Dim _Command As SqlCommand
	Dim _DataReader As SqlDataReader
	Dim _Connection As SqlConnection
	Dim _FilePath As String
	Dim _FileIdParameter As SqlParameter

	Dim _SQLFileStream As SqlTypes.SqlFileStream
	Dim _Context As Byte()

	Try
		_Connection = New SqlConnection("Server=.\SQL2008;Integrated Authentication=SSPI;Database=FileSystemExample;")
		_Connection.Open()
		_Transaction = _Connection.BeginTransaction()
		_Command = New SqlCommand("SELECT FileStreamData.PathName() AS [FilePath],GET_FILESTREAM_TRANSACTION_CONTEXT() AS [Context] FROM SQLFileSystem WHERE FileId=@FileId", _Connection, _Transaction)
		_Command.CommandType = CommandType.StoredProcedure

		_FileIdParameter = New SqlParameter("@FileId", SqlDbType.UniqueIdentifier)
		_FileIdParameter.Value = fileId
		_Command.Parameters.Add(_FileIdParameter)

		_DataReader = _Command.ExecuteReader(CommandBehavior.SingleRow)
		If _DataReader.HasRows Then
			_FilePath = _DataReader("FilePath").ToString()
			_Context = TryCast(_DataReader("Context"), Byte())
		End If

		_SQLFileStream = New SqlFileStream(_FilePath, _Context, FileAccess.Read)
		_SQLFileStream.Read(_Context, 0, _Context.Length)
		_SQLFileStream.Close()

		_Transaction.Commit()
	Catch ex As Exception
		If _Transaction IsNot Nothing Then
			_Transaction.Rollback()
		End If
	Finally
		If _Connection IsNot Nothing Then
			_Connection.Close()
		End If
	End Try
	Return _Context
End Function

Stored Procedure

CREATE PROCEDURE xsp_InsertFile
(
@FileName VARCHAR(255), 
@FileId UNIQUEIDENTIFIER, 
@FilePath VARCHAR(MAX) OUTPUT)
AS
BEGIN
SET NOCOUNT OFF
INSERT INTO SQLFileSystem (FileId, FileName) VALUES(@FileId, @FileName)
SELECT @FilePath = SystemFile.PathName() from SQLFileSystem where FileId = @FileId
END

Note: I didn’t tested the code. Please let me know if you found any issues in the implementation.

Posted in .Net, .Net 3.0 / 3.5, ASP.Net, SQL Server, Visual Studio, Windows Forms | Tagged , , , , , | 1 Comment