C# OdbcConnection.CreateCommand方法代码示例

    //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*/
    }

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

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

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

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

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

        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 void ExecuteSqlUpdate(string dsnConnection, string query)
 {
     using (var connection = new OdbcConnection(dsnConnection))
     {
         connection.Open();
         var cmd = connection.CreateCommand();
         cmd.CommandText = query;
         cmd.ExecuteNonQuery();
     }
 }