/src/Blizzard/DAL/SqlClientPlugin.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data;



namespace Blizzard.DAL

{

  public  class SqlClientPlugin : IDAO

    {



        SqlConnection _cnn = new SqlConnection();



        public SqlClientPlugin(string connectionString)

	    {

            _cnn.ConnectionString = connectionString;

	    }



        public DataTable GetDataTable(string table,string whereClause)

        {

                SqlCommand cmd = new SqlCommand( "Select * from " + table + " " + whereClause);

                return getDataTable(cmd);

        }

        public DataTable GetDataTable(string table, string identityFieldName ,int id)

        {

            SqlCommand cmd = new SqlCommand("Select * From " + table + " where " + identityFieldName + " = " + id);

            return getDataTable(cmd);

        }

        public DataTable GetManyToManyAssociates(string associateTable, string joinTable, string associatedId, string joinTableAssociateId, string joinTableMainId, int mainObjectId)

        {

           



            string sql = "Select A.* From " + associateTable + " A inner join " + joinTable +

                 " J on A." + associatedId + " = J." + joinTableAssociateId +

                 " where " + joinTableMainId + " = " + mainObjectId;



            SqlCommand cmd = new SqlCommand(sql);



            return getDataTable(cmd);

        }



        public DataTable GetParent(string associateTable, string mainTable, string associateTableIdField, string associateIdFieldInMainTable, string mainTableIdField, int mainObjectId)

        {

    

          string  sql = "select A.* from " + associateTable + " A inner join " +

                     mainTable + " M on A." + associateTableIdField + " = M." + associateIdFieldInMainTable +

                     " where M." + mainTableIdField + " = " + mainObjectId;





          SqlCommand cmd = new SqlCommand(sql);

    

          return getDataTable(cmd);

        }



        public DataTable GetChilderen(string associateTable, string mainTable, string mainTableIdFieldInAssociateTable, string mainTableIdField, int mainObjectId)

        {

           

           string sql = "select A.* from " + associateTable + " A inner join " +

                          mainTable + " M on A." + mainTableIdFieldInAssociateTable + " = M." + mainTableIdField +

                          " where M." + mainTableIdField + " = " + mainObjectId;





           SqlCommand cmd = new SqlCommand(sql);



           return getDataTable(cmd);

        }

        

        public void JoinTableDeleter(string joinTable, string fieldNameOne, string fieldNameTwo, int filedOneValue, int fieldTwoValue)

        {



            string sql = " Delete from " + joinTable + " where " +

                    fieldNameOne + " = " + filedOneValue + " and " +

                    fieldNameTwo + " = " + fieldTwoValue;



            SqlCommand cmd = new SqlCommand(sql);



            executeNonQuery(cmd);

  

        }

        

        public void DeleteRecord(string tableName, string identityFieldName, int identityFieldValue)

        {

            string sql = " delete from " + tableName + " where " + identityFieldName + " = " + identityFieldValue;



            SqlCommand cmd = new SqlCommand(sql);

       

            executeNonQuery(cmd);

        }

        public void Insert(string tableName, Dictionary<string, object> fieldValuePairs)

        {



            SqlCommand cmd = new SqlCommand();

      

            string sql = "INSERT INTO " + tableName + " ( ";



            foreach (KeyValuePair<string,object> fieldValue in fieldValuePairs)

            {

                string field = fieldValue.Key;



                //A field can eventualy contain '[' or ']' characters 

                //(for example if the field name is a reserved word or

                //the filed name contains spaces), thats why for the name

                //of a parameter we should delete them

                string parameterName = field.Replace("[", "");

                parameterName = parameterName.Replace("]", "");

                parameterName = parameterName.Replace(" ", "");

                parameterName = "@" + parameterName;



                object propertyValue = fieldValue.Value;



                if (propertyValue == null)

                {

                    cmd.Parameters.Add(new SqlParameter(parameterName, DBNull.Value));

                }

                else

                {

                    cmd.Parameters.Add(new SqlParameter(parameterName, propertyValue));

                }

                    

                sql = sql + field + ",";

           

            }



            sql = sql.TrimEnd(',');

            sql = sql + ") VALUES (";



            foreach (SqlParameter parameter in cmd.Parameters)

            {

                    sql = sql + parameter + ",";

            }



            sql = sql.TrimEnd(',');

            sql = sql + ")";





            cmd.CommandText = sql;

            executeNonQuery(cmd);

            

        }

        

