Ejemplo n.º 1
0
        public static DataSet fnReturnDataset(string strQuery, Constring objEnum)
        {
            SqlDataAdapter adTemp = null;
            DataSet        dsTemp = new DataSet();

            SqlConnection m_objConSBS = Connection(objEnum);

            try
            {
                adTemp = new SqlDataAdapter(strQuery, m_objConSBS);
                adTemp.Fill(dsTemp);
            }
            catch (Exception ex)
            {
                Util.WriteToFile("fnReturnDataset Error:" + ex.ToString() + "\r\n" + ex.StackTrace);
            }
            finally
            {
                if (m_objConSBS.State != ConnectionState.Closed)
                {
                    m_objConSBS.Close();
                }

                adTemp.Dispose();
                m_objConSBS.Dispose();
            }
            return(dsTemp);
        }
Ejemplo n.º 2
0
        public static int fn_ExecuteNonQuery(string strQuery, Constring objEnum)
        {
            SqlCommand    objSQLCmd;
            int           suc         = 0;
            SqlConnection m_objConSBS = Connection(objEnum);

            try
            {
                if (m_objConSBS.State != ConnectionState.Open)
                {
                    m_objConSBS.Open();
                }
                objSQLCmd = new SqlCommand(strQuery, m_objConSBS);
                suc       = objSQLCmd.ExecuteNonQuery();
                m_objConSBS.Close();

                return(suc);
            }
            catch (Exception ex)
            {
                Util.WriteToFile("fn_ExecuteNonQuery Error:" + ex.ToString() + "\r\n" + ex.StackTrace);
            }
            finally
            {
                m_objConSBS.Dispose();
            }
            return(suc);
        }
