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

        }



      

 /// <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;

        }