DAL

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using JYK;

namespace Database
{
    public class DAL
    {
        #region 运行查询语句返回记录（DataSet、DataTable、DataRow、String[]、String）

        public static DataSet RunSqlDataSet(string sql)
        {
            string msg = "";
            return RunSqlDataSet(sql, out msg);
        }

        /// <summary>
        /// 运行SQL查询语句 返回DataSet。可以传入多条查询语句，返回的DataSet里会有多个DataTable
        /// </summary>
        /// <param name="SQL">查询语句。比如select * from tableName</param>
        #region 函数实现 — — RunSqlDataSet
        public static DataSet RunSqlDataSet(string sql, out string ErrorMsg)
        {
            ErrorMsg = "";
            //设置DataAdapter
            DbDataAdapter da = Factory.CreateDataAdapter(sql);
            try
            {
                DataSet DS = new DataSet();
                da.Fill(DS);
                return DS;
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlDataSet", sql, ex.Message);    //处理错误
                ErrorMsg = ex.Message + "<BR>RunSqlDataSet_SQL:" + sql;
                return null;
            }
            finally
            {
                //关闭数据库，销毁实例
                Factory.DbDataAdapterDispose(da);

            }
        }
        #endregion

        public static DataTable RunSqlDataTable(string sql)
        {
            string msg = "";
            return RunSqlDataTable(sql, out msg);

        }

