WinCE.NET 上SQLServer CE3.0 C#数量I/O操作方法集
using
System;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Windows.Forms;
using System.IO;
namespace SmartTerm.Pub
... {
/**//// <summary>
/// SQLCE 的摘要说明。
/// </summary>
public class SQLCE
...{
public static SqlCeEngine eng = null;
public static SqlCeConnection conn = null;
public static SqlCeTransaction trans = null;
public static SqlCeCommand cmd = null;
public static string sData = @" ";
public static string sBackup = @"";
public static string sConn = @" ";
public SQLCE()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static bool Init()
...{
try
...{
if ((conn == null) || (conn.State == ConnectionState.Closed))
...{
bool bCreate = false;
if (!File.Exists(sData))
...{
eng = new SqlCeEngine(sConn);
eng.CreateDatabase();
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;
}
conn.Close();
if (bCreate)
...{
SQLExec(Common.SQL_OPERINFO);
SQLExec(Common.SQL_ORDERBILL);
SQLExec(Common.SQL_ORDERITEM);
SQLExec(Common.SQL_BUYBILL);
SQLExec(Common.SQL_BUYITEM);
}
}
}
catch
...{
MessageBox.Show("连接数据库失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return false;
}
return true;
}
public static bool Close()
...{
try
...{
conn.Close();
return true;
}
catch
...{
return 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("查询失败, SQL[ " + sSQL + " ]", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return false;
}
return true;
}
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;
}
}
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;
}
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;
}
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;
}
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)
...{
if (Common.StrToIntDef(ds.Tables[0].Rows[0][0].ToString(), 0) > 0)
...{
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;
}
}
}
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Windows.Forms;
using System.IO;
namespace SmartTerm.Pub
... {
/**//// <summary>
/// SQLCE 的摘要说明。
/// </summary>
public class SQLCE
...{
public static SqlCeEngine eng = null;
public static SqlCeConnection conn = null;
public static SqlCeTransaction trans = null;
public static SqlCeCommand cmd = null;
public static string sData = @" ";
public static string sBackup = @"";
public static string sConn = @" ";
public SQLCE()
...{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static bool Init()
...{
try
...{
if ((conn == null) || (conn.State == ConnectionState.Closed))
...{
bool bCreate = false;
if (!File.Exists(sData))
...{
eng = new SqlCeEngine(sConn);
eng.CreateDatabase();
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;
}
conn.Close();
if (bCreate)
...{
SQLExec(Common.SQL_OPERINFO);
SQLExec(Common.SQL_ORDERBILL);
SQLExec(Common.SQL_ORDERITEM);
SQLExec(Common.SQL_BUYBILL);
SQLExec(Common.SQL_BUYITEM);
}
}
}
catch
...{
MessageBox.Show("连接数据库失败", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return false;
}
return true;
}
public static bool Close()
...{
try
...{
conn.Close();
return true;
}
catch
...{
return 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("查询失败, SQL[ " + sSQL + " ]", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
return false;
}
return true;
}
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;
}
}
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;
}
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;
}
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;
}
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)
...{
if (Common.StrToIntDef(ds.Tables[0].Rows[0][0].ToString(), 0) > 0)
...{
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;
}
}
}