从SQLserver中导出表数据到Access
- WBOYオリジナル
- 2016-06-07 15:24:201256ブラウズ
每篇随便都得有个背景吧，这次做一个项目时，突然碰到这个样一个问题，需要将本地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
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
前の記事：常见的数据库连接池次の記事：MSZBSQL面试题2