1、对SQLite的封装：
<span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Collections.Generic;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Linq;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Text;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Data;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Data.SQLite;</p><p></span><span style="color: #0000ff;"><span class="keyword">namespace</span></span><span style="color: #000000;"> DataBaseHelper</p><p>{</p><p><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">class</span></span><span style="color: #000000;"> SQLiteHelper</p><p><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">private</span></span> SQLiteConnection connection = <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">----创建连接串并连接数据库----</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> SQLiteHelper(<span style="color: #0000ff;"><span class="keyword">string</span></span> path, <span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> password)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">string</span></span> conn_str = <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">data source=</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span> + path + <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">;password=</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span> +<span style="color: #000000;"> password;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> SQLiteConnection(conn_str); </p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection.Open();</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">----修改数据库密码----</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> <span style="color: #0000ff;"><span class="keyword">bool</span></span> ChangePassword(<span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> newPassword)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">bool</span></span> ret = <span style="color: #0000ff;"><span class="keyword">false</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">try</span></span><span style="color: #000000;"></p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection.ChangePassword(newPassword);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>ret </span>= <span style="color: #0000ff;"><span class="keyword">true</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">catch</span></span><span style="color: #000000;"> (System.Data.SQLite.SQLiteException ex)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">log.Error("ChangeDBPwd occurs exceptions：" + ex.Message);</span></span><span style="color: #008000;"></span></p><p><span style="color: #000000;"><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">return</span></span><span style="color: #000000;"> ret;</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">----关闭数据库连接----</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> <span style="color: #0000ff;"><span class="keyword">void</span></span><span style="color: #000000;"> CloseConnection()</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection.Close();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection </span>= <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"><span style="color: #008000;"> 执行一个查询语句，返回一个包含查询结果的DataTable </span></span><span style="color: #008000;"></p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"></summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="sql"></span></span><span style="color: #008000;">要执行的查询语句</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="parameters"></span></span><span style="color: #008000;">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><returns></span><span class="xmlDocTag"></returns></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> DataTable ExecuteDataTable(<span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> sql, SQLiteParameter[] parameters)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">try</span></span><span style="color: #000000;"></p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">using</span></span> (SQLiteCommand Command = <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> SQLiteCommand(sql, connection))</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">if</span></span> (parameters != <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>Command.Parameters.AddRange(parameters);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>SQLiteDataAdapter adapter </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> SQLiteDataAdapter(Command);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>DataTable dataTable </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> DataTable();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>adapter.Fill(dataTable);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">return</span></span><span style="color: #000000;"> dataTable;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">catch</span></span><span style="color: #000000;"> (SQLiteException ex)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>System.Exception exc </span>=<span style="color: #000000;"> ex;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">throw</span></span><span style="color: #000000;"> (exc);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"><span style="color: #008000;"> 对SQLite数据库执行增删改操作，返回受影响的行数。 </span></span><span style="color: #008000;"></p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"></summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="sql"></span></span><span style="color: #008000;">要执行的增删改的SQL语句</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="parameters"></span></span><span style="color: #008000;">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><returns></span><span class="xmlDocTag"></returns></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> <span style="color: #0000ff;"><span class="keyword">int</span></span> ExecuteNonQuery(<span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> sql, SQLiteParameter[] parameters)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">int</span></span> affectRows = <span style="color: #800080;"><span class="number">0</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">try</span></span><span style="color: #000000;"></p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">using</span></span> (SQLiteTransaction Transaction =<span style="color: #000000;"> connection.BeginTransaction())</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">using</span></span> (SQLiteCommand Command = <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> SQLiteCommand(sql, connection, Transaction))</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">if</span></span> (parameters != <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>Command.Parameters.AddRange(parameters);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>affectRows </span>=<span style="color: #000000;"> Command.ExecuteNonQuery();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>Transaction.Commit();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">catch</span></span><span style="color: #000000;"> (SQLiteException ex)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>affectRows </span>= -<span style="color: #800080;"><span class="number">1</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">log.Error("ExecuteNonQuery occurs exception：" + ex.Message);</span></span><span style="color: #008000;"></span></p><p><span style="color: #000000;"><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">return</span></span><span style="color: #000000;"> affectRows;</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span>}</p><p>}</span>
调用示例：
SQLiteHelper helper = <span style="color: #0000ff;"><span class="keyword">new</span></span> SQLiteHelper(<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">D:\\mysqlite.db</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>,<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">123456</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>); <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">连接到D盘下的mysqlite.db数据库,连接密码为123456</span></span><span style="color: #008000;"></p><p></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">bool ch = helper.ChangePassword("654321"); </span><span style="color: #008000;">//</span><span style="color: #008000;">将密码修改为：654321</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span></p><p><span style="color: #0000ff;"><span class="keyword">string</span></span> select_sql = <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">select * from student</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>; <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">查询的SQL语句</span></span><span style="color: #008000;"></span></p><p>DataTable dt = helper.ExecuteDataTable(select_sql, <span style="color: #0000ff;"><span class="keyword">null</span></span>); <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">执行查询操作,结果存放在dt中</span></span><span style="color: #008000;"></p><p></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">向数据库中student表中插入了一条(name = "马兆瑞"，sex = "男"，telephone = "15550008990")的记录</span></span><span style="color: #008000;"></span></p><p><span style="color: #0000ff;"><span class="keyword">string</span></span> insert_sql = <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">insert into student(name,sex,telephone) values(?,?,?)</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>; <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">插入的SQL语句(带参数)</span></span><span style="color: #008000;"></span></p><p>SQLiteParameter[] para = <span style="color: #0000ff;"><span class="keyword">new</span></span> SQLiteParameter[<span style="color: #800080;"><span class="number">3</span></span>]; <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">构造并绑定参数</span></span><span style="color: #008000;"></span></p><p><span style="color: #0000ff;"><span class="keyword">string</span></span>[] tag = { <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">name</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>, <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">sex</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>, <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">telephone</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span><span style="color: #000000;"> };</p><p></span><span style="color: #0000ff;"><span class="keyword">string</span></span>[] <span class="keyword">value</span> = { <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">马兆瑞</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>,<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">男</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>,<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">15550008990</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span><span style="color: #000000;">};</p><p></span><span style="color: #0000ff;"><span class="keyword">for</span></span> (<span style="color: #0000ff;"><span class="keyword">int</span></span> i = <span style="color: #800080;"><span class="number">0</span></span>; i < <span style="color: #800080;"><span class="number">3</span></span>; i++<span style="color: #000000;">)</p><p>{</p><p> para[i] </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> SQLiteParameter(tag[i], <span class="keyword">value</span>[i]); </p><p>}</p><p></span><span style="color: #0000ff;"><span class="keyword">int</span></span> ret = helper.ExecuteNonQuery(insert_sql, para); <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">执行插入操作</span></span><span style="color: #008000;"></span>
2、对Access的封装：
<span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Collections.Generic;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Linq;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Text;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Data;</p><p></span><span style="color: #0000ff;"><span class="keyword">using</span></span><span style="color: #000000;"> System.Data.OleDb;</p><p></span><span style="color: #0000ff;"><span class="keyword">namespace</span></span><span style="color: #000000;"> DataBaseHelper</p><p>{</p><p><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">public</span></span> <span style="color: #0000ff;"><span class="keyword">class</span></span><span style="color: #000000;"> AccessHelper</p><p><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">private</span></span> OleDbConnection connection = <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">----创建连接串并连接数据库----</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> AccessHelper(<span style="color: #0000ff;"><span class="keyword">string</span></span> path, <span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> password)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">string</span></span> conn_str = <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span> + path + <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">;Jet OLEDB:Database Password= </span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span> +<span style="color: #000000;"> password;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> OleDbConnection(conn_str); </p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection.Open();</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">----关闭数据库连接----</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> <span style="color: #0000ff;"><span class="keyword">void</span></span><span style="color: #000000;"> CloseConnection()</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection.Close();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>connection </span>= <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"><span style="color: #008000;"> 执行一个查询语句，返回一个包含查询结果的DataTable </span></span><span style="color: #008000;"></p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"></summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="sql"></span></span><span style="color: #008000;">要执行的查询语句</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="parameters"></span></span><span style="color: #008000;">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><returns></span><span class="xmlDocTag"></returns></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> DataTable ExecuteDataTable(<span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> sql, OleDbParameter[] parameters)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">try</span></span><span style="color: #000000;"></p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">using</span></span> (OleDbCommand Command = <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> OleDbCommand(sql, connection))</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">if</span></span> (parameters != <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>Command.Parameters.AddRange(parameters);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>OleDbDataAdapter adapter </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> OleDbDataAdapter(Command);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>DataTable dataTable </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> DataTable();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>adapter.Fill(dataTable);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">return</span></span><span style="color: #000000;"> dataTable;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">catch</span></span><span style="color: #000000;"> (OleDbException ex)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>System.Exception exc </span>=<span style="color: #000000;"> ex;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">throw</span></span><span style="color: #000000;"> (exc);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"><span style="color: #008000;"> 对Access数据库执行增删改操作，返回受影响的行数。 </span></span><span style="color: #008000;"></p><p><span class="indent"> </span><span class="indent"> </span></span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"></summary></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="sql"></span></span><span style="color: #008000;">要执行的增删改的SQL语句</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><param name="parameters"></span></span><span style="color: #008000;">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</span><span style="color: #808080;"><span class="xmlDocTag"></param></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #808080;"><span class="comment"><span class="xmlDocTag">///</span></span></span><span class="comment"> <span style="color: #808080;"><span class="xmlDocTag"><returns></span><span class="xmlDocTag"></returns></span></span> </span></p><p><span class="indent"> </span><span class="indent"> </span><span style="color: #0000ff;"><span class="keyword">public</span></span> <span style="color: #0000ff;"><span class="keyword">int</span></span> ExecuteNonQuery(<span style="color: #0000ff;"><span class="keyword">string</span></span><span style="color: #000000;"> sql, OleDbParameter[] parameters)</p><p><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">int</span></span> affectRows = <span style="color: #800080;"><span class="number">0</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">try</span></span><span style="color: #000000;"></p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">using</span></span> (OleDbTransaction Transaction =<span style="color: #000000;"> connection.BeginTransaction())</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">using</span></span> (OleDbCommand Command = <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> OleDbCommand(sql, connection, Transaction))</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">if</span></span> (parameters != <span style="color: #0000ff;"><span class="keyword">null</span></span><span style="color: #000000;">)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>Command.Parameters.AddRange(parameters);</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>affectRows </span>=<span style="color: #000000;"> Command.ExecuteNonQuery();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>Transaction.Commit();</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">catch</span></span><span style="color: #000000;"> (OleDbException ex)</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>{</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>affectRows </span>= -<span style="color: #800080;"><span class="number">1</span></span><span style="color: #000000;">;</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">log.Error("ExecuteNonQuery occurs exception：" + ex.Message);</span></span><span style="color: #008000;"></span></p><p><span style="color: #000000;"><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span><span class="indent"> </span><span class="indent"> </span></span><span style="color: #0000ff;"><span class="keyword">return</span></span><span style="color: #000000;"> affectRows;</p><p><span class="indent"> </span><span class="indent"> </span>}</p><p><span class="indent"> </span>}</p><p>}</span>
调用示例：
AccessHelper helper = <span style="color: #0000ff;"><span class="keyword">new</span></span> AccessHelper(<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">D:\\myaccess.mdb</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>,<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">123456789</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>); <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">连接到D盘下的myaccess.mdb数据库,密码为123456789</span></span><span style="color: #008000;"></span></p><p><span class="indent"> </span></p><p><span style="color: #0000ff;"><span class="keyword">string</span></span> select_sql = <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">select * from student</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>; <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">查询的SQL语句</span></span><span style="color: #008000;"></span></p><p>DataTable dt = helper.ExecuteDataTable(select_sql, <span style="color: #0000ff;"><span class="keyword">null</span></span>); <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">执行查询操作,结果存放在dt中</span></span><span style="color: #008000;"></p><p></span><span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">向数据库中student表中插入了一条(name = "马兆瑞"，sex = "男"，telephone = "15550008990")的记录</span></span><span style="color: #008000;"></span></p><p><span style="color: #0000ff;"><span class="keyword">string</span></span> insert_sql = <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">insert into student(name,sex,telephone) values(?,?,?)</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>; <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">插入的SQL语句(带参数)</span></span><span style="color: #008000;"></span></p><p>SQLiteParameter[] para = <span style="color: #0000ff;"><span class="keyword">new</span></span> SQLiteParameter[<span style="color: #800080;"><span class="number">3</span></span>]; <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">构造并绑定参数</span></span><span style="color: #008000;"></span></p><p><span style="color: #0000ff;"><span class="keyword">string</span></span>[] tag = { <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">name</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>, <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">sex</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>, <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">telephone</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span><span style="color: #000000;"> };</p><p></span><span style="color: #0000ff;"><span class="keyword">string</span></span>[] <span class="keyword">value</span> = { <span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">马兆瑞</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>,<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">男</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span>,<span style="color: #800000;"><span class="string">"</span></span><span class="string"><span style="color: #800000;">15550008990</span><span style="color: #800000;">"</span></span><span style="color: #800000;"></span><span style="color: #000000;">};</p><p></span><span style="color: #0000ff;"><span class="keyword">for</span></span> (<span style="color: #0000ff;"><span class="keyword">int</span></span> i = <span style="color: #800080;"><span class="number">0</span></span>; i < <span style="color: #800080;"><span class="number">3</span></span>; i++<span style="color: #000000;">)</p><p>{</p><p> para[i] </span>= <span style="color: #0000ff;"><span class="keyword">new</span></span><span style="color: #000000;"> SQLiteParameter(tag[i], <span class="keyword">value</span>[i]); </p><p>}</p><p></span><span style="color: #0000ff;"><span class="keyword">int</span></span> ret = helper.ExecuteNonQuery(insert_sql, para); <span style="color: #008000;"><span class="comment">//</span></span><span class="comment"><span style="color: #008000;">执行插入操作</span></span><span style="color: #008000;"></span>
本人是IT菜鸟，代码有很多不足之处，望大家多多指教