[Access] C#通过COM组件访问Access文件

说明： 1，采用dynamic调用COM组件，适用于.NET 4.0以上支持dynamic版本的才可以； 2，执行速度不敢恭维，只是因为要用于Silverlight OOB模式中才研究一二； 3，测试环境 .net 4.5 + Silverlight 5.0 + Visual Studio 2013 4，见如下helper类（需引用 using

说明：

1，采用dynamic调用COM组件，适用于.NET 4.0以上支持dynamic版本的才可以；

2，执行速度不敢恭维，只是因为要用于Silverlight OOB模式中才研究一二；

3，测试环境.net 4.5 + Silverlight 5.0 + Visual Studio 2013

4，见如下helper类（需引用using System.Runtime.InteropServices.Automation;）：

<span>  1</span> <span>public</span> <span>class</span><span> SLAccessHelper
</span><span>  2</span> <span>{
</span><span>  3</span>     <span>private</span> dynamic m_AccessApp;<span>//</span><span> Access.Application</span>
<span>  4</span>     <span>private</span> dynamic m_Database;<span>//</span><span> Database</span>
<span>  5</span>     <span>private</span><span> dynamic m_Recordset;
</span><span>  6</span> 
<span>  7</span>     <span>///</span> <span><summary></summary></span>
<span>  8</span>     <span>///</span><span> 构造函数
</span><span>  9</span>     <span>///</span> <span></span>
<span> 10</span>     <span>///</span> <span><param name="visible"></span><span>Access是否可见</span><span></span>
<span> 11</span>     <span>public</span> SLAccessHelper(<span>bool</span><span> visible)
</span><span> 12</span> <span>    {
</span><span> 13</span>         m_AccessApp = AutomationFactory.CreateObject(<span>"</span><span>Access.Application</span><span>"</span><span>);
</span><span> 14</span>         m_AccessApp.Visible =<span> visible;
</span><span> 15</span> <span>    }
</span><span> 16</span> 
<span> 17</span>     <span>///</span> <span><summary></summary></span>
<span> 18</span>     <span>///</span><span> 打开数据库
</span><span> 19</span>     <span>///</span> <span></span>
<span> 20</span>     <span>///</span> <span><param name="filePath"></span><span>Access数据库文件路径</span><span></span>
<span> 21</span>     <span>///</span> <span><param name="exclusive"></span><span>是否共享</span><span></span>
<span> 22</span>     <span>///</span> <span><param name="bstrPassword"></span><span>密码</span><span></span>
<span> 23</span>     <span>public</span> <span>void</span> OpenDb(<span>string</span> filePath, <span>bool</span> exclusive = <span>false</span>, <span>string</span> bstrPassword = <span>""</span><span>)
</span><span> 24</span> <span>    {
</span><span> 25</span> <span>        m_AccessApp.OpenCurrentDatabase(filePath, exclusive, bstrPassword);
</span><span> 26</span>         m_Database =<span> m_AccessApp.CurrentDb();
</span><span> 27</span> <span>    }
</span><span> 28</span> 
<span> 29</span>     <span>///</span> <span><summary></summary></span>
<span> 30</span>     <span>///</span><span> 获取当前数据库中所有表名称集合
</span><span> 31</span>     <span>///</span> <span></span>
<span> 32</span>     <span>///</span> <span><returns></returns></span><span>所有表名称集合</span><span></span>
<span> 33</span>     <span>public</span> Liststring><span> GetTableNames()
</span><span> 34</span> <span>    {
</span><span> 35</span>         Liststring> tableNames = <span>new</span> Liststring><span>();
</span><span> 36</span>         dynamic tableDefs =<span> m_Database.TableDefs;
</span><span> 37</span>         <span>foreach</span> (dynamic tableDef <span>in</span><span> tableDefs)
</span><span> 38</span> <span>        {
</span><span> 39</span> <span>            tableNames.Add(tableDef.Name);
</span><span> 40</span> <span>        }
</span><span> 41</span> 
<span> 42</span>         <span>return</span><span> tableNames;
</span><span> 43</span> <span>    }
</span><span> 44</span> 
<span> 45</span>     <span>///</span> <span><summary></summary></span>
<span> 46</span>     <span>///</span><span> 加载表数据
</span><span> 47</span>     <span>///</span> <span></span>
<span> 48</span>     <span>///</span> <span><param name="tableName"></span><span>表名称</span><span></span>
<span> 49</span>     <span>///</span> <span><returns></returns></span><span>表数据</span><span></span>
<span> 50</span>     <span>public</span> List<list>string>> LoadTable(<span>string</span><span> tableName)
</span><span> 51</span> <span>    {
</span><span> 52</span>         dynamic recordSet =<span> m_Database.OpenRecordset(tableName);
</span><span> 53</span>         <span>int</span> fieldsCount =<span> recordSet.Fields.Count;
</span><span> 54</span>         List<list>string>> data = <span>new</span> List<list>string>><span>();
</span><span> 55</span>         <span>if</span> (fieldsCount > <span>0</span><span>)
</span><span> 56</span> <span>        {
</span><span> 57</span>             <span>try</span>
<span> 58</span> <span>            {
</span><span> 59</span>                 Liststring> fieldNames = <span>new</span> Liststring><span>();
</span><span> 60</span>                 <span>for</span> (<span>int</span> i = <span>0</span>; i )
<span> 61</span> <span>                {
</span><span> 62</span> <span>                    fieldNames.Add(recordSet.Fields[i].Name);
</span><span> 63</span> <span>                }
</span><span> 64</span> <span>                data.Add(fieldNames);
</span><span> 65</span>                 <span>if</span> (!<span>recordSet.EOF)
</span><span> 66</span> <span>                {
</span><span> 67</span> <span>                    recordSet.MoveFirst();
</span><span> 68</span>                     <span>while</span> (!<span>recordSet.EOF)
</span><span> 69</span> <span>                    {
</span><span> 70</span>                         <span>object</span>[] dataRow = recordSet.GetRows();<span>//</span><span> 返回一维数组</span>
<span> 71</span>                         Liststring> dataRowStr = <span>new</span> Liststring><span>();
</span><span> 72</span>                         <span>for</span> (<span>int</span> i = <span>0</span>; i )
<span> 73</span> <span>                        {
</span><span> 74</span>                             dataRowStr.Add(dataRow[i] == <span>null</span> ? <span>""</span><span> : dataRow[i].ToString());
</span><span> 75</span> <span>                        }
</span><span> 76</span> <span>                        data.Add(dataRowStr);
</span><span> 77</span> <span>                    }
</span><span> 78</span> <span>                }
</span><span> 79</span> <span>            }
</span><span> 80</span>             <span>catch</span><span> (Exception ex)
</span><span> 81</span> <span>            {
</span><span> 82</span>                 <span>throw</span> <span>new</span><span> Exception(ex.Message);
</span><span> 83</span> <span>            }
</span><span> 84</span>             <span>finally</span>
<span> 85</span> <span>            {
</span><span> 86</span>                 <span>if</span> (recordSet != <span>null</span><span>)
</span><span> 87</span> <span>                {
</span><span> 88</span> <span>                    recordSet.Close();
</span><span> 89</span> <span>                    ((IDisposable)recordSet).Dispose();
</span><span> 90</span>                     recordSet = <span>null</span><span>;
</span><span> 91</span> <span>                }
</span><span> 92</span> <span>            }
</span><span> 93</span> <span>        }
</span><span> 94</span> 
<span> 95</span>         <span>return</span><span> data;
</span><span> 96</span> <span>    }
</span><span> 97</span> 
<span> 98</span>     <span>///</span> <span><summary></summary></span>
<span> 99</span>     <span>///</span><span> 添加新纪录
</span><span>100</span>     <span>///</span> <span></span>
<span>101</span>     <span>///</span> <span><param name="tableName"></span><span>表格名称</span><span></span>
<span>102</span>     <span>///</span> <span><param name="data"></span><span>数据</span><span></span>
<span>103</span>     <span>public</span> <span>void</span> AddNewRecord(<span>string</span> tableName, List<dictionary>string, <span>object</span>>><span> data)
</span><span>104</span> <span>    {
</span><span>105</span>         <span>try</span>
<span>106</span> <span>        {
</span><span>107</span>             m_Recordset = m_Database.OpenRecordset(tableName, <span>1</span>);<span>//</span><span> 1=RecordsetTypeEnum.dbOpenTable</span>
<span>108</span>             <span>int</span> fieldsCount =<span> m_Recordset.Fields.Count;
</span><span>109</span>             Liststring> fieldNames = <span>new</span> Liststring><span>();
</span><span>110</span>             <span>for</span> (<span>int</span> i = <span>0</span>; i )
<span>111</span> <span>            {
</span><span>112</span> <span>                fieldNames.Add(m_Recordset.Fields[i].Name);
</span><span>113</span> <span>            }
</span><span>114</span>             <span>for</span> (<span>int</span> rowIndex = <span>0</span>; rowIndex )
<span>115</span> <span>            {
</span><span>116</span> <span>                m_Recordset.AddNew();
</span><span>117</span>                 <span>foreach</span> (<span>string</span> fieldName <span>in</span><span> fieldNames)
</span><span>118</span> <span>                {
</span><span>119</span>                     m_Recordset.Fields[fieldName].Value =<span> data[rowIndex][fieldName];
</span><span>120</span> <span>                }
</span><span>121</span> <span>                m_Recordset.Update();
</span><span>122</span> <span>            }
</span><span>123</span> <span>        }
</span><span>124</span>         <span>catch</span><span>(Exception ex)
</span><span>125</span> <span>        {
</span><span>126</span>             <span>throw</span> <span>new</span><span> Exception(ex.Message);
</span><span>127</span> <span>        }
</span><span>128</span>         <span>finally</span>
<span>129</span> <span>        {
</span><span>130</span>             <span>if</span> (m_Recordset != <span>null</span><span>)
</span><span>131</span> <span>            {
</span><span>132</span> <span>                m_Recordset.Close();
</span><span>133</span> <span>                ((IDisposable)m_Recordset).Dispose();
</span><span>134</span>                 m_Recordset = <span>null</span><span>;
</span><span>135</span> <span>            }
</span><span>136</span> <span>        }
</span><span>137</span> <span>    }
</span><span>138</span> 
<span>139</span>     <span>///</span> <span><summary></summary></span>
<span>140</span>     <span>///</span><span> 更新表格数据
</span><span>141</span>     <span>///</span> <span></span>
<span>142</span>     <span>///</span> <span><param name="tableName"></span><span>表格名称</span><span></span>
<span>143</span>     <span>///</span> <span><param name="data"></span><span>数据</span><span></span>
<span>144</span>     <span>public</span> <span>void</span> UpdateTable(<span>string</span> tableName, List<dictionary>string, <span>string</span>>><span> data)
</span><span>145</span> <span>    {
</span><span>146</span>         <span>try</span>
<span>147</span> <span>        {
</span><span>148</span>             m_Recordset = m_Database.OpenRecordset(tableName, <span>1</span>);<span>//</span><span> 1=RecordsetTypeEnum.dbOpenTable</span>
<span>149</span> <span>            m_Recordset.MoveFirst();
</span><span>150</span>             <span>for</span> (<span>int</span> rowIndex = <span>0</span>; rowIndex )
<span>151</span> <span>            {
</span><span>152</span> <span>                m_Recordset.Edit();
</span><span>153</span>                 <span>foreach</span> (<span>string</span> fieldName <span>in</span><span> data[rowIndex].Keys)
</span><span>154</span> <span>                {
</span><span>155</span>                     m_Recordset.Fields[fieldName].Value =<span> data[rowIndex][fieldName];
</span><span>156</span> <span>                }
</span><span>157</span> <span>                m_Recordset.Update();
</span><span>158</span> <span>                m_Recordset.MoveNext();
</span><span>159</span> <span>            }
</span><span>160</span> <span>        }
</span><span>161</span>         <span>catch</span><span> (Exception ex)
</span><span>162</span> <span>        {
</span><span>163</span>             <span>throw</span> <span>new</span><span> Exception(ex.Message);
</span><span>164</span> <span>        }
</span><span>165</span>         <span>finally</span>
<span>166</span> <span>        {
</span><span>167</span>             <span>if</span> (m_Recordset != <span>null</span><span>)
</span><span>168</span> <span>            {
</span><span>169</span> <span>                m_Recordset.Close();
</span><span>170</span> <span>                ((IDisposable)m_Recordset).Dispose();
</span><span>171</span>                 m_Recordset = <span>null</span><span>;
</span><span>172</span> <span>            }
</span><span>173</span> <span>        }
</span><span>174</span> <span>    }
</span><span>175</span> 
<span>176</span>     <span>///</span> <span><summary></summary></span>
<span>177</span>     <span>///</span><span> 关闭
</span><span>178</span>     <span>///</span> <span></span>
<span>179</span>     <span>public</span> <span>void</span><span> Close()
</span><span>180</span> <span>    {
</span><span>181</span>         <span>if</span> (m_Database != <span>null</span><span>)
</span><span>182</span> <span>        {
</span><span>183</span> <span>            m_Database.Close();
</span><span>184</span> <span>            ((IDisposable)m_Database).Dispose();
</span><span>185</span>             m_Database = <span>null</span><span>;
</span><span>186</span> <span>        }
</span><span>187</span>         <span>if</span> (m_AccessApp != <span>null</span><span>)
</span><span>188</span> <span>        {
</span><span>189</span> <span>            m_AccessApp.CloseCurrentDatabase();
</span><span>190</span>             <span>//</span><span> m_AccessApp.Quit();</span><span>//</span><span> 导致最后会弹出Access主页面</span>
<span>191</span> <span>            ((IDisposable)m_AccessApp).Dispose();
</span><span>192</span>             m_AccessApp = <span>null</span><span>;
</span><span>193</span> <span>        }
</span><span>194</span> <span>        GC.Collect();
</span><span>195</span> <span>    }
</span><span>196</span> }</dictionary></dictionary></list></list></list>

View Code

 

通过dynamic构建的COM对象，在使用完成后都要手动关闭销毁，比如代码中的m_AccessApp, m_Database, m_Recordset三个对象，否则只是将m_AccessApp关闭清空释放掉，Access进程还是无法关闭，在程序关闭之前，始终都会有一个空白的无法关闭的Access界面；

在循环中处理dynamic和C#类型转换会降低程序执行效率，就比如像GetTableNames方法中循环遍历表名，都要花两三秒时间，所以尽量像object[] dataRow = recordSet.GetRows();直接获取其中的所有数据，然后再遍历处理，会极大提高执行效率；

要修改Access中的数据时，一定要先m_Recordset.Edit();才会允许你编辑其中的内容；