数据库操作类，可执行数据库简单操作

可对数据库进行简单操作

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.OracleClient;

/// <summary>
/// 数据库操作类，目前仅支持sqlserver数据库增删改选基本操作。暂未对oracle数据库编写相关操作，但原理基本相同
/// </summary>
namespace MyDatabase
{
    public class myDatabase
    {
        public static string defaultDB = @"server=192.168.128.231;database=yunwei;uid=sa;pwd=123";
        /// <summary>
        /// 读取sqlserver数据库数据
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        protected static SqlDataReader ReadSqlData(string cmd, string connection)
        {
            SqlConnection sc = new SqlConnection(connection);
            if (OpenSql(ref sc))
            {

            }
            else
                sc.Open();
            SqlCommand sCmd = new SqlCommand(cmd, sc);
            return sCmd.ExecuteReader();
        }
        /// <summary>
        /// 在sqlserver中执行sql语句
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="connection"></param>
        protected static void ExecuteSqlCmd(string cmd, string connection)
        {
            SqlConnection sc = new SqlConnection(connection);
            if (OpenSql(ref sc))
            {

            }
            else
                sc.Open();
            SqlCommand sCmd = new SqlCommand(cmd, sc);
            sCmd.ExecuteNonQuery();
            sc.Close();
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <param name="sc">一个SqlConnection实例的引用</param>
        /// <param name="server">服务器</param>
        /// <param name="database">数据库名称</param>
        /// <param name="uid">用户名</param>
        /// <param name="pwd">密码</param>
        /// <returns>返回一个bool型的值,如果成功连接返回true,失败则返回false</returns>
        protected static bool OpenSql(ref SqlConnection sc)
        {
            try
            {
                sc.Open();
            }
            catch(Exception ex)
            {
                CatchException(ex);
                return false;
            }
            return true;
        }

        protected static void ExecuteOracleCmd(string cmd, string ip)
        {
            OracleConnection oCon = new OracleConnection("user id = cdm;password=cdm; (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)" +
 "(HOST = " + ip + ")(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))");
            OracleCommand oCmd = new OracleCommand(cmd, oCon);
            oCmd.ExecuteNonQuery();
            oCon.Close();
        }
        protected static OracleDataReader ReadOracleData(string cmd, string ip)
        {
            OracleConnection oCon = new OracleConnection("user id = cdm;password=cdm; (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)" +
                "(HOST = " + ip + ")(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))");
            OracleCommand oCmd = new OracleCommand(cmd, oCon);
            OracleDataReader oDr = oCmd.ExecuteReader();
            return oDr;
        }

        /// <summary>
        /// 将符合条件的列值更新至SQL数据库
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列集合，Colum类中包含ColumnName与Value属性</param>
        /// <param name="conditions">条件集合，Condition类中包含ColumnName与Value属性</param>
        protected static void UpdateTable(string tableName, Column[] columns, Condition[] conditions)
        {
            string values = "", condition = "";
            foreach (Column c in columns)
            {
                values += c.ColumnName + "='" + c.Value + "'";
                if (c != columns[columns.Length - 1])
                    values += ",";
            }
            foreach (Condition c in conditions)
            {
                condition += c.ColumnName + "='" + c.Value + "'";
                if (c != conditions[conditions.Length - 1])
                    condition += " and ";
            }
            string cmd = "update " + tableName + " set " + values + " where " + condition;
            ExecuteSqlCmd(cmd, defaultDB);
        }
        /// <summary>
        /// 将数据插入SQL数据库
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列集合，Colum类中包含ColumnName与Value属性</param>
        protected static void InsertTable(string tableName, Column[] columns)
        {
            string column = "", value = "";
            foreach (Column c in columns)
            {
                column += c.ColumnName;
                value += "'" + c.Value + "'";
                if (c != columns[columns.Length - 1])
                {
                    column += ","; value += ",";
                }
            }
            string cmd = "insert into " + tableName + " (" + column + ") values (" + value + ")";
            ExecuteSqlCmd(cmd, defaultDB);
        }
        /// <summary>
        /// 从数据库中选择符合条件的列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列集合，Colum类中包含ColumnName与Value属性</param>
        /// <param name="conditions">条件集合，Condition类中包含ColumnName与Value属性</param>
        /// <returns></returns>
        protected static object SelectTable(string tableName, Column[] columns, Condition[] conditions/*,string databaseType*/)
        {
            object reader = null;
            string column = "", condition = "";
            string databaseType = "sql";
            foreach (Column c in columns)
            {
                column += c.ColumnName;
                if (c != columns[columns.Length - 1])
                    column += ",";
            }
            foreach (Condition c in conditions)
            {
                condition += c.ColumnName + "='" + c.Value + "'";
                if (c != conditions[conditions.Length - 1])
                    condition += " and ";
            }
            string cmd = "select " + column + " from " + tableName + " where " + condition;
            if (databaseType == "sql")
            {
                reader = ReadSqlData(cmd, defaultDB);
            }
            else if (databaseType == "oracle")
            {
                reader = ReadOracleData(cmd, "");
            }
            return reader;
        }
        /// <summary>
        /// 删除数据库中符合条件的表数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="conditions">条件集合，Condition类中包含ColumnName与Value属性</param>
        protected static void DeleteTable(string tableName, Condition[] conditions)
        {
            string condition="";
            foreach (Condition c in conditions)
            {
                condition += c.ColumnName + "='" + c.Value + "'";
                if (c != conditions[conditions.Length - 1])
                    condition += " and ";
            }
            string cmd = "delete from "+tableName+" where "+condition;
            ExecuteSqlCmd(cmd,defaultDB);
        }
        public static void CatchException(Exception ex)
        {
            string cmd = @"insert into Service_Error_List (message,source,stackTrace,time) values ('" + ex.Message + "','" + ex.Source + "','" + ex.StackTrace + "','" + DateTime.Now + "')";
            ExecuteSqlCmd(cmd, defaultDB);
        }


        protected class Column
        {
            private string columnName;
            private string value;
            public Column(string columnName, string value)
            {
                ColumnName = columnName;
                Value = value;
            }
            public Column(string columnName)
            {
                ColumnName = columnName;
                Value = "";
            }

            public string ColumnName
            {
                get
                {
                    return columnName;
                }

                set
                {
                    columnName = value;
                }
            }

            public string Value
            {
                get
                {
                    return value;
                }

                set
                {
                    this.value = value;
                }
            }
        }

        protected class Condition
        {
            private string columnName;
            private string value;
            public Condition(string columnName, string value)
            {
                ColumnName = columnName;
                Value = value;
            }
            public string ColumnName
            {
                get
                {
                    return columnName;
                }

                set
                {
                    columnName = value;
                }
            }

            public string Value
            {
                get
                {
                    return value;
                }

                set
                {
                    this.value = value;
                }
            }
        }
    }
}

引用该类后，可参考以下例子进行调用，免去在源码中写入大量sql语句。

 

Column[] columns; Condition[] conditions;
            columns = new Column[1] { new Column("*") };
            conditions = new Condition[3] { new Condition("date", date), new Condition("module_name", module), new Condition("ip", ip) };
            SqlDataReader dr = (SqlDataReader)SelectTable("checkIP_result", columns, conditions);