用户注册



邮箱：

密码：

用户登录


邮箱：

密码：
记住登录一个月忘记密码？

发表随想


还能输入：200字
云代码 - asp代码库

生成sql语句

2012-10-19 作者： 程序猿style举报

[asp]代码库

view sourceprint?
///
/// 枚举查询排序方式
///
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();
            }
        }
    }
}


网友评论    (发表评论)

共1 条评论 1/1页

发表评论：

评论须知：

  • 1、评论每次加2分，每天上限为30；
  • 2、请文明用语，共同创建干净的技术交流环境；
  • 3、若被发现提交非法信息，评论将会被删除，并且给予扣分处理，严重者给予封号处理；
  • 4、请勿发布广告信息或其他无关评论，否则将会删除评论并扣分，严重者给予封号处理。


扫码下载

加载中，请稍后...

输入口令后可复制整站源码

加载中，请稍后...