public IDataReader Select(string table,
string[] columns,
string where,
object[] whereParamValues,
string orderBy,
int pageNumber,
int pageSize,
string identityColumn,
bool identityColumnIsNumber = true,
DbTransaction transaction = null)
{
ISqlStatementFactory sqlStatementFactory = this.database.GetSqlStatementFactory();
string selectRangeSqlStatement = sqlStatementFactory.CreateSelectRangeStatement(table, where, orderBy, pageSize, (pageNumber - 1) * pageSize, identityColumn, identityColumnIsNumber, null, columns);
string[] whereParamNames = this.database.DiscoverParams(where);
DbCommand selectRangeCommand = this.PrepareSqlStringCommand(selectRangeSqlStatement,
whereParamNames.ToArray(),
null,
whereParamValues.ToArray());
if (transaction != null)
{
return(this.database.ExecuteReader(selectRangeCommand, transaction));
}
else
{
return(this.database.ExecuteReader(selectRangeCommand));
}
}
public IDataReader SelectReader(string table,
string[] columns,
string where,
object[] whereValues,
string orderBy,
string groupBy,
int pageNumber,
int pageSize,
string identityColumn,
bool identityColumnIsNumber = true)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("The table name cannot be null or empty.");
}
if (pageNumber <= 0)
{
throw new ArgumentOutOfRangeException("pageNumber", pageNumber, "The pageNumber is one-based and should be larger than zero.");
}
if (pageSize <= 0)
{
throw new ArgumentOutOfRangeException("pageSize", pageSize, "The pageSize is one-based and should be larger than zero.");
}
string[] whereParamNames = this.database.DiscoverParams(where);
if (whereParamNames != null &&
(whereValues == null ||
!whereParamNames.Length.Equals(whereValues.Length)))
{
throw new ArgumentException("The length of parameter values in where Sql should equal the length of parameter names in where Sql if where is not null or empty.");
}
ISqlStatementFactory sqlStatementFactory = this.database.GetSqlStatementFactory();
string selectRangeQuerySql = sqlStatementFactory.CreateSelectRangeStatement(table,
where,
orderBy,
pageSize,
(pageNumber - 1) * pageSize,
identityColumn,
identityColumnIsNumber,
groupBy,
columns);
DbCommand command = this.PrepareSqlStringCommand(whereParamNames, null, whereValues, selectRangeQuerySql);
return(this.database.ExecuteReader(command));
}
public void Delete(string table, string where, DbType[] whereDbTypes, object[] whereValues, DbTransaction transaction)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("The table name cannot be null or empty.");
}
string[] whereParamNames = this.database.GetParsedParamNames(where);
if (whereParamNames != null &&
(whereValues == null ||
whereValues.Length.Equals(0)))
{
throw new ArgumentNullException("The parameter values in where Sql cannot be null or zero if where is not null or empty.");
}
if (whereParamNames != null &&
whereDbTypes != null &&
!whereDbTypes.Length.Equals(whereParamNames.Length))
{
throw new ArgumentException("The length of parameter types in where Sql should equal the length of parameter names in where Sql if where is not null or empty.");
}
if (whereParamNames != null &&
(whereValues == null ||
!whereParamNames.Length.Equals(whereValues.Length)))
{
throw new ArgumentException("The length of parameter values in where Sql should equal the length of parameter names in where Sql if where is not null or empty..");
}
ISqlStatementFactory sqlStatementFactory = this.database.DBProvider.CreateStatementFactory();
string deleteSqlStatement = sqlStatementFactory.CreateDeleteStatement(table, where);
DbCommand command = this.PrepareSqlStringCommand(whereParamNames,
whereParamNames == null ? null : whereDbTypes,
whereParamNames == null ? null : whereValues,
deleteSqlStatement);
if (transaction == null)
{
this.database.ExecuteNonQuery(command);
}
else
{
this.database.ExecuteNonQuery(command, transaction);
}
}
public DataSet SelectDataSet(string table,
string[] columns,
string where,
DbType[] whereDbTypes,
object[] whereValues,
string orderBy,
DbTransaction transaction)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("The table name cannot be null or empty.");
}
string[] whereParamNames = this.database.DiscoverParams(where);
if (whereParamNames != null &&
whereDbTypes != null &&
!whereParamNames.Length.Equals(whereDbTypes.Length))
{
throw new ArgumentException("The length of parameter types in where Sql should equal the length of parameter names in where Sql if where is not null or empty.");
}
if (whereParamNames != null &&
(whereValues == null ||
!whereParamNames.Length.Equals(whereValues.Length)))
{
throw new ArgumentException("The length of parameter values in where Sql should equal the length of parameter names in where Sql if where is not null or empty.");
}
ISqlStatementFactory sqlStatementFactory = this.database.DBProvider.CreateStatementFactory();
string selectSqlStatement = sqlStatementFactory.CreateSelectStatement(table, where, orderBy, columns);
DbCommand command = this.PrepareSqlStringCommand(whereParamNames, whereDbTypes, whereValues, selectSqlStatement);
if (transaction == null)
{
return(this.database.ExecuteDataSet(command));
}
else
{
return(this.database.ExecuteDataSet(command, transaction));
}
}
/// <summary>
/// Update a table with where condition.
/// </summary>
public void Update(string table,
string[] columns,
DbType[] dbTypes,
object[] values,
string where,
DbType[] whereDbTypes,
object[] whereValues,
DbTransaction transaction)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("The table name cannot be null or empty.");
}
if (columns == null ||
columns.Length.Equals(0))
{
throw new ArgumentNullException("The column names of parameters cannot be null or zero.");
}
if (values == null ||
values.Length.Equals(0))
{
throw new ArgumentNullException("The values of parameters cannot be null or zero.");
}
if (!columns.Length.Equals(values.Length))
{
throw new ArgumentException("The length of columns should equal the length of parameter values.");
}
if (dbTypes != null &&
!columns.Length.Equals(dbTypes.Length))
{
throw new ArgumentException("The length of columns should equal the length of parameter db types.");
}
string[] whereParamNames = this.database.GetParsedParamNames(where);
if (whereParamNames != null &&
whereDbTypes != null &&
!whereParamNames.Length.Equals(whereDbTypes.Length))
{
throw new ArgumentException("The length of parameter db types in where sql should equal the length of parameter names in where sql if sql is not null or empty.");
}
if (whereParamNames != null &&
whereValues != null &&
!whereParamNames.Length.Equals(whereValues.Length))
{
throw new ArgumentException("The length of parameter names in where sql should equal the length of parameter values in where sql if sql is not null or empty.");
}
ISqlStatementFactory sqlStatementFactory = this.database.DBProvider.CreateStatementFactory();
string updateSqlStatement = sqlStatementFactory.CreateUpdateStatement(table, where, columns);
List <string> allParamNames = new List <string>();
List <DbType> allParamDbTypes = new List <DbType>();
List <object> allParamValues = new List <object>();
for (int columnIndex = 0; columnIndex < columns.Length; columnIndex++)
{
allParamNames.Add(columns[columnIndex]);
if (dbTypes != null)
{
allParamDbTypes.Add(dbTypes[columnIndex]);
}
allParamValues.Add(values[columnIndex]);
}
if (whereParamNames != null)
{
for (int whereParamIndex = 0; whereParamIndex < whereParamNames.Length; whereParamIndex++)
{
allParamNames.Add(whereParamNames[whereParamIndex]);
if (whereDbTypes != null)
{
allParamDbTypes.Add(whereDbTypes[whereParamIndex]);
}
allParamValues.Add(whereValues[whereParamIndex]);
}
}
DbCommand command = this.PrepareSqlStringCommand(allParamNames.ToArray(),
allParamDbTypes.Count.Equals(0)? null : allParamDbTypes.ToArray(),
allParamValues.ToArray(),
updateSqlStatement);
if (transaction == null)
{
this.database.ExecuteNonQuery(command);
}
else
{
this.database.ExecuteNonQuery(command, transaction);
}
}
public int Insert(string table, string[] columns, DbType[] dbTypes, object[] values, DbTransaction transaction)
{
if (string.IsNullOrEmpty(table))
{
throw new ArgumentNullException("The table name cannot be null or empty.");
}
if (values == null ||
values.Length == 0)
{
throw new ArgumentNullException("The values of parameters cannot be null or zero.");
}
if (columns != null &&
dbTypes != null &&
!columns.Length.Equals(dbTypes.Length))
{
throw new ArgumentException("The length of columns of parameter doesn't equal the length of db types of parameters.");
}
if (columns != null &&
!columns.Length.Equals(values.Length))
{
throw new ArgumentException("The length of columns of parameter doesn't equal the length of values of parameters.");
}
if (columns == null &&
dbTypes != null &&
!dbTypes.Length.Equals(values.Length))
{
throw new ArgumentException("The length of db types of parameter doesn't equal the length of values of parameters.");
}
ISqlStatementFactory statementFactory = this.database.DBProvider.CreateStatementFactory();
string insertSqlStatement = statementFactory.CreateInsertStatement(table, columns);
string[] paramNames;
if (columns == null)
{
paramNames = new string[values.Length];
string paramNamesSql = string.Empty;
for (int paramIndex = 0; paramIndex < values.Length; paramIndex++)
{
paramNames[paramIndex] = "@column_" + (paramIndex + 1).ToString();
paramNamesSql += paramNames[paramIndex] + ",";
}
insertSqlStatement = string.Format(insertSqlStatement, paramNamesSql.TrimEnd(','));
}
else
{
paramNames = columns;
}
DbCommand insertCommand = this.PrepareSqlStringCommand(paramNames, dbTypes, values, insertSqlStatement);
object returnValue;
if (!this.database.IsBatchConnection &&
!string.IsNullOrEmpty(this.database.DBProvider.SelectLastInsertedRowAutoIDStatement))
{
if (!this.database.DBProvider.SelectLastInsertedRowAutoIDStatement.StartsWith("SELECT SEQ_"))
{
insertCommand.CommandText = insertCommand.CommandText.Trim(';') + " ; " + this.database.DBProvider.SelectLastInsertedRowAutoIDStatement;
if (transaction == null)
{
returnValue = this.database.ExecuteScalar(insertCommand);
}
else
{
returnValue = this.database.ExecuteScalar(insertCommand, transaction);
}
}
else
{
returnValue = this.database.ExecuteScalar(CommandType.Text,
string.Format(this.database.DBProvider.SelectLastInsertedRowAutoIDStatement, table));
if (transaction == null)
{
this.database.ExecuteNonQuery(insertCommand);
}
else
{
this.database.ExecuteNonQuery(insertCommand, transaction);
}
}
if (returnValue != null &&
returnValue != DBNull.Value)
{
return(Convert.ToInt32(returnValue));
}
}
else
{
if (transaction == null)
{
returnValue = this.database.ExecuteNonQuery(insertCommand);
}
else
{
returnValue = this.database.ExecuteNonQuery(insertCommand, transaction);
}
if (returnValue != null &&
returnValue != DBNull.Value)
{
return(Convert.ToInt32(returnValue));
}
}
return(0);
}
public MySqlDbProvider(string connectionString) :
base(connectionString, MySqlClientFactory.Instance)
{
this.sqlStatementFactory = new MySqlStatementFactory(this);
}
public OracleDbProvider(string connectionString) :
base(connectionString, OracleClientFactory.Instance)
{
this.sqlStatementFactory = new OracleStatementFactory(this);
}
public SqlLiteDbProvider(string connectionString) :
base(connectionString, System.Data.SQLite.SQLiteFactory.Instance)
{
this.sqlStatementFactory = new SqlLiteStatementFactory(this);
}
