Ejemplo n.º 1
0
        // TODO https://docs.microsoft.com/en-us/dotnet/api/microsoft.data.sqlclient.sqlconnection.begintransaction?view=sqlclient-dotnet-core-1.1
        private async Task <ReturnClass> ExecSqlAsyncMethods(string strQuery, SP_Parameters p, bool isProc)
        {
            ReturnClass outcome  = new ReturnClass(true);
            int         result   = 0;
            string      errormsg = "";
            bool        isError  = false;

            using (var conn = new SqlConnection(connectionstring)) {
                await conn.OpenAsync();

                using (var tran = conn.BeginTransaction()) {
                    using (var command = new SqlCommand(strQuery, conn, tran)) {
                        command.CommandTimeout = commandtimeout;
                        if (isProc)
                        {
                            command.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            command.CommandType = CommandType.Text;
                        }

                        if (p != null)
                        {
                            foreach (SqlParameter objparam1 in p)
                            {
                                command.Parameters.Add(objparam1);
                            }
                        }
                        try {
                            result = await command.ExecuteNonQueryAsync();
                        } catch (Exception ex) {
                            errormsg = ex.ToString();
                            isError  = true;
                            tran.Rollback();
                            throw;
                        }
                        tran.Commit();
                    }
                }
            }
            if (isError)
            {
                outcome.Success     = false;
                outcome.Message     = "A query Failed. Please see logs for exact error";
                outcome.Techmessage = "GetDataTable error. Query is[" + strQuery + "] Error:[" + errormsg + "]";
            }
            outcome.Intvar = result;

            return(outcome);
        }
Ejemplo n.º 2
0
        private ReturnClass ExecSqlProcVoidMethods(string strQuery, bool isProc, SP_Parameters p)
        {
            ReturnClass outcome = new ReturnClass(true);
            int         results = 0;

            try {
                using (var conn = new SqlConnection(connectionstring)) {
                    conn.Open();
                    using (var command = new SqlCommand(strQuery, conn)) {
                        command.CommandTimeout = commandtimeout;

                        if (isProc)
                        {
                            command.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            command.CommandType = CommandType.Text;
                        }

                        if (p != null)
                        {
                            foreach (SqlParameter objparam1 in p)
                            {
                                command.Parameters.Add(objparam1);
                            }
                        }

                        results = command.ExecuteNonQuery();
                    }
                }
            } catch (Exception ex) {
                outcome.SetFailureMessage("An update query Failed. Please see logs for exact error", "ExecSql error. Query is[" + strQuery + "] Error:[" + ex.Message + "]");
                results = -1;
            }
            outcome.Intvar = results;
            return(outcome);
        }
Ejemplo n.º 3
0
        private ReturnClass SyncScalarMethods(string strQuery, SP_Parameters p, bool isProc, ScalarType sctype)
        {
            ReturnClass outcome = new ReturnClass(true);

            try {
                using (var conn = new SqlConnection(connectionstring)) {
                    conn.Open();
                    using (var command = new SqlCommand(strQuery, conn)) {
                        command.CommandTimeout = commandtimeout;

                        if (isProc)
                        {
                            command.CommandType = CommandType.StoredProcedure;
                        }
                        else
                        {
                            command.CommandType = CommandType.Text;
                        }

                        if (p != null)
                        {
                            foreach (SqlParameter objparam1 in p)
                            {
                                command.Parameters.Add(objparam1);
                            }
                        }

                        try {
                            using (var reader = command.ExecuteReader()) {
                                if (reader.Read())
                                {
                                    switch (sctype)
                                    {
                                    case ScalarType.String:
                                        outcome.Message = reader[0].ToString();
                                        break;

                                    case ScalarType.Int:
                                        outcome.Intvar = (int)reader[0];
                                        break;

                                    case ScalarType.Long:
                                        outcome.Longvar = (long)reader[0];
                                        break;

                                    case ScalarType.Double:
                                        outcome.Doublevar = (double)reader[0];
                                        break;
                                    }
                                }
                            }
                        } catch (Exception ex) {
                            outcome.SetFailureMessage("A query Failed. Please see logs for exact error", "SyncScalarMethods Error Query is[" + strQuery + "] Error:[" + ex.ToString() + "]");
                        }
                    }
                }
            } catch (SqlException ex) {
                outcome.SetFailureMessage("A query Failed. Please see logs for exact error", "SyncScalarMethods Error Query is[" + strQuery + "] Sql Error:[" + ex.ToString() + "]");
            } catch (Exception ex) {
                outcome.SetFailureMessage("A query Failed. Please see logs for exact error", "SyncScalarMethods Error Query is[" + strQuery + "] Error:[" + ex.ToString() + "]");
            }
            return(outcome);
        }
