fang1004tw

簡單的SAMPLE 
       [WebMethod]
        public string ExecSql(string pwd,string DataSource, string EncodeSQL) {
            if (pwd!="XXXXXXXX")
            {
                return "";
            }

            try
            {
                ClickOnceTest.WebServiceEncodeLib cs = new ClickOnceTest.WebServiceEncodeLib();
                string sSQL = cs.Decompress(EncodeSQL);

                string sConnStr = ConfigurationManager.ConnectionStrings[DataSource].ConnectionString;             
                OleDbConnection conn = new OleDbConnection(sConnStr);
                conn.Open();

                OleDbCommand cmd = new OleDbCommand(sSQL, conn);
                string rtn = cmd.ExecuteNonQuery().ToString();

                cmd.Dispose();
                conn.Close();

                return rtn;
            }
            catch (Exception e)
            {
                return e.Message;                
            }            
        }

        [WebMethod]
        public string getDataSet(string pwd, string DataSource, string EncodeSQL)
        {
            string cs_sqlstring = ConfigurationManager.AppSettings["SAVE_EFFICACY_DATA"];


            if ((DataSource == "XXXXX") && (cs_sqlstring=="TRUE"))
            {
                return getDataSetTest(pwd, DataSource, EncodeSQL);
            }
            else
            {
                return getDataSetLIVE(pwd, DataSource, EncodeSQL);
            }
        }

        static public string quotoStr(object o)
        {
            return "'" + o.ToString().Replace("'", "''").Trim() + "'";
        }

        private string getDataSetTest(string pwd, string DataSource, string EncodeSQL)
        {
            DateTime Rec_Time = DateTime.Now; //接收時間
            int Rec_Size = EncodeSQL.Length; //接收資料大小
            string sSQL = "";
            if (pwd != "XXXXXXX")
            {
                return "";
            }

            try
            {
                string sConnStr = ConfigurationManager.ConnectionStrings[DataSource].ConnectionString;

                OleDbConnection conn = new OleDbConnection(sConnStr);

                ClickOnceTest.WebServiceEncodeLib cs = new ClickOnceTest.WebServiceEncodeLib();

                sSQL = cs.Decompress(EncodeSQL);

                int rec_unzip_size = sSQL.Length; // 接收資料解壓縮後大小

                conn.Open();

                //System.IO.File.WriteAllText("C://Inetpub//wwwroot//webservice//WH//WH_Proj//log.txt", sSQL);

                DateTime START_QUERY = DateTime.Now; //效能統計用

                OleDbDataAdapter da = new OleDbDataAdapter(sSQL, conn);

                DateTime END_QUERY = DateTime.Now;//效能統計用

                DataSet ds = new DataSet();

                da.Fill(ds);

                DateTime FILL_DATASET = DateTime.Now;//效能統計用

                string s = ds.Tables[0].TableName;
                System.IO.MemoryStream sm = new System.IO.MemoryStream();
                ds.WriteXml(sm, XmlWriteMode.WriteSchema); //產生XML stream

                DateTime CHANGE_XML = DateTime.Now;//效能統計用

                sm.Position = 0;
                System.IO.StreamReader reader = new System.IO.StreamReader(sm);
                string text = reader.ReadToEnd();

                int RTN_SIZE = text.Length;//資料大小

                string rtn = cs.Compress(text);  //壓縮後編碼轉成字串

                DateTime COMPRESS = DateTime.Now;//效能統計用

                int RTN_ZIP_SIZE = rtn.Length;//壓完大小

                ds.Dispose();
                da.Dispose();

                DateTime Rtn_Time = DateTime.Now; //最後資料發出時間


                string query_time = (END_QUERY - START_QUERY).Seconds.ToString() +"."+ (END_QUERY - START_QUERY).Milliseconds.ToString();
                string fill_ds_time = (FILL_DATASET - END_QUERY).Seconds.ToString() +"."+ (FILL_DATASET - END_QUERY).Milliseconds.ToString();
                string to_xml_time = (CHANGE_XML - FILL_DATASET).Seconds.ToString() +"."+ (CHANGE_XML - FILL_DATASET).Milliseconds.ToString();
                string zip_time = (Rtn_Time - CHANGE_XML).Seconds.ToString() +"."+ (Rtn_Time - CHANGE_XML).Milliseconds.ToString();

                sSQL = "INSERT INTO web_service_efficacy (REC_TIME, REC_SIZE , Rec_Unzip_Size , Rtn_Time , Rtn_Size , Rtn_Zip_Size,"
                       + " query_time,fill_ds_time,to_xml_time,zip_time)values "
                       + " ( TO_DATE('" + Rec_Time.ToString("yyyyMMddHHmmss") + "','yyyymmddHH24MISS') ," + Rec_Size.ToString()
                       + " , " + rec_unzip_size.ToString() + " , TO_DATE('" + Rtn_Time.ToString("yyyyMMddHHmmss") + "','yyyymmddHH24MISS')"
                       + " , " + RTN_SIZE.ToString() + " ," + RTN_ZIP_SIZE.ToString()
                       + "," + query_time + "," + fill_ds_time + "," + to_xml_time + "," + zip_time + " )";

                OleDbCommand cmd = new OleDbCommand(sSQL, conn);
                cmd.ExecuteNonQuery().ToString();
                cmd.Dispose();

                conn.Close();

                return rtn;
            }
            catch (Exception e)
            {
                return e.Message + ";" + sSQL;
            }
        }

        private string getDataSetLIVE(string pwd, string DataSource, string EncodeSQL)
        {
            if (pwd != "XXXXXX")
            {
                return "";
            }

            try
            {
                string sConnStr = ConfigurationManager.ConnectionStrings[DataSource].ConnectionString;
                OleDbConnection conn = new OleDbConnection(sConnStr);
                ClickOnceTest.WebServiceEncodeLib cs = new ClickOnceTest.WebServiceEncodeLib();
                string sSQL = cs.Decompress(EncodeSQL);
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sSQL, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                string s = ds.Tables[0].TableName;
                System.IO.MemoryStream sm = new System.IO.MemoryStream();
                ds.WriteXml(sm, XmlWriteMode.WriteSchema); //產生XML stream
                sm.Position = 0;
                System.IO.StreamReader reader = new System.IO.StreamReader(sm);
                string text = reader.ReadToEnd();
                string rtn = cs.Compress(text);  //壓縮後編碼轉成字串
                ds.Dispose();
                da.Dispose();
                conn.Close();
                return rtn;
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }