C# SqlDataRecord.SetString方法代码示例

 public static void SetStringRecord_NullEmpty(SqlDataReader dr, string columnName, SqlDataRecord record, int ordinal)
 {
     if (dr[columnName] == DBNull.Value)
         record.SetString(ordinal, "");
     else
         record.SetString(ordinal, Convert.ToString(dr[columnName]));
 }

    private static SqlDataRecord FillRecord(Int32 pk, SqlDataRecord record)
    {
        Int32 age = SlowRandom(16, 99);
        string sourceString = "Age: " + age.ToString();
        DateTime sourceDate = DateTime.UtcNow;

        var data = /*salt + */sourceString;
                
        string key = "Top Secret Key";

        var encData = AES.EncryptBytes(data, key);
        //var encDataBytes = Encoding.Unicode.GetBytes(encData);
        var decData = AES.DecryptBytes(encData, key);

        var sha = new SHA256Managed();
        byte[] dataSHA256 = sha.ComputeHash(encData/*Bytes*/);
        sha.Dispose();

        // конвертирую хеш из byte[16] в строку шестнадцатиричного формата
        // (вида «3C842B246BC74D28E59CCD92AF46F5DA»)
        // это опциональный этап, если вам хеш нужен в строковом виде
        // string sha512hex = BitConverter.ToString(dataSHA512).Replace("-", string.Empty); 

        record.SetInt32(0, pk);
        record.SetDateTime(1, sourceDate);        
        record.SetString(2, sourceString);
        record.SetString(3, Convert.ToBase64String(dataSHA256)); // sha256
        record.SetString(4, Convert.ToBase64String(encData)); // Encrypted
        record.SetString(5, decData); // Decrypted

        return record;
    }

        protected static IEnumerable<SqlDataRecord> GenerateMetaPropertyTable(MetaObject o)
        {
            var metaFields = new List<SqlDataRecord>();

            try
            {
                SqlMetaData[] metaData = new SqlMetaData[2];
                metaData[0] = new SqlMetaData("FieldName", SqlDbType.VarChar, 30);
                metaData[1] = new SqlMetaData("FieldValue", SqlDbType.VarChar, -1);

                foreach (KeyValuePair<string, JToken> prop in o.MetaPropertiesObject)
                {
                    SqlDataRecord record = new SqlDataRecord(metaData);
                    record.SetString(0, prop.Key);
                    // coming from the DB the value will be an object representing the field, with a "Value" key
                    // coming from the client the value will be a single value
                    var value = prop.Value.SelectToken("Value") ?? prop.Value;
                    if (value.Type == JTokenType.Null)
                    {
                        record.SetDBNull(1);
                    }
                    else
                    {
                        record.SetString(1, value.ToString());
                    }
                    metaFields.Add(record);
                }
            }
            catch (Exception e)
            {
            }

            return metaFields;
        }

    public static void IR_SM_AvailableObjective()
    {
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;
            List<SqlCommand> commands = new List<SqlCommand>();

            commands.Add(new SqlCommand("SELECT distinct [BUDGETTYPECODE] as ObjectiveType, [YEAR] as YearT, BRANDCODE as BrandCode FROM [SALESBUDGET] inner join SAPRODUCTS on SAPRODUCTS.saproductcode = salesbudget.saproductcode order by BRANDCODE"));
            commands.Add(new SqlCommand("SELECT distinct [type] as ObjectiveType, [year] as YearT, [brandcode]  as BrandCode FROM [IR_Brand_Budget] order by brandcode"));

            SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ObjectiveType", SqlDbType.NVarChar, 50),
                                    new SqlMetaData("YearT", SqlDbType.Int),
                                    new SqlMetaData("BrandCode", SqlDbType.NVarChar, 50),
                                    new SqlMetaData("TotalProvince", SqlDbType.NVarChar, 50));

            pipe.SendResultsStart(record);

            bool isTotal = true; 
            foreach (SqlCommand cmd in commands)
            {
                try
                {
                    cmd.Connection = con;
                    con.Open();


                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        string objectiveType = Convert.ToString(dr["objectivetype"]);
                        int year = Convert.ToInt32(dr["yeart"]);
                        string brandCode = Convert.ToString(dr["brandcode"]);
                        string totalProvince = (isTotal) ? "T" : "P";

                        record.SetString(0, objectiveType);
                        record.SetInt32(1, year);
                        record.SetString(2, brandCode);
                        record.SetString(3, totalProvince);
                        pipe.SendResultsRow(record);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (con != null)
                        con.Close();
                    isTotal = false;
                }
            }
            pipe.SendResultsEnd();
        }
    }

 public static void IR_SM_PriceList_PerType()
 {
     using (SqlConnection con = new SqlConnection("context connection=true"))
     {
         SqlPipe pipe = SqlContext.Pipe;
         List<SqlCommand> commands = new List<SqlCommand>();
         try
         {
             commands.Add(new SqlCommand("SELECT DISTINCT IR_PriceList.PRICE, IR_PriceList.YEAR AS YearT, IR_PriceList.MONTH AS MonthT, IR_PriceList.SAPRODUCTCODE, IR_PriceList.PRICETYPE FROM IR_PriceList"));
             SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PRICE", SqlDbType.Float),
             new SqlMetaData("MonthT", SqlDbType.Int),
             new SqlMetaData("YearT", SqlDbType.Int),
             new SqlMetaData("SAPRODUCTCODE", SqlDbType.NVarChar, 20),
             new SqlMetaData("PRICETYPE", SqlDbType.NVarChar, 20));
             pipe.SendResultsStart(record);
             foreach (SqlCommand cmd in commands)
             {
                 try
                 {
                     cmd.Connection = con;
                     con.Open();
                     SqlDataReader dr = cmd.ExecuteReader();
                     while (dr.Read())
                     {
                         double price = Convert.ToDouble(dr["PRICE"]);
                         int month = Convert.ToInt32(dr["montht"]);
                         int year = Convert.ToInt32(dr["yeart"]);
                         string saproductcode = Convert.ToString(dr["SAPRODUCTCODE"]);
                         string thePriceType = Convert.ToString(dr["PRICETYPE"]);
                         record.SetDouble(0, price);
                         record.SetInt32(1, month);
                         record.SetInt32(2, year);
                         record.SetString(3, saproductcode);
                         record.SetString(4, thePriceType);
                         pipe.SendResultsRow(record);
                     }
                 }
                 catch (Exception ex)
                 {
                     throw ex;
                 }
                 finally
                 {
                     if (con != null)
                         con.Close();
                 }
             }
             pipe.SendResultsEnd();
         }
         catch (Exception ex)
         {
             throw ex;
         }
     }
 }

 private static void BindFile(FileInfo _file, SqlDataRecord record)
 {
     record.SetSqlDateTime(0, _file.Exists ? _file.CreationTime : SqlDateTime.Null);
     record.SetSqlDateTime(1, _file.Exists ? _file.LastAccessTime : SqlDateTime.Null);
     record.SetSqlDateTime(2, _file.Exists ? _file.LastWriteTime : SqlDateTime.Null);
     record.SetSqlBoolean(3, _file.Exists);
     record.SetString(4, _file.Name);
     record.SetString(5, _file.DirectoryName);
     record.SetString(6, _file.Extension);
     record.SetSqlInt64(7, _file.Exists ? _file.Length : SqlInt64.Null);
 }

        public static void GerarEventoAuditoria(this DbContext context, int codigoTipoEventoAuditoria, Func<int> idUsuarioFunc, params ParametroEvento[] parametrosEvento)
        {
            string siglaSistemaPermisys = ConfigurationManager.AppSettings["Permisys.SiglaSistema"];
            string siglaModuloPermisys = ConfigurationManager.AppSettings["Permisys.SiglaModulo"];

            if (string.IsNullOrWhiteSpace("Permisys.SiglaSistema") || string.IsNullOrWhiteSpace("Permisys.SiglaModulo")) {
                throw new Exception("As configurações \"Permisys.SiglaSistema\" e \"Permisys.SiglaModulo\" são obrigatórias.");
            }

            if (idUsuarioFunc == null) {
                throw new Exception("A função de obtenção do ID do usuário é obrigatória.");
            }

            int idUsuarioPermisys = idUsuarioFunc();

            List<SqlDataRecord> parametros = null;

            if (parametrosEvento != null && parametrosEvento.Count() > 0) {

                parametros = new List<SqlDataRecord>();

                SqlMetaData[] rowMetadata = new SqlMetaData[] {
                    new SqlMetaData("ORDEM", SqlDbType.Int),
                    new SqlMetaData("NOME", SqlDbType.VarChar, 20),
                    new SqlMetaData("VALOR", SqlDbType.VarChar, 100)
                };

                foreach (var parametro in parametrosEvento) {

                    if (string.IsNullOrWhiteSpace(parametro.Nome) || string.IsNullOrWhiteSpace(parametro.Valor)) {
                        throw new Exception("O \"Nome\" e \"Valor\" são obrigatórios para todos os parâmetros.");
                    }

                    SqlDataRecord row = new SqlDataRecord(rowMetadata);
                    row.SetInt32(0, parametro.Ordem);
                    row.SetString(1, parametro.Nome);
                    row.SetString(2, parametro.Valor);
                    parametros.Add(row);
                }
            }

            context.Database.ExecuteSqlCommand("LOGSYS.SP_GERAR_EVENTO_AUDITORIA @SIGLA_SISTEMA_PERMISYS, @SIGLA_MODULO_PERMISYS, @ID_USUARIO_PERMISYS, @CODIGO_TIPO_EVENTO_AUDITORIA, @PARAMETROS_EVENTO",
                new object[] {
                    new SqlParameter("SIGLA_SISTEMA_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaSistemaPermisys },
                    new SqlParameter("SIGLA_MODULO_PERMISYS", SqlDbType.VarChar, 30) { Value = siglaModuloPermisys },
                    new SqlParameter("ID_USUARIO_PERMISYS", SqlDbType.Int) { Value = idUsuarioPermisys },
                    new SqlParameter("CODIGO_TIPO_EVENTO_AUDITORIA", SqlDbType.Int) { Value = codigoTipoEventoAuditoria },
                    new SqlParameter("PARAMETROS_EVENTO", SqlDbType.Structured) { TypeName = "LOGSYS.LOGSYS_LISTA_PARAMETROS", Value = parametros }
                }
            );
        }

    public static void Shop_CalculateRefund(string StoreName, string APIKey, string Password, long OrderID, string JsonString)
    {
        ShopifyClient sp = new ShopifyClient(StoreName, APIKey, Password);
        SqlPipe p = SqlContext.Pipe;

        // Create a new record with the column metadata. The constructor is
        // able to accept a variable number of parameters.
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData[] { new SqlMetaData("ShippingAmount", SqlDbType.NVarChar,18),
            new SqlMetaData("ShippingTax", SqlDbType.NVarChar,18),
            new SqlMetaData("MaximumRefundable", SqlDbType.NVarChar,18),
            new SqlMetaData("RefundLineItems", SqlDbType.Int,4),
            new SqlMetaData("Transactions", SqlDbType.Int,4)}
            );

        Refund r = sp.CalculateRefund(OrderID, JsonString);
        // Mark the begining of the result-set.
        SqlContext.Pipe.SendResultsStart(record);
        // Set the record fields.
        record.SetString(0,r.shipping.amount);
        record.SetString(1, r.shipping.tax);
        record.SetString(2, r.shipping.maximum_refundable);
        record.SetInt32(3, r.refund_line_items.Count);
        record.SetInt32(4, r.transactions.Count);

        //record.SetInt32(1, 42);
        //record.SetDateTime(2, DateTime.Now);

        // Send the row back to the client.
        SqlContext.Pipe.SendResultsRow(record);

        // Mark the end of the result-set.
        SqlContext.Pipe.SendResultsEnd();

        // Send the record to the calling program.
        SqlContext.Pipe.Send(record);
    }

        public void SendRow(SqlDataRecord dataRecord, object[] items)
        {
            for (int i = 0; i < items.Length; i++)
            {
                string value = items[i].ToStringSafe();
                if (string.IsNullOrEmpty(value))
                {
                    dataRecord.SetDBNull(i);
                }
                else
                {
                    dataRecord.SetString(i, value);
                }
            }

            if (SqlContext.IsAvailable && SqlContext.Pipe != null)
            {
                // Send the row back to the client.
                SqlContext.Pipe.SendResultsRow(dataRecord);
            }
        }

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

    public static void Publisher(SqlString hostname, SqlString route, SqlString message)
    {
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("Result", SqlDbType.NVarChar, 512)
            );

        using (Realtime.Publisher.Publisher pub = new Realtime.Publisher.Publisher(new ServerConfig()
        {
            Host = hostname.ToString(),
            Port = 8081,
            Route = route.ToString(),
            Debug = true
        }))
        {
            pub.Publish(message.ToString()
                , () =>
                {
                    record.SetString(0, "Message is correct sended to Server.");

                    SqlContext.Pipe.SendResultsStart(record);
                    SqlContext.Pipe.SendResultsEnd();
                });
        }
    }

        protected List<SqlDataRecord> GenerateStringTableParameterRecords(IEnumerable<string> items)
        {
            SqlMetaData[] tableDefinition = { new SqlMetaData("Id", SqlDbType.NVarChar, 100) };

            return items.Select(item =>
            {
                var record = new SqlDataRecord(tableDefinition);
                record.SetString(0, item);
                return record;
            }).ToList();
        }

    public static void IR_SM_Pos_Objective(string brandCodes, int startMonth, int startYear, int endMonth, int endYear, string dsIds)
    {
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            SqlPipe pipe = SqlContext.Pipe;
            List<SqlCommand> commands = new List<SqlCommand>();
            try
            {
                SqlInt32 smallyear = (startYear < endYear) ? startYear : endYear;
                SqlInt32 bigyear = (startYear > endYear) ? startYear : endYear;

                List<string> brdcods = new List<string>();
                string[] split = brandCodes.Split('-');
                foreach (string s in split)
                    if (s.ToLower().Trim() != "")
                        brdcods.Add(s.Trim().ToLower());

                List<string> dsids = new List<string>();
                string[] split2 = dsIds.Split('-');
                foreach (string s in split2)
                    if (s.ToLower().Trim() != "")
                        dsids.Add(s.Trim().ToLower());

                for (int year = (int)smallyear; year <= bigyear; year++)
                {
                    foreach (string brandCode in brdcods)
                    {
                        foreach (string dsid in dsids)
                        {
                            commands.Add(new SqlCommand("SELECT [Counter], [Year], [Month], [DSID], [BudgetValue], [Brandcode] FROM [IR_POS_OBJ] where DSID = @dsid AND (BRANDCODE LIKE '%' + @brandcode + '%') AND ([YEAR] = @year) AND ([MONTH] BETWEEN @startmonth AND @endmonth)"));
                            commands[commands.Count - 1].Parameters.AddWithValue("@brandcode", brandCode);
                            commands[commands.Count - 1].Parameters.AddWithValue("@startmonth", (year == smallyear) ? startMonth : 1);
                            commands[commands.Count - 1].Parameters.AddWithValue("@endmonth", (year == bigyear) ? endMonth : 12);
                            commands[commands.Count - 1].Parameters.AddWithValue("@year", year);
                            commands[commands.Count - 1].Parameters.AddWithValue("@dsid", dsid);
                        }
                    }
                }

                SqlDataRecord record = new SqlDataRecord(new SqlMetaData("ID", SqlDbType.Int),
                                       new SqlMetaData("DSID", SqlDbType.Int),
                                       new SqlMetaData("MonthT", SqlDbType.Int),
                                       new SqlMetaData("YearT", SqlDbType.Int),
                                       new SqlMetaData("VAL", SqlDbType.Float),
                                       new SqlMetaData("BrandCode", SqlDbType.NVarChar, 255));

                pipe.SendResultsStart(record);
                int idCounter = 1;
                foreach (SqlCommand cmd in commands)
                {
                    try
                    {
                        cmd.Connection = con;
                        con.Open();

                        SqlDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            
                            int dsid = Convert.ToInt32(dr["DSID"]);
                            int month = Convert.ToInt32(dr["month"]);
                            int year = Convert.ToInt32(dr["year"]);
                            double val = Convert.ToDouble(((dr["BudgetValue"] == DBNull.Value) ? 0 : dr["BudgetValue"]));
                            string brandcode = Convert.ToString(dr["Brandcode"]);
                            record.SetInt32(0, idCounter);
                            record.SetInt32(1, dsid);
                            record.SetInt32(2, month);
                            record.SetInt32(3, year);
                            record.SetDouble(4, val);
                            record.SetString(5, brandcode);
                            pipe.SendResultsRow(record);
                            idCounter++;
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (con != null)
                            con.Close();
                    }
                }
                pipe.SendResultsEnd();

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

        public static void CopyBlob(
            SqlString destinationAccount, SqlString destinationSharedKey, SqlBoolean useHTTPS,
            SqlString sourceAccountName,
            SqlString sourceContainerName, SqlString sourceBlobName,
            SqlGuid sourceLeaseId, SqlGuid destinationLeaseId,
            SqlString destinationContainerName, SqlString destinationBlobName,
            SqlString xmsclientrequestId)
        {
            AzureBlobService absDest = new AzureBlobService(destinationAccount.Value, destinationSharedKey.Value, useHTTPS.Value);
            Container contDest = absDest.GetContainer(destinationContainerName.Value);
            ITPCfSQL.Azure.Blob bbDest = new Azure.Blob(contDest, destinationBlobName.Value);

            AzureBlobService absSrc = new AzureBlobService(sourceAccountName.Value, "", useHTTPS.Value);
            Container contSrc = absSrc.GetContainer(sourceContainerName.Value);
            ITPCfSQL.Azure.Blob bbSrc = new Azure.Blob(contSrc, sourceBlobName.Value);

            Responses.CopyBlobResponse resp = bbSrc.Copy(bbDest,
                sourceLeaseID: sourceLeaseId.IsNull ? (Guid?)null : sourceLeaseId.Value,
                destinationLeaseID: destinationLeaseId.IsNull ? (Guid?)null : destinationLeaseId.Value,
                xmsclientrequestId: xmsclientrequestId.IsNull ? null : xmsclientrequestId.Value);

            SqlDataRecord record = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("BlobCopyStatus", System.Data.SqlDbType.NVarChar, 255),
                    new SqlMetaData("CopyId", System.Data.SqlDbType.NVarChar, 255),
                    new SqlMetaData("Date", System.Data.SqlDbType.DateTime),
                    new SqlMetaData("ETag", System.Data.SqlDbType.NVarChar, 255),
                    new SqlMetaData("LastModified", System.Data.SqlDbType.DateTime),
                    new SqlMetaData("RequestID", System.Data.SqlDbType.UniqueIdentifier),
                    new SqlMetaData("Version", System.Data.SqlDbType.NVarChar, 255)
                });

            SqlContext.Pipe.SendResultsStart(record);

            record.SetString(0, resp.BlobCopyStatus.ToString());
            record.SetString(1, resp.CopyId);
            record.SetDateTime(2, resp.Date);
            record.SetString(3, resp.ETag);
            record.SetDateTime(4, resp.LastModified);
            record.SetGuid(5, resp.RequestID);
            record.SetString(6, resp.Version);

            SqlContext.Pipe.SendResultsRow(record);
            SqlContext.Pipe.SendResultsEnd();
        }

    public static void CreateNewRecordProc()
    {
        DateTime now = DateTime.UtcNow;

        SqlDataRecord record = new SqlDataRecord(new SqlMetaData("PK", SqlDbType.Int),
            new SqlMetaData("UTC_DateTime", SqlDbType.DateTime),
            new SqlMetaData("Source", SqlDbType.NVarChar, 128),
            new SqlMetaData("Encrypted_SHA256", SqlDbType.NVarChar, 32),
            new SqlMetaData("Encrypted_AES", SqlDbType.NVarChar, 512),
            new SqlMetaData("Decrypted", SqlDbType.NVarChar, 128));

        SqlContext.Pipe.SendResultsStart(record);

        for (int i = 0; i < SlowRandom(1, 50); i++)
        {
            SqlContext.Pipe.SendResultsRow(FillRecord(i, record));
        }

        TimeSpan delta = DateTime.UtcNow - now;

        record.SetInt32(0, 0);
        record.SetDateTime(1, DateTime.UtcNow);
        record.SetString(2, "Total ms:");
        record.SetString(3, delta.Milliseconds.ToString());
        record.SetString(4, ""); 
        record.SetString(5, ""); 

        SqlContext.Pipe.SendResultsRow(record);

        SqlContext.Pipe.SendResultsEnd();
    }