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 } }