internal static DataSet GetDataSet(string environment, SqlCommand cmd)
{
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
var dataSet = new DataSet("NewDataSet");
try
{
connection.Open();
cmd.Connection = connection;
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataSet);
}
catch (Exception e)
{
Util.SendErrorMail("SiteManagement", e.Message + ';' + e.StackTrace);
throw e;
}
finally
{
connection.Close();
}
return(dataSet);
}
/// <summary>
/// Copy the huge data using this method
/// </summary>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="sourceCmd"><c>sourceCmd is source database SqlCommand</c></param>
/// <param name="destConnStr"><c>destConnStr is dest Sql connection string</c></param>
/// <param name="destTable"><c>destTable is dest Sql table name</c></param>
internal static void BulkCopy(string environment, SqlCommand sourceCmd, String destConnStr, String destTable)
{
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection sourceConnection = sourceCmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
using (sourceConnection)
{
sourceConnection.Open();
SqlDataReader reader = sourceCmd.ExecuteReader();
using (var destinationConnection = new SqlConnection(destConnStr))
{
destinationConnection.Open();
using (var bulkCopy = new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName = destTable;
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
throw ex;
}
finally
{
reader.Close();
}
}
}
}
}
/// <summary>
/// <c>The GetObject method getting data from the database based on SqlCommand</c>
/// </summary>
/// <typeparam name="T"><c>T isGeneric datatype</c></typeparam>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
/// <returns>returns the IList generic object</returns>
internal static T GetValue <T>(string environment, SqlCommand cmd)
{
T returnValue = default(T);
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
try
{
connection.Open();
cmd.Connection = connection;
object value = cmd.ExecuteScalar();
if (value != null && value != DBNull.Value)
{
returnValue = (T)value;
}
}
catch (Exception e)
{
Util.SendErrorMail("healthywayz", e.Message + ';' + e.StackTrace);
throw e;
}
finally
{
connection.Close();
}
return(returnValue);
}
/// <summary>
/// Get the Client Connection String
/// </summary>
/// <returns>returns the connection string</returns>
//internal static string GetConString()
//{
// string strConfigConnection = ConfigurationManager.AppSettings["ConnectionString"];
// return strConfigConnection;
//}
/// <summary>
/// Execute any DML command thru this method
/// </summary>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
private static void ExecuteSql(string environment, SqlCommand cmd)
{
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
try
{
connection.Open();
cmd.Connection = connection;
cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
/// <summary>
/// <c>The GetObject method data from the database based on SqlCommand</c>
/// </summary>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
/// <returns>returns the IList generic object</returns>
internal static string GetXMLObjects(string environment, SqlCommand cmd)
{
string result = string.Empty;
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
XmlReader reader = null;
try
{
connection.Open();
cmd.Connection = connection;
reader = cmd.ExecuteXmlReader();
reader.Read();
//result = reader[0].ToString();
while (reader.ReadState != System.Xml.ReadState.EndOfFile)
{
result = reader.ReadOuterXml();
}
}
catch (Exception e)
{
Util.SendErrorMail("healthywayz", e.Message + ';' + e.StackTrace);
throw e;
}
finally
{
if (reader != null)
{
reader.Close();
}
connection.Close();
}
return(result);
}
/// <summary>
/// Execute any DML command thru this method
/// </summary>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
/// <param name="strStatus"><c>strStatus is returns value from the database</c></param>
/// <returns>returns effect rows number</returns>
private static int ExecuteSql(string environment, SqlCommand cmd, string strStatus)
{
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
try
{
connection.Open();
cmd.Connection = connection;
int rowsAffetected = cmd.ExecuteNonQuery();
return(rowsAffetected);
}
catch (Exception)
{
return(0);
}
finally
{
connection.Close();
}
}
/// <summary>
/// <c>The GetObject method data from the database based on SqlCommand</c>
/// </summary>
/// <typeparam name="T"><c>T isGeneric datatype</c></typeparam>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
/// <param name="itemFactory">itemFactory is Delegate object</param>
/// <returns>returns the IList generic object</returns>
internal static List <T> GetObjects <T>(string environment, SqlCommand cmd, ItemFactoryDelegate itemFactory)
{
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
SqlDataReader reader = null;
var result = new List <T>();
try
{
connection.Open();
cmd.Connection = connection;
reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add((T)itemFactory(reader));
}
}
catch (Exception e)
{
Util.SendErrorMail("SiteManagement", e.Message + ';' + e.StackTrace);
throw e;
}
finally
{
if (reader != null)
{
reader.Close();
}
connection.Close();
}
return(result);
}
/// <summary>
/// <c>The GetSortedObjects method getting sorted data from the database based on SqlCommand</c>
/// </summary>
/// <typeparam name="TKey">TKey is sorted key value</typeparam>
/// <typeparam name="TValue">TValue is sorted values</typeparam>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
/// <param name="itemFactory">itemFactory is Delegate object</param>
/// <returns>returns the IDictionary generic object</returns>
internal static IDictionary <TKey, TValue> GetSortedObjects <TKey, TValue>(string environment, SqlCommand cmd, ItemFactoryDelegate itemFactory)
{
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
SqlDataReader reader = null;
IDictionary <TKey, TValue> result = new Dictionary <TKey, TValue>();
try
{
connection.Open();
cmd.Connection = connection;
reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add((TKey)reader[0], (TValue)itemFactory(reader));
}
}
catch (Exception e)
{
Util.SendErrorMail("healthywayz", e.Message + ';' + e.StackTrace);
throw e;
}
finally
{
if (reader != null)
{
reader.Close();
}
connection.Close();
}
return(result);
}
/// <summary>
/// <c>The GetObject method data from the database based on SqlCommand</c>
/// </summary>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <param name="cmd"><c>cmd is SqlCommand</c></param>
/// <returns>returns the IList generic object</returns>
internal static string GetObjects(string environment, SqlCommand cmd)
{
string result;
SsConnection ssconn = SSDAL.Instance.ConnService.GetClientConnection(environment);
SqlConnection connection = cmd.Connection ?? new SqlConnection(ssconn.EnterpriseConnectionString);
SqlDataReader reader = null;
try
{
connection.Open();
cmd.Connection = connection;
reader = cmd.ExecuteReader();
reader.Read();
result = Convert.ToString(reader[0]);
}
catch (Exception e)
{
Util.SendErrorMail("healthywayz", e.Message + ';' + e.StackTrace);
throw e;
}
finally
{
if (reader != null)
{
reader.Close();
}
connection.Close();
}
return(result);
}
/// <summary>
/// Get the client connection string
/// </summary>
/// <param name="environment"><c>environment is either dev, stag or prod</c></param>
/// <returns><c>returns SsConnection object</c></returns>
public SsConnection GetClientConnection(string environment)
{
string keyValue = string.Format("{0}", environment);
if (!(_ssconnections.ContainsKey(keyValue) && !_ssconnections[keyValue].IsExpired()))
{
lock (_sslock)
{
if (!(_ssconnections.ContainsKey(keyValue) && !_ssconnections[keyValue].IsExpired()))
{
if (_ssconnections.ContainsKey(keyValue))
{
_ssconnections.Remove(keyValue);
}
_ssconnections.Add(keyValue, new ObjectCache <SsConnection>(new SsConnection(GetConnectionString(environment))));
}
}
}
SsConnection result = _ssconnections[keyValue].ObjectItem;
return(result);
}