/// <summary>
/// 查询字符串最多支持2个表
/// </summary>
/// <param name="top">填写0时代表全部</param>
/// <param name="selname">查询字段名称如有2个表带表名</param>
/// <param name="sqltab">查询表的名称前面为主表</param>
/// <param name="sqlJoin">链接字符串前面为主表</param>
/// <param name="sqlwhere">条件语句</param>
/// <param name="SqlOrder">排序字段</param>
/// <param name="SqlOrderdc">排序方向</param>
/// <returns></returns>
public string SqlStrInfo(int top,string[] selname, string [] sqltab, string []sqlJoin,string [] sqlwhere,string [] SqlOrder,string [] SqlOrderdc)
{ string aa = @"SELECT BigSort.title, MidSort.bid, MidSort.istop, BigSort.id, MidSort.title AS Expr1
FROM BigSort INNER JOIN
MidSort ON BigSort.id = MidSort.bid";
StringBuilder str = new StringBuilder();
str.Append(" select ");
if (top == 0)
{
str.Append(" top " + top + " ");
}
if (selname != null)
{
for (int i = 0; i < selname.Length; i++)
{
str.Append(selname[i].ToString());
if (selname.Length - 1 != i)
{
str.Append(" , ");
}
}
}
else
{
str.Append(" * ");
}
str.Append(" from ");
if (sqltab.Length == 2)
{
str.Append(sqltab[0].ToString() + " INNER JOIN " + sqltab[1].ToString() + " on ");
str.Append(sqlJoin[0] + " = " + sqlJoin[1]);
}
else
{
str.Append( sqltab[0].ToString() );
} if (sqlwhere != null)
{
str.Append(" where ");
for (int i = 0; i < sqlwhere.Length; i++)
{
str.Append(sqlwhere[i].ToString() + " = @" + sqlwhere[i]);
if (sqlwhere.Length - 1 != i)
{
str.Append(" and ");
}
}
}
if (SqlOrder != null)
{
str.Append(" order by ");
for (int i = 0; i < SqlOrder.Length; i++)
{
str.Append(SqlOrder[i].ToString() + " " + SqlOrderdc[i].ToString());
if (SqlOrder.Length - 1 != i)
{
str.Append(" , ");
}
}
}
return str.ToString();
}
我想问下这个sql拼接语句好不好。另希望求一个拼接语句功能类似上面这个的谢谢
/// 查询字符串最多支持2个表
/// </summary>
/// <param name="top">填写0时代表全部</param>
/// <param name="selname">查询字段名称如有2个表带表名</param>
/// <param name="sqltab">查询表的名称前面为主表</param>
/// <param name="sqlJoin">链接字符串前面为主表</param>
/// <param name="sqlwhere">条件语句</param>
/// <param name="SqlOrder">排序字段</param>
/// <param name="SqlOrderdc">排序方向</param>
/// <returns></returns>
public string SqlStrInfo(int top,string[] selname, string [] sqltab, string []sqlJoin,string [] sqlwhere,string [] SqlOrder,string [] SqlOrderdc)
{ string aa = @"SELECT BigSort.title, MidSort.bid, MidSort.istop, BigSort.id, MidSort.title AS Expr1
FROM BigSort INNER JOIN
MidSort ON BigSort.id = MidSort.bid";
StringBuilder str = new StringBuilder();
str.Append(" select ");
if (top == 0)
{
str.Append(" top " + top + " ");
}
if (selname != null)
{
for (int i = 0; i < selname.Length; i++)
{
str.Append(selname[i].ToString());
if (selname.Length - 1 != i)
{
str.Append(" , ");
}
}
}
else
{
str.Append(" * ");
}
str.Append(" from ");
if (sqltab.Length == 2)
{
str.Append(sqltab[0].ToString() + " INNER JOIN " + sqltab[1].ToString() + " on ");
str.Append(sqlJoin[0] + " = " + sqlJoin[1]);
}
else
{
str.Append( sqltab[0].ToString() );
} if (sqlwhere != null)
{
str.Append(" where ");
for (int i = 0; i < sqlwhere.Length; i++)
{
str.Append(sqlwhere[i].ToString() + " = @" + sqlwhere[i]);
if (sqlwhere.Length - 1 != i)
{
str.Append(" and ");
}
}
}
if (SqlOrder != null)
{
str.Append(" order by ");
for (int i = 0; i < SqlOrder.Length; i++)
{
str.Append(SqlOrder[i].ToString() + " " + SqlOrderdc[i].ToString());
if (SqlOrder.Length - 1 != i)
{
str.Append(" , ");
}
}
}
return str.ToString();
}
我想问下这个sql拼接语句好不好。另希望求一个拼接语句功能类似上面这个的谢谢
解决方案 »
- 为什么我的网站每6小时重启一次。
- 我写了个存储过程查询返回结果是张表格。该表格的字段是动态生成的,现在我想把这张边显示到页面中控件(gridview或datalist)中。怎么实现?
- 三层分离不想在逻辑曾声明SqlDataReader各类型的变量想在一个单独的类中实现[高手指点]
- VS2005开发电脑配置
- 水晶报表在Web中可不可以即打即停
- 求助!!寻找--ASP.NET web应用程序开发新思维--英文版e-book
- 为什么我的出错页面的字体很小
- 如何在javascript里锁定dropdownlist控件
- 为什么我提的问题没人及时回答了?
- 高分请教有关表格拆分问题
- ASP.net 选项卡控件
- 谁有论坛和博客整合好的,发给我一份啊,跪求啊
string [] tab={"bigsort","midsort"};
string [] tabjoin={"bigsort.id","midsort.id"};
string [] sqlor={"bigsort.id","midsort.id"};
string [] sqlordc={"desc","desc"};
string [] sqlwhere={"bid.id"};
string sqlstr = sqlhp.SqlStrInfo(0, null, tab, tabjoin, sqlwhere, sqlor, sqlordc);
SqlParameter[] par =
{
new SqlParameter("@bid.id",SqlDbType.Int)
};
par[0].Value = id;
/// <summary>
/// 根据ID删除
/// </summary>
/// <param name="tbname">表名</param>
/// <returns> where id=@id</returns>
public string DelStr(string tbname)
{
StringBuilder str = new StringBuilder();
str.Append(" delete from " + tbname);
str.Append(" where id=@id ");
return str.ToString();
}
以后调用删除语句可以直接通过传递 表名和id 值就能删除了。不 需要在写查询语句了。现在刚学这些。所有有些不太懂想问下。
public void DelBigSort(int id)
{
SqlParameter[] par =
{
new SqlParameter("@id",SqlDbType.Int)
};
par[0].Value = id;
sqlhp.ExCmdNon(sqlhp.DelStr("BigSort"), par);
}
主要是写sql太多看着不舒服。