SqlServerCE数据库操作Help类

开始尝试MW6开发，收藏下sqlce操作代码，类中没有涉及SqlCeResultSet，以后整理加入。

对于SqlCeTransaction也没有涉及。

 

 

：如花！上代码。 

花：はい，以下になります。

using System; using System.Data; using System.Data.SqlServerCe; using System.Data.Common; namespace Foundation { /** <summary> /// 本类为：SQLCE数据库的一些基本操作。在使用前，先将数据库的连接字符串确认好，然后使用； /// 本类目的为了方便大家更好的使用SQLCE数据库； /// 本类为开源代码； /// 有使用本类所产生的一切后果由使用者自负 /// </summary> public class DataBase { private String Server, dbName, UID, Password; private String SqlCeConnectionString; private SqlCeConnection con; public DataBase() { // 初始化数据库连接字符串 Server = "Input Correct string!"; dbName = "Input Correct string!"; UID = "Input Correct string!"; Password = "Input Correct string!"; SqlCeConnectionString = "user id=" + UID + ";password=" + Password + ";initial catalog=" + dbName + ";data source=" + Server + ";Connect Timeout=30"; } /** <summary> /// 打开数据库连接。 /// </summary> private void Open() { if (con == null) { con = new SqlCeConnection(SqlCeConnectionString); con.Open(); } } /** <summary> /// 关闭数据库连接。 /// </summary> public void Close() { if (con != null) { con.Close(); this.Dispose(); } } /** <summary> /// Release resources. /// 释放资源。 /// </summary> public void Dispose() { // make sure connection is closed if (con != null) { con.Dispose(); con = null; } } /** <summary> /// 测试数据库连接是否成功 /// </summary> /// <param name="server">server</param> /// <param name="database">database</param> /// <param name="uid">用户名</param> /// <param name="password">密码</param> /// <returns>bool</returns> public bool TestConnection(string server, string database, string uid, string password) { try { con = null; SqlCeConnectionString = "user id=" + uid + ";password=" + password + ";initial catalog=" + database + ";data source=" + server + " "; this.Open(); } catch { return false; } return true; } /** <summary> /// 创建command对象以便执行SqlCe语句。 /// </summary> /// <param name="SqlCe">SqlCe Text.</param> /// <returns>Command object.</returns> private SqlCeCommand CreateCommand(string SqlCe) { // make sure connection is open Open(); SqlCeCommand cmd = new SqlCeCommand(SqlCe, con); cmd.CommandType = CommandType.Text; return cmd; } /** <summary> /// 创建带Prameters的Command对象 /// </summary> /// <param name="SqlCeCe">SqlCe语句</param> /// <param name="prams">SqlCeParameters参数</param> /// <returns>Command对象</returns> public SqlCeCommand CreateCommand(String SqlCe, SqlCeParameter[] prams) { Open(); SqlCeCommand cmd = new SqlCeCommand(SqlCe,con); cmd.CommandType = CommandType.Text; cmd.Parameters.Clear(); if (prams != null) { foreach (SqlCeParameter parameter in prams) cmd.Parameters.Add(parameter); } return cmd; } /** <summary> /// 执行一个无返回的SqlCe语句 /// </summary> /// <param name="SqlCe">SqlCe语句</param> /// <returns>执行结果</returns> public bool QueryExec(string SqlCe) { SqlCeCommand cmd = CreateCommand(SqlCe); try { cmd.ExecuteNonQuery(); } catch(Exception ex) { ex.Message.ToString(); return false; } finally { this.Close(); } return true; } /** <summary> /// 执行一个无返回的SqlCe语句，带parameters /// </summary> /// <param name="SqlCe">SqlCe语句</param> /// <returns>执行结果</returns> public bool QueryExec(string SqlCe, SqlCeParameter[] parms) { SqlCeCommand cmd = CreateCommand(SqlCe, parms); try { cmd.ExecuteNonQuery(); } catch(Exception ex) { ex.Message.ToString(); return false; } finally { this.Close(); } return true; } /** <summary> /// 执行一个插入记录操作，返回primary key /// </summary> /// <param name="SqlCe">insert SqlCe语句</param> /// <returns>返回的primary key</returns> public String InsertExec(string SqlCe) { SqlCe += ";SELECT @@identity AS [@@IDENTITY];"; SqlCeCommand cmd = CreateCommand(SqlCe); try { return cmd.ExecuteScalar().ToString(); } catch { return null; } finally { this.Close(); } } /** <summary> /// 执行一个插入记录操作，带parameters，返回primary key /// </summary> /// <param name="SqlCe">insert SqlCe语句</param> /// <returns>返回的primary key</returns> public String InsertExec(string SqlCe, SqlCeParameter[] prams) { SqlCe += ";SELECT @@identity AS [@@IDENTITY];"; SqlCeCommand cmd = CreateCommand(SqlCe, prams); try { return cmd.ExecuteScalar().ToString(); } catch(Exception ex) { ex.Message.ToString(); return null; } finally { this.Close(); } } /** <summary> /// 通过查询指定的SqlCe语句来获得一个返回值 /// </summary> /// <param name="SqlCe">SqlCe语句</param> /// <returns>返回值</returns> public String QueryValue(string SqlCe) { SqlCeCommand cmd = CreateCommand(SqlCe); try { return cmd.ExecuteScalar().ToString(); } catch(Exception ex) { ex.Message.ToString(); return null; } finally { this.Close(); } } /** <summary> /// 通过查询指定的SqlCe语句来获得一个返回表，带parms /// </summary> /// <param name="SqlCe"></param> /// <param name="prams"></param> /// <returns></returns> public DataTable QueryDataTable(string SqlCe,SqlCeParameter[] parms) { DataTable dataTable = new DataTable(); SqlCeCommand cmd = CreateCommand(SqlCe,parms); try { SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd); dataAdapter.Fill(dataTable); } catch(Exception ex) { ex.Message.ToString(); dataTable = null; } finally { this.Close(); } return dataTable; } /** <summary> /// 通过查询指定的SqlCe语句来获得一个返回值，带parms /// </summary> /// <param name="SqlCe">SqlCe语句</param> /// <returns>返回值</returns> public String QueryValue(string SqlCe, SqlCeParameter[] prams) { SqlCeCommand cmd = CreateCommand(SqlCe, prams); try { return cmd.ExecuteScalar().ToString(); } catch(Exception ex) { ex.Message.ToString(); return null; } finally { this.Close(); } } /** <summary> /// 查询返回DATAREADER /// </summary> /// <param name="dr"></param> /// <param name="error"></param> /// <param name="SqlCeConnectionString"></param> /// <param name="SqlCe">SqlCe语句</param> public SqlCeDataReader QueryDataReader(string SqlCe) { try { SqlCeCommand cmd = CreateCommand(SqlCe); return cmd.ExecuteReader(); } catch { return null; } finally { } } /** <summary> /// 查询返回DATASET /// </summary> /// <param name="SqlCe">SqlCe语句</param> /// <returns>DataSet对象</returns> public DataSet QueryDataSet (string SqlCe) { SqlCeCommand cmd = CreateCommand(SqlCe); DataSet ds = new DataSet(); try { SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd); dataAdapter.Fill(ds); } catch(Exception ex) { ex.Message.ToString(); ds = null; } finally { this.Close(); } return ds; } /** <summary> /// 通过SqlCe语句返回DataTable。 /// </summary> /// <returns>DataTable</returns> public DataTable QueryDataTable(string SqlCe) { DataTable dataTable = new DataTable(); SqlCeCommand cmd = CreateCommand(SqlCe); try { SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd); dataAdapter.Fill(dataTable); } catch(Exception ex) { ex.Message.ToString(); dataTable = null; } finally { this.Close(); } return dataTable; } /** <summary> /// 包装输入参数。 /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public SqlCeParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } /** <summary> /// 包装输出参数。 /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <returns>New parameter.</returns> public SqlCeParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); } public SqlCeParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Value); } /** <summary> /// 包装Command参数。 /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Direction">Parm direction.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public SqlCeParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { SqlCeParameter param; if(Size > 0) param = new SqlCeParameter(ParamName, DbType, Size); else param = new SqlCeParameter(ParamName, DbType); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; else Value = param.Value; return param; } } }