コード例 #1
0
    public static List<UserLink> getUserLinks(String username)
    {
        List<UserLink> links = new List<UserLink>(); //list to hold results of query

        String database = System.Configuration.ConfigurationManager.ConnectionStrings["programaholics_anonymous_databaseConnectionString"].ConnectionString; //connection string for db

        OleDbConnection sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + database); //db connection

        sqlConn.Open(); //connect to db

        //sql select string
        String select = "SELECT [links].path, [links].textValue FROM [links] INNER JOIN [users] ON [users].accessLevel = [links].accessLevel WHERE username = @username";

        OleDbCommand cmd = new OleDbCommand(select, sqlConn);

        //add parameters to command
        cmd.Parameters.Add("userName", OleDbType.VarChar, 255).Value = username;
        cmd.Prepare();

        //create data reader
        OleDbDataReader dr = cmd.ExecuteReader();

        //add results of query to links list
        while (dr.Read())
        {
            String path = dr["path"].ToString();
            String textValue = dr["textValue"].ToString();
            UserLink link = new UserLink(path, textValue);
            links.Add(link);
        }
        //close all resources and return list to calling method
        dr.Close();
        sqlConn.Close();
        return links;
    }
    protected void Button1_Click(object sender, EventArgs e)
    { // ELIMINAR UM PROFESSOR
        String paramCodigo = "";
        try
        {
            paramCodigo = Request["TextBox1"];
            if (paramCodigo == null) paramCodigo = "-1";
        }
        catch (Exception) { };

        try
        {
            conexao = new OleDbConnection(
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Escola.mdb");
            conexao.Open();

            //Pelo método tradicional:
            //stm = new OleDbCommand("DELETE FROM Professores WHERE codprof = " + paramCodigo, conexao);

            //Usando prepared statement:
            //"The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement 
            //or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, 
            //the question mark (?) placeholder must be used." Example: SELECT * FROM Customers WHERE CustomerID = ?
            //https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx
            //https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.prepare(v=vs.110).aspx
            stm = new OleDbCommand("DELETE FROM Professores WHERE codprof = ?", conexao);
            stm.Parameters.Add("codp", OleDbType.Integer);
            stm.Parameters[0].Value = Convert.ToInt16(paramCodigo);
            stm.Prepare();

            int qtde = stm.ExecuteNonQuery();

            if (qtde == 0)
            {
                Label1.Text = "Naõ foi possível eliminar o professor com código: " + paramCodigo + ".";
            }
            else
            {
                Label1.Text = "Professor eliminado com sucesso: professor código " + paramCodigo + ".";
            }

            stm.Dispose();
            conexao.Close();
        }
        catch (Exception exc)
        {
            Label1.Text = "Erro: " + exc.Message;
        }

    }
コード例 #3
0
ファイル: Form1.cs プロジェクト: AlasdairHaig/DatabaseApp
        private void GetDataSet()
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = conn;
                command.CommandText = "Select * from Mitarbeiter";
                command.Prepare();
                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    MessageBox.Show(reader["Name"].ToString());
                }
            }
        }
