生成sql语句

[asp]代码库

///
/// 枚举查询排序方式
///
public enum OrderType
{
	DESC,
	ASC
}

public sealed class SqlComposition
{
	#region Select语句的组合
///
/// 组合查询语句
///
/// 字段名称
/// 数据库表名，可以传入一个组合的语句
/// 查询条件
/// 排序字段
/// 排序方式
/// 返回组合的语句
	public String SelectSql ( String fieldName, String tableName, String strWhere, String orderField, OrderType ot )
	{
		if ( String.IsNullOrEmpty ( tableName ) )
		{
			return null;
		}
		StringBuilder sb = new StringBuilder();
		sb.Append ( " SELECT " );
		if ( String.IsNullOrEmpty ( fieldName ) )
		{
			sb.Append ( " * " );
		}
		else
		{
			sb.Append ( fieldName );
		}
		sb.Append ( " FROM " );
		sb.Append ( tableName );
		if ( !String.IsNullOrEmpty ( strWhere ) )
		{
			sb.Append ( " WHERE " );
			sb.Append ( strWhere );
		}
		if ( !String.IsNullOrEmpty ( orderField ) )
		{
			sb.Append ( " ORDER BY " );
			sb.Append ( orderField );
			switch ( ot )
			{
			case OrderType.ASC:
				sb.Append ( " ASC " );
				break;
			case OrderType.DESC:
			default:
				sb.Append ( " DESC " );
				break;
			}
		}
		return sb.ToString();
	}

///
/// 组合查询语句,默认降序排列
///
/// 字段名称
/// 数据库表名，可以传入一个组合的语句
/// 查询条件
/// 排序字段
/// 返回组合的语句
	public String SelectSql ( String fieldName, String tableName, String strWhere, String orderField )
	{
		return SelectSql ( fieldName, tableName, strWhere, orderField, OrderType.DESC );
	}

///
/// 组合查询语句，无查询条件
///
/// 字段名称
/// 数据库表名，可以传入一个组合的语句
/// 排序方式
/// 排序字段
/// 返回组合的语句
	public String SelectSql ( String fieldName, String tableName, OrderType ot, String orderField )
	{
		return SelectSql ( fieldName, tableName, null, orderField, ot );
	}

///
/// 组合查询语句，无查询字段
///
/// 数据库表名，可以传入一个组合的语句
/// 查询条件
/// 排序字段
/// 排序方式
/// 返回组合的语句
	public String SelectSql ( String tableName, String strWhere, String orderField, OrderType ot )
	{
		return SelectSql ( null, tableName, strWhere, orderField, ot );
	}

///
/// 组合查询语句，无排序方式
///
/// 查询字段
/// 数据库表名，可以传入一个组合的语句
/// 查询条件
/// 返回组合的语句
	public String SelectSql ( String fieldName, String tableName, String strWhere )
	{
		return SelectSql ( fieldName, tableName, strWhere, null );
	}

///
/// 组合查询语句
///
/// 查询字段
/// 表名，可以传入一个组合的语句
/// 返回组合的语句
	public String SelectSql ( String fieldName, String tableName )
	{
		return SelectSql ( fieldName, tableName, null );
	}

///
/// 组合查询语句
///
/// 数据库表名，可以传入一个组合的语句
/// 返回组合的语句
	public String SelectSql ( String tableName )
	{
		return SelectSql ( null, tableName );
	}

///
/// 组合查询语句
///
/// 需要查询的表名集合
/// 需要进行连接的字符串
/// 需要查询的字段,用来判断是否允许删除的字段
/// 需要查询的表名,主要是防止字段名称重复
/// 需要查询的条件,如果有相同的字段,请加上表名
/// 返回组合的语句
	public String SelectSql ( ArrayList al, String fieldName, String searchFieldName, String searchTableName, String strWhere )
	{
		StringBuilder sb = new StringBuilder();
		for ( int i = 0; i < al.Count; i++ )
		{
			if ( i == 0 )
			{
				if ( !String.IsNullOrEmpty ( searchFieldName ) )
				{
					if ( !String.IsNullOrEmpty ( searchTableName ) )
					{
						sb.Append ( SelectSql ( searchTableName + "." + searchFieldName, al[0].ToString() ) );
					}
					else
					{
						sb.Append ( SelectSql ( searchFieldName, al[0].ToString() ) );
					}
				}
				else
				{
					sb.Append ( SelectSql ( al[0].ToString() ) );
				}
			}
			else
			{
				sb.Append ( " inner join " );
				sb.Append ( al[i] );
				sb.Append ( " on " );
				sb.Append ( al[0] + "." + fieldName );
				sb.Append ( "=" );
				sb.Append ( al[i] + "." + fieldName );
			}
		}
		if ( String.IsNullOrEmpty ( strWhere ) )
		{
			sb.Append ( " WHERE " );
			sb.Append ( strWhere );
		}
		return sb.ToString();
	}

///
/// 组合查询语句
///
/// 需要查询的表名集合
/// 需要进行连接的字符串
/// 需要查询的字段,用来判断是否允许删除的字段
/// 需要查询的条件,如果有相同的字段,请加上表名
/// 返回组合的语句
	public String SelectSql ( ArrayList al, String fieldName, String searchFieldName, String strWhere )
	{
		return SelectSql ( al, fieldName, searchFieldName, null, strWhere );
	}

///
/// 组合查询语句
///
/// 需要查询的表名集合
/// 需要进行连接的字符串
/// 需要查询的字段,用来判断是否允许删除的字段
/// 返回组合的语句
	public String SelectSql ( ArrayList al, String fieldName, String searchFieldName )
	{
		return SelectSql ( al, fieldName, searchFieldName, null );
	}

//public String SelectSql(Hashtable tablename, Hashtable joinfield, String strWhere)
//{
//StringBuilder sb = new StringBuilder();
//StringBuilder sbtable = new StringBuilder();
//sb.Append(" select ");
//foreach (DictionaryEntry ti in tablename)
//{

// sbtable.Append(" "+ti.Key);

// if ((ti.Value.ToString()).IndexOf(",") > 0)
// {
// ArrayList al = (ArrayList)(ti.Value.ToString()).Split(",");
// for (int i = 0; i < al.Count; i++)
// {
// sb.Append(" "+ ti.Key + "." + al[i]);
// }
// }
// else
// {
// sb.Append(" " + ti.Key + "." + (ti.Value.ToString()));
// }

//}
//sb.Append(" inner join ");
//sb.Append(" "+sbtable);
//sb.Append(" on ");
//foreach(DictionaryEntry ji in joinfield)
//{
// sb.Append(" " + ji.Key + "." + ji.Value.ToString()+" = ");

//}
//sb.Remove(sb.Length - 1, 1);
//sbtable.Remove(sbtable.Length - 1, 1);

//if (String.IsNullOrEmpty(strWhere))
//{
// sb.Append(" WHERE ");
// sb.Append(strWhere);
//}
//return sb.ToString();

//}

	#endregion

///
/// 组合更新语句
///
/// 数据库表名
/// Hashtable 用于记录字段名和值
/// 更新条件
/// 返回Update语句
	public String UpdateSelect ( String tableName, Hashtable ht, String strWhere )
	{
		if ( String.IsNullOrEmpty ( tableName ) || ht.Count == 0 )
		{
			return null;
		}
		StringBuilder sb = new StringBuilder();
		sb.Append ( " UPDATE " );
		sb.Append ( tableName );
		sb.Append ( " SET " );
		foreach ( DictionaryEntry ide in ht )
		{
			sb.Append ( ide.Key );
			sb.Append ( "='" );
			sb.Append ( ide.Value.ToString().Replace ( "'", "''" ) );
			sb.Append ( "'" );
			sb.Append ( "," );
		}
		sb.Remove ( sb.Length - 1, 1 );
		if ( String.IsNullOrEmpty ( strWhere ) )
		{
			sb.Append ( " WHERE " );
			sb.Append ( strWhere );
		}
		return sb.ToString();
	}

///
/// 组合虚拟删除语句
///
/// 数据库表名
/// 查询条件
/// 返回组合的Sql语
	public String DeleteSql ( String tableName, String strWhere )
	{
		if ( String.IsNullOrEmpty ( tableName ) || String.IsNullOrEmpty ( strWhere ) )
		{
			return null;
		}
		StringBuilder sb = new StringBuilder();
		sb.Append ( " Update " );
		sb.Append ( tableName );
		sb.Append ( " set IsDelete=1 " );
		sb.Append ( " WHERE " );
		sb.Append ( strWhere );
		return sb.ToString();
	}

///
/// 组合彻底删除语句
///
/// 数据库表名
/// 查询条件
/// 返回组合的Sql语
	public String DeleteFactSql ( String tableName, String strWhere )
	{
		if ( String.IsNullOrEmpty ( tableName ) || String.IsNullOrEmpty ( strWhere ) )
		{
			return null;
		}
		StringBuilder sb = new StringBuilder();
		sb.Append ( " Delete from " );
		sb.Append ( tableName );
		sb.Append ( " WHERE " );
		sb.Append ( strWhere );
		return sb.ToString();
	}
///
/// 组合插入语句
///
/// 数据库表名
/// 记录字段，值的Hashtable
/// 返回组合的Sql语句
	public String InsertSql ( String tableName, Hashtable ht )
	{
		if ( String.IsNullOrEmpty ( tableName ) || ht.Count == 0 )
		{
			return null;
		}
		StringBuilder sb = new StringBuilder();
		sb.Append ( "INSERT INTO " );
		sb.Append ( tableName );
		sb.Append ( "(" );
		StringBuilder tempSb = new StringBuilder();
		foreach ( DictionaryEntry ide in ht ) //枚举Hshtable记录的数据
		{
			sb.Append ( ide.Key );
			sb.Append ( "," );
			tempSb.Append ( "'" );
			tempSb.Append ( ide.Value.ToString().Replace ( "'", "''" ) );
			tempSb.Append ( "'" );
			tempSb.Append ( "," );
		}
		sb.Remove ( sb.Length - 1, 1 );
		tempSb.Remove ( tempSb.Length - 1, 1 );
		sb.Append ( ") VALUES (" );
		sb.Append ( tempSb.ToString() );
		tempSb.Remove ( 0, tempSb.Length );
		sb.Append ( ")" );
		return sb.ToString();
	}

}

