public static void SaveToFile(DataTable ntable, string filename, DataProgressEvent eventprogress)
{
string folder = Path.GetDirectoryName(filename);
DbConnection connection = CreateConnection(folder);
connection.Open();
try
{
SaveToFile(ntable, filename, connection, 4, eventprogress);
}
finally
{
connection.Close();
}
}
public static void SaveToFile(DataTable ntable, string filename, DbConnection connection, int level, DataProgressEvent eventprogress)
{
foreach (DataColumn ncol in ntable.Columns)
{
if (ncol.ColumnName.Length > 8)
{
throw new Exception("Column name exceeding 8 characters, rename the column before exporting: Column name=" + ncol.ColumnName);
}
}
DbProviderFactory nfac = DbProviderFactories.GetFactory("System.Data.OleDb");
File.Delete(filename);
filename = Path.GetFileName(filename);
StringBuilder nbuilder = new StringBuilder();
nbuilder.Append("CREATE TABLE ");
nbuilder.Append(filename);
nbuilder.Append(" (");
for (int i = 0; i < ntable.Columns.Count; i++)
{
DataColumn ncol = ntable.Columns[i];
if (i > 0)
{
nbuilder.Append(",");
}
nbuilder.Append(ncol.ColumnName);
nbuilder.Append(" ");
nbuilder.Append(TypeToDBFSqlType(ncol.DataType, ncol.MaxLength));
}
nbuilder.Append(")");
using (DbCommand ncommand = connection.CreateCommand())
{
ncommand.CommandText = nbuilder.ToString();
ncommand.ExecuteNonQuery();
nbuilder = new StringBuilder();
StringBuilder nfields = new StringBuilder(" (");
StringBuilder nvalues = new StringBuilder(" (");
nbuilder.Append("INSERT INTO ");
nbuilder.Append(filename);
for (int i = 0; i < ntable.Columns.Count; i++)
{
if (i > 0)
{
nfields.Append(",");
nvalues.Append(",");
}
DataColumn ncol = ntable.Columns[i];
nfields.Append(ncol.ColumnName);
nvalues.Append("@");
nvalues.Append(ncol.ColumnName);
DbParameter nparam = nfac.CreateParameter();
nparam.ParameterName = "@" + ncol.ColumnName;
nparam.DbType = TypeToDBFDbType(ncol.DataType, 4);
ncommand.Parameters.Add(nparam);
}
nfields.Append(")");
nvalues.Append(")");
ncommand.CommandText = nbuilder.ToString() + nfields.ToString() + " VALUES " + nvalues.ToString();
DateTime mmfirst = DateTime.Now;
int ncount = 0;
bool docancel = false;
using (DataView nview = new DataView(ntable, "", "", DataViewRowState.CurrentRows))
{
foreach (DataRowView xrv in nview)
{
DataRow xrow = xrv.Row;
for (int i = 0; i < ntable.Columns.Count; i++)
{
ncommand.Parameters[i].Value = xrow[i];
}
ncommand.ExecuteNonQuery();
ncount++;
if ((ncount % 10) == 0)
{
DateTime mmlast = DateTime.Now;
if ((mmlast - mmfirst).TotalMilliseconds > 500)
{
if (eventprogress != null)
{
eventprogress(null, ncount, ntable.Rows.Count, ref docancel);
if (docancel)
{
throw new Exception("Operation cancelled");
}
}
mmfirst = DateTime.Now;
}
}
}
}
}
}
