SQL server 有个导入导出的功能,非常强大。名为DTS 。 细心的人早注意到了在使用导入导出的时候可以保存一个VB语言写的DTS包。 那就是DTS的源程序。以下的源程序是我参照生成的DTS包改成的C#导出数据。大家可以参照一下。改成导入导出的。
如何生成相应的文档如: Access ,Word , Excel ,FoxPro 请看在 .Net for Windows 的那遍
public class AppExportData
{
//DTS包对象
public DTS.Package2Class ObjDTS;
//源文件路径
private string strSourceFilePath;
//目标文件路径
private string strDestinationFilePath;
//文件类型
private AppExportData.DataFileType strFileType;
//导出数据的SQL语句
private string strSQL;
//目标表名
private string strTableName;
//得到目标列名
private ArrayList arrDestinationColumns;
//得到源数据列名
private ArrayList arrSourceColumns;
#region 私有属性
private ArrayList SourceColumns
{
get
{
return arrSourceColumns;
}
set
{
arrSourceColumns = value;
}
}
/// <summary>
/// 得到目标列名
/// </summary>
private ArrayList DestinationColumns
{
get
{
return arrDestinationColumns;
}
set
{
arrDestinationColumns = value;
}
}
#endregion
#region 公共属性
/// <summary>
/// 目标表名
/// </summary>
public string TableName
{
get
{
return strTableName;
}
set
{
strTableName = value;
}
}
/// <summary>
/// 导出数据的SQL语句
/// </summary>
public string SQL
{
get
{
return strSQL;
}
set
{
strSQL = value;
}
}
/// <summary>
/// 导出数据保存的文件类型
/// </summary>
public AppExportData.DataFileType FileType
{
get
{
return strFileType;
}
set
{
strFileType = value;
}
}
/// <summary>
/// 源文件所在的路径
/// </summary>
public string SourceFilePath
{
get
{
return strSourceFilePath;
}
set
{
strSourceFilePath = value;
}
}
/// <summary>
/// 目标文件所在的路径
/// </summary>
public string DestinationFilePath
{
get
{
return strDestinationFilePath;
}
set
{
strDestinationFilePath = value;
}
}
#endregion
public AppExportData()
{
strTableName = "结果";
arrDestinationColumns = new ArrayList();
arrSourceColumns = new ArrayList();
//
// TODO: 在此处添加构造函数逻辑
//
}
#region 导出的全过程
public bool ExportData()
{
try
{
ObjDTS = new DTS.Package2Class();
if ( this.arrDestinationColumns.Count == 0 || this.arrSourceColumns.Count == 0)
{
if ( !this.GetColumns() )
{
System.Windows.Forms.MessageBox.Show( "没有获得数据,导出文件失败!","提示信息" );
return false;
}
}
//新建一个新的DTS包,设置它的属性
ObjDTS.Name = "新建包";
ObjDTS.Description = "DTS 包描述";
ObjDTS.WriteCompletionStatusToNTEventLog = false;
ObjDTS.FailOnError = false;
ObjDTS.PackagePriorityClass = ( DTS.DTSPackagePriorityClass )2;
ObjDTS.MaxConcurrentSteps = 4;
ObjDTS.LineageOptions = 0;
ObjDTS.UseTransaction = true;
ObjDTS.TransactionIsolationLevel = ( DTS.DTSIsolationLevel )4096;
ObjDTS.AutoCommitTransaction = true;
ObjDTS.RepositoryMetadataOptions = 0;
ObjDTS.UseOLEDBServiceComponents = true;
ObjDTS.LogToSQLServer = false;
ObjDTS.LogServerFlags = 0;
ObjDTS.FailPackageOnLogFailure = false;
ObjDTS.ExplicitGlobalVariables = false;
ObjDTS.PackageType = 0;
//建立SQL的连接,设置其属性
DTS.Connection2 oConnection;
oConnection = ( DTS.Connection2 )ObjDTS.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.ConnectionProperties.Item("Data Source").Value = this.strSourceFilePath;
oConnection.ConnectionProperties.Item("Mode").Value = 1;
oConnection.Name = "连接1";
oConnection.ID = 1;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.DataSource = this.strSourceFilePath ;
oConnection.ConnectionTimeout = 60;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
ObjDTS.Connections.Add( ( DTS.Connection )oConnection );
oConnection = null;
//建立导出数据库的连接,设置其属性
oConnection = ( DTS.Connection2 )ObjDTS.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.ConnectionProperties.Item("Data Source").Value = this.strDestinationFilePath;
switch ( this.strFileType )
{
case DataFileType.Access:
oConnection.ConnectionProperties.Item("Mode").Value = 3;
break;
case DataFileType.Excel:
oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;";
break;
default:
oConnection.ConnectionProperties.Item("Extended Properties").Value = "dBase 5.0";
//还要把列名的长度,在foxpro中列名最长只能是10个英文字母也就是5个汉字
for ( int i = 0 ; i < this.arrDestinationColumns.Count; i ++ )
{
if ( WyScore.AID.AppAssert.CheckString( this.arrDestinationColumns[i].ToString(),8 ) )
{
if ( this.arrDestinationColumns[i].ToString().Length > 5 )
{
this.arrDestinationColumns[i] = this.arrDestinationColumns[i].ToString().Substring( 0,5 );
}
}
}
break;
}
oConnection.Name = "连接2";
oConnection.ID = 2;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.DataSource = this.strDestinationFilePath;
oConnection.ConnectionTimeout = 60;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
//将其加入DTS包中
ObjDTS.Connections.Add( ( DTS.Connection )oConnection );
oConnection = null;
//设置DTS执行的步骤
DTS.Step2 oStep;
DTS.PrecedenceConstraint oPrecConstraint;
oStep = ( DTS.Step2 ) ObjDTS.Steps.New();
//创建表,设置属性
oStep.Name = "创建表 " + this.strTableName + " 步骤";
oStep.Description = "创建表 "+ this.strTableName + " 步骤";
oStep.ExecutionStatus = ( DTS.DTSStepExecStatus )1;
oStep.TaskName = "创建表 " + this.strTableName + " 任务";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.RelativePriority = ( DTS.DTSStepRelativePriority )3;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = false;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = false;
//将其加入DTS包中
ObjDTS.Steps.Add( oStep );
oStep = null;
//设置数据导出的步骤 , 设置性属
oStep = ( DTS.Step2 )ObjDTS.Steps.New();
oStep.Name = "Copy Data from 结果 to " + this.strTableName + " 步骤";
oStep.Description = "Copy Data from 结果 to " + this.strTableName + " 步骤";
oStep.ExecutionStatus = ( DTS.DTSStepExecStatus )1;
oStep.TaskName = "Copy Data from 结果 to " + this.strTableName + " 任务";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.RelativePriority = ( DTS.DTSStepRelativePriority )3;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = false;
//将其添加到DTS包中
ObjDTS.Steps.Add( oStep );
oStep = null;
//设置生成表的步骤
oStep = ( DTS.Step2 )ObjDTS.Steps.Item("Copy Data from 结果 to " + this.strTableName + " 步骤");
oPrecConstraint = oStep.PrecedenceConstraints.New("创建表 " + this.strTableName + " 步骤");
oPrecConstraint.StepName = "创建表 " + this.strTableName + " 步骤";
oPrecConstraint.PrecedenceBasis = 0;
oPrecConstraint.Value = 4;
oStep.PrecedenceConstraints.Add( oPrecConstraint );
oPrecConstraint = null;
this.Task_Sub1( ObjDTS );
this.Task_Sub2( ObjDTS );
//执行导出数据
ObjDTS.Execute();
tracePackageError( ObjDTS );
return true;
}
catch
{
return false;
}
finally
{
ObjDTS.UnInitialize();
System.Runtime.InteropServices.Marshal.ReleaseComObject( ObjDTS );
ObjDTS = null;
GC.Collect();
this.arrDestinationColumns.Clear();
this.arrSourceColumns.Clear();
}
}
/// <summary>
/// 导出到Word文件
/// </summary>
/// <param name="Table">源数据集</param>
/// <param name="label">标签</param>
public bool ExportWord( DataTable Table , System.Windows.Forms.Label label )
{
object Missing = System.Reflection.Missing.Value;
int NumRows, NumColumns, rowIndex, colIndex;
//保存word文件的路径
object FileName = this.strDestinationFilePath;
Word.ApplicationClass wordApp = new Word.ApplicationClass();
Word.Document myDoc = null;
Word.Table oTable;
rowIndex = 1;
colIndex = 0;
try
{
wordApp.Documents.Add(ref Missing,ref Missing,ref Missing, ref Missing);
myDoc = wordApp.ActiveDocument;
oTable = myDoc.Tables.Add(myDoc.Range(ref Missing, ref Missing),NumRows=Table.Rows.Count + 1, NumColumns=Table.Columns.Count,ref Missing,ref Missing);
//将所得到的表的列名,赋值给单元格
foreach(DataColumn Col in Table.Columns)
{
colIndex = colIndex + 1;
oTable.Cell(1, colIndex).Range.InsertAfter(Col.ColumnName);
}
//得到的表所有行,赋值给单元格
foreach(DataRow Row in Table.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
foreach(DataColumn Col in Table.Columns)
{
colIndex = colIndex + 1;
oTable.Cell(rowIndex, colIndex).Range.InsertAfter(Row[Col.ColumnName].ToString());
}
label.Text = "正在导出数据," + (rowIndex - 1).ToString() + "/" + Table.Rows.Count.ToString();
System.Windows.Forms.Application.DoEvents();
}
oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleDashDot;
oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDot;
//true:word文件显示 false:word文件不显示
wordApp.Visible = false;
myDoc.SaveAs2000(ref FileName,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing );
return true;
}
catch
{
return false;
}
finally
{
myDoc.Close( ref Missing,ref Missing,ref Missing );
System.Runtime.InteropServices.Marshal.ReleaseComObject( myDoc );
myDoc = null;
GC.Collect();
wordApp.Quit( ref Missing,ref Missing, ref Missing );
System.Runtime.InteropServices.Marshal.ReleaseComObject( wordApp );
wordApp = null;
GC.Collect();
}
}
/// <summary>
/// 异常处理
/// </summary>
/// <param name="oPackage">DTS包对象</param>
private void tracePackageError( DTS.Package2Class oPackage)
{
int ErrorCode;
string ErrorSource;
string ErrorDescription;
string ErrorHelpFile;
int ErrorHelpContext;
string ErrorIDofInterfaceWithError;
for( int i = 1 ; i < oPackage.Steps.Count ; i ++ )
{
if ( oPackage.Steps.Item(i).ExecutionResult == DTS.DTSStepExecResult.DTSStepExecResult_Failure )
{
oPackage.Steps.Item(i).GetExecutionErrorInfo(out ErrorCode,out ErrorSource,out ErrorDescription,out ErrorHelpFile,out ErrorHelpContext,out ErrorIDofInterfaceWithError);
}
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="goPackage">DTS包对象</param>
private void Task_Sub1( DTS.Package2Class ObjDTS )
{
DTS.Task oTask;
DTS.ExecuteSQLTask2 oCustomTask1;
oTask = ObjDTS.Tasks.New("DTSExecuteSQLTask");
oTask.Name = "创建表 " + this.strTableName + " 任务";
oCustomTask1 = ( DTS.ExecuteSQLTask2 )oTask.CustomTask;
oCustomTask1.Name = "创建表 " + this.strTableName + " 任务";
oCustomTask1.Description = "创建表 " + this.strTableName + " 任务";
oCustomTask1.SQLStatement = "CREATE TABLE `" + this.strTableName + "` (" + "\n";
for( int i = 0 ; i < this.arrDestinationColumns.Count ; i ++ )
{
if ( i == this.arrDestinationColumns.Count - 1 )
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + "[" + this.arrDestinationColumns[i].ToString() +"] VarChar(50) " + "\n";
else
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + "[" + this.arrDestinationColumns[i].ToString() +"] VarChar(50), " + "\n";
}
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + ")";
oCustomTask1.ConnectionID = 2;
oCustomTask1.CommandTimeout = 0;
oCustomTask1.OutputAsRecordset = false;
ObjDTS.Tasks.Add( oTask );
oCustomTask1 = null;
oTask = null;
}
/// <summary>
/// 执行导出的SQL语句
/// </summary>
/// <param name="ObjDTS">DTS包对象</param>
private void Task_Sub2( DTS.Package2Class ObjDTS )
{
DTS.Task oTask;
DTS.DataPumpTask2 oCustomTask2;
oTask = ObjDTS.Tasks.New("DTSDataPumpTask");
oTask.Name = "Copy Data from 结果 to " + this.strTableName + " 任务";
oCustomTask2 = (DTS.DataPumpTask2 )oTask.CustomTask;
oCustomTask2.Name = "Copy Data from 结果 to " + this.strTableName + " 任务";
oCustomTask2.Description = "Copy Data from 结果 to " + this.strTableName + " 任务";
oCustomTask2.SourceConnectionID = 1;
oCustomTask2.SourceSQLStatement = this.strSQL;
oCustomTask2.DestinationConnectionID = 2;
if ( this.strFileType == AppExportData.DataFileType.Excel )
oCustomTask2.DestinationObjectName = this.strTableName;
else
oCustomTask2.DestinationObjectName = this.strTableName;
oCustomTask2.ProgressRowCount = 1000;
oCustomTask2.MaximumErrorCount = 0;
oCustomTask2.FetchBufferSize = 1;
oCustomTask2.UseFastLoad = true;
oCustomTask2.InsertCommitSize = 0;
oCustomTask2.ExceptionFileColumnDelimiter = "|";
oCustomTask2.ExceptionFileRowDelimiter = @"\n";
oCustomTask2.AllowIdentityInserts = false;
oCustomTask2.FirstRow = 0;
oCustomTask2.LastRow = 0;
oCustomTask2.FastLoadOptions = ( DTS.DTSFastLoadOptions )2;
oCustomTask2.ExceptionFileOptions = ( DTS.DTSExceptionFileOptions )1;
oCustomTask2.DataPumpOptions = 0;
this.oCustomTask2_Trans_Sub1( oCustomTask2 );
ObjDTS.Tasks.Add( oTask );
oCustomTask2 = null;
oTask = null;
}
/// <summary>
/// 设置导出的源列和目标列的对应关系
/// </summary>
/// <param name="oCustomTask2"></param>
private void oCustomTask2_Trans_Sub1( DTS.DataPumpTask2 oCustomTask2 )
{
DTS.Transformation2 oTransformation;
DTS.Properties oTransProps;
DTS.Column oColumn;
oTransformation = ( DTS.Transformation2 )oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy");
oTransformation.Name = "DirectCopyXform";
oTransformation.TransformFlags = 63;
oTransformation.ForceSourceBlobsBuffered = ( DTS.DTSForceMode )1;
oTransformation.ForceBlobsInMemory = false;
oTransformation.InMemoryBlobSize = 1048576;
oTransformation.TransformPhases = 4;
for( int i = 0 ;i< this.arrSourceColumns.Count ; i++ )
{
oColumn = oTransformation.SourceColumns.New( this.arrSourceColumns[i].ToString(),i + 1 );
oColumn.Name = this.arrSourceColumns[i].ToString();
oColumn.Ordinal = i + 1;
oColumn.Flags = 8;
oColumn.Size = 50;
oColumn.DataType = 130;
oColumn.Precision = 0;
oColumn.NumericScale = 0;
oColumn.Nullable = false;
oTransformation.SourceColumns.Add( oColumn );
oColumn = null;
}
for( int j = 0 ; j < this.arrDestinationColumns.Count; j++ )
{
oColumn = oTransformation.DestinationColumns.New( this.arrDestinationColumns[j].ToString(),j + 1 );
oColumn.Name = this.arrDestinationColumns[j].ToString();
oColumn.Ordinal = j + 1;
oColumn.Flags = 8;
oColumn.Size = 50 ;
oColumn.DataType = 130;
oColumn.Precision = 0;
oColumn.NumericScale = 0;
oColumn.Nullable = false;
oTransformation.DestinationColumns.Add( oColumn );
oColumn = null;
}
oTransProps = oTransformation.TransformServerProperties;
oTransProps = null;
oCustomTask2.Transformations.Add( oTransformation );
oTransformation = null;
}
#endregion
#region 从SQL语句中提出源列名和目标列名,这是创建表和设置对应关系必须要的
public enum DataFileType
{
/// <summary>
/// Excel类型数据导入
/// </summary>
Excel = 1,
/// <summary>
/// Access类型数据导入
/// </summary>
Access = 2,
/// <summary>
/// FoxPro类型数据导入
/// </summary>
FoxPro = 3,
}
/// <summary>
/// 在SQL语句中得到目标表的列名 , 本来是想根据SQL语句来决定列名,没有找到高效的分解SQL语句的方法。如果大家有什么好方法。请告诉我,谢谢
/// </summary>
private bool GetColumns()
{
try
{
OleDbConnection con = new OleDbConnection(AppConfig.ConnString);
OleDbDataAdapter da = new OleDbDataAdapter( this.strSQL,con);
DataSet ds = new DataSet();
da.Fill(ds,"temp");
for ( int i = 0; i < ds.Tables["temp"].Columns.Count; i++ )
{
this.arrDestinationColumns.Add( ds.Tables["temp"].Columns[i].ColumnName );
this.arrSourceColumns.Add( ds.Tables["temp"].Columns[i].ColumnName );
}
return true;
}
catch
{
return false;
}
}
#endregion
}