Hi Saravana,
I found solution for this, add below code in your solution and modify code accoding to your Dataset. call this function and pass parameters of this
strDirectory = your .MDB Savingpath
dtt=your Dataset
private void Exportmdb(string strDirectory, System.Data.DataSet dtt)
{
try
{
Catalog cat = new Catalog();
UpdateProgress("");
string str = "provider=Microsoft.Jet.OleDb.4.0;Data Source=" + strDirectory;
cat.Create(str);
cat = null;
OleDbConnection sceConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strDirectory);
sceConnection.Open();
foreach (System.Data.DataTable dttemp in dtt.Tables)
{
string tableName = dttemp.TableName;
StringBuilder stbSqlGetHeaders = new StringBuilder();
stbSqlGetHeaders.Append("create table " + tableName + " (");
int z = 0;
StringBuilder stbSqlQuery = new StringBuilder();
StringBuilder stbFields = new StringBuilder();
StringBuilder stbParameters = new StringBuilder();
foreach (DataColumn col in dttemp.Columns)
{
string datatyp = col.DataType.Name.ToString().Trim().ToLower();
if (z != 0) stbSqlGetHeaders.Append(", "); ;
String strName = col.ColumnName;
String strType = col.DataType.Name.ToString().Trim().ToLower();
if (strType.Equals("")) throw new ArgumentException("DataType Empty");
if (strType.Equals("int32")) strType = "Number";
if (strType.Equals("int64")) strType = "Number";
if (strType.Equals("int16")) strType = "Number";
if (strType.Equals("float")) strType = "float";
if (strType.Equals("double")) strType = "Double";
if (strType.Equals("decimal")) strType = "Double";
if (strType.Equals("string")) strType = "memo";
if (strType.Equals("boolean")) strType = "Bit";
if (strType.Equals("datetime")) strType = "datetime";
if (strType.Equals("byte[]")) strType = "Image";
stbSqlGetHeaders.Append("[" + strName + "] " + strType);
z++;
stbFields.Append("[" + col.ColumnName + "]");
stbParameters.Append("@" + col.ColumnName.ToLower());
if (col.ColumnName != dttemp.Columns[dttemp.Columns.Count - 1].ColumnName)
{
stbFields.Append(", ");
stbParameters.Append(", ");
}
}
stbSqlGetHeaders.Append(")");
OleDbCommand sceCreateTableCommand;
string strCreateTableQuery = stbSqlGetHeaders.ToString();
sceCreateTableCommand = new OleDbCommand(strCreateTableQuery, sceConnection);
sceCreateTableCommand.ExecuteNonQuery();
stbSqlQuery.Append("insert into " + tableName + " (");
OleDbCommand comm = new OleDbCommand();
stbSqlQuery.Append(stbFields.ToString() + ") ");
stbSqlQuery.Append("values (");
stbSqlQuery.Append(stbParameters.ToString() + ") ");
string strTotalRows = dttemp.Rows.Count.ToString();
foreach (DataRow row in dttemp.Rows)
{
OleDbCommand sceInsertCommand = new OleDbCommand(stbSqlQuery.ToString(), sceConnection);
foreach (DataColumn col in dttemp.Columns)
{
string colnameparam = col.ColumnName;
string colparam = col.ColumnName.ToLower();
string datatyp1 = col.DataType.Name.ToString().Trim().ToLower();
if (datatyp1.Substring(0, 3) == "str")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarWChar).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarWChar).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "dat")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Date).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Date).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "byt")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarBinary).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.LongVarBinary).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "int")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.BigInt).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.BigInt).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "boo")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Boolean).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Boolean).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "flo")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "dou")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Double).Value = DBNull.Value;
}
}
else if (datatyp1.Substring(0, 3) == "dec")
{
if (row[colnameparam].ToString() != "")
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Decimal).Value = row[colnameparam];
}
else
{
sceInsertCommand.Parameters.Add("@" + colparam.Trim(), OleDbType.Decimal).Value = DBNull.Value;
}
}
}
sceInsertCommand.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Export Data", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
}
}
Updated 15-Jul-13 20:11pm
v3