Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Windows Application
Hi Friends,
 
I am using windows application with c sharp coding, I have a export option to generate .mdb file. I have a Dataset which contains minimum of 20 tables and each table will have more than 100 rows and minimum of 15 columns. if i use "FOR loop " for exporting row wise or column wise means it will take long time to process , so is there a any quick process to export data from Dataset to .mdb ?
Posted 27-May-13 5:14am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
SARAVANAKUMAR.M at 28-May-13 3:08am
   
Thanks for your reply Mehdi,
 
I was trying this concept only but i am getting the error while inserting the data for the Image field column of a table as "syntax error (missing operator) in query expression 'system.Byte[] '".
 
While executing the command i found this error on the line , "INSERT INTO CaseID1FileID18TableID43Tracklogs ([Track], [RecID], [Latitude], [Longitude], [DateTime], [Elevation(ft)], [Elevation(m)], [S_No], [Images], [ELatitude], [ELongitude]) VALUES ('TRK-0022_ACTIVE LOG: 31 OCT 2011 10:53', '4535', '26.099017', '-80.136197', '31-Oct-2011 14:53:58', '104.5275591', '31.86', 1, System.Byte[], '26.099017', '-80.136197')" . Here the System.Byte[] is nothing but the Empty Array , i.e, byte[] ImgByte[]={}. So can u suggest me to store NULL value for Image field in .mdb file ?
Mehdi Gholam at 28-May-13 9:10am
   
Just put null instead of the System.Byte[] in your query.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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);
}

}
  Permalink  
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 16 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100