Ejemplo n.º 3
0
        public static async Task Delete(long refid)
        {
            using (SqlConnection con = new SqlConnection(Constring.ConnectionString()))
            {
                await con.OpenAsync();

                SqlCommand     command     = con.CreateCommand();
                SqlTransaction transaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                command.Connection  = con;
                command.Transaction = transaction;

                try
                {
                    string updateMaiquery = String.Format("update Main set IsActive = {0} where RefId = {1} and IsActive = {2}", 0, refid, 1);
                    command.CommandText = updateMaiquery;
                    await command.ExecuteNonQueryAsync();

                    string insertDelRef = String.Format("insert into DeletedRefId(deletedRefId) values ({0})", refid);
                    command.CommandText = insertDelRef;
                    await command.ExecuteNonQueryAsync();

                    command.Dispose();

                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
Ejemplo n.º 4
0
        public static SqlConnection Connection(Constring constring)
        {
            SqlConnection con = null;

            try
            {
                switch (constring)
                {
                case Constring.SBSSqlServer:
                {
                    con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["conSBSServerDataBase"].ToString());
                    return(con);
                }

                case Constring.CGSqlServer:
                {
                    // defind latter whenever requird
                    return(con);
                }

                default:
                {
                    throw new Exception();
                }
                }
            }
            catch (Exception ex)
            {
                Util.WriteToFile("Conection error " + ex.Message + " stacktrac  " + ex.StackTrace);
            }
            return(con);
        }
Ejemplo n.º 5
0
        public static async Task <string> Get(long refid)
        {
            using (SqlConnection con = new SqlConnection(Constring.ConnectionString()))
            {
                await con.OpenAsync();

                SqlCommand command = con.CreateCommand();
                command.Connection = con;

                try
                {
                    string query = String.Format("select Val from Main where RefId = {0} and IsActive = 1", refid);
                    command.CommandText = query;
                    object queryObj = await command.ExecuteScalarAsync();

                    if (queryObj != null)
                    {
                        return(queryObj.ToString());
                    }

                    return(String.Empty);
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }
Ejemplo n.º 6
0
        private static DataSet fnReturnDataset(string strQuery, Constring objEnum)
        {
            SqlDataAdapter adTemp = null;
            DataSet        dsTemp = new DataSet();

            try
            {
                switch (objEnum)
                {
                case Constring.CGSqlServer:
                {
                    adTemp = new SqlDataAdapter(strQuery, m_objConCG);
                    adTemp.Fill(dsTemp);
                    break;
                }

                case Constring.SBSSqlServer:
                {
                    adTemp = new SqlDataAdapter(strQuery, m_objConSBS);
                    adTemp.Fill(dsTemp);
                    break;
                }
                }
                return(dsTemp);
            }
            catch (Exception ex)
            {
                clsSMS.WriteToFile("fnReturnDataset Error:" + ex.ToString());
                throw;
            }
            finally
            {
                switch (objEnum)
                {
                case Constring.CGSqlServer:
                {
                    if (m_objConCG.State != ConnectionState.Closed)
                    {
                        m_objConCG.Close();
                    }
                    break;
                }

                case Constring.SBSSqlServer:
                {
                    if (m_objConSBS.State != ConnectionState.Closed)
                    {
                        m_objConSBS.Close();
                    }
                    break;
                }
                }
                adTemp.Dispose();
            }
        }
Ejemplo n.º 7
0
        private static int fn_ExecuteNonQuery(string strQuery, Constring objEnum)
        {
            SqlCommand objSQLCmd;
            int        suc = 0;

            try
            {
                switch (objEnum)
                {
                case Constring.CGSqlServer:
                {
                    if (m_objConCG.State != ConnectionState.Open)
                    {
                        m_objConCG.Open();
                    }
                    objSQLCmd = new SqlCommand(strQuery, m_objConCG);
                    suc       = objSQLCmd.ExecuteNonQuery();
                    break;
                }

                case Constring.SBSSqlServer:
                {
                    if (m_objConSBS.State != ConnectionState.Open)
                    {
                        m_objConSBS.Open();
                    }
                    objSQLCmd = new SqlCommand(strQuery, m_objConSBS);
                    suc       = objSQLCmd.ExecuteNonQuery();
                    break;
                }
                }
                return(suc);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
            }
        }
Ejemplo n.º 8
0
        // transaction important here since it concerns multiple table manipulations
        public static async Task <long> Add(string value)
        {
            long result = 1;

            using (SqlConnection con = new SqlConnection(Constring.ConnectionString()))
            {
                await con.OpenAsync();

                SqlCommand     command     = con.CreateCommand();
                SqlTransaction transaction = con.BeginTransaction(IsolationLevel.ReadCommitted);
                command.Connection  = con;
                command.Transaction = transaction;

                try
                {
                    string getDelIdquery = @"select top 1 deletedRefId from DeletedRefId";
                    command.CommandText = getDelIdquery;
                    object delIdObj = await command.ExecuteScalarAsync();

                    if (delIdObj != null)
                    {
                        result = long.Parse(delIdObj.ToString());
                        string delRefquery = String.Format("delete from DeletedRefId where deletedRefId = {0}", result);
                        command.CommandText = delRefquery;
                        await command.ExecuteNonQueryAsync();
                    }
                    else
                    {
                        string getcounter = @"select top 1 counter from Counter";
                        command.CommandText = getcounter;
                        object counterObj = await command.ExecuteScalarAsync();

                        if (counterObj != null)
                        {
                            result = long.Parse(counterObj.ToString());
                            string updatecount = String.Format(@"update Counter set counter = {0}", result + 1);
                            command.CommandText = updatecount;
                            await command.ExecuteNonQueryAsync();
                        }
                        else
                        {
                            string insertcount = String.Format(@"insert into Counter(counter) values ({0})", result + 1);
                            command.CommandText = insertcount;
                            await command.ExecuteNonQueryAsync();
                        }
                    }
                    string insertMain = "insert into Main(RefId,Val) values (" + result + ",'" + value + "')";
                    command.CommandText = insertMain;
                    await command.ExecuteNonQueryAsync();

                    command.Dispose();

                    transaction.Commit();

                    return(result);
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
Ejemplo n.º 9
0
        //SqlDataReader dataReader;

        public void ExecuteScripts(string selectedOption, DataGridView dgvListProcess, ArrayList checkedDB, Label label9,
                                   BackgroundWorker bgwScript, Label label10, Server cons, RichTextBox rcProcess, Constring connects, ComboBox cboAuthentication,
                                   string selectedServer, TextBox txtUsername, TextBox txtPassword, CheckBox grantCheckBox, string constring)
        {
            BusinessFacade facade = new BusinessFacade();

            string erro = string.Empty;

            //if (flag != filePaths.Length)
            //{
            //    //ubah lstdrive ke dgvListProcess
            //    filePaths = new string[dgvListProcess.RowCount];
            //    //filePaths = new string[lstDrive.Items.Count];

            //    for (int j = 0; j < dgvListProcess.RowCount; j++)
            //    {
            //        //filePaths[j] = lstDrive.Items[j].ToString();
            //        filePaths[j] = dgvListProcess.Rows[j].ToString();
            //    }
            //}
            int point;

            switch (selectedOption)
            {
            case "all":
                startPoint = 0;
                break;

            case "one":
                point      = dgvListProcess.CurrentRow.Index;
                startPoint = point;
                endPoint   = point;
                break;

            case "from":
                point      = dgvListProcess.CurrentRow.Index;
                startPoint = point;
                break;
            }

            //else if (execOptions.SelectedIndex == null)
            //{
            //    MessageBox.Show("Please select options below first.");
            //    return;
            //}

            ////get checked dgv
            //if (dgvListProcess.SelectedRows.Count == 0)
            //{
            //    startPoint = 0;
            //}
            //else
            //{
            //    int point = dgvListProcess.CurrentRow.Index;
            //    startPoint = point;
            //}

            //initiate connection
            connection = new SqlConnection(constring);
            connection.Open();

            int i;

            for (i = startPoint; i < dgvListProcess.Rows.Count - 1; i++)
            {
                //if (i >= filePaths.Length)
                //{
                //    break;
                //}
                //Constring connects;
                foreach (string DB in checkedDB)
                {
                    label9.Invoke((MethodInvoker) delegate
                    {
                        label9.Text = DB;
                    });
                    //connect to db
                    cboAuthentication.Invoke((MethodInvoker) delegate
                    {
                        if (cboAuthentication.Items[cboAuthentication.SelectedIndex].ToString().Contains("Windows"))
                        {
                            //connects = new Constring(this.ddlServer.Text, DB);
                            connects = new Constring(selectedServer, DB);
                        }
                        else
                        {
                            //connects = new Constring(this.txtUsername.Text, this.txtPassword.Text, this.ddlServer.Text, DB);
                            connects = new Constring(txtUsername.Text, txtPassword.Text, selectedServer, DB);
                        }

                        cons = connects.Connect();
                    });

                    //facade.InsertAppLog(filePaths[i], DB);
                    facade.InsertAppLog(dgvListProcess.Rows[i].Cells[1].Value.ToString(), DB);
                    //exec script
                    this.GenerateStoredProc(dgvListProcess.Rows[i].Cells[1].Value.ToString(), ref erro, label10, cons, rcProcess, DB, selectedServer);
                    if (grantCheckBox.Checked == false)
                    {
                        //if execution fails
                        if (!string.IsNullOrEmpty(erro))
                        {
                            facade.InsertAppLog(dgvListProcess.Rows[i].Cells[1].Value.ToString(), DB, erro, grantCheckBox);
                            bgwScript.ReportProgress(i, "Error");
                            MessageBox.Show(erro);
                            return;
                        }
                    }
                    else
                    {
                        //if execution fails
                        if (!string.IsNullOrEmpty(erro))
                        {
                            facade.InsertAppLog(dgvListProcess.Rows[i].Cells[1].Value.ToString(), DB, erro, grantCheckBox);
                            bgwScript.ReportProgress(i, "Error");
                            MessageBox.Show(erro);
                            //ga berhenti jika ada error
                        }
                    }


                    //this.lstDrive.Items.RemoveAt(this.lstDrive.FindString(filePaths[i]));
                    //bgwScript.ReportProgress(i, "Success");
                    //this.dgvListProcess.Rows[i].Cells[0].Value = "Success";
                }
                bgwScript.ReportProgress(i, "Success");
                //this.flag--;
                if (i == endPoint)
                {
                    endPoint = 999999;
                    //MessageBox.Show("scripts on DB " + label9.Text + " Successfully execute");
                    return;
                }
            }
            //close connection
            connection.Close();
        }