自动生成SQL语句(Oracle)

using System;
using System.Collections;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Cemic.Dal;

namespace Cemic.Bll
{
    
/// <summary>
    
/// 自动生成SQL语句 的摘要说明。
    
/// </summary>

    public abstract class BuildSql
    
{
        
protected static string str_TableName;        // 数据库表名
        protected static string str_KeyField;        // 关键字
        protected static string str_KeyValue;        // 关键字的值        

        
public BuildSql(){}        


        
#region 获取INSERT语句
        
public string GetInsertSql(HtmlForm Form1)
        
{
            
// 获取当前要新增记录的字段及值
            Hashtable htFields = GetFieldsList(Form1);
            
// 获取表中各字段类型
            Hashtable htType = GetFieldsType();
            
// 申明一个存放新增记录各字段的字符串
            string str_Fields = string.Empty;
            
// 申明一个存放新增记录各字段值的字符串
            string str_Values = string.Empty;

            
// 生成新增语句
            IDictionaryEnumerator enumeratorField = htFields.GetEnumerator();
            
// 遍历Hashtable表,对要更新操作的各字段进行类型判断
            while(enumeratorField.MoveNext())
            
{
                
if(htType.ContainsKey(enumeratorField.Key))
                
{
                    Type type 
= htType[enumeratorField.Key.ToString()] as Type;

                    str_Fields 
+= "," + enumeratorField.Key.ToString();
                    
                    str_Values 
+= "," + GetByTypeString(type, enumeratorField.Value.ToString());
                }

            }
    
            
// 添加关键字
            if(str_KeyField != string.Empty && str_KeyValue != string.Empty)
            
{
                str_Fields 
= str_KeyField  + str_Fields;
                str_Values 
= str_KeyValue + str_Values;
            }

            
else
            
{
                str_Fields 
= str_Fields.Substring(1, str_Fields.Length-1);
                str_Values 
= str_Values.Substring(1, str_Values.Length-1);
            }


            
string cmdText = "Insert into {0}({1}) Values({2})";
            cmdText 
= string.Format(cmdText, str_TableName, str_Fields, str_Values);
            
return cmdText;            
        }

        
#endregion


        
#region 获取UPDATE语句
        
public string GetUpdateSql(HtmlForm Form1, object obj_KeyValue)
        
// 获取当前要更新的字段及值
            Hashtable htFields = GetFieldsList(Form1);
            
// 获取表中各字段类型
            Hashtable htType = GetFieldsType();
            
// 申明一个存放更新操作的的字符串
            string str_Fields = string.Empty;
            
// 申明一个存放条件的字符串
            string str_Where = string.Empty;

            
// 生成更新语句
            IDictionaryEnumerator enumeratorField = htFields.GetEnumerator();
            
// 遍历Hashtable表,对要更新操作的各字段进行类型判断
            while(enumeratorField.MoveNext())
            
{
                
if(htType.ContainsKey(enumeratorField.Key))
                
{
                    Type type 
= htType[enumeratorField.Key.ToString()] as Type;
                    str_Fields 
+= "," + enumeratorField.Key.ToString() + "=";

                    str_Fields 
+= GetByTypeString(type, enumeratorField.Value.ToString());                    
                }

            }

            str_Fields 
= str_Fields.Substring(1, str_Fields.Length-1);
            
            
// 生成更新操作的条件;
            if(htType.ContainsKey(str_KeyField))
            
{
                str_Where 
= str_KeyField + "=";
                Type type 
= htType[str_KeyField] as Type;

                str_Where 
+= GetByTypeString(type, obj_KeyValue.ToString());
            }

            
else
            
{
                
throw(new Exception("您 操作的表有错误"));
            }
            

            
string cmdText = "Update {0} Set {1} Where {2}";
            cmdText 
= string.Format(cmdText, str_TableName, str_Fields, str_Where);
            
return cmdText;
            
        }

        
#endregion

        
        
#region 获取DELETE语句
        
public string GetDeleteSql(object obj_KeyValue)
        
{
            
// 获取表中各字段的类型
            Hashtable htType = GetFieldsType();
            
// 申明一个存放条件的字符串
            string str_Where = string.Empty;

            
// 生成删除操作的条件
            if(htType.ContainsKey(str_KeyField))
            
{
                str_Where 
= str_KeyField + "=";
                Type type 
= htType[str_KeyField] as Type;

                str_Where 
+= GetByTypeString(type, obj_KeyValue.ToString());                
            }

            
else
            
{
                
throw(new Exception("您 操作的表有错误"));
            }


            
string cmdText = "Delete {0} Where {1}";
            cmdText 
= string.Format(cmdText, str_TableName, str_Where);
            
return cmdText;
        }

        
#endregion

        
        
#region 获取类型字符串
        
private string GetByTypeString(Type type, string str_Value)
        
{
            
string str_Tmp = string.Empty;

            
// 对类型进行比较返回相对应的字符串
            if(type == typeof(string))                                    
                str_Tmp 
= "'" + str_Value + "'";    
            
else if(type == typeof(Decimal))
                str_Tmp 
= str_Value;
            
else if(type == typeof(DateTime))
            
{
                
if(str_Value.IndexOf(":"> 0)
                    str_Tmp 
= "To_Date('" + str_Value + "','yyyy-mm-dd hh24:mi:ss')";
                
else
                    str_Tmp 
= "To_Date('" + str_Value + "','yyyy-mm-dd')";
            }

            
            
// 返回类型字符串
            return str_Tmp;
        }

        
#endregion


        
#region 获取所要的字段及值
        
private Hashtable GetFieldsList(HtmlForm Form1)
        
{
            Hashtable Fields 
= new Hashtable();
            
            
for(int i=0; i<Form1.Controls.Count; i++)
            
{
                
if(Form1.Controls[i] is TextBox)
                
{
                    
// 转换控件
                    TextBox txtControl = Form1.Controls[i] as TextBox;
                    Fields.Add(txtControl.ID, txtControl.Text);
                }

                
else if(Form1.Controls[i] is HtmlInputText)
                
{
                    HtmlInputText txtControl 
= Form1.Controls[i] as HtmlInputText;
                    Fields.Add(txtControl.ID, txtControl.Value);
                }

            }


            
return Fields;
        }

        
#endregion


        
#region 获取所要的字段及类型
        
private Hashtable GetFieldsType()
        
{
            
string cmdText = "select * From " + str_TableName + " Where rownum <=1";

            SqlTextAction sqlAction 
= new SqlTextAction();
            DataSet ds 
= sqlAction.SelectByWhere(cmdText);
            sqlAction 
= null;            
            DataColumnCollection dc 
= ds.Tables[0].Columns;

            Hashtable Fields 
= new Hashtable();
        
            
for(int i=0; i<dc.Count; i++)
            
{
                
// 保存字段类型
                Fields.Add(dc[i].Caption, dc[i].DataType);
            }

            
            
return Fields;
        }

        
#endregion
    

    }


    
public class BuildSqlByArticle:BuildSql
    
{
        
public BuildSqlByArticle()
        
{
            str_TableName 
= "DIA_WEB_ARTICLE";
            str_KeyField 
= "AR_ID";
            str_KeyValue 
= "DIA_WEB_AR_ID.Nextval";
        }

    }

}

posted @ 2010-11-16 10:30  gllg  阅读(831)  评论(1编辑  收藏  举报