public void Load(string xmlFileName, ref OperationResult op) { try { this.XmlDoc = new XmlDocument(); this.XmlDoc.Load(xmlFileName); XmlNodeList scriptList = XmlDoc.SelectNodes("/SqlScripts/SqlScript"); foreach (XmlElement script in scriptList) { string name = script.SelectSingleNode("Name").InnerText.Trim(); string sql = script.SelectSingleNode("Sql").InnerText; string type = script.SelectSingleNode("Type").InnerText; XmlNodeList paramList = script.SelectNodes("Parameters/Parameter"); SqlScriptMembers sm = new SqlScriptMembers(); sm.Parameters = new Dictionary <string, SqlScriptParameter>(); foreach (XmlElement param in paramList) { string pName = param.GetAttribute("name").Trim(); string pType = param.GetAttribute("type").Trim(); string pMarker = param.InnerText; SqlScriptParameter ssp = new SqlScriptParameter(); ssp.Type = GetParamType(pType); ssp.Marker = pMarker; sm.Parameters.Add(pName, ssp); } sm.Sql = sql; if (sm.Sql.Trim().Length > 1 && name.Trim().Length > 1) { this.SqlScriptDict.Add(name, sm); } sm.Type = type; } if (SqlScriptDict == null || SqlScriptDict.Count < 1) { op.AddError("Sql Scripts not set. Make sure there are valid scripts."); this.IsSet = false; return; } this.IsSet = true; } catch (Exception ex) { op.AddException(ex); } }
public DataTable ExecuteSql(OleDbConnection conn, SqlScriptEnum sqlScript, Dictionary <string, string> sqlPrmNameValueList, ref OperationResult op) { if (string.IsNullOrEmpty(ConnectionString)) { op.AddError("The connection string is not set."); return(null); } DataTable dt = new DataTable(); SqlScriptMembers sqlScrptMem = SqlScripts.SqlScriptDict[sqlScript.ToString()]; Dictionary <string, SqlScriptParameter> paramList = sqlScrptMem.Parameters; string sql = ValidateAndInsertParamList(sqlScrptMem.Sql, ref paramList, ref sqlPrmNameValueList, ref op); if (!op.Success) { return(null); } try { if (conn.State != ConnectionState.Open) { conn.Open(); } using (OleDbCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = this.CommandType; dt = new DataTable(); new OleDbDataAdapter(cmd).Fill(dt); } return(dt); } catch (Exception ex) { op.AddException(ex); } return(null); }
public DataTable ExecuteSql(SqlScriptEnum sqlScript, Dictionary <string, string> sqlPrmNameValueList, ref OperationResult op) { DataTable dt = new DataTable(); SqlScriptMembers sqlScrptMem = SqlScripts.SqlScriptDict[sqlScript.ToString()]; Dictionary <string, SqlScriptParameter> paramList = sqlScrptMem.Parameters; string sql = ValidateAndInsertParamList(sqlScrptMem.Sql, ref paramList, ref sqlPrmNameValueList, ref op); if (!op.Success) { return(null); } dt = ExecSql(sql, ref op); if (!op.Success) { return(null); } return(dt ?? new DataTable()); }