调用DEMO
ViewState["basedata"] = Request.QueryString["basedata"].ToString().Trim();
ViewState["basecode"] = Request.QueryString["basecode"].ToString().Trim();
ViewState["basename"] = Request.QueryString["basename"].ToString().Trim();
Hashtable ht=new Hashtable ( 1 );
ht.Add ( ViewState["basename"].ToString(), txt_name.Text.Trim() );
list.Add ( sc.UpdateSelect ( ViewState["basedata"].ToString(), ht, ViewState["basecode"] + "='" + txt_bianma.Text.Trim() + "'" ) );


int updateCount = ExecuteSqlTran ( list );
/////
/// 执行多条SQL语句，实现数据库事务。
///
/// 多条SQL语句
public static int ExecuteSqlTran ( List SQLStringList )
{
	using ( SqlConnection conn = new SqlConnection ( connectionString ) )
	{
		conn.Open();
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		SqlTransaction tx = conn.BeginTransaction();
		cmd.Transaction = tx;
		try
		{
			int count = 0;
			for ( int n = 0; n < SQLStringList.Count; n++ )
			{
				string strsql = SQLStringList[n];
				if ( strsql.Trim().Length > 1 )
				{
					cmd.CommandText = strsql;
					count += cmd.ExecuteNonQuery();
				}
			}
			tx.Commit();
			return count;
		}
		catch
	{
		tx.Rollback();
			return -1;
		}
		finally
		{
			if ( conn.State == ConnectionState.Open )
			{
				conn.Close();
			}
		}
	}
}