c#开发的运行在wm5.0环境中的sqlce数据库操作。

最新推荐文章于 2013-11-18 19:59:00 发布

allen3010

最新推荐文章于 2013-11-18 19:59:00 发布

阅读量2.3k

点赞数

分类专栏： C# 文章标签：数据库 c# sql dataset null 存储

C# 专栏收录该内容

34 篇文章 2 订阅

订阅专栏

这是数据库的封装类：
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;
}
}
}
源码上传了！