自动生成SQL语句(Oracle)

 

Posted on 2006-06-01 10:11 做回自己 阅读(814) 评论(3)  编辑 收藏 引用 网摘 所属分类: C#

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(){}        


        获取INSERT语句#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

        获取UPDATE语句#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
        
        获取DELETE语句#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";
        }
    }
}