473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to Store Outlook Mails as such in sql server 2000

I have this requirement where I have to store outlook mails into database and retrieve as necessary. The mail has to be retrieved back in outlook window itself, and not in any other format. The intention is to give him all the features available in the outlook.

So can someone please guide me as how to store the .msg file in the database and retrieve back? Am using VB 6.0 and SQL 2000
Jun 24 '07 #1
4 4819
Killer42
8,435 Expert 8TB
I have this requirement where I have to store outlook mails into database and retrieve as necessary. The mail has to be retrieved back in outlook window itself, and not in any other format. The intention is to give him all the features available in the outlook.

So can someone please guide me as how to store the .msg file in the database and retrieve back? Am using VB 6.0 and SQL 2000
I suppose one possibility would be to open the .msg file as binary, read it into a string or binary array, then store that into a "blob" in the database. Not sure how you'd go about displaying it later, but maybe you could write it to a temp .msg file and open that.

There are probably much better ways to go about it, such as storing it in an OLE field or something.
Jun 24 '07 #2
I have this requirement where I have to store outlook mails into database and retrieve as necessary. The mail has to be retrieved back in outlook window itself, and not in any other format. The intention is to give him all the features available in the outlook.

So can someone please guide me as how to store the .msg file in the database and retrieve back? Am using VB 6.0 and SQL 2000
This Add-on can store it to DB, not as .msg, but per Outlook Column:
http://www.geniusconnect.com/
Nov 16 '07 #3
moodz
2
- generates database programmatically.
- trigger procedure in SQL to prevent duplicate mail importing
- updates mail count and attachment count.
- reference key between mail and attachment table
- the database is called mailborg3 ... ;-)
- there is also a simple sql query facility.
eg
USE mailborg3;
SELECT * FROM mail;

- you need a form with an import button, query button, dbinitialise button,
text box ( for sql entries ), datagrid ( for query results ), mailcount textbox,
attachmentcount textbox.

- developed on SQLExpress and C# Express 2008. Thanks to Microsoft corp.
- tested on outlook 2003.

Enjoy.



using System;
using System.IO; //req for file operations
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using Microsoft.Office.Interop.Outlook;

using System.Data.SqlClient;


