sp
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Windows;
using System.Windows.Threading;
using ARLP.Common.DomainModel.DataSourceKeyResolver;
namespace ARLP.Common.DomainModel.StoredProcedure
{
public class StoredProcedureHelper
{
public void GetDataTableBySPAsync(string storedProcedureName, SqlParameter[] param,
Action<bool, string, DataTable> closeCallback,bool dispatchAutomatically = true)
{
try
{
var conn = new SqlConnection(GetConnectionString());
autoDispatch = dispatchAutomatically;
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = storedProcedureName,
Connection = conn
};
CompletedCallbackDT = closeCallback;
if (param != null)
{
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
}
conn.Open();
cmd.BeginExecuteReader(HandleCallbackDT, cmd);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetDataTableBySP(string storedProcedureName, SqlParameter[] param)
{
try
{
var conn = new SqlConnection(GetConnectionString());
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = storedProcedureName,
Connection = conn
};
if (param != null)
{
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
}
conn.Open();
var da = new SqlDataAdapter(cmd);
var dt = new DataTable();
da.Fill(dt);
conn.Close();
da.Dispose();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public void ExecuteSPAsync(string storedProcedureName, SqlParameter[] param, Action<bool, string> closeCallback,
bool dispatchAutomatically = true)
{
try
{
autoDispatch = dispatchAutomatically;
var conn = new SqlConnection(GetConnectionString());
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = storedProcedureName,
Connection = conn
};
CompletedCallback = closeCallback;
cmd.CommandTimeout = 600;
if (param != null)
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
conn.Open();
cmd.BeginExecuteNonQuery(HandleCallbackNonQuery, cmd);
}
catch (Exception ex)
{
throw ex;
}
}
public void ExecuteSP(string storedProcedureName, SqlParameter[] param)
{
try
{
using (var conn = new SqlConnection(GetConnectionString()))
{
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = storedProcedureName,
Connection = conn
};
cmd.CommandTimeout = 600;
if (param != null)
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
public object ExecuteSPScalar(string storedProcedureName, SqlParameter[] param)
{
try
{
using (var conn = new SqlConnection(GetConnectionString()))
{
var cmd = new SqlCommand
{
CommandType = CommandType.StoredProcedure,
CommandText = storedProcedureName,
Connection = conn
};
cmd.CommandTimeout = 600;
if (param != null)
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
conn.Open();
var obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool DetermineDisconnected()
{
// Default to distributed
bool isDistributed = true;
// Check for local settings
var key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("Software\\AMOS");
if (key != null)
{
var value = key.GetValue("RunLocal");
bool convBool = true;
if (value == null)
{
value = string.Empty;
}
if (bool.TryParse(value.ToString(), out convBool))
{
isDistributed = !convBool;
}
}
return isDistributed;
}
public bool autoDispatch;
public T ConvertToEntity<T>(DataRow tableRow) where T : new()
{
// Create a new type of the entity I want
Type t = typeof (T);
T returnObject = new T();
foreach (DataColumn col in tableRow.Table.Columns)
{
string colName = col.ColumnName;
// Look for the object's property with the columns name, ignore case
PropertyInfo pInfo = t.GetProperty(colName.ToLower(),
BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
// did we find the property ?
if (pInfo != null)
{
object val = tableRow[colName];
// is this a Nullable<> type
bool IsNullable = (Nullable.GetUnderlyingType(pInfo.PropertyType) != null);
if (IsNullable)
{
if (val is System.DBNull)
{
val = null;
}
else
{
// Convert the db type into the T we have in our Nullable<T> type
val = Convert.ChangeType
(val, Nullable.GetUnderlyingType(pInfo.PropertyType));
}
}
else
{
// Convert the db type into the type of the property in our entity
val = Convert.ChangeType(val, pInfo.PropertyType);
}
// Set the value of the property with the value from the db
pInfo.SetValue(returnObject, val, null);
}
}
// return the entity object with values
return returnObject;
}
public Action<bool, string> CompletedCallback;
public Action<bool, string, DataTable> CompletedCallbackDT;
public void HandleCallbackDT(IAsyncResult result)
{
try
{
var command = (SqlCommand)result.AsyncState;
var reader = command.EndExecuteReader(result);
var table = new DataTable();
table.Load(reader);
if (autoDispatch)
{
Application.Current.Dispatcher.Invoke(
DispatcherPriority.Normal, new Action(() => CompletedCallbackDT(true, "", table)));
}
else
{
CompletedCallbackDT(true, "", table);
}
command.Connection.Close();
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
var realerror = ex.InnerException;
while (realerror.InnerException != null)
realerror = realerror.InnerException;
if (autoDispatch)
{
Application.Current.Dispatcher.Invoke(
DispatcherPriority.Normal, new Action(() =>
CompletedCallbackDT(false, realerror.Message,
new DataTable())));
}
else
{
CompletedCallbackDT(true, "", new DataTable());
}
}
else
{
CompletedCallbackDT(false, ex.Message, new DataTable());
}
}
}
public void HandleCallbackNonQuery(IAsyncResult result)
{
try
{
var command = (SqlCommand) result.AsyncState;
command.EndExecuteNonQuery(result);
CompletedCallback(true, "");
command.Connection.Close();
}
catch (Exception ex)
{
if (ex.InnerException != null)
{
var realerror = ex.InnerException;
while (realerror.InnerException != null)
realerror = realerror.InnerException;
if (autoDispatch)
{
Application.Current.Dispatcher.Invoke(
DispatcherPriority.Normal, new Action(() =>
CompletedCallback(false, realerror.Message)));
}
else
{
CompletedCallback(false, realerror.Message);
}
}
else
{
if (autoDispatch)
{
Application.Current.Dispatcher.Invoke(
DispatcherPriority.Normal, new Action(() =>
CompletedCallback(false, ex.Message)));
}
else
{
CompletedCallback(false, ex.Message);
}
}
}
}
private string GetConnectionString()
{
var connectionStringName = DetermineDisconnected() ? ConfigurationManager.ConnectionStrings["Operation"].ConnectionString
: ConfigurationManager.ConnectionStrings["OperationTablet"].ConnectionString;
var connectionStr = CustomDataSourceKeyResolver.DecryptString(
connectionStringName,
ConfigurationManager.AppSettings.Get("Environment")) + "Async=true";
return connectionStr;
}
}
}