        public int GetTheLastInsertedId(string tableName , string identityFieldName)

        {

            SqlCommand cmd = new SqlCommand("Select MAX(" + identityFieldName + ") From " + tableName);

            return (int)executeScalar(cmd);

        }

  

        public void Update(string tableName, string identityFieldName, int identityFieldValue, Dictionary<string, object> fieldValuePairs)

        {





            SqlCommand cmd = new SqlCommand();

        

            string sql = "Update " + tableName + " set ";

            

            foreach (KeyValuePair<string, object> fieldValue in fieldValuePairs)

            {

                string field = fieldValue.Key;



                //A field can eventualy contain '[' or ']' characters 

                //(for example if the field name is a reserved word or

                //the filed name contains spaces), thats why for the name

                //of a parameter we should delete them

                string parameterName = field.Replace("[", "");

                parameterName = parameterName.Replace("]", "");

                parameterName = parameterName.Replace(" ", "");

                parameterName = "@" + parameterName;

                

                object propertyValue = fieldValue.Value;



                SqlParameter parameter = new SqlParameter(parameterName, propertyValue);

                cmd.Parameters.Add(parameter);

                sql = sql + field + " = " + parameter + ",";

             }



            sql = sql.TrimEnd(',');

            sql = sql + " where " + identityFieldName + " = " + identityFieldValue;



            cmd.CommandText = sql;

            

            executeNonQuery(cmd);



        }

    

        public void JoinTableInserter(string joinTable, string fieldNameOne, string fieldNameTwo, int filedValueOne, int fieldValueTwo)

        {

            SqlCommand cmd = new SqlCommand();



            //A field can eventualy contain '[' or ']' characters 

            //(for example if the field name is a reserved word or

            //the filed name contains spaces), thats why for the name

            //of a parameter we should delete them

            string parameterName1 = fieldNameOne.Replace("[", "");

            parameterName1 = parameterName1.Replace("]", "");

            parameterName1 = parameterName1.Replace(" ", "");

            parameterName1 = "@" + parameterName1;



            string parameterName2 = fieldNameTwo.Replace("[", "");

            parameterName2 = parameterName2.Replace("]", "");

            parameterName2 = parameterName2.Replace(" ", "");

            parameterName2 = "@" + parameterName2;



            SqlParameter parameter1 = new SqlParameter(parameterName1, filedValueOne);

            SqlParameter parameter2 = new SqlParameter(parameterName2, fieldValueTwo);



            cmd.Parameters.Add(parameter1);

            cmd.Parameters.Add(parameter2);





            string sql = "Insert into " + joinTable + " ( " + fieldNameOne +

                " , " + fieldNameTwo + " ) Values ( " + parameter1 + " , " + parameter2 + " ) ";



            cmd.CommandText = sql;

            

            executeNonQuery(cmd);

        }

      

        public void SetParentId(string childTable, string childIdField, int childId, string parentIdFieldInChildTable, int parentIdValue)

        {

            string sql = " Update " + childTable + " set " + parentIdFieldInChildTable + " = " + parentIdValue + "  where " +

             childIdField + " = " + childId;



            SqlCommand cmd = new SqlCommand(sql);

       

            executeNonQuery(cmd);

        }



        //===============================================private methods==================================================

        //===============================================private methods==================================================

        //===============================================private methods==================================================

        //===============================================private methods==================================================



        private void executeNonQuery(SqlCommand cmd)

        {

            cmd.Connection = _cnn;



            if (_cnn.State != ConnectionState.Open)

                _cnn.Open();



            cmd.ExecuteNonQuery();

        }

        private object executeScalar(SqlCommand cmd)

        {

            cmd.Connection = _cnn;



            if (_cnn.State != ConnectionState.Open)

                _cnn.Open();



            return cmd.ExecuteScalar();

        }

        private DataTable getDataTable(SqlCommand cmd)

        {

            DataTable dt = new DataTable();



            if (_cnn.State != ConnectionState.Open)

                _cnn.Open();



            cmd.Connection = _cnn;

            SqlDataAdapter da = new SqlDataAdapter(cmd);



            da.Fill(dt);



            return dt;

        }



    }

}