        /// <summary>
        /// 运行SQL查询语句 返回DataTable。
        /// </summary>
        /// <param name="SQL">查询语句。比如select * from tableName</param>
        ///
        #region 函数实现 — — RunSqlDataTable
        public static DataTable RunSqlDataTable(string sql, out string ErrorMsg)
        {
            ErrorMsg = "";
            //设置DataAdapter
            DbDataAdapter da = Factory.CreateDataAdapter(sql);
            try
            {
                DataTable DT = new DataTable();
                da.Fill(DT);
                return DT;
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlDataTable", sql, ex.Message);    //处理错误
                ErrorMsg = ex.Message + "<BR>RunSqlDataSet_SQL:" + sql;
                return null;
            }
            finally
            {
                //关闭数据库，销毁实例
                Factory.DbDataAdapterDispose(da);
            }
        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回第一条记录。返回DataRow
        /// </summary>
        /// <param name="SQL">查询语句。比如select * from tableName</param>
        ///
        #region 函数实现 — — RunSqlDataRow
        public static DataRow RunSqlDataRow(string sql)
        {
            //设置DataAdapter
            DbDataAdapter da = Factory.CreateDataAdapter(sql);
            try
            {
                DataTable DT = new DataTable();
                da.Fill(DT);
                if (DT.Rows.Count > 0)
                    return DT.Rows[0];
                else
                    return null;
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlDataRow", sql, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                //关闭数据库，销毁实例
                Factory.DbDataAdapterDispose(da);
            }

        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回第一条记录的数组。返回字符串数组
        /// </summary>
        /// <param name="SQL">查询语句。比如select top 1 * from tableName</param>
        ///
        #region 函数实现 — — RunSqlStrings
        public static string[] RunSqlStrings(string sql)
        {
            //传入查询语句，返回第一条记录的字符串数组
            //设置command
            DbCommand cm = Factory.CreateCommand(sql);

            try
            {
                cm.Connection.Open();

                DbDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
                if (r.Read())
                {
                    int ArrLength = r.FieldCount;

                    string[] strValue = new string[ArrLength];
                    for (int i = 0; i < ArrLength; i++)
                        strValue[i] = r.GetValue(i).ToString();
                    return strValue;
                }
                else
                {
                    r.Close();
                    return null;
                }
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlStrings", sql, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                cm.Connection.Close();
                cm.Connection.Dispose();
                cm.Dispose();

            }
        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回每一条记录的第一个字段的数组。返回字符串数组
        /// </summary>
        /// <param name="SQL">查询语句。比如select myName from tableName</param>
        ///
        #region 函数实现 — — RunSqlStringsByRow
        public static string[] RunSqlStringsByRow(string sql)
        {

            //传入查询语句，每一条记录的第一个字段的数组。返回字符串数组
            //设置command
            DbCommand cm = Factory.CreateCommand(sql);

            try
            {
                cm.Connection.Open();

                DbDataReader r = cm.ExecuteReader();

                System.Collections.Generic.List<string> list = new List<string>();
                while (r.Read())
                    list.Add(r[0].ToString());

                return list.ToArray();

            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlStringsByRow", sql, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                cm.Connection.Close();
                cm.Connection.Dispose();
                cm.Dispose();

            }
        }
        #endregion

        /// <summary>
        /// 运行SQl语句返回第一条记录的第一列的值。
        /// </summary>
        /// <param name="SQL">查询语句。比如select top 1 ID from tableName where userName='aa'。会返回ID的内容</param>
        ///
        #region 函数实现 — — RunSqlGetID
        public static string RunSqlGetID(string sql)
        {
            //设置command
            DbCommand cm = Factory.CreateCommand(sql);

            try
            {
                cm.Connection.Open();

                DbDataReader r = cm.ExecuteReader(CommandBehavior.SingleRow);
                if (r.Read())
                    return r.GetValue(0).ToString();
                else
                    return null;
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlGetID", sql, ex.Message);    //处理错误
                return null;
            }
            finally
            {
                cm.Connection.Close();
                cm.Connection.Dispose();
                cm.Dispose();
            }
        }
        #endregion

        #endregion
        #region 运行查询语句不返回记录集（无返回记录、检查持否存在指定的记录）
        /// <summary>
        /// 运行SQL查询语句，不返回记录集。用于添加、修改、删除等操作
        /// </summary>
        /// <param name="SQL">查询语句。比如insert into tableName 、update tableName</param>
        ///
        #region 函数实现 — — RunSql
        public static void RunSql(string sql)
        {
            //设置command
            DbCommand cm = Factory.CreateCommand(sql);

            try
            {
                cm.Connection.Open();
                cm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSql", sql, ex.Message);    //处理错误
            }
            finally
            {
                cm.Connection.Close();
                cm.Connection.Dispose();
                cm.Dispose();
            }
        }
        #endregion

        /// <summary>
        /// 执行一条SQL语句，看是否能查到记录 有：返回true；没有返回false，用于判断是否重名
        /// </summary>
        /// <param name="SQL">查询语句。比如select ID from tableName where userName='aa'</param>
        ///
        #region 函数实现 — — RunSqlExists
        public static bool RunSqlExists(string sql)
        {
            //设置command
            DbCommand cm = Factory.CreateCommand(sql);

            try
            {
                if (cm.Connection.State == ConnectionState.Broken || cm.Connection.State == ConnectionState.Closed)
                    cm.Connection.Open();

                DbDataReader r = cm.ExecuteReader(CommandBehavior.CloseConnection);
                if (r.HasRows)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                WriteLog.SetErrorMsg("RunSqlDataSet", sql, ex.Message);    //处理错误
                return true;
            }
            finally
            {
                cm.Connection.Close();
                cm.Connection.Dispose();
                cm.Dispose();
            }
        }
        #endregion

        #endregion
        #region 查询语句的方式添加、修改数据

        /// <summary>
        /// 添加记录。传入表名，字段数组，值数组，返回新生成记录的ID
        /// </summary>
        /// <param name="TableName">要添加记录的表的名称</param>
        /// <param name="ziduan">字段名数组</param>
        /// <param name="msg">字段对应的值的数组</param>
        /// <returns></returns>
        public static string InsertDataStr(string TableName, string[] columns, string[] msg)
        {
            //添加数据    返回新添加的ID
            System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);
            SQL.Append("insert into ");                    //insert into
            SQL.Append(TableName);
            SQL.Append(" (");
            int i;
            for (i = 0; i < columns.Length - 1; i++)        //字段
            {
                SQL.Append(columns[i]);
                SQL.Append(",");
            }
            SQL.Append(columns[i]);
            SQL.Append(")  values ('");

            for (i = 0; i < columns.Length - 1; i++)
            {
                SQL.Append(msg[i]);
                SQL.Append("','");
            }
            SQL.Append(msg[i]);
            if (WebConfig.DataBaseType() == 1)
                SQL.Append("')  select scope_identity() as a1");
            else
                SQL.Append("')");

            string re = RunSqlGetID(SQL.ToString());
            SQL.Length = 1;
            if (re == null)
                return "-1";
            else
                return re;
        }

        /// <summary>
        /// 修改记录。传入表名，字段数组，值数组
        /// </summary>
        /// <param name="TableName">要修改记录的表的名称</param>
        /// <param name="ziduan">字段名数组</param>
        /// <param name="msg">字段对应的值的数组</param>
        /// <param name="tiaojian">条件 ，加在where 后面的语句</param>
        /// <returns></returns>
        public static bool UpdateData(string TableName, string[] cloumns, string[] msg, string myWhere)
        {
            System.Text.StringBuilder SQL = new System.Text.StringBuilder(300);
            SQL.Append("update ");                    //update
            SQL.Append(TableName);
            SQL.Append(" set ");
            int i;
            for (i = 0; i < cloumns.Length - 1; i++)
            {
                SQL.Append(cloumns[i]);                    //update
                SQL.Append("='");
                SQL.Append(msg[i]);
                SQL.Append("',");
            }
            SQL.Append(cloumns[i]);                    //update
            SQL.Append("='");
            SQL.Append(msg[i]);
            SQL.Append("' where ");
            SQL.Append(myWhere);

            RunSql(SQL.ToString());
            return true;
        }
        #endregion
    }
}