namespace mailborg3
{
public partial class Form1 : Form
{

Microsoft.Office.Interop.Outlook.Application outlk = new Microsoft.Office.Interop.Outlook.ApplicationClass( );
MailItem t;
string constr = "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True";
SqlDataAdapter da = new SqlDataAdapter("select * from mail", "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True");
SqlDataAdapter da2 = new SqlDataAdapter("select * from attachment", "Data Source=.\\SQLEXPRESS;Initial Catalog=mailborg3;Integrated Security=True");
DataSet ds = new DataSet();
DataSet ds2 = new DataSet();

private MAPIFolder selectedFolder = null;

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
getOutlook();
}
public void getOutlook()
{
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
SqlCommandBuilder cb2 = new SqlCommandBuilder(da2);
da2.UpdateCommand = cb2.GetUpdateCommand();
da.Fill(ds);
da2.Fill(ds2);
NameSpace NS = outlk.GetNamespace("MAPI");

selectedFolder = NS.PickFolder();
getFolderMail(selectedFolder, selectedFolder.Name);

//MAPIFolder inboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderInbox );
//getFolderMail(inboxFld, "Inbox");
//MAPIFolder junkFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderJunk) ;
//getFolderMail(junkFld, "Junk");
//MAPIFolder sentFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderSentM ail);
//getFolderMail(sentFld, "Sent");
//MAPIFolder outboxFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderOutbo x);
//getFolderMail(outboxFld, "Outbox");
//MAPIFolder draftFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDraft s);
//getFolderMail(draftFld, "Draft");
//MAPIFolder deleteFld = NS.GetDefaultFolder(OlDefaultFolders.olFolderDelet edItems);
//getFolderMail(deleteFld, "Delete");

}

public void getFolderMail(MAPIFolder folder, string foldername)
{


int mailID = 0;
int nAttachCount = 0;

for (int i = 1; i <= folder.Items.Count; i++)
{
System.Windows.Forms.Application.DoEvents();
try
{
DataRow dr = ds.Tables[0].NewRow();
t = (MailItem)folder.Items[i];

int size = t.Size / 1000;
string sizeinK = size.ToString() + "K";
dr["folder"] = foldername;
dr["fromName"] = t.SenderName;
dr["fromID"] = t.SenderEmailAddress;
dr["toName"] = t.ReceivedByName;
dr["toID"] = t.To;
dr["cc"] = t.CC;
dr["bcc"] = t.BCC;
dr["subject"] = t.Subject;
dr["body"] = t.Body;
dr["date"] = t.SentOn ;
dr["attachment"] = t.Attachments.Count;
dr["size"] = sizeinK;
dr["readStatus"] = t.UnRead;

ds.Tables [0].Rows.Add(dr);
da.Update(ds);

textBox3.Text = Convert.ToString(i); //update mail count
//System.Windows.Forms.Application.DoEvents();

if (t.Attachments.Count > 0)
{
mailID = getMailID();
for (int j = 1; j <= t.Attachments.Count; j++)
{
DataRow dra = ds2.Tables[0].NewRow();

dra["mailID"] = mailID;

dra["Name"] = t.Attachments[j].DisplayName;
string filePath =Path.GetDirectoryName(System.Windows.Forms.Applic ation.StartupPath ) +t.Attachments[j].FileName; // @"G:/prabu"
t.Attachments[j].SaveAsFile(filePath);
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
int length = (int)fs.Length;
byte[] content = new byte[length];
fs.Read(content, 0, length);
dra["contents"] = content;
dra["contentSize"] = length;

fs.Close();
FileInfo f = new FileInfo(filePath);
f.Delete();
ds2.Tables[0].Rows.Add(dra);
da2.Update(ds2);

nAttachCount += 1;
textBox2.Text = Convert.ToString(nAttachCount); //update attachment count
System.Windows.Forms.Application.DoEvents();
}
}
}
catch (System .Exception ex)
{
Console.WriteLine(ex.ToString());
}

}
}

public int getMailID()
{
int mailID = 0;
SqlDataAdapter da1 = new SqlDataAdapter("select max(mailID) as newMailID from mail", constr);
DataSet ds1 = new DataSet();
da1.Fill(ds1);
foreach (DataRow dr in ds1.Tables[0].Rows)
{
mailID = Convert.ToInt32(dr["newMailID"]);
}
return mailID;
}

static void initDB()
{
//SqlConnection thisConnection = new SqlConnection("server=(local)\\SQLEXPRESS;database =MyDatabase;Int egrated Security=SSPI");
SqlConnection thisConnection = new SqlConnection("server=.\\SQLEXPRESS;Integrated Security=True");
SqlCommand nonqueryCommand = thisConnection.CreateCommand();

try
{
thisConnection.Open();

nonqueryCommand.CommandText = "DROP DATABASE mailborg3";
Console.WriteLine(nonqueryCommand.CommandText);

nonqueryCommand.ExecuteNonQuery();
Console.WriteLine("Existing DataBase Destroyed");

nonqueryCommand.CommandText = "CREATE DATABASE mailborg3";
Console.WriteLine(nonqueryCommand.CommandText);

nonqueryCommand.ExecuteNonQuery();
Console.WriteLine("Database created, now switching");
thisConnection.ChangeDatabase("mailborg3");

nonqueryCommand.CommandText = "CREATE TABLE mail("
//+ "pindex INT PRIMARY KEY,"
//+ "mailID int REFERENCES attachment(mailID) PRIMARY KEY," // foreign key to attachment DB
+ "mailID INT IDENTITY(1,1) PRIMARY KEY,"
+ "folder VARCHAR(MAX),"
+ "fromName VARCHAR(MAX),"
+ "fromID VARCHAR(MAX),"
+ "toName VARCHAR(MAX),"
+ "toID VARCHAR(MAX),"
+ "cc VARCHAR(MAX),"
+ "bcc VARCHAR(MAX),"
+ "subject VARCHAR(MAX),"
+ "body VARCHAR(MAX),"
+ "date DATETIME,"
+ "attachment INT,"
+ "size VARCHAR(MAX),"
+ "readStatus BIT"
+ ")";
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
//+ " GO"
nonqueryCommand.CommandText = " CREATE TRIGGER trgDateTimeUNQ"
+ " ON mail FOR INSERT, UPDATE"
+ " AS"
+ " IF EXISTS(SELECT I.date"
+ " FROM inserted AS I JOIN mail AS C"
+ " ON I.date = C.date"
+ " WHERE I.date <> ''"
+ " GROUP BY I.date"
+ " HAVING COUNT(*) > 1)"
+ " BEGIN"
+ " RAISERROR('Duplicates found. Transaction rolled back.', 10, 1)"
+ " ROLLBACK TRAN"
+ " END";
//+ " GO";


Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());



nonqueryCommand.CommandText = "CREATE TABLE attachment("
+ "pindex INT IDENTITY(1,1) PRIMARY KEY,"
+ "mailID int REFERENCES mail(mailID)," // foreign key to mail DB
//+ "mailID INT PRIMARY KEY,"
+ "Name VARCHAR(MAX),"
+ "contents VARCHAR(MAX),"
+ "contentSize VARCHAR(MAX)"
+ ")";
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());





//nonqueryCommand.CommandText = "INSERT INTO mailID VALUES (99)";
//Console.WriteLine(nonqueryCommand.CommandText);
//Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());

}
catch (SqlException ex)
{

Console.WriteLine(ex.ToString());

}
finally
{

thisConnection.Close();
Console.WriteLine("Connection Closed.");

}
}



