資料用Stored_Procedure來抓

namespace SB_XML
{
public partial class SB_XML : Form
{
//這是個有效率的字串組合工具
StringBuilder sb = new StringBuilder();

public SB_XML()
{
InitializeComponent();
}

private void btn_SB_Click(object sender, EventArgs e)
{

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{

StreamReader sr = new StreamReader(openFileDialog1.FileName, Encoding.UTF8);

//去讀字元,讀不到的話就是-1
while (sr.Peek() != -1)
{
//把讀到的一整行東西附加到sb這個StringBuilder上面
sb.Append(sr.ReadLine());

//讀完後加入一個換行符號進去,這是根據不同環境的電腦來
//插入不同的換行符號,如果用\n的話在寫出時會有問題顯示不出來換行
sb.Append(Environment.NewLine);
}

//用richtext控制項才能顯示出XML的換行,textbox不行,memoedit好像也
//不行(可能需要設定吧)
richTextContent.Text = sb.ToString();
sr.Dispose();
}
}

private void btn_GetDB_Click(object sender, EventArgs e)
{

string mystr="";

using (SqlConnection cn = new SqlConnection())
{


SqlCommand cmd = new SqlCommand();

cn.ConnectionString = ConfigurationManager.ConnectionStrings["A_DB"].ConnectionString.ToString();

cn.Open();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_A_AD_Query";

//記得這是把StoreProcedure裡面的變數型態等等資料拿來用,就不用再宣告一次吧
SqlCommandBuilder.DeriveParameters(cmd);


cmd.Parameters["@F_Language"].Value = ConfigurationManager.AppSettings["Language"];
cmd.Parameters["@F_Company_ID"].Value=ConfigurationManager.AppSettings["CompanyID"];
cmd.Parameters["@F_Type"].Value = "A";


SqlDataReader dr = cmd.ExecuteReader();


while(dr.Read())
{
byte[] imageData = (byte[])dr["F_Icon"];

string dirpath = "";

string fpath = "";


/*這邊再把/DataSource/Company/' + a.F_Company_ID + '/AD/' +
F_Type + '/' + F_ID + '.jpg'這種/的路徑轉成電腦檔案系統裡的
\eSystem\eConsole這種斜線的路徑 */

dirpath = dr["F_Image"].ToString().Replace("~", "");

dirpath = dirpath.Replace(@"/",@"\");

fpath = dirpath;


/*下面這兩行是要把剛剛那個路徑最後的+ F_ID + '.jpg這個句子去
掉,因為要創建”資料夾”不用用到這個檔案的路徑 */
int li = dirpath.LastIndexOf(@"\");
dirpath=dirpath.Substring(0, li);

//設定要創見資料夾的路徑,一路上的所有資料夾全部都會產生
DirectoryInfo dir = new DirectoryInfo(Application.StartupPath+"..\\..\\.."+dirpath);

//開始創立資料夾
dir.Create();

//直接把二進位檔寫出成一個jpg檔案 File.WriteAllBytes(Application.StartupPath+"..\\..\\.."+fpath, imageData);




//這邊要組字串去取代XML裡的我打的you_can_replace_me這個句子
mystr=mystr+string.Format(" +" target=\"blank\" stay=\"3\">",
dr["F_Image"].ToString(), dr["F_Link"].ToString(),dr["F_Title"].ToString());

mystr += Environment.NewLine;
mystr += Environment.NewLine;

}

cn.Close();

}
//開始取代
sb.Replace("you_can_replace_me",mystr);

richTextContent.Text = sb.ToString();
}

private void btn_Save_Click(object sender, EventArgs e)
{
saveFileDialog1.FileName = "advanced-xml-banner-rotator-04.xml";

if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{ //如果沒有指定副檔名,預設就用.xml當副檔名
saveFileDialog1.AddExtension = true;
saveFileDialog1.DefaultExt = ".xml";

//寫出修改後的XML檔到路徑的XML檔
FileInfo file = new FileInfo(saveFileDialog1.FileName);
StreamWriter sw = file.CreateText();
sw.Write(sb);
sw.Flush();
sw.Close();
}
}
}
}




//這裡是用到的預存程序
ALTER PROCEDURE dbo.[sp_A_AD_Query]
(
@F_Language as varchar(20),
@F_Company_ID as varchar(20),
@F_Type as varchar(20)
)
as
begin

select
F_No = row_number() Over(Order by a.F_Type asc, a.F_ID asc),

F_Icon = a.F_Icon,
F_Type = a.F_Type,
F_ID = a.F_ID,
F_Title = a.F_Title,
F_Link = a.F_URL,
F_Image = '/DataSource/Company/' + a.F_Company_ID + '/AD/' + F_Type + '/' + F_ID + '.jpg'
from A_AD a
where a.F_Stat = '1'
--and a.F_Company_ID = @F_Company_ID
order by a.F_Type asc, a.F_Priority asc

end








//下面這個是要置換的XML檔,我把要置換的部分換成you_can_replace_me這個句子,到時只要置換這個句子就好
<bannerRotator>


<settings>

<totalWidth>590</totalWidth>
<totalHeight>300</totalHeight>

<blurX>20</blurX>
<blurY>0</blurY>
<blurQuality>2</blurQuality>

<slideAnimationTime>0.5</slideAnimationTime>
<slideAnimationType>easeOutCirc</slideAnimationType>

<descriptionBlurX>10</descriptionBlurX>
<descriptionBlurY>10</descriptionBlurY>
<descriptionBlurQuality>2</descriptionBlurQuality>

</settings>

<content>
you_can_replace_me
</content>


</bannerRotator>










//下面這個XML檔是已經置換過後的樣子
<bannerRotator>


<settings>

<totalWidth>590</totalWidth>
<totalHeight>300</totalHeight>

<blurX>20</blurX>
<blurY>0</blurY>
<blurQuality>2</blurQuality>

<slideAnimationTime>0.5</slideAnimationTime>
<slideAnimationType>easeOutCirc</slideAnimationType>

<descriptionBlurX>10</descriptionBlurX>
<descriptionBlurY>10</descriptionBlurY>
<descriptionBlurQuality>2</descriptionBlurQuality>

</settings>

<content>
<slide src="/DataSource/Company/A207/AD/A/A207_AD_000001.jpg" url="http://www.yahoo.com" target="blank" stay="3"><![CDATA[Hello,I am 5]]></slide>

<slide src="/DataSource/Company/A207/AD/A/A207_AD_000002.jpg" url="http://www.yahoo.com" target="blank" stay="3"><![CDATA[Hello,I am 6]]></slide>

<slide src="/DataSource/Company/A207/AD/B/A207_AD_000003.jpg" url="http://www.yahoo.com" target="blank" stay="3"><![CDATA[Hello,I am 7]]></slide>

<slide src="/DataSource/Company/A207/AD/B/A207_AD_000004.jpg" url="http://www.yahoo.com" target="blank" stay="3"><![CDATA[Hello,I am 8]]></slide>

<slide src="/DataSource/Company/A207/AD/B/A207_AD_000005.jpg" url="http://www.udn.com" target="blank" stay="3"><![CDATA[Hello,I am 9]]></slide>


</content>


</bannerRotator>

welkingunther 發表在 痞客邦 PIXNET 留言(0) 人氣()