protected List <KeyValueCollection> GetEntityPage(DB2DataReader reader, int offset, int limit)
{
List <KeyValueCollection> entitys = new List <KeyValueCollection>();
// 读取列
List <string> columns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// 反射创建实体类
int currentIndex = 0;
while (reader.Read())
{
if (offset > currentIndex++)
{
continue;
}
if (offset + limit == currentIndex)
{
break;
}
KeyValueCollection entity = new KeyValueCollection();
foreach (var col in columns)
{
entity[col] = reader[col];
}
entitys.Add(entity);
}
return(entitys);
}
/// <summary>
/// 反射实体类信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
protected List <T> GetEntityCollection <T>(DB2DataReader reader) where T : class, new()
{
PropertyInfo[] properties = typeof(T).GetProperties();
if (properties == null || properties.Length <= 0)
{
throw new ArgumentNullException("实体类{0}不包含任何属性信息！".ToFormat(typeof(T).Name));
}
// 读取列
Dictionary <string, string> columns = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
for (int i = 0; i < reader.FieldCount; i++)
{
string cname = reader.GetName(i);
columns[cname] = cname;
}
// 反射创建实体类
List <T> entitys = new List <T>();
while (reader.Read())
{
T entity = typeof(T).Create <T>();
foreach (PropertyInfo property in properties)
{
string p_name = property.Name;
object[] attributes = property.GetCustomAttributes(typeof(Field), true);
object[] proc_attributes = property.GetCustomAttributes(typeof(Proc), true);
// 存在自定义Field实例处理方法
if (attributes != null && attributes.Length > 0)
{
Field dbfield = attributes[0] as Field;
// 检测自增变量信息
p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name;
}
else if (proc_attributes != null && proc_attributes.Length > 0)
{
Proc dbfield = proc_attributes[0] as Proc;
// 检测自增变量信息
p_name = dbfield.Name.IsNullOrEmpty() ? p_name : dbfield.Name;
}
// 不存在时处理方法
else
{
}
// 读取数据，并赋值
if (columns.ContainsKey(p_name))
{
property.SetValue(entity, reader[columns[p_name]].ChangeTo(property.PropertyType), null);
}
}
entitys.Add(entity);
}
return(entitys);
}
/// <summary>
/// 反射实体类信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
protected KeyValueCollection GetEntity(DB2DataReader reader)
{
KeyValueCollection entity = new KeyValueCollection();
// 读取列
List <string> columns = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i));
}
// 反射创建实体类
if (reader.Read())
{
foreach (var col in columns)
{
entity[col] = reader[col];
}
}
return(entity);
}
protected void DB2Connection()
{
string Text1 = TextBox1.Text;
string inputPattern = @"^[\s\;]*\w+[^\;]*[\;\s]*$";
Regex input = new Regex(inputPattern);
Match inputmatch = input.Match(TextBox1.Text);
if (inputmatch.Success)
{
string s = @"Server=localhost:50000;Database=" + DropDownList2.SelectedItem.Text + ";UID=db2admin;PWD=***;CurrentSchema=db2admin";
DB2Connection myConnection = new DB2Connection(s);
DB2Command cmd = new DB2Command();
DB2DataReader r = null;
DB2DataAdapter adapter = new DB2DataAdapter(Text1, myConnection);
//string usertype = User.Identity.Name.Substring(4);
string usertype = Session["Role"].ToString().ToLower();
string pattern = "";
switch (usertype)
{
case "level1":
pattern = @"((^|\;)\s*(?i)(\binsert\b|\bupdate\b|\bdelete\b|\bmerge\b|\bcreate\b|\balter\b|\bdrop\b)(?i))|(?i)\binto\b(?i)";
break;
case "level2":
pattern = @"(^|;|\*\/)\s*(?i)(\bcreate\b|\balter\b|\bdrop\b)(?i)";
break;
default:
pattern = @"((^|;|\*\/)\s*(?i)(\bdrop\b\s*database)(?i))";
break;
}
Regex rgx = new Regex(pattern);
Match match = rgx.Match(TextBox1.Text);
if (match.Success)
{
msg.Text = "Permission was denied on database " + DropDownList2.SelectedItem.Text;
}
else
{
try
{
cmd.CommandText = TextBox1.Text;
cmd.Connection = myConnection;
myConnection.Open();
string firstWord = Regex.Match(TextBox1.Text, @"\w+\b").ToString().ToLower();
if (firstWord == "select")
{
DataTable dt = new DataTable();
adapter.Fill(dt);
GridView1.DataSource = dt;
r = cmd.ExecuteReader();
int rowCount = 0;
if (r.HasRows) //results>0
{
while (r.Read())
{
rowCount++;
}
msg.Text = "Result: " + rowCount + " row(s) found";
}
else
{
msg.Text = "Result: 0 row(s) found.<br /><span style='background-color:#339933; color:black; font-size: 15pt'>";
for (int i = 0; i < r.FieldCount; i++)
{
msg.Text += r.GetName(i) + " |";
}
msg.Text += "</span><br />";
}
r.Close();
}
else
{
int numberOfRecords = cmd.ExecuteNonQuery();
msg.Text = "Result: " + numberOfRecords + " row(s) affected.";
}
}
catch (Exception ex)
{
msg.Text = ex.Message;
}
finally
{
myConnection.Close();
GridView1.DataBind();
FormatView();
}
}
}
else
{
//msg.Text = "Only one statement allowed";
Header.Controls.Add(new LiteralControl("<script type=\"text/javascript\">alert('Only one statement allowed');</script>"));
}
}
public string Read(string jsonQuery)
{
string selectSQL = this._sqlParser.ReadSqlParser(jsonQuery);
string objName = this._sqlParser.ObjName;
bool isReadToList = this._sqlParser.IsReadToList;
string jsonDataSet = string.Empty;
if (!string.IsNullOrEmpty(selectSQL))
{
DB2Command dbCmd = new DB2Command(selectSQL, this._connection);
this.OpenConnection();
DB2DataReader dbReader = dbCmd.ExecuteReader();
while (dbReader.Read())
{
// add LIST seperator jsonDataSet
if (isReadToList && !string.IsNullOrEmpty(jsonDataSet))
{
jsonDataSet += ",";
}
int fieldCount = dbReader.FieldCount;
// open json row
jsonDataSet += "{";
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++)
{
// GET NAME
string fieldName = dbReader.GetName(fieldIndex);
string JOIN_PREFIX = "JOIN_";
bool isJoinField = false;
if (fieldName.ToUpper().StartsWith("JOIN_"))
{
isJoinField = true;
fieldName = fieldName.Substring(fieldName.IndexOf(JOIN_PREFIX) + JOIN_PREFIX.Length);
}
fieldName = $"\"{fieldName}\"";
// GET VALUE
string fieldValue = dbReader.IsDBNull(fieldIndex) ? "\"\"" : dbReader.GetString(fieldIndex);
if (!isJoinField)
{
fieldValue = $"\"{fieldValue}\"";
}
jsonDataSet += $"{fieldName}:{fieldValue}";
if (fieldIndex < fieldCount - 1)
{
jsonDataSet += ",";
}
}
// close json row
jsonDataSet += "}";
}
//close
dbReader.Close();
dbReader.Dispose();
this.CloseConnection();
}
return(StringHelper.Simpler(string.IsNullOrEmpty(jsonDataSet) ? null : ("{" + $"\"{objName}\"" + ":" + ((isReadToList) ? "[" + jsonDataSet + "]" : jsonDataSet) + "}"), Pattern.PATTERN_SPECIAL_CHARS));
}
public override string GetName(int ordinal)
{
return(source.GetName(ordinal));
}