asp.net将sql语句封装在类库中

将sql语句封装在cs中,通过类库的引用使用他的select、update、insert

源代码(cs):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; //包括from、where、group by、order by...
namespace BLL
{//------------------------------------------------------------------------------------------------
/// <summary>
/// 构建 SQL 语句
/// </summary>
[Serializable]
public class BuildSQL
{ #region 字段 private int g_intStart;
private int g_intRowCnt;
private string g_strField;
private string g_strFrom;
private string g_strWhere;
private string g_strOrderBy;
private string g_strGroupBy;
private string g_strOther; #endregion #region 属性 //--------------------------------------------------------------------------------------------
/// <summary>
/// 取得数据开始编号
/// </summary>
public int Start
{
get { return g_intStart; }
set { g_intStart = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 取得记录数
/// </summary>
public int RowCnt
{
get { return g_intRowCnt; }
set { g_intRowCnt = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 字段名
/// </summary>
public string Field
{
get { return g_strField; }
set { g_strField = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 表名
/// </summary>
public string From
{
get { return g_strFrom; }
set { g_strFrom = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 条件
/// </summary>
public string Where
{
get { return g_strWhere; }
set { g_strWhere = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 排序
/// </summary>
public string OrderBy
{
get { return g_strOrderBy; }
set { g_strOrderBy = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 分组
/// </summary>
public string GroupBy
{
get { return g_strGroupBy; }
set { g_strGroupBy = value; }
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 其它
/// </summary>
public string Other
{
get { return g_strOther; }
set { g_strOther = value; }
} #endregion #region 方法 //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
public BuildSQL()
{
g_strField = "*";
g_strFrom = "";
g_strWhere = "";
g_strOrderBy = "";
g_strGroupBy = "";
g_strOther = "";
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
/// <param name="m_strFrom">表名</param>
public BuildSQL(string m_strFrom)
: this()
{
g_strFrom = m_strFrom;
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
/// <param name="m_strField">字段名</param>
/// <param name="m_strFrom">表名</param>
public BuildSQL(string m_strField, string m_strFrom)
: this()
{
g_strField = m_strField;
g_strFrom = m_strFrom;
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
/// <param name="m_strField">字段名</param>
/// <param name="m_strFrom">表名</param>
/// <param name="m_strWhere">条件</param>
public BuildSQL(string m_strField, string m_strFrom, string m_strWhere)
: this()
{
g_strField = m_strField;
g_strFrom = m_strFrom;
g_strWhere = m_strWhere; } public BuildSQL(string m_strField, string m_strFrom, string m_strWhere, string m_strGroupBy)
: this()
{
g_strField = m_strField;
g_strFrom = m_strFrom;
g_strWhere = m_strWhere;
g_strGroupBy = m_strGroupBy; } //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
/// <param name="m_strField">字段名</param>
/// <param name="m_strFrom">表名</param>
/// <param name="m_strWhere">条件</param>
/// <param name="m_strOrderBy">排序</param>
//public BuildSQL(string m_strField, string m_strFrom, string m_strWhere, string m_strOrderBy)
// : this()
//{
// g_strField = m_strField;
// g_strFrom = m_strFrom;
// g_strWhere = m_strWhere;
// g_strOrderBy = m_strOrderBy;
//} //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
/// <param name="m_strField">字段名</param>
/// <param name="m_strFrom">表名</param>
/// <param name="m_strWhere">条件</param>
/// <param name="m_strOrderBy">排序</param>
/// <param name="m_strGroupBy">分组</param>
public BuildSQL(string m_strField, string m_strFrom,
string m_strWhere, string m_strOrderBy, string m_strGroupBy)
: this()
{
g_strField = m_strField;
g_strFrom = m_strFrom;
g_strWhere = m_strWhere;
g_strOrderBy = m_strOrderBy;
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 初始化构建 SQL 语句
/// </summary>
/// <param name="m_strField">字段名</param>
/// <param name="m_strFrom">表名</param>
/// <param name="m_strWhere">条件</param>
/// <param name="m_strOrderBy">排序</param>
/// <param name="m_strGroupBy">分组</param>
/// <param name="m_strOther">其它</param>
public BuildSQL(string m_strField, string m_strFrom, string m_strWhere,
string m_strOrderBy, string m_strGroupBy, string m_strOther)
: this()
{
g_strField = m_strField;
g_strFrom = m_strFrom;
g_strWhere = m_strWhere;
g_strOrderBy = m_strOrderBy;
g_strOther = m_strOther;
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 获取统计记录数的 SQL 语句
/// </summary>
/// <returns>返回 SQL 语句</returns>
public string getRowTotalSQL()
{
return "SELECT COUNT(1) " + getFromSQL();
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 构建 SQL 语句
/// </summary>
/// <returns>返回 SQL 语句</returns>
public string getSQL()
{
return "SELECT " + g_strField + getFromSQL()
+ (g_strOrderBy != "" ? " ORDER BY " + g_strOrderBy : "")
+ (g_strGroupBy != "" ? " GROUP BY " + g_strGroupBy : "")
+ g_strOther;
} //--------------------------------------------------------------------------------------------
/// <summary>
/// 获取除字段信息外的 SQL 语句
/// </summary>
/// <returns>返回 SQL 语句</returns>
private string getFromSQL()
{
return " FROM " + g_strFrom
+ (g_strWhere != "" ? " WHERE " + g_strWhere : "");
} #endregion }
}

实例:将sql server数据库中的数据导入到station的gridview中

连接到数据库:

<appSettings>
<!--数据库连接字符串-->
<add key="DBConnString" value="Data Source=localhost;database=manager;uid=sa;pwd=sa;" />
</appSettings>

在station页面page_load时加载dataBind(sender,e)方法:

 private void dataBind(object sender, System.EventArgs e)
{
#region SELECT FIELD 语句 StringBuilder sbField = new StringBuilder();
sbField.Append(" a.id");
sbField.Append(" ,a.tname");
sbField.Append(" ,a.agency ");
sbField.Append(" ,a.province");
sbField.Append(" ,a.city");
sbField.Append(" ,a.address");
sbField.Append(" ,a.long");
sbField.Append(" ,a.lat");
sbField.Append(" ,a.error");
sbField.Append(" ,a.tperson");
sbField.Append(" ,a.perphone");
sbField.Append(" ,b.sname");
sbField.Append(" ,b.sperson");
sbField.Append(" ,b.sphone");
sbField.Append(" ,a.bgtime");
sbField.Append(" ,a.endtime");
sbField.Append(" ,a.ctime"); #endregion SELECT FIELD 语句 #region SELECT FROM 语句 StringBuilder sbFrom = new StringBuilder();
sbFrom.Append(" station a ");
//将left join语句添加至此
sbFrom.Append(" left join service b on b.id = a.service ");
//sbFrom.Append(" left join engineer c on c.service = b.id "); #endregion SELECT FROM 语句 #region SELECT WHERE 语句 List<SqlParameter> paramList = new List<SqlParameter>();
StringBuilder sbWhere = new StringBuilder(); sbWhere.Append(" 1 = 1 "); if (!string.IsNullOrWhiteSpace(TextBox1.Text))
{
sbWhere.Append(" and a.tname like @tname ");
paramList.Add(new SqlParameter("@tname", "%" + TextBox1.Text + "%"));
}
if (!string.IsNullOrWhiteSpace(TextBox2.Text))
{
sbWhere.Append(" and a.city like @city ");
paramList.Add(new SqlParameter("@city", "%" + TextBox2.Text + "%"));
} #endregion SELECT WHERE 语句 //StringBuilder sbGroupBy = new StringBuilder();
//sbGroupBy.Append(" group by a.tname "); #region SELECT 语句 //SQL语句
BuildSQL bsSQL = new BuildSQL
(sbField.ToString(), sbFrom.ToString(), sbWhere.ToString());
//传递参数
SqlParameter[] bsParam = paramList.ToArray(); #endregion SELECT 语句 #region 分页绑定 uc_GvPage.RowTotal = GvDataBind.getRowTotal(bsSQL, bsParam);
uc_GvPage.init_Lbl(); //获取显示的数据
bsSQL.Start = uc_GvPage.RowNum;
bsSQL.RowCnt = uc_GvPage.RowCount;
GvDataBind.dataBind(GridView1, bsSQL, bsParam); #endregion 分页绑定
}

至此,实例分析完毕。

源码网址:这里写链接内容

登录名:高阳

密码:gaoyang

版权声明:本文为博主原创文章,未经博主允许不得转载。

上一篇:std::string的Copy-on-Write:不如想象中美好(VC不使用这种方式,而使用对小字符串更友好的SSO实现)


下一篇:刚刚完成了在vs2013中通过 ef连接mysql数据库的工作。感觉没有想象中的简单。试了n次终于成功。故记录成功的方法,希望可以帮到大家