/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject对象</param>
/// <returns>DataSet</returns>
public DataSet ExecuteDataSet(ExecuteObject sender)
{
DataSet set;
using (SqlCeConnection connection = new SqlCeConnection(this.ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand())
{
CreateCommand(command, sender);
command.Connection = connection;
connection.Open();
try
{
using (SqlCeDataAdapter adapter = new SqlCeDataAdapter(command))
{
set = new DataSet();
adapter.Fill(set);
}
}
finally { }
}
}
return(set);
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Boolean</returns>
public Boolean ExecuteCommand(ExecuteObject sender)
{
Boolean flag;
using (SqlCeConnection connection = new SqlCeConnection(this.ConnectionString))
{
using (SqlCeCommand command = new SqlCeCommand())
{
CreateCommand(command, sender);
command.Connection = connection;
connection.Open();
using (SqlCeTransaction transaction = command.Connection.BeginTransaction())
{
try
{
command.Transaction = transaction;
int num = command.ExecuteNonQuery();
transaction.Commit();
flag = num >= 0;
}
catch (Exception ex)
{
transaction.Rollback();
throw new ApplicationException(ex.Message);
}
finally { }
}
}
}
return(flag);
}
public static Boolean ExecuteCommand(ExecuteObject sender, String DbName)
{
IDbCommand command;
Boolean flag;
CreateCommand(out command, sender, DbName);
IDbTransaction transaction = command.Connection.BeginTransaction();
command.Transaction = transaction;
try
{
int num = command.ExecuteNonQuery();
transaction.Commit();
flag = num >= 0;
}
catch (Exception exception)
{
transaction.Rollback();
throw new ApplicationException(exception.Message);
}
finally
{
if (command != null)
{
transaction.Dispose();
command.Connection.Close();
command.Dispose();
}
}
return(flag);
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Json字符串</returns>
public String ExecuteJson(ExecuteObject sender)
{
String json = String.Empty;
using (IDataReader reader = ExecuteReader(sender))
{
ConvertToJson(reader, ref json);
}
return(json);
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="sender">ExecuteObject</param>
/// <returns>泛型</returns>
public T ExecuteObject <T>(ExecuteObject sender)
{
T result = Activator.CreateInstance <T>();
using (IDataReader reader = ExecuteReader(sender))
{
SetValue <T>(reader, ref result);
}
return(result);
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>IDataReader</returns>
public IDataReader ExecuteReader(ExecuteObject sender)
{
SqlCeDataReader reader;
using (SqlCeCommand command = new SqlCeCommand())
{
SqlCeConnection connection = new SqlCeConnection(this.ConnectionString);
CreateCommand(command, sender);
command.Connection = connection;
connection.Open();
try
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
finally { }
}
return(reader);
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Object</returns>
public Object ExecuteScalar(ExecuteObject sender)
{
Object obj;
using (SqlConnection connection = new SqlConnection(this.ConnectionString))
{
using (SqlCommand command = new SqlCommand())
{
CreateCommand(command, sender);
command.Connection = connection;
connection.Open();
try
{
obj = command.ExecuteScalar();
}
finally { }
}
}
return(obj);
}
public static IDataReader ExecuteReader(ExecuteObject sender, String DbName)
{
IDbCommand command;
IDataReader reader;
CreateCommand(out command, sender, DbName);
try
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception exception)
{
throw new ApplicationException(exception.Message);
}
finally
{
if (command != null)
{
command.Connection.Close();
command.Dispose();
}
}
return(reader);
}
public static DataSet ExecuteDataSet(ExecuteObject sender, String DbName)
{
IDbCommand command;
DataSet set;
CreateCommand(out command, sender, DbName);
try
{
IDbDataAdapter dataAdapter = GetDataAdapter();
dataAdapter.SelectCommand = command;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
set = dataSet;
}
finally
{
if (command.Connection.State != ConnectionState.Closed)
{
command.Connection.Close();
command.Dispose();
}
}
return(set);
}
public Object ExecuteScalar(ExecuteObject sender)
{
throw new NotImplementedException();
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>DataTable</returns>
public DataTable ExecuteDataTable(ExecuteObject sender)
{
return(ExecuteDataSet(sender).Tables[0]);
}
public static DataTable ExecuteDataTable(ExecuteObject sender, String DbName)
{
return(ExecuteDataSet(sender, DbName).Tables[0]);
}
private void CreateCommand(SqlCeCommand cmd, ExecuteObject sender)
{
String sqlStr = String.Empty;
String sqlField = String.Empty;
String sqlWhere = String.Empty;
String sqlParam = String.Empty;
String sqlValue = String.Empty;
Dictionary <String, Type> schema = GetTableSchema(sender.tableName);
#region 构造字段
foreach (String key in schema.Keys)
{
if (sender.cells.Contains(key))
{
if (sender.cmdtype == CmdType.INSERT)
{
sqlParam = sqlParam + "[" + key + "],";
sqlValue = sqlValue + "@" + key + ",";
}
else if (sender.cmdtype == CmdType.SELECT)
{
sqlField = sqlField + "[" + key + "],";
}
else
{
sqlField = sqlField + "[" + key + "]=@" + key + ",";
}
if (sender.cmdtype != CmdType.SELECT && sender.cmdtype != CmdType.DELETE)
{
cmd.Parameters.Add(new SqlCeParameter(key, sender.cells[key]));
}
}
}
if (sender.cmdtype == CmdType.INSERT)
{
sqlField = "(" + sqlParam.Substring(0, sqlParam.Length - 1) + ") VALUES (" + sqlValue.Substring(0, sqlValue.Length - 1) + ")";
}
else if (sqlField != "")
{
sqlField = sqlField.Substring(0, sqlField.Length - 1);
}
#endregion
#region 构造条件
if (sender.cmdtype != CmdType.INSERT)
{
foreach (String key in schema.Keys)
{
String _key = key;
if (sender.terms.Contains(_key))
{
if (cmd.Parameters.Contains(_key))
{
_key += "_2";
}
sqlWhere = sqlWhere + "[" + key + "]=@" + _key + " AND ";
cmd.Parameters.Add(new SqlCeParameter(_key, sender.terms[key]));
}
}
}
#endregion
switch (sender.cmdtype)
{
case CmdType.SELECT:
if (sqlField == "")
{
sqlStr = "SELECT * FROM [" + sender.tableName + "] ";
}
else
{
sqlStr = "SELECT " + sqlField + " FROM [" + sender.tableName + "] ";
}
break;
case CmdType.INSERT:
sqlStr = "INSERT INTO [" + sender.tableName + "]" + sqlField;
break;
case CmdType.UPDATE:
sqlStr = "UPDATE [" + sender.tableName + "] SET " + sqlField;
break;
case CmdType.DELETE:
sqlStr = "DELETE FROM [" + sender.tableName + "]";
break;
}
if (sqlWhere != "")
{
sqlWhere = " WHERE " + sqlWhere.Substring(0, sqlWhere.Length - 4);
sqlStr = sqlStr + sqlWhere;
}
cmd.CommandText = sqlStr;
}
public static void CreateCommand(out IDbCommand cmd, ExecuteObject sender, String DbName)
{
String str;
DataTable tableInfo = GetTableInfo(sender.tableName);
cmd = GetCommand(DbName);
String str2 = "";
String str3 = "";
String str4 = "";
String str5 = "";
String str6 = "";
String key = "";
foreach (DataColumn column in tableInfo.Columns)
{
key = column.ColumnName.ToLower();
if (sender.cells.Contains(key))
{
if (sender.cmdtype != CmdType.INSERT)
{
if (sender.cmdtype == CmdType.SELECT)
{
str3 = str3 + "[" + key + "],";
}
else
{
str = str3;
str3 = str + "[" + key + "]=@" + key + ",";
}
}
else
{
str5 = str5 + "[" + key + "],";
str6 = str6 + "@" + key + ",";
}
if (sender.cmdtype != CmdType.SELECT)
{
cmd.Parameters.Add(GetParameter(key, sender.cells[key]));
}
}
}
foreach (DataColumn column in tableInfo.Columns)
{
key = column.ColumnName.ToLower();
if (sender.terms.Contains(key))
{
str = str4;
str4 = str + "[" + key + "]=@" + key + " AND ";
cmd.Parameters.Add(GetParameter(key, sender.terms[key]));
}
}
if (sender.cmdtype == CmdType.INSERT)
{
str3 = "(" + str5.Substring(0, str5.Length - 1) + ") VALUES (" + str6.Substring(0, str6.Length - 1) + ")";
}
else if (str3 != "")
{
str3 = str3.Substring(0, str3.Length - 1);
}
switch (sender.cmdtype)
{
case CmdType.SELECT:
if (str3 == "")
{
str2 = "SELECT * FROM [" + sender.tableName + "] ";
break;
}
str2 = "SELECT " + str3 + " FROM [" + sender.tableName + "] ";
break;
case CmdType.INSERT:
str2 = "INSERT INTO [" + sender.tableName + "]" + str3;
break;
case CmdType.UPDATE:
str2 = "UPDATE [" + sender.tableName + "] SET " + str3;
break;
case CmdType.DELETE:
switch (CurrDbType)
{
case DatabaseType.ACCESS:
case DatabaseType.SQLite:
str2 = "DELETE FROM [" + sender.tableName + "]";
break;
default:
str2 = "DELETE [" + sender.tableName + "]";
break;
}
break;
}
if (str4 != "")
{
str4 = " WHERE " + str4.Substring(0, str4.Length - 4);
str2 = str2 + str4;
}
cmd.CommandText = str2;
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>String</returns>
public static String ExecuteJson(ExecuteObject sender)
{
return(Instance.ExecuteJson(sender));
}
/// <summary>
/// 执行ExecuteObject返回单个字段
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Object</returns>
public static Object ExecuteScalar(ExecuteObject sender)
{
return(Instance.ExecuteScalar(sender));
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>DataTable</returns>
public static DataTable ExecuteDataTable(ExecuteObject sender)
{
return(Instance.ExecuteDataTable(sender));
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataSet(ExecuteObject sender)
{
return(Instance.ExecuteDataSet(sender));
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>Boolean</returns>
public static Boolean ExecuteCommand(ExecuteObject sender)
{
return(Instance.ExecuteCommand(sender));
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <param name="sender">ExecuteObject</param>
/// <returns>IDataReader</returns>
public static IDataReader ExecuteReader(ExecuteObject sender)
{
return(Instance.ExecuteReader(sender));
}
/// <summary>
/// 执行ExecuteObject
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="sender">ExecuteObject</param>
/// <returns>T</returns>
public static T ExecuteObject <T>(ExecuteObject sender)
{
return(Instance.ExecuteObject <T>(sender));
}
public static void CreateCommand(out IDbCommand cmd, ExecuteObject sender)
{
CreateCommand(out cmd, sender, null);
}