public SOIUtil()
{
_server = GetServerEnvironment();
_serverLog = new ServerLogger();
}
private byte[] ExecuteStoredProcedureHandler(NameValueCollection boundVariables,
JsonObject operationInput,
string outputFormat,
string requestProperties,
out string responseProperties)
{
responseProperties = null;
string retString = "";
try
{
//return Encoding.UTF8.GetBytes(retStrn);
//return null;
string pipeDelimetedStringValuePairsForStoredProc = "";
bool found = operationInput.TryGetString("ParamValuePairs", out pipeDelimetedStringValuePairsForStoredProc);
if (!found || string.IsNullOrEmpty(pipeDelimetedStringValuePairsForStoredProc))
{
throw new ArgumentNullException("ParamValuePairs");
}
string extra;
found = operationInput.TryGetString("Extra", out extra);
if (!found || string.IsNullOrEmpty(extra))
{
throw new ArgumentNullException("extra");
}
responseProperties = null;
IServerEnvironment3 senv = GetServerEnvironment() as IServerEnvironment3;
JsonObject result = new JsonObject();
JsonObject suinfoj = new JsonObject();
//get user info and serialize into JSON
IServerUserInfo suinfo = senv.UserInfo;
if (null != suinfo)
{
suinfoj.AddString("currentUser", suinfo.Name);
IEnumBSTR roles = suinfo.Roles;
List <string> rolelist = new List <string>();
if (null != roles)
{
string role = roles.Next();
while (!string.IsNullOrEmpty(role))
{
rolelist.Add(role);
role = roles.Next();
}
}
suinfoj.AddArray("roles", rolelist.ToArray());
result.AddJsonObject("serverUserInfo", suinfoj);
}
else
{
result.AddJsonObject("serverUserInfo", null);
}
IServerObject so = serverObjectHelper.ServerObject;
retString = "got so>";
string progString = "";
retString += "> Stored Proc via oleDB ";// + ex.Message;
OleDbConnection con = new OleDbConnection();
string paramsThatParsed = "";
con.ConnectionString = @"Provider =SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + extra.Split(',')[0] + ";Data Source=" + extra.Split(',')[1];// @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=PC684"; //
string storedProcedureName = "dbo.apLITSaveChanges";
bool isStatusOutput = Convert.ToBoolean(extra.Split(',')[2]);
//the connection string below uses integrated security which is usually superior to storing credential in visible text
//con.ConnectionString = @"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=PC684";
//con.Open();
//retString += ">opened connection";
string SQLString = "";
OleDbCommand cmd = new OleDbCommand(storedProcedureName, con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
string userName = "******";
if (suinfo.Name != null)
{
userName = suinfo.Name;
}
cmd.Parameters.AddWithValue("@WindowsLogin", userName);
SQLString += "@WindowsLogin='******'";
//SQLString += "@WindowsLogin="******">created command";
string[] paramValsForStoredProc = pipeDelimetedStringValuePairsForStoredProc.Split('|');
foreach (string paramVal in paramValsForStoredProc)
{
string param = paramVal.Split(',')[0];
paramsThatParsed += "," + param;
string val = paramVal.Split(',')[1];
retString += ">param and value : " + param + " = " + val;
param = "@" + param;
if (param.ToUpper().Contains("GEOLOCID"))
{
int i = int.Parse(val);
cmd.Parameters.AddWithValue(param, i);
SQLString += ", " + param + "= " + i;
}
else if (param.ToUpper() == "@LATITUDE" || param.ToUpper() == "@LONGITUDE")
{
double d = double.Parse(val);
cmd.Parameters.AddWithValue(param, d);
SQLString += ", " + param + "= " + d;
}
else if (param.ToUpper() == "@STATUS")
{
if (isStatusOutput)
{
//cmd.Parameters[param].Direction = ParameterDirection.Output;
retString += ">Set direction of status parameter to output";
SQLString += ", @STATUS = @localstatus OUTPUT";
}
}
else
{
cmd.Parameters.AddWithValue(param, val);
//SQLString += ", " + param + "= " + val ;
SQLString += ", " + param + "= '" + val + "'";
}
}//CurGeoLocID,NewGeoLocID,Address,City,ZipCode,Latitude,Longitude,FacilityName,AppID,WindowsLogin,ServerName,ServerName,Status
SQLString = "exec dbo.apLITSaveChanges " + SQLString;
if (isStatusOutput)
{
SQLString = "DECLARE @localstatus varchar(256);" + SQLString;
}
string retStrn = UseAOToCreateUpdateFeatures(SQLString);
return(Encoding.UTF8.GetBytes(retStrn));
return(null);
cmd.Connection = con;
cmd.ExecuteNonQuery();
return(Encoding.UTF8.GetBytes(result.ToJson() + " - " + retString.ToString()));
}
catch (Exception ex)
{
return(Encoding.UTF8.GetBytes("ERROR " + ex.ToString() + " : " + retString.ToString()));
}
}
