public DataSet ExecuteTableQueryToDataSet(string table, string[] fieldNames, string[] conditionFields, object[] conditionValues, int?commandTimeout = null)
{
var index = 0;
var paramNames = AdoCommandHelper.CreateArray("@condition{0}", conditionFields.Length);
return(ExecuteQueryWithParamToDataSet(string.Format("SELECT {0} FROM {1} WHERE {2};",
BuildSelectFields(fieldNames),
QuoteIdentifier(table)
, string.Join(" AND ", conditionFields.Select(x => string.Format("{0} = @condition{1}", QuoteIdentifier(x), index++)))),
paramNames, conditionValues,
commandTimeout: commandTimeout));
}
public bool CheckExist(string table, string[] conditionFields, object[] conditionValues, int?commandTimeout = null)
{
var index = 0;
var paramNames = AdoCommandHelper.CreateArray("@condition{0}", conditionFields.Length);
var result = ExecuteScalarWithParam(string.Format("SELECT 1 FROM {0} WHERE {1};",
QuoteIdentifier(table)
, string.Join(" AND ", conditionFields.Select(x => string.Format("{0} = @condition{1}", QuoteIdentifier(x), index++)))),
paramNames, conditionValues,
commandTimeout: commandTimeout);
return(result != null && result != DBNull.Value);
}
public long ExecuteTableCount(string table, string[] conditionFields, object[] conditionValues, int?commandTimeout = null)
{
var index = 0;
var paramNames = AdoCommandHelper.CreateArray("@condition{0}", conditionFields.Length);
var count = ExecuteScalarWithParam(string.Format("SELECT COUNT(1) FROM {0} WHERE {1};",
QuoteIdentifier(table),
string.Join(" AND ", conditionFields.Select(x => string.Format("{0} = @condition{1}", QuoteIdentifier(x), index++)))),
paramNames, conditionValues,
commandTimeout: commandTimeout);
return(Convert.ToInt64(count));
}
public long ExecuteTableCountIn(string table, string conditionField, object[] conditionValues, int?commandTimeout = null)
{
var paramNames = AdoCommandHelper.CreateArray("@condition{0}", conditionValues.Length);
var count = ExecuteScalarWithParam(
string.Format("SELECT COUNT(1) FROM {0} WHERE {1} IN ({2});",
QuoteIdentifier(table),
QuoteIdentifier(conditionField),
string.Join(",", paramNames)),
paramNames, conditionValues,
commandTimeout: commandTimeout);
return(Convert.ToInt64(count));
}
public int ExecuteDeleteIn(string table, string conditionField, object[] conditionValues, int?commandTimeout = null)
{
var paramNames = AdoCommandHelper.CreateArray("@value{0}", conditionValues.Length);
var result = ExecuteNonQueryWithParam(string.Format("DELETE FROM {0} WHERE {1} IN ({2});",
QuoteIdentifier(table),
QuoteIdentifier(conditionField), string.Join(",", paramNames)),
paramNames, conditionValues, false,
commandTimeout: commandTimeout);
if (result > 0)
{
var dataChange = BuildDeleteDataChange(table, conditionField, conditionValues);
RequestFireDataChanged(dataChange);
}
return(result);
}
public int ExecuteSerialInsert(string table, string[] fieldNames, DbType[] fieldTypes, IEnumerable <object[]> insertValueSets, int?commandTimeout = null)
{
var paramNames = AdoCommandHelper.CreateArray("@value{0}", fieldNames.Length);
var result = ExecuteSerialNonQueryWithParam(string.Format("INSERT INTO {0}({1}) VALUES({2});",
QuoteIdentifier(table),
string.Join(",", fieldNames.Select(x => QuoteIdentifier(x))),
string.Join(",", paramNames)),
paramNames, fieldTypes, insertValueSets, false,
commandTimeout: commandTimeout);
if (result > 0)
{
var dataChange = BuildInsertDataChange(table, fieldNames, insertValueSets);
RequestFireDataChanged(dataChange);
}
return(result);
}
public object ExecuteScalar(string table, string scalarField, string[] conditionFields, object[] conditionValues, int?commandTimeout = null)
{
if (string.IsNullOrWhiteSpace(scalarField))
{
scalarField = "1";
}
else
{
scalarField = QuoteIdentifier(scalarField);
}
var index = 0;
var paramNames = AdoCommandHelper.CreateArray("@condition{0}", conditionFields.Length);
return(ExecuteScalarWithParam(string.Format("SELECT {0} FROM {1} WHERE {2};",
scalarField,
QuoteIdentifier(table),
string.Join(" AND ", conditionFields.Select(x => string.Format("{0} = @condition{1}", QuoteIdentifier(x), index++)))),
paramNames, conditionValues,
commandTimeout: commandTimeout));
}
public object ExecuteScalarIn(string table, string scalarField, string conditionField, object[] conditionValues, int?commandTimeout = null)
{
if (string.IsNullOrWhiteSpace(scalarField))
{
scalarField = "1";
}
else
{
scalarField = QuoteIdentifier(scalarField);
}
var paramNames = AdoCommandHelper.CreateArray("@condition{0}", conditionValues.Length);
return(ExecuteScalarWithParam(
string.Format("SELECT {0} FROM {1} WHERE {2} IN ({3});",
scalarField,
QuoteIdentifier(table),
QuoteIdentifier(conditionField),
string.Join(",", paramNames)),
paramNames, conditionValues,
commandTimeout: commandTimeout));
}
public int ExecuteUpdate(string table, string[] conditionFields, object[] conditionValues, string[] updateFieldNames, object[] updateFieldValues, int?commandTimeout = null)
{
if (conditionFields == null || conditionValues == null || updateFieldNames == null || updateFieldValues == null)
{
throw new ArgumentNullException();
}
if (conditionFields.Length != conditionValues.Length || updateFieldNames.Length != updateFieldValues.Length)
{
throw new ArgumentException("Number of values is different from number of values.");
}
var N = updateFieldNames.Length;
var M = conditionFields.Length;
var index = 0;
var setPairs = string.Join(", ", updateFieldNames.Select(x => string.Format("{0} = @value{1}", QuoteIdentifier(x), index++)));
var conditionPairs = string.Join(", ", conditionFields.Select(x => string.Format("{0} = @value{1}", QuoteIdentifier(x), index++)));
var values = new List <object>();
values.AddRange(updateFieldValues);
values.AddRange(conditionValues);
var result = ExecuteNonQueryWithParam(
string.Format("UPDATE {0} SET {1} WHERE {2};", QuoteIdentifier(table), setPairs, conditionPairs),
AdoCommandHelper.CreateArray("@value{0}", N + M),
values.ToArray(), false,
commandTimeout: commandTimeout);
if (result > 0)
{
var dataChange = BuildUpdateDataChange(table, conditionFields, conditionValues);
RequestFireDataChanged(dataChange);
}
return(result);
}