public static ReturnValue<DataTable> GetData(FactoryEnum factory, string key, Dictionary<string, string> parameters = null)
{
var rv = new ReturnValue<DataTable>();
if (parameters == null)
parameters = new Dictionary<string, string>();
SqlExecuteService.StatementResult sr;
try
{
sr = ServiceClient.GetStatement(factory.ToString(),
key,
AF.Environment.AccessToken);
if (!sr.Result)
{
rv.Result = false;
rv.Message = sr.Message;
return rv;
}
}
catch (Exception ex)
{
rv.Result = false;
rv.Message = ex.Message;
return rv;
}
List<SqlParameter> sp = new List<SqlParameter>();
if (parameters.Count > 0)
{
foreach (var kv in parameters)
sp.Add(new SqlParameter(kv.Key, kv.Value));
}
return DB.ExecuteDataTable(sr.ConnectionString, sr.Statements, sp.ToArray());
}
/// <summary>
/// Execute a sql command to dataTable
/// </summary>
/// <param name="connString"></param>
/// <param name="cmdText"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static ReturnValue<DataTable> ExecuteDataTable(
string connString,
string cmdText,
params SqlParameter[] paras)
{
var rv = new ReturnValue<DataTable>();
var dt = new DataTable("UF_DAL_ExecuteDataTable");
using (var conn = new SqlConnection(connString))
{
try
{
conn.Open();
}
catch (Exception ex)
{
rv.Result = false;
rv.Message = ex.Message;
return rv;
}
SqlTransaction ts = null;
var cmd = new SqlCommand();
var adt = new SqlDataAdapter();
cmd.Connection = conn;
cmd.CommandTimeout = 0;
cmd.CommandText = cmdText;
if (paras != null && paras.Length > 0)
cmd.Parameters.AddRange(paras.ToArray());
ts = conn.BeginTransaction();
cmd.Transaction = ts;
adt.SelectCommand = cmd;
try
{
rv.Message = adt.Fill(dt).ToString();
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
rv.Result = false;
rv.Message = ex.Message;
return rv;
}
finally
{
cmd.Parameters.Clear();
}
} //end using
rv.Output1 = dt;
return rv;
}
public static ReturnValue Parse(string val)
{
var rv = new ReturnValue();
if (string.IsNullOrEmpty(val))
return null;
var xe = XElement.Parse(val);
if (xe == null)
return null;
var xa = xe.Attribute("Result");
if (xa == null)
return null;
rv.Result = bool.Parse(xa.Value);
var sxe = xe.Element("Message");
if (sxe == null)
return null;
rv.Message = sxe.Value;
return rv;
}
public static ReturnValue<String> GetDefaultValue(string path, string propertyName, List<TextValuePair> list)
{
var rv = new ReturnValue<string>();
XElement root;
if (_cachedFile.ContainsKey(path))
root = _cachedFile[path];
else
{
root = XDocument.Load(System.Windows.Application.GetResourceStream(
new Uri(ResourceFile.MapXMLPath(path, "DefaultValue"))).Stream).Root;
_cachedFile.Add(path, root);
}
var items = root.Elements("item");
foreach (var item in items)
{
if (item.Attribute("key").Value == propertyName)
{
var ia = item.Attribute("value");
if (ia != null)
{
rv.Output1 = ia.Value;
return rv;
}
ia = item.Attribute("text");
if (ia != null)
{
var li = list.FirstOrDefault(p => p.Text == ia.Value);
if (li != null)
{
rv.Output1 = li.Value;
return rv;
}
else
{
rv.Result = false;
rv.Message = BuildErrorMessage(path, propertyName, "text not found.");
return rv;
}
}
ia = item.Attribute("index");
if (ia != null)
{
int idx;
if (int.TryParse(ia.Value, out idx))
{
if (list.Count > idx)
{
rv.Output1 = list[idx].Value;
return rv;
}
else
{
rv.Result = false;
rv.Message = BuildErrorMessage(path, propertyName, "index out of range.");
return rv;
}
}
else
{
rv.Result = false;
rv.Message = BuildErrorMessage(path, propertyName, "index is not an integer.");
return rv;
}
}
}
}
rv.Result = false;
rv.Message = BuildErrorMessage(path, propertyName, "Property name not found.");
return rv;
}
/// <summary>
/// Execute a command
/// </summary>
/// <param name="server"></param>
/// <param name="cmdText"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static ReturnValue ExecuteNoneQuery(
string connSring,
string cmdText,
params SqlParameter[] paras)
{
var rv = new ReturnValue();
using (var conn = new SqlConnection(connSring))
{
try
{
conn.Open();
}
catch (Exception ex)
{
rv.Result = false;
rv.Message = ex.Message;
return rv;
}
SqlTransaction ts = null;
var cmd = new SqlCommand();
cmd.Connection = conn;
//3600 seconds, means 1 hour
cmd.CommandTimeout = 3600;
cmd.CommandText = cmdText;
if (paras != null && paras.Length > 0)
cmd.Parameters.AddRange(paras.ToArray());
ts = conn.BeginTransaction();
cmd.Transaction = ts;
try
{
rv.Message = cmd.ExecuteNonQuery().ToString();
ts.Commit();
}
catch (Exception ex)
{
ts.Rollback();
rv.Result = false;
rv.Message = ex.Message;
return rv;
}
finally
{
cmd.Parameters.Clear();
}
} //end using
return rv;
}
