public static CommonPage GetPageRaw(string connStr,string sql,int pageIndex,int pageSize)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
db.CommandText = sql;
db.CommandType = CommandType.Text;
DataTable dt = db.ExecuteDataTable();
CommonPage cp = new CommonPage();
cp.RecordCount = db.TotalCount;
int pg = 0;
pg = cp.RecordCount / pageSize;
if (cp.RecordCount % pageSize > 0)
{
pg++;
}
cp.PageCount = pg;
cp.DataTable = dt;
db.Close();
return cp;
}
public static CommonPage GetCombinePage(string connStr, string table1,string table2, string[] fields1,string[] fields2,string on, string where, string orderby, int pageIndex, int pageSize)
{
string sql = "select ";
for (int i = 0; i < fields1.Length; i++)
{
sql += "" + table1 + "." + fields1[i];
//if (i != fields1.Length - 1)
sql += ",";
}
for (int i = 0; i < fields2.Length; i++)
{
sql += "" + table2 + "." + fields2[i];
if (i != fields2.Length - 1)
sql += ",";
}
if (fields1 == null || fields2 == null)
{
sql += " * ";
}
sql += " from " + table1 +" ";
sql += " left outer join "+ table2+" on " + on;
if (!string.IsNullOrEmpty(where))
sql += " where " + where + " ";
if (!string.IsNullOrEmpty(orderby))
sql += " order by " + orderby;
sql = sql.Trim();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
db.CommandText = sql;
db.CommandType = CommandType.Text;
DataTable dt = db.ExecuteDataTable(pageSize, pageIndex);
CommonPage cp = new CommonPage();
cp.RecordCount = db.TotalCount;
cp.DataTable = dt;
db.Close();
return cp;
}
public static int Insert(string connStr, string tableName, string[] fields, object[] values)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
string sql = "insert into " + tableName + " (";
for (int i = 0; i < fields.Length; i++)
{
sql += fields[i];
if (i != fields.Length - 1)
sql += ",";
if (values[i] == null)
values[i] = "";
db.AddParameter(fields[i], values[i]);
}
sql += ")values(";
for (int i = 0; i < values.Length; i++)
{
sql += "@"+fields[i]+"";
if (i != values.Length - 1)
sql += ",";
}
sql += ")";
db.CommandText = sql;
db.CommandType = CommandType.Text;
int ret = db.ExecuteNonQuery();
db.Close();
return ret;
}
public static string GetValue(string connStr, string sql)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
db.CommandText = sql;
db.CommandType = CommandType.Text;
object str = db.ExecuteScalar();
db.Close();
if (str == null)
return null;
else
return Convert.ToString(str);
}
public static CommonPageWithCat GetPageWithCat(string connStr, string tableName, string listfield, string where, string orderby, int pageIndex, int pageSize)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
string sql = "select " + listfield + " from " + tableName + " ";
if (!string.IsNullOrEmpty(where))
sql += " where " + where + " ";
if (!string.IsNullOrEmpty(orderby))
sql += " order by " + orderby + " ";
sql = sql.Trim();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
db.CommandText = sql;
db.CommandType = CommandType.Text;
DataTable dt = db.ExecuteDataTable(pageSize, pageIndex);
CommonPageWithCat cp = new CommonPageWithCat();
cp.RecordCount = db.TotalCount;
int pg = 0;
pg = cp.RecordCount / pageSize;
if (cp.RecordCount % pageSize > 0)
{
pg++;
}
cp.PageCount = pg;
cp.DataTable = dt;
db.Close();
return cp;
}
private static DataRow SelectSingleModel(string connStr, string tableName, string listfield, string where)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
int pageIndex = 1;
int pageSize = 1;
string orderby = "id desc";
string sql = "select " + listfield + " from " + tableName + " ";
if (!string.IsNullOrEmpty(where))
sql += " where " + where + " ";
if (!string.IsNullOrEmpty(orderby))
sql += " order by " + orderby + " ";
sql = sql.Trim();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
db.CommandText = sql;
db.CommandType = CommandType.Text;
DataTable dt = db.ExecuteDataTable(pageSize, pageIndex);
CommonPage cp = new CommonPage();
cp.RecordCount = db.TotalCount;
cp.DataTable = dt;
db.Close();
if (cp != null && cp.DataTable != null && cp.DataTable.Rows.Count > 0)
{
return cp.DataTable.Rows[0];
}
else
return null;
}
public static int Delete(string connStr, string tableName, string where)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
string sql = "delete from " + tableName ;
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
db.CommandText = sql;
db.CommandType = CommandType.Text;
int ret = db.ExecuteNonQuery();
db.Close();
return ret;
}
public static int Update(string connStr, string tableName, string[] fields,object[] values, string where)
{
if (string.IsNullOrEmpty(connStr))
connStr = GetConnectionStr();
DBHandler db = DBHandlerFactory.GetHandler(connStr);
string sql = "update " + tableName + " set ";
for (int i = 0; i < fields.Length; i++)
{
sql += fields[i] + "=@" + fields[i];
if (i != fields.Length - 1)
sql += ",";
string v = (string)values[i];
if (v == null)
v = "";
db.AddParameter(fields[i], v);
}
if (!string.IsNullOrEmpty(where))
sql += " where " + where;
db.CommandText = sql;
db.CommandType = CommandType.Text;
int ret = db.ExecuteNonQuery();
db.Close();
return ret;
}