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);
}
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);
}
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;
}
}
}
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);
}
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;
}
}
}
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();
}
}
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
{
}
}
// 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;
}
}
}
//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();
}