|
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("连接数据库失败",?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?+?"?]",?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("查询失败,?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("查询失败,?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("查询失败,?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("执行失败,?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("执行失败,?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("查询失败,?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(),?MessageBoxDefaultButton.Button1);
?????return?false;
????}
???}
???catch(Exception?e)
???...{
????MessageBox.Show(e.Message?+?"调用存储过程失败",?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("查询失败,?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("查询失败,?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;
??}
?}
}
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
