- 这是数据库的封装类:
- 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 SqlCeTransaction 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 Source = " + 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 SqlCeConnection(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);// 建表语句
- }
- }
- MessageBox.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;
- }
- }
- /// 将查询结果封装到DataSet中
- /// <param name="sSQL"> sql 语句 </param>
- /// <param name="ds"> 调用方定义的DataSet </param>
- /// <returns> 成功返回true 失败返回false </returns>
- 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("查询失败, SQL[ " + sSQL + " ]", "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- MessageBoxDefaultButton.Button1);
- return false;
- }
- return true;
- }
- /// 将 查询结果封装到 lv 中;
- /// <param name="sSQL">sql语句</param>
- /// <param name="lv">外界定义的 ListView </param>
- /// <returns> 查询成功返回true 错误返回false </returns>
- public static bool SQLQuery(string sSQL, ref ListView lv)
- {
- try
- {
- if (conn.State == ConnectionState.Closed)
- {
- conn.Open();
- }
- SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn);
- DataSet ds = new DataSet();
- adp.Fill(ds);
- lv.Items.Clear();
- int iRecordCount = ds.Tables[0].Rows.Count;
- if (iRecordCount <= 0)
- {
- return true;
- }
- for (int i = 0; i < iRecordCount; i++)
- {
- string[] item = new string[ds.Tables[0].Columns.Count];
- for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
- {
- item[j] = ds.Tables[0].Rows[i][j].ToString();
- }
- lv.Items.Add(new ListViewItem(item));
- }
- //Common.AutoWidth(ref lv);
- return true;
- }
- catch
- {
- MessageBox.Show("查询失败, SQL[ " + sSQL + " ]", "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- MessageBoxDefaultButton.Button1);
- return false;
- }
- }
- /// 查询某表 的 某个字段的最大值;
- /// <param name="sTable">表名</param>
- /// <param name="sField">字段</param>
- public static int SQLMaxValue(string sTable, string sField)
- {
- string sSQL = "select 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.Tables[0].Rows.Count > 0)
- {
- return Common.StrToIntDef(ds.Tables[0].Rows[0][0].ToString(), 0);
- }
- }
- }
- catch
- {
- MessageBox.Show("查询失败, SQL[ " + sSQL + " ]", "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- MessageBoxDefaultButton.Button1);
- return -1;
- }
- return 1;
- }
- /// 查询单个值;
- /// <param name="sSQL">sql语句;</param>
- /// <returns>返回结果字符串</returns>
- public static string SQLValue(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.Tables[0].Rows.Count > 0)
- {
- return ds.Tables[0].Rows[0][0].ToString();
- }
- }
- }
- catch
- {
- MessageBox.Show("查询失败, SQL[ " + sSQL + " ]", "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- 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;
- }
- /// 带事务的执行方式;
- /// <param name="sSQL">sql语句</param>
- /// <param name="bCommit">是否有事务提交</param>
- 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 = 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 传进去 再传出来;
- public 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.Tables[0].Rows.Count > 0)
- { //ds.Tables[0].Rows[0][0].ToString(); 数据格式;
- return true;
- }
- }
- }
- catch
- {
- 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.Open();
- }
- 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 < pProcParams.Length; i++)
- {
- para = cmd.Parameters.Add(pProcParams[i].ParamName, pProcParams[i].ParamType);
- para.Value = pProcParams[i].ParamValue;
- }
- para = cmd.Parameters.Add("@err_msg", SqlDbType.VarChar, 80);
- para.Direction = ParameterDirection.Output;
- cmd.ExecuteNonQuery();
- if (Common.StrToIntDef(cmd.Parameters["@RETURN_VALUE"].Value.ToString(), -1) < 0)
- {
- MessageBox.Show(cmd.Parameters["@err_msg"].Value.ToString(), "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- MessageBoxDefaultButton.Button1);
- return false;
- }
- }
- catch (Exception e)
- {
- MessageBox.Show(e.Message + "调用存储过程失败", "提示", MessageBoxButtons.OK,
- MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
- return false;
- }
- return true;
- }
- public static void CmbAdd(ref System.Windows.Forms.ComboBox cmb, string sSQL)
- {
- try
- {
- if (conn.State == ConnectionState.Closed)
- {
- conn.Open();
- }
- SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn);
- DataSet ds = new DataSet();
- adp.Fill(ds);
- cmb.Items.Clear();
- if (ds.Tables.Count > 0)
- {
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- cmb.Items.Add(ds.Tables[0].Rows[i][0].ToString() + "-" + ds.Tables[0].Rows[i][1].ToString());
- }
- cmb.SelectedIndex = 0;
- }
- }
- catch
- {
- MessageBox.Show("查询失败, SQL[ " + sSQL + " ]", "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- MessageBoxDefaultButton.Button1);
- }
- }
- public static void CmbAdd(ref System.Windows.Forms.ComboBox cmb, string sSQL, bool bBlank)
- {
- try
- {
- if (conn.State == ConnectionState.Closed)
- {
- conn.Open();
- }
- SqlCeDataAdapter adp = new SqlCeDataAdapter(sSQL, conn);
- DataSet ds = new DataSet();
- adp.Fill(ds);
- cmb.Items.Clear();
- if (ds.Tables.Count > 0)
- {
- if (bBlank)
- {
- cmb.Items.Add("");
- }
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- cmb.Items.Add(ds.Tables[0].Rows[i][0].ToString() + "-" +
- ds.Tables[0].Rows[i][1].ToString());
- }
- cmb.SelectedIndex = 0;
- }
- }
- catch
- {
- MessageBox.Show("查询失败, SQL[ " + sSQL + " ]", "提示",
- MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
- MessageBoxDefaultButton.Button1);
- }
- }
- }
- public class ProcParam
- {
- public string ParamName;
- public SqlDbType ParamType;
- public object ParamValue;
- public ProcParam(string Name, SqlDbType Type, object Value)
- {
- ParamName = Name;
- ParamType = Type;
- ParamValue = Value;
- }
- }
- }
- 源码上传了!
c#开发的运行在wm5.0环境中的sqlce数据库操作。
最新推荐文章于 2013-11-18 19:59:00 发布