从SQLserver中导出表数据到Access
- WBOYOriginal
- 2016-06-07 15:24:201255browse
每篇随便都得有个背景吧，这次做一个项目时，突然碰到这个样一个问题，需要将本地sqlserver中的 数据 导出 到access后，再传输access 数据 库，所以就在想怎样实现这样的操作。后面经过在网上查找了一些资料结合以前的知识，搞了这样一个东西出来； 1 /// su
每篇随便都得有个背景吧，这次做一个项目时，突然碰到这个样一个问题，需要将本地sqlserver中的数据导出到access后，再传输access数据库，所以就在想怎样实现这样的操作。后面经过在网上查找了一些资料结合以前的知识，搞了这样一个东西出来；
<span> 1</span> <span>///</span> <span><summary></summary></span>
<span> 2</span> <span>///</span><span> 从sqlserver中<strong>导出</strong><strong>数据</strong>到access
</span><span> 3</span> <span>///</span><span> state=0 Jzjl <strong>导出</strong>菜品信息表
</span><span> 4</span> <span>///</span><span> state=1 lbxf_jz <strong>导出</strong>收银信息表
</span><span> 5</span> <span>///</span> <span></span>
<span> 6</span> <span>///</span> <span><param name="host"></span>
<span> 7</span> <span>///</span> <span><param name="name"></span>
<span> 8</span> <span>///</span> <span><param name="password"></span>
<span> 9</span> <span>///</span> <span><param name="root"></span>
<span> 10</span> <span>static</span> <span>void</span> BackupA(<span>string</span> tablename, <span>int</span> state, <span>string</span> connection = <span>"</span><span>Data Source=.;Initial Catalog=mpcy;Integrated Security=True</span><span>"</span><span>)
</span><span> 11</span> <span> {
</span><span> 12</span> <span>string</span> path =<span> System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
</span><span> 13</span> <span>string</span> file = <span>"</span><span>mpcy.model</span><span>"</span><span>;
</span><span> 14</span> <span>string</span> root = path +<span> file;
</span><span> 15</span>
<span> 16</span> Console.WriteLine(<span>"</span><span>文件路径:</span><span>"</span> +<span> root);
</span><span> 17</span> <span>//</span><span>备份Access<strong>数据</strong>库</span>
<span> 18</span> <span>if</span> (state == <span>0</span>) { tablename = <span>"</span><span>Jzjl</span><span>"</span>; chkandcrt(root); } <span>else</span> { tablename = <span>"</span><span>lbxf_jz</span><span>"</span><span>; }
</span><span> 19</span>
<span> 20</span> root = BakPath + <span>"</span><span>\\mpcy.dat</span><span>"</span><span>;
</span><span> 21</span> <span>string</span> accesssqlconn = <span>@"</span><span>Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:DataBase Password=xhjxjf168;Data Source=</span><span>"</span> + root + <span>"</span><span>;</span><span>"</span><span>;
</span><span> 22</span> <span>//</span><span>SqlHelper.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", "127.0.0.1", "mpcyTemp", "sa", "12345");
</span><span> 23</span> <span>//</span><span>connection = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", "mpcyTemp");</span>
<span> 24</span> SqlHelper.ConnectionString =<span> connection;
</span><span> 25</span> <span>//</span><span>从sqlserver中读出<strong>数据</strong>到datatable</span>
<span> 26</span> OleDbConnection conn = <span>new</span><span> OleDbConnection(accesssqlconn);
</span><span> 27</span> OleDbCommand cmd =<span> conn.CreateCommand();
</span><span> 28</span> <span>try</span>
<span> 29</span> <span> {
</span><span> 30</span> DataTable user = SqlHelper.ExecuteTable(CommandType.Text, <span>string</span>.Format(<span>"</span><span>select * from {0}</span><span>"</span>, tablename), <span>null</span><span>);
</span><span> 31</span> <span>int</span> i = <span>0</span><span>;
</span><span> 32</span> <span>if</span> (user.Rows.Count > <span>0</span><span>)
</span><span> 33</span> <span> {
</span><span> 34</span> <span>using</span><span> (conn)
</span><span> 35</span> <span> {
</span><span> 36</span> <span>using</span><span> (cmd)
</span><span> 37</span> <span> {
</span><span> 38</span> <span> conn.Open();
</span><span> 39</span>
<span> 40</span> <span>//</span><span>每次导入前，先清空<strong>数据</strong>库表</span>
<span> 41</span> <span>string</span> sql = <span>string</span>.Format(<span>"</span><span>delete * from {0}</span><span>"</span>, tablename); cmd.CommandText =<span> sql; cmd.ExecuteNonQuery();
</span><span> 42</span>
<span> 43</span> OleDbDataAdapter adp = <span>new</span> OleDbDataAdapter(); adp.SelectCommand = <span>new</span> OleDbCommand(<span>string</span>.Format(<span>"</span><span>select * from {0}</span><span>"</span>, tablename), conn); OleDbCommandBuilder cb = <span>new</span> OleDbCommandBuilder(adp); DataSet data = <span>new</span><span> DataSet();
</span><span> 44</span> <span>//</span><span>加载access中的<strong>数据</strong>表，并通过追加的方式放入dataset中</span>
<span> 45</span> <span> adp.Fill(data);
</span><span> 46</span> <span>for</span> (<span>int</span> j = <span>0</span>; j )
<span> 47</span> <span> {
</span><span> 48</span> <span>//</span><span>for (int l = 0; l <span> 49</span> <span>//</span><span>{
</span><span> 50</span> <span>//</span><span> if (l > user.Columns.Count - 1)
</span><span> 51</span> <span>//</span><span> break;</span>
<span> 52</span> DataRow dr = data.Tables[<span>0</span><span>].NewRow();
</span><span> 53</span> <span>for</span> (<span>int</span> k = <span>0</span>; k )
</span><span> 54</span> <span> {
</span><span> 55</span> Type typ =<span> user.Rows[j][k].GetType();
</span><span> 56</span> <span>if</span> (typ == <span>typeof</span>(<span>double</span>) || typ == <span>typeof</span>(<span>int</span>) || typ == <span>typeof</span>(<span>decimal</span>) || typ == <span>typeof</span>(<span>float</span><span>))
</span><span> 57</span> { <span>if</span> (<span>string</span>.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = <span>"</span><span>0</span><span>"</span>; } <span>else</span> { dr[k] =<span> user.Rows[j][k].ToString(); } }
</span><span> 58</span> <span>else</span>
<span> 59</span> { <span>if</span> (<span>string</span>.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = <span>"</span> <span>"</span>; } <span>else</span> { dr[k] =<span> user.Rows[j][k].ToString(); } }
</span><span> 60</span> <span> }
</span><span> 61</span> <span>//</span><span>dr["UserID"] = user.Rows[j][l].ToString();
</span><span> 62</span> <span>//</span><span>dr["UserName"] = user.Rows[j][l + 1].ToString();
</span><span> 63</span> <span>//</span><span>网dataset中添加<strong>数据</strong></span>
<span> 64</span> data.Tables[<span>0</span><span>].Rows.Add(dr);
</span><span> 65</span> <span>//</span><span>}</span>
<span> 66</span> <span> }
</span><span> 67</span> data.Tables[<span>0</span>].TableName =<span> tablename;
</span><span> 68</span> i = adp.Update(data.Tables[<span>0</span>]); <span>//</span><span>通过update方法Insert或update<strong>数据</strong> </span>
<span> 69</span> <span> Console.WriteLine(i.ToString());
</span><span> 70</span> <span> }
</span><span> 71</span> <span> }
</span><span> 72</span> <span> }
</span><span> 73</span> <span> }
</span><span> 74</span> <span>//</span><span>判断异常类型</span>
<span> 75</span> <span>catch</span><span> (SqlException er)
</span><span> 76</span> <span> {
</span><span> 77</span> ErrorTime++<span>;
</span><span> 78</span> <span>if</span> (ErrorTime > <span>3</span><span>)
</span><span> 79</span> { FileLog.Logger.Write(er); FileLog.Logger.Write(<span>"</span><span>错误次数太多，退出程序！</span><span>"</span>); Console.WriteLine(<span>"</span><span>错误次数太多，退出程序！</span><span>"</span><span>); }
</span><span> 80</span> <span>else</span>
<span> 81</span> <span> {
</span><span> 82</span> <span>if</span> (ErrorTime % <span>2</span> == <span>0</span><span>)
</span><span> 83</span> <span> {
</span><span> 84</span> FileLog.Logger.Write(<span>"</span><span>尝试使用MSSQL身份验证登陆<strong>数据</strong>库！</span><span>"</span>); FileLog.Logger.Write(er); Console.WriteLine(<span>"</span><span>SQLSERVER打开失败，尝试使用MSSQL方式登录<strong>数据</strong>库。</span><span>"</span><span>);
</span><span> 85</span> <span>string</span> sqlcon = <span>string</span>.Format(<span>"</span><span>Data Source={0};Initial Catalog={1};User ID={2};Password={3}</span><span>"</span>, ConfigHelper.DBHost, <span>"</span><span>mpcy</span><span>"</span><span>, ConfigHelper.DBName, ConfigHelper.DBPwd);
</span><span> 86</span> <span>//</span><span>string sqlcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", "127.0.0.1", "mpcyTemp", "sa", "12345");</span>
<span> 87</span> BackupA(<span>""</span><span>, state, sqlcon);
</span><span> 88</span> <span> }
</span><span> 89</span> <span>else</span>
<span> 90</span> { FileLog.Logger.Write(<span>"</span><span>尝试使用WINDOWS身份验证登陆<strong>数据</strong>库！</span><span>"</span>); FileLog.Logger.Write(er); Console.WriteLine(<span>"</span><span>SQLSERVER打开失败，尝试使用WINDOWS方式登录<strong>数据</strong>库。</span><span>"</span>); BackupA(<span>""</span><span>, state); }
</span><span> 91</span> <span> }
</span><span> 92</span> <span> }
</span><span> 93</span> <span>catch</span><span> (Exception e)
</span><span> 94</span> <span> {
</span><span> 95</span> FileLog.Logger.Write(<span>"</span><span><strong>数据</strong><strong>导出</strong>失败！</span><span>"</span>); FileLog.Logger.Write(e); Console.WriteLine(<span>"</span><span><strong>数据</strong><strong>导出</strong>失败！</span><span>"</span><span>);
</span><span> 96</span> <span>//</span><span>throw (new Exception("<strong>数据</strong><strong>导出</strong>失败,请联系IT部！"));</span>
<span> 97</span> <span> }
</span><span> 98</span> <span>finally</span>
<span> 99</span> <span> {
</span><span>100</span> <span> conn.Close();
</span><span>101</span> <span> }
</span><span>102</span> <span>if</span> (state == <span>0</span><span>)
</span><span>103</span> <span> {
</span><span>104</span> <span>string</span> sqlcon = <span>string</span>.Format(<span>"</span><span>Data Source={0};Initial Catalog={1};User ID={2};Password={3}</span><span>"</span>, ConfigHelper.DBHost, <span>"</span><span>mpcyTemp</span><span>"</span><span>, ConfigHelper.DBName, ConfigHelper.DBPwd);
</span><span>105</span> <span>//</span><span>string sqlcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", "127.0.0.1", "mpcyTemp", "sa", "12345");</span>
<span>106</span> BackupA(<span>""</span>, <span>1</span><span>, sqlcon);
</span><span>107</span> <span> }
</span><span>108</span> <span> }
</span><span>109</span>
<span>110</span> <span>///</span> <span><summary></summary></span>
<span>111</span> <span>///</span><span> 检查并备份
</span><span>112</span> <span>///</span> <span></span>
<span>113</span> <span>///</span> <span><param name="check"></span>
<span>114</span> <span>///</span> <span><param name="root"></span>
<span>115</span> <span>static</span> <span>void</span> chkandcrt(<span>string</span><span> root)
</span><span>116</span> <span> {
</span><span>117</span> <span>try</span> { <span>if</span> (!Directory.Exists(BakPath)) { Directory.CreateDirectory(BakPath); } <span>if</span> (!File.Exists(BakPath + <span>"</span><span>\\mpcy.dat</span><span>"</span>)) { File.Copy(root, BakPath + <span>"</span><span>\\mpcy.dat</span><span>"</span><span>); } }
</span><span>118</span> <span>catch</span><span> (Exception e)
</span><span>119</span> { FileLog.Logger.Write(e); FileLog.Logger.Write(<span>"</span><span>文件检查失败！</span><span>"</span>); Console.WriteLine(<span>"</span><span>文件信息检查失败！</span><span>"</span><span>); }
</span><span>120</span> }
代码不多，核心代码就这几句：
OleDbDataAdapter adp = <span>new</span> OleDbDataAdapter(); adp.SelectCommand = <span>new</span> OleDbCommand(<span>string</span>.Format(<span>"</span><span>select * from {0}</span><span>"</span>, tablename), conn); OleDbCommandBuilder cb = <span>new</span> OleDbCommandBuilder(adp); DataSet data = <span>new</span><span> DataSet();
</span><span>44</span> <span>//</span><span>加载access中的<strong>数据</strong>表，并通过追加的方式放入dataset中</span>
<span>45</span><span> adp.Fill(data);
</span><span>46</span> <span>for</span> (<span>int</span> j = <span>0</span>; j )
<span>47</span><span> {
</span><span>48</span> <span>//</span><span>for (int l = 0; l
<span>49</span> <span>//</span><span>{</span>
<span>50</span> <span>//</span><span> if (l > user.Columns.Count - 1)</span>
<span>51</span> <span>//</span><span> break;</span>
<span>52</span> DataRow dr = data.Tables[<span>0</span><span>].NewRow();
</span><span>53</span> <span>for</span> (<span>int</span> k = <span>0</span>; k )
</span><span>54</span><span> {
</span><span>55</span> Type typ =<span> user.Rows[j][k].GetType();
</span><span>56</span> <span>if</span> (typ == <span>typeof</span>(<span>double</span>) || typ == <span>typeof</span>(<span>int</span>) || typ == <span>typeof</span>(<span>decimal</span>) || typ == <span>typeof</span>(<span>float</span><span>))
</span><span>57</span> { <span>if</span> (<span>string</span>.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = <span>"</span><span>0</span><span>"</span>; } <span>else</span> { dr[k] =<span> user.Rows[j][k].ToString(); } }
</span><span>58</span> <span>else</span>
<span>59</span> { <span>if</span> (<span>string</span>.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = <span>"</span> <span>"</span>; } <span>else</span> { dr[k] =<span> user.Rows[j][k].ToString(); } }
</span><span>60</span><span> }
</span><span>61</span> <span>//</span><span>dr["UserID"] = user.Rows[j][l].ToString();</span>
<span>62</span> <span>//</span><span>dr["UserName"] = user.Rows[j][l + 1].ToString();</span>
<span>63</span> <span>//</span><span>网dataset中添加<strong>数据</strong></span>
<span>64</span> data.Tables[<span>0</span><span>].Rows.Add(dr);
</span><span>65</span> <span>//</span><span>}</span>
<span>66</span><span> }
</span><span>67</span> data.Tables[<span>0</span>].TableName =<span> tablename;
</span><span>68</span> i = adp.Update(data.Tables[<span>0</span>]); <span>//</span><span>通过update方法Insert或update<strong>数据</strong> </span>
<span>69</span> Console.WriteLine(i.ToString());
这中间是这样的：先通过一个DataAdapter读取了Access数据库的结构，然后再通过从Sqlserver中读取到数据的DataTable将数据导入到这个被DataAdapter填充了结构的DataSet的表中。最后，通过这个DataAdapter来更新整个表，当然，表的结构要一致！！
另外，这里有一个OleDbCommandBuilder，根据网上解释:自动生成用于协调对 DataSet的更改与关联数据库的单表命令。也就是说，在我们这个例子中，我们的表结构是没有改变，但是后面新添加了数据，于是这个OleDbCommandBuilder就会自动将表单命令生成为Insert语句，于是就执行了插入命令，但是如果在我们初始化的DataSet中本来就存在数据，然后我们进行了对原始数据的改变，后面就会将命令变成Update。
这样，就将Sqlserver中的数据填充到了Access数据库中了。
参考：http://bbs.bccn.net/thread-292655-1-1.html
http://blog.csdn.net/a3676212/article/details/2776027
http://www.cnblogs.com/rhythmK/archive/2010/07/19/1780874.html
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：常见的数据库连接池Next article：MSZBSQL面试题2