Exemplo n.º 1
0
    public DataTable getDataTable(string SQLQuery, string connectionstring)
    {
        DataTable      dt   = new DataTable();
        SqlCommand     comm = new SqlCommand();
        SqlDataAdapter adap = new SqlDataAdapter();

        try
        {
            comm.CommandText   = SQLQuery.Trim();
            comm.Connection    = getConnection(connectionstring);
            comm.CommandType   = CommandType.Text;
            adap.SelectCommand = comm;
            adap.Fill(dt);
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Get Data From SQL Database", exp.Message + "(query:" + SQLQuery + ")", true, exp);
        }
        finally
        {
            if (comm.Connection.State > 0)
            {
                comm.Connection.Close();
            }
            comm.Connection.Dispose();
            comm.Dispose();
            adap.Dispose();
        }
        return(dt);
    }
Exemplo n.º 2
0
 public void RollBack(SqlCommand comm)
 {
     try
     {
         if (comm == null)
         {
             return;
         }
         if (comm.Transaction != null)
         {
             comm.Transaction.Rollback();
         }
         else
         {
             return;
         }
         if (comm.Connection.State > 0)
         {
             comm.Connection.Close();
         }
         if (comm != null)
         {
             comm.Dispose();
         }
     }
     catch (Exception exp)
     {
         ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
         clslog.Log("Roll Back SQL Query Execution", exp.Message, true, exp);
     }
 }
Exemplo n.º 3
0
    public bool ExecuteNonQuery(string query, SqlParameter[] SqlPar, string connectionstring, CommandType comm_type, out string ret_msg)
    {
        bool res = false;

        ret_msg = "";
        try
        {
            SqlConnection conn = getConnection(connectionstring);
            SqlCommand    comm = new SqlCommand(query, conn);
            comm.CommandType = comm_type;
            comm.Parameters.AddRange(SqlPar);
            int rAff = comm.ExecuteNonQuery();
            res = rAff > 0 ? true : false;
            if (conn.State > 0)
            {
                conn.Close();
            }
            comm.Dispose();
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Execute Non Query in SQL with Par", exp.Message + "(query:" + query + ")", true, exp);
            ret_msg = exp.ToString();
        }
        return(res);
    }
Exemplo n.º 4
0
    public DataTable getDataTable(string SQLQuery, SqlParameter[] SqlPar, string connectionstring, CommandType comm_type)
    {
        DataTable      dt   = new DataTable();
        SqlCommand     comm = new SqlCommand();
        SqlDataAdapter adap = new SqlDataAdapter();

        try
        {
            comm             = new SqlCommand(SQLQuery.Trim(), getConnection(connectionstring));
            comm.CommandType = comm_type;
            comm.Parameters.AddRange(SqlPar);
            adap = new SqlDataAdapter(comm);
            adap.Fill(dt);
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Get Data From SQL Database with Par", SQLQuery + "-" + exp.Message + "(query:" + SQLQuery + ")", true, exp);
        }
        finally
        {
            if (comm.Connection.State > 0)
            {
                comm.Connection.Close();
            }
            comm.Connection.Dispose();
            comm.Dispose();
            adap.Dispose();
        }
        return(dt);
    }
Exemplo n.º 5
0
    public bool ExecuteNonQuery(string query, string connectionstring, bool need_log, out string ret_msg)
    {
        bool res = false;

        ret_msg = "";
        try
        {
            SqlConnection conn = getConnection(connectionstring);
            SqlCommand    comm = new SqlCommand(query, conn);
            int           rAff = comm.ExecuteNonQuery();
            res = rAff > 0 ? true : false;
            if (conn.State > 0)
            {
                conn.Close();
            }
            comm.Dispose();
        }
        catch (Exception exp)
        {
            if (need_log)
            {
                ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
                clslog.Log("Execute Non Query in SQL", exp.Message + "(query:" + query + ")", true, exp);
            }
            ret_msg = exp.ToString();
        }
        return(res);
    }
Exemplo n.º 6
0
    public string ExecuteScalar(string query, string connectionstring)
    {
        string        res  = "";
        SqlConnection conn = getConnection(connectionstring);

        try
        {
            if (conn.State == 0)
            {
                conn.Open();
            }
            SqlCommand comm = new SqlCommand(query, conn);
            object     obj  = comm.ExecuteScalar();
            if (conn.State > 0)
            {
                conn.Close();
            }
            conn.Close();
            comm.Dispose();
            if (obj == null)
            {
                return("");
            }
            res = obj.ToString();
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Execute Scalar Query in SQL", exp.Message + "(query:" + query + ")", true, exp);
        }
        return(res);
    }
