protected ADODB.Command GenerateCommand(CommandTypeEnum eCmdType, SqlFrag frag)
{
ADODB.Command SqlCmd = new Command();
string strQuery = "";
if (frag.SqlParams.Count < 2040)
{
ProcessParameters(SqlCmd, frag);
// replace our tags
strQuery = frag.m_strSql.Replace("{", "");
strQuery = strQuery.Replace("}", "");
}
else
{
strQuery = frag.Flatten();
}
// ensure we have a valid connections
Ensure();
// set up the object
SqlCmd.ActiveConnection = m_pConnection;
SqlCmd.CommandText = strQuery;
SqlCmd.CommandType = eCmdType;
return(SqlCmd);
}
public static SqlFrag operator +(SqlFrag Frag1, string strToAdd)
{
SqlFrag Cloned = new SqlFrag(Frag1);
Cloned.m_strSql += " ";
Cloned.m_strSql += strToAdd;
return(Cloned);
}
public int ExecuteSql(CommandTypeEnum eCmdType, SqlFrag frag)
{
ADODB.Command sqlCmd = GenerateCommand(eCmdType, frag);
object obRecsAffected = 0;
sqlCmd.Execute(out obRecsAffected);
return((int)obRecsAffected);
}
public static SqlFrag operator +(SqlFrag Frag1, SqlFrag Frag2)
{
SqlFrag Cloned = new SqlFrag(Frag1);
Cloned.m_strSql += " ";
Cloned.m_strSql += Frag2.m_strSql;
Cloned.m_Params.AddRange(Frag2.m_Params);
return(Cloned);
}
public bool ReturnsRecords(SqlFrag frag)
{
ADORecordset rs = CreateRecordset("IF EXISTS({SQL}) (SELECT CAST(1 AS BIT) AS Val) ELSE (SELECT CAST(0 AS BIT) AS Val)", frag);
bool bExists = false;
if (!rs.EOF)
{
bExists = rs.GetValue <bool>("Val");
}
return(bExists);
}
public SqlDataReader GetDataReader(SqlFrag frag)
{
if (frag.SqlParams.Count < 2040)
{
return(GetParamDataReader("{SQL}", frag));
}
else
{
return(GetParamDataReader(frag.Flatten()));
}
}
public DataSet GetDataSet(SqlFrag frag)
{
if (frag.SqlParams.Count < 2040)
{
return(GetParamDataSet("{SQL}", frag));
}
else
{
return(GetParamDataSet(frag.Flatten()));
}
}
public int ExecuteSql(SqlFrag frag)
{
if (frag.SqlParams.Count < 2040)
{
return(ExecuteParamSql("{SQL}", frag));
}
else
{
return(ExecuteParamSql(frag.Flatten()));
}
}
public Recordset CreateRecordset(SqlFrag frag, CommandTypeEnum eCmdType = CommandTypeEnum.adCmdText,
CursorLocationEnum eCursorLoc = CursorLocationEnum.adUseClient, CursorTypeEnum eCursorType = CursorTypeEnum.adOpenDynamic,
LockTypeEnum eLockType = LockTypeEnum.adLockOptimistic, ExecuteOptionEnum eExecOptions = ExecuteOptionEnum.adOptionUnspecified)
{
ADODB.Command SqlCmd = GenerateCommand(eCmdType, frag);
Recordset rRecords = new Recordset();
rRecords.CursorLocation = eCursorLoc;
rRecords.Open(SqlCmd, Type.Missing, eCursorType, eLockType, (int)eExecOptions);
return(rRecords);
}
private void ProcessParameters(Command SqlCmd, SqlFrag frag)
{
if (frag == null || frag.SqlParams.Count == 0)
{
return;
}
for (int i = 0; i < frag.SqlParams.Count; i++)
{
AppendParameter(frag.SqlParams[i], SqlCmd);
}
}
public SqlFrag(SqlFrag FragToClone)
{
m_strSql = FragToClone.Sql;
m_Params = new List <SqlParam>(FragToClone.SqlParams);
}
public void ProcessParamSql(string strSql, ref object[] parms)
{
// TODO: Use StringBuilder for better performance/less of a memory hit?
int CurrIndexOpenCurly = -1;
int CurrIndexCloseCurly = -1;
foreach (object oParam in parms)
{
CurrIndexOpenCurly = strSql.IndexOf('{', CurrIndexOpenCurly + 1);
if (CurrIndexOpenCurly == -1)
{
break;
}
CurrIndexCloseCurly = strSql.IndexOf('}', CurrIndexOpenCurly + 1);
if (CurrIndexCloseCurly == -1)
{
throw new Exception("An incomplete parameter tag exists in this query");
}
if (CurrIndexCloseCurly <= CurrIndexOpenCurly)
{
throw new Exception("The index of the closing brace is less than the index of the opening brace.");
}
SqlParam sqlParam;
sqlParam.Value = oParam;
sqlParam.DataType = GetParameterType(CurrIndexOpenCurly, CurrIndexCloseCurly, ref strSql);
string strInsert = "{?}";
switch (sqlParam.DataType)
{
case SqlParamType.Sql:
{
SqlFrag frag = (SqlFrag)sqlParam.Value;
strInsert = frag.m_strSql;
m_Params.AddRange(frag.m_Params);
}
break;
case SqlParamType.ConstantString:
case SqlParamType.ConstantInt:
strInsert = FlattenParameter(sqlParam);
break;
default:
strInsert = "{?}";
m_Params.Add(sqlParam);
break;
}
strSql = strSql.Remove(CurrIndexOpenCurly, CurrIndexCloseCurly - CurrIndexOpenCurly + 1);
strSql = strSql.Insert(CurrIndexOpenCurly, strInsert);
CurrIndexOpenCurly += strInsert.Length;
}
m_strSql = strSql;
}