private void Form1_Load(object sender, EventArgs e)
{

}

private void button2_Click(object sender, EventArgs e)
{
initDB();
}

private void button3_Click(object sender, EventArgs e)
{

SqlConnection thisConnection = new SqlConnection("server=.\\SQLEXPRESS;Integrated Security=True");
//create a new sql command object of type dynamic sql
SqlCommand cmd = thisConnection.CreateCommand();
cmd.CommandType = CommandType.Text;
//textBox1.Text = "";
dataGrid1.DataSource = null;
dataGrid1.DataMember = null;

try
{
thisConnection.Open();

//set the sql command to whatever is in the textbox
cmd.CommandText = textBox1.Text;

//create a new data adapter and bind it to the command obejct
SqlDataAdapter daQuery = new SqlDataAdapter();
daQuery.SelectCommand = cmd;

//create a new dataset and fill it with results based adapter
DataSet dsQuery = new DataSet();
daQuery.Fill(dsQuery, "Results");

//bind the local variable dataset to the datagrid on the form
dataGrid1.DataSource = dsQuery;
dataGrid1.DataMember = "Results";
}
catch (SqlException ex)
{

Console.WriteLine(ex.ToString());

}
catch (System.Exception ee)
{
Console.WriteLine(ee.ToString());
}

finally
{

thisConnection.Close();
Console.WriteLine("Connection Closed.");

}
}

private void textBox1_TextChanged(object sender, EventArgs e)
{

}

private void textBox2_TextChanged(object sender, EventArgs e)
{

}
}
}
Nov 28 '07 #4
Killer42
8,435 Expert 8TB
- generates database programmatically.
- trigger procedure in SQL to prevent duplicate mail importing
...
Did you lose the start of your message or something? What did you intend to say?
Nov 28 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: kim | last post by:
Hi All, I had two Window NT 4.0 Server, say A and B... We recently upgraded from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for Computer B, I installed SQL Server 2000 from...
6
by: micmic.chion | last post by:
I am using Windows 2003 Server English Version. I wanna store the big-5 data so I install the sql server 2000 as if i install it in the Windows 2000 with Server Collation of the...
1
by: Martin van der Linden | last post by:
Hi, Can anybody tell me how to store a japanese unicode string from a rich textbox control in a database. I tried rs!Name = RichTextbox1.Text but this generates a bunch of questionmarks. There is...
8
by: Woody Splawn | last post by:
I am asking this question here because I asked this question in the Reporting Services Newsgroup and did not get an answer. Does anyone know if Reporting Services is intended to work in a...
4
by: Vince Varallo | last post by:
Hello, I do not have SQL Server 2005 installed on my computer and will not be able to based on company policy. However I do have SQL Server 2000. I want to create the application service...
2
by: aaryan | last post by:
hi all, i just want to know how to store photos in sql server 2000 and to retrieve it thro' vb
0
Kabyr
by: Kabyr | last post by:
Hi all, I am working on a project that reads fingerprints for identification. My problem is I want to save the print images in a table. How can i achieve this please? I taought saving them as...
3
by: abraham1 | last post by:
Hai, As I am doing project in a university , i need the coding of how to store image in sql server 2000 from asp.net written in vb language. Can anyone of U please help me....... I...
2
by: prakashsakthivel | last post by:
Hi Members, Is it possible to use Outlook 2003 from excel 2000. I am using Outlook 2003 and Excel 2000. While try to send mail automatically from excel 2000 (VBA), Application is giving...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.