分享SQL操作类 本类的特色:可以象在SQL客户端那样直接输入存储过程和参数调用存储过程

[www7788softcom]

分享
http://blog.csdn.net/soft7788/article/details/9748805

[www7788softcom]

namespace www7788softcom
{
    /// <summary>
    /// sqlserver操作类
    /// </summary>
    public  class Sql78
    {
        private string _SQLconstring;

        private InfoTable procInfoTable;
        /// <summary>
        /// 数据源的名称(系统内唯一）
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 是否保存修改备自动更新(下版取消)
        /// </summary>
        public bool bSave { get; set; }
        /// <summary>
        /// 会话ID
        /// </summary>
        public string sid { get; set; }
        /// <summary>
        /// 直接执行sql密码
        /// </summary>
        public string pwd { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public Sql78()
        {
            bSave = true;
            Name = "";
            pwd = Guid.NewGuid().ToString();//默认不直接执行SQL语句
        }

        /// <summary>
        /// 获取SQL连接
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetCon()
        {
            return new SqlConnection(_SQLconstring);
        }

      

[www7788softcom]

见鬼 太长了发不了

 /// <summary>
        /// “查询”执行存储过程返回JSONStream
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public Stream GetJsonStream(string procName, params object[] parms)
        {
            MemoryStream ms = new MemoryStream( );
            StreamWriter sw = new StreamWriter(ms, Encoding.UTF8);
        
            bool b2 = false;//第二行开始加,

            string[] paramInfo = (string[])(procInfoTable[procName]);
            if (paramInfo == null)
            {
                sw.Write("存储过程不存在");
                sw.Flush();
                ms.Seek(0, SeekOrigin.Begin);
                //string back = Encoding.Default.GetString(ms.ToArray());
                return ms;
            }
            using (SqlConnection sqlcon = GetCon())
            {
                SqlCommand sc = new SqlCommand(procName, sqlcon) { CommandType = CommandType.StoredProcedure };
                for (int i = 0; i < parms.Length && i < paramInfo.Length; ++i)
                {
                    sc.Parameters.AddWithValue(paramInfo[i], parms[i]);
                }
                if (sc.Connection.State != ConnectionState.Open)
                    sc.Connection.Open();
                try
                {
                    using (SqlDataReader reader = sc.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        sw.Write("{\"tb\":[");
                        while (reader.Read())
                        {
                            if (b2)
                                sw.Write(",");
                            else
                                b2 = true;
                            sw.Write("{");
                            for (int j = 0; j < reader.FieldCount; j++)
                            {
                                sw.Write("\"" + reader.GetName(j)  + "\":" + "\"" + reader.GetValue(j).ToString().Replace("\"", "\'") + "\"");
                                if (j < reader.FieldCount - 1)
                                    sw.Write(",");
                            }
                            sw.Write("}");
                            sw.Flush();
                        }
                        reader.Close();
                        sw.Write("]}");

                        sw.Flush();
                    }
                }
                catch(Exception ex)
                {
                    sw.Write(ex.Message);
                    sw.Flush();
                }
                finally
                {
                    sc.Parameters.Clear();
                    sc.Dispose();
                    sqlcon.Close();
                }
            }
         
            ms.Seek(0, SeekOrigin.Begin);
            //string back = Encoding.Default.GetString(ms.ToArray());
            return ms;
        }