dotnet下生成的SQL语句(简易版)代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 dotnet下生成的SQL语句(简易版)
作者:Dezai.CN / 发布于2012/8/23/ 593
dotnet下生成的SQL语句
<div>/// <summary>
/// 枚举查询排序方式
/// </summary>
public enum  OrderType
{
DESC,
ASC
}

public sealed class  SqlComposition
{
#region Select语句的组合
/// <summary>
///  组合查询语句
/// </summary>
/// <param  name="fieldName">字段名称</param>
/// <param  name="tableName">数据库表名，可以传入一个组合的语句</param>
/// <param  name="strWhere">查询条件</param>
/// <param  name="orderField">排序字段</param>
/// <param  name="ot">排序方式</param>
///  <returns>返回组合的语句</returns>
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();
}

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

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

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

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

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

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

///  <summary>
/// 组合查询语句
/// </summary>
/// <param  name="al">需要查询的表名集合</param>
/// <param  name="fieldName">需要进行连接的字符串</param>
/// <param  name="searchFieldName">需要查询的字段,用来判断是否允许删除的字段</param>
/// <param  name="searchTableName">需要查询的表名,主要是防止字段名称重复</param>
/// <param  name="strWhere">需要查询的条件,如果有相同的字段,请加上表名</param>
///  <returns>返回组合的语句</returns>
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();
}

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

///  <summary>
/// 组合查询语句
/// </summary>
/// <param  name="al">需要查询的表名集合</param>
/// <param  name="fieldName">需要进行连接的字符串</param>
/// <param  name="searchFieldName">需要查询的字段,用来判断是否允许删除的字段</param>
///  <returns>返回组合的语句</returns>
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

/// <summary>
/// 组合更新语句
///  </summary>
/// <param name="tableName">数据库表名</param>
///  <param name="ht">Hashtable 用于记录字段名和值</param>
/// <param  name="strWhere">更新条件</param>
///  <returns>返回Update语句</returns>
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();
}

///  <summary>
/// 组合虚拟删除语句
/// </summary>
/// <param  name="tableName">数据库表名</param>
/// <param  name="strWhere">查询条件</param>
///  <returns>返回组合的Sql语</returns>
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();
}

///  <summary>
/// 组合彻底删除语句
/// </summary>
/// <param  name="tableName">数据库表名</param>
/// <param  name="strWhere">查询条件</param>
///  <returns>返回组合的Sql语</returns>
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();
}
///  <summary>
/// 组合插入语句
/// </summary>
/// <param  name="tableName">数据库表名</param>
/// <param  name="ht">记录字段，值的Hashtable</param>
///  <returns>返回组合的Sql语句</returns>
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);
/////  <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
///  <param name="SQLStringList">多条SQL语句</param>        
public static  int ExecuteSqlTran(List<String> 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();
}
}
}
}
</div>
试试其它关键字
　生成的SQL语句　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)