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())); } }