TeraData数据库，将Excel文件存入数据库并取出打开

 

TeraData数据库 字段类型 ：BLOB

错误提示：LOBs are not allowed to be selected in Record or Indicator modes.

控制面板——管理工具——ODBC配置：Options

 

 

将Excel文件存入： 

保存数据 

 1             try
 2             {
 3                 OpenFileDialog open = new OpenFileDialog();//创建OpenFileDialog实例
 4                 open.Filter = "Excel文件|*.xls";
 5                 open.Multiselect = false;                  //每次只能选择一个文件
 6                 if (open.ShowDialog() == DialogResult.OK)
 7                 {
 8                     System.IO.FileInfo fi = new System.IO.FileInfo(open.FileName);
 9                     System.IO.FileStream fs = fi.OpenRead();
10                     byte[] bytes = new byte[fs.Length];                   
11 
12                     fs.Read(bytes, 0, Convert.ToInt32(fs.Length));
13                     fs.Close();
14 　　　　　　　　　　　 //压 缩  Byte
15                     bytes = Compress(bytes);

16 　　　　　　　　　　　 //也可以将Byte[]转化为String  这个看个人需求 转String后 容量会变大，不推荐

17                     string str = RetrieveObject(bytes);
18 　　　　　　　　　　　 //将数据存入数据库 

19                     //DataHelper.DataInstance.WatConfig.InsertFile("F8", str);

                       存入数据库方法： public int InsertFile(string fabCode, byte[] bytes){............}
20                 }
21 
22             }
23             catch (SystemException ex)
24             { 
25                 ExceptionBase.ExceptionHandler(ex);
26             }

  

从数据库中取出文件：

读取数据

1                 DataTable dt = DataHelper.DataInstance.WatConfig.QueryFile();

　　　　　　　　　　 //byte[] by = GetBinaryFormat(str); //用String时 可以转Byte【】

                  byte[] by = (byte[])dt.Rows[0][1];
3                 byte[] bytes = Decompress(by); 
5                 FileStream stream = new FileStream(@"C:\Users\......\Desktop\6.xls", FileMode.CreateNew);
6                 BinaryWriter bw = new BinaryWriter(stream);
7                 bw.Write(bytes, 0, bytes.Length);
8                 bw.Close();
9                 fs.Close();

 

压缩/解压缩、序列化/反序列化

View Code

 1         /// <summary>
 2         /// 压缩数据
 3         /// </summary>
 4         /// <param name="data"></param>
 5         /// <returns></returns>
 6         public static byte[] Compress(byte[] data)
 7         {
 8             byte[] bytes;
 9             MemoryStream ms = new MemoryStream();
10             GZipStream stream = new GZipStream(ms, CompressionMode.Compress, true);
11             stream.Write(data, 0, data.Length);
12             stream.Close();
13             stream.Dispose();
14             //必须把stream流关闭才能返回ms流数据,不然数据会不完整
15             bytes = ms.ToArray();
16             ms.Close();
17             ms.Dispose();
18             return bytes;
19         }
20 
21         /// <summary>
22         /// 解压数据
23         /// </summary>
24         /// <param name="data"></param>
25         /// <returns></returns>
26         public static byte[] Decompress(byte[] data)
27         {
28             byte[] bytes;
29             MemoryStream ms = new MemoryStream();
30             ms.Write(data, 0, data.Length);
31             ms.Position = 0;
32             GZipStream stream = new GZipStream(ms, CompressionMode.Decompress, true);
33             byte[] buffer = new byte[1024];
34             MemoryStream temp = new MemoryStream();
35             int read = stream.Read(buffer, 0, buffer.Length);
36             while (read > 0)
37             {
38                 temp.Write(buffer, 0, read);
39                 read = stream.Read(buffer, 0, buffer.Length);
40             }
41             //必须把stream流关闭才能返回ms流数据,不然数据会不完整
42             stream.Close();
43             stream.Dispose();
44             ms.Close();
45             ms.Dispose();
46             bytes = temp.ToArray();
47             temp.Close();
48             temp.Dispose();
49             return bytes;
50         }
51 
52         /// <summary>
53         /// 将objec格式化成字节数组byte[]，并压缩
54         /// </summary>
55         /// <param name="dsOriginal">object对象</param>
56         /// <returns>字节数组</returns>
57         public static byte[] GetBinaryFormat(string dsOriginal)
58         {

　　　　　　　      IFormatter formatter = new BinaryFormatter();
                  byte[] byt = Convert.FromBase64String(dsOriginal);
                  object obj = null;
                  using (Stream stream = new MemoryStream(byt, 0, byt.Length))
                  {
                        obj = formatter.Deserialize(stream);
                  }
                  byte[] by = (byte[])obj;

                  return by;

67         }
68 
69         /// <summary>
70         /// 将字节数组解压后反序列化成object对象
71         /// </summary>
72         /// <param name="binaryData">字节数组</param>
73 　　     /// <returns>object对象</returns>
74         public static string RetrieveObject(byte[] binaryData)
75         {
76             IFormatter formatter = new BinaryFormatter();
77             string result = string.Empty;
78             using (MemoryStream stream = new MemoryStream())
79             {
80                 formatter.Serialize(stream, binaryData);
81                 byte[] byt = new byte[stream.Length];
82                 byt = stream.ToArray();
83                 result = Convert.ToBase64String(byt);
84                 stream.Flush();
85             }
86             return result;
87         }

转载于:https://www.cnblogs.com/ywp0212/archive/2012/02/17/2355541.html