将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
版权声明：本文为博主原创文章，未经博主允许不得转载。