How to Store Outlook Mails as such in sql server 2000

[angel1982]

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

[Killer42]

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.

[johngen]

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.geniusconne ct.com/

[moodz]

- generates database programmaticall y.
- 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.Collecti ons.Generic;
using System.Componen tModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows. Forms;

using Microsoft.Offic e.Interop.Outlo ok;

using System.Data.Sql Client;


namespace mailborg3
{
public partial class Form1 : Form
{

Microsoft.Offic e.Interop.Outlo ok.Application outlk = new Microsoft.Offic e.Interop.Outlo ok.ApplicationC lass( );
MailItem t;
string constr = "Data Source=.\\SQLEX PRESS;Initial Catalog=mailbor g3;Integrated Security=True";
SqlDataAdapter da = new SqlDataAdapter( "select * from mail", "Data Source=.\\SQLEX PRESS;Initial Catalog=mailbor g3;Integrated Security=True") ;
SqlDataAdapter da2 = new SqlDataAdapter( "select * from attachment", "Data Source=.\\SQLEX PRESS;Initial Catalog=mailbor g3;Integrated Security=True") ;
DataSet ds = new DataSet();
DataSet ds2 = new DataSet();

private MAPIFolder selectedFolder = null;

public Form1()
{
InitializeCompo nent();
}

private void button1_Click(o bject sender, EventArgs e)
{
getOutlook();
}
public void getOutlook()
{
SqlCommandBuild er cb = new SqlCommandBuild er(da);
da.UpdateComman d = cb.GetUpdateCom mand();
SqlCommandBuild er cb2 = new SqlCommandBuild er(da2);
da2.UpdateComma nd = cb2.GetUpdateCo mmand();
da.Fill(ds);
da2.Fill(ds2);
NameSpace NS = outlk.GetNamesp ace("MAPI");

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

//MAPIFolder inboxFld = NS.GetDefaultFo lder(OlDefaultF olders.olFolder Inbox );
//getFolderMail(i nboxFld, "Inbox");
//MAPIFolder junkFld = NS.GetDefaultFo lder(OlDefaultF olders.olFolder Junk) ;
//getFolderMail(j unkFld, "Junk");
//MAPIFolder sentFld = NS.GetDefaultFo lder(OlDefaultF olders.olFolder SentM ail);
//getFolderMail(s entFld, "Sent");
//MAPIFolder outboxFld = NS.GetDefaultFo lder(OlDefaultF olders.olFolder Outbo x);
//getFolderMail(o utboxFld, "Outbox");
//MAPIFolder draftFld = NS.GetDefaultFo lder(OlDefaultF olders.olFolder Draft s);
//getFolderMail(d raftFld, "Draft");
//MAPIFolder deleteFld = NS.GetDefaultFo lder(OlDefaultF olders.olFolder Delet edItems);
//getFolderMail(d eleteFld, "Delete");

}

public void getFolderMail(M APIFolder folder, string foldername)
{


int mailID = 0;
int nAttachCount = 0;

for (int i = 1; i <= folder.Items.Co unt; i++)
{
System.Windows. Forms.Applicati on.DoEvents();
try
{
DataRow dr = ds.Tables[0].NewRow();
t = (MailItem)folde r.Items[i];

int size = t.Size / 1000;
string sizeinK = size.ToString() + "K";
dr["folder"] = foldername;
dr["fromName"] = t.SenderName;
dr["fromID"] = t.SenderEmailAd dress;
dr["toName"] = t.ReceivedByNam e;
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.C ount;
dr["size"] = sizeinK;
dr["readStatus "] = t.UnRead;

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

textBox3.Text = Convert.ToStrin g(i); //update mail count
//System.Windows. Forms.Applicati on.DoEvents();

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

dra["mailID"] = mailID;

dra["Name"] = t.Attachments[j].DisplayName;
string filePath =Path.GetDirect oryName(System. Windows.Forms.A pplic ation.StartupPa th ) +t.Attachments[j].FileName; // @"G:/prabu"
t.Attachments[j].SaveAsFile(fil ePath);
FileStream fs = new FileStream(file Path, FileMode.Open, FileAccess.Read );
int length = (int)fs.Length;
byte[] content = new byte[length];
fs.Read(content , 0, length);
dra["contents"] = content;
dra["contentSiz e"] = length;

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

nAttachCount += 1;
textBox2.Text = Convert.ToStrin g(nAttachCount) ; //update attachment count
System.Windows. Forms.Applicati on.DoEvents();
}
}
}
catch (System .Exception ex)
{
Console.WriteLi ne(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;dat abase=MyDatabas e;Int egrated Security=SSPI") ;
SqlConnection thisConnection = new SqlConnection(" server=.\\SQLEX PRESS;Integrate d Security=True") ;
SqlCommand nonqueryCommand = thisConnection. CreateCommand() ;

try
{
thisConnection. Open();

nonqueryCommand .CommandText = "DROP DATABASE mailborg3";
Console.WriteLi ne(nonqueryComm and.CommandText );

nonqueryCommand .ExecuteNonQuer y();
Console.WriteLi ne("Existing DataBase Destroyed");

nonqueryCommand .CommandText = "CREATE DATABASE mailborg3";
Console.WriteLi ne(nonqueryComm and.CommandText );

nonqueryCommand .ExecuteNonQuer y();
Console.WriteLi ne("Database created, now switching");
thisConnection. ChangeDatabase( "mailborg3" );

nonqueryCommand .CommandText = "CREATE TABLE mail("
//+ "pindex INT PRIMARY KEY,"
//+ "mailID int REFERENCES attachment(mail ID) 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.WriteLi ne(nonqueryComm and.CommandText );
Console.WriteLi ne("Number of Rows Affected is: {0}", nonqueryCommand .ExecuteNonQuer y());
//+ " 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('Dupl icates found. Transaction rolled back.', 10, 1)"
+ " ROLLBACK TRAN"
+ " END";
//+ " GO";


Console.WriteLi ne(nonqueryComm and.CommandText );
Console.WriteLi ne("Number of Rows Affected is: {0}", nonqueryCommand .ExecuteNonQuer y());



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),"
+ "contentSiz e VARCHAR(MAX)"
+ ")";
Console.WriteLi ne(nonqueryComm and.CommandText );
Console.WriteLi ne("Number of Rows Affected is: {0}", nonqueryCommand .ExecuteNonQuer y());





//nonqueryCommand .CommandText = "INSERT INTO mailID VALUES (99)";
//Console.WriteLi ne(nonqueryComm and.CommandText );
//Console.WriteLi ne("Number of Rows Affected is: {0}", nonqueryCommand .ExecuteNonQuer y());

}
catch (SqlException ex)
{

Console.WriteLi ne(ex.ToString( ));

}
finally
{

thisConnection. Close();
Console.WriteLi ne("Connection Closed.");

}
}



private void Form1_Load(obje ct sender, EventArgs e)
{

}

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

private void button3_Click(o bject sender, EventArgs e)
{

SqlConnection thisConnection = new SqlConnection(" server=.\\SQLEX PRESS;Integrate d Security=True") ;
//create a new sql command object of type dynamic sql
SqlCommand cmd = thisConnection. CreateCommand() ;
cmd.CommandType = CommandType.Tex t;
//textBox1.Text = "";
dataGrid1.DataS ource = null;
dataGrid1.DataM ember = 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.SelectC ommand = cmd;

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

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

Console.WriteLi ne(ex.ToString( ));

}
catch (System.Excepti on ee)
{
Console.WriteLi ne(ee.ToString( ));
}

finally
{

thisConnection. Close();
Console.WriteLi ne("Connection Closed.");

}
}

private void textBox1_TextCh anged(object sender, EventArgs e)
{

}

private void textBox2_TextCh anged(object sender, EventArgs e)
{

}
}
}

[Killer42]

- generates database programmaticall y.
- 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?