public List <T> ExecuteAsListText <T>(string sqlCommand)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = sqlCommand;
SQLCmd.CommandType = CommandType.Text;
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
List <T> mList = new List <T>();
mList = DataSourceHelper.FillCollection <T>(SQLReader);
if (SQLReader != null)
{
SQLReader.Close();
}
SQLConn.Close();
return(mList);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Execute As List
/// </summary>
/// <typeparam name="T"><T></typeparam>
/// <param name="StroredProcedureName">Storedprocedure Name</param>
/// <returns></returns>
public async Task <IList <T> > ExecuteAsListAsync <T>(string StroredProcedureName)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
try
{
SqlDataReader SQLReader;
await SQLConn.OpenAsync();
SQLReader = await SQLCmd.ExecuteReaderAsync(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
IList <T> mList = DataSourceHelper.FillCollection <T>(SQLReader);
if (SQLReader != null)
{
SQLReader.Close();
}
SQLConn.Close();
return(mList);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
public static List <LanguageSwitchKeyValue> GetLanguageSwitchSettings(int portalId, int UserModuleID)
{
List <LanguageSwitchKeyValue> lstSettings = new List <LanguageSwitchKeyValue>();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", portalId));
ParaMeterCollection.Add(new KeyValuePair <string, object>("@UserModuleID", UserModuleID));
string StoredProcedureName = "usp_loc_GetLanguageSwitchSettings";
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
lstSettings.Add(new LanguageSwitchKeyValue(SQLReader["SettingKey"].ToString(), SQLReader["SettingValue"].ToString()));
}
SQLReader.Close();
return(lstSettings);
}
public static List <Language> GetAvailableLocales()
{
List <Language> lstAvailableLocales = new List <Language>();
string StoredProcedureName = "sp_LanguageGet";
SqlDataReader SQLReader;
try
{
SqlConnection SQLConn = new SqlConnection(SystemSetting.SageFrameConnectionString);
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StoredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader();
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
Language obj = new Language(int.Parse(SQLReader["LanguageID"].ToString()), SQLReader["CultureName"].ToString(), SQLReader["CultureCode"].ToString());
obj.LanguageN = SQLReader["CultureName"].ToString();
obj.Country = SQLReader["CultureName"].ToString();
lstAvailableLocales.Add(obj);
}
SQLReader.Close();
return(lstAvailableLocales);
}
public static List <ModuleInfo> GetCoreModules()
{
List <ModuleInfo> lstCoreModules = new List <ModuleInfo>();
string StoredProcedureName = "usp_loc_CoreModulesGet";
SqlDataReader SQLReader;
try
{
SqlConnection SQLConn = new SqlConnection(SystemSetting.SageFrameConnectionString);
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StoredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader();
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
ModuleInfo obj = new ModuleInfo();
obj.ModuleID = int.Parse(SQLReader["ModuleID"].ToString());
obj.ModuleName = SQLReader["ModuleName"].ToString();
lstCoreModules.Add(obj);
}
SQLReader.Close();
return(lstCoreModules);
}
public static List <Language> GetPortalLanguages(int portalId)
{
List <Language> lstPortalLanguages = new List <Language>();
List <KeyValuePair <string, object> > ParaMeterCollection = new List <KeyValuePair <string, object> >();
ParaMeterCollection.Add(new KeyValuePair <string, object>("@PortalID", portalId));
string StoredProcedureName = "usp_loc_PortalLanguagesGet";
SqlDataReader SQLReader;
try
{
SQLHandler sagesql = new SQLHandler();
SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
}
catch (Exception e)
{
throw e;
}
while (SQLReader.Read())
{
Language obj = new Language(int.Parse(SQLReader["LanguageID"].ToString()), SQLReader["CultureName"].ToString(), SQLReader["CultureCode"].ToString());
obj.LanguageN = SQLReader["CultureName"].ToString();
obj.Country = SQLReader["CultureName"].ToString();
lstPortalLanguages.Add(obj);
}
SQLReader.Close();
return(lstPortalLanguages);
}
/// <summary>
/// Execute As list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="StroredProcedureName">StoreProcedure Name</param>
/// <param name="ParaMeterCollection"></param>
/// <returns></returns>
public IList <T> ExecuteAsList <T>(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
IList <T> mList = DataSourceHelper.FillCollection <T>(SQLReader);
if (SQLReader != null)
{
SQLReader.Close();
}
SQLConn.Close();
return(mList);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Execute As enumerable
/// </summary>
/// <typeparam name="T"><T></typeparam>
/// <param name="StroredProcedureName">Storedprocedure Name</param>
/// <returns></returns>
public IEnumerable <T> ExecuteAsEnumerable <T>(string StroredProcedureName)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
IEnumerable <T> mList = DataSourceHelper.FillCollection <T>(SQLReader);
if (SQLReader != null)
{
SQLReader.Close();
}
SQLConn.Close();
return(mList);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Execute as object.
/// </summary>
/// <typeparam name="T">Given type of object.</typeparam>
/// <param name="StroredProcedureName">Store procedure name.</param>
/// <param name="ParaMeterCollection">Accept key value collection for parameters.</param>
/// <returns></returns>
public T ExecuteAsObject <T>(string StroredProcedureName, List <KeyValuePair <string, object> > ParaMeterCollection)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
//Loop for Parameters
for (int i = 0; i < ParaMeterCollection.Count; i++)
{
SqlParameter sqlParaMeter = new SqlParameter();
sqlParaMeter.IsNullable = true;
sqlParaMeter.ParameterName = ParaMeterCollection[i].Key;
sqlParaMeter.Value = ParaMeterCollection[i].Value;
SQLCmd.Parameters.Add(sqlParaMeter);
}
//End of for loop
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList arrColl = DataSourceHelper.FillCollection(SQLReader, typeof(T));
SQLConn.Close();
if (SQLReader != null)
{
SQLReader.Close();
}
if (arrColl != null && arrColl.Count > 0)
{
return((T)arrColl[0]);
}
else
{
return(default(T));
}
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Execute As Object
/// </summary>
/// <typeparam name="T"><T></typeparam>
/// <param name="StroredProcedureName">StoreProcedure Name</param>
/// <param name="ParaMeterCollection">Accept Key Value Collection For Parameters</param>
/// <returns></returns>
public async Task <T> ExecuteAsObjectAsync <T>(string StroredProcedureName, List <SQLParam> ParaMeterCollection)
{
using (SqlConnection SQLConn = new SqlConnection(this._connectionString))
{
using (SqlCommand SQLCmd = new SqlCommand())
{
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SqlParameter[] sqlParameters = new SQLParamCollection(ParaMeterCollection).ParamCollection;
SQLCmd.Parameters.AddRange(sqlParameters);
try
{
SqlDataReader SQLReader;
await SQLConn.OpenAsync();
SQLReader = await SQLCmd.ExecuteReaderAsync(CommandBehavior.CloseConnection);
ArrayList arrColl = DataSourceHelper.FillCollection(SQLReader, typeof(T));
SQLConn.Close();
if (SQLReader != null)
{
SQLReader.Close();
}
if (arrColl != null && arrColl.Count > 0)
{
return((T)arrColl[0]);
}
else
{
return(default(T));
}
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
}
}
/// <summary>
/// Execute as list.
/// </summary>
/// <typeparam name="T">Given type of object</typeparam>
/// <param name="StroredProcedureName">Store procedure name.</param>
/// <param name="ParaMeterCollection">Accept Key Value collection for parameters.</param>
/// <returns>Type of list of object implementing.</returns>
public List <T> ExecuteAsList <T>(string StroredProcedureName, List <KeyValuePair <string, string> > ParaMeterCollection)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
//Loop for Paramets
for (int i = 0; i < ParaMeterCollection.Count; i++)
{
SqlParameter sqlParaMeter = new SqlParameter();
sqlParaMeter.IsNullable = true;
sqlParaMeter.ParameterName = ParaMeterCollection[i].Key;
sqlParaMeter.Value = ParaMeterCollection[i].Value;
SQLCmd.Parameters.Add(sqlParaMeter);
}
//End of for loop
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader(CommandBehavior.CloseConnection); //datareader automatically closes the SQL connection
List <T> mList = new List <T>();
mList = DataSourceHelper.FillCollection <T>(SQLReader);
if (SQLReader != null)
{
SQLReader.Close();
}
SQLConn.Close();
return(mList);
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}
/// <summary>
/// Execute As Object
/// </summary>
/// <typeparam name="T">Given type of object.</typeparam>
/// <param name="StroredProcedureName">Accept Key Value Collection For Parameters</param>
/// <returns> Type of the object implementing</returns>
public T ExecuteAsObject <T>(string StroredProcedureName)
{
SqlConnection SQLConn = new SqlConnection(this._connectionString);
try
{
SqlDataReader SQLReader;
SqlCommand SQLCmd = new SqlCommand();
SQLCmd.Connection = SQLConn;
SQLCmd.CommandText = StroredProcedureName;
SQLCmd.CommandType = CommandType.StoredProcedure;
SQLConn.Open();
SQLReader = SQLCmd.ExecuteReader();
ArrayList arrColl = DataSourceHelper.FillCollection(SQLReader, typeof(T));
SQLConn.Close();
if (SQLReader != null)
{
SQLReader.Close();
}
if (arrColl != null && arrColl.Count > 0)
{
return((T)arrColl[0]);
}
else
{
return(default(T));
}
}
catch (Exception e)
{
throw e;
}
finally
{
SQLConn.Close();
}
}