Exemplo n.º 7
0
 public void KillCommand(SqlCommand comm)
 {
     try
     {
         if (comm.Connection.State > 0)
         {
             comm.Connection.Close();
         }
         comm.Dispose();
     }
     catch (Exception exp)
     {
         ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
         clslog.Log("KillCommand", exp.Message, true, exp);
     }
 }
Exemplo n.º 8
0
    public SqlCommand GetCommandWithTransaction(string connectionstring)
    {
        SqlCommand comm = new SqlCommand();

        try
        {
            comm.Connection = getConnection(connectionstring);
            SqlTransaction trans = comm.Connection.BeginTransaction();
            comm.Transaction = trans;
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Get Command with Transaction in SQL", exp.Message + ", Connection string: " + connectionstring, true, exp);
        }
        return(comm);
    }
Exemplo n.º 9
0
    public SqlConnection getConnection(string connectionString)
    {
        SqlConnection Sq1 = new SqlConnection();

        try
        {
            Sq1 = new SqlConnection(getConnectionString(connectionString));
            if (Sq1.State == 0)
            {
                Sq1.Open();
            }
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Establish Connection with SQL Server", exp.Message, true, exp);
        }
        return(Sq1);
    }
Exemplo n.º 10
0
    public bool ExecuteNonQuery(string query, SqlParameter[] SqlPar, CommandType comm_type, SqlCommand comm)
    {
        bool res = false;

        try
        {
            if (comm == null)
            {
                comm = new SqlCommand(query, getConnection(""));
            }
            comm.CommandText = query;
            comm.Parameters.Clear();
            comm.Parameters.AddRange(SqlPar);
            comm.CommandType = comm_type;
            return(ExecuteNonQuery(query, comm));
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Execute Non Query in SQL with Par", exp.Message + "(query:" + query + ")", true, exp);
        }
        return(res);
    }
Exemplo n.º 11
0
    public string getConnectionString(string connectionString)
    {
        string res = "";

        try
        {
            if (connectionString.Trim().Length == 0)
            {
                clsCommon common = new clsCommon();
                res = common.GetConfig("sqlconn");
            }
            else
            {
                return(connectionString);
            }
        }
        catch (Exception exp)
        {
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("connectionstring", exp.Message, true, exp);
        }
        return(res);
    }
Exemplo n.º 12
0
 public string ExecuteScalar(string query, string connectionstring, SqlCommand comm)
 {
     try
     {
         bool is_new_command = false;
         if (comm == null)
         {
             is_new_command = true;
             comm           = new SqlCommand(query, getConnection(connectionstring));
         }
         else
         {
             comm.CommandText = query;
         }
         object obj = comm.ExecuteScalar();
         if (is_new_command)
         {
             if (comm.Connection.State > 0)
             {
                 comm.Connection.Close();
             }
             comm.Connection.Close();
             comm.Dispose();
         }
         if (obj == null)
         {
             return("");
         }
         return(obj.ToString());
     }
     catch (Exception exp)
     {
         ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
         clslog.Log("ExecuteScalar", exp.Message + "(query:" + query + ")", true, exp);
         return("");
     }
 }
Exemplo n.º 13
0
    public bool ExecuteNonQuery(string query, string connectionstring, SqlCommand comm, out string ret_msg)
    {
        bool res            = false;
        bool is_new_command = false;

        ret_msg = "";
        try
        {
            if (comm == null)
            {
                comm           = new SqlCommand(query, getConnection(connectionstring));
                is_new_command = true;
            }
            else
            {
                comm.CommandText = query;
            }
            int rAff = comm.ExecuteNonQuery();
            res = rAff > 0 ? true : false;
            if (is_new_command)
            {
                if (comm.Connection.State > 0)
                {
                    comm.Connection.Close();
                }
                comm.Dispose();
            }
        }
        catch (Exception exp)
        {
            ret_msg = exp.Message;
            ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
            clslog.Log("Execute Non Query in SQL with Command Par", exp.Message + "(query:" + query + ")", true, exp);
        }
        return(res);
    }
Exemplo n.º 14
0
 public void Log(string action, string logDesc, bool isError, Exception expp)
 {
     ecomserv.Common.clsLog clslog = new ecomserv.Common.clsLog();
     clslog.Log(action, logDesc, isError, expp);
 }