public static SPDataProxy.SPLookupDataTable GetSearchResults(string SearchType, string SubType, string SearchFor, string SearchItem, string ConnectionString)
{
if (string.IsNullOrEmpty(ConnectionString))
{
using (SoftLogik.Win.SPDataProxyTableAdapters.taSPLookup lookupAdapter = new SoftLogik.Win.SPDataProxyTableAdapters.taSPLookup())
{
return lookupAdapter.GetSearchResults(SearchType, SubType, SearchFor, SearchItem);
}
}
else
{
SoftLogik.Win.Data.ISPDataStore lookupAdapter = new SQLDataStore(new System.Data.SqlClient.SqlConnection(ConnectionString));
SPDataParamCollection objParams = new SPDataParamCollection();
objParams.Add("@SearchType", SearchType);
objParams.Add("@SubType", SubType);
objParams.Add("@SearchFor", SearchFor);
objParams.Add("@SearchItem", SearchItem);
DataTable genericSearchTable = lookupAdapter.GetTable("SPLookup_Search", ref objParams, false);
SPDataProxy.SPLookupDataTable searchTable = new SPDataProxy.SPLookupDataTable();
searchTable.Load(genericSearchTable.CreateDataReader());
return searchTable;
}
}
private static bool ProcessParams(SqlCommand SourceCommand, SPDataParamCollection SourceParams)
{
bool boolFoundParam = false;
try
{
try
{
if (SourceCommand.Connection.State == ConnectionState.Closed)
{
SourceCommand.Connection.Open();
}
SqlCommandBuilder.DeriveParameters(SourceCommand);
}
catch (Exception)
{
}
foreach (SPDataParam targetParam in SourceParams)
{
boolFoundParam = false;
foreach (SqlParameter sourceParam in SourceCommand.Parameters)
{
if (sourceParam.ParameterName.ToUpper() == targetParam.Name.ToUpper())
{
sourceParam.Value = targetParam.Value;
boolFoundParam = true;
break;
}
}
//if not found in the source list add it
if (boolFoundParam == false)
{
SourceCommand.Parameters.Add(targetParam);
}
}
}
catch (Exception)
{
}
}
public static SqlDataReader ExecuteSQL_DR(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
{
SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
objCmd.CommandType = CommandType.Text;
ProcessParams(objCmd, @Params); //Parse Parameters
objCmd.Connection.Open();
using (objCmd)
{
return objCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
return objCmd.ExecuteReader();
// objCmd.Dispose();
// objCmd = null;
}
public static DataTable ExecuteSQL_DT(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
{
SqlDataAdapter daExecuteSP = new SqlDataAdapter();
DataSet dsExecuteSP = new DataSet();
SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
objCmd.CommandType = CommandType.Text;
ProcessParams(objCmd, @Params); //Parse Parameters
daExecuteSP.SelectCommand = objCmd;
daExecuteSP.Fill(dsExecuteSP);
return dsExecuteSP.Tables[0];
// daExecuteSP.Dispose();
// daExecuteSP = null;
// objCmd.Dispose();
// objCmd = null;
}
public static object ExecuteSQL(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
{
SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
objCmd.CommandType = CommandType.Text;
ProcessParams(objCmd, @Params); //Parse Parameters
return objCmd.ExecuteScalar();
// objCmd.Dispose();
// objCmd = null;
}
public static DataTable ExecuteSP_DT(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection, bool Direct)
{
DataTable dsExecuteSP = new DataTable();
SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
SqlDataAdapter taData = new SqlDataAdapter(objCmd);
if (Direct)
{
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "SELECT * FROM " + ExecuteText;
}
else
{
objCmd.CommandType = CommandType.StoredProcedure;
}
objCmd.Connection.Open();
ProcessParams(objCmd, @Params); //Parse Parameters
taData.Fill(dsExecuteSP);
objCmd.Connection.Close();
return dsExecuteSP;
// objCmd.Dispose();
// objCmd = null;
}
public static object ExecuteSP(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
{
SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection.Open();
ProcessParams(objCmd, @Params); //Parse Parameters
using (objCmd)
{
try
{
return objCmd.ExecuteScalar();
}
catch (Exception)
{
throw;
}
}
}
public static string ExecuteSP_Str(string ExecuteText, ref SPDataParamCollection Params, ref SqlConnection AltConnection)
{
SqlCommand objCmd = new SqlCommand(ExecuteText, BuildConnection(AltConnection));
IDataReader drData;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection.Open();
ProcessParams(objCmd, @Params); //Parse Parameters
using (objCmd)
{
drData = objCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
if (drData != null)
{
drData.Read();
try
{
return drData.GetString(0);
}
catch (Exception)
{
return Constants.vbNullString;
}
}
return Constants.vbNullString;
}
public System.Data.DataTable GetTable(string ExecuteText, ref SPDataParamCollection Params, bool Direct)
{
RegExpressions.StatementPatternType stMatch = RegExpressions.StatementMatch(ExecuteText.ToUpper());
switch (stMatch)
{
case RegExpressions.StatementPatternType.SelectStatement:
return DataSupport.ExecuteSQL_DT(ExecuteText, ref @Params, ref m_conConnection); //Select Statement
case RegExpressions.StatementPatternType.InsertStatement:
case RegExpressions.StatementPatternType.UpdateStatement:
case RegExpressions.StatementPatternType.DeleteStatement:
throw (new Exception("Invalid argument encountered in ExecuteText."));
break;
default:
return DataSupport.ExecuteSP_DT(ExecuteText, ref @Params, ref m_conConnection, Direct); //Stored Procedure
}
}
public string GetString(string ExecuteText, ref SPDataParamCollection Params)
{
RegExpressions.StatementPatternType stMatch = RegExpressions.StatementMatch(ExecuteText.ToUpper());
switch (stMatch)
{
case RegExpressions.StatementPatternType.SelectStatement:
//Return ExecuteSQL_Str(ExecuteText, Params, m_conConnection) 'Select Statement
return Constants.vbNullString;
case RegExpressions.StatementPatternType.InsertStatement:
case RegExpressions.StatementPatternType.UpdateStatement:
case RegExpressions.StatementPatternType.DeleteStatement:
throw (new Exception("Invalid argument encountered in ExecuteText."));
break;
default:
return DataSupport.ExecuteSP_Str(ExecuteText, ref @Params, ref m_conConnection); //Stored Procedure
}
}
public object ExecuteCommand(string ExecuteText, ref SPDataParamCollection Params)
{
RegExpressions.StatementPatternType stMatch = RegExpressions.StatementMatch(ExecuteText.ToUpper());
switch (stMatch)
{
case RegExpressions.StatementPatternType.InsertStatement:
case RegExpressions.StatementPatternType.SelectStatement:
case RegExpressions.StatementPatternType.UpdateStatement:
case RegExpressions.StatementPatternType.DeleteStatement:
return DataSupport.ExecuteSQL(ExecuteText, ref @Params, ref m_conConnection); //DML Statement
default:
return DataSupport.ExecuteSP(ExecuteText, ref @Params, ref m_conConnection); //Stored Procedure
}
}
