/// <summary>
/// Union
/// </summary>
/// <returns></returns>
public static SqlDataReader Union()
{
//检查
string sql = "select * from BaseTable union select * from Tables";
//不检查
string sql1 = "select * from BaseTable union select * from Tables";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 连接查询
/// </summary>
/// <returns></returns>
public static SqlDataReader Join()
{
//内连接
string sql = "select * from BaseTable join tabname on BaseTable.id=tabname.id";
//左连接
string sql1 = "select * from BaseTable left join tabname on BaseTable.id=tabname.id";
//右连接
string sql2 = "select * from BaseTable right join tabname on BaseTable.id=tabname.id";
//完全外连接
string sql3 = "select * from BaseTable full join tabname on BaseTable.id=tabname.id";
//交叉外连接
string sql4 = "select * from BaseTable cross join tabname ";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 添加数据
/// </summary>
public static int Add()
{
string sql = "insert into BaseTable(name,age) values(@name,@age)";
return(SQLHelps.ExecuteNonQuery(sql, new SqlParameter("@name", "张三"), new SqlParameter("@age", 18)));
}
public static SqlDataReader SearchColumn(string table)
{
string sql = "select name from syscolumns where id=object_id('" + table + "')";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 查询数据库所有表名
/// </summary>
/// <returns></returns>
public static SqlDataReader SearchTable()
{
string sql = "select name from sysobjects where type='U'";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 添加列
/// </summary>
public static int AddCloumn(string column, string type)
{
string sql = "Alter table tabname add " + column + " " + type;
return(SQLHelps.ExecuteNonQuery(sql));
}
/// <summary>
/// 删除表
/// </summary>
public static int DropTable()
{
string sql = "drop table tabname";
return(SQLHelps.ExecuteNonQuery(sql));
}
/// <summary>
/// Intersect
/// </summary>
/// <returns></returns>
public static SqlDataReader Intersect()
{
string sql = "select id from BaseTable intersect select id from tabname";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 求平均数
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <returns></returns>
public static int Avg(string table, string column)
{
string sql = "select avg(" + column + ") as avgvalue from " + table;
return((int)SQLHelps.ExecuteScalar(sql));
}
/// <summary>
/// 提交事务
/// </summary>
public static void Save()
{
SQLHelps.ExecuteNonQueryNo(SqlList);
}
public static void UpdateNo(List <string> list)
{
SQLHelps.ExecuteNonQueryNo(list);
}
/// <summary>
/// 创建表
/// </summary>
public static int AddTable()
{
string sql = "create table tabname(id int primary key )";
return(SQLHelps.ExecuteNonQuery(sql));
}
public static SqlDataReader ProcBaseTable(string procname, params SqlParameter[] param)
{
return(SQLHelps.ExecuteReaderSP(procname, param));
}
/// <summary>
/// 查询范围
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列名</param>
/// <param name="value1">开始值</param>
/// <param name="value2">结束值</param>
/// <returns></returns>
public static SqlDataReader Between(string table, string column, string value1, string value2)
{
string sql = "select * from " + table + " where " + column + " between " + value1 + " and " + value2;
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 查询指定表的数据
/// </summary>
/// <param name="table"></param>
public static SqlDataReader Search(string table)
{
string sql = "select count(*) from " + table;
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 根据条件模糊查询指定表总数
/// </summary>
/// <param name="table">查询的表</param>
/// <param name="value">条件值</param>
/// <returns></returns>
public static SqlDataReader SearchLike(string table, string value)
{
string sql = "select count(*) from " + table + " where name like '%" + value + "%'";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 根据条件修改指定表字段的值
/// </summary>
/// <param name="table">表名</param>
/// <param name="column1">修改的列</param>
/// <param name="value1">修改的值</param>
/// <param name="column2">条件列</param>
/// <param name="value2">条件值</param>
/// <returns></returns>
public static int Update(string table, string column1, string value1, string column2, string value2)
{
string sql = "update " + table + " set " + column1 + "=" + value1 + " where " + column2 + "=" + value2;
return((int)SQLHelps.ExecuteNonQuery(sql));
}
