// only support clause "GROUP", "HAVING", "ORDER"
// Or, users can modify public property SensitiveKeywords to add more
// Or, developer can modify the initialization of the protected variable strSensitiveKeywords
// to enable more clauses in the class constructor
/// <summary>
/// RestrictReturnRecords
/// </summary>
/// <param name="string strCommandText"></param>
/// <param name="RecordsRestrictnInfo"></param>
/// <returns>string</returns>
protected string RestrictReturnRecords(string strCommandText, RecordsRestrictInfo recordsRestrictInfo)
{
ArrayList lstSensitiveKeywords;
StringBuilder tmpString = new StringBuilder();
string strTemp = string.Empty, strTableShortName = string.Empty;
lstSensitiveKeywords = this.CheckSensitiveKeywords(strCommandText, this.lstSensitiveKeyWords);
strCommandText = strCommandText.ToUpper();
tmpString.Append("SELECT TOP ");
tmpString.Append(recordsRestrictInfo.RecordsCount);
tmpString.Append(" ");
if (strCommandText.IndexOf("WHERE") != -1)
{
tmpString.Append(strCommandText.Substring(strCommandText.IndexOf("SELECT") + 7, strCommandText.IndexOf("WHERE")));
}
else
{
if (lstSensitiveKeywords.Count == 0)
{
tmpString.Append(strCommandText.Substring(strCommandText.IndexOf("SELECT") + 7));
}
else
{
tmpString.Append(strCommandText.Substring(strCommandText.IndexOf("SELECT") + 7, strCommandText.IndexOf(lstSensitiveKeywords[0].ToString()) - lstSensitiveKeywords[0].ToString().Length - 2));
}
}
if (recordsRestrictInfo.PrimaryKey != string.Empty && recordsRestrictInfo.PrimaryKey != null)
{
if (strCommandText.IndexOf("WHERE") != -1)
{
strTemp = strCommandText.Substring(strCommandText.IndexOf("FROM"), strCommandText.IndexOf("WHERE"));
}
else
{
strTemp = strCommandText.Substring(strCommandText.IndexOf("FROM"));
}
if (strCommandText.IndexOf("WHERE") == -1)
{
tmpString.Append(" WHERE ");
}
// Get the table short-cut name
if (strTemp.IndexOf(",") != -1)
{
strTableShortName = strTemp.Substring(0, strTemp.IndexOf(","));
}
else
{
strTableShortName = strTemp;
}
strTableShortName = strTableShortName.Substring(strTableShortName.IndexOf("FROM ") + 6);
strTableShortName = strTableShortName.Substring(strTableShortName.IndexOf(" ") + 1);
if (strTemp.IndexOf(",") != -1)
{
tmpString.Append(strTableShortName + ".");
}
tmpString.Append(recordsRestrictInfo.PrimaryKey + " NOT IN (SELECT TOP ");
tmpString.Append(recordsRestrictInfo.StartPosition);
if (strTemp.IndexOf(",") != -1)
{
tmpString.Append(" " + strTableShortName + ".");
}
else
{
tmpString.Append(" ");
}
tmpString.Append(recordsRestrictInfo.PrimaryKey + " ");
tmpString.Append(strCommandText.Substring(strCommandText.IndexOf("FROM ")));
tmpString.Append(")");
if (strCommandText.IndexOf("WHERE") != -1)
{
tmpString.Append(" AND " + strCommandText.Substring(strCommandText.IndexOf("WHERE ") + 6));
}
strCommandText = tmpString.ToString();
}
tmpString.Remove(0, tmpString.Length);
tmpString = null;
return(strCommandText);
}
/// <summary>
/// ExecuteCommandScript
/// </summary
/// <param name="CommandType commandType"></param>
/// <param name="string commandText"></param>
/// <returns>DataSet</returns>
public DataSet ExecuteCommandScript(string commandText, RecordsRestrictInfo recordsRestrictInfo, params SqlParameter [] parameters)
{
string tmpCommand = string.Empty;
bool bConnectionOpened = true;
DataSet dsResult = new DataSet();
if (this.sqlDbConnection.State == ConnectionState.Closed)
{
throw new Exception("Database Connection is closed.");
}
if (this.dataAdapter == null)
{
this.dataAdapter = new SqlDataAdapter();
}
if (this.command == null)
{
this.command = new SqlCommand();
}
SqlTransaction transaction = this.sqlDbConnection.BeginTransaction();
tmpCommand = this.RestrictReturnRecords(commandText, recordsRestrictInfo);
try
{
this.command.Connection = this.sqlDbConnection;
this.command.Transaction = transaction;
this.command.CommandType = CommandType.Text;
this.command.CommandText = tmpCommand;
this.command.CommandTimeout = 300; //extend the timeout from default 30 to 300
if (parameters != null)
{
if (!this.AttachSQLParameters(this.command, parameters))
{
throw new Exception("Failed to attach the SQL parameters to command.");
}
}
this.dataAdapter.SelectCommand = this.command;
this.dataAdapter.Fill(dsResult);
transaction.Commit();
if (!bConnectionOpened)
{
this.sqlDbConnection.Close();
}
return(dsResult);
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception exc)
{
this.ErrorLog(exc);
}
this.ErrorLog(ex);
return(null);
}
finally
{
dsResult.Dispose();
}
}
