public IStatement Prepare(string sql)
{
MSSQLStatement stmt = new MSSQLStatement(this, sql);
stmt.PreExec += OnStmtPreExec;
return(stmt);
}
public int Insert(ICollection list)
{
MSSQLField[] writeable = GetFields(FieldFlags.Write);
MSSQLField[] identity = GetFields(FieldFlags.Auto);
//REMOVE FIELD INDENTITY KEY
if (identity.Length > 0)
{
var temp = writeable.ToList();
temp.Remove(identity[0]);
writeable = temp.ToArray();
}
string[] names = new string[writeable.Length];
string[] places = new string[writeable.Length];
for (int i = 0; i < writeable.Length; ++i)
{
names[i] = database.QuoteName(writeable[i].Name);
places[i] = "{" + i + "}";
}
string fieldstr = string.Join(",", names);
string valuestr = string.Join(",", places);
string sql = "INSERT INTO " + QuotedName +
"(" + fieldstr + ") VALUES(" + valuestr + ");";
SqlParameter pID = null;
int rowcount = 0;
object[] parameters = new object[writeable.Length];
using (MSSQLStatement stmt = database.Prepare(sql) as MSSQLStatement)
{
stmt.Prepare(writeable.Length);
if (pID != null)
{
stmt.Command.Parameters.Add(pID);
}
foreach (object obj in list)
{
for (int i = 0; i < writeable.Length; ++i)
{
parameters[i] = writeable[i].GetValue(obj);
if (parameters[i] == null && writeable[i].DataType == MSSQLStatement.ByteArrayType)
{
parameters[i] = MSSQLStatement.BinaryNull;
}
}
rowcount += stmt.ExecNonQuery(parameters);
if (pID != null)
{
object idValue = Convert.IsDBNull(pID.Value) ? null : pID.Value;
identity[0].SetValue(obj, idValue);
}
}
}
return(rowcount);
}
protected void Select(IList list)
{
MSSQLField[] readables = table.GetFields(FieldFlags.Read);
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ");
if (distinct)
{
sql.Append("DISTINCT ");
}
if (selectTop > 0)
{
sql.Append("TOP ").Append(selectTop).Append(" ");
}
bool first = true;
foreach (MSSQLField f in readables)
{
if (!first)
{
sql.Append(",");
}
first = false;
sql.Append(database.QuoteName(f.Name));
}
sql.Append(" FROM ").Append(table.QuotedName);
if (where.Length > 0)
{
sql.Append(" WHERE ").Append(where.ToString());
}
ForEachCallback callback = new ForEachCallback(delegate(IRow row)
{
object obj = table.NewObject();
object value = null;
int i = 0;
foreach (MSSQLField f in readables)
{
value = row[i++];
if (Convert.IsDBNull(value))
{
value = null;
}
f.SetValue(obj, value);
}
list.Add(obj);
});
using (MSSQLStatement stmt = database.Prepare(sql.ToString()) as MSSQLStatement)
using (IQueryResult result = stmt.ExecQuery(values.ToArray()))
{
result.ForEach(callback);
}
}
public int Delete()
{
StringBuilder sql = new StringBuilder();
sql.Append("DELETE FROM ").Append(table.QuotedName);
if (where.Length > 0)
{
sql.Append(" WHERE ").Append(where.ToString());
}
int rowcount = 0;
using (MSSQLStatement stmt = database.Prepare(sql.ToString()) as MSSQLStatement)
rowcount = stmt.ExecNonQuery(values.ToArray());
return(rowcount);
}
public int Count()
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT COUNT(*) FROM ").Append(table.QuotedName);
if (where.Length > 0)
{
sql.Append(" WHERE ").Append(where.ToString());
}
int count = 0;
using (MSSQLStatement stmt = database.Prepare(sql.ToString()) as MSSQLStatement)
count = (int)stmt.ExecScalar();
return(count);
}
public int Delete(ICollection list)
{
MSSQLField[] keys = GetFields(FieldFlags.Key);
StringBuilder sql = new StringBuilder();
sql.Append("DELETE FROM ")
.Append(QuotedName)
.Append(" WHERE ");
bool first = true;
for (int i = 0; i < keys.Length; ++i)
{
if (!first)
{
sql.Append(" AND ");
}
sql.Append(database.QuoteName(keys[i].Name))
.Append("=")
.Append("{").Append(i).Append("}");
first = false;
}
int rowcount = 0;
object[] parameters = new object[keys.Length];
using (MSSQLStatement stmt = database.Prepare(sql.ToString()) as MSSQLStatement)
{
foreach (object obj in list)
{
for (int i = 0; i < keys.Length; ++i)
{
parameters[i] = keys[i].GetValue(obj);
}
rowcount += stmt.ExecNonQuery(parameters);
}
}
return(rowcount);
}
public int Update(ICollection list)
{
MSSQLField[] nonKeys = GetFields(delegate(MSSQLField f) {
return((f.Flags & FieldFlags.Write) == FieldFlags.Write &&
(f.Flags & FieldFlags.Key) != FieldFlags.Key);
});
MSSQLField[] keys = GetFields(FieldFlags.Key);
int i = 0;
bool first = true;
StringBuilder sql = new StringBuilder();
sql.Append("UPDATE ")
.Append(QuotedName)
.Append(" SET ");
foreach (MSSQLField f in nonKeys)
{
if (!first)
{
sql.Append(",");
}
sql.Append(database.QuoteName(f.Name))
.Append("=")
.Append("{").Append(i).Append("}");
first = false;
i++;
}
if (keys.Length > 0)
{
first = true;
sql.Append(" WHERE ");
foreach (MSSQLField f in keys)
{
if (!first)
{
sql.Append(" AND ");
}
sql.Append(database.QuoteName(f.Name))
.Append("=")
.Append("{").Append(i).Append("}");
first = false;
i++;
}
}
int rowcount = 0;
object[] parameters = new object[nonKeys.Length + keys.Length];
using (MSSQLStatement stmt = database.Prepare(sql.ToString()) as MSSQLStatement)
{
foreach (object obj in list)
{
i = 0;
foreach (MSSQLField f in nonKeys)
{
parameters[i++] = f.GetValue(obj);
}
foreach (MSSQLField f in keys)
{
parameters[i++] = f.GetValue(obj);
}
rowcount += stmt.ExecNonQuery(parameters);
}
}
return(rowcount);
}