// (obsolete) Execute the requested call internal static DataSet execProcCall(procCall CallRequest) { DataSet dsResults = new DataSet(); //string connectionString = getConnectionString(); //using (SqlConnection conn = new SqlConnection(connectionString)) //{ // conn.Open(); // SqlDataAdapter daExecProcCall = new SqlDataAdapter(CallRequest.schemaName + "." + CallRequest.procName, conn); // daExecProcCall.SelectCommand.CommandType = CommandType.StoredProcedure; // // Add parameters to the command // foreach(ProcParameters p in CallRequest.procParms) // { // daExecProcCall.SelectCommand.Parameters.Add(new SqlParameter(p.parmName, p.parmValue)); // } // daExecProcCall.Fill(dsResults); // conn.Close(); //} return dsResults; }
// (obsolete) Verify the existance of the proc and parameters against the catalog using stored proc internal static bool verifyCallRequest(ref procCall CallRequest) { //string connectionString = getConnectionString(); //int retCode; // using proc calls: verify the proc exists and get the paramter list // parameters that don't exist in the dd proc definition are removed // from CallRequest //using (SqlConnection conn = new SqlConnection(connectionString)) //{ // conn.Open(); // // Setup to call admin.GetProcParameters_sp // // which will verify if the proc exists and return the list of parameters // SqlDataAdapter dbProc = new SqlDataAdapter("admin.GetProcParameters_sp", conn); // dbProc.SelectCommand.CommandType = CommandType.StoredProcedure; // dbProc.SelectCommand.Parameters.AddWithValue("@schema", CallRequest.schemaName); // dbProc.SelectCommand.Parameters.AddWithValue("@procName", CallRequest.procName); // dbProc.SelectCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue; // DataSet dsParameters = new DataSet(); // retCode = dbProc.Fill(dsParameters); // // retCode = 0 if the proc does not exist // retCode = Convert.ToInt32(dbProc.SelectCommand.Parameters["@RETURN_VALUE"].Value.ToString()); // // If the proc doesn't exist, return false // if (retCode == 0) // { // return false; // } // // list of the proc's parameters as indicated int the DB // List<string> dbParmName = new List<string>(20); // // for each parameter, get the name to lower case // foreach (DataRow r in dsParameters.Tables[0].Rows) // { // dbParmName.Add(r["parameterName"].ToString().ToLower()); // } // // Get a list of the parameters in the request that don't // // have the parameter as part of the proc parameters // int matchIdx; // List<ProcParameters> parmsNotMatched = new List<ProcParameters>(); // LogMessage(""); // foreach (ProcParameters p in CallRequest.procParms) // { // // we're really looking for the parameter that doesn't match // matchIdx = dbParmName.FindIndex(m => m.Equals(p.parmName.ToLower())); // // Save the list of user provided parameters that don't exist in the DB // if (matchIdx == -1) // { // parmsNotMatched.Add(p); // LogMessage("Supplied parameter not used by procedure: " + p.parmName); // } // } // // remove the parameters that don't match the proc parameters on the DB // foreach (ProcParameters p in parmsNotMatched) // { // CallRequest.procParms.RemoveAll(m => m.parmName == p.parmName); // } //} return true; }
// (obsolete) Parse Call XML into ProcCall Class object internal static procCall parseXML(string CallProcRequest) { // string myXML = CallProcRequest; // SECTION 1: Shred the xml into parameters and result tables // ------------------------------------------------------------ // string value = ""; // string attrbName = ""; // string dir = ""; // string targetTableName = ""; // int resultSetMappingId; procCall procCall = new procCall(); // XmlDocument xmlDoc = new XmlDocument(); // xmlDoc.LoadXml(myXML); // XmlNodeList xnList = xmlDoc.SelectNodes("/execute"); // XmlNode node = xnList[0]; // string procName = node.Attributes["proc"].Value; // string schema = node.Attributes["schema"].Value; // LogMessage(""); // LogMessage("Proc: " + schema + "." + procName); // procCall.procName = procName; // procCall.schemaName = schema; // xnList = xmlDoc.SelectNodes("/execute/output"); // get the list of output tables, in the event of multiple // result sets. Sequence is important here as we'll // map the resulting dataset.tables in the order listed here // procCall.tblOut = new List<outTables>(); // a default value for mapping the table to the result set // int defaultSeq = 1; // LogMessage(""); // foreach (XmlNode xn in xnList) // { // get the list of output tables // resultSetSeq will be the order the results are // stored in if multiple result sets // targetTableName = xn.Attributes["target"].Value; // if a resultsetseq is given use it otherwise use sequential counter // resultSetMappingId = xn.Attributes["resultsetseq"] == null ? defaultSeq : Convert.ToInt32(xn.Attributes["resultsetseq"].Value); // procCall.tblOut.Add(new outTables { resultSetSeq = resultSetMappingId, tableName = targetTableName }); // LogMessage("Output TableName: " + xn.Attributes["target"].Value); // defaultSeq++; // } // xnList = xmlDoc.SelectNodes("/execute/parm"); // List<ProcParameters> parms = new List<ProcParameters>(); // procCall.procParms = new List<ProcParameters>(); // LogMessage(""); // get the parameter list <parm name value direction /> // if(xnList != null) // { // foreach (XmlNode xn in xnList) // { // attrbName = xn.Attributes["name"].Value; // value = xn.Attributes["value"].Value; // dir = xn.Attributes["direction"] == null ? "input" : xn.Attributes["direction"].Value; // LogMessage("Attribute: " + attrbName + " = " + value); // save the list of parameters and their values // parms.Add(new ProcParameters { parmName = attrbName, parmValue = value, parmDirection = dir }); // procCall.procParms.Add(new ProcParameters { parmName = attrbName, parmValue = value, parmDirection = dir }); // } // } return procCall; }