using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Cyberpipe
{
    class OledbHelper1
    {
        private static SqlConnection conn;
        private static SqlCommand sqlCmd;
        private static SqlDataReader sqlReader;

        public static DataTable QueryTable(string cmdText)
        {
            try
            {
                if (conn == null)
                    conn = new SqlConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");

                conn.Open();
                sqlCmd = conn.CreateCommand();
                sqlCmd.CommandText = cmdText;
                sqlReader = sqlCmd.ExecuteReader();

                DataTable table = new DataTable();
                table.Load(sqlReader);

                sqlReader.Close();
                conn.Close();

                return table;

            }
            catch (Exception ex)
            {
                if (sqlReader != null)
                {
                    sqlReader.Close();
                    
                }
                if (conn != null)
                {
                    conn.Close();
                }
                MessageBox.Show(ex.Message, "提示");
               // LogError.PublishError(ex);
                return null;
            }

        }

        public static int ExecuteNonQuery(string cmdText)
        {
            OleDbConnection oleConn = new OleDbConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
            OleDbCommand oleCmd = new OleDbCommand();
            oleCmd.Connection = oleConn;
            oleCmd.CommandText = cmdText;
            int rowCount = 0;
            try
            {
                oleConn.Open();
                rowCount = oleCmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message, "提示");
            }
            finally
            {
                if (oleConn.State == ConnectionState.Open)
                {
                    oleConn.Close();
                }
            }
            return rowCount;
        }

        public static SqlConnection sqlConnection()
        {
            SqlConnection conn = new SqlConnection("Server=" + Utility.DBServer + ";Database=master;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + ";Trusted_Connection=false");
            return conn;
        }

        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] ops)
        {
            conn = new SqlConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
            try
            {
                conn.Open();
                sqlCmd = conn.CreateCommand();
                sqlCmd.CommandText = sql;
                foreach (SqlParameter op in ops)
                {
                    sqlCmd.Parameters.Add(op);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                //LogError.PublishError(ex);
                MessageBox.Show(ex.Message, "提示");
                return null;
            }
        }

        public static int sqlExecuteNonQuery(string sql)
        {
            conn = new SqlConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
            
            sqlCmd = conn.CreateCommand();
            sqlCmd.CommandText = sql;
            int rowCount = 0;
            try
            {
                conn.Open();
                rowCount = sqlCmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                rowCount = -1;
                //LogError.PublishError(ex);
                MessageBox.Show(ex.Message, "提示");
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return rowCount;
        }

        public static int ExecuteScalar(string sql)
        {
            conn = new SqlConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
            sqlCmd = conn.CreateCommand();
            sqlCmd.CommandText = sql;
            int num = 0;
            try
            {
                conn.Open();
                num = Convert.ToInt32(sqlCmd.ExecuteScalar().ToString());
            }
            catch(Exception ex)
            {
                num = -1;
               // LogError.PublishError(ex);
                MessageBox.Show(ex.Message, "提示");
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return num;
        }
    }
}