C# 代码片段

 

取字符串拼音首母

class PYHelper
    {
        public static string GetPY(string s)
        {
            string r = "";
            foreach (char c in s.ToCharArray())
            {
                r += GetPYChar(c.ToString());
            }
            return r;
        }

        //http://v.163.com/zongyi/V7TFMDFS0/V8PPTEPKJ.html#fr=email
        /// <summary>
        /// 取单个字符的拼音声母
        /// Code By MuseStudio@hotmail.com
        /// 2004-11-30
        /// </summary>
        /// <param name="c">要转换的单个汉字</param>
        /// <returns>拼音声母</returns>
        private static string GetPYChar(string c)
        {
            byte[] array = new byte[2];
            array = System.Text.Encoding.Default.GetBytes(c);
            int i = (short)(array[0] - '\0') * 256 + ((short)(array[1] - '\0'));

            if (i < 0xB0A1) return "*";
            if (i < 0xB0C5) return "a";
            if (i < 0xB2C1) return "b";
            if (i < 0xB4EE) return "c";
            if (i < 0xB6EA) return "d";
            if (i < 0xB7A2) return "e";
            if (i < 0xB8C1) return "f";
            if (i < 0xB9FE) return "g";
            if (i < 0xBBF7) return "h";
            if (i < 0xBFA6) return "g";
            if (i < 0xC0AC) return "k";
            if (i < 0xC2E8) return "l";
            if (i < 0xC4C3) return "m";
            if (i < 0xC5B6) return "n";
            if (i < 0xC5BE) return "o";
            if (i < 0xC6DA) return "p";
            if (i < 0xC8BB) return "q";
            if (i < 0xC8F6) return "r";
            if (i < 0xCBFA) return "s";
            if (i < 0xCDDA) return "t";
            if (i < 0xCEF4) return "w";
            if (i < 0xD1B9) return "x";
            if (i < 0xD4D1) return "y";
            if (i < 0xD7FA) return "z";
            return "*";
        }

 获得工作日天数

class DateHelper
    {
        public static int GetWorkingDays(DateTime from, DateTime to)
        {
            var totalDays = 0;
            for (var date = from; date < to; date = date.AddDays(1))
            {
                if (date.DayOfWeek != DayOfWeek.Saturday
                    && date.DayOfWeek != DayOfWeek.Sunday)
                    totalDays++;
            }

            return totalDays;
        }

        public static int GetWorkingDays1(DateTime from, DateTime to)
        {
            var dayDifference = (int)to.Subtract(from).TotalDays + 1;
            return Enumerable
                .Range(0, dayDifference)
                .Select(x => from.AddDays(x))
                .Where(x => x.DayOfWeek != DayOfWeek.Saturday && x.DayOfWeek != DayOfWeek.Sunday)
                .Count();
        }
    }

 XML 格式化 C#

/// <summary>
/// XML格式化为文本显示
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static string ShowXml(string str)
{
    MemoryStream mstream = new MemoryStream();
    XmlTextWriter writer = new XmlTextWriter(mstream, null);
    XmlDocument xmldoc = new XmlDocument();
    writer.Formatting = Formatting.Indented;


    xmldoc.LoadXml(str);
    xmldoc.WriteTo(writer);
    writer.Flush();
    writer.Close();


    Encoding encoding = Encoding.GetEncoding("utf-8");
    string strReturn = encoding.GetString(mstream.ToArray());
    mstream.Close();
    return strReturn;
}

 大数据表删除清理

/*
Step 1：导出符合清理条件数据，主键insert临时表
*/
--RFC_变更号_操作编号
--取需要清理的TmpOrderID
SELECT  a.tmporderid
INTO    #temp_tmporderid
FROM    dbo.sales_salesorder a ( NOLOCK )
        LEFT JOIN o_pkgorder b ( NOLOCK ) ON a.tmporderid = b.tmporderid
WHERE   b.tmporderid IS NULL

/*
Step 2：临时表创建索引
*/
CREATE CLUSTERED INDEX IX_TmpOrderID ON #TEMP_TmpOrderID (TmpOrderID)

/*
Step 3：申明表变量存放每批次数据
*/
DECLARE @TMP_StatusLog TABLE ( TmpOrderID INT )

/*
Step 4：小批量事务循环删除
*/
WHILE EXISTS ( SELECT TOP 1
                        *
               FROM     #TEMP_TmpOrderID ) 
    BEGIN
        BEGIN TRY
            DELETE  FROM @TMP_StatusLog
    --取100条数据
            INSERT  INTO @TMP_StatusLog
                    SELECT TOP 100
                            *
                    FROM    #TEMP_TmpOrderID

            BEGIN TRAN
        --BACKUP TO TABLE
            INSERT  INTO dbo.Sales_SalesOrder_bak
                    SELECT  *
                    FROM    dbo.Sales_SalesOrder
                    WHERE   TmpOrderID IN ( SELECT  TmpOrderID
                                            FROM    @TMP_StatusLog )   



--DELETE 
            DELETE  FROM dbo.Sales_SalesOrder
            WHERE   TmpOrderID IN ( SELECT  TmpOrderID
                                    FROM    @TMP_StatusLog )   

            DELETE  FROM #TEMP_TmpOrderID
            WHERE   TmpOrderID IN ( SELECT  TmpOrderID
                                    FROM    @TMP_StatusLog )

            COMMIT

        END TRY
        BEGIN CATCH
            IF ( @@TRANCOUNT > 0 ) 
                BEGIN 
                    SELECT  ERROR_NUMBER() AS ErrorNumber ,
                            ERROR_SEVERITY() AS ErrorSeverity ,
                            ERROR_STATE() AS ErrorState ,
                            ERROR_PROCEDURE() AS ErrorProcedure ,
                            ERROR_LINE() AS ErrorLine ,
                            ERROR_MESSAGE() AS ErrorMessage
           --回滚
                    ROLLBACK
           --exit
                    BREAK
                END
            ELSE 
                IF ( @@TRANCOUNT = 0 ) 
                    BEGIN
                        SELECT  ERROR_NUMBER() AS ErrorNumber ,
                                ERROR_SEVERITY() AS ErrorSeverity ,
                                ERROR_STATE() AS ErrorState ,
                                ERROR_PROCEDURE() AS ErrorProcedure ,
                                ERROR_LINE() AS ErrorLine ,
                                ERROR_MESSAGE() AS ErrorMessage
--exit
                        BREAK
                    END
        END CATCH
    END

--删除临时表
DROP TABLE #TEMP_TmpOrderID

 获取存储过程

SELECT  pr.name ,
        p.* ,
        t.name ,
        t.max_length
FROM    sys.procedures pr
        INNER JOIN sys.parameters p ON pr.object_id = p.object_id
        INNER JOIN sys.types t ON p.system_type_id = t.system_type_id

SELECT  pr.name ,
        p.*
FROM    sys.procedures pr
        INNER JOIN sys.parameters p ON pr.object_id = p.object_id

SELECT  *
FROM    INFORMATION_SCHEMA.PARAMETERS

SQL十进制转二进制和十六的两个函数

DECLARE @i INT
DECLARE @j FLOAT
DECLARE @m INT
DECLARE @OUT1 VARCHAR(1)
DECLARE @OUT2 VARCHAR(20)

SET @i = 22
SET @out2 = ' '
WHILE @i >= 1 
    BEGIN 
        SET @j = @i / 2
        SET @m = @i % 2
        SET @i = FLOOR(@j)
        SET @OUT1 = CAST(@m AS CHAR(1))
        SET @OUT2 = @OUT1 + @OUT2
    END
    
PRINT @OUT2
    
    
   -- 十进制转二进制和十六的两个函数
--A.调用测试:
--Update MySheet1 set 二进制=  dbo.inttobit(十进制)
--Update MySheet2 set 十六进制=  dbo.inttohex(十进制)
--B.函数内容
--十进制转二进制函数
CREATE FUNCTION dbo.inttobit ( @number INT )
RETURNS VARCHAR(100)
AS 
    BEGIN
        DECLARE @i INT
        DECLARE @j FLOAT
        DECLARE @m INT
        DECLARE @OUT1 VARCHAR(1)
        DECLARE @OUT2 VARCHAR(20)
        SET @i = @number
        SET @out2 = ' '
        WHILE @i >= 1 
            BEGIN 
                SET @j = @i / 2
                SET @m = @i % 2
                SET @i = FLOOR(@j)
                SET @OUT1 = CAST(@m AS CHAR(1))
                SET @OUT2 = @OUT1 + @OUT2
            END
        RETURN @OUT2
    END 

--十进制转十六进制函数
CREATE   FUNCTION dbo.inttohex ( @i INT )
RETURNS VARCHAR(15)
    BEGIN 
--declare @i int 
--set @i=11259375 
        DECLARE @r VARCHAR(10)   
        SET @r = '' 
        WHILE @i / 16 > 0 
            BEGIN 
                SET @r = ( CASE WHEN ( @i % 16 ) <= 9
                                THEN CONVERT(VARCHAR(1), @i % 16)
                                WHEN ( @i % 16 ) = 10 THEN 'A'
                                WHEN ( @i % 16 ) = 11 THEN 'B'
                                WHEN ( @i % 16 ) = 12 THEN 'C'
                                WHEN ( @i % 16 ) = 13 THEN 'D'
                                WHEN ( @i % 16 ) = 14 THEN 'E'
                                WHEN ( @i % 16 ) = 15 THEN 'F'
                           END ) + @r 
--select @r,@i 
                SET @i = @i / 16 
            END 
--select @r,@i 
        IF @i > 0 
            SET @r = ( CASE WHEN ( @i % 16 ) <= 9
                            THEN CONVERT(VARCHAR(1), @i % 16)
                            WHEN ( @i % 16 ) = 10 THEN 'A'
                            WHEN ( @i % 16 ) = 11 THEN 'B'
                            WHEN ( @i % 16 ) = 12 THEN 'C'
                            WHEN ( @i % 16 ) = 13 THEN 'D'
                            WHEN ( @i % 16 ) = 14 THEN 'E'
                            WHEN ( @i % 16 ) = 15 THEN 'F'
                       END ) + @r 
-- select @r 
        RETURN @r 
    END