public override DataTable ExecuteTable(SqlModel model) { DataTable result = new DataTable(); using (SqlConnection conn = GetConnection(model)) { SqlCommand command = new SqlCommand(GetSQL(model), conn); PreCommand(command, model.spList); try { //result.Load(command.ExecuteReader()); //return result; //性能优于直接加载result返回 SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = command; DataSet dataSet = new DataSet(); adapter.Fill(dataSet, "Result"); command.Parameters.Clear(); if (dataSet.Tables.Count > 0) { return(dataSet.Tables["Result"]); } return(null); } catch (Exception ex) { ExceptionDeal(ex, command.CommandText, model.spList); return(null); } finally { conn.Dispose(); } } }
public override DbDataReader ExecuteReader(SqlModel model) { DataTable dt = ExecuteTable(model); DataTableReader reader = new DataTableReader(dt); return(reader); }
//--------------分页与连结 public override DataTable SelPage(PageSetting config) { int offset = config.psize * (config.cpage - 1); if (string.IsNullOrEmpty(config.t2)) { config.sql = "SELECT " + config.fields + " FROM " + config.t1 + " WHERE " + config.where + " LIMIT " + offset + "," + config.psize; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " WHERE " + config.where; } else { config.sql = "SELECT " + config.fields + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where + " LIMIT " + offset + "," + config.psize; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on + " WHERE " + config.where; } config.DealWithAlias(); SqlModel countMod = new SqlModel() { sql = config.countsql }; countMod.AddSpToList(config.sp); SqlModel model = new SqlModel() { sql = config.sql }; model.AddSpToList(config.sp); config.itemCount = DataConvert.CLng(ExecuteScala(countMod)); config.pageCount = GetPageCount(config.itemCount, config.psize); return(ExecuteTable(model)); }
public override object ExecuteScala(SqlModel model) { OleDbDataAdapter command = new OleDbDataAdapter(); string sql = GetSQL(model); try { DataTable dt = new DataTable(); command = new OleDbDataAdapter(sql, GetConnection()); command.Fill(dt); if (dt.Rows.Count > 0) { return(dt.Rows[0][0]); } else { return(""); } } catch (Exception ex) { ExceptionDeal(ex, "查询失败"); return(null); } finally { command.Dispose(); } }
public override string GetSQL(SqlModel model) { if (!string.IsNullOrEmpty(model.sql)) { return(model.sql); } string sql = ""; GetWhereSql(model); switch (model.cmd) { case SqlModel.SqlCmd.Insert: //insert into table1(field1,field2) values(value1,value2) sql = "INSERT INTO " + model.tbName + " ({0}) VALUES({1});SELECT @@IDENTITY;"; sql = string.Format(sql, model.fields, model.values); break; case SqlModel.SqlCmd.Delete: //delete * from table1 sql = "DELETE * FROM " + model.tbName; if (!string.IsNullOrEmpty(model.where)) { sql += " WHERE " + model.where; } break; case SqlModel.SqlCmd.Update: //update table1 set field1=value1 sql = "UPDATE " + model.tbName + " SET " + model.set + " "; if (!string.IsNullOrEmpty(model.where)) { sql += " WHERE " + model.where; } break; case SqlModel.SqlCmd.Select: //SELECT * FROM {tbName} WHERE {where} ORDER BY {order} if (model.size > 0) { model.fields = " TOP " + model.size + " " + model.fields; } sql = "SELECT " + model.fields + " FROM " + model.tbName; if (!string.IsNullOrEmpty(model.where)) { sql += " WHERE " + model.where; } if (!string.IsNullOrEmpty(model.order)) { sql += " ORDER BY " + model.order; } break; } return(sql); }
//4.0以后不再提供对Oracle的支持,根据需要可替换为ODP.NET private OracleConnection GetConnection(SqlModel model) { OracleConnection conn = new OracleConnection(ConnectionString); try { conn.Open(); return(conn); } catch (Exception ex) { conn.Dispose(); ExceptionDeal(ex, "打开Oracle连接失败"); } return(null); }
//--------------Tools private MySqlConnection GetConnection(SqlModel model) { MySqlConnection conn = new MySqlConnection(ConnectionString); try { conn.Open(); return(conn); } catch (Exception ex) { conn.Dispose(); throw new Exception(ex.Message); } }
/// <summary> /// 用于执行Insert,返回ID /// </summary> public override object ExecuteScalar(SqlModel model) { string sql = GetSQL(model); using (MySqlConnection conn = GetConnection(model)) { MySqlCommand command = new MySqlCommand(sql, conn); PreCommand(command, model.spList); return(command.ExecuteScalar()); } //return 0; }
public override DbDataReader ExecuteReader(SqlModel model) { string sql = GetSQL(model); OracleConnection conn = GetConnection(model); try { OracleCommand command = new OracleCommand(sql, conn); PreCommand(command, model.spList); return(command.ExecuteReader(CommandBehavior.CloseConnection)); } catch (Exception ex) { ExceptionDeal(ex, sql, model.spList); conn.Dispose(); return(null); } }
public override int InsertID(SqlModel model) { //Oracle是否要考虑为附加式,即ID不自动增长,而是手动生成后再插入??(减小脚本,查询准确) ExecuteNonQuery(model); if (string.IsNullOrEmpty(model.pk)) { model.pk = "ID"; } return(DataConvert.CLng(ExecuteScala(new SqlModel() { sql = "SELECT MAX(" + model.pk + ") FROM " + model.tbName }))); }
public override object ExecuteScala(SqlModel model) { string sql = GetSQL(model); OracleConnection conn = GetConnection(model); try { OracleCommand command = new OracleCommand(sql, conn); PreCommand(command, model.spList); return(command.ExecuteScalar()); } catch (Exception ex) { ExceptionDeal(ex, sql, model.spList); return(0); } finally { conn.Dispose(); } }
public override void ExecuteNonQuery(SqlModel model) { string sql = GetSQL(model); OracleConnection conn = GetConnection(model); try { OracleCommand command = new OracleCommand(sql, conn); PreCommand(command, model.spList); command.ExecuteNonQuery(); } catch (Exception ex) { ExceptionDeal(ex, sql, model.spList); } finally { conn.Dispose(); } }
/// <summary> /// 用于执行Insert,返回ID /// </summary> public override object ExecuteScala(SqlModel model) { string sql = GetSQL(model); using (OleDbConnection conn = GetConnection()) { try { OleDbCommand command = new OleDbCommand(sql, conn); PreCommand(command, model.spList); return(command.ExecuteScalar()); } catch (Exception ex) { conn.Dispose(); ExceptionDeal(ex, sql, model.spList.ToArray()); return(0); } } }
public override DataTable ExecuteTable(SqlModel model) { DataTable result = new DataTable(); OracleConnection conn = GetConnection(model); string sql = GetSQL(model); try { OracleCommand command = new OracleCommand(sql, conn); PreCommand(command, model.spList); result.Load(command.ExecuteReader()); } catch (Exception ex) { ExceptionDeal(ex, sql, model.spList); } finally { conn.Dispose(); } return(result); }
public override void ExecuteNonQuery(SqlModel model) { MySqlConnection conn = GetConnection(model); try { MySqlCommand command = new MySqlCommand(GetSQL(model), conn); PreCommand(command, model.spList); command.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Dispose(); } }
public override void ExecuteNonQuery(SqlModel model) { string sql = GetSQL(model); using (SqlConnection conn = GetConnection(model)) { SqlCommand command = new SqlCommand(sql, conn); try { PreCommand(command, model.spList); command.ExecuteNonQuery(); command.Parameters.Clear(); } catch (Exception ex) { ExceptionDeal(ex, sql, model.spList); } finally { conn.Dispose(); } } }
/// <summary> /// 用于执行Insert,返回ID /// </summary> public override object ExecuteScalar(SqlModel model) { string sql = GetSQL(model); using (SqlConnection conn = GetConnection(model)) { try { SqlCommand command = new SqlCommand(sql, conn); PreCommand(command, model.spList); object result = command.ExecuteScalar(); command.Parameters.Clear(); return(result); } catch (Exception ex) { conn.Dispose(); ExceptionDeal(ex, sql, model.spList.ToArray()); return(0); } } }
//-------------- public override DataTable SelPage(PageSetting config) { SqlToOracle(config); config.sql = "SELECT T.*,ROWNUM FROM ({0}) T WHERE ROWNUM>" + (config.cpage - 1) * config.psize + " AND ROWNUM<=" + config.cpage * config.psize; string innersql = ""; if (!string.IsNullOrEmpty(config.t2))//双表查询 { innersql = "SELECT " + config.fields + " FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on; config.countsql = "SELECT COUNT(*) FROM " + config.t1 + " A LEFT JOIN " + config.t2 + " B ON " + config.on; } else//单表查询 { innersql = "SELECT " + config.fields + " FROM " + config.t1 + " A "; config.countsql = "SELECT COUNT(*) FROM " + config.t1; } if (!string.IsNullOrEmpty(config.where)) { innersql += " WHERE " + config.where; config.countsql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { innersql += config.order; } config.sql = string.Format(config.sql, innersql); config.DealWithAlias(); //------------------ SqlModel countMod = new SqlModel() { sql = config.countsql }; countMod.AddSpToList(config.sp); SqlModel model = new SqlModel() { sql = config.sql }; model.AddSpToList(config.sp); config.itemCount = DataConvert.CLng(ExecuteScala(countMod)); config.pageCount = GetPageCount(config.itemCount, config.psize); return(ExecuteTable(model)); }
public override DataTable ExecuteTable(SqlModel model) { DataTable result = new DataTable(); using (MySqlConnection conn = GetConnection(model)) { MySqlCommand command = new MySqlCommand(GetSQL(model), conn); PreCommand(command, model.spList); MySqlDataAdapter adapter = new MySqlDataAdapter(); adapter.SelectCommand = command; DataSet dataSet = new DataSet(); adapter.Fill(dataSet, "Result"); command.Parameters.Clear(); if (dataSet.Tables.Count > 0) { return(dataSet.Tables["Result"]); } return(null); } }
public override DataTable ExecuteTable(SqlModel model) { OleDbDataAdapter command = new OleDbDataAdapter(); OleDbConnection conn = GetConnection(); string sql = GetSQL(model); try { DataTable dt = new DataTable(); command = new OleDbDataAdapter(sql, conn); command.Fill(dt); return(dt); } catch (Exception ex) { ExceptionDeal(ex, "查询失败"); return(null); } finally { conn.Dispose(); command.Dispose(); } }
public override DataTable JoinQuery(PageSetting config) { config.sql = "SELECT {0} FROM {1} A LEFT JOIN {2} B ON {3} "; config.sql = string.Format(config.sql, config.fields, config.t1, config.t2, config.on); if (!string.IsNullOrEmpty(config.where)) { config.sql += " WHERE " + config.where; } if (!string.IsNullOrEmpty(config.order)) { config.sql += config.order; } config.DealWithAlias(); SqlModel model = new SqlModel() { sql = config.sql, }; model.AddSpToList(config.sp); return(ExecuteTable(model)); }
public override string GetSQL(SqlModel model) { if (!string.IsNullOrEmpty(model.sql)) { return(model.sql); } string sql = ""; GetWhereSql(model); switch (model.cmd) { case SqlModel.SqlCmd.Insert: //string sql = string.Format("insert into [Sheet1$] values('{0}','{1}','{2}')", "123", "226", "775"); throw new Exception("Insert Limited"); case SqlModel.SqlCmd.Delete: throw new Exception("Delete Limited"); case SqlModel.SqlCmd.Update: throw new Exception("UPDATE Limited"); case SqlModel.SqlCmd.Select: //SELECT TOP 2 * FROM [Sheet1$] WHERE ID>1 ORDER BY ID DESC if (model.size > 0) { model.fields = " TOP " + model.size + " " + model.fields; } sql = "SELECT " + model.fields + " FROM " + model.tbName; if (!string.IsNullOrEmpty(model.where)) { sql += " WHERE " + model.where; } if (!string.IsNullOrEmpty(model.order)) { sql += " ORDER BY " + model.order; } break; } return(sql); }
//--------------Where模型 public override string GetWhereSql(SqlModel model) { string sql = ""; if (model.whereList == null || model.whereList.Count < 1) { return(sql); } foreach (Sql_Where where in model.whereList) { switch (where.type) { case "date": sql += GetDateSql(where); break; default: break; } } model.where += sql; return(sql); }
public abstract DataTable ExecuteTable(SqlModel model);
public abstract void ExecuteNonQuery(SqlModel model);
public override int InsertID(SqlModel model) { return(DataConvert.CLng(ExecuteScalar(model))); }
public abstract DbDataReader ExecuteReader(SqlModel model);
public abstract object ExecuteScalar(SqlModel model);
public abstract int InsertID(SqlModel model);
//--------------------Model方法(字段,参数,字段=参数) public abstract string GetWhereSql(SqlModel model);