XS202408319330/DataServices/SQLDataBase.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataServices;
using System.Data;
using System.Data.SqlClient;

namespace DataServices
{
    public class SQLDataBase:DataBase
    {
        SqlConnection con;
        public SQLDataBase()
        {
            con = new SqlConnection();
        }
        public SQLDataBase(string dataFile):this()
        {
            DataFile = dataFile;
        }
        public override void ExecuteNonQuery(string sqlCommand)
        {
            using (SqlCommand com = new SqlCommand())
            {
                com.CommandText = sqlCommand;
                com.ExecuteNonQuery();
                if (!IsAlwaysOpen)
                {
                    if(con != null)
                    con.Dispose();
                }
            }
        }

        public override object Execute(string sqlCommand)
        {
            DataTable table = new DataTable();
            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter())
                {
                    using (SqlCommand com = new SqlCommand())
                    {
                        if (con == null)
                        {
                            if (ConStr != null)
                            {
                                Open(ConStr);
                            }
                        }
                        com.Connection = con;
                        com.CommandText = sqlCommand;
                        if (sqlCommand.Contains("select") || sqlCommand.Contains("SELECT") || sqlCommand.Contains("Select"))
                        {
                            da.SelectCommand = com;
                            da.Fill(table);
                        }
                        else if (sqlCommand.Contains("insert") || sqlCommand.Contains("INSERT") || sqlCommand.Contains("Insert"))
                        {
                            da.InsertCommand = com;
                            da.Fill(table);
                        }
                        else if (sqlCommand.Contains("update") || sqlCommand.Contains("UPDATE") || sqlCommand.Contains("Update"))
                        {
                            da.UpdateCommand = com;
                            da.Fill(table);
                        }
                        else if (sqlCommand.Contains("delete") || sqlCommand.Contains("DELETE") || sqlCommand.Contains("Delete"))
                        {
                            da.DeleteCommand = com;
                            da.Fill(table);
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (!IsAlwaysOpen)
                {
                    if(con != null)
                    con.Dispose();
                }
            }
            return table;
        }
        
        public override void Open()
        {
            
        }

        public override void Close()
        {
            if (con != null)
            {
                con.Dispose();
            }
        }



        public override void Open(string dataSource, string dataFile, string userName = null, string passWord = "")
        {
            if (string.IsNullOrEmpty(userName))
            {
                if (dataFile.Contains(@"\"))
                {
                    ConStr = @"Data Source=" + dataSource + ";AttachDbFilename=" + dataFile + ";Integrated Security=True";
                }
                else
                {
                    ConStr = @"Data Source=" + dataSource + @";AttachDbFilename=|DataDirectory|\" + dataFile + ";Integrated Security=True";
                }
                
            }
            else
            {
                ConStr = @"server=" + dataSource + ";database=" + dataFile + ";Integrated Security=False;" + "user=" + userName + ";pwd=" + passWord;
            }

            if (con == null)
            {
                con = new SqlConnection();
            }
            con.ConnectionString = ConStr;
            try
            {
                con.Open();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public override void Open(string conStr)
        {
            try
            {
                if (con == null)
                    con = new SqlConnection();
                con.ConnectionString = conStr;
                con.Open();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        public override void ExecuteNonQuery(string sqlCommand, string conStr)
        {
            if (con == null)
            {
                con = new SqlConnection();
            }
            try
            {
                con.Close();
                con.ConnectionString = ConStr;
                con.Open();
                ExecuteNonQuery(sqlCommand);
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public override object Execute(string sqlCommand, string conStr)
        {
            if (con == null)
            {
                con = new SqlConnection();
            }
            try
            {
                con.Close();
                con.ConnectionString = ConStr;
                con.Open();
                return Execute(sqlCommand);
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}