C# SqlDataRecord.SetValue方法代码示例

    public static void GetConnectedPublnSets()
    {

        using (var connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {

                SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
                new SqlMetaData("cluster", SqlDbType.Int),
                new SqlMetaData("new_id", SqlDbType.Int)
                    });

                SqlContext.Pipe.SendResultsStart(record);


                List<int> publnUT = new List<int>();
                List<int> pubMinPairIndex = new List<int>();
                List<int> pubMaxPairIndex = new List<int>();
                List<int> pairPub2Index = new List<int>();

                command.CommandText = string.Format("select new_id, min_pair_index, max_pair_index from tmp_publns4 order by pub_index");

                using (SqlDataReader reader = command.ExecuteReader())
                    while (reader.Read())
                    {
                        publnUT.Add(reader.GetInt32(0));
                        pubMinPairIndex.Add(reader.GetInt32(1));
                        pubMaxPairIndex.Add(reader.GetInt32(2));
                    }
                command.CommandText = string.Format("select publn2_index from tmp_publn_pairs3 order by pair_index");

                using (SqlDataReader reader = command.ExecuteReader())
                    while (reader.Read())
                        pairPub2Index.Add(reader.GetInt32(0));

                ConnectedPubSetSearcher connectedPubSetSearcher = new ConnectedPubSetSearcher(pubMinPairIndex, pubMaxPairIndex, pairPub2Index);
                List<List<int>> connectedPubSets = connectedPubSetSearcher.getConnectedPubSets();

                for (int i = 0; i < connectedPubSets.Count; i++)
                {
                    record.SetValue(0, i);
                    for (int j = 0; j < connectedPubSets[i].Count; j++)
                    {
                        record.SetValue(1, publnUT[connectedPubSets[i][j]]);
                        SqlContext.Pipe.SendResultsRow(record);
                    }
                }
                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }

        private void AddToWindow(object[] newTuple, string[] operators, ArrayList resultCollection, ArrayList resultstringCollection, SqlDataRecord record, SqlBoolean isFrameworkMode, int level, DataTable dtResult)
        {
            //Erste Spalte ist die ID
            long?[] recordInt = new long?[operators.GetUpperBound(0) + 1];
            string[] recordstring = new string[operators.GetUpperBound(0) + 1];
            DataRow row = dtResult.NewRow();

            for (int iCol = 0; iCol <= newTuple.GetUpperBound(0); iCol++)
            {
                //Only the real columns (skyline columns are not output fields)
                if (iCol <= operators.GetUpperBound(0))
                {
                    //LOW und HIGH Spalte in record abfüllen
                    if (operators[iCol].Equals("LOW"))
                    {
                        if (newTuple[iCol] == DBNull.Value)
                            recordInt[iCol] = null;
                        else
                            recordInt[iCol] = (long)newTuple[iCol];

                        //Check if long value is incomparable
                        if (iCol + 1 <= recordInt.GetUpperBound(0) && operators[iCol + 1].Equals("INCOMPARABLE"))
                        {
                            //Incomparable field is always the next one
                            recordstring[iCol] = (string)newTuple[iCol + 1];
                        }
                    }

                }
                else
                {
                    row[iCol - (operators.GetUpperBound(0) + 1)] = newTuple[iCol];
                    record.SetValue(iCol - (operators.GetUpperBound(0) + 1), newTuple[iCol]);
                }
            }
            row[record.FieldCount - 1] = level;
            record.SetValue(record.FieldCount - 1, level);

            if (isFrameworkMode == true)
            {
                dtResult.Rows.Add(row);
            }
            else
            {
                SqlContext.Pipe.SendResultsRow(record);
            }
            resultCollection.Add(recordInt);
            resultstringCollection.Add(recordstring);
        }

        public static void SendDataTable(DataTable dt)
        {
            bool[] coerceToString;  // Do we need to coerce this column to string?
            SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);

            SqlDataRecord record = new SqlDataRecord(metaData);
            SqlPipe pipe = SqlContext.Pipe;
            pipe.SendResultsStart(record);
            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    for (int index = 0; index < record.FieldCount; index++)
                    {
                        object value = row[index];
                        if (null != value && coerceToString[index])
                            value = value.ToString();
                        record.SetValue(index, value);
                    }

                    pipe.SendResultsRow(record);
                }
            }
            finally
            {
                pipe.SendResultsEnd();
            }
        }

        public void Update(IList<TreeViewNode> treeViewNodes)
        {
            var sqlConnection = new SqlConnection(ConnectionString);
            var sqlCommand = new SqlCommand("dbo.UpdateTreeViewData", sqlConnection)
            {
                CommandType = CommandType.StoredProcedure,
            };

            sqlCommand.Parameters.Add("@p_TreeViewData", SqlDbType.Structured);
            var list = new List<SqlDataRecord>();

            foreach (var treeViewNode in treeViewNodes)
            {
                var sqlDataRecord = new SqlDataRecord(
                    new SqlMetaData("Id", SqlDbType.Int),
                    new SqlMetaData("ParentId", SqlDbType.Int),
                    new SqlMetaData("NodeName", SqlDbType.NVarChar, 50),
                    new SqlMetaData("IsSelected", SqlDbType.Bit));

                sqlDataRecord.SetValue(0, treeViewNode.Id);
                sqlDataRecord.SetValue(1, treeViewNode.ParentId);
                sqlDataRecord.SetString(2, treeViewNode.NodeName);
                sqlDataRecord.SetValue(3, treeViewNode.IsSelected);

                list.Add(sqlDataRecord);
            }

            sqlCommand.Parameters["@p_TreeViewData"].Value = list;

            try
            {
                sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }

            finally
            {
                sqlConnection.Close();
            }
        }

        /// <summary>Send a DataTable to a SqlContext.</summary>
        /// <exception cref="Exception">Throw an exception if the DataTable is null.</exception>
        /// <param name="dt">The DataTable to send to the SqlContext.</param>
        internal static void SendDataTable(DataTable dt)
        {
            if (dt == null)
            {
                throw new Exception(ExceptionMessage.Unexpected_NullResultSet);
            }

            bool[] useToString;
            var metaData = ExtractDataTableColumnMetaData(dt, out useToString);

            var record = new SqlDataRecord(metaData);
            var pipe = SqlContext.Pipe;

            pipe.SendResultsStart(record);

            try
            {
                foreach (DataRow row in dt.Rows)
                {
                    for (var index = 0; index < record.FieldCount; index++)
                    {
                        var value = row[index];
                        if (value != null && useToString[index])
                        {
                            value = value.ToString();
                        }

                        record.SetValue(index, value);
                    }

                    pipe.SendResultsRow(record);
                }
            }
            finally
            {
                pipe.SendResultsEnd();
            }
        }

 /// <summary>
 /// Gets the orders by order ids.
 /// </summary>
 /// <param name="ids">The ids.</param>
 /// <param name="fcn">SQL connection.</param>
 /// <param name="ftrns">SQL transaction.</param>
 /// <returns>The matching orders.</returns>
 public static List<Order> GetOrdersByOrderIds(int[] ids, SqlConnection fcn, SqlTransaction ftrns)
 {
     List<Order> orders = new List<Order>();
     if(ids.Length == 0) { return orders; };
     List<SqlDataRecord> rowData = new List<SqlDataRecord>();
     SqlMetaData[] hashTable = {
         new SqlMetaData("keyName",SqlDbType.VarChar,100),
         new SqlMetaData("keyValue",SqlDbType.Variant),
         new SqlMetaData("primary_key",SqlDbType.Bit),
         new SqlMetaData("dataType",SqlDbType.VarChar,50),
         new SqlMetaData("dataLength",SqlDbType.Int),
         new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
     };
     StringBuilder s = new StringBuilder();
     foreach(int id in ids) {
         SqlDataRecord rec = new SqlDataRecord(hashTable);
         rec.SetValue(0, "orderId");
         rec.SetValue(1, id);
         rec.SetBoolean(2, false);
         rec.SetString(3, "int");
         rec.SetValue(4, 8);
         rowData.Add(rec);
     }
     SqlConnection cn;
     if(fcn != null) {
         cn = fcn;
     } else {
         cn = Site.SqlConnection;
     }
     using(SqlCommand cmd = cn.CreateCommand()) {
         if(fcn != null) {
             cmd.Transaction = ftrns;
         }
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.CommandText = "dbo.getOrders";
         cmd.Parameters.Add("@orderIds", SqlDbType.Structured);
         cmd.Parameters["@orderIds"].Direction = ParameterDirection.Input;
         cmd.Parameters["@orderIds"].Value = rowData;
         using(SqlDataReader u = cmd.ExecuteReader()) {
             int orderId = -1;
             DateTime orderDate = DateTime.MinValue;
             decimal grandTotal = 0;
             decimal taxTotal = 0;
             decimal subTotal = 0;
             decimal shippingTotal = 0;
             decimal service1 = 0;
             decimal service2 = 0;
             string manifest = "";
             string purchaseOrder = "";
             decimal discount = 0;
             string comment = "";
             decimal paid = 0;
             Guid billToAddressId = Guid.Empty;
             bool closed = false;
             bool canceled = false;
             Guid paymentMethodId = Guid.Empty;
             int termId = -1;
             int userId = -1;
             string orderNumber = "";
             bool creditMemo = false;
             string scanned_order_image = "";
             DateTime readyForExport = DateTime.MinValue;
             DateTime recalculatedOn = DateTime.MinValue;
             Guid sessionId = Guid.Empty;
             int soldBy = -1;
             int requisitionedBy = -1;
             int approvedBy = -1;
             DateTime deliverBy = DateTime.MinValue;
             string vendor_accountNo = "";
             string FOB = "";
             int parentOrderId = -1;
             int order_status = -1;
             List<Line> lines = new List<Line>();
             while(u.Read()) {
                 /* #44 is orderId */
                 if(u.GetInt32(44) != orderId && orderId != -1) {
                     /*the orderId has changed, add the previous order */
                     orders.Add(new Order(
                         orderId, orderDate, grandTotal, taxTotal, subTotal, shippingTotal, service1,
                         service2, manifest, purchaseOrder, discount, comment, paid, billToAddressId, closed,
                         canceled, paymentMethodId, termId, userId, orderNumber, creditMemo, scanned_order_image,
                         readyForExport, recalculatedOn, sessionId, soldBy, requisitionedBy, approvedBy, deliverBy,
                         vendor_accountNo, FOB, parentOrderId, lines, order_status, cn, ftrns
                     ));
                     lines = new List<Line>();/* create a new list of lines for the next order */
                 }
                 orderId = u.GetInt32(44);
                 orderDate = u.GetDateTime(132);
                 grandTotal = u.GetDecimal(133);
                 taxTotal = u.GetDecimal(134);
                 subTotal = u.GetDecimal(135);
                 shippingTotal = u.GetDecimal(136);
                 service1 = u.GetDecimal(137);
//.........这里部分代码省略.........

        private static IEnumerable<SqlDataRecord> BuildTableValueParameter(IEnumerable<string> list, SqlMetaData[] tvpDefinition)
        {
            var includeList = new List<SqlDataRecord>();
            foreach (var item in list)
            {
                var rec = new SqlDataRecord(tvpDefinition);
                rec.SetValue(0, item);
                includeList.Add(rec);
            }

            return includeList;
        }

    public static void GetConnectedPubSetsTest()
    {
        List<int> blocks_to_process = new List<int>();

        using (var connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "select block_id from block_processing order by block_id";
                using (SqlDataReader reader = command.ExecuteReader())
                    while (reader.Read())
                        //blocks_to_process.Add(Convert.ToInt32(reader.GetInt32(0)));
                        blocks_to_process.Add(reader.GetInt32(0));

                SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
                new SqlMetaData("block_id", SqlDbType.Int),
                new SqlMetaData("cluster", SqlDbType.Int),
                new SqlMetaData("ut", SqlDbType.Char, 15),
                new SqlMetaData("au_count", SqlDbType.Int)
                    });

                SqlContext.Pipe.SendResultsStart(record);

                for (int k = 0; k < blocks_to_process.Count; k++)
                {
                    List<string> pubUT = new List<string>();
                    List<int> pubAuCount = new List<int>();
                    List<int> pubMinPairIndex = new List<int>();
                    //var pubMinPairIndex = new List<Int64>();
                    List<int> pubMaxPairIndex = new List<int>();
                    List<int> pairPub2Index = new List<int>();

                    //command.CommandText = string.Format("select ut, min_pair_index, max_pair_index from tmp_blocks_pubs4 where block_id = {0} order by pub_index", blocks_to_process[k]);
                    command.CommandText = string.Format("select ut, au_count, min_pair_index, max_pair_index from tmp_blocks_pubs4 where block_id = {0} order by pub_index", blocks_to_process[k]);

                    //command.CommandText = "select ut, min_pair_index, max_pair_index from tmp_blocks_pubs4 where block_id = " + blocks_to_process[k] + " order by pub_index";
                    using (SqlDataReader reader = command.ExecuteReader())
                        while (reader.Read())
                        {
                            pubUT.Add(reader.GetString(0));
                            //pubMinPairIndex.Add(Convert.ToInt32(reader.GetInt64(1)));
                            pubAuCount.Add(reader.GetInt32(1));
                            pubMinPairIndex.Add(reader.GetInt32(2));
                            //pubMaxPairIndex.Add(Convert.ToInt32(reader.GetInt64(2)));
                            pubMaxPairIndex.Add(reader.GetInt32(3));
                        }
                    //command.CommandText = "select pub2_index from tmp_blocks_pub_pairs3 where block_id = " + blocks_to_process[k] + " order by pair_index";
                    command.CommandText = string.Format("select pub2_index from tmp_blocks_pub_pairs3 where block_id = {0} order by pair_index", blocks_to_process[k]);

                    using (SqlDataReader reader = command.ExecuteReader())
                        while (reader.Read())
                            //                            pairPub2Index.Add(Convert.ToInt32(reader.GetInt64(0)));
                            pairPub2Index.Add(reader.GetInt32(0));

                    ConnectedPubSetSearcher connectedPubSetSearcher = new ConnectedPubSetSearcher(pubMinPairIndex, pubMaxPairIndex, pairPub2Index);
                    List<List<int>> connectedPubSets = connectedPubSetSearcher.getConnectedPubSets();

                    //SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
                    //new SqlMetaData("block_id", SqlDbType.Int),
                    //new SqlMetaData("cluster", SqlDbType.Int),
                    //new SqlMetaData("ut", SqlDbType.Char, 15)
                    //    });
                    //SqlContext.Pipe.SendResultsStart(record);

                    //foreach (var connectedPubSet in connectedPubSets)
                    //{
                    //    record.SetValue(1, connectedPubSet);
                    //}


                    for (int i = 0; i < connectedPubSets.Count; i++)
                    {
                        record.SetValue(0, blocks_to_process[k]);
                        record.SetValue(1, i);
                        for (int j = 0; j < connectedPubSets[i].Count; j++)
                        {
                            record.SetValue(2, pubUT[connectedPubSets[i][j]]);
                            record.SetValue(3, pubAuCount[connectedPubSets[i][j]]);
                            SqlContext.Pipe.SendResultsRow(record);
                        }
                    }
                    //SqlContext.Pipe.SendResultsEnd();
                }
                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }

 public static JsonResponse JsonReadOrDelete(string objectName, int rowFrom, int rowTo, SqlWhere whereClause, Guid accountId, SqlWhere searchClause,
                                     IDictionary<string, string> aggregates, ICollection<int> selectedRows, bool includeSchemaData, Int64 checksum, bool deleteSelection,
                                     string orderBy, OrderDirection orderByDirection, SqlConnection connection)
 {
     var s = new JsonResponse();
     var aggs = new StringBuilder();
     var sRows = new StringBuilder();
     s.MethodName = "JsonReadOrDelete";
     var rows = new List<object>();
     s.Add("rows", rows);
     // convert aggregate column dictionary to a string
     aggregates = aggregates ?? new Dictionary<string, string>();
     if(aggregates.Count>0){
         foreach(var k in aggregates){
             aggs.AppendFormat("{0}|{1},", k.Key, k.Value);
         }
         // remove trailing comma
         aggs.Remove(aggs.Length - 1, 1);
     }
     selectedRows = selectedRows ?? new Collection<int>();
     foreach(var i in selectedRows){
         sRows.AppendFormat("{0},",i);
         // remove trailing comma
         sRows.Remove(aggs.Length - 1, 1);
     }
     using (var cn = connection ?? CreateConnection()) {
         if (cn.State != ConnectionState.Open) { cn.Open(); }
         using (var cmd  = cn.CreateCommand()) {
             cmd.CommandText = _jsonReadOrDeleteQuery;
             cmd.CommandType = CommandType.StoredProcedure;
             SqlMetaData[] prameterList = {
                 new SqlMetaData("Name",SqlDbType.VarChar,100),
                 new SqlMetaData("Type",SqlDbType.VarChar,100),
                 new SqlMetaData("Length",SqlDbType.VarChar,10),
                 new SqlMetaData("Value",SqlDbType.Variant)
             };
             var whereParameterList = new List<SqlDataRecord>();
             var searchParameterList = new List<SqlDataRecord>();
             whereClause = whereClause ?? new SqlWhere();
             searchClause = searchClause ?? new SqlWhere();
             foreach(var p in whereClause.Parmeters){
                 var whereParameter = new SqlDataRecord(prameterList);
                 whereParameter.SetString(0, p.Name);
                 whereParameter.SetString(1, p.SqlDbType.ToString());
                 whereParameter.SetValue(2, p.Length);
                 whereParameter.SetValue(3, p.Value);
                 whereParameterList.Add(whereParameter);
             }
             foreach (var p in searchClause.Parmeters) {
                 var searchParameter = new SqlDataRecord(prameterList);
                 searchParameter.SetString(0, p.Name);
                 searchParameter.SetString(1, p.SqlDbType.ToString());
                 searchParameter.SetValue(2, p.Length);
                 searchParameter.SetValue(3, p.Value);
                 searchParameterList.Add(searchParameter);
             }
             cmd.Parameters.Add("@objName", SqlDbType.VarChar).Value = objectName;
             cmd.Parameters.Add("@record_from", SqlDbType.Int).Value = rowFrom;
             cmd.Parameters.Add("@record_to", SqlDbType.Int).Value = rowTo;
             cmd.Parameters.Add("@suffix", SqlDbType.VarChar).Value = whereClause.WhereClause;
             cmd.Parameters.Add("@accountId", SqlDbType.UniqueIdentifier).Value = accountId;
             cmd.Parameters.Add("@searchSuffix", SqlDbType.VarChar).Value = searchClause.WhereClause;
             cmd.Parameters.Add("@aggregateColumns", SqlDbType.VarChar).Value = aggs.ToString();
             cmd.Parameters.Add("@selectedRowsCSV", SqlDbType.VarChar).Value = sRows.ToString();
             cmd.Parameters.Add("@includeSchema", SqlDbType.Bit).Value = includeSchemaData;
             cmd.Parameters.Add("@checksum", SqlDbType.BigInt).Value = checksum;
             cmd.Parameters.Add("@delete", SqlDbType.Bit).Value = deleteSelection;
             cmd.Parameters.Add("@orderBy_override", SqlDbType.VarChar).Value = orderBy;
             cmd.Parameters.Add("@orderDirection_override", SqlDbType.VarChar).Value = orderByDirection == OrderDirection.Ascending ? "asc" : "desc";
             cmd.Parameters.Add("@whereParameterList", SqlDbType.Structured);
             cmd.Parameters["@whereParameterList"].Direction = ParameterDirection.Input;
             cmd.Parameters["@whereParameterList"].Value = (whereParameterList.Count == 0 ? null : whereParameterList);
             cmd.Parameters.Add("@searchParameterList", SqlDbType.Structured);
             cmd.Parameters["@searchParameterList"].Direction = ParameterDirection.Input;
             cmd.Parameters["@searchParameterList"].Value = (searchParameterList.Count == 0 ? null : searchParameterList);
             using (var r = cmd.ExecuteReader()) {
                 if(searchClause.WhereClause.Length == 0){
                     // add range data
                     var range = new Dictionary<string, object> { { "from", rowFrom }, { "to", rowTo } };
                     s.Add("range", range);
                     // first row contains error data
                     r.Read();
                     var header = JsonConvert.DeserializeObject<Dictionary<string, object>>(r.GetString(0));
                     s.Add("header", header);
                     // pull error and description info from SP result
                     s.Error = Convert.ToInt32((Int64)header["error"]);
                     s.Message = (string)header["description"];
                     // second row contains schema data
                     r.Read();
                     var schema = JsonConvert.DeserializeObject<List<object>>(r.GetString(0));
                     s.Add("columns", schema);
                     // the rest are row data
                     while (r.Read()) {
                         var row = JsonConvert.DeserializeObject<List<object>>(r.GetString(0));
                         rows.Add(row);
                     }
                 }else {
                     if(r.HasRows){
                         s["rows"] = JsonConvert.DeserializeObject<List<int>>(r.GetString(0));
                     }else {
//.........这里部分代码省略.........

            /// <summary>
            /// Pays with existing payment.
            /// </summary>
            /// <param name="paymentMethodIds">The list of paymentMethodIds.</param>
            /// <param name="amount">The amount.</param>
            /// <param name="userId">The userId.</param>
            /// <param name="postingDate">The posting date.</param>
            /// <param name="orderIds">The order ids.</param>
            /// <param name="cn">The sql connection (or null).</param>
            /// <param name="trans">The sql transaction (or null).</param>
            /// <returns>{error:0,desc:"error description"}.</returns>
            public static Dictionary<string, object> PayWithExistingPaymentMethods( List<object> paymentMethodIds,
			decimal amount, int userId, DateTime postingDate, List<object> orderIds,
			SqlConnection cn, SqlTransaction trans )
            {
                int errorId = 0;
                string desc = "";
                List<int> intIds = orderIds.ConvertAll( delegate( object i ) {
                    return Convert.ToInt32( i );
                } );
                Dictionary<string, object> j = new Dictionary<string, object>();
                /* before updating - check to ensure that there really is enouch left over on this paymentMethodId(s)
                 * to attach the desiered amount to the selected order
                 */
                using( SqlCommand cmd = new SqlCommand() ) {
                    List<SqlDataRecord> rec_paymentMethodIds = new List<SqlDataRecord>();
                    List<SqlDataRecord> rec_orderIds = new List<SqlDataRecord>();
                    SqlMetaData[] hashTable = {
                        new SqlMetaData("keyName",SqlDbType.VarChar,100),
                        new SqlMetaData("keyValue",SqlDbType.Variant),
                        new SqlMetaData("primary_key",SqlDbType.Bit),
                        new SqlMetaData("dataType",SqlDbType.VarChar,50),
                        new SqlMetaData("dataLength",SqlDbType.Int),
                        new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
                    };
                    foreach( string id in paymentMethodIds ) {
                        SqlDataRecord rec = new SqlDataRecord( hashTable );
                        rec.SetValue( 0, "paymentMethodId" );
                        rec.SetValue( 1, id );
                        rec.SetBoolean( 2, false );
                        rec.SetString( 3, "uniqueidentifier" );
                        rec.SetValue( 4, 32 );
                        rec_paymentMethodIds.Add( rec );
                    }
                    foreach( int id in intIds ) {
                        SqlDataRecord rec = new SqlDataRecord( hashTable );
                        rec.SetValue( 0, "orderId" );
                        rec.SetValue( 1, id );
                        rec.SetBoolean( 2, false );
                        rec.SetString( 3, "int" );
                        rec.SetValue( 4, 8 );
                        rec_orderIds.Add( rec );
                    }
                    cmd.Connection = cn;
                    cmd.Transaction = trans;
                    cmd.CommandType = CommandType.StoredProcedure;
                    /* this SP will return a single row with error, desc saying if the procedure was successfull.
                     * the SP sums the remaning total value left on the selected paymentMethods and compares
                     * it to the amount trying to be paid.  If the remaining amount is >= the amount trying to
                     * be paid the payments will be attached, if the remaining amount is < the amoun trying to
                     * be paid an error will be returned saying as much.
                     */
                    cmd.CommandText = "dbo.attachPaymentMethods";
                    cmd.Parameters.Add( "@amountTryingToBePaid", SqlDbType.Money ).Value = amount;
                    cmd.Parameters.Add( "@paymentMethodIds", SqlDbType.Structured );
                    cmd.Parameters[ "@paymentMethodIds" ].Direction = ParameterDirection.Input;
                    if( rec_paymentMethodIds.Count == 0 ) {
                        string message = "You must select at least one payment method.";
                        message.Debug( 7 );
                        Exception ex = new Exception( message );
                        throw ex;
                    } else {
                        cmd.Parameters[ "@paymentMethodIds" ].Value = rec_paymentMethodIds;
                    }
                    cmd.Parameters.Add( "@orderIds", SqlDbType.Structured );
                    cmd.Parameters[ "@orderIds" ].Direction = ParameterDirection.Input;
                    if( rec_orderIds.Count == 0 ) {
                        string message = "You must select at least one payment method.";
                        message.Debug( 7 );
                        Exception ex = new Exception( message );
                        throw ex;
                    } else {
                        cmd.Parameters[ "@orderIds" ].Value = rec_orderIds;
                    }
                    using( SqlDataReader r = cmd.ExecuteReader() ) {
                        /* batch 1 is the status */
                        r.Read();
                        Dictionary<string, object> s = new Dictionary<string, object>();
                        errorId = r.GetInt32( 0 );
                        desc = r.GetString( 1 );
                        /* NOTE:  Addtional callback information for attaching payments to orders
                         * I don't really care about this stuff so I'm not going to write anything to capture it
                         * but there is is for anyone who does want to capture it.
                         */
                        /* batch 2 is the actual payment detail inserts (paymentMethodDetailId,paymentMethodId,refId,amount)*/
                        /* batch 3 is the actual upated orders (orderId, paid) */
                    }
                }
                if( errorId != 0 ) {
                    j.Add( "error", errorId );
//.........这里部分代码省略.........

            public static void Insert( string paymentType, Guid paymentMethodId, Guid addressId, int userId, Guid sessionId,
			int termId, string reference, decimal amount, DateTime postingDate, List<int> orderIds,
			string cardName, string cardType, string cardNumber, string expMonth, string expYear,
			string secNumber, string routingNumber, string checkNumber, string bankAccountNumber,
			string payPalEmailAddress, string swift, string bankName, string routingTransitNumber,
			bool cash, string notes, bool _promiseToPay, SqlConnection cn, SqlTransaction trans )
            {
                String.Format( "Place Order > insertPaymentMethod for userId: {0}, type: {1}", userId, paymentType ).Debug( 7 );
                try {
                    using(SqlCommand cmd=new SqlCommand()) {
                        List<SqlDataRecord> rowData=new List<SqlDataRecord>();
                        SqlMetaData[] hashTable= {
                            new SqlMetaData("keyName",SqlDbType.VarChar,100),
                            new SqlMetaData("keyValue",SqlDbType.Variant),
                            new SqlMetaData("primary_key",SqlDbType.Bit),
                            new SqlMetaData("dataType",SqlDbType.VarChar,50),
                            new SqlMetaData("dataLength",SqlDbType.Int),
                            new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
                        };
                        StringBuilder s=new StringBuilder();
                        foreach(int id in orderIds) {
                            SqlDataRecord rec=new SqlDataRecord(hashTable);
                            rec.SetValue(0,"orderId");
                            rec.SetValue(1,id);
                            rec.SetBoolean(2,false);
                            rec.SetString(3,"int");
                            rec.SetValue(4,8);
                            rowData.Add(rec);
                        }
                        cmd.Connection=cn;
                        cmd.Transaction=trans;
                        cmd.CommandType=CommandType.StoredProcedure;
                        cmd.CommandText="dbo.insertPaymentMethod";
                        cmd.Parameters.Add("@paymentMethodId",SqlDbType.UniqueIdentifier).Value=paymentMethodId;
                        cmd.Parameters.Add("@paymentType",SqlDbType.VarChar).Value=paymentType;
                        cmd.Parameters.Add("@cardName",SqlDbType.VarChar).Value=cardName;
                        cmd.Parameters.Add("@cardType",SqlDbType.VarChar).Value=cardType.MaxLength(25,false);
                        cmd.Parameters.Add("@cardNumber",SqlDbType.VarChar).Value=cardNumber;
                        cmd.Parameters.Add("@expMonth",SqlDbType.VarChar).Value=expMonth;
                        cmd.Parameters.Add("@expYear",SqlDbType.VarChar).Value=expYear;
                        cmd.Parameters.Add("@secNumber",SqlDbType.VarChar).Value=secNumber;
                        cmd.Parameters.Add("@userId",SqlDbType.Int).Value=userId;
                        cmd.Parameters.Add("@sessionId",SqlDbType.UniqueIdentifier).Value=sessionId;
                        cmd.Parameters.Add("@addressId",SqlDbType.UniqueIdentifier).Value=addressId;
                        cmd.Parameters.Add("@routingNumber",SqlDbType.VarChar).Value="";
                        cmd.Parameters.Add("@checkNumber",SqlDbType.VarChar).Value="";
                        cmd.Parameters.Add("@bankAccountNumber",SqlDbType.VarChar).Value="";
                        cmd.Parameters.Add("@payPalEmailAddress",SqlDbType.VarChar).Value="";
                        cmd.Parameters.Add("@swift",SqlDbType.VarChar).Value="";
                        cmd.Parameters.Add("@bankName",SqlDbType.VarChar).Value="";
                        cmd.Parameters.Add("@routingTransitNumber",SqlDbType.VarChar).Value=routingTransitNumber;
                        cmd.Parameters.Add("@cash",SqlDbType.Bit).Value=cash;
                        cmd.Parameters.Add("@notes",SqlDbType.VarChar).Value=notes;
                        cmd.Parameters.Add("@termId",SqlDbType.Int).Value=termId;
                        cmd.Parameters.Add("@reference",SqlDbType.VarChar).Value=reference;
                        cmd.Parameters.Add("@amount",SqlDbType.Money).Value=amount;
                        cmd.Parameters.Add("@promiseToPay",SqlDbType.Bit).Value=_promiseToPay;
                        cmd.Parameters.Add("@orderIds",SqlDbType.Structured);
                        cmd.Parameters["@orderIds"].Direction=ParameterDirection.Input;
                        if(rowData.Count==0) {
                            cmd.Parameters["@orderIds"].Value=null;
                        } else {
                            cmd.Parameters["@orderIds"].Value=rowData;
                        }
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                } catch(Exception ex) {
                    String.Format("Place Order > insertPaymentMethod exception:{0}",ex.Message).Debug(0);
                }
            }

 private SqlDataRecord SetDbRecord(SqlDataRecord record, object value, int index)
 {
     if (value.IsPrimitive())
     {
         var cValue = Convert.ChangeType(value, record.GetFieldType(index));
         record.SetValue(index, cValue);
     }
     else
     {
         record.SetValue(index, value.ToString());
     }
     return record;
 }

        /// <summary>
        /// Posts payments to general ledger.
        /// </summary>
        /// <param name="ids">The payment ids.</param>
        /// <param name="postingDate">The posting date.</param>
        /// <param name="postingNotes">The posting notes.</param>
        /// <param name="preview">if set to <c>true</c> [preview].</param>
        /// <returns>{error:0,desc:"error description",preview:false,
        /// generalLedgerEntries:{
        ///		drDate,
        ///		drDetails,
        ///		drReference,
        ///		drAmount,
        ///		crDate,
        ///		crDetails,
        ///		crReference,
        ///		crAmount
        /// },
        /// rawGL:{
        ///		generalLedgerId,
        ///		creditRecord,
        ///		debitRecord,
        ///		amount,
        ///		userId,
        ///		termId,
        ///		addDate,
        ///		reference,
        ///		orderId,
        ///		generalLedgerId
        /// },
        /// rawGLDetail:{
        ///		generalLedgerDetailId,
        ///		generalLedgerId,
        ///		refId,
        ///		refType
        /// }
        /// }.</returns>
        public static Dictionary<string, object> PostPaymentsToGeneralLedger(List<object> ids, string postingDate, string postingNotes, bool preview)
        {
            Dictionary<string, object> j = new Dictionary<string, object>();
            List<object> accountantReadable = new List<object>();
            List<object> rawGL = new List<object>();
            List<object> rawGLDetail = new List<object>();
            using(SqlConnection cn = Site.CreateConnection(true, true)) {
                cn.Open();
                using(SqlTransaction trans = cn.BeginTransaction("paymentPosting")) {
                    List<Order> orders = new List<Order>();
                    int error = 0;
                    string desc = "";
                    decimal crTotal = 0;
                    decimal drTotal = 0;
                    DateTime _postingDate;
                    if(!DateTime.TryParse(postingDate, out _postingDate)) {
                        j.Add("preview", preview);
                        j.Add("error", -2);
                        j.Add("description", "Posting date is not in the correct format.");
                    }
                    if(ids.Count == 0) {
                        j.Add("preview", preview);
                        j.Add("error", -1);
                        j.Add("description", "No orders selected.");
                    };
                    List<SqlDataRecord> rowData = new List<SqlDataRecord>();
                    SqlMetaData[] hashTable = {
                        new SqlMetaData("keyName",SqlDbType.VarChar,100),
                        new SqlMetaData("keyValue",SqlDbType.Variant),
                        new SqlMetaData("primary_key",SqlDbType.Bit),
                        new SqlMetaData("dataType",SqlDbType.VarChar,50),
                        new SqlMetaData("dataLength",SqlDbType.Int),
                        new SqlMetaData("varCharMaxValue",SqlDbType.VarChar,-1)
                    };
                    StringBuilder s = new StringBuilder();
                    foreach(object id in ids) {
                        SqlDataRecord rec = new SqlDataRecord(hashTable);
                        rec.SetValue(0, "paymentId");
                        rec.SetValue(1, (new Guid((string)id)).ToString());
                        rec.SetBoolean(2, false);
                        rec.SetString(3, "uniqueidentifier");
                        rec.SetValue(4, 0);
                        rowData.Add(rec);
                    }

                    using(SqlCommand cmd = cn.CreateCommand()) {
                        cmd.Transaction = trans;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "dbo.postPaymentsToGeneralLedger";

                        cmd.Parameters.Add("@paymentMethodIds", SqlDbType.Structured);
                        cmd.Parameters["@paymentMethodIds"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@paymentMethodIds"].Value = rowData;

                        cmd.Parameters.Add("@unique_site_id", SqlDbType.UniqueIdentifier);
                        cmd.Parameters["@unique_site_id"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@unique_site_id"].Value = new Guid(Site.Id.ToString());

                        cmd.Parameters.Add("@postingDate", SqlDbType.DateTime);
                        cmd.Parameters["@postingDate"].Direction = ParameterDirection.Input;
                        cmd.Parameters["@postingDate"].Value = _postingDate;

                        cmd.Parameters.Add("@referenceNotes", SqlDbType.VarChar);
//.........这里部分代码省略.........

        public static void SendDataTableOverPipe(DataTable tbl)
        {
            // Build our record schema
            List<SqlMetaData> OutputColumns = new List<SqlMetaData>(tbl.Columns.Count);
            foreach (DataColumn col in tbl.Columns)
            {

                SqlMetaData outputColumn;
                if (col.DataType == typeof(Int32) || col.DataType == typeof(Int64) || col.DataType == typeof(DateTime))
                {
                    outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType));
                }
                else if (col.DataType == typeof(Decimal))
                {
                    outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), 12, 10);
                }
                else
                {
                    outputColumn = new SqlMetaData(col.ColumnName, TypeConverter.ToSqlDbType(col.DataType), col.MaxLength);
                }

                OutputColumns.Add(outputColumn);
            }

            // Build our SqlDataRecord and start the results
            SqlDataRecord record = new SqlDataRecord(OutputColumns.ToArray());
            SqlContext.Pipe.SendResultsStart(record);

            // Now send all the rows
            foreach (DataRow row in tbl.Rows)
            {
                for (int col = 0; col < tbl.Columns.Count; col++)
                {
                    record.SetValue(col, row.ItemArray[col]);
                }
                SqlContext.Pipe.SendResultsRow(record);
            }

            // And complete the results
            SqlContext.Pipe.SendResultsEnd();
        }

        internal static SqlDataRecord ToSqlDataRecord(this EventRecord record)
        {
            var sqlDataRecord = new SqlDataRecord(SqlMetaData);

            sqlDataRecord.SetValue(0, record.InstanceName ?? string.Empty);
            sqlDataRecord.SetValue(1, record.ProviderId);
            sqlDataRecord.SetValue(2, record.ProviderName ?? string.Empty);
            sqlDataRecord.SetValue(3, record.EventId);
            sqlDataRecord.SetValue(4, record.EventKeywords);
            sqlDataRecord.SetValue(5, record.Level);
            sqlDataRecord.SetValue(6, record.Opcode);
            sqlDataRecord.SetValue(7, record.Task);
            sqlDataRecord.SetValue(8, record.Timestamp);
            sqlDataRecord.SetValue(9, record.Version);
            sqlDataRecord.SetValue(10, (object)record.FormattedMessage ?? DBNull.Value);
            sqlDataRecord.SetValue(11, (object)record.Payload ?? DBNull.Value);

            return sqlDataRecord;
        }