//luodaan uusi bisnes
public BLbusiness(string name, string katuosoite, string postinro, string kaupunki, OdbcConnection conn)
{
//business address
this.address = new BLaddress(conn, katuosoite, postinro, kaupunki);
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO business (BusinessName, Address_AddressId) VALUES (?, ?)";
insertcmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name;
insertcmd.Parameters.Add("@Address_AddressId", OdbcType.Int).Value = this.address.id;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT BusinessId, BusinessName FROM business WHERE BusinessName = ?";
cmd.Parameters.Add("@BusinessName", OdbcType.VarChar, 100).Value = name;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLfeedback(int customerid, int servicepackageid, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO feedback (Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId) VALUES (?,?,?)";
insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid;
this.timestamp = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
insertcmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp;
insertcmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT FeedbackId, Customer_CustomerId, Timestamp, ServicePackage_ServicePackageId FROM feedback WHERE Customer_CustomerId = ? AND Timestamp = ? AND ServicePackage_ServicePackageId = ?";
cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = customerid;
cmd.Parameters.Add("@Timestamp", OdbcType.VarChar, 20).Value = this.timestamp;
cmd.Parameters.Add("@Customer_CustomerId", OdbcType.Int).Value = servicepackageid;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
int id2;
int.TryParse(rows.GetString(1), out id2);
this.customerid = id2;
this.timestamp = rows.GetString(2);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLcustomer(string name, string phonenumber, string email, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO customer (CustomerName, PhoneNro, Email) VALUES (?, ?, ?)";
insertcmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name;
insertcmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber;
insertcmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT CustomerId, CustomerName, PhoneNro, Email FROM customer WHERE CustomerName = ? AND PhoneNro = ? AND Email = ?";
cmd.Parameters.Add("@CustomerName", OdbcType.VarChar, 100).Value = name;
cmd.Parameters.Add("@PhoneNro", OdbcType.VarChar, 10).Value = phonenumber;
cmd.Parameters.Add("@Email", OdbcType.VarChar, 100).Value = email;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
this.phonenumber = rows.GetString(2);
this.email = rows.GetString(3);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLcomment(string name, int feedbackid, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO comment (Text, Feedback_FeedbackId) VALUES (?, ?)";
insertcmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name;
insertcmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT CommentId, Text, Feedback_FeedbackId FROM comment WHERE Text = ? AND Feedback_FeedbackId = ?";
cmd.Parameters.Add("@Text", OdbcType.VarChar, 1024).Value = name;
cmd.Parameters.Add("@Feedback_FeedbackId", OdbcType.Int).Value = feedbackid;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
int id2;
int.TryParse(rows.GetString(2), out id2);
this.feedbackid = id2;
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLaddress(OdbcConnection conn, string katuosoite, string postinro, string kaupunki)
{
//constructing attributes
this.city = new BLcity(conn, kaupunki);
this.zipcode = new BLzipcode(conn, postinro);
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO address (City_CityId, ZIPCode_ZIPCodeId, StreetAddress) VALUES (?, ?, ?)";
insertcmd.Parameters.Add("@City_CityId", OdbcType.Int).Value = this.city.id;
insertcmd.Parameters.Add("@ZIPCode_ZIPCodeId", OdbcType.Int).Value = this.zipcode.id;
insertcmd.Parameters.Add("@StreetAddress", OdbcType.VarChar, 50).Value = katuosoite;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT AddressId, StreetAddress FROM address WHERE StreetAddress = ?";
cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 50).Value = katuosoite;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
public BLservicepackage(string name, int service, OdbcConnection conn)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO servicepackage (ServicePackageName, Service_ServiceId) VALUES (?, ?)";
insertcmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name;
insertcmd.Parameters.Add("@Service_ServiceId", OdbcType.Int).Value = service;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ServicePackageId, ServicePackageName FROM servicepackage WHERE ServicePackageName = ?";
cmd.Parameters.Add("@ServicePackageName", OdbcType.VarChar, 100).Value = name;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
//constructor
public BLzipcode(OdbcConnection conn, string postinro)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO zipcode (ZIPCodeNumber) VALUES (?)";
insertcmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ZIPCodeId, ZIPCodeNumber FROM zipcode WHERE ZIPCodeNumber = ?";
cmd.Parameters.Add("@ZIPCodeNumber", OdbcType.VarChar, 5).Value = postinro;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select kaikissa osoite BL:ssä sama*/
}
//constructor
public BLcity(OdbcConnection conn, string kaupunki)
{
/*start copy pasta insert/select same in all BL files*/
try
{
//parameterized queries
conn.Open();
OdbcCommand insertcmd = conn.CreateCommand();
insertcmd.CommandText = "INSERT IGNORE INTO city (CityName) VALUES (?)";
insertcmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki;
insertcmd.ExecuteNonQuery();
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
try
{
//parameterized queries
conn.Open();
OdbcCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT CityId, CityName FROM city WHERE CityName = ?";
cmd.Parameters.Add("@CityName", OdbcType.VarChar, 20).Value = kaupunki;
OdbcDataReader rows = cmd.ExecuteReader();
rows.Read();
int id;
int.TryParse(rows.GetString(0), out id);
this.id = id;
this.name = rows.GetString(1);
}
catch (Exception ex)
{
this.id = -1;
this.name = ex.ToString();
conn.Close();
return;
}
finally
{
conn.Close();
}
/*end copy pasta insert/select same in all BL files*/
}
protected void Button1_Click(object sender, EventArgs e)
{
string ConnectionString = "";
// ConnectionString =
//@"DRIVER=MapR Drill ODBC Driver;
//AdvancedProperties={CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=local;ExcludedSchemas=sys,INFORMATION_SCHEMA;NumberOfPrefetchBuffers=5};
//Catalog=DRILL;
//Schema=;
//AuthenticationType=No Authentication;
//ConnectionType=Direct;
//Host=192.168.2.113;
//Port=31010;
//";
// ConnectionString = "DRIVER=MapR Drill ODBC Driver;Catalog=DRILL;Schema=hbase;ConnectionType=Direct;Host=192.168.2.113;Port=31010";
ConnectionString = "DRIVER =MapR Drill ODBC Driver; AdvancedProperties ={ CastAnyToVarchar = true; HandshakeTimeout = 5; QueryTimeout = 180; TimestampTZDisplayTimezone = local; ExcludedSchemas = sys,INFORMATION_SCHEMA; NumberOfPrefetchBuffers = 5}; Catalog = DRILL; Schema =; AuthenticationType = No Authentication; ConnectionType = ZooKeeper; ZKQuorum = dataNode04:2181,dataNode03: 2181,dataNode02: 2181,nameNode: 2181; ZKClusterID = drillbits1; ";
System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(ConnectionString);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM `hbase`.`tab` ";
System.Data.Odbc.OdbcDataAdapter ad = new System.Data.Odbc.OdbcDataAdapter(cmd);
DataSet myds = new DataSet();
ad.Fill(myds);
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="count"></param>
/// <param name="start"></param>
/// <returns></returns>
public IEnumerable <ContentObject> GetObjectsWithRange(string query, int count, int start, SortOrder order, string username)
{
List <ContentObject> objects = new List <ContentObject>();
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
using (var command = conn.CreateCommand())
{
command.CommandText = query;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("s", start);
command.Parameters.AddWithValue("length", count);
command.Parameters.AddWithValue("sortOrder", (order == SortOrder.Descending) ? "DESC" : "ASC");
command.Parameters.AddWithValue("uname", username);
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
var co = new ContentObject();
FillContentObjectLightLoad(co, resultSet);
LoadReviews(co, conn);
objects.Add(co);
}
}
}
if (_TotalObjects < 0)
{
setContentObjectCount(conn, username);
}
}
return(objects);
}
private OdbcCommand GetCommand(OdbcConnection connection)
{
OdbcCommand command = connection.CreateCommand();
command.Transaction = connection.LocalTransaction;
return(command);
}
public static DataTable ExecuteQuery(string dsnConnection, string query, params object[] queryParams)
{
try
{
using (var connection = new OdbcConnection(dsnConnection))
{
connection.Open();
var queryCommand = connection.CreateCommand();
queryCommand.CommandText = query;
queryCommand.CommandTimeout = 1;
var i = 0;
foreach (var queryParam in queryParams)
{
queryCommand.Parameters.Add("@p" + i, OdbcType.DateTime).Value = queryParam;
i++;
}
var dataSet = new DataSet();
var da = new OdbcDataAdapter(queryCommand);
da.Fill(dataSet);
return dataSet.Tables[0];
}
} catch(Exception ex)
{
Logging.LogError(1, "Error executing query {0} on connection {1} message is {2}", query, dsnConnection, ex.Message);
throw;
}
}
/* queries for all students (IDs, First names, last names)
* whose ID numbers contain the values provided by the student W# field
* in the current form. */
private void button1_Click(object sender, System.EventArgs e)
{
//Refresh the listview.
searchView.Items.Clear();
string SearchTerm = searchField.Text;
System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(dsnSource);
try
{
connection.Open();
System.Data.Odbc.OdbcCommand command = connection.CreateCommand();
command.CommandText = "SELECT ID, FIRST_NAME, LAST_NAME FROM G_PERSON WHERE ID like '%" + SearchTerm + "%'";
System.Data.Odbc.OdbcDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
String id = Reader["ID"].ToString();
String fname = Reader["FIRST_NAME"].ToString();
String lname = Reader["LAST_NAME"].ToString();
ListViewItem item = new ListViewItem(new[] { id, fname, lname });
searchView.Items.Add(item);
}
connection.Close();
} catch (Exception s) {
MessageBox.Show("Could not connect at this time. Please try again later.");
MessageBox.Show(s.ToString());
}
}
private static void SetupSqlServerOdbc(Cfg.Configuration cfg)
{
var connStr = cfg.Properties[Cfg.Environment.ConnectionString];
using (var conn = new OdbcConnection(connStr.Replace("Database=nhibernateOdbc", "Database=master")))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "drop database nhibernateOdbc";
try
{
cmd.ExecuteNonQuery();
}
catch(Exception e)
{
Console.WriteLine(e);
}
cmd.CommandText = "create database nhibernateOdbc";
cmd.ExecuteNonQuery();
}
}
}
public string[] GetMostPopularTags()
{
string[] result = new string[25];
//SELECT *, count(*) as ct FROM `3dr`.`associatedkeywords` inner join (select * from 3dr.keywords) as r on associatedkeywords.keywordid = r.id group by keyword order by ct desc limit 25
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "{SELECT *, count(*) as ct FROM `3dr`.`associatedkeywords` inner join (select * from 3dr.keywords) as r on associatedkeywords.keywordid = r.id group by keyword order by ct desc limit 25}";
using (var resultSet = command.ExecuteReader())
{
int i = 0;
while (resultSet.Read())
{
result[i] = resultSet["keyword"].ToString() + "." + resultSet["ct"].ToString();
i++;
}
}
}
return(result);
}
}
public string[] GetMostPopularDevelopers()
{
string[] result = new string[15];
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "{Select sponsorname, count(sponsorname) as ct from contentobjects where sponsorname != '' group by sponsorname union Select developername, count(developername) as ct from contentobjects where developername != '' group by developername union Select artistname, count(artistname) as ct from contentobjects where artistname != '' group by artistname order by ct desc limit 15}";
using (var resultSet = command.ExecuteReader())
{
int i = 0;
while (resultSet.Read())
{
result[i] = resultSet["sponsorname"].ToString();
result[i] += " (" + resultSet["ct"].ToString() + ")";
i++;
}
}
}
return(result);
}
}
private void uploadGCRC_Load(object sender, EventArgs e)
{
OdbcConnection MyConnection = new OdbcConnection("DSN=dinamap");
MyConnection.Open();
OdbcCommand DbCommand = MyConnection.CreateCommand();
DbCommand.CommandText = "SELECT * FROM gifts";
OdbcDataReader DbReader = DbCommand.ExecuteReader();
int fCount = DbReader.FieldCount;
label1.Text += ":";
for (int i = 0; i < fCount; i++)
{
String fName = DbReader.GetName(i);
label1.Text += fName + ":";
}
label1.Text += "\n";
while (DbReader.Read())
{
label1.Text += ":";
for (int i = 0; i < fCount; i++)
{
String col = DbReader.GetString(i);
label1.Text += col + ":";
}
label1.Text += "\n";
}
DbReader.Close();
DbCommand.Dispose();
MyConnection.Close();
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
/// <param name="filename"></param>
/// <returns></returns>
public bool RemoveSupportingFile(ContentObject co, string filename)
{
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
command.CommandText = "{CALL DeleteSupportingFile(?,?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("pid", co.PID);
command.Parameters.AddWithValue("filename", filename);
var result = command.ExecuteReader();
List <SupportingFile> remove = new List <SupportingFile>();
foreach (SupportingFile t in co.SupportingFiles)
{
if (t.Filename == filename)
{
remove.Add(t);
}
}
foreach (SupportingFile t in remove)
{
if (t.Filename == filename)
{
co.SupportingFiles.Remove(t);
}
}
}
}
return(true);
}
private void btnOpenFile_Click(object sender, RoutedEventArgs e)
{
try
{
OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection();
string fileName = "DUTY.DBF";
//OpenFileDialog openFileDialog = new OpenFileDialog();
//if (openFileDialog.ShowDialog() == true)
//{
//lblStatus.Content = "File conversion is in progress.. Please wait..!!";
obdcconn.ConnectionString = GetConnectionString(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
obdcconn.Open();
OdbcCommand oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "SELECT * FROM " + System.Configuration.ConfigurationSettings.AppSettings["InputPath"];
/*Load data to table*/
DataTable dt1 = new DataTable();
dt1.Load(oCmd.ExecuteReader());
string currentPath = Path.GetFullPath(System.Configuration.ConfigurationSettings.AppSettings["InputPath"]);
currentPath = Directory.GetParent(currentPath).FullName + "\\";
obdcconn.Close();
WriteDataToTemplate(currentPath, fileName, dt1);
//DataSetIntoDBF(fileName, currentPath, dt1);
//lblStatus.Content = "File conversion completed, file is in " + dir + "\\" + fileName;
//}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public IEnumerable <ContentObject> GetContentObjectsByField(string field, string value, string identity)
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "{CALL GetContentObjectsByField(?,?,?)}";
command.Parameters.AddWithValue("field", field);
command.Parameters.AddWithValue("val", value);
command.Parameters.AddWithValue("uname", identity);
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
var co = new ContentObject();
FillContentObjectLightLoad(co, resultSet);
objects.Add(co);
}
}
}
return(objects);
}
}
public ReviewDto Create()
{
//CREATE THE DTO
ReviewDto dto = new ReviewDto()
{
Id = Guid.NewGuid(),
Rating = int.Parse(Properties.Resources.DefaultReviewRating),
Comments = Properties.Resources.DefaultReviewComments,
CustomerId = Guid.Empty
};
//INSERT INTO THE DB
var connStr = Properties.Resources.ConnectionString;
using (OdbcConnection connection = new OdbcConnection(connStr))
{
connection.Open();
string queryStr = string.Format(@"INSERT INTO {0} " +
@"({1}, {2}, {3}, {4}) " +
@"VALUES('{5}','{6}', '{7}','{8}');",
Properties.Resources.ReviewTable,
Properties.Resources.ReviewIdColumn, Properties.Resources.ReviewRatingColumn,
Properties.Resources.ReviewCommentsColumn, Properties.Resources.ReviewCustomerIdColumn,
dto.Id, dto.Rating,
dto.Comments, dto.CustomerId);
var cmd = connection.CreateCommand();
cmd.CommandText = queryStr;
var numRowsAffected = cmd.ExecuteNonQuery();
}
//RETURN OUR INSERTED DTO
return dto;
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
public void InsertContentRevision(ContentObject co)
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
int id = 0;
using (var command = conn.CreateCommand())
{
command.CommandText = "{CALL InsertContentObject(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); }";
command.CommandType = System.Data.CommandType.StoredProcedure;
var properties = co.GetType().GetProperties();
foreach (var prop in properties)
{
if (prop.PropertyType == typeof(String) && prop.GetValue(co, null) == null)
{
prop.SetValue(co, String.Empty, null);
}
}
FillCommandFromContentObject(co, command);
id = int.Parse(command.ExecuteScalar().ToString());
}
SaveKeywords(conn, co, id);
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public IEnumerable <ContentObject> GetAllContentObjects()
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var command = conn.CreateCommand())
{
command.CommandText = "{CALL GetAllContentObjects()}";
command.CommandType = System.Data.CommandType.StoredProcedure;
using (var resultSet = command.ExecuteReader())
{
while (resultSet.Read())
{
var co = new ContentObject();
FillContentObjectFromResultSet(co, resultSet);
LoadReviews(co, conn);
co.Keywords = LoadKeywords(conn, co.PID);
objects.Add(co);
}
}
}
return(objects);
}
}
private void RunHrdSync()
{
using (ContactStore cs = new ContactStore(m_Server, m_Database, m_Username, m_Password))
{
Exception storedEx = null;
using (OdbcConnection localConn = new OdbcConnection(m_ConnString))
{
localConn.Open();
List<int> keysToMarkUploaded = new List<int>();
using (OdbcCommand localCmd = localConn.CreateCommand())
{
try
{
localCmd.CommandText = @"SELECT * FROM TABLE_HRD_CONTACTS_V01 WHERE COL_USER_DEFINED_1 IS NULL OR COL_USER_DEFINED_1 <> 'CamLogUploadDone'";
using (OdbcDataReader reader = localCmd.ExecuteReader())
{
while (reader.Read())
{
Contact c = new Contact();
c.SourceId = cs.SourceId;
c.Callsign = (string)reader["COL_CALL"];
c.StartTime = (DateTime)reader["COL_TIME_ON"];
c.EndTime = (DateTime)reader["COL_TIME_OFF"];
c.Band = BandHelper.Parse(reader["COL_BAND"] as string);
c.Frequency = GetFrequency(reader.GetInt32(reader.GetOrdinal("COL_FREQ")).ToString());
c.Mode = ModeHelper.Parse(reader["COL_MODE"] as string);
c.Operator = (reader["COL_OPERATOR"] as string) ?? m_DefaultOperator.Text;
c.Station = m_Station;
c.LocatorReceived = new Locator(0, 0);
c.ReportReceived = c.ReportSent = "599";
cs.SaveContact(c);
Invoke(new MethodInvoker(() => m_LastQsoLabel.Text = c.Callsign));
keysToMarkUploaded.Add(reader.GetInt32(reader.GetOrdinal("COL_PRIMARY_KEY")));
}
}
}
catch (Exception ex)
{
storedEx = ex;
}
}
using (OdbcCommand updateCommand = localConn.CreateCommand())
{
foreach (int key in keysToMarkUploaded)
{
updateCommand.CommandText =
"UPDATE TABLE_HRD_CONTACTS_V01 SET COL_USER_DEFINED_1='CamLogUploadDone' WHERE COL_PRIMARY_KEY=" + key;
updateCommand.ExecuteNonQuery();
}
}
if (storedEx != null)
throw storedEx;
}
}
}
private static OdbcCommand GetCommand(OdbcConnection connection)
{
OdbcCommand command = connection.CreateCommand();
// You need to make sure you pick up the transaction from the connection,
// or odd things can happen...
command.Transaction = connection.LocalTransaction;
return(command);
}
private static void ExecuteSqlUpdate(string dsnConnection, string query)
{
using (var connection = new OdbcConnection(dsnConnection))
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
}
public DremioOdbcConnection(string uid, string pwd, string host = "127.0.0.1", int port = 31010)
{
var driver = "{Dremio Connector}";
var cnn = new System.Data.Odbc.OdbcConnection($"Driver={driver};ConnectionType=Direct;HOST={host};PORT={port};AuthenticationType=Plain;UID={uid};PWD={pwd}");
cnn.Open();
var command = cnn.CreateCommand();
OdbcCommand = command;
}
static int Main ()
{
if (Environment.GetEnvironmentVariable ("MONO_TESTS_SQL") == null)
return 0;
OdbcConnection conn;
OdbcCommand cmd;
conn = new OdbcConnection (CreateConnectionString ());
conn.Open ();
try {
cmd = conn.CreateCommand ();
cmd.CommandText = drop_table;
cmd.ExecuteNonQuery ();
cmd = conn.CreateCommand ();
cmd.CommandText = create_table;
cmd.ExecuteNonQuery ();
cmd = conn.CreateCommand ();
cmd.CommandText = insert_data;
cmd.ExecuteNonQuery ();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from bug318854";
IDbDataAdapter da = new OdbcDataAdapter ();
DataSet ds = new DataSet ();
da.SelectCommand = cmd;
if (da.Fill (ds) != 2)
return 1;
return 0;
} finally {
cmd = conn.CreateCommand ();
cmd.CommandText = drop_table;
cmd.ExecuteNonQuery ();
conn.Dispose ();
}
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
public void DeleteContentObject(ContentObject co)
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
using (var command = conn.CreateCommand())
{
command.CommandText = "{CALL DeleteContentObject(?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("targetpid", System.Data.Odbc.OdbcType.VarChar, 45).Value = co.PID;
command.ExecuteNonQuery();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="id"></param>
public void IncrementDownloads(string id)
{
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
command.CommandText = "{CALL IncrementDownloads(?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("targetpid", id);
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// create command
/// </summary>
public OdbcCommand CreateCommand()
{
OdbcCommand sqlCommand = new OdbcCommand();
try
{
sqlCommand = dbConnection.CreateCommand();
}
catch (Exception ex)
{
throw ex;
}
return(sqlCommand);
}
public static ArrayList GetDonationGiftList(string username)
{
//get a list of item from redeemable_gift table
ArrayList redeemableGifts = new ArrayList();
IDbConnection connection = null;
IDbCommand command = null;
IDataReader reader = null;
try
{
connection = new OdbcConnection( ConnectionString );
connection.Open( );
command = connection.CreateCommand( );
command.CommandText = String.Format("SELECT redeemable_gift.id AS id, redeemable_gift.type_id AS type, gift_type.type_name AS name FROM redeemable_gift INNER JOIN gift_type ON redeemable_gift.type_id=gift_type.type_id WHERE redeemable_gift.account_name='{0}' ORDER BY redeemable_gift.id ASC", username);
reader = command.ExecuteReader();
while (reader.Read())
{
int giftId = System.Convert.ToInt32(reader["id"]);
int giftTypeId = System.Convert.ToInt32(reader["type"]);
string giftName = (string)reader["name"];
DonationGift gift = new DonationGift(giftId, giftTypeId, giftName);
redeemableGifts.Add(gift);
}
reader.Close();
}
catch( Exception e )
{
Console.WriteLine( "[Retrieve Donation Gift List] Error..." );
Console.WriteLine( e );
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
if (command != null && connection != null)
{
command.Dispose();
connection.Close();
}
}
return redeemableGifts;
}
/// <summary>
///
/// </summary>
/// <param name="rating"></param>
/// <param name="text"></param>
/// <param name="submitterEmail"></param>
/// <param name="contentObjectId"></param>
public void InsertReview(int rating, string text, string submitterEmail, string contentObjectId)
{
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
command.CommandText = "{CALL InsertReview(?,?,?,?);}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("newrating", rating);
command.Parameters.AddWithValue("newtext", text);
command.Parameters.AddWithValue("newsubmittedby", submitterEmail);
command.Parameters.AddWithValue("newcontentobjectid", contentObjectId);
var i = command.ExecuteNonQuery();
}
}
}
private void readFileSQL(FileInfo file)
{
Boolean failures = false;
StreamReader reader = new StreamReader(file.FullName);
FileInfo file2 = new FileInfo(file.FullName + "2");
StreamWriter writer = new StreamWriter(file2.FullName, true);
OdbcConnection MyConnection = new OdbcConnection("DSN=dinamapMySQL2");
try
{
MyConnection.Open();
}
catch
{
reader.Close();
writer.Close();
file2.Delete();
return;
}
OdbcCommand DbCommand = MyConnection.CreateCommand();
string SQLstatement;
while (!reader.EndOfStream)
{
SQLstatement = reader.ReadLine();
try
{
DbCommand.CommandText = SQLstatement;
DbCommand.ExecuteNonQuery();
}
catch
{
writer.WriteLine(SQLstatement);
failures = true;
}
}
reader.Close();
writer.Close();
file.Delete();
if (failures)
file2.MoveTo(file.FullName);
else
file2.Delete();
MessageBox.Show("Uploaded queued SQL and deleted file: " + file.FullName);
}
public CustomerDto Create()
{
//CREATE THE DTO
CustomerDto dto = new CustomerDto() { Id = Guid.NewGuid(), Name = Properties.Resources.DefaultCustomerName };
//INSERT INTO THE DB
var connStr = Properties.Resources.ConnectionString;
#region Odbc Connection Learning Process/NOTES
//connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=HireMe;User Id=;Password=;"; //can't find datasource
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=HireMe;Uid=User;Pwd=;"; //better, now login failed
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=HireMe;Uid=User;";//try without pwd=.....
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=HireMe;Trusted_Connection=yes;";//nope...try trusted connection.
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=HireMe;Trusted_Connection=yes;";//nope...try trusted connection.
//connStr = @"Data Source=C:\Users\User\Documents\Visual Studio 2010\Projects\HireMe\HireMe.WcfService\HireMe.DataAccess.OdbcProvider\HireMe.sdf";
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Data Source=.\SQLEXPRESS;AttachDbFilename=""C:\Users\User\Documents\Visual Studio 2010\Projects\HireMe\HireMe.WcfService\HireMe.DataAccess.OdbcProvider\DataSource\HireMeDatabase.mdf"";Integrated Security=True;User Instance=True";
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;AttachDbFilename=""C:\Users\User\Documents\Visual Studio 2010\Projects\HireMe\HireMe.WcfService\HireMe.DataAccess.OdbcProvider\DataSource\HireMeDatabase.mdf"";Integrated Security=True;User Instance=True";
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=HireMeDatabase;C:\Users\User\Documents\Visual Studio 2010\Projects\HireMe\HireMe.WcfService\HireMe.DataAccess.OdbcProvider\DataSource\HireMeDatabase.mdf"";Integrated Security=True;User Instance=True";
//connStr = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=HireMeDatabase.mdf;Integrated Security=True;User Instance=True";
//connStr = @"FILEDSN=C:\Users\User\Documents\Visual Studio 2010\Projects\HireMe\HireMe.WcfService\HireMe.DataAccess.OdbcProvider\DataSource\HireMeOdbc.dsn;";
//connStr = @"DSN=HireMeOdbc; Trusted_Connection=yes;Database=HireMe;AttachDbFilename=""C:\HireMe\HireMe.DataSource\HireMe.DataSource\HireMe.mdf""";
//connStr = @"DSN=HireMeOdbc; Trusted_Connection=yes;"; //does work, so good enough!
//connStr = @"FILEDSN=C:\HireMe\OdbcDataSource\HireMeOdbc.dsn; Trusted_Connection=yes;";
//The lessons here are:
//The AttachDbFilename has to be in a location where the sql server can access it.
//TODO: Need to test if you can copy mdf file to other location than where it was created. It seems like this should be possible.
//Trusted_Connection=yes indicates no user/pass
//Driver,Server,Database can be included in the DSN config.
//In DSN config, you must set both the Name to the database name, and the path to the attached file (where it can be accessed).
//If you even come close to modifying the mdf file, you will have to reconfig the dsn file.
#endregion
using (OdbcConnection connection = new OdbcConnection(connStr))
{
connection.Open();
string queryStr = string.Format(@"INSERT INTO {0} " +
@"({1}, {2}, {3}) " +
@"VALUES('{4}','{5}', '{6}');",
Properties.Resources.CustomerTable,
Properties.Resources.CustomerIdColumn, Properties.Resources.NameColumn, Properties.Resources.EmailAddressColumn,
dto.Id, dto.Name, dto.EmailAddress);
var cmd = connection.CreateCommand();
cmd.CommandText = queryStr;
var numRowsAffected = cmd.ExecuteNonQuery();
}
//RETURN OUR INSERTED DTO
return dto;
}
public bool VerifyStudentId(string studentId)
{
var isValidId = false;
var connection = new OdbcConnection { ConnectionString = "DSN=CARS" };
var academicYear = GetAcademicYear();
const string sql = "SELECT unique id_rec.id FROM id_rec, prog_enr_rec, outer stu_acad_rec " +
"WHERE id_rec.id = prog_enr_rec.id " +
"AND id_rec.id = stu_acad_rec.id " +
"AND stu_acad_rec.yr in ('{0}','{1}') " +
"AND stu_acad_rec.reg_stat='C' " +
"AND (stu_acad_rec.reg_hrs > 0 or stu_acad_rec.reg_au_hrs > 0) " +
"AND id_rec.id = '{2}'";
var sqlToExecute = string.Format(sql, academicYear[0], academicYear[1], studentId);
try
{
connection.Open();
var command = connection.CreateCommand();
command.CommandText = sqlToExecute;
command.Connection = connection;
var reader = command.ExecuteReader();
reader.Read();
isValidId = reader.HasRows;
reader.Close();
reader.Dispose();
}
catch (Exception ex)
{
// ReSharper disable once UnusedVariable
var msg = ex.Message;
}
finally
{
connection.Close();
connection.Dispose();
}
return isValidId;
}
/// <summary>
/// Imports a single file into staging.
/// </summary>
private static void ImportFile(string csvFile, SqlTransaction transaction)
{
var statement = string.Format("select * from \"{0}\"", csvFile);
using (var connection = new OdbcConnection(Settings.Default.ImportDriver))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = statement;
using (var reader = command.ExecuteReader())
{
BulkImport(reader, transaction);
}
}
}
}
static void Main(string[] args)
{
// SQL setup
// CREATE TABLE timetest ([key] int identity(1,1), value time(7))
// INSERT INTO timetest (value) VALUES ('08:00'), ('12:00'), ('16:00'), ('20:00')
// CREATE TABLE datetest ([key] int identity(1,1), value date)
// INSERT INTO datetest (value) VALUES ('2012-01-02'), ('2012-02-03'), ('2012-03-04'), ('2012-04-05')
string driver = "{SQL Server Native Client 11.0}";
string server = "gavinm";
string database = "XRG_Dev";
using (var connection = new OdbcConnection(String.Format("Driver={0};Server={1};Database={2};Trusted_Connection=yes", driver, server, database))) {
connection.Open();
using (var command = connection.CreateCommand()) {
try {
#region date - http://technet.microsoft.com/en-us/library/bb630352
command.CommandText = "SELECT value FROM datetest WHERE value < { d '2012-02-15' }"; // OK
//command.CommandText = "SELECT value FROM datetest WHERE value < ?";
//command.Parameters.AddWithValue("value", new DateTime(2012, 02, 15, 12, 34, 56)); // OK - fails if ms specified
using (var reader = command.ExecuteReader())
while (reader.Read()) Console.WriteLine(reader.GetDate(0));
#endregion
#region time - http://technet.microsoft.com/en-us/library/bb677243
command.CommandText = "SELECT value FROM timetest WHERE value < { t '12:34:56' }"; // incompatible
//command.CommandText = "SELECT value FROM timetest WHERE value < '12:34:56.123456789'"; // OK
//command.CommandText = "SELECT value FROM timetest WHERE value < ?";
//command.Parameters.AddWithValue("value", new TimeSpan(0, 12, 34, 56, 789)); // OK
using (var reader = command.ExecuteReader())
while (reader.Read()) Console.WriteLine(reader.GetTime(0));
#endregion
} catch (Exception ex) {
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
/// <param name="filename"></param>
/// <param name="type"></param>
/// <param name="UVset"></param>
/// <returns></returns>
public bool AddMissingTexture(ContentObject co, string filename, string type, int UVset)
{
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
//AddMissingTexture(pid,filename,texturetype,uvset)
command.CommandText = "{CALL AddMissingTexture(?,?,?,?,?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("filename", filename);
command.Parameters.AddWithValue("texturetype", type);
command.Parameters.AddWithValue("uvset", UVset);
command.Parameters.AddWithValue("pid", co.PID);
command.Parameters.AddWithValue("revision", co.Revision);
var result = command.ExecuteReader();
co.MissingTextures.Add(new Texture(filename, type, 0));
}
}
return(true);
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
/// <returns></returns>
public bool RemoveAllKeywords(ContentObject co)
{
bool result;
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
command.CommandText = "{CALL RemoveAllKeywords(?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("pid", co.PID);
Boolean.TryParse(command.ExecuteReader().ToString(), out result);
if (result == null)
{
result = false;
}
}
}
return(result);
}
public DataTable GetDataFromHive(string hiveQl)
{
var dbConnection = new OdbcConnection("DSN=horton");
try
{
dbConnection.Open();
}
catch (OdbcException ex)
{
Console.WriteLine(ex.Message);
return null;
}
OdbcCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = hiveQl; // "SELECT * FROM sample_08 LIMIT 100;";
DbDataReader dr = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dr);
dbConnection.Close();
return dataTable;
}
public OmnisConnection()
{
faecherspiegelFOS = new Dictionary<string, string[]>();
faecherspiegelBOS = new Dictionary<string, string[]>();
OdbcConnection conn = new OdbcConnection("DSN=sd");
conn.Open();
var command = conn.CreateCommand();
command.CommandText = "SELECT FSP_NUM, FSP_SCHULART, FSP_Z_F01, FSP_Z_F02, FSP_Z_F03, FSP_Z_F04, FSP_Z_F05, FSP_Z_F06, FSP_Z_F07, FSP_Z_F08, FSP_Z_F09, FSP_Z_F10, FSP_Z_F11, FSP_Z_F12, FSP_Z_F13, FSP_Z_F14, FSP_Z_F15, FSP_Z_F16, FSP_Z_F17, FSP_Z_F18, FSP_Z_F19, FSP_Z_F20, FSP_Z_F21, FSP_Z_F22, FSP_Z_F23, FSP_Z_F24, FSP_Z_F25, FSP_Z_F26, FSP_Z_F27, FSP_Z_F28, FSP_Z_F29, FSP_Z_F30 FROM DZeugnisFspSt WHERE FSP_Schulart='FOS' OR FSP_Schulart = 'BOS'";
var cmdResult = command.ExecuteReader();
while (cmdResult.Read())
{
string code = cmdResult.GetString(0);
string schulart = cmdResult.GetString(1);
if ("FOS".Equals(schulart, StringComparison.OrdinalIgnoreCase))
{
if (!faecherspiegelFOS.ContainsKey(code))
{
faecherspiegelFOS.Add(code, new string[30]);
}
for (int i = 0; i < 30; i++)
{
faecherspiegelFOS[code][i] = cmdResult.GetString(i + 2);
}
}
else if ("BOS".Equals(schulart, StringComparison.OrdinalIgnoreCase))
{
if (!faecherspiegelBOS.ContainsKey(code))
{
faecherspiegelBOS.Add(code, new string[30]);
}
for (int i = 0; i < 30; i++)
{
faecherspiegelBOS[code][i] = cmdResult.GetString(i + 2);
}
}
}
}
/**
* Autentica account dati username, password hash e salt
*/
public bool DoAuthUsername(String username, String pwhash, byte[] salt, out String error)
{
//
bool auth = false;
// Preimposta error
error = "";
try
{
// Odbc
OdbcConnection odbc = new OdbcConnection("DSN=nwn_master_server");
odbc.Open();
using (OdbcCommand dbcmd = odbc.CreateCommand())
{
// Query selezione account
String query = "SELECT * FROM " + TABLE_ACCOUNTS + " WHERE username='******'";
dbcmd.CommandText = query;
using (OdbcDataReader dbreader = dbcmd.ExecuteReader())
{
while (dbreader.Read())
{
if (pwhash == Hashing.Md5PwdHashStr((String)dbreader["password"], salt))
auth = true;
}
dbreader.Close();
}
}
odbc.Close();
}
catch (Exception e)
{
error = e.StackTrace;
}
// Exit
return auth;
}
static async Task ExecHqlCmd(string traceId, string hqlCmd, string[] settings = null, bool skipResults = true)
{
Trace.TraceInformation(traceId + " : strated");
var connectionString = OdbcUnitTests.GetConnectionString(OdbcUnitTests.HiveDsnConnectionString, settings);
Trace.TraceInformation("{0} Using connection string => {1} ", traceId, connectionString);
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
var startTime = DateTimeOffset.UtcNow;
await conn.OpenAsync();
using (OdbcCommand cmd = conn.CreateCommand())
{
cmd.CommandText = hqlCmd;
using (DbDataReader dr = await cmd.ExecuteReaderAsync())
{
var completionTime = DateTimeOffset.UtcNow;
var execTime = completionTime.Subtract(startTime);
Trace.TraceInformation(traceId + " : execution time (sec) -> " + execTime.TotalSeconds);
if (!skipResults)
{
while (dr.Read())
{
var columns = new List<string>();
for (int i = 0; i < dr.VisibleFieldCount; i++)
{
columns.Add(dr.GetValue(i).ToString());
}
Trace.TraceInformation(traceId + " : " + string.Join(", ", columns));
}
}
}
}
}
Trace.TraceInformation(traceId + " : completed");
}
public IEnumerable <ContentObject> GetContentObjectsByKeywords(string keywords, string identity)
{
//We must transform the list into something MySQL finds acceptible in its syntax
char[] delimiters = new char[] { ',' };
string[] list = keywords.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
string escapeTemplate = "'{0}'";
//Add quotes around each of the list items, while also escaping any existing quotes
for (int i = 0; i < list.Length; i++)
{
list[i] = String.Format(escapeTemplate, list[i].Replace("'", "\'"));
}
keywords = String.Join(",", list);
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
List <ContentObject> objects = new List <ContentObject>();
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "{CALL GetContentObjectsByKeywords(?,?)}";
cmd.Parameters.AddWithValue("keylist", keywords);
cmd.Parameters.AddWithValue("uname", identity);
using (var results = cmd.ExecuteReader())
{
while (results.HasRows && results.Read())
{
ContentObject co = new ContentObject();
FillContentObjectLightLoad(co, results);
objects.Add(co);
}
}
}
return(objects);
}
}
private void Form1_Load(object sender, EventArgs e)
{
UInt32 counter = 0;
dataGridView1.Rows.Clear();
OdbcConnection db = new OdbcConnection("DSN=xoops3");
db.Open();
OdbcCommand dbCommand = db.CreateCommand();
dbCommand.CommandText = "SELECT * FROM xoops_buyersguide_manufacturer";
OdbcDataReader dbReader = dbCommand.ExecuteReader();
string[] columns = new string[6];
while (dbReader.Read())
{
counter++;
columns[0] = counter.ToString();
columns[1] = dbReader["manu_name"].ToString();
columns[2] = dbReader["manu_description"].ToString();
columns[3] = dbReader["manu_address"].ToString();
columns[4] = dbReader["manu_zip"].ToString();
columns[5] = dbReader["manu_town"].ToString();
dataGridView1.Rows.Add(columns);
}
}
/// <summary>
/// Establish a DSN connection with HotelReservationSystem database using
/// ODBCConnection class and validate userID and Password. If a user is
/// authorized, it logon the user to the Welcome page of the HotelReservationSystem
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void logonButton_Click(object sender, EventArgs e)
{
try
{
//Establish connection with database using OdbcConnection class
OdbcConnection objectOdbcConnection = new OdbcConnection(CONNECTION_STRING);
objectOdbcConnection.Open();
//Create an OdbcCommand object using CreateCommand of the OdbcConnection object
OdbcCommand objectOdbcCommand = objectOdbcConnection.CreateCommand();
objectOdbcCommand.CommandText = "SELECT * FROM USERS WHERE userId = ? and password= ?";
objectOdbcCommand.Parameters.Add("userid", OdbcType.NVarChar).Value = userIDTextBox.Text;
objectOdbcCommand.Parameters.Add("pasword", OdbcType.NVarChar).Value = passwordTextBox.Text;
OdbcDataReader dbReader = objectOdbcCommand.ExecuteReader();
dbReader.Read();
if (dbReader.HasRows)
{
//This is a valid userId/password, show the welcome page
Welcome objectWelcomeForm;
objectWelcomeForm = new Welcome();
objectWelcomeForm.Show();
this.Hide();
dbReader.Close();
objectOdbcCommand.Dispose();
objectOdbcConnection.Close();
return;
}
//The user id or password is not valid
dbReader.Close();
objectOdbcCommand.Dispose();
objectOdbcConnection.Dispose();
objectOdbcConnection.Close();
MessageBox.Show("Please enter a valid user and password");
}
catch (Exception exp)
{
MessageBox.Show("An exception in the code: \n" + exp.Message.ToString());
}
}
/// <summary>
///
/// </summary>
/// <param name="co"></param>
/// <param name="filename"></param>
/// <param name="description"></param>
public void AddSupportingFile(ContentObject co, string filename, string description, string dsid)
{
System.Data.Odbc.OdbcConnection connection = GetConnection();
{
using (var command = connection.CreateCommand())
{
//AddMissingTexture(pid,filename,texturetype,uvset)
command.CommandText = "{CALL AddSupportingFile(?,?,?,?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("newfilename", filename);
command.Parameters.AddWithValue("newdescription", description);
command.Parameters.AddWithValue("newcontentobjectid", co.PID);
command.Parameters.AddWithValue("newdsid", dsid);
var result = command.ExecuteReader();
//while (result.Read())
//{
co.SupportingFiles.Add(new SupportingFile(filename, description, dsid));
// }
}
}
}
private void button1_Click(object sender, EventArgs e)
{
var connection = new OdbcConnection("DSN=Local");
var command = connection.CreateCommand();
command.CommandText = "{ CALL AuthenticateAccount(?, ?, ?, ?) }";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@TLogin", OdbcType.NVarChar).Value = textBox1.Text;
command.Parameters.Add("@TPIN", OdbcType.NVarChar).Value = textBox2.Text;
var pResult = command.Parameters.Add("@Result", OdbcType.Bit);
pResult.Direction = ParameterDirection.Output;
var pAccountLocked = command.Parameters.Add("@AccountLocked", OdbcType.Bit);
// pAccountLocked.IsNullable = true;
pAccountLocked.Direction = ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
checkBox1.Checked = (Boolean)pResult.Value;
var accountLocked = pAccountLocked.Value != DBNull.Value && (Boolean)pAccountLocked.Value;
checkBox2.Checked = accountLocked;
}
private void Thread_Start()
{
bool connected = false;
OdbcConnection connection = null;
OdbcCommand command = null;
OdbcTransaction transact = null;
DateTime start = DateTime.Now;
bool shouldWriteException = true;
while (true)
{
this.m_Sync.WaitOne();
while (this.m_Queue.Count > 0)
{
try
{
object obj = this.m_Queue.Dequeue();
if (obj == null)
{
if (connected)
{
if (transact != null)
{
try
{
transact.Commit();
}
catch (Exception commitException)
{
Console.WriteLine("MyRunUO: Exception caught when committing transaction");
Console.WriteLine(commitException);
try
{
transact.Rollback();
Console.WriteLine("MyRunUO: Transaction has been rolled back");
}
catch (Exception rollbackException)
{
Console.WriteLine("MyRunUO: Exception caught when rolling back transaction");
Console.WriteLine(rollbackException);
}
}
}
try
{
connection.Close();
}
catch
{
}
try
{
connection.Dispose();
}
catch
{
}
try
{
command.Dispose();
}
catch
{
}
try
{
this.m_Sync.Close();
}
catch
{
}
Console.WriteLine(this.m_CompletionString, (DateTime.Now - start).TotalSeconds);
this.m_HasCompleted = true;
return;
}
else
{
try
{
connected = true;
connection = new OdbcConnection(this.m_ConnectionString);
connection.Open();
command = connection.CreateCommand();
if (Config.UseTransactions)
{
transact = connection.BeginTransaction();
command.Transaction = transact;
}
}
catch (Exception e)
{
try
{
if (transact != null)
transact.Rollback();
}
catch
{
}
try
{
if (connection != null)
connection.Close();
}
catch
{
}
try
{
if (connection != null)
connection.Dispose();
}
catch
{
}
try
{
if (command != null)
command.Dispose();
}
catch
{
}
try
{
this.m_Sync.Close();
}
catch
{
}
Console.WriteLine("MyRunUO: Unable to connect to the database");
Console.WriteLine(e);
this.m_HasCompleted = true;
return;
}
}
}
else if (obj is string)
{
command.CommandText = (string)obj;
command.ExecuteNonQuery();
}
else
{
string[] parms = (string[])obj;
command.CommandText = parms[0];
if (command.ExecuteScalar() == null)
{
command.CommandText = parms[1];
command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
if (shouldWriteException)
{
Console.WriteLine("MyRunUO: Exception caught in database thread");
Console.WriteLine(e);
shouldWriteException = false;
}
}
}
lock (this.m_Queue.SyncRoot)
{
if (this.m_Queue.Count == 0)
this.m_Sync.Reset();
}
}
}
/// <summary>
///
/// </summary>
/// <param name="pid"></param>
/// <param name="updateViews"></param>
/// <param name="getReviews"></param>
/// <param name="revision"></param>
/// <returns></returns>
public ContentObject GetContentObjectById(string pid, bool updateViews, bool getReviews = true, int revision = -1)
{
if (String.IsNullOrEmpty(pid))
{
return(null);
}
List <ContentObject> results = new List <ContentObject>();
ContentObject resultCO = null;
if (false)//(_Memory.ContainsKey(co.PID))
{
//co = _Memory[co.PID];
}
else
{
System.Data.Odbc.OdbcConnection conn = GetConnection();
{
using (var command = conn.CreateCommand())
{
command.CommandText = "{CALL GetContentObject(?);}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("targetpid", pid);
//command.Parameters.AddWithValue("pid", pid);
using (var result = command.ExecuteReader())
{
int NumberOfRows = 0;
while (result.Read())
{
NumberOfRows++;
var co = new ContentObject()
{
PID = pid,
Reviews = new List <Review>()
};
var properties = co.GetType().GetProperties();
foreach (var prop in properties)
{
if (prop.PropertyType == typeof(String) && prop.GetValue(co, null) == null)
{
prop.SetValue(co, String.Empty, null);
}
}
FillContentObjectFromResultSet(co, result);
LoadTextureReferences(co, conn);
LoadMissingTextures(co, conn);
LoadSupportingFiles(co, conn);
LoadReviews(co, conn);
co.Keywords = LoadKeywords(conn, co.PID);
results.Add(co);
}
ContentObject highest = null;
if (results.Count > 0)
{
if (revision == -1)
{
highest = (from r in results
orderby r.Revision descending
select r).First();
}
else
{
highest = (from r in results
where r.Revision == revision
select r).First();
}
resultCO = highest;
}
else
{
return(null);
}
}
}
}
}
if (updateViews)
{
System.Data.Odbc.OdbcConnection secondConnection = GetConnection();
{
using (var command = secondConnection.CreateCommand())
{
command.CommandText = "{CALL IncrementViews(?)}";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("targetpid", pid);
command.ExecuteNonQuery();
}
}
}
return(resultCO);
}
public static void WriteDataToTemplate(string filePath, string fileName, DataTable table)
{
//CopyTemplateToWorkingDir(filePath, fileName);
try
{
OdbcConnection obdcconn = new System.Data.Odbc.OdbcConnection(GetConnectionString(dir + fileName));
OdbcCommand oCmd = null;
oCmd = obdcconn.CreateCommand();
obdcconn.Open();
oCmd.CommandText = "INSERT INTO " + fileName + " VALUES ('0000',' 0000','0000','0000','0','0000',0,0,0,'')";
oCmd.ExecuteNonQuery();
oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "DELETE FROM " + fileName + " WHERE BOARD <> '0000';";
var affected = oCmd.ExecuteNonQuery();
try
{
var count = 0;
foreach (DataRow row in table.Rows)
{
count++;
oCmd = obdcconn.CreateCommand();
string insertSql = "INSERT INTO " + fileName + " VALUES(";
string values = "";
for (int i = 0; i < table.Columns.Count; i++)
{
string value = ReplaceEscape(row[i].ToString());
switch (i)
{
case 1:
value = " " + value;
break;
case 2:
value = value.PadRight(6, ' ');
break;
case 6:
value = CalculateDay(value);
values = values + value + ",";
values = values + "0,";
values = values + "0,";
break;
case 7:
values = "'" + value + "'," + values;
break;
default:
break;
}
if (i != 6 && i != 0 && i != 7)
{
values = values + "'" + value + "',";
}
}
values = values.Substring(0, values.Length - 1) + ")";
insertSql = insertSql + values;
oCmd.CommandText = insertSql;
oCmd.ExecuteNonQuery();
oCmd.Dispose();
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
oCmd = obdcconn.CreateCommand();
oCmd.CommandText = "DELETE FROM " + fileName + " WHERE BOARD='0000'";
oCmd.ExecuteNonQuery();
obdcconn.Close();
CopyTemplateToWorkingDir(System.Configuration.ConfigurationSettings.AppSettings["OutputPath"], fileName);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
