using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Data.Common;
namespace Foundation
{
/// <summary>
/// 本类为：SQLCE数据库的一些基本操作。在使用前，先将数据库的连接字符串确认好，然后使用；
/// 本类为rebel2004整理，目的为了方便大家更好的使用SQLCE数据库；
/// 本类为开源代码；
/// 有使用本类所产生的一切后果由使用者自负
/// </summary>
public class DataBase
{
private String Server, dbName, UID, Password;
private String SqlCeConnectionString;
private SqlCeConnection con;
public DataBase()
{
// 初始化数据库连接字符串
Server = "Input Correct string!";
dbName = "Input Correct string!";
UID = "Input Correct string!";
Password = "Input Correct string!";
SqlCeConnectionString = "user id=" + UID + ";password=" + Password + ";initial catalog=" + dbName + ";data source=" + Server + ";Connect Timeout=30";
}
/// <summary>
/// 打开数据库连接。
/// </summary>
private void Open()
{
if (con == null)
{
con = new SqlCeConnection(SqlCeConnectionString);
con.Open();
}
}
/// <summary>
/// 关闭数据库连接。
/// </summary>
public void Close()
{
if (con != null)
{
con.Close();
this.Dispose();
}
}
/// <summary>
/// Release resources.
/// 释放资源。
/// </summary>
public void Dispose()
{
// make sure connection is closed
if (con != null)
{
con.Dispose();
con = null;
}
}
/// <summary>
/// 测试数据库连接是否成功
/// </summary>
/// <param name="server">server</param>
/// <param name="database">database</param>
/// <param name="uid">用户名</param>
/// <param name="password">密码</param>
/// <returns>bool</returns>
public bool TestConnection(string server, string database, string uid, string password)
{
try
{
con = null;
SqlCeConnectionString = "user id=" + uid + ";password=" + password + ";initial catalog=" + database + ";data source=" + server + " ";
this.Open();
}
catch
{
return false;
}
return true;
}
/// <summary>
/// 创建command对象以便执行SqlCe语句。
/// </summary>
/// <param name="SqlCe">SqlCe Text.</param>
/// <returns>Command object.</returns>
private SqlCeCommand CreateCommand(string SqlCe)
{
// make sure connection is open
Open();
SqlCeCommand cmd = new SqlCeCommand(SqlCe, con);
cmd.CommandType = CommandType.Text;
return cmd;
}
/// <summary>
/// 创建带Prameters的Command对象
/// </summary>
/// <param name="SqlCeCe">SqlCe语句</param>
/// <param name="prams">SqlCeParameters参数</param>
/// <returns>Command对象</returns>
public SqlCeCommand CreateCommand(String SqlCe, SqlCeParameter[] prams)
{
Open();
SqlCeCommand cmd = new SqlCeCommand(SqlCe,con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
if (prams != null)
{
foreach (SqlCeParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
return cmd;
}
/// <summary>
/// 执行一个无返回的SqlCe语句
/// </summary>
/// <param name="SqlCe">SqlCe语句</param>
/// <returns>执行结果</returns>
public bool QueryExec(string SqlCe)
{
SqlCeCommand cmd = CreateCommand(SqlCe);
try
{
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
ex.Message.ToString();
return false;
}
finally
{
this.Close();
}
return true;
}
/// <summary>
/// 执行一个无返回的SqlCe语句，带parameters
/// </summary>
/// <param name="SqlCe">SqlCe语句</param>
/// <returns>执行结果</returns>
public bool QueryExec(string SqlCe, SqlCeParameter[] parms)
{
SqlCeCommand cmd = CreateCommand(SqlCe, parms);
try
{
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
ex.Message.ToString();
return false;
}
finally
{
this.Close();
}
return true;
}
/// <summary>
/// 执行一个插入记录操作，返回primary key
/// </summary>
/// <param name="SqlCe">insert SqlCe语句</param>
/// <returns>返回的primary key</returns>
public String InsertExec(string SqlCe)
{
SqlCe += ";SELECT @@identity AS [@@IDENTITY];";
SqlCeCommand cmd = CreateCommand(SqlCe);
try
{
return cmd.ExecuteScalar().ToString();
}
catch
{
return null;
}
finally
{
this.Close();
}
}
/// <summary>
/// 执行一个插入记录操作，带parameters，返回primary key
/// </summary>
/// <param name="SqlCe">insert SqlCe语句</param>
/// <returns>返回的primary key</returns>
public String InsertExec(string SqlCe, SqlCeParameter[] prams)
{
SqlCe += ";SELECT @@identity AS [@@IDENTITY];";
SqlCeCommand cmd = CreateCommand(SqlCe, prams);
try
{
return cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.Close();
}
}
/// <summary>
/// 通过查询指定的SqlCe语句来获得一个返回值
/// </summary>
/// <param name="SqlCe">SqlCe语句</param>
/// <returns>返回值</returns>
public String QueryValue(string SqlCe)
{
SqlCeCommand cmd = CreateCommand(SqlCe);
try
{
return cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.Close();
}
}
/// <summary>
/// 通过查询指定的SqlCe语句来获得一个返回表，带parms
/// </summary>
/// <param name="SqlCe"></param>
/// <param name="prams"></param>
/// <returns></returns>
public DataTable QueryDataTable(string SqlCe,SqlCeParameter[] parms)
{
DataTable dataTable = new DataTable();
SqlCeCommand cmd = CreateCommand(SqlCe,parms);
try
{
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd);
dataAdapter.Fill(dataTable);
}
catch(Exception ex)
{
ex.Message.ToString();
dataTable = null;
}
finally
{
this.Close();
}
return dataTable;
}
/// <summary>
/// 通过查询指定的SqlCe语句来获得一个返回值，带parms
/// </summary>
/// <param name="SqlCe">SqlCe语句</param>
/// <returns>返回值</returns>
public String QueryValue(string SqlCe, SqlCeParameter[] prams)
{
SqlCeCommand cmd = CreateCommand(SqlCe, prams);
try
{
return cmd.ExecuteScalar().ToString();
}
catch(Exception ex)
{
ex.Message.ToString();
return null;
}
finally
{
this.Close();
}
}
/// <summary>
/// 查询返回DATAREADER
/// </summary>
/// <param name="dr"></param>
/// <param name="error"></param>
/// <param name="SqlCeConnectionString"></param>
/// <param name="SqlCe">SqlCe语句</param>
public SqlCeDataReader QueryDataReader(string SqlCe)
{
try
{
SqlCeCommand cmd = CreateCommand(SqlCe);
return cmd.ExecuteReader();
}
catch
{
return null;
}
finally
{
}
}
/// <summary>
/// 查询返回DATASET
/// </summary>
/// <param name="SqlCe">SqlCe语句</param>
/// <returns>DataSet对象</returns>
public DataSet QueryDataSet (string SqlCe)
{
SqlCeCommand cmd = CreateCommand(SqlCe);
DataSet ds = new DataSet();
try
{
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd);
dataAdapter.Fill(ds);
}
catch(Exception ex)
{
ex.Message.ToString();
ds = null;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 通过SqlCe语句返回DataTable。
/// </summary>
/// <returns>DataTable</returns>
public DataTable QueryDataTable(string SqlCe)
{
DataTable dataTable = new DataTable();
SqlCeCommand cmd = CreateCommand(SqlCe);
try
{
SqlCeDataAdapter dataAdapter = new SqlCeDataAdapter(cmd);
dataAdapter.Fill(dataTable);
}
catch(Exception ex)
{
ex.Message.ToString();
dataTable = null;
}
finally
{
this.Close();
}
return dataTable;
}
/// <summary>
/// 包装输入参数。
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public SqlCeParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
/// <summary>
/// 包装输出参数。
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <returns>New parameter.</returns>
public SqlCeParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
public SqlCeParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, Value);
}
/// <summary>
/// 包装Command参数。
/// </summary>
/// <param name="ParamName">Name of param.</param>
/// <param name="DbType">Param type.</param>
/// <param name="Size">Param size.</param>
/// <param name="Direction">Parm direction.</param>
/// <param name="Value">Param value.</param>
/// <returns>New parameter.</returns>
public SqlCeParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlCeParameter param;
if(Size > 0)
param = new SqlCeParameter(ParamName, DbType, Size);
else
param = new SqlCeParameter(ParamName, DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
else Value = param.Value;
return param;
}
}
}
不好意思，到现在才将代码补充上来！今天在公司写了一天的和移动开发无关的代码，好累啊！手好冷啊！电热毯咋坏了呢！这不是欺负我没MONEY嘛。。。。。。
|