C# OleDbCommand.Prepare方法代码示例

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

    }

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

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

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

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

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

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

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

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

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

		}

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

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

//.........这里部分代码省略.........
                                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"));