none
odbc实现导入导出的统一接口 RRS feed

  • 常规讨论

  • 小弟刚刚接触c#两个月,最近做项目中的配置导入导出功能,研究了几天,找了很多资料写了一个ODBC的接口,贴出来希望大家多给写意见。

    namespace BaseDatabase
    {
      public class ExportAndImport
      {
        private static string FileNew = null;      //文件名
        private static string PathNew = null;      //文件路径
        private static byte FileType = 0;        //导出文件类型 1-xls 2-xlsb 3-txt或csv
        private static string newtablename;      //创建新表名称
        private static string strConnString;      //连接字符串
    
        private static bool FileCheck(string filename)
        {
          if ((filename == "") || (filename == null) || (filename == "") || (filename == null) || (filename == "") || (filename == null))
          {
            return false;
          }
          //获得文件扩展名
          string temp = System.IO.Path.GetExtension(filename);
    
          if (temp == ".xls" || temp == ".txt" || temp == ".csv" || temp == ".xlsb")
          {
            FileNew = System.IO.Path.GetFileName(filename);
            PathNew = System.IO.Path.GetDirectoryName(filename);
            if (temp == ".xls")
              FileType = 1;
            else if (temp == ".xlsb")
              FileType = 3;
            else
              FileType = 2;
            return true;
          }
          return false;
        }
    
        private static void CreateConnString(string tablename)
        {
          if (FileType == 1)
          {
            strConnString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" + PathNew + "\\"
              + FileNew + "; DriverID=790;;";//FIL=Excel 12.0;";//CREATE_DB=" + PathNew + "\\" + FileNew;
            newtablename = tablename;
          }
          else if (FileType == 3)
          {
            strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" + 
              "DriverID=1046;FIRSTROWHASNAMES=1;Readonly=False;DBQ=" 
              + PathNew + "\\" + FileNew;
            newtablename = tablename;
          }
          else
          {
            strConnString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + PathNew
              + ";Extensions=asc,csv,tab,txt;Persist Security Info=False;;";
            newtablename = "[" + FileNew + "]";
          }
        }
    
        /*
        public static bool SqlExportFile(string filename, string tablename)
        Description:把数据库中单个表导出,
        Input:filename 预导出文件名(含路径)
          tablename 数据库中的表明。
        Return:
        false 失败
        ture 成功
        */
        public static bool SqlExportFile(string filename, string tablename)
        {
          try
          {
            if (FileCheck(filename))
            {
              if (File.Exists(filename))
                File.Delete(filename);
              if (File.Exists(PathNew + "\\schema.ini"))		//如果细化的话可以把schema.ini中的指定文件配置删除掉,否则可能会影响其它文件
                File.Delete(PathNew + "\\schema.ini");
              CreateConnString(tablename);
    
              using (OdbcConnection conn = new OdbcConnection(strConnString.Trim()))
              {
                string s = "";
                string f = "";
                conn.Open();
    				
                Stopwatch sw = new Stopwatch();			//测试运行效率
                sw.Start();
    
                using (DataSet srcData = BaseDbManager.GetDataSet("SELECT * FROM " + tablename))
                {
                  if (srcData == null)
                  {
                    BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), "No export data");
                    return false;
                  }
                  //创建表
                  DataTable srcTable = srcData.Tables[0];
                  for (int i = 0; i < srcTable.Columns.Count; i++)
                  {
                    //if (numlx.Contains(srcTable.Columns[i].DataType.ToString()))
                    //{
                    //  s = s + ",[" + srcTable.Columns[i].ColumnName + "] NUMBER";
                    //}
                    //else
                    //{
                    s = s + ",[" + srcTable.Columns[i].ColumnName + "] TEXT";
                    //}
                    f = f + ",[" + srcTable.Columns[i].ColumnName + "]";
                  }
    
                  s = @"CREATE TABLE " + newtablename + " (" + s.Substring(1) + ")";
    
                  OdbcCommand cmd1 = new OdbcCommand(s, conn);
                  cmd1.ExecuteNonQuery();
                  //插入数据
                  f = "insert into " + newtablename + "(" + f.Substring(1) + ") values(";
                  for (int j = 0; j < srcTable.Rows.Count; j++)
                  {
                    s = "";
                    for (int i = 0; i < srcTable.Columns.Count; i++)
                    {
                      if (srcTable.Rows[j][i] != DBNull.Value)
                      {
                        if (i == 0)
                          s = s + "'" + srcTable.Rows[j][i].ToString() + "'";
                        else
                          s = s + ",'" + srcTable.Rows[j][i].ToString() + "'";
                      }
                      else
                      {
                        if (i == 0)
                          s = s + "''";
                        else
                          s = s + ",''";
                      }
                    }
                    cmd1.CommandText = f + s + ")";
                    cmd1.ExecuteNonQuery();
                  }
                }
                sw.Stop();
                Console.WriteLine("Stopwatch 时间精度:{0}ms", sw.ElapsedMilliseconds);
              }
            }
            else
            {
              BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), "File format error");
              return false;
            }
          }
          catch (Exception ex)
          {
            BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), ex.Message);
            return false;
          }
          return true;
        }
        /*
        public static bool FileImportSql(string filename, string tablename)
        Description:把文件数据导入到数据库的表中
        Input:filename 预导入文件名(含路径)
          tablename 数据库中的表明。
        Return:
        false 失败
        ture 成功
        */
        public static bool FileImportSql(string filename, string tablename)
        {
          string sql_select;
          try
          {
            if (FileCheck(filename))
            {
              CreateConnString(tablename);
    
              OdbcConnection conn = new OdbcConnection(strConnString.Trim());
              conn.Open();
              if (FileType == 2)
                sql_select = "select * from [" + FileNew + "]";
              else
                sql_select = "select * from [" + tablename + "$]";
    
              Stopwatch sw = new Stopwatch();
              sw.Start();
              OdbcCommand commandSourceData = new OdbcCommand(sql_select, conn);
    
              using (OdbcDataReader dataReader = commandSourceData.ExecuteReader())
              {
                SqlConnection destinationConnection = (SqlConnection)(BaseDbManager.ReturnOpen());
    
                using (SqlTransaction transaction = destinationConnection.BeginTransaction())
                {
                  SqlCommand dbCommand = new SqlCommand("DELETE FROM " + tablename, destinationConnection);
                  dbCommand.Transaction = transaction;
                  try
                  {
                    int rows = dbCommand.ExecuteNonQuery();
                  }
                  catch (Exception ex)
                  {
                    Console.WriteLine(ex.Message);
                    transaction.Rollback();
                    BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), ex.Message);
                    return false;
                  }
    
                  using (SqlBulkCopy bc = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction))
                  {
                    bc.DestinationTableName = tablename;
    
                    try
                    {
                      bc.WriteToServer(dataReader);
                      transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                      BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), ex.Message);
                      transaction.Rollback();
                      return false;
                    }
                  }
                }
              }
              sw.Stop();
              Console.WriteLine("Stopwatch 时间精度:{0}ms", sw.ElapsedMilliseconds);
            }
            else
            {
              BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), "File format error");
              return false;
            }
          }
          catch (Exception ex)
          {
            BaseEvent.BaseEvent.AddMsg(EventTypes.BF6500ImExportError.ToString(), ex.Message);
            return false;
          }
          return true;
        }

          这里的BaseDbManager类是项目中的一个数据库操作类接口,BaseEvent是一个事件处理接口。项目只要求导出txt、csv、execl,并没有提供其它接口。

    问题1、对于strConnString的赋值,ODBC对于每种数据库的链接都有特定的链接关键字,网上中英文资料都很少,这几天做下来主要的问题都卡在这里,如果想查看这方面的资料到哪里能找到啊?

    问题2、用ODBC链接EXECL2007,我只能导出的文件xlsb文件,怎么能导出2007的xls?

    问题3、用ODBC操作TXT或CSV类型的文件速度奇慢,程序中有运行时间的打印,和文件流差几十倍,呵呵,但我们只对配置文件作操作,速度可以接受,而且实现了接口统一,我想问的是谁知道为什么会这么慢?

    希望大家看过后给我这个新人能多提意见。

    2010年10月7日 2:40