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