public int InsertLogin(string Loginuser, string Password)
{
TdConnection con = new TdConnection(strConnString);
int id = 0;
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddLogin = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Login Values('" + Loginuser + "','"
+ Password + "')", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddLogin.ExecuteNonQuery();
id = 1;
return(id);
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(id);
}
public void UpdateDefect(string Comments, string LoginUser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp sysdate, String filenames, String filesize, Byte Attachments, int histid, String environment, String Severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand cmdUpdateDefect =
new Teradata.Client.Provider.TdCommand(@"Update coebatch.tbl_Defect set DefectName='" + DefectName + "' ,Status='" + Status + "',SCycle ='" + Cycle + "',Description='" + Description + "',Project='" + Project + "',Tester='" + Tester + "',TestcasesAssociated='" + TestcasesAssociated + "',Module='" + Module + "',AssignedTo='" + Assignedto + "',CloseDate='" + sysdate + "', Environment = '" + environment + "' , Severity = '" + Severity + "', Releases = '" + Projectname + "',Rootcause = '" + Rootcause + "' where DefectID= '" + DefectID + "'", con);
Teradata.Client.Provider.TdCommand cmdInsertComments =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Comments Values ('" + DefectID + "','" + Comments + "','" + sysdate + "', '" + LoginUser + "' )", con);
DateTime Datetimestamp = DateTime.Now;
Teradata.Client.Provider.TdCommand cmdUpdateDefectHistory =
new Teradata.Client.Provider.TdCommand(@"Insert into coebatch.tbl_Defect_History Values('" + DefectID + "','" + DefectName + "' ,'" + Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','" + sysdate + "','" + histid + "','" + LoginUser + "', '" + environment + "' , '" + Severity + "','" + Projectname + "','" + Rootcause + "') ", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdUpdateDefect.ExecuteNonQuery();
cmdInsertComments.ExecuteNonQuery();
cmdUpdateDefectHistory.ExecuteNonQuery();
}
catch (TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public void InsertDefect(string Loginuser, int DefectID, String DefectName, TdTimestamp Date, String Status, String Cycle, String Description, String Project, String Tester, String TestcasesAssociated, String Module, String Assignedto, TdTimestamp date1, String filenames, String filesize, Byte Attachments, String environment, String severity, String Projectname, String Rootcause)
{
TdConnection con = new TdConnection(strConnString);
// Date = CAST(Date AS FORMAT 'YYYY-MM-DD');
// TdTimestamp dt = new TdTimestamp(Date);
// string dts = dt.ToString("MM/dd/yyyy HH:mm:ss");
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddDefect = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect Values('" + DefectID + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + filenames + "','" + filesize + "',,'" + environment + "','" + severity + "','" + Projectname + "','" + Rootcause + "')", con);
int DefectIDD = (int)GetNextDefectID();
int histid = 1;
//DefectIDD = DefectIDD - 1;
Teradata.Client.Provider.TdCommand cmdAddDefectHistory = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Defect_History Values('"
+ DefectIDD + "','"
+ DefectName + "','"
+ Date + "','" + Status + "','" + Cycle + "','" + Description + "','" + Project + "','" + Tester + "','" + TestcasesAssociated + "','" + Module + "','" + Assignedto + "','"
+ date1 + "','" + histid + "','" + Loginuser + "','" + environment + "','" + severity + "', '" + Projectname + "','" + Rootcause + "')", con);
int intLengt = 0;
byte[] objDat;
objDat = new byte[intLengt];
string DataOb = Convert.ToString(objDat);
// cmdAddDefect.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
// cmdAddDefectHistory.Parameters.Add(new Teradata.Client.Provider.TdParameter("@Attachments", (object)objDat));
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddDefect.ExecuteNonQuery();
cmdAddDefectHistory.ExecuteNonQuery();
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public int GetHistoryid(int DefectID)
{
//Define the Command Object
int ID = 0;
string defect1 = "";
//IDDefect = Convert.ToInt32(DefectID);
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand AdvancedDefectSearch =
new Teradata.Client.Provider.TdCommand("Select max(defecthistid)+ 1 as test from coebatch.tbl_Defect_history where DefectID = " + DefectID + ";", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
//AdvancedDefectSearch.ExecuteNonQuery();
TdDataReader r = AdvancedDefectSearch.ExecuteReader();
if (r.Read())
{
defect1 = r["test"].ToString();
}
r.Close();
if (defect1 == "")
{
con.Close();
return(ID);
}
else
{
ID = Convert.ToInt32(defect1);
return(ID);
}
// nextDefectId = (int)cmdNewDefect.ExecuteScalar();
}
catch (TdException ex)
{
return(ID);
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
}
public int GetDefectcount()
{
string query = "";
int ID = 0;
TdCommand cmd = new TdCommand(query);
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand AdvancedDefectSearch =
new Teradata.Client.Provider.TdCommand("select count(*) as test from coebatch.tbl_defect ", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
//AdvancedDefectSearch.ExecuteNonQuery();
TdDataReader r = AdvancedDefectSearch.ExecuteReader();
if (r.Read())
{
query = r["test"].ToString();
}
r.Close();
if (query == "")
{
con.Close();
return(ID);
}
else
{
ID = Convert.ToInt32(query);
}
// nextDefectId = (int)cmdNewDefect.ExecuteScalar();
}
catch (TdException ex)
{
return(ID);
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(ID);
}
public int GetNextDefectID()
{
TdConnection con = new TdConnection(strConnString);
Teradata.Client.Provider.TdCommand cmdNewDefect = new Teradata.Client.Provider.TdCommand("SELECT MAX(DefectId)+1 as test FROM coebatch.tbl_Defect", con);
int nextDefectId = 0;
string defectid = "";
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
//nextDefectId = (int)cmdNewDefect.ExecuteScalar();
TdDataReader r = cmdNewDefect.ExecuteReader();
if (r.Read())
{
defectid = r["test"].ToString();
}
r.Close();
// nextDefectId = cmdNewDefect.ExecuteScalar();
if (defectid == "")
{
nextDefectId = 1;
}
else
{
nextDefectId = Convert.ToInt32(defectid);
}
}
catch (Teradata.Client.Provider.TdException ex)
{
//Throw the exception to calling environment
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(nextDefectId);
}
public int AddAttachments(string text, string filename, int Defectid1, int id1)
{
TdConnection con = new TdConnection(strConnString);
int id = 0;
//Define the Command Object
Teradata.Client.Provider.TdCommand cmdAddAttach = new Teradata.Client.Provider.TdCommand(@"INSERT INTO coebatch.tbl_Attachments Values('" + text + "','"
+ filename + "',"
+ Defectid1 + ","
+ id1 + " )", con);
try
{
//Open the Connection to connect to the database
con.Open();
//Execute the Query
cmdAddAttach.ExecuteNonQuery();
id = 1;
return(id);
}
catch (Teradata.Client.Provider.TdException ex)
{
throw ex;
}
finally
{
//Close the Connection from the Database
con.Close();
}
return(id);
}
private void button1_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
Teradata.Client.Provider.TdConnection mainConn = new Teradata.Client.Provider.TdConnection();
TdConnectionStringBuilder stringBuilder = new TdConnectionStringBuilder();
stringBuilder.CommandTimeout = 300;
stringBuilder.ConnectionTimeout = 100;
stringBuilder.DataSource = host;
stringBuilder.UserId = uid;
stringBuilder.Password = pwd;
mainConn.ConnectionString = stringBuilder.ConnectionString;
this.Text = host;
mainConn.Open();
StreamReader gperead = new StreamReader(@txtFile.Text);
string szLine = "";
string[] szFields;
string[] szHeader;
szLine = gperead.ReadLine();
szLine = szLine.Replace("\"", "");
szHeader = szLine.Split(SEPARATOR);
Teradata.Client.Provider.TdCommand gpeCmd = new Teradata.Client.Provider.TdCommand("SELECT * FROM " + cboDatabaseList.Text + "." + cboTables.Text, mainConn);
gpeCmd.CommandTimeout = 10000;
int counter = 0;
Teradata.Client.Provider.TdDataAdapter gpeAdapter = new Teradata.Client.Provider.TdDataAdapter(gpeCmd);
gpeAdapter.UpdateBatchSize = 100000;
gpeAdapter.KeepCommandBatchSequence = false;
Teradata.Client.Provider.TdCommandBuilder cb = new Teradata.Client.Provider.TdCommandBuilder(gpeAdapter);
DataTable dt = new DataTable();
gpeAdapter.Fill(dt);
while ((szLine = gperead.ReadLine()) != null)
{
szLine = szLine.Replace("\"", "");
szFields = szLine.Split(SEPARATOR);
DataRow dr = dt.NewRow();
if (szFields.GetUpperBound(0) == szHeader.GetUpperBound(0))
{
for (int i = 0; i < szHeader.GetLength(0); i++)
{
if (szFields[i] == "?")
{
dr[szHeader[i]] = DBNull.Value;
}
else if (dr.Table.Columns[szHeader[i]].DataType == typeof(DateTime))
{
try {
dr[szHeader[i]] = Convert.ToDateTime(szFields[i].Trim('\"'));
}
catch (Exception ex) {
dr[szHeader[i]] = DBNull.Value;
}
}
else if (dr.Table.Columns[szHeader[i]].DataType == typeof(double))
{
dr[szHeader[i]] = Convert.ToDecimal(szFields[i].Trim('\"').Replace('.', ','));
}
else
{
try {
dr[szHeader[i]] = szFields[i].Trim('\"');
}
catch (Exception ex) {
dr[szHeader[i]] = DBNull.Value;
}
}
}
dt.Rows.Add(dr);
}
if ((counter++ % 100000) == 0)
{
try {
gpeAdapter.Update(dt);
}
catch (Exception ex) {
MessageBox.Show(this, ex.Message);
}
textBox1.Text = (counter - 1).ToString();
}
Application.DoEvents();
}
gpeAdapter.Update(dt);
textBox1.Text = counter.ToString();
dt.Dispose();
gpeCmd.Dispose();
mainConn.Close();
mainConn.Dispose();
gperead.Close();
sw.Stop();
MessageBox.Show("Done!\n" + sw.Elapsed);
}