Ejemplo n.º 4
0
        private async Task <ReturnClass> GetScalarAsyncMethods(string strQuery, SP_Parameters p, bool isProc, ScalarType sctype)
        {
            ReturnClass outcome = new ReturnClass(true);

            string errormsg = "";
            bool   isError  = false;

            using (var conn = new SqlConnection(connectionstring)) {
                await conn.OpenAsync();

                using (var command = new SqlCommand(strQuery, conn)) {
                    command.CommandTimeout = commandtimeout;

                    if (isProc)
                    {
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    else
                    {
                        command.CommandType = CommandType.Text;
                    }

                    if (p != null)
                    {
                        foreach (SqlParameter objparam1 in p)
                        {
                            command.Parameters.Add(objparam1);
                        }
                    }

                    try {
                        using (var reader = await command.ExecuteReaderAsync()) {
                            if (await reader.ReadAsync())
                            {
                                switch (sctype)
                                {
                                case ScalarType.String:
                                    outcome.Message = reader[0].ToString();
                                    break;

                                case ScalarType.Int:
                                    outcome.Intvar = (int)reader[0];
                                    break;

                                case ScalarType.Long:
                                    outcome.Longvar = (long)reader[0];
                                    break;

                                case ScalarType.Double:
                                    outcome.Doublevar = (double)reader[0];
                                    break;
                                }
                            }
                        }
                    } catch (Exception ex) {
                        errormsg = ex.ToString();
                        isError  = true;
                    }
                }
            }

            if (isError)
            {
                outcome.Success     = false;
                outcome.Message     = "A query Failed. Please see logs for exact error";
                outcome.Techmessage = "GetDataTable error. Query is[" + strQuery + "] Error:[" + errormsg + "]";
            }

            return(outcome);
        }
Ejemplo n.º 5
0
        public async Task <ReturnClass> GetDoubleScalarProcParamsAsync(string strQuery, SP_Parameters p)
        {
            ReturnClass outcome = await GetScalarAsyncMethods(strQuery, p, true, ScalarType.Double);

            return(outcome);
        }
Ejemplo n.º 6
0
        public async Task <ReturnClass> GetDoubleScalarProcAsync(string strQuery)
        {
            ReturnClass outcome = await GetScalarAsyncMethods(strQuery, null, true, ScalarType.Double);

            return(outcome);
        }
Ejemplo n.º 7
0
        public ReturnClass UploadImageField(string filepath, string imagefieldparametername, string Sql)
        {
            ReturnClass   outcome   = new ReturnClass(true);
            SP_Parameters p         = new SP_Parameters();
            SqlCommand    cmd       = null;
            Stream        imgStream = null;
            FileInfo      file      = null;

            byte[] imgBinaryData = null;
            int    RowsAffected  = 0;
            int    filesize      = 0;
            int    n             = 0;

            if (!imagefieldparametername.StartsWith("@"))
            {
                imagefieldparametername = "@" + imagefieldparametername;
            }
            if (!File.Exists(filepath))
            {
                outcome.SetFailureMessage("The file does not exist or is not accessible.");
            }

            if (outcome.Success)
            {
                try {
                    file     = new FileInfo(filepath);
                    filesize = Convert.ToInt32(file.Length);
                } catch (Exception ex) {
                    outcome.Success = false;
                    outcome.Message = ex.Message;
                }
            }

            if (outcome.Success)
            {
                try {
                    imgStream     = File.OpenRead(filepath);
                    imgBinaryData = new byte[filesize];
                    n             = imgStream.Read(imgBinaryData, 0, filesize);
                } catch (Exception ex) {
                    outcome.Success = false;
                    outcome.Message = ex.Message;
                }
            }

            if (outcome.Success)
            {
                try {
                    using (var conn = new SqlConnection(connectionstring)) {
                        conn.Open();
                        cmd = new SqlCommand(Sql, conn);
                        if (commandtimeout > 0)
                        {
                            cmd.CommandTimeout = commandtimeout;
                        }
                        p.Add(imagefieldparametername, SqlDbType.Image, filesize, ParameterDirection.Input, imgBinaryData);
                        foreach (SqlParameter objparam1 in p)
                        {
                            cmd.Parameters.Add(objparam1);
                        }
                        RowsAffected = cmd.ExecuteNonQuery();
                    }
                } catch (Exception ex) {
                    outcome.Success = false;
                    outcome.Message = ex.Message;
                }
            }

            try {
                imgStream.Close();
            } catch (Exception ex) {
            }
            return(outcome);
        }
Ejemplo n.º 8
0
        public async Task <ReturnClass> GetLongScalarParamsAsync(string strQuery, SP_Parameters p)
        {
            ReturnClass outcome = await GetScalarAsyncMethods(strQuery, p, false, ScalarType.Long);

            return(outcome);
        }
Ejemplo n.º 9
0
        public async Task <ReturnClass> GetLongScalarAsync(string strQuery)
        {
            ReturnClass outcome = await GetScalarAsyncMethods(strQuery, null, false, ScalarType.Long);

            return(outcome);
        }
Ejemplo n.º 10
0
        public async Task <ReturnClass> ExecProcVoidParamsAsync(string procname, SP_Parameters p)
        {
            ReturnClass outcome = await ExecSqlAsyncMethods(procname, p, true);

            return(outcome);
        }
Ejemplo n.º 11
0
        public async Task <ReturnClass> ExecProcVoidAsync(string procname)
        {
            ReturnClass outcome = await ExecSqlAsyncMethods(procname, null, true);

            return(outcome);
        }
Ejemplo n.º 12
0
        public async Task <ReturnClass> ExecSqlParamsAsync(string strQuery, SP_Parameters p)
        {
            ReturnClass outcome = await ExecSqlAsyncMethods(strQuery, p, false);

            return(outcome);
        }
Ejemplo n.º 13
0
        public async Task <ReturnClass> ExecSqlAsync(string strQuery)
        {
            ReturnClass outcome = await ExecSqlAsyncMethods(strQuery, null, false);

            return(outcome);
        }