コード例 #4
0
        internal bool addNewCreditCard(PaymentInformation newCreditCard)
        {
            using (OleDbConnection sqlCon = new OleDbConnection(database))
            {
                try
                {

                    sqlCon.Open();

                    String insert = "INSERT INTO [PAYMENT_INFORMATION] ([user_id], [credit_card_type], [credit_card_number], [card_city], [card_state], [card_exp_date], [security_code]) " +
                        "VALUES(@userId, @creditCardType, @creditCardNumber, @cardCity, @cardState, @expDate, @securityCode)";
                    OleDbCommand cmd = new OleDbCommand(insert, sqlCon);

                    cmd.Parameters.Add("userId", OleDbType.VarChar, 255).Value = newCreditCard.getUser().getId();
                    cmd.Parameters.Add("creditCardType", OleDbType.VarChar, 255).Value = newCreditCard.getCreditCardType();
                    cmd.Parameters.Add("creditCardNumber", OleDbType.VarChar, 255).Value = newCreditCard.getCreditCardNumber();
                    cmd.Parameters.Add("cardCity", OleDbType.VarChar, 255).Value = newCreditCard.getCity();
                    cmd.Parameters.Add("cardState", OleDbType.VarChar, 255).Value = newCreditCard.getState();
                    cmd.Parameters.Add("expDate", OleDbType.VarChar, 255).Value = newCreditCard.getCardExpDate();
                    cmd.Parameters.Add("securityCode", OleDbType.VarChar, 255).Value = newCreditCard.getSecurityCode();

                    cmd.Prepare();
                    int rows = cmd.ExecuteNonQuery();

                    if (rows == 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (OleDbException ex)
                {
                    return false;
                }
                finally
                {
                    sqlCon.Close();
                }
            }
        }
コード例 #5
0
        private static void Test_Command_Prepare()
        {
            (new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery();
            (new OleDbCommand("create table t(id int)", conn)).ExecuteNonQuery();

            using (OleDbCommand cmd = new OleDbCommand("insert into t(id) value(?);", conn))
            {
                OleDbParameter para = cmd.CreateParameter();
                para.Value = 10;
                para.OleDbType = OleDbType.Integer;
                para.ParameterName = "id";
                cmd.Parameters.Add(para);

                cmd.Prepare();
                cmd.ExecuteNonQuery();

                int count = GetTableRowsCount("t",conn);
                Assert.AreEqual(count, 1);
            }
        }
コード例 #6
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
        /// <summary>
        /// Adds a weapon name to the database.
        /// </summary>
        /// <param name="weaponName">The weapon name to add to the database.</param>
        /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
        /// <exception cref="OleDbException">A connection-level error occurred while opening the connection.</exception>
        /// <exception cref="InvalidOperationException">The INSERT INTO Weapon command failed.</exception>
        public static void AddWeaponName(String weaponName, ConfigReader configReader)
        {
            using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
                openConnection(conn);

                using (OleDbCommand command = new OleDbCommand()) {
                    command.Connection = conn;
                    command.CommandText = "INSERT INTO Weapon (WeaponName) VALUES (?)";
                    command.Prepare();
                    setStringParameter(command, "WeaponName", weaponName);

                    try {
                        command.ExecuteNonQuery();
                    } catch (InvalidOperationException e) {
                        Log.LogError("Could not perform INSERT INTO Weapon.", "SQL", command.CommandText);
                        throw e;
                    }
                }
            }
        }
コード例 #7
0
        public static void Initialize(TestContext context)
        {

            // The table referencing foreigh key should be droped firstly, or drop the owner tale will fail.
            OleDbCommand command = new OleDbCommand("DROP TABLE IF EXISTS score", TestCases.conn);
            command.ExecuteNonQuery();

            // create student table
            command.CommandText = "DROP TABLE IF EXISTS student";
            command.ExecuteNonQuery();
            command.CommandText = "CREATE TABLE student(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL) AUTO_INCREMENT=100";
            command.ExecuteNonQuery();

            // create course table
            command.CommandText = "DROP TABLE IF EXISTS course";
            command.ExecuteNonQuery();
            command.CommandText = "CREATE TABLE course(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL) AUTO_INCREMENT=100";
            command.ExecuteNonQuery();

            // create score table
            command.CommandText = "CREATE TABLE score(student_id INT NOT NULL FOREIGN KEY REFERENCES student(id) ON DELETE CASCADE, course_id INT NOT NULL FOREIGN KEY REFERENCES course(id), score INT NOT NULL, PRIMARY KEY(student_id, course_id))";
            command.ExecuteNonQuery();

            // insert data
            command.CommandText = "INSERT INTO student(name) VALUES ('qint'),('weihua'),('guojia')";
            command.ExecuteNonQuery();
            command.CommandText = "INSERT INTO course(name) VALUES ('Database'), ('Software Architecture'), ('Data Structure')";
            command.ExecuteNonQuery();
            command = new OleDbCommand("INSERT INTO score VALUES(?, ?, 90)", TestCases.conn);
            command.Parameters.Add("student_id", OleDbType.Integer);
            command.Parameters.Add("course_id", OleDbType.Integer);
            command.Prepare();
            for (int studentId = 100; studentId < 103; studentId++)
                for (int courseId = 100; courseId < 103; courseId++)
                {
                    command.Parameters[0].Value = studentId;
                    command.Parameters[1].Value = courseId;
                    command.ExecuteNonQuery();
                }
        }
コード例 #8
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
        /// <summary>
        /// Adds a caliber unit to the database.
        /// </summary>
        /// <param name="caliberUnit">The <c>CaliberUnit</c> to add to the database.</param>
        /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
        /// <exception cref="OleDbException">A connection-level error occurred while opening the connection.</exception>
        /// <exception cref="InvalidOperationException">The INSERT INTO Caliber command failed.</exception>
        public static void AddCaliberUnit(Datatype.CaliberUnit caliberUnit, ConfigReader configReader)
        {
            using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location")))
            {
                openConnection(conn);

                using (OleDbCommand command = new OleDbCommand()) {
                    command.Connection = conn;
                    command.CommandText = "INSERT INTO Caliber (UnitName, UnitsPerInch) VALUES (?, ?)";
                    command.Prepare();
                    setStringParameter(command, "UnitName", caliberUnit.unitName);
                    command.Parameters.Add("UnitsPerInch", OleDbType.Double).Value = caliberUnit.unitsPerInch;

                    try {
                        command.ExecuteNonQuery();
                    } catch (InvalidOperationException e) {
                        Log.LogError("Could not perform INSERT INTO Caliber.", "SQL", command.CommandText);
                        throw e;
                    }
                }
            }
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string cc = "provider = Microsoft.Ace.Oledb.12.0; data source = " + Application.StartupPath + @"\proyectoroque1.accdb";

            OleDbConnection cn = new OleDbConnection(cc);
            cn.Open();

            if (rbtmostrar.Checked)
            {
                DataTable tabla;
                OleDbDataAdapter datosAdapter;
                OleDbCommand comandoSQL;

                try
                {
                    tabla = new DataTable();

                    datosAdapter = new OleDbDataAdapter(txtsql.Text, cc);
                    comandoSQL = new OleDbCommand();

                    datosAdapter.Fill(tabla);

                    dgvlista3.DataSource = tabla;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error al mostrar los datos de la tabla [" +
                        "] de MySQL: " +
                        ex.Message, "Error ejecutar SQL",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            if (rbtconsulta.Checked)
            {
                try
                {
                    int numeroRegistrosAfectados = 0;

                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = cn;
                    cmd.CommandText = txtsql.Text;
                    cmd.Prepare();
                    numeroRegistrosAfectados = cmd.ExecuteNonQuery();
                    MessageBox.Show("Consulta de modificación de datos " +
                        "ejecutada, número de registros afectados: " +
                        Convert.ToString(numeroRegistrosAfectados) + ".",
                        "Consulta SQL ejecutada correctamente",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error ejecutar consulta de " +
                        "modificación de datos: " +
                        ex.Message, "Error ejecutar SQL",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
コード例 #10
0
ファイル: DB.cs プロジェクト: zhouxin262/idreader
 public static void prepareSql(OleDbCommand cmd)
 {
     try
     {
         openConnection();
         cmd.Connection = conn;
         cmd.Prepare();
         cmd.ExecuteNonQuery();
     }
     catch (Exception e)
     {
         throw new Exception(e.Message);
     }
     finally
     { closeConnection(); }
 }
コード例 #11
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
        /// <summary>
        /// Loads an <c>ImageData</c> from the database.
        /// </summary>
        /// <param name="targetID">The <paramref name="targetID"/> of the target to load.</param>
        /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
        /// <returns>The <c>ImageData</c> corresponding to the <paramref name="targetID"/>.</returns>
        /// <exception cref="OleDbException">A connection-level error occurred while opening the connection.</exception>
        public static Datatype.ImageData LoadData(int targetID, ConfigReader configReader)
        {
            Datatype.ImageData imageData = new Datatype.ImageData();
            using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
                openConnection(conn);

                using (OleDbCommand command = new OleDbCommand()) {
                    command.Connection = conn;
                    command.CommandText = "SELECT * FROM Target WHERE TargetID = ?";
                    command.Prepare();
                    command.Parameters.Add("TargetID", OleDbType.Integer).Value = targetID;

                    using (OleDbDataReader reader = command.ExecuteReader()) {
                        if (reader.Read()) {
                            imageData.targetID = reader.GetInt32(reader.GetOrdinal("TargetID"));
                            if (imageData.targetID != targetID) {
                                throw new Exception("Error in LoadData()");
                            }

                            imageData.origFilename = reader.GetString(reader.GetOrdinal("OrigFilename"));
                            imageData.reportFilename = reader.GetString(reader.GetOrdinal("ReportFilename"));
                            imageData.dateTimeFired = reader.GetDateTime(reader.GetOrdinal("DateTimeFired"));
                            imageData.dateTimeProcessed = reader.GetDateTime(reader.GetOrdinal("DateTimeProcessed"));
                            imageData.shooterLName = reader.GetString(reader.GetOrdinal("ShooterLName"));
                            imageData.shooterFName = reader.GetString(reader.GetOrdinal("ShooterFName"));
                            imageData.rangeLocation = reader.GetString(reader.GetOrdinal("RangeLocation"));
                            imageData.distanceUnits = (Datatype.UnitsOfMeasure)reader.GetByte(reader.GetOrdinal("DistanceUnits"));
                            imageData.distance = reader.GetInt32(reader.GetOrdinal("Distance"));
                            imageData.temperature = (Datatype.ImageData.Temperature)reader.GetByte(reader.GetOrdinal("Temperature"));
                            imageData.weaponName = reader.GetString(reader.GetOrdinal("WeaponName"));
                            imageData.serialNumber = reader.GetString(reader.GetOrdinal("SerialNumber"));
                            imageData.weaponNotes = reader.GetString(reader.GetOrdinal("WeaponNotes"));
                            imageData.caliber = GetCaliberUnit(reader.GetInt32(reader.GetOrdinal("CaliberID")), configReader);
                            imageData.caliberValue = reader.GetDouble(reader.GetOrdinal("CaliberValue"));
                            imageData.lotNumber = reader.GetString(reader.GetOrdinal("LotNumber"));
                            imageData.projectileMassGrains = reader.GetInt32(reader.GetOrdinal("ProjectileMassGrains"));
                            imageData.ammunitionNotes = reader.GetString(reader.GetOrdinal("AmmunitionNotes"));
                            imageData.shotsFired = reader.GetInt32(reader.GetOrdinal("ShotsFired"));

                            String points = reader.GetString(reader.GetOrdinal("ShotLocations"));
                            String[] pointElements = points.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                            if (pointElements.Length % 2 != 0) {
                                Log.LogWarning("A Points field has invalid values.", "targetID", targetID.ToString(),
                                    "SQL", command.CommandText);
                            } else {
                                for (int i = 0; i < pointElements.Length; i += 2) {
                                    imageData.points.Add(new Point(Int32.Parse(pointElements[i]), Int32.Parse(pointElements[i + 1])));
                                }
                            }

                            String scale = reader.GetString(reader.GetOrdinal("Scale"));
                            String[] scaleElements = scale.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                            if (scaleElements.Length != 8) {
                                Log.LogWarning("A Scale field has invalid values.", "targetID", targetID.ToString(),
                                    "SQL", command.CommandText);
                            } else {
                                imageData.scale.horizontal = new Point(Int32.Parse(scaleElements[0]), Int32.Parse(scaleElements[1]));
                                imageData.scale.middle = new Point(Int32.Parse(scaleElements[2]), Int32.Parse(scaleElements[3]));
                                imageData.scale.vertical = new Point(Int32.Parse(scaleElements[4]), Int32.Parse(scaleElements[5]));
                                imageData.scale.horizontalLength = float.Parse(scaleElements[6]);
                                imageData.scale.verticalLength = float.Parse(scaleElements[7]);
                            }

                            String ROI = reader.GetString(reader.GetOrdinal("ROI"));
                            String[] ROIElements = ROI.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                            if (ROIElements.Length != 4) {
                                Log.LogWarning("An ROI field has invalid values.", "targetID", targetID.ToString(),
                                    "SQL", command.CommandText);
                            } else {
                                imageData.regionOfInterest.topLeft = new Point(Int32.Parse(ROIElements[0]), Int32.Parse(ROIElements[1]));
                                imageData.regionOfInterest.bottomRight = new Point(Int32.Parse(ROIElements[2]), Int32.Parse(ROIElements[3]));
                            }
                        } else {
                            Log.LogInfo("Target ID not found.", "targetID", targetID.ToString(), "SQL", command.CommandText);
                            return null;
                        }
                    }
                }
            }

            return imageData;
        }
コード例 #12
0
ファイル: OleDbCommand_Prepare.cs プロジェクト: nlhepler/mono
		public void run()
		{
			Exception exp = null;
			int intRecordsAffected = 0;

			string sql = "Update Shippers Set CompanyName=? Where ShipperID = 2";
			OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
			OleDbCommand cmd = new OleDbCommand("", con);
			con.Open();


			//get expected result
			cmd.CommandText = "select count(*) from Shippers where ShipperID = 2";
			int ExpectedRows = int.Parse(cmd.ExecuteScalar().ToString());

			cmd.CommandText = sql;

			//Currently not running on DB2: .Net-Failed, GH:Pass
			//if (con.Provider.IndexOf("IBMDADB2") >= 0) return ;

			cmd.Parameters.Add(new OleDbParameter()); 
			cmd.Parameters[0].ParameterName = "CompName";
			cmd.Parameters[0].OleDbType = OleDbType.VarWChar; //System.InvalidOperationException:
			cmd.Parameters[0].Size = 20; //System.InvalidOperationException
			cmd.Parameters[0].SourceColumn = "CompanyName";
			cmd.Parameters[0].Value = "Comp1";

			try
			{
				BeginCase("Prepare Exception - missing OleDbType");
				try
				{
					cmd.Parameters[0].OleDbType = OleDbType.Empty ;
					cmd.Prepare();
				}
				catch (Exception ex) {exp = ex;}
				Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
				exp=null;
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
			cmd.Parameters[0].OleDbType = OleDbType.VarWChar; 

			try
			{
				BeginCase("Prepare Exception - missing Size");
				try
				{
					cmd.Parameters[0].Size = 0;
					cmd.Prepare();
				}
				catch (Exception ex) {exp = ex;}
				Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
				exp=null;
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
			cmd.Parameters[0].Size = 20;

			try
			{
				BeginCase("Prepare Exception - missing Size");
				try
				{
					con.Close();
					cmd.Prepare();
				}
				catch (Exception ex) {exp = ex;}
				Compare(exp.GetType().FullName, typeof(InvalidOperationException).FullName );
				exp=null;
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
			con.Open();

			try
			{
				BeginCase("ExecuteNonQuery first time");
				intRecordsAffected = cmd.ExecuteNonQuery();
				Compare(intRecordsAffected , ExpectedRows);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}


			try
			{
				BeginCase("ExecuteNonQuery second time, chage value");
				cmd.Parameters[0].Value = "Comp2";
				intRecordsAffected  = cmd.ExecuteNonQuery();
				Compare(intRecordsAffected , ExpectedRows);
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}
	
			if (con.State == ConnectionState.Open) con.Close();

		}
コード例 #13
0
ファイル: Form1.cs プロジェクト: AlasdairHaig/DatabaseAccess
        /// <summary>
        /// Fill the Listview
        /// </summary>
        private void fillListView()
        {
            using (OleDbConnection conn = new OleDbConnection(_sConnectionString))
            {
                conn.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = conn;
                command.CommandText = "Select * from Mitarbeiter";
                command.Prepare();
                OleDbDataReader reader = command.ExecuteReader();

                /*
                 *Create an ArrayList to hold the results
                 *
                 *First we create an array of objects. Each object in the array represents a column in the DataReader.
                 * We know how many columns are in the DataReader by using the FieldCount property.
                 * Now we have the array of objects we need to get some values. If we wanted, we could get each value
                 *individually and add it to the array; another way is to use the GetValues method. This method will populate.
                 */

                ArrayList rowList = new ArrayList();

                while (reader.Read())
                {
                    object[] values = new object[reader.FieldCount];
                    reader.GetValues(values);
                    rowList.Add(values);
                }

                // Have the columns already being added to the list view?
                if (_bColumnsSet == false)
                {
                    // No, so get the schema for this result set
                    DataTable schema = reader.GetSchemaTable();

                    // And set the list view to reflect the
                    // contents of the schema
                    SetColumnHeaders(schema);
                    listView1.Columns.RemoveAt(0);
                }

                /*
                 * Populate a List View with the Values from an Array List
                 * The first thing we have to do is to clear the ListView. Now we need to add the column
                 * values for each row into the ListView. We will do this by creating a ListViewItem object and
                 * adding that to the Items collection of the ListView. In order to create a ListViewItem we want to
                 * create an array of strings, where each string in the array corresponds to a column in the ListView.
                 * Using the Length property of the "row" in the ArrayList we are able to allocate enough strings
                 * in the string array to hold each column that exits in the row. Once we have built the string array,
                 * we create a new ListViewItem and add it to the ListView.
                 */

                listView1.Items.Clear();

                foreach (object[] row in rowList)
                {
                    string[] rowDetails = new string[row.Length - 1];
                    int columnIndex = 0;
                    bool pastFrst = false;

                    foreach (object column in row)
                    {
                        // I added an 'if' statement and boolean, to skip the ID column during the iteration
                        if (pastFrst)
                        {
                            rowDetails[columnIndex++] = Convert.ToString(column);
                        }
                        pastFrst = true;
                    }

                    ListViewItem newItem = new ListViewItem(rowDetails);

                    listView1.Items.Add(newItem);
                }

                conn.Close();
            }
        }
コード例 #14
0
ファイル: WSCFieldExport.cs プロジェクト: jwebb-vtg/WSCIEMP
        public static void PopulateContractDirtTable(int cropYear)
        {
            try {

                // Pull data
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) {

                    using (SqlDataReader dr = WSCField.FieldPerformance2GetYear(conn, cropYear)) {

                        using (OLEDB.OleDbConnection msConn =
                                  new OLEDB.OleDbConnection(Globals.BeetExportConnectionString())) {

                            string qry = "ContractDirtInsert";

                            if (msConn.State != System.Data.ConnectionState.Open) { msConn.Open(); }
                            using (OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(qry, msConn)) {

                                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                                cmd.Parameters.Add("pSHID", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pCropYear", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldName", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pFieldSequenceNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pContractNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pStationNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pStationName", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pDirtPercent", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pDirtPercent"].Precision = 18;
                                cmd.Parameters["pDirtPercent"].Scale = 4;
                                cmd.Parameters.Add("pStationContracts", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pDirtPercentRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Prepare();

                                while (dr.Read()) {

                                    cmd.Parameters["pSHID"].Value = dr.GetString(dr.GetOrdinal("fldp2_shid"));
                                    cmd.Parameters["pCropYear"].Value = dr.GetInt32(dr.GetOrdinal("fldp2_crop_year"));
                                    cmd.Parameters["pFieldName"].Value = dr.GetString(dr.GetOrdinal("fldp2_field_name"));
                                    cmd.Parameters["pFieldSequenceNo"].Value = dr.GetInt32(dr.GetOrdinal("fldp2_field_sequence_no"));
                                    cmd.Parameters["pContractNo"].Value = dr.GetInt32(dr.GetOrdinal("fldp2_contract_no"));
                                    cmd.Parameters["pStationNo"].Value = dr.GetInt32(dr.GetOrdinal("fldp2_station_no"));
                                    cmd.Parameters["pStationName"].Value = dr.GetString(dr.GetOrdinal("fldp2_station_name"));
                                    cmd.Parameters["pDirtPercent"].Value = dr.GetDecimal(dr.GetOrdinal("fldp2_dirt_pct"));
                                    cmd.Parameters["pStationContracts"].Value = dr.GetInt32(dr.GetOrdinal("fldp2_station_contracts"));
                                    cmd.Parameters["pDirtPercentRank"].Value = dr.GetInt32(dr.GetOrdinal("fldp2_dirt_pct_rank"));

                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex) {
                string errMsg = MOD_NAME + "PopulateContractDirtTable";
                WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, ex);
                throw (wscEx);
            }
        }
コード例 #15
0
ファイル: WSCFieldExport.cs プロジェクト: jwebb-vtg/WSCIEMP
        //public static void TestQuery() {
        //    try {
        //        using (OLEDB.OleDbConnection msConn = new OLEDB.OleDbConnection(Globals.BeetExportConnectionString())) {
        //            using  (OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand("select * from FieldContracting where City = 'Ovid'", msConn)) {
        //                msConn.Open();
        //                using (OLEDB.OleDbDataReader odr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) {
        //                    while (odr.Read()) {
        //                        int colNum = odr.GetOrdinal("FieldAvgHarvestDate");
        //                    }
        //                }
        //            }
        //        }
        //    } catch(Exception ex) {
        //        string errMsg = MOD_NAME;
        //        WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, ex);
        //        throw (wscEx);
        //    }
        //}
        public static void PopulateContractingTable(int cropYear)
        {
            const string METHOD_NAME = "PopulateContractingTable";
            string tmp = "";

            try {

                //TestQuery();

                // Pull data
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) {
                    using (SqlDataReader dr = FieldContractingGetExport(conn, cropYear)) {

                        using (OLEDB.OleDbConnection msConn =
                                  new OLEDB.OleDbConnection(Globals.BeetExportConnectionString())) {

                            string qry = "FieldContractingInsert";

                            if (msConn.State != System.Data.ConnectionState.Open) { msConn.Open(); }
                            using (OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(qry, msConn)) {

                                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                                cmd.Parameters.Add("pSHID", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pCropYear", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pBusinessName", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pTaxID", OLEDB.OleDbType.VarChar, 12);
                                cmd.Parameters.Add("pAddrLine1", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pAddrLine2", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pCity", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pState", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pPostalCode", OLEDB.OleDbType.VarChar, 11);
                                cmd.Parameters.Add("pPhone", OLEDB.OleDbType.VarChar, 16);
                                cmd.Parameters.Add("pFax", OLEDB.OleDbType.VarChar, 16);
                                cmd.Parameters.Add("pCellPhone", OLEDB.OleDbType.VarChar, 16);
                                cmd.Parameters.Add("pOther", OLEDB.OleDbType.VarChar, 40);
                                cmd.Parameters.Add("pEmail", OLEDB.OleDbType.VarChar, 50);
                                cmd.Parameters.Add("pContractNumber", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAgriculturist", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pContractFactoryNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pContractFactoryName", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pContractStationNumber", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pContractStationName", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldName", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pFieldSequenceNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldCounty", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pFieldState", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldTownship", OLEDB.OleDbType.VarChar, 3);
                                cmd.Parameters.Add("pFieldRange", OLEDB.OleDbType.VarChar, 4);
                                cmd.Parameters.Add("pFieldSection", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldQuadrant", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldQuarterQuandrant", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldLatitude", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldLatitude"].Precision = 18;
                                cmd.Parameters["pFieldLatitude"].Scale = 6;
                                cmd.Parameters.Add("pFieldLongitude", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldLongitude"].Precision = 18;
                                cmd.Parameters["pFieldLongitude"].Scale = 6;
                                cmd.Parameters.Add("pFieldDescription", OLEDB.OleDbType.VarChar, 100);
                                cmd.Parameters.Add("pFieldAcres", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFSAOfficial", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldFSANumber", OLEDB.OleDbType.VarChar, 26);
                                cmd.Parameters.Add("pFieldFSAState", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldFSACounty", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pFieldFarmNumber", OLEDB.OleDbType.VarChar, 5);
                                cmd.Parameters.Add("pFieldTractNumber", OLEDB.OleDbType.VarChar, 5);
                                cmd.Parameters.Add("pFieldNumber", OLEDB.OleDbType.VarChar, 4);
                                cmd.Parameters.Add("pFieldQuarterField", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldRotationLength", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPriorCrop", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pFieldYearsHavingBeets", OLEDB.OleDbType.VarChar, 15);
                                cmd.Parameters.Add("pFieldSuspectRhizomania", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectAphanomyces", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectCurlyTop", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectFusarium", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectRhizoctonia", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectNematode", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectCercospora", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectRootAphid", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSuspectPowderyMildew", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldWaterSource", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pFieldIrrigationSystem", OLEDB.OleDbType.VarChar, 20);
                                // Added 2/2007
                                cmd.Parameters.Add("pLandOwner", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldPostAphanomyces", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostCercospora", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostCurlyTop", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostFusarium", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostNematode", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostPowderyMildew", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostRhizoctonia", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostRhizomania", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostRootAphid", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pSampleGridZone", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldPostWater", OLEDB.OleDbType.Boolean, 0);
                                // Added 2/2008
                                cmd.Parameters.Add("pFieldOwnership", OLEDB.OleDbType.VarChar, 15);
                                cmd.Parameters.Add("pFieldTillage", OLEDB.OleDbType.VarChar, 30);
                                // Added 11/2009
                                cmd.Parameters.Add("pFieldAvgHarvestDate", OLEDB.OleDbType.Date);

                                cmd.Prepare();

                                while (dr.Read()) {

                                    cmd.Parameters["pSHID"].Value = dr.GetString(dr.GetOrdinal("SHID"));
                                    cmd.Parameters["pCropYear"].Value = dr.GetInt32(dr.GetOrdinal("CropYear"));
                                    cmd.Parameters["pBusinessName"].Value = dr.GetString(dr.GetOrdinal("BusinessName"));
                                    cmd.Parameters["pTaxID"].Value = dr.GetString(dr.GetOrdinal("TaxID"));
                                    cmd.Parameters["pAddrLine1"].Value = dr.GetString(dr.GetOrdinal("AddrLine1"));
                                    cmd.Parameters["pAddrLine2"].Value = dr.GetString(dr.GetOrdinal("AddrLine2"));
                                    cmd.Parameters["pCity"].Value = dr.GetString(dr.GetOrdinal("City"));
                                    cmd.Parameters["pState"].Value = dr.GetString(dr.GetOrdinal("State"));
                                    cmd.Parameters["pPostalCode"].Value = dr.GetString(dr.GetOrdinal("PostalCode"));
                                    cmd.Parameters["pPhone"].Value = dr.GetString(dr.GetOrdinal("Phone"));
                                    cmd.Parameters["pFax"].Value = dr.GetString(dr.GetOrdinal("Fax"));
                                    cmd.Parameters["pCellPhone"].Value = dr.GetString(dr.GetOrdinal("CellPhone"));
                                    cmd.Parameters["pOther"].Value = dr.GetString(dr.GetOrdinal("Other"));
                                    cmd.Parameters["pEmail"].Value = dr.GetString(dr.GetOrdinal("Email"));
                                    cmd.Parameters["pContractNumber"].Value = Convert.ToInt32(dr.GetString(dr.GetOrdinal("ContractNumber")));
                                    cmd.Parameters["pAgriculturist"].Value = dr.GetString(dr.GetOrdinal("Agriculturist"));
                                    cmd.Parameters["pContractFactoryNo"].Value = dr.GetInt16(dr.GetOrdinal("ContractFactoryNo"));
                                    cmd.Parameters["pContractFactoryName"].Value = dr.GetString(dr.GetOrdinal("ContractFactoryName"));
                                    cmd.Parameters["pContractStationNumber"].Value = dr.GetInt16(dr.GetOrdinal("ContractStationNumber"));
                                    cmd.Parameters["pContractStationName"].Value = dr.GetString(dr.GetOrdinal("ContractStationName"));
                                    cmd.Parameters["pFieldName"].Value = dr.GetString(dr.GetOrdinal("FieldName"));
                                    cmd.Parameters["pFieldSequenceNo"].Value = dr.GetInt16(dr.GetOrdinal("FieldSequenceNo"));
                                    cmd.Parameters["pFieldCounty"].Value = dr.GetString(dr.GetOrdinal("FieldCounty"));
                                    cmd.Parameters["pFieldState"].Value = dr.GetString(dr.GetOrdinal("FieldState"));
                                    cmd.Parameters["pFieldTownship"].Value = dr.GetString(dr.GetOrdinal("FieldTownship"));
                                    cmd.Parameters["pFieldRange"].Value = dr.GetString(dr.GetOrdinal("FieldRange"));
                                    cmd.Parameters["pFieldSection"].Value = dr.GetString(dr.GetOrdinal("FieldSection"));
                                    cmd.Parameters["pFieldQuadrant"].Value = dr.GetString(dr.GetOrdinal("FieldQuadrant"));
                                    cmd.Parameters["pFieldQuarterQuandrant"].Value = dr.GetString(dr.GetOrdinal("FieldQuadrant"));
                                    cmd.Parameters["pFieldLatitude"].Value = dr.GetDecimal(dr.GetOrdinal("FieldLatitude"));
                                    cmd.Parameters["pFieldLongitude"].Value = dr.GetDecimal(dr.GetOrdinal("FieldLongitude"));
                                    cmd.Parameters["pFieldDescription"].Value = dr.GetString(dr.GetOrdinal("FieldDescription"));
                                    cmd.Parameters["pFieldAcres"].Value = dr.GetInt32(dr.GetOrdinal("FieldAcres"));
                                    cmd.Parameters["pFSAOfficial"].Value = (dr.GetString(dr.GetOrdinal("FSAOfficial")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldFSANumber"].Value = dr.GetString(dr.GetOrdinal("FieldFSANumber"));
                                    cmd.Parameters["pFieldFSAState"].Value = dr.GetString(dr.GetOrdinal("FieldFSAState"));
                                    cmd.Parameters["pFieldFSACounty"].Value = dr.GetString(dr.GetOrdinal("FieldFSACounty"));
                                    cmd.Parameters["pFieldFarmNumber"].Value = dr.GetString(dr.GetOrdinal("FieldFarmNumber"));
                                    cmd.Parameters["pFieldTractNumber"].Value = dr.GetString(dr.GetOrdinal("FieldTractNumber"));
                                    cmd.Parameters["pFieldNumber"].Value = dr.GetString(dr.GetOrdinal("FieldNumber"));
                                    cmd.Parameters["pFieldQuarterField"].Value = dr.GetString(dr.GetOrdinal("FieldQuarterField"));
                                    cmd.Parameters["pFieldRotationLength"].Value = dr.GetString(dr.GetOrdinal("FieldRotationLength"));
                                    cmd.Parameters["pFieldPriorCrop"].Value = dr.GetString(dr.GetOrdinal("FieldPriorCrop"));
                                    cmd.Parameters["pFieldYearsHavingBeets"].Value = dr.GetString(dr.GetOrdinal("FieldYearsHavingBeets"));
                                    cmd.Parameters["pFieldSuspectRhizomania"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectRhizomania")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectAphanomyces"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectAphanomyces")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectCurlyTop"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectCurlyTop")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectFusarium"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectFusarium")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectRhizoctonia"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectRhizoctonia")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectNematode"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectNematode")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectCercospora"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectCercospora")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectRootAphid"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectRootAphid")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSuspectPowderyMildew"].Value = (dr.GetString(dr.GetOrdinal("FieldSuspectPowderyMildew")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldWaterSource"].Value = dr.GetString(dr.GetOrdinal("FieldWaterSource"));
                                    cmd.Parameters["pFieldIrrigationSystem"].Value = dr.GetString(dr.GetOrdinal("FieldIrrigationSystem"));
                                    // Added 2/2007
                                    cmd.Parameters["pLandOwner"].Value = dr.GetString(dr.GetOrdinal("LandOwner"));
                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostAphanomyces"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostAphanomyces"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostAphanomyces"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostCercospora"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostCercospora"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostCercospora"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostCurlyTop"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostCurlyTop"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostCurlyTop"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostFusarium"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostFusarium"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostFusarium"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostNematode"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostNematode"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostNematode"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostPowderyMildew"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostPowderyMildew"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostPowderyMildew"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostRhizoctonia"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostRhizoctonia"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostRhizoctonia"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostRhizomania"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostRhizomania"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostRhizomania"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostRootAphid"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostRootAphid"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostRootAphid"].Value = tmp;
                                    }

                                    cmd.Parameters["pSampleGridZone"].Value = (dr.GetString(dr.GetOrdinal("SampleGridZone")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldPostWater"].Value = (dr.GetString(dr.GetOrdinal("FieldPostWater")) == "Y" ? true : false);

                                    tmp = dr.GetString(dr.GetOrdinal("FieldOwnership"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldOwnership"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldOwnership"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldTillage"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldTillage"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldTillage"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("AvgDeliveryDate"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldAvgHarvestDate"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldAvgHarvestDate"].Value = tmp;
                                    }

                                    try {
                                        cmd.ExecuteNonQuery();
                                    }
                                    catch (OLEDB.OleDbException oex) {
                                        string errMsg = MOD_NAME + METHOD_NAME + ": OLEDB exception calling ExecuteQuery.";
                                        WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, oex);
                                        throw (wscEx);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex) {
                string errMsg = MOD_NAME + METHOD_NAME + ": tmp: " + tmp;
                WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, ex);
                throw (wscEx);
            }
        }
コード例 #16
0
        internal InventoryItem getSingleItem(int productId)
        {
            using (OleDbConnection sqlConn = new OleDbConnection(database))
            {
                InventoryItem item = null;
                try
                {
                    sqlConn.Open();
                    String select = "SELECT * FROM [INVENTORY_ITEM] WHERE [inventory_id] = @productId";

                    OleDbCommand cmd = new OleDbCommand(select, sqlConn);
                    cmd.Parameters.Add("productId", OleDbType.Integer).Value = productId;

                    cmd.Prepare();

                    OleDbDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        item = getItemFromReader(reader);
                    }

                    return item;
                }
                catch (OleDbException ex)
                {
                    return item;
                }
                finally
                {
                    sqlConn.Close();
                }
            }
        }
コード例 #17
0
ファイル: Form1.cs プロジェクト: AlasdairHaig/DatabaseAccess
        /// <summary>
        /// Fill the list box
        /// </summary>
        private void fillListBox()
        {
            //Step 1 - Open connection
            using (OleDbConnection conn = new OleDbConnection(_sConnectionString))
            {
                conn.Open();

                OleDbCommand command = new OleDbCommand();
                command.Connection = conn;
                command.CommandText = "Select * from Mitarbeiter";
                command.Prepare();
                OleDbDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        _ma.Name = (reader.GetValue(2).ToString());
                        _ma.Vorname = (reader.GetValue(1).ToString());
                        _ma.Hausnummer = (reader.GetValue(4).ToString());
                        _ma.Strasse = (reader.GetValue(3).ToString());
                        _ma.Postleitzahl = (reader.GetValue(5).ToString());
                        _ma.Ort = (reader.GetValue(6).ToString());

                        _blstContactList.Add(_ma.concatToString());
                    }
                    conn.Close();
            }
        }
コード例 #18
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
        /// <summary>
        /// Inserts an <c>ImageData</c> object into the <c>Target</c> table.
        /// </summary>
        /// <param name="imageData">The <c>ImageData</c> to insert.</param>
        /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
        /// <exception cref="OleDbException">A connection-level error occurred while opening the connection.</exception>
        /// <exception cref="InvalidOperationException">The INSERT INTO Target command failed.</exception>
        /// <exception cref="ArgumentException">The caliber or weapon name was not found in the database.</exception>
        /// <returns>The <c>targetID</c> of the newly-inserted record.</returns>
        private static int InsertData(Datatype.ImageData imageData, ConfigReader configReader)
        {
            int targetID = -1;
            using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
                openConnection(conn);

                if (!CaliberIDExists(imageData.caliber.caliberUnitID, configReader)) {
                    Log.LogError("Caliber to insert not found in database.", "unitID",
                        imageData.caliber.caliberUnitID.ToString(), "unitName", imageData.caliber.unitName,
                        "unitsPerInch", imageData.caliber.unitsPerInch.ToString());
                    throw new ArgumentException("Caliber to insert does not exist.", "caliber");
                }
                if (!WeaponExists(imageData.weaponName, configReader)) {
                    Log.LogError("Weapon name to insert not found in database.", "weaponName", imageData.weaponName);
                    throw new ArgumentException("Weapon name to insert not found in database.", "weaponName");
                }

                using (OleDbCommand command = new OleDbCommand()) {
                    command.Connection = conn;
                    command.CommandText = "INSERT INTO Target (OrigFilename, ReportFilename, DateTimeFired, DateTimeProcessed, ShooterLName, ShooterFName, RangeLocation, DistanceUnits, Distance, Temperature, WeaponName, SerialNumber, WeaponNotes, CaliberID, CaliberValue, LotNumber, ProjectileMassGrains, AmmunitionNotes, ShotsFired, ShotLocations, Scale, ROI) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                    command.Prepare();
                    setStringParameter(command, "OrigFilename", imageData.origFilename);
                    setStringParameter(command, "ReportFilename", imageData.reportFilename);
                    command.Parameters.Add("DateTimeFired", OleDbType.Date).Value = imageData.dateTimeFired;
                    command.Parameters.Add("DateTimeProcessed", OleDbType.Date).Value = DateTime.Now;
                    setStringParameter(command, "ShooterLName", imageData.shooterLName);
                    setStringParameter(command, "ShooterFName", imageData.shooterFName);
                    setStringParameter(command, "RangeLocation", imageData.rangeLocation);
                    command.Parameters.Add("DistanceUnits", OleDbType.UnsignedTinyInt).Value = imageData.distanceUnits;
                    command.Parameters.Add("Distance", OleDbType.Integer).Value = imageData.distance;
                    command.Parameters.Add("Temperature", OleDbType.UnsignedTinyInt).Value = imageData.temperature;
                    setStringParameter(command, "WeaponName", imageData.weaponName);
                    setStringParameter(command, "SerialNumber", imageData.serialNumber);
                    command.Parameters.Add("WeaponNotes", OleDbType.LongVarWChar).Value = imageData.weaponNotes == null ? "" : imageData.weaponNotes;
                    command.Parameters.Add("CaliberID", OleDbType.Integer).Value = imageData.caliber.caliberUnitID;
                    command.Parameters.Add("CaliberValue", OleDbType.Double).Value = imageData.caliberValue;
                    setStringParameter(command, "LotNumber", imageData.lotNumber);
                    command.Parameters.Add("ProjectileMassGrains", OleDbType.Integer).Value = imageData.projectileMassGrains;
                    command.Parameters.Add("AmmunitionNotes", OleDbType.LongVarWChar).Value = imageData.ammunitionNotes == null ? "" : imageData.ammunitionNotes;
                    command.Parameters.Add("ShotsFired", OleDbType.Integer).Value = imageData.shotsFired;

                    StringBuilder pointStringBuilder = new StringBuilder();
                    foreach (Point point in imageData.points) {
                        pointStringBuilder.Append(point.X);
                        pointStringBuilder.Append(",");
                        pointStringBuilder.Append(point.Y);
                        pointStringBuilder.Append(",");
                    }
                    command.Parameters.Add("ShotLocations", OleDbType.LongVarWChar).Value = pointStringBuilder.ToString();

                    StringBuilder scaleStringBuilder = new StringBuilder();
                    scaleStringBuilder.Append(imageData.scale.horizontal.X);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.horizontal.Y);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.middle.X);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.middle.Y);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.vertical.X);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.vertical.Y);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.horizontalLength);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.verticalLength);
                    setStringParameter(command, "Scale", scaleStringBuilder.ToString());

                    StringBuilder ROIStringBuilder = new StringBuilder();
                    ROIStringBuilder.Append(imageData.regionOfInterest.topLeft.X);
                    ROIStringBuilder.Append(",");
                    ROIStringBuilder.Append(imageData.regionOfInterest.topLeft.Y);
                    ROIStringBuilder.Append(",");
                    ROIStringBuilder.Append(imageData.regionOfInterest.bottomRight.X);
                    ROIStringBuilder.Append(",");
                    ROIStringBuilder.Append(imageData.regionOfInterest.bottomRight.Y);
                    setStringParameter(command, "ROI", ROIStringBuilder.ToString());

                    try {
                        command.ExecuteNonQuery();
                    } catch (InvalidOperationException e) {
                        Log.LogError("Could not perform INSERT INTO Target.", "SQL", command.CommandText);
                        throw e;
                    }
                }

                using (OleDbCommand command = new OleDbCommand("SELECT MAX(TargetID) FROM Target", conn)) {
                    using (OleDbDataReader reader = command.ExecuteReader()) {
                        if (reader.Read()) {
                            targetID = reader.GetInt32(0);
                        } else {
                            Log.LogError("Unable to get the maximum TargetID.", "SQL", command.CommandText);
                        }
                    }
                }
            }

            return targetID;
        }
コード例 #19
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
 /// <summary>
 /// Returns true if the given <c>caliberID</c> is in the database.
 /// </summary>
 /// <param name="caliberID">The <c>caliberID</c> to find.</param>
 /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
 /// <returns>True if the given <c>caliberID</c> is in the database.</returns>
 private static Boolean CaliberIDExists(int caliberID, ConfigReader configReader)
 {
     using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
         openConnection(conn);
         using (OleDbCommand command = new OleDbCommand()) {
             command.Connection = conn;
             command.CommandText = "SELECT CaliberID FROM Caliber WHERE CaliberID = ?";
             command.Prepare();
             command.Parameters.Add("CaliberID", OleDbType.Integer).Value = caliberID;
             using (OleDbDataReader reader = command.ExecuteReader()) {
                 return reader.HasRows;
             }
         }
     }
 }
コード例 #20
0
 /// <summary>
 /// 返回DataTable
 /// </summary>
 /// <param name="sql">要执行的sql语句</param>
 /// <param name="values">sql语句中的参数</param>
 /// <returns></returns>
 public static DataTable GetDataTable(string sql, params OleDbParameter[] values)
 {
     using (OleDbConnection connection = new OleDbConnection(connectionString))
     {
         OleDbCommand cmd = new OleDbCommand(sql, connection);
         cmd.Prepare();
         cmd.Parameters.AddRange(values);
         using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
         {
             DataTable dt = new DataTable();
             try
             {
                 da.Fill(dt);
                 cmd.Parameters.Clear();
             }
             catch (OleDbException e)
             {
                 connection.Close();
                 throw new Exception(e.Message, e);
             }
             return dt;
         }
     }
 }
コード例 #21
0
 /// <summary>
 /// 返回查询结果的集合(只进只读,ExecuteReader)
 /// </summary>
 /// <param name="sql">要执行的sql语句</param>
 /// <param name="values">sql语句中的参数</param>
 /// <returns></returns>
 public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values)
 {
     OleDbConnection connection = new OleDbConnection(connectionString);
     OleDbCommand cmd = new OleDbCommand(sql, connection);
     try
     {
         connection.Open();
         cmd.Prepare();
         cmd.Parameters.AddRange(values);
         OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
         cmd.Parameters.Clear();
         return reader;
     }
     catch (OleDbException e)
     {
         connection.Close();
         throw new Exception(e.Message, e);
     }
 }
    protected void Button2_Click(object sender, EventArgs e)
    { //ALTERAR UM PROFESSOR
        Label4.Visible = false;
        Label5.Visible = false;

        String paramCodigo = "";
        String paramNome = "";
        String paramSexo = "";
        String paramSalario = "";
        try
        {
            paramCodigo = Request["TextBox1"];
            paramNome = Request["TextBox2"];
            paramSexo = Request["TextBox3"];
            paramSalario = Request["TextBox4"];
            if (paramCodigo == null) paramCodigo = "";
            if (paramNome == null) paramNome = "";
            if (paramSexo == null) paramSexo = "";
            if (paramSalario == null) paramSalario = "";
        }
        catch (Exception) { };

        try
        {
            conexao = new OleDbConnection(
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/temp/Escola.mdb");
            conexao.Open();

            int qtde;
            
            /*
            //Usando o método tradicional:
            string sql = "UPDATE professores SET nomeprof='" + paramNome 
                + "', sexo='" + paramSexo + "', salario=" 
                + paramSalario + " WHERE codprof=" + paramCodigo;
            stm = new OleDbCommand(sql, conexao);   
            */

            //Usando prepared statements:
            string sql = "UPDATE professores SET nomeprof=? , sexo=?, salario=? WHERE codprof=?";
            stm = new OleDbCommand(sql, conexao);
            stm.Parameters.Add("nomp", OleDbType.VarChar, 100); //O método OleDbCommand.Prepare exige que todos os parâmetros de comprimento variável tenham um valor Size não nulo explicitamente definido. 
            stm.Parameters.Add("sexp", OleDbType.VarChar, 1); 
            stm.Parameters.Add("salp", OleDbType.Single);
            stm.Parameters.Add("codp", OleDbType.Integer);
            stm.Parameters[0].Value = paramNome;
            stm.Parameters[1].Value = paramSexo;
            stm.Parameters[2].Value = Convert.ToSingle(paramSalario);
            stm.Parameters[3].Value = Convert.ToInt16(paramCodigo);
            stm.Prepare();
            
            qtde = stm.ExecuteNonQuery();

            if (qtde == 0)
            {
                Label4.Visible = false;
                Label5.Text = "Naõ foi possível alterar o professor com código: " + paramCodigo + ".";
                Label5.Visible = true; //erro
            }
            else
            {
                Label4.Text = "Registro alterado com sucesso: professor código " + paramCodigo + ".";
                Label4.Visible = true;  //inserido com sucesso
                Label5.Visible = false;
            }

            stm.Dispose();
            conexao.Close();
        }
        catch (Exception exc)
        {
            Label4.Visible = false;
            Label5.Visible = true; //erro
            Label5.Text = exc.Message;
        }


    }
コード例 #23
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
 /// <summary>
 /// Loads a <c>CaliberUnit</c> from the database.
 /// </summary>
 /// <param name="caliberID">The <paramref name="caliberID"/> of the <c>CaliberUnit</c>.</param>
 /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
 /// <returns>The <c>CaliberUnit</c> from the database.</returns>
 /// <exception cref="OleDbException">A connection-level error occurred while opening the connection.</exception>
 private static Datatype.CaliberUnit GetCaliberUnit(int caliberID, ConfigReader configReader)
 {
     using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
         openConnection(conn);
         using (OleDbCommand command = new OleDbCommand()) {
             command.Connection = conn;
             command.CommandText = "SELECT * FROM Caliber WHERE CaliberID = ?";
             command.Prepare();
             command.Parameters.Add("CaliberID", OleDbType.Integer).Value = caliberID;
             using (OleDbDataReader reader = command.ExecuteReader()) {
                 if (reader.Read()) {
                     // We could trust that the columns are index 0, 1, and 2,
                     // but let's use the specific column name for safety.
                     int caliberUnitID = reader.GetInt32(reader.GetOrdinal("CaliberID"));
                     String unitName = reader.GetString(reader.GetOrdinal("UnitName"));
                     double unitsPerInch = reader.GetDouble(reader.GetOrdinal("UnitsPerInch"));
                     if (caliberID != caliberUnitID) {
                         Log.LogError("IDs do not match in GetCaliberUnit().", "caliberID", caliberID.ToString(),
                             "caliberUnitID", caliberUnitID.ToString());
                     }
                     return new Datatype.CaliberUnit(caliberUnitID, unitName, unitsPerInch);
                 }
             }
         }
     }
     Log.LogError("Caliber unit not found in database.", "unitID", caliberID.ToString());
     throw new Exception("Error in GetCaliberUnit()");
 }
コード例 #24
0
    //this method returns credentials for a user
    public static Credentials getUserCredentials(string username)
    {
        Credentials userCredentials = null;
        String database = System.Configuration.ConfigurationManager.ConnectionStrings["programaholics_anonymous_databaseConnectionString"].ConnectionString;
        using (OleDbConnection sqlConn = new OleDbConnection("PROVIDER=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + database))
        {
            try
            {
                sqlConn.Open();
                String select = "SELECT [users].username AS DBusername, [users].accessLevel AS DBAccessLevel, [password].password AS DBPassword, [password].salt AS DBSalt  FROM [users] INNER JOIN [password] on [password].ID = [users].password WHERE [users].username = @userName ORDER BY [users].username";
                OleDbCommand cmd = new OleDbCommand(select, sqlConn);
                cmd.Parameters.Add("userName", OleDbType.VarChar, 255).Value = username;

                cmd.Prepare();

                OleDbDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    String dbusername = dr["DBusername"].ToString();
                    String dbaccessLevel = dr["DBAccessLevel"].ToString();
                    String dbpassword = dr["DBPassword"].ToString();
                    String dbsalt = dr["DBSalt"].ToString();

                    userCredentials = new Credentials(dbusername, dbpassword, dbsalt, dbaccessLevel);
                }

            }

            catch (OleDbException ex)
            {
                userCredentials = null;
            }
            finally
            {
                sqlConn.Close();

            }

            return userCredentials;
        }
    }
コード例 #25
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
        /// <summary>
        /// Updates an existing <c>ImageData</c> object in the <c>Target</c> table.
        /// </summary>
        /// <param name="imageData">The <c>ImageData</c> to update.</param>
        /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
        /// <exception cref="OleDbException">A connection-level error occurred while opening the connection.</exception>
        /// <exception cref="InvalidOperationException">The UPDATE Target command failed.</exception>
        /// <exception cref="ArgumentException">The caliber or weapon name was not found in the database.</exception>
        /// <returns>The <c>targetID</c> of the updated record.</returns>
        private static int UpdateData(Datatype.ImageData imageData, ConfigReader configReader)
        {
            using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
                openConnection(conn);

                if (!CaliberIDExists(imageData.caliber.caliberUnitID, configReader)) {
                    Log.LogError("Caliber to insert not found in database.", "unitID",
                        imageData.caliber.caliberUnitID.ToString(), "unitName", imageData.caliber.unitName,
                        "unitsPerInch", imageData.caliber.unitsPerInch.ToString());
                    throw new ArgumentException("Caliber to insert does not exist.", "caliber");
                }
                if (!WeaponExists(imageData.weaponName, configReader)) {
                    Log.LogError("Weapon name to insert not found in database.", "weaponName", imageData.weaponName);
                    throw new ArgumentException("Weapon name to insert not found in database.", "weaponName");
                }

                using (OleDbCommand command = new OleDbCommand()) {
                    command.Connection = conn;
                    command.CommandText = "UPDATE Target SET OrigFilename = ?, ReportFilename = ?, DateTimeFired = ?, DateTimeProcessed = ?, ShooterLName = ?, ShooterFName = ?, RangeLocation = ?, DistanceUnits = ?, Distance = ?, Temperature = ?, WeaponName = ?, SerialNumber = ?, WeaponNotes = ?, CaliberID = ?, CaliberValue = ?, LotNumber = ?, ProjectileMassGrains = ?, AmmunitionNotes = ?, ShotsFired = ?, ShotLocations = ?, Scale = ?, ROI = ? WHERE TargetID = ?";
                    command.Prepare();
                    setStringParameter(command, "OrigFilename", imageData.origFilename);
                    setStringParameter(command, "ReportFilename", imageData.reportFilename);
                    command.Parameters.Add("DateTimeFired", OleDbType.Date).Value = imageData.dateTimeFired;
                    command.Parameters.Add("DateTimeProcessed", OleDbType.Date).Value = DateTime.Now;
                    setStringParameter(command, "ShooterLName", imageData.shooterLName);
                    setStringParameter(command, "ShooterFName", imageData.shooterFName);
                    setStringParameter(command, "RangeLocation", imageData.rangeLocation);
                    command.Parameters.Add("DistanceUnits", OleDbType.UnsignedTinyInt).Value = imageData.distanceUnits;
                    command.Parameters.Add("Distance", OleDbType.Integer).Value = imageData.distance;
                    command.Parameters.Add("Temperature", OleDbType.UnsignedTinyInt).Value = imageData.temperature;
                    setStringParameter(command, "WeaponName", imageData.weaponName);
                    setStringParameter(command, "SerialNumber", imageData.serialNumber);
                    command.Parameters.Add("WeaponNotes", OleDbType.LongVarWChar).Value = imageData.weaponNotes;
                    command.Parameters.Add("CaliberID", OleDbType.Integer).Value = imageData.caliber.caliberUnitID;
                    command.Parameters.Add("CaliberValue", OleDbType.Double).Value = imageData.caliberValue;
                    setStringParameter(command, "LotNumber", imageData.lotNumber);
                    command.Parameters.Add("ProjectileMassGrains", OleDbType.Integer).Value = imageData.projectileMassGrains;
                    command.Parameters.Add("AmmunitionNotes", OleDbType.LongVarWChar).Value = imageData.ammunitionNotes;
                    command.Parameters.Add("ShotsFired", OleDbType.Integer).Value = imageData.shotsFired;

                    StringBuilder pointStringBuilder = new StringBuilder();
                    foreach (Point point in imageData.points) {
                        pointStringBuilder.Append(point.X);
                        pointStringBuilder.Append(",");
                        pointStringBuilder.Append(point.Y);
                        pointStringBuilder.Append(",");
                    }
                    command.Parameters.Add("ShotLocations", OleDbType.LongVarWChar).Value = pointStringBuilder.ToString();

                    StringBuilder scaleStringBuilder = new StringBuilder();
                    scaleStringBuilder.Append(imageData.scale.horizontal.X);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.horizontal.Y);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.middle.X);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.middle.Y);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.vertical.X);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.vertical.Y);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.horizontalLength);
                    scaleStringBuilder.Append(",");
                    scaleStringBuilder.Append(imageData.scale.verticalLength);
                    setStringParameter(command, "Scale", scaleStringBuilder.ToString());

                    StringBuilder ROIStringBuilder = new StringBuilder();
                    ROIStringBuilder.Append(imageData.regionOfInterest.topLeft.X);
                    ROIStringBuilder.Append(",");
                    ROIStringBuilder.Append(imageData.regionOfInterest.topLeft.Y);
                    ROIStringBuilder.Append(",");
                    ROIStringBuilder.Append(imageData.regionOfInterest.bottomRight.X);
                    ROIStringBuilder.Append(",");
                    ROIStringBuilder.Append(imageData.regionOfInterest.bottomRight.Y);
                    setStringParameter(command, "ROI", ROIStringBuilder.ToString());

                    command.Parameters.Add("TargetID", OleDbType.Integer).Value = imageData.targetID;

                    try {
                        command.ExecuteNonQuery();
                    } catch (InvalidOperationException e) {
                        Log.LogError("Could not perform UPDATE Target.", "SQL", command.CommandText);
                        throw e;
                    }
                }
            }

            return imageData.targetID;
        }
コード例 #26
0
ファイル: WSCFieldExport.cs プロジェクト: jwebb-vtg/WSCIEMP
        public static void PopulatePerformanceTable(int cropYear)
        {
            try {

                // Pull data
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) {

                    using (SqlDataReader dr = WSCField.FieldPerformanceGet(conn, cropYear, 0, 0)) {

                        using (OLEDB.OleDbConnection msConn =
                                  new OLEDB.OleDbConnection(Globals.BeetExportConnectionString())) {

                            string qry = "FieldPerformanceInsert";

                            if (msConn.State != System.Data.ConnectionState.Open) { msConn.Open(); }
                            using (OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(qry, msConn)) {

                                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                                cmd.Parameters.Add("pSHID", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pCropYear", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldName", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pContractNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldStateName", OLEDB.OleDbType.VarChar, 2);
                                cmd.Parameters.Add("pFieldCountyName", OLEDB.OleDbType.VarChar, 20);
                                cmd.Parameters.Add("pAreaName", OLEDB.OleDbType.VarChar, 60);
                                cmd.Parameters.Add("pRegionName", OLEDB.OleDbType.VarChar, 60);
                                cmd.Parameters.Add("pAcresContracted", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAcresHarvested", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pSugarPct", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pSugarPct"].Precision = 18;
                                cmd.Parameters["pSugarPct"].Scale = 2;
                                cmd.Parameters.Add("pExtractableSugar", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pExtractableSugar"].Precision = 18;
                                cmd.Parameters["pExtractableSugar"].Scale = 4;
                                cmd.Parameters.Add("pTonsPerAcre", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pTonsPerAcre"].Precision = 18;
                                cmd.Parameters["pTonsPerAcre"].Scale = 4;
                                cmd.Parameters.Add("pGrossDollarsPerAcre", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pGrossDollarsPerAcre"].Precision = 18;
                                cmd.Parameters["pGrossDollarsPerAcre"].Scale = 4;
                                cmd.Parameters.Add("pSLMPct", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pSLMPct"].Precision = 18;
                                cmd.Parameters["pSLMPct"].Scale = 4;
                                cmd.Parameters.Add("pTarePct", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pTarePct"].Precision = 18;
                                cmd.Parameters["pTarePct"].Scale = 4;
                                cmd.Parameters.Add("pTotalAvailableNitrogen", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pTotalAvailableNitrogen"].Precision = 18;
                                cmd.Parameters["pTotalAvailableNitrogen"].Scale = 4;
                                cmd.Parameters.Add("pTotalAvailableNitrogenGroup", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountyCount", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaCount", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionCount", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopCount", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountySugarPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountyExtractableSugarRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountyTonsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountyGrossDollarsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountySlmPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCountyTarePctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaSugarPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaExtractableSugarRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaTonsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaGrossDollarsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaSlmPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAreaTarePctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionSugarPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionExtractableSugarRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionTonsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionGrossDollarsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionSlmPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pRegionTarePctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopSugarPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopExtractableSugarRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopTonsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopGrossDollarsPerAcreRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopSlmPctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pCoopTarePctRank", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pBeetsPerAcre", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pAvgTopping", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pAvgTopping"].Precision = 18;
                                cmd.Parameters["pAvgTopping"].Scale = 2;

                                cmd.Prepare();

                                while (dr.Read()) {

                                    cmd.Parameters["pSHID"].Value = dr.GetString(dr.GetOrdinal("SHID"));
                                    cmd.Parameters["pCropYear"].Value = dr.GetInt32(dr.GetOrdinal("CropYear"));
                                    cmd.Parameters["pFieldName"].Value = dr.GetString(dr.GetOrdinal("FieldName"));
                                    cmd.Parameters["pContractNo"].Value = dr.GetInt32(dr.GetOrdinal("ContractNo"));
                                    cmd.Parameters["pFieldStateName"].Value = dr.GetString(dr.GetOrdinal("FieldStateName"));
                                    cmd.Parameters["pFieldCountyName"].Value = dr.GetString(dr.GetOrdinal("FieldCountyName"));
                                    cmd.Parameters["pAreaName"].Value = dr.GetString(dr.GetOrdinal("AreaName"));
                                    cmd.Parameters["pRegionName"].Value = dr.GetString(dr.GetOrdinal("RegionName"));
                                    cmd.Parameters["pAcresContracted"].Value = dr.GetInt32(dr.GetOrdinal("AcresContracted"));
                                    cmd.Parameters["pAcresHarvested"].Value = dr.GetInt32(dr.GetOrdinal("AcresHarvested"));
                                    cmd.Parameters["pSugarPct"].Value = dr.GetDecimal(dr.GetOrdinal("SugarPct"));
                                    cmd.Parameters["pExtractableSugar"].Value = dr.GetDecimal(dr.GetOrdinal("ExtractableSugar"));
                                    cmd.Parameters["pTonsPerAcre"].Value = dr.GetDecimal(dr.GetOrdinal("TonsPerAcre"));
                                    cmd.Parameters["pGrossDollarsPerAcre"].Value = dr.GetDecimal(dr.GetOrdinal("GrossDollarsPerAcre"));
                                    cmd.Parameters["pSLMPct"].Value = dr.GetDecimal(dr.GetOrdinal("SLMPct"));
                                    cmd.Parameters["pTarePct"].Value = dr.GetDecimal(dr.GetOrdinal("TarePct"));
                                    cmd.Parameters["pTotalAvailableNitrogen"].Value = dr.GetDecimal(dr.GetOrdinal("TotalAvailableNitrogen"));
                                    cmd.Parameters["pTotalAvailableNitrogenGroup"].Value = dr.GetInt32(dr.GetOrdinal("TotalAvailableNitrogenGroup"));
                                    cmd.Parameters["pCountyCount"].Value = dr.GetInt32(dr.GetOrdinal("CountyCount"));
                                    cmd.Parameters["pAreaCount"].Value = dr.GetInt32(dr.GetOrdinal("AreaCount"));
                                    cmd.Parameters["pRegionCount"].Value = dr.GetInt32(dr.GetOrdinal("RegionCount"));
                                    cmd.Parameters["pCoopCount"].Value = dr.GetInt32(dr.GetOrdinal("CoopCount"));
                                    cmd.Parameters["pCountySugarPctRank"].Value = dr.GetInt32(dr.GetOrdinal("CountySugarPctRank"));
                                    cmd.Parameters["pCountyExtractableSugarRank"].Value = dr.GetInt32(dr.GetOrdinal("CountyExtractableSugarRank"));
                                    cmd.Parameters["pCountyTonsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("CountyTonsPerAcreRank"));
                                    cmd.Parameters["pCountyGrossDollarsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("CountyGrossDollarsPerAcreRank"));
                                    cmd.Parameters["pCountySlmPctRank"].Value = dr.GetInt32(dr.GetOrdinal("CountySlmPctRank"));
                                    cmd.Parameters["pCountyTarePctRank"].Value = dr.GetInt32(dr.GetOrdinal("CountyTarePctRank"));
                                    cmd.Parameters["pAreaSugarPctRank"].Value = dr.GetInt32(dr.GetOrdinal("AreaSugarPctRank"));
                                    cmd.Parameters["pAreaExtractableSugarRank"].Value = dr.GetInt32(dr.GetOrdinal("AreaExtractableSugarRank"));
                                    cmd.Parameters["pAreaTonsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("AreaTonsPerAcreRank"));
                                    cmd.Parameters["pAreaGrossDollarsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("AreaGrossDollarsPerAcreRank"));
                                    cmd.Parameters["pAreaSlmPctRank"].Value = dr.GetInt32(dr.GetOrdinal("AreaSlmPctRank"));
                                    cmd.Parameters["pAreaTarePctRank"].Value = dr.GetInt32(dr.GetOrdinal("AreaTarePctRank"));
                                    cmd.Parameters["pRegionSugarPctRank"].Value = dr.GetInt32(dr.GetOrdinal("RegionSugarPctRank"));
                                    cmd.Parameters["pRegionExtractableSugarRank"].Value = dr.GetInt32(dr.GetOrdinal("RegionExtractableSugarRank"));
                                    cmd.Parameters["pRegionTonsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("RegionTonsPerAcreRank"));
                                    cmd.Parameters["pRegionGrossDollarsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("RegionGrossDollarsPerAcreRank"));
                                    cmd.Parameters["pRegionSlmPctRank"].Value = dr.GetInt32(dr.GetOrdinal("RegionSlmPctRank"));
                                    cmd.Parameters["pRegionTarePctRank"].Value = dr.GetInt32(dr.GetOrdinal("RegionTarePctRank"));
                                    cmd.Parameters["pCoopSugarPctRank"].Value = dr.GetInt32(dr.GetOrdinal("CoopSugarPctRank"));
                                    cmd.Parameters["pCoopExtractableSugarRank"].Value = dr.GetInt32(dr.GetOrdinal("CoopExtractableSugarRank"));
                                    cmd.Parameters["pCoopTonsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("CoopTonsPerAcreRank"));
                                    cmd.Parameters["pCoopGrossDollarsPerAcreRank"].Value = dr.GetInt32(dr.GetOrdinal("CoopGrossDollarsPerAcreRank"));
                                    cmd.Parameters["pCoopSlmPctRank"].Value = dr.GetInt32(dr.GetOrdinal("CoopSlmPctRank"));
                                    cmd.Parameters["pCoopTarePctRank"].Value = dr.GetInt32(dr.GetOrdinal("CoopTarePctRank"));
                                    cmd.Parameters["pBeetsPerAcre"].Value = dr.GetInt32(dr.GetOrdinal("BeetsPerAcre"));
                                    cmd.Parameters["pAvgTopping"].Value = dr.GetDecimal(dr.GetOrdinal("AvgTopping"));

                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex) {
                string errMsg = MOD_NAME + "PopulatePerformanceTable";
                WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, ex);
                throw (wscEx);
            }
        }
コード例 #27
0
ファイル: MSAccess.cs プロジェクト: siegleal/iSanta
 /// <summary>
 /// Returns true if the given <c>weaponName</c> is in the database.
 /// </summary>
 /// <param name="caliberID">The <c>weaponName</c> to find.</param>
 /// <param name="configReader">The <c>ConfigReader</c> with the path to the database.</param>
 /// <returns>True if the given <c>weaponName</c> is in the database.</returns>
 private static Boolean WeaponExists(String weaponName, ConfigReader configReader)
 {
     using (OleDbConnection conn = new OleDbConnection(configReader.getValue("Database Location"))) {
         openConnection(conn);
         using (OleDbCommand command = new OleDbCommand()) {
             command.Connection = conn;
             command.CommandText = "SELECT WeaponName FROM Weapon WHERE WeaponName = ?";
             command.Prepare();
             setStringParameter(command, "WeaponName", weaponName);
             using (OleDbDataReader reader = command.ExecuteReader()) {
                 return reader.HasRows;
             }
         }
     }
 }
コード例 #28
0
ファイル: WSCFieldExport.cs プロジェクト: jwebb-vtg/WSCIEMP
        public static void PopulateAgronomyTable(int cropYear)
        {
            try {

                // Pull data
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BeetConn"].ToString())) {
                    using (SqlDataReader dr = FieldAgronomyGetExport(conn, cropYear)) {

                        using (OLEDB.OleDbConnection msConn =
                                  new OLEDB.OleDbConnection(Globals.BeetExportConnectionString())) {

                            string qry = "FieldAgronomyInsert";

                            if (msConn.State != System.Data.ConnectionState.Open) { msConn.Open(); }
                            using (OLEDB.OleDbCommand cmd = new OLEDB.OleDbCommand(qry, msConn)) {

                                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                                cmd.Parameters.Add("pSHID", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pCropYear", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pContractNumber", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldSequenceNo", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldSeedVariety", OLEDB.OleDbType.VarChar, 16);
                                cmd.Parameters.Add("pFieldSeedPrimed", OLEDB.OleDbType.VarChar, 12);
                                cmd.Parameters.Add("pFieldSeedRxChemical", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldRowSpacing", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldPlantingDate", OLEDB.OleDbType.DBDate, 0);
                                cmd.Parameters.Add("pFieldPlantSpacing", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldPlantSpacing"].Precision = 18;
                                cmd.Parameters["pFieldPlantSpacing"].Scale = 2;
                                cmd.Parameters.Add("pFieldRePlantingDate", OLEDB.OleDbType.DBDate, 0);
                                cmd.Parameters.Add("pFieldReplantSeedVariety", OLEDB.OleDbType.VarChar, 16);
                                cmd.Parameters.Add("pFieldAcresReplanted", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldReplantReason", OLEDB.OleDbType.VarChar, 60);
                                cmd.Parameters.Add("pFieldTestSeason", OLEDB.OleDbType.VarChar, 6);
                                cmd.Parameters.Add("pFieldTestDepth", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldTest_N", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldTest_N"].Precision = 18;
                                cmd.Parameters["pFieldTest_N"].Scale = 2;
                                cmd.Parameters.Add("pFieldTest_P", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldTest_K", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldTest_K"].Precision = 18;
                                cmd.Parameters["pFieldTest_K"].Scale = 2;
                                cmd.Parameters.Add("pFieldTest_pH", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldTest_pH"].Precision = 18;
                                cmd.Parameters["pFieldTest_pH"].Scale = 2;
                                cmd.Parameters.Add("pFieldTest_OrgMat", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldTest_OrgMat"].Precision = 18;
                                cmd.Parameters["pFieldTest_OrgMat"].Scale = 2;
                                cmd.Parameters.Add("pFieldLastYrManure", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldFertFall_N", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertFall_N"].Precision = 18;
                                cmd.Parameters["pFieldFertFall_N"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertFall_P", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertFall_P"].Precision = 18;
                                cmd.Parameters["pFieldFertFall_P"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertFall_K", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertFall_K"].Precision = 18;
                                cmd.Parameters["pFieldFertFall_K"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertSpring_N", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertSpring_N"].Precision = 18;
                                cmd.Parameters["pFieldFertSpring_N"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertSpring_P", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertSpring_P"].Precision = 18;
                                cmd.Parameters["pFieldFertSpring_P"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertSpring_K", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertSpring_K"].Precision = 18;
                                cmd.Parameters["pFieldFertSpring_K"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertInSeason_N", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertInSeason_N"].Precision = 18;
                                cmd.Parameters["pFieldFertInSeason_N"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertInSeason_P", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertInSeason_P"].Precision = 18;
                                cmd.Parameters["pFieldFertInSeason_P"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertInSeason_K", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldFertInSeason_K"].Precision = 18;
                                cmd.Parameters["pFieldFertInSeason_K"].Scale = 2;
                                cmd.Parameters.Add("pFieldFertStarter", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldPreInsecticide", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldPostInsecticide", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldPreWeedControl", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldLaybyHerbicide", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldLaybyChemical", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldRootMaggotInsecticide", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldCounterLbs", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldCounterLbs"].Precision = 18;
                                cmd.Parameters["pFieldCounterLbs"].Scale = 2;
                                cmd.Parameters.Add("pFieldTemikLbs", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldTemikLbs"].Precision = 18;
                                cmd.Parameters["pFieldTemikLbs"].Scale = 2;
                                cmd.Parameters.Add("pFieldThimetLbs", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldThimetLbs"].Precision = 18;
                                cmd.Parameters["pFieldThimetLbs"].Scale = 2;
                                cmd.Parameters.Add("pFieldCercspApp1Chem", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldCercosporaApp1Date", OLEDB.OleDbType.DBDate, 20);
                                cmd.Parameters.Add("pFieldCercspApp2Chem", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldCercosporaApp2Date", OLEDB.OleDbType.DBDate, 20);
                                cmd.Parameters.Add("pFieldCercspApp3Chem", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldCercosporaApp3Date", OLEDB.OleDbType.DBDate, 20);
                                cmd.Parameters.Add("pFieldHailStress", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldWeedControl", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldTreatedPowderyMildew", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldTreatedNematode", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldTreatedRhizoctonia", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldAgronomyReviewed", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldAgronomyInclude", OLEDB.OleDbType.Boolean, 0);
                                // Added 2/2007: changed 2/2008
                                cmd.Parameters.Add("pLandOwner", OLEDB.OleDbType.VarChar, 30);
                                cmd.Parameters.Add("pFieldPostAphanomyces", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostCercospora", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostCurlyTop", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostFusarium", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostNematode", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostPowderyMildew", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostRhizoctonia", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostRhizomania", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostRootAphid", OLEDB.OleDbType.VarChar, 10);
                                cmd.Parameters.Add("pFieldPostWater", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldGridZone", OLEDB.OleDbType.Boolean, 0);
                                // Added 2/2008
                                cmd.Parameters.Add("pFieldOwnership", OLEDB.OleDbType.VarChar, 15);
                                cmd.Parameters.Add("pFieldTillage", OLEDB.OleDbType.VarChar, 30);

                                cmd.Parameters.Add("pFieldSoilTexture", OLEDB.OleDbType.VarChar, 15);
                                cmd.Parameters.Add("pFieldTestSalts", OLEDB.OleDbType.Decimal, 0);
                                cmd.Parameters["pFieldTestSalts"].Precision = 18;
                                cmd.Parameters["pFieldTestSalts"].Scale = 4;
                                cmd.Parameters.Add("pFieldHerbicideRxCount", OLEDB.OleDbType.Integer, 0);
                                cmd.Parameters.Add("pFieldEmerg80Date", OLEDB.OleDbType.DBDate, 0);
                                cmd.Parameters.Add("pFieldSeedRxPoncho", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSeedRxTachigaren", OLEDB.OleDbType.Boolean, 0);
                                cmd.Parameters.Add("pFieldSeedRxCruiser", OLEDB.OleDbType.Boolean, 0);

                                cmd.Prepare();

                                string tmp = "";
                                while (dr.Read()) {

                                    cmd.Parameters["pSHID"].Value = dr.GetString(dr.GetOrdinal("SHID"));
                                    cmd.Parameters["pCropYear"].Value = dr.GetInt32(dr.GetOrdinal("CropYear"));
                                    cmd.Parameters["pContractNumber"].Value = Convert.ToInt32(dr.GetString(dr.GetOrdinal("ContractNumber")));
                                    cmd.Parameters["pFieldSequenceNo"].Value = dr.GetInt16(dr.GetOrdinal("FieldSequenceNo"));
                                    cmd.Parameters["pFieldSeedVariety"].Value = dr.GetString(dr.GetOrdinal("FieldSeedVariety"));
                                    cmd.Parameters["pFieldSeedPrimed"].Value = dr.GetString(dr.GetOrdinal("FieldSeedPrimed"));
                                    cmd.Parameters["pFieldSeedRxChemical"].Value = dr.GetString(dr.GetOrdinal("FieldSeedRxChemical"));
                                    cmd.Parameters["pFieldRowSpacing"].Value = dr.GetInt16(dr.GetOrdinal("FieldRowSpacing"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPlantingDate"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPlantingDate"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPlantingDate"].Value = tmp;
                                    }

                                    cmd.Parameters["pFieldPlantSpacing"].Value = dr.GetDecimal(dr.GetOrdinal("FieldPlantSpacing"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldRePlantingDate"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldRePlantingDate"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldRePlantingDate"].Value = tmp;
                                    }
                                    cmd.Parameters["pFieldReplantSeedVariety"].Value = dr.GetString(dr.GetOrdinal("FieldReplantSeedVariety"));
                                    cmd.Parameters["pFieldAcresReplanted"].Value = dr.GetInt32(dr.GetOrdinal("FieldAcresReplanted"));
                                    cmd.Parameters["pFieldReplantReason"].Value = dr.GetString(dr.GetOrdinal("FieldReplantReason"));
                                    cmd.Parameters["pFieldTestSeason"].Value = dr.GetString(dr.GetOrdinal("FieldTestSeason"));
                                    cmd.Parameters["pFieldTestDepth"].Value = dr.GetInt16(dr.GetOrdinal("FieldTestDepth"));
                                    cmd.Parameters["pFieldTest_N"].Value = dr.GetDecimal(dr.GetOrdinal("FieldTest_N"));
                                    cmd.Parameters["pFieldTest_P"].Value = dr.GetString(dr.GetOrdinal("FieldTest_P"));
                                    cmd.Parameters["pFieldTest_K"].Value = dr.GetDecimal(dr.GetOrdinal("FieldTest_K"));
                                    cmd.Parameters["pFieldTest_pH"].Value = dr.GetDecimal(dr.GetOrdinal("FieldTest_pH"));
                                    cmd.Parameters["pFieldTest_OrgMat"].Value = dr.GetDecimal(dr.GetOrdinal("FieldTest_OrgMat"));
                                    cmd.Parameters["pFieldLastYrManure"].Value = dr.GetInt32(dr.GetOrdinal("FieldLastYrManure"));
                                    cmd.Parameters["pFieldFertFall_N"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertFall_N"));
                                    cmd.Parameters["pFieldFertFall_P"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertFall_P"));
                                    cmd.Parameters["pFieldFertFall_K"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertFall_K"));
                                    cmd.Parameters["pFieldFertSpring_N"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertSpring_N"));
                                    cmd.Parameters["pFieldFertSpring_P"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertSpring_P"));
                                    cmd.Parameters["pFieldFertSpring_K"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertSpring_K"));
                                    cmd.Parameters["pFieldFertInSeason_N"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertInSeason_N"));
                                    cmd.Parameters["pFieldFertInSeason_P"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertInSeason_P"));
                                    cmd.Parameters["pFieldFertInSeason_K"].Value = dr.GetDecimal(dr.GetOrdinal("FieldFertInSeason_K"));
                                    cmd.Parameters["pFieldFertStarter"].Value = (dr.GetString(dr.GetOrdinal("FieldFertStarter")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldPreInsecticide"].Value = (dr.GetString(dr.GetOrdinal("FieldPreInsecticide")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldPostInsecticide"].Value = (dr.GetString(dr.GetOrdinal("FieldPostInsecticide")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldPreWeedControl"].Value = (dr.GetString(dr.GetOrdinal("FieldPreWeedControl")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldLaybyHerbicide"].Value = (dr.GetString(dr.GetOrdinal("FieldLaybyHerbicide")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldLaybyChemical"].Value = dr.GetString(dr.GetOrdinal("FieldLaybyChemical"));
                                    cmd.Parameters["pFieldRootMaggotInsecticide"].Value = (dr.GetString(dr.GetOrdinal("FieldRootMaggotInsecticide")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldCounterLbs"].Value = dr.GetDecimal(dr.GetOrdinal("FieldCounterLbs"));
                                    cmd.Parameters["pFieldTemikLbs"].Value = dr.GetDecimal(dr.GetOrdinal("FieldTemikLbs"));
                                    cmd.Parameters["pFieldThimetLbs"].Value = dr.GetDecimal(dr.GetOrdinal("FieldThimetLbs"));
                                    cmd.Parameters["pFieldCercspApp1Chem"].Value = dr.GetString(dr.GetOrdinal("FieldCercspApp1Chem"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldCercosporaApp1Date"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldCercosporaApp1Date"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldCercosporaApp1Date"].Value = tmp;
                                    }
                                    cmd.Parameters["pFieldCercspApp2Chem"].Value = dr.GetString(dr.GetOrdinal("FieldCercspApp2Chem"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldCercosporaApp2Date"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldCercosporaApp2Date"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldCercosporaApp2Date"].Value = tmp;
                                    }
                                    cmd.Parameters["pFieldCercspApp3Chem"].Value = dr.GetString(dr.GetOrdinal("FieldCercspApp3Chem"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldCercosporaApp3Date"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldCercosporaApp3Date"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldCercosporaApp3Date"].Value = tmp;
                                    }
                                    cmd.Parameters["pFieldHailStress"].Value = dr.GetString(dr.GetOrdinal("FieldHailStress"));
                                    cmd.Parameters["pFieldWeedControl"].Value = dr.GetString(dr.GetOrdinal("FieldWeedControl"));
                                    cmd.Parameters["pFieldTreatedPowderyMildew"].Value = (dr.GetString(dr.GetOrdinal("FieldTreatedPowderyMildew")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldTreatedNematode"].Value = (dr.GetString(dr.GetOrdinal("FieldTreatedNematode")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldTreatedRhizoctonia"].Value = (dr.GetString(dr.GetOrdinal("FieldTreatedRhizoctonia")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldAgronomyReviewed"].Value = (dr.GetString(dr.GetOrdinal("FieldAgronomyReviewed")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldAgronomyInclude"].Value = (dr.GetString(dr.GetOrdinal("FieldAgronomyInclude")) == "Y" ? true : false);
                                    // Added 2/2007
                                    cmd.Parameters["pLandOwner"].Value = dr.GetString(dr.GetOrdinal("LandOwner"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostAphanomyces"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostAphanomyces"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostAphanomyces"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostCercospora"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostCercospora"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostCercospora"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostCurlyTop"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostCurlyTop"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostCurlyTop"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostFusarium"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostFusarium"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostFusarium"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostNematode"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostNematode"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostNematode"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostPowderyMildew"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostPowderyMildew"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostPowderyMildew"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostRhizoctonia"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostRhizoctonia"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostRhizoctonia"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostRhizomania"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostRhizomania"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostRhizomania"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldPostRootAphid"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldPostRootAphid"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldPostRootAphid"].Value = tmp;
                                    }

                                    cmd.Parameters["pFieldPostWater"].Value = (dr.GetString(dr.GetOrdinal("FieldPostWater")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldGridZone"].Value = (dr.GetString(dr.GetOrdinal("FieldGridZone")) == "Y" ? true : false);

                                    tmp = dr.GetString(dr.GetOrdinal("FieldOwnership"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldOwnership"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldOwnership"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldTillage"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldTillage"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldTillage"].Value = tmp;
                                    }

                                    tmp = dr.GetString(dr.GetOrdinal("FieldSoilTexture"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldSoilTexture"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldSoilTexture"].Value = tmp;
                                    }

                                    cmd.Parameters["pFieldTestSalts"].Value = dr.GetDecimal(dr.GetOrdinal("FieldTestSalts"));
                                    cmd.Parameters["pFieldHerbicideRxCount"].Value = dr.GetInt32(dr.GetOrdinal("FieldHerbicideRxCount"));

                                    tmp = dr.GetString(dr.GetOrdinal("FieldEmerg80Date"));
                                    if (tmp.Length == 0) {
                                        cmd.Parameters["pFieldEmerg80Date"].Value = DBNull.Value;
                                    } else {
                                        cmd.Parameters["pFieldEmerg80Date"].Value = tmp;
                                    }

                                    cmd.Parameters["pFieldSeedRxPoncho"].Value = (dr.GetString(dr.GetOrdinal("FieldSeedRxPoncho")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSeedRxTachigaren"].Value = (dr.GetString(dr.GetOrdinal("FieldSeedRxTachigaren")) == "Y" ? true : false);
                                    cmd.Parameters["pFieldSeedRxCruiser"].Value = (dr.GetString(dr.GetOrdinal("FieldSeedRxCruiser")) == "Y" ? true : false);

                                    cmd.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex) {
                string errMsg = MOD_NAME + "PopulateAgronomyTable";
                WSCIEMP.Common.CException wscEx = new WSCIEMP.Common.CException(errMsg, ex);
                throw (wscEx);
            }
        }