C# SqlDataRecord.SetValues方法代码示例

    public static void spGetNewVestaErrands(SqlDateTime date)
    {
        DateTime d = date.Value;

        String reqStr = String.Format("http://{0}/services/internalSite/errands/sendNewVestaErrandsMails?date={1}",
                                                                                                                                UserDefinedFunctions.fGetWapServerName(), d.ToString("s"));
        SqlContext.Pipe.Send(reqStr);
        HttpWebRequest rq = (HttpWebRequest)WebRequest.Create(reqStr);
        rq.KeepAlive = false;
        XmlDocument xdoc = new XmlDocument();
        using(HttpWebResponse rs = (HttpWebResponse)rq.GetResponse())
        using(Stream stream = rs.GetResponseStream())
                xdoc.Load(stream);
        XmlNode root = xdoc["result"];

        SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("id", SqlDbType.NVarChar, -1),
                                                                                                                                                                new SqlMetaData("text", SqlDbType.NVarChar, -1),
                                                                                                                                                                new SqlMetaData("creator", SqlDbType.NVarChar, -1),
                                                                                                                                                                new SqlMetaData("email", SqlDbType.NVarChar, -1)
                                                                                                                                                            );
        SqlContext.Pipe.SendResultsStart(rec);
        foreach(XmlNode ch in root.ChildNodes)
        {
            rec.SetValues(ch["id"].InnerText,
                                                                    ch["text"].InnerText,
                                                                    ch["creator"].InnerText,
                                                                    ch["email"].InnerText
                                                                    );
            SqlContext.Pipe.SendResultsRow(rec);
        }
        SqlContext.Pipe.SendResultsEnd();
    }

        private static SqlDataRecord createRecordPopulatedWithData(SqlDataReader dataReader, SqlMetaData[] meta)
        {
            SqlDataRecord rec = new SqlDataRecord(meta);
            object[] recordData = new object[dataReader.FieldCount];
            dataReader.GetSqlValues(recordData);

            rec.SetValues(recordData);
            return rec;
        }

    private static void SendTable(SqlDataReader reader, TRowSetMap Map)
    {
        //SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
        DataTable LDataTable = reader.GetSchemaTable();
        SqlDataRecord WriteRecord;

        List<TFieldAlias> Fields = new List<TFieldAlias>();
        TFieldAlias Field;
        string FieldName;
        int FieldCount = reader.FieldCount, WriteFieldCount = 0;
        int i;
        SqlMetaData[] WriteFields;

        if(Map.Fields.Length > 0)
        {
          WriteFields = new SqlMetaData[0];

          foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
          {
        i = FieldMap.IndexOf('=');
        if(i >= 0)
        {
          Field.Name = FieldMap.Substring(0, i);
          FieldName  = FieldMap.Substring(i + 1);
        }
        else
        {
          Field.Name = FieldMap;
          FieldName  = FieldMap;
        }

        for(i = 0; i < FieldCount; i++)
        {
          if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
            break;
        }
        if((i < 0) || (i >= FieldCount))
          throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
        Field.FieldIndex = i;
        Fields.Add(Field);

        Array.Resize(ref WriteFields, ++WriteFieldCount);
        //WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
        WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
          }
        }
        else
        {
          WriteFields = new SqlMetaData[FieldCount];
          for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
        WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
        }
        WriteRecord = new SqlDataRecord(WriteFields);

        try
        {
          SqlContext.Pipe.SendResultsStart(WriteRecord);
          Object[] values = new Object[FieldCount];

          while (reader.Read())
          {
        reader.GetValues(values);
        if(Map.Fields.Length > 0)
        {
          for(i = 0; i < WriteFieldCount; i++)
            WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
        }
        else
        {
          WriteRecord.SetValues(values);
        }
        SqlContext.Pipe.SendResultsRow(WriteRecord);
          }
        }
        finally
        {
          SqlContext.Pipe.SendResultsEnd();
        }
    }

        public override void Start()
        {
            try
            {
                Status = TaskStatus.Running;
                var token = _cancelToken.Token;

                Task.Run(() =>
                    {
                        try 
                        {
                            _dtResult = new DataTable();

                             var tableList = new List<Tuple<string, int, int, int>>().Select(item => new { FullTableName = item.Item1, HasSameColumns = item.Item2, HasPK = item.Item3, MaxRowCountFromBoth = item.Item4 }).ToList();

                            if (!UserPermissions.Instance.UserSpecificPermissions[Owner.ToUpper()].CopyAndSearchFromDatabaseSeverList.Contains(_databaseServer))
                                throw new Exception("No permission to use this server.");


                            List<string> tableListRevided = new List<string>();
                            if (!String.IsNullOrWhiteSpace(_listOfTablesToCompare))
                                _listOfTablesToCompare.Replace("--", "").Split(',').ToList().ForEach(item => tableListRevided.Add(item.Trim()));


                            AppendOutputText(String.Format("Retrieving list of table to compare...{0}", Environment.NewLine));


                            var serverToUse = DatabaseServers.Instance.ItemsList.FirstOrDefault(item => item.Name == _databaseServer);


                            try
                            {
                                using (SqlConnection conn = new SqlConnection(serverToUse.ConnectionString + ";Connection Timeout=30"))
                                {
                                    SqlMetaData[] meta = new[]
                                    {
                                             new SqlMetaData("item", SqlDbType.NVarChar, 200),
                                    };


                                    var results = tableListRevided.Select(item =>
                                    {
                                        SqlDataRecord newRow = new SqlDataRecord(meta);
                                        newRow.SetValues(item);
                                        return newRow;
                                    });

                                    SqlCommand command = conn.CreateCommand();
                                    command.CommandTimeout = 5 * 60;
                                    command.CommandType = CommandType.StoredProcedure;
                                    command.CommandText = "dbo.GetTablesForDataCompare";
                                    command.Parameters.Add(new SqlParameter("@FirstDatabaseName", _databaseNameToCompare));
                                    command.Parameters.Add(new SqlParameter("@SecondDatabaseName", _databaseNameToCompareAgaints));
                                    SqlParameter param1 = new SqlParameter("@TablesToCompare", SqlDbType.Structured);
                                    param1.TypeName = "dbo.StringList";
                                    param1.Value = tableListRevided.Count > 0 ? results : null;
                                    command.Parameters.Add(param1);

                                    conn.Open();
                                    SqlDataReader myReader = command.ExecuteReader(CommandBehavior.CloseConnection);


                                    while (myReader.Read())
                                    {
                                        tableList.Add( new { FullTableName =  myReader["FullTableName"].ToString(), HasSameColumns = Int32.Parse(myReader["HasSameColumns"].ToString()), HasPK = Int32.Parse(myReader["HasPK"].ToString()), MaxRowCountFromBoth = Int32.Parse(myReader["MaxRowCountFromBoth"].ToString()) });
                                    }
                                    conn.Close();
                                    AppendOutputText(String.Format("Finished retrieving list of table.{0}", Environment.NewLine));
                                }
                            }
                            catch (Exception ex)
                            {
                                Status = TaskStatus.Failed;
                                this.AppendOutputText(ex.Message);
                                Log.ErrorFormat("Exception while retrieving list of table to compare from server: {0}, exception:{1}", serverToUse.Name, ex.Message);
                                return;
                            }


                            token.ThrowIfCancellationRequested();

                            int totalCount = tableList.Count;

                            if (totalCount == 0)
                                AppendOutputText(String.Format("There are no tables to compare.{0}", Environment.NewLine));


                            Parallel.ForEach(tableList, (item, loopState) =>
                            {
                                if (token.IsCancellationRequested)
                                {
                                    loopState.Stop();
                                    return;
                                }

                                if (item.HasSameColumns == 0)
                                {
                                    AppendOutputText(String.Format("Finished {1,4} of {2}. Table {0} will not be compared, column schema difference.{3}", item.FullTableName, Interlocked.Increment(ref _taskProgress), totalCount, Environment.NewLine));

                                }
//.........这里部分代码省略.........

        public static List<string> GetTablesForDataTrack(string databaseServer, string databaseName, string tablesToTrackCommaSeparated, string databaseOwner)
        {
            try
            {
                List<string> sqlStatementList = new List<string>();
                DatabaseServerInfo server = DatabaseServers.Instance.ItemsList.First(item => String.Compare(item.Name, databaseServer, true) == 0);

                List<string> tablesToTrackList = new List<string>();

                if (!String.IsNullOrWhiteSpace(tablesToTrackCommaSeparated))
                    tablesToTrackCommaSeparated.Replace("--", "").Split(',').ToList().ForEach(item => tablesToTrackList.Add(item.Trim()));


                try
                {
                    using (SqlConnection conn = new SqlConnection(server.ConnectionString))
                    {
                        SqlMetaData[] meta = new [] { new SqlMetaData("item", SqlDbType.NVarChar, 200)};


                        var results = tablesToTrackList.Select(item =>
                        {
                            SqlDataRecord newRow = new SqlDataRecord(meta);
                            newRow.SetValues(item);
                            return newRow;
                        });

                        var command = conn.CreateCommand();
                        command.CommandType = CommandType.StoredProcedure;
                        command.CommandText = "dbo.GetTablesForDataTrack";
                        command.Parameters.AddWithValue("@DatabaseName", databaseName);
                        command.Parameters.AddWithValue("@DatabaseOwner", databaseOwner);
                        SqlParameter param1 = new SqlParameter("@TableNames", System.Data.SqlDbType.Structured);
                        param1.TypeName = "dbo.StringList";
                        param1.Value = tablesToTrackList.Count > 0 ? results : null;
                        command.Parameters.Add(param1);

                        conn.Open();
                        SqlDataReader myReader = command.ExecuteReader(CommandBehavior.CloseConnection);
                        while (myReader.Read())
                        {
                            sqlStatementList.Add(myReader.GetString(0));
                        }
                    }

                    return sqlStatementList;
                }
                catch (Exception ex)
                {
                    Log.Error(String.Format("Problem accessing server: {0}.", server.Name), ex);
                    throw;
                }


            }
            catch (Exception ex)
            {
                Log.Error(ex);
                throw;
            }
        }

  public static void OpenQueryCLR(string ConnStr, string Query) {

    try {
      PrintOutput = null;

      using (SqlConnection connection = new SqlConnection(ConnStr))
      {
        connection.Open();
        connection.InfoMessage += new SqlInfoMessageEventHandler(connection_InfoMessage);

        using (IDbCommand qry = new SqlCommand())
        {
          qry.Connection = connection;
          qry.CommandText = Query;
          qry.CommandType = CommandType.Text;

          //execute the proc and get a reader back
          using (IDataReader rdr = qry.ExecuteReader(CommandBehavior.CloseConnection))
          {

            //use the returned columns to build a sql resultset
            using (DataTable columns = rdr.GetSchemaTable())
            {

              //only return a resultset if we actually got one to return, many /400 procs are just parm outputs
              if ((columns != null) && (columns.Rows.Count > 0))
              {
                SqlMetaData[] md = new SqlMetaData[columns.Rows.Count];

                for (int c = 0; c < columns.Rows.Count; c++)
                {
                  md[c] = new SqlMetaData(columns.Rows[c]["ColumnName"].ToString(), SqlDbType.VarChar, -1);
                }

                SqlDataRecord record = new SqlDataRecord(md);
                object[] vals = new object[columns.Rows.Count];
                SqlContext.Pipe.SendResultsStart(record);
                int rowcount = 0;
                while (rdr.Read())
                {
                  rowcount++;
                  rdr.GetValues(vals);
                  for (int i = 0; i < vals.Length; i++) vals[i] = vals[i].ToString().Trim();
                  record.SetValues(vals);
                  SqlContext.Pipe.SendResultsRow(record);
                }
                SqlContext.Pipe.SendResultsEnd();
              }

            }

          }

          //connection.Close(); // this happens automatically as a result of "CommandBehavior.CloseConnection" passed in to the qry.ExecuteReader
        }

      }
      if (PrintOutput != null) SqlContext.Pipe.Send(PrintOutput);
    }
    catch (Exception ex) {
      if (SqlContext.Pipe.IsSendingResults) SqlContext.Pipe.SendResultsEnd();
      SqlContext.Pipe.Send("Exception: " + ex.Message + "\r\n" + ex.StackTrace);
    }

  }