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