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