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