C#SQLCE.doc

1、c#开发的运行在 wm5.0环境中的 sqlce数据库操纵。2011-06-04 00:56这是数据库的封装类： using System; using System.Data; using System.Data.Common; using System.Data.SqlServerCe; using System.Windows.Forms; using System.IO; namespace testDB public class SQLCE public static SqlCeConnection conn = null; public static SqlCeTransactio

2、n trans = null; public static SqlCeCommand cmd = null; public static string sData = System.IO.Path.GetDirectoryName( System.Reflection.Assembly.GetExecutingAssembly(). GetName().CodeBase) + “test.sdf“; public SQLCE() / TODO: 在此处添加构造函数逻辑 / 初始化数据库; public static bool Init() try String sConn = “Data So

3、urce = “ + sData; if (conn = null) | (conn.State = ConnectionState.Closed) bool bCreate = false; if (!File.Exists(SQLCE.sData) String connStr = “Data Source = “ + sData; SqlCeEngine engine = new SqlCeEngine(sConn); engine.CreateDatabase();/创建数据库 engine.Dispose(); bCreate = true; conn = new SqlCeConn

4、ection(sConn); cmd = new SqlCeCommand(); cmd.Connection = conn; conn.Open(); if (conn.State = ConnectionState.Closed) MessageBox.Show(“连接数据库失败“, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return false; if (bCreate) SQLExec(Common.SQL_TEST);/ 建表语句 Messag

5、eBox.Show(“数据库初始化成功“); catch MessageBox.Show(“连接数据库失败“, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return false; return true; / 封闭数据库; public static bool Close() try cmd.Connection.Close(); conn.Close(); return true; catch return false; / 将查询结果封装到 DataS

6、et中 / sql 语句 / 调用方定义的 DataSet / 成功返回 true 失败返回 false public static bool SQLQuery(string sSQL, ref DataSet ds) try if (conn.State = ConnectionState.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); if (ds = null) ds = new DataSet(); adp.Fill(ds); catch MessageBox.Show(“查询失

7、败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return false; return true; / 将 查询结果封装到 lv 中; / sql 语句 / 外界定义的 ListView / 查询成功返回 true 错误返回 false public static bool SQLQuery(string sSQL, ref ListView lv) try if (conn.State = ConnectionSt

8、ate.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); lv.Items.Clear(); int iRecordCount = ds.Tables0.Rows.Count; if (iRecordCount 表名 / 字段 public static int SQLMaxValue(string sTable, string sField) string sSQL = “select max(“ + s

9、Field + “) as MAXVALUE from “ + sTable; try if (conn.State = ConnectionState.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); if (ds.Tables.Count 0) if (ds.Tables0.Rows.Count 0) return Common.StrToIntDef(ds.Tables0.Rows00.ToStrin

10、g(), 0); catch MessageBox.Show(“查询失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return -1; return 1; / 查询单个值; / sql 语句; / 返回结果字符串 public static string SQLValue(string sSQL) try if (conn.State = ConnectionState.Closed) conn.Open(); S

11、qlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); if (ds.Tables.Count 0) if (ds.Tables0.Rows.Count 0) return ds.Tables0.Rows00.ToString(); catch MessageBox.Show(“查询失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageB

12、oxDefaultButton.Button1); return “; return “; / 不带事务的执行语句 public static bool SQLExec(string sSQL) try if (conn.State = ConnectionState.Closed) conn.Open(); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); catch MessageBox.Show(“执行失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Excla

13、mation, MessageBoxDefaultButton.Button1); return false; return true; / 带事务的执行方式; / sql 语句 / 是否有事务提交 public static bool SQLExec(string sSQL, bool bCommit) try if (conn.State = ConnectionState.Closed) conn.Open(); if (!bCommit) trans = conn.BeginTransaction(); cmd.Transaction = trans; cmd.CommandText

14、= sSQL; cmd.ExecuteNonQuery(); if (bCommit) if (trans != null) trans.Commit(); trans = null; catch trans.Rollback(); MessageBox.Show(“执行失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return false; return true; /将 dataSet 传进往 再传出来; pu

15、blic static bool SQLExist(string sSQL) try if (conn.State = ConnectionState.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); if (ds.Tables.Count 0) if (ds.Tables0.Rows.Count 0) /ds.Tables0.Rows00.ToString(); 数据格式; return true; ca

16、tch MessageBox.Show(“查询失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return true; return false; /执行存储过程 public static bool SQLProc(string sProcName, params ProcParam pProcParams) try if (conn.State = ConnectionState.Closed) conn.Ope

17、n(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = sProcName; cmd.Parameters.Clear(); SqlCeParameter para = cmd.Parameters.Add(“RETURN_VALUE“, SqlDbType.Int); para.Direction = ParameterDirection.ReturnValue; for (int i = 0; i 0) for (int i = 0; i 0) if (bBlank) cmb.Items.Add(“); f

18、or (int i = 0; i sql 语句 / 调用方定义的 DataSet / 成功返回 true 失败返回 false public static bool SQLQuery(string sSQL, ref DataSet ds) try if (conn.State = ConnectionState.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); if (ds = null) ds = new DataSet(); adp.Fill(ds); catch MessageBo

19、x.Show(“查询失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return false; return true; / 将 查询结果封装到 lv 中; / sql 语句 / 外界定义的 ListView / 查询成功返回 true 错误返回 false public static bool SQLQuery(string sSQL, ref ListView lv) try if (conn.State = C

20、onnectionState.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); lv.Items.Clear(); int iRecordCount = ds.Tables0.Rows.Count; if (iRecordCount 表名 / 字段 public static int SQLMaxValue(string sTable, string sField) string sSQL = “selec

21、t max(“ + sField + “) as MAXVALUE from “ + sTable; try if (conn.State = ConnectionState.Closed) conn.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); if (ds.Tables.Count 0) if (ds.Tables0.Rows.Count 0) return Common.StrToIntDef(ds.Tables0.Row

22、s00.ToString(), 0); catch MessageBox.Show(“查询失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return -1; return 1; / 查询单个值; / sql 语句; / 返回结果字符串 public static string SQLValue(string sSQL) try if (conn.State = ConnectionState.Closed) con

23、n.Open(); SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn); DataSet ds = new DataSet(); adp.Fill(ds); if (ds.Tables.Count 0) if (ds.Tables0.Rows.Count 0) return ds.Tables0.Rows00.ToString(); catch MessageBox.Show(“查询失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamatio

24、n, MessageBoxDefaultButton.Button1); return “; return “; / 不带事务的执行语句 public static bool SQLExec(string sSQL) try if (conn.State = ConnectionState.Closed) conn.Open(); cmd.CommandText = sSQL; cmd.ExecuteNonQuery(); catch MessageBox.Show(“执行失败, SQL “ + sSQL + “ “, “提示“, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1); return false; return true; / 带事务的执行方式; / sql 语句 / 是否有事务提交 public static bool SQLExec(string sSQL, bool bCommit) try if (conn.State = ConnectionState.Closed) conn.Open(); if (!bCommit) trans = conn.BeginTransaction(); cmd.Transaction = trans;