SqlServer，DBF两种格式的转化问题做个总结

这里，就SqlServer，DBF两种格式的转化问题做个总结。
 
一： 从dBase文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
 
/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
 
--如果导入数据并生成表
select * into 表 from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
 

/*===================================================================*/
--如果从SQL数据库中,导出数据到dBase,如果dBase文件已经存在,就可以简单的用:
insert into
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:\','select * from [test.dbf]')
select * from 表
 

/*--说明:
DATABASE=c:\               c:\是dbf文件的存放目录
'select * from [test.dbf]  test.dbf是指dbf文件名
--*/
 
 
 
二 如果dBase文件不存在,就需要用到下面的存储过程了.
 
 
 
/*--数据导出dBase
 
 导出表中的数据到dBase,如果文件不存在,将自动创建文件
 基于通用性考虑,仅支持导出标准数据类型
--*/
 
/*--调用示例
 
 --导出dBase
 p_exporttb @tbname='地区资料',@path='c:\',@over=0
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
 
create proc p_exporttb
@tbname sysname,    --要导出的表名
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250)='',  --文件名,默认为表名
@over bit=0      --是否覆盖已经存在的文件,如果不覆盖,则直接追加
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
 
--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.dbf'
 
--检查文件是否已经存在
if right(@path,1)<>'\' set @path=@path+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
 if @over=1
 begin
  set @sql='del '+@sql
  exec master..xp_cmdshell @sql,no_output
 end
 else
  set @over=0
else
 set @over=1
 
--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
 +';HDR=NO;DATABASE='+@path+'"'
 
--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
 
exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr
 
--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
 ,@sql=@sql+',['+a.name+'] '
  +case when b.name in('char','nchar','varchar','nvarchar') then
     'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')'
   when b.name in('tynyint','int','bigint','tinyint') then 'int'
   when b.name in('smalldatetime','datetime') then 'datetime'
   when b.name in('money','smallmoney') then 'money'
   else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
 and object_id(@tbname)=id
select @sql='create table ['+@fname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)
 
if @over=1
begin
 exec @err=sp_oamethod @obj,'execute',@out out,@sql
 if @err<>0 goto lberr
end
 
exec @err=sp_oadestroy @obj
 
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
 +@path+''',''select * from ['+@fname+']'')'
 
--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
 
return
 
lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist
 
go

两个很常用的存储过程 

1 用于产生10条评论数据

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/**//*存储过程，用于产生10条评论数据*/

-- -- -- -- delete from reviews where operateID>15
-- -- -- --
-- -- -- -- delete from contentinfos where ObjectType=5

ALTER       proc Create100Comments
as
declare @i int
declare @ContentID bigint
set @i=1
while @i<=10
begin

        BEGIN TRAN
         if( @@error != 0 )
            goto ErrorHandler
        commit tran

    INSERT INTO [ContentInfos]
        ( CategoryID,Source,
                    ObjectType,ClickCount,
                    ReviewCount,Grade,
                    VoteCount,CommendCount,
                    AuthorID,BlogID,
                    Size,CollectionTime,
                    IsActive,Keyword,
                    CollectionUser,OriginalID,
                    OriginalURL,F1,
                    F2,F3,
                    F4)
    VALUES
        ( 15,'',
                    5,100,
                    100,1000,
                    100,99,
                    54,204562,
                    10000,getdate(),
                    0,'',
                    'testuser',999,
                    'testurl',100,
                    100,'',
                    '')

    SET @ContentID = IDENT_CURRENT('ContentInfos')
    INSERT INTO  Reviews (CellID ,  PortalID ,  UserID ,  ContentID ,  OperateTitle ,  OperateContent ,  OperateDateTime ,  BlogID ,  BlogTitle ,  OperateUserID ,  UserName ,  UserNick ,  CBlogName ,  OperateEmail ,  OperateIP ,  IsAnonymous ,  IsActive ,  OperateHomePage ,  OperateType ,  OperateValue )
        VALUES(@i, @i, @i, @ContentID, '操作标题', '评论内容1<br>评论内容2<br>评论内容1<br><br><br>< br>评论内容2<br><br><br><br><br>评论内容2< br><br><br><br><br>评论内容4<br>', getdate(), @i, 'Blog标题', @i, '用户名称', '用户呢称', 'Blog名称', 'test@126.com', '124.458.135.500', 0, 1, 'http://www.blogcn.com', 1, @i)

          
    if( @@error != 0 )
        goto ErrorHandler

ErrorHandler:
    if( @@error != 0 )
    begin
        rollback tran
    end
set @i=@i+1

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

2 产生分页的存储过程

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/**//****** Object:  Stored Procedure dbo.CPP_GetTheLastSchoolsWithPage   Script Date: 2006-5-11 10:40:08 ******/

/**//* ##SUMMARY 查询一个学校的话题或者活动等 */

-- ##REMARKS Authors :wht    Date:2006-6-5
-- ##PARAM @PageSize     页大小          整型INT
-- ##PARAM @PageIndex    页索引       整型INT
-- ##PARAM @RowCount     总记录数          整型INT

 

--CPP_GetSchoolThemeWithPageNew 10,0,100

create procedure CPP_Getthesis_thesisWithPageNew
(
   @PageSize       INT,
   @PageIndex      INT,
   @RowCount       INT
   ----@whereClauses varchar(1000)
)

AS

DECLARE @SQL VARCHAR(5000)
declare @PageCount int
declare @currentPageSize  int

 

 

--计算总页数
SET @PageCount=CASE WHEN @RowCount%@PageSize=0 THEN @RowCount/@PageSize ELSE @RowCount/@PageSize+1 END

SET @PageIndex=@PageIndex+1

--第一页
IF @PageIndex<=1
BEGIN              
       set @SQL='select SchoolName,t.* from (SELECT top '+cast(@PageSize as varchar(10))+' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
         FROM  School_Themes
      
         order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc'

END
ELSE
BEGIN
    --最后一页
    IF @PageIndex>=@PageCount OR @PageIndex<=0
        BEGIN
            set @currentPageSize= @RowCount-(@PageCount-1)*@PageSize
                        if(@currentPageSize<=0)
            begin
                            set @currentPageSize=@PageSize
                        end

            SET @SQL='select SchoolName,t.*
                  from (SELECT top '+cast(@PageSize as varchar(10))+'ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
                     FROM  School_Themes
                     order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc'

        END
    ELSE
        BEGIN
            --中间页（上）
            IF @PageIndex>1 AND @PageIndex<=@PageCount/2+1
                BEGIN
                    SET @SQL='                  
                        SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' SchoolName, t.*
                        FROM (
                            SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
                            FROM (
                                SELECT TOP '+CONVERT(VARCHAR(15),@pageSize*@pageIndex)+'  ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
                                        FROM  School_Themes
                               
                                 order by id desc             
                                  )AS A                               
                            order by id asc  
                              )AS t  inner join school v on t.SchoolID = v.SchoolID             
                        order by id desc'
                END
            ELSE
            --中间页（下）
                BEGIN
                    SET @SQL='SELECT TOP '+CONVERT(VARCHAR(15),@PageSize)+' shoolName, t.*
                          FROM (
                            SELECT TOP '+CONVERT(VARCHAR(15),@rowCount - @pageSize * @pageIndex+@pageSize)+' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
                                    FROM  School_Themes
                           
                             order by id asc  
                               )AS t    inner join school v on t.SchoolID = v.SchoolID                                          
                              order id desc    '

                END
        END
END
print @SQL
EXEC (@SQL)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

3、根据存储过程名取存储过程内容

--     根据存储过程名取存储过程内容
-- 　调试　GetContentByProcedureName '[dbo].[CPP_CountOfTrdeByUserID]'
alter procedure GetContentByProcedureName
(@ProcedureName nvarchar(500))
as

if exists (select * from dbo.syscomments where id=object_id(N''+@ProcedureName+''))
select c.text, c.encrypted, c.number, xtype=convert(nchar(2), o.xtype),   
 datalength(c.text), convert(varbinary(8000), c.text), 0 from dbo.syscomments c, dbo.sysobjects o  
  where o.id = c.id and c.id = object_id(N''+@ProcedureName+'')
order by c.number, c.colid option(robust plan)
第一步：创建一个类clsTurnPicture

增加方法如下　：

/** <summary>
        /// 播放图片类(downmoon)
        /// </summary>
        /// <param name="arrImgName">arrImgName</param>
        /// <param name="arrDesc">arrDesc</param>
        /// <param name="strShopName">strShopName</param>
        /// <param name="strBrandName">strBrandName</param>
        /// <param name="strResoourceCode">strResoourceCode</param>
        /// <param name="ScrWidth">ScrWidth</param>
        /// <param name="ScrHeight">ScrHeight</param>
        /// <param name="PicWidth">PicWidth</param>
        /// <param name="PicHeight">PicHeight</param>
        /// <param name="TextHeight">TextHeight</param>
        /// <param name="ToolBarHeight">ToolBarHeight</param>
        /// <param name="FontSize">默认为16,px</param>
        /// <returns></returns>
        public string getPicsForHtml(ArrayList arrImgName,ArrayList arrDesc,string strShopName,string strBrandName,string strResoourceCode,int ScrWidth,int ScrHeight,int PicWidth,int PicHeight,int TextHeight,int ToolBarHeight,int FontSize)
        ...{
             StringBuilder sb = new StringBuilder();
            string strHtml="";
            if(arrImgName==null) return "" ;
            ArrayList ar1=new ArrayList(arrImgName);
            ArrayList ar2=new ArrayList(arrDesc);
            int len=ar1.Count;
            int len2=ar1.Count-1;
            int len3=ToolBarHeight-2;
            string str1="";
            string str2="";

            //strHtm+=@"   <html><head><title>Buynow{0}-{1}-{2}图片信息</title>
            strHtml=@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"" ><HTML><HEAD><title>"+    strShopName+"-"+strBrandName+"-"+"席位："+strResoourceCode+   "-外观展示图"+
                @"</title>
        <meta http-equiv=""content-type"" content=""text/html; charset=gb2312"">
        <meta name=""GENERATOR"" Content=""Microsoft Visual Studio .NET 7.1"">
        <meta name=""CODE_LANGUAGE"" Content=""C#"">
        <meta name=""vs_defaultClientScript"" content=""JavaScript"">
        <meta name=""vs_targetSchema"" content=""http://schemas.microsoft.com/intellisense/ie5"">  "+System.Environment.NewLine;

            sb.Append(strHtml);
            sb.Append("<script language=JavaScript>"+" ");
            sb.Append("<!-- Begin"+System.Environment.NewLine);
            sb.Append("var interval = 6; // delay between rotating images (in seconds)"+System.Environment.NewLine);
            sb.Append("interval *= 1000;"+System.Environment.NewLine);
            sb.Append("var flagtime=0;"+System.Environment.NewLine);
            sb.Append("var image_index = -1;"+System.Environment.NewLine);
            sb.Append("image_list = new Array();"+System.Environment.NewLine);
            sb.Append("note_list = new noteArray("+len2.ToString()+")"+System.Environment.NewLine);
            sb.Append("var number_of_image = image_list.length;"+System.Environment.NewLine);
            sb.Append("var timesnum=0;    "+System.Environment.NewLine);
            //strHtml=string.Format(strHtml,len.ToString());
            string strTemp="";
            for(int k=0;k<ar1.Count;k++)
            ...{
                strTemp=ar1[k].ToString();
                //strHtm+=" image_list[k] = new imageItem("+strTemp+")";
                str1+=" image_list["+k.ToString()+"] = new imageItem(""+strTemp+"")"+"; ";
            }
            sb.Append(""+System.Environment.NewLine);
            for(int k=0;k<ar2.Count;k++)
            ...{
                strTemp=strShopName+"-"+strBrandName+"-"+strResoourceCode+"-"+ar2[k].ToString();
                str2+=" note_list["+k.ToString()+"] = ""+strTemp+"""+"; ";
            }
            sb.Append(str1);
            sb.Append(str2);
            sb.Append("function noteArray(size)"+System.Environment.NewLine);
            sb.Append("{"+System.Environment.NewLine);
            sb.Append("this.length=size;"+System.Environment.NewLine);
            sb.Append("for(i=1;i<=size;i++)"+System.Environment.NewLine);
            sb.Append("{"+System.Environment.NewLine);
            sb.Append(@"this[i]="""""+System.Environment.NewLine);
            sb.Append("    }"+System.Environment.NewLine);
            sb.Append("return this;"+System.Environment.NewLine);
            sb.Append("}"+System.Environment.NewLine);
            sb.Append("function counternum()"+System.Environment.NewLine);
            sb.Append("{"+System.Environment.NewLine);
            sb.Append("if( timesnum < "+len2.ToString()+")"+System.Environment.NewLine);
            sb.Append("{"+System.Environment.NewLine);
            sb.Append("    timesnum++;"+System.Environment.NewLine);
            sb.Append(@"//document.all.slide_ent.src=""about:blank"";");
            sb.Append(System.Environment.NewLine);
          
            sb.Append(@"//document.all.slide_ent.src=""about:blank"";");
            sb.Append(System.Environment.NewLine);
            sb.Append("    }"+System.Environment.NewLine);
            sb.Append("}"+System.Environment.NewLine);
            sb.Append(@"function run(obj)
                            {
                                interval=obj.options[obj.selectedIndex].value*1000;
                                rotateImage();
                            }");
            sb.Append(@"function stop()
                    {
                        interval=0;
                    }
                    function previous()
                    {
                        var new_image = getPreviousImage();
                        document[""rImage""].src = new_image;
                    }
                    function next()
                        {
                            var new_image = getNextImage();
                            document[""rImage""].src = new_image;
                        }
                        function imageItem(image_location)
                        {
                            this.image_item = new Image();
                            this.image_item.src = image_location;
                        }
                        function noteItem(note)
                        {
                            this.note_item = new Image();
                            this.note_item.src = note;
                        }
                        function get_ImageItemLocation(imageObj)
                        {
                            return(imageObj.image_item.src)
                            }
                        function get_NoteItemLocation(noteObj)
                        {
                            return(noteObj.note_item.src)
                            }
                        function generate(x, y)
                        {
                            var range = y - x + 1;
                            return Math.floor(Math.random() * range) + x;
                        }");
            sb.Append(@"function getPreviousImage()
                        {
                            image_index = image_index-1;
                            if (image_index < 0)
                            {");
            sb.Append("image_index=image_index+"+len.ToString()+";");
            sb.Append(@"}
                var new_image = get_ImageItemLocation(image_list[image_index]);
                document.all.span_1.innerText=image_index+1;
                document.all.summary.innerText=note_list[image_index];
                return(new_image);");
            sb.Append(System.Environment.NewLine+"}");
            sb.Append(@"function getNextImage()
                    {
                        image_index = image_index+1;
                    ");
            sb.Append("if (image_index >= "+len.ToString()+")");
            sb.Append(System.Environment.NewLine);          
            sb.Append(@"{");
            sb.Append(System.Environment.NewLine);      
            sb.Append("image_index=image_index-"+len.ToString()+";");
            sb.Append(System.Environment.NewLine);      
            sb.Append("}");
            sb.Append(System.Environment.NewLine);      
            //sb.Append(@"");
            sb.Append(@"var new_image = get_ImageItemLocation(image_list[image_index]);
                        document.all.span_1.innerText=image_index+1;
                        document.all.summary.innerText=note_list[image_index];
                        return(new_image);");
            sb.Append(System.Environment.NewLine+"}");      
            sb.Append(@"
                        //新增图片效果
                        function setTransition()
                        {
                        if (document.all)
                        {
                        document.all('rImage').filters.revealTrans.Transition=Math.floor(Math.random()*23);
                        document.all('rImage').filters.revealTrans.apply();
                        }
                        }
                        function playTransition()
                        {
                        if (document.all)
                        document.all('rImage').filters.revealTrans.play();
                        }");
          
          
            strHtml=@"
                    function rotateImage()
                    {
                        if (interval == 0)
                        {
                            return 1;
                        }
                        if (flagtime == 1)
                        {
                            var now = new Date();
                            var time1 = now.getTime();
                            var time2;
                            time1=time1+3000;
                            time2=now.getTime();
                            while(time2 < time1)
                            {
                                now = new Date();
                                time2=now.getTime();
                            }
                            flagtime=0;
                        }
                        var new_image = getNextImage();
                        setTransition();//新增图片效果
                        document['rImage'].src = new_image;
                        var recur_call = ""rotateImage('""+'rImage'+""')"";
                         playTransition();//新增图片效果
                        setTimeout(recur_call,interval);
                    }
                    // End -->
                    </script>
      
                    <script type=""text/javascript"" src=""Resource/Script/ygcss.js""></script>
                ";
            sb.Append(strHtml);
            strHtml=@"</HEAD><body topmargin=""0"" leftmargin=""0""><form name=""frmDispImage"">";
                  
            sb.Append(strHtml);
                strHtml=@"
                <center>
                <center>
                <table bgcolor='#fff2df' border='0' cellpadding='0' cellspacing='0' width='"
                    +ScrWidth.ToString()+@"'>
                <tbody><tr>
                <td align='center' valign='top'><table border='0' cellpadding='0' cellspacing='0' width='100%'>
                <tbody><tr>
                <td height='1'></td>
                </tr>
                </tbody></table>
                <table border='0' cellpadding='0' cellspacing='0' height='"+
                    PicHeight.ToString()+"' width='"+PicWidth.ToString()+@"' align='center'>
                <tbody><tr>
                <td align='center'><img name=""rImage"" src='"+
                  
                    ar1[0].ToString()+"' width='"+PicWidth+"' height='"+PicHeight+@"'  οnlοad=""counternum();"" border='0' style='FILTER: revealTrans(duration=3,transition=20)'></td>
                    </tr>
                    </tbody></table>
                  
                    <!--text-->
                    <table border='0' cellpadding='0' cellspacing='0' width='100%'>
                    <tbody><tr>
                    <td id=""summary"" align='center' height='"+
                    TextHeight.ToString()+"' style='font-size:"+FontSize.ToString()+"pt;'>"
                  
                    +strShopName+"-"+strBrandName+"-"+strResoourceCode+"-"+ar2[0].ToString()+@"</td>
                    </tr>
                    </tbody></table>
                    <!--/text-->
                    <!--bottom-->
                    <table border='0' cellpadding='0' cellspacing='0' width='100%'>
                    <tbody><tr>
                    <td><img src='Resource/Image/TurnImage/slideshow_bg_3.gif' border='0' height='4' width='"+ScrWidth.ToString()+@"'></td>
                    </tr>
                    <tr>
                    <td bgcolor='#c9beeb' height='"+ToolBarHeight.ToString()+@"'>
                    <table border='0' cellpadding='1' cellspacing='0' width='100%'>
                    <tbody><tr>
                    <td class='sbody' align='center' height='"+len3.ToString()+"'><font style='font-size:"+FontSize+"pt;'><span id='span_1'>1</span>/"+len.ToString()+

                            @"&nbsp;&nbsp;&nbsp;&nbsp;
                            <img src='Resource/Image/TurnImage/slideshows_bottom_1.gif' alt='开始' οnclick=""run(document.frmDispImage.select1)"" align='absmiddle' border='0' height='21' width='35'>&nbsp;&nbsp;<img src='Resource/Image/TurnImage/slideshows_bottom_2.gif' alt='停止' οnclick=""stop();"" align='absmiddle' border='0' height='21' width='35'>
                            &nbsp;&nbsp;<img src='Resource/Image/TurnImage/slideshows_bottom_3.gif' alt='前一张' οnclick=""previous();"" align='absmiddle' border='0' height='21' width='35'>&nbsp;&nbsp;<img src='Resource/Image/TurnImage/slideshows_bottom_4.gif' alt='后一张' οnclick=""next();"" align='absmiddle' border='0' height='21' width='35'>&nbsp;&nbsp;速度：
                            <select name=""select1"" οnchange=""run(this)"">
                            <option value=""12"">12 sec.</option>
                            <option selected=""selected"" value=""6"">6 sec.</option>
                            <option value=""3"">3 sec.</option>
                            <option value=""9"">9 sec.</option>
                            </select></font></td>
                            </tr>

                            </tbody></table>
                            </td>
                            </tr>
                            <tr>
                            <td bgcolor='#625a68' height='1'></td>
                            </tr>
                            </tbody></table>
                            </td>
                            </tr>
                            </tbody></table>
                            <!--/--></center>
                            <script language=""javascript"">
                                flagtime=1;
                            rotateImage();//修改为图片自动播放
                            <!--slideit()-->
                                </script>

                            </center>";
                sb.Append(strHtml);
            sb.Append("</form></body></HTML>");
            return sb.ToString();  

        }

调用格式：
     
/**/                ArrayList ar1=new ArrayList();
                ar1.Clear();
                ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=160720060328095407");
                ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=160420060327085709");
                ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=160420060327091030");
                ar1.Add("http://downmoon-hgh/RichChartServer/Top30/frmForumDisplayImage.aspx?id=160420060327092445");
          
                drawPic(ar1);

                if(this.ar1==null || ar1.Count==0  )
                ...{
                   // this.MsgBox("该席位/资源无外观图！");
                   // this.CloseWebForm();
                    return ;
                }
                else
                ...{

                    drawPic(ar1);
                }

private void drawPic(ArrayList arrImages)
        ...{
            if(pic==null)
            ...{
                pic=new clsTurnPicture();
            }
             int i;  
            ArrayList ar2=new ArrayList();
            ar2.Clear();
            for(int k=0;k<arrImages.Count;k++)
            ...{    i=k+1;
                ar2.Add("展示图"+i.ToString());
            }
        Response.Write(pic.getPicsForHtml(arrImages,ar2,CurrShopName,CurBrandName,strResourceCode,1024,768,1022,640,36,34,16));
          
          
        }

 SQL中通配符、转义符与"["号的使用(downmoon)
一、搜索通配符字符的说明
可以搜索通配符字符。有两种方法可指定平常用作通配符的字符：

使用 ESCAPE 关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。例如，要搜索在任意位置包含字符串 5% 的字符串，请使用：
WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
在上述 LIKE 子句中，前导和结尾百分号 (%) 解释为通配符，而斜杠 (/) 之后的百分号解释为字符 %。

在方括号 ([ ]) 中只包含通配符本身。要搜索破折号 (-) 而不是用它指定搜索范围，请将破折号指定为方括号内的第一个字符：
WHERE ColumnA LIKE '9[-]5'
下表显示了括在方括号内的通配符的用法。

符号 含义
LIKE '5[%]' 5%
LIKE '5%' 5 后跟 0 个或更多字符的字符串
LIKE '[_]n' _n
LIKE '_n' an, in, on (and so on)
LIKE '[a-cdf]' a, b, c, d, or f
LIKE '[-acdf]' -, a, c, d, or f
LIKE '[ [ ]' [
LIKE ']' ]

二、实例说明：

在表PersonalMember中查找strloginname字段中含有"["的记录。

可用三条语句：
1、

select strloginname,* from PersonalMember where strloginname like '%[%' escape ''

2、(说明"\"与"/"均可与escape关键字结合作为转义符)

select strloginname,* from PersonalMember where strloginname like '%/[%' escape '/'

3、
select strloginname,* from dbo.PersonalMember where charindex('[',strloginname)>0

4、

select strloginname,* from dbo.PersonalMember where strloginname  like
'%[[]%'
 SQL语句导入／导出数据集
一.在MSSQL2000中使用Transact-SQL语句处理DBF数据转换
1、查询dbf数据
SELECT * FROM OpenDataSource('vfpoledb','Data Source="d:vfptest.dbc"')...test1

SELECT a.* FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Visual FoxPro Driver};
Exclusive=No;Collate=Machine;Deleted=Yes;Null=No; SourceDB=d:vfptest.dbc;
SourceType=DBC;', 'SELECT * FROM test1') AS a Select * From Openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceDB=d:vfp;SourceType=DBF',
'select * from test3')

2、将DBF数据导入mssql中
SELECT * into test1 FROM OpenDataSource('vfpoledb','Data Source="d:vfptest.dbc"')...test1

3、将SQL数据导入dbf中
insert into OPENROWSET('MSDASQL', 'DRIVER=Microsoft Visual FoxPro Driver}; Exclusive=No;
Collate=Machine;Deleted=Yes;Null=No; SourceDB=d:vfptest.dbc;SourceType=DBC;',
'SELECT * FROM test1')
select * from Test1

4、修改数据
Update openrowset('MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=d:vfp;SourceType=DBF',
'select * from test3 where code="11"') set name = '"12"'

二.导入/导出dBase

/*=================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:','select * from [test.dbf]')

--如果导入数据并生成表
select * into 表 from
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:','select * from [test.dbf]')

/*===============================================*/
--如果从SQL数据库中,导出数据到dBase,如果dBase文件已经存在,就可以简单的用:
insert into
openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=c:','select * from [test.dbf]')
select * from 表

/*--说明:
DATABASE=c: c:是dbf文件的存放目录
'select * from [test.dbf] test.dbf是指dbf文件名
--*/

--如果dBase文件不存在,就需要用到下面的存储过程了.

/*--数据导出dBase
导出表中的数据到dBase,如果文件不存在,将自动创建文件
基于通用性考虑,仅支持导出标准数据类型
--*/

/*--调用示例

--导出dBase
p_exporttb @tbname='地区资料',@path='c:',@over=0
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO

create proc p_exporttb
@tbname sysname, --要导出的表名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='', --文件名,默认为表名
@over bit=0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')='' set @fname=@tbname+'.dbf'

--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
begin
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
end
else
set @over=0
else
set @over=1

--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+';HDR=NO;DATABASE='+@path+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)

if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
end

exec @err=sp_oadestroy @obj

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'

--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
return

lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go

 

 

/*--数据导出dBase

导出查询语句中的数据到dBase,如果文件不存在,将自动创建文件
基于通用性考虑,仅支持导出标准数据类型
--*/

/*--调用示例

--导出dBase
p_exporttb @sqlstr='select * from 地区资料',@path='c:',@over=1
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO

create proc p_exporttb
@sqlstr varchar(8000), --要导出的查询名
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250)='temp.dbf',--文件名,默认为temp
@over bit=0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')='' set @fname='temp.dbf'

--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
if exists(select 1 from #tb where a=1)
if @over=1
begin
set @sql='del '+@sql
exec master..xp_cmdshell @sql,no_output
end
else
set @over=0
else
set @over=1

--数据库创建语句
set @sql=@path+@fname
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="dBASE 5.0;'
+';HDR=NO;DATABASE='+@path+'"'

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>250 then 250 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@fname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)

if @over=1
begin
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr
end

exec @err=sp_oadestroy @obj

set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''dBase 5.0;DATABASE='
+@path+''',''select * from ['+@fname+']'')'

--导入数据
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'
exec(@sql)

return

lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go

三.ＳＱＬ语句导入／导出大全

/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q
-S"GNETDATA/GNETDATA" -U"sa" -P""'
/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/*动态文件名
declare @fn varchar(20),@s varchar(1000)
set @fn = 'c:test.xls'
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0'''
set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$'
exec(@s)
*/

SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/********************** EXCEL导到远程SQL
insert OPENDATASOURCE(
'SQLOLEDB',
'Data Source=远程ip;User ID=sa;Password=密码'
).库名.dbo.表名 (列名1,列名2)
SELECT 列名1,列名2
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in cT.txt -c -Sservername -Usa -Ppassword'

/** 导出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out cT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout cT.txt -c
-Sservername -Usa -Ppassword'

导出到TXT文本，用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:tt.txt" -c -t ,-U sa -P password'

BULK INSERT 库名..表名
FROM 'c:test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = 'n'
)

--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料4.dbf]')
--*/

--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料3.dbf]')
--*/

--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
--*/

/**************导入DBF文件****************/
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:VFP98data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

insert into openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
'select * from [aa.DBF]')
select * from 表

说明:
SourceDB=c: 指定foxpro表所在的文件夹
aa.DBF 指定foxpro表的文件名.

 

/*************导出到Access********************/
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'x:A.mdb';'admin';'',A表) select * from 数据库名..B表

/*************导入Access********************/
insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
'x:A.mdb';'admin';'',A表)

文件名为参数
declare @fname varchar(20)
set @fname = 'd:test.mdb'
exec('SELECT a.* FROM opendatasource(''Microsoft.Jet.OLEDB.4.0'',
'''+@fname+''';''admin'';'''', topics) as a ')

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="f:northwind.mdb";Jet OLEDBatabase Password=123;User ID=Admin;Password=;')...产品

********************* 导入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc ='

 

Customer was very satisfied

 

white red" />
Important
Happy Customer.

 

'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()')
EXEC sp_xml_removedocument @idoc

 

???????

/**********************Excel导到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
实现将一个Excel文件内容导入到一个文本文件

假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分开。

邹健：
如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.

insert into
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=Yes;DATABASE=C:'
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls'
--,Sheet1$)
)...[Sheet1$]

 

如果你想直接插入并生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

--首先将excel表内容导入到一个全局临时表
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into '+@tbname+' from
opendatasource(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls''
)...[Sheet1$]'
exec(@sql)

--然后用bcp从全局临时表导出到文本文件
set @sql='bcp "'+@tbname+'" out "c:aa.txt" /S"(local)" /P"" /c'
exec master..xp_cmdshell @sql

--删除临时表
exec('drop table '+@tbname)

/********************导整个数据库************************/

用bcp实现的存储过程

/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:zj.txt',1
----导出整个数据库
exec file2table 'zj','','','xzkh_sa','C:docman',1

--导入调用示例
----导入单个表
exec file2table 'zj','','','xzkh_sa..地区资料','c:zj.txt',0
----导入整个数据库
exec file2table 'zj','','','xzkh_sa','C:docman',0

*/
if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200) --服务器名
,@username varchar(200) --用户名,如果用NT验证方式,则为空''
,@password varchar(200) --密码
,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,
--则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit --1为导出,0为导入
as
declare @sql varchar(8000)

if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
begin
set @sql='bcp '+@tbname
+case when @isout=1 then ' out ' else ' in ' end
+' "'+@filename+'" /w'
+' /S '+@servername
+case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>'' set @filename=@filename+''

set @m_tbname='declare #tb cursor for select name from
'+@tbname+'..sysobjects where xtype=''U'''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp '+@tbname+'..'+@m_tbname
+case when @isout=1 then ' out ' else ' in ' end
+' "'+@filename+@m_tbname+'.txt " /w'
+' /S '+@servername
+case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go

/************* Oracle **************/
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO

delete from openquery(mailser,'select * from yulin')

select * from openquery(mailser,'select * from yulin')

update openquery(mailser,'select * from yulin where id=15')set disorder=555,catago=888

insert into openquery(mailser,'select disorder,catago from yulin')values(333,777)

 

补充：

对于用bcp导出,是没有字段名的.

用openrowset导出,需要事先建好表.

用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入

create proc up_010
as
begin
declare @sql varchar(99),@tbl varchar(30),@fk varchar(30)
declare cur_fk cursor local for
select object_name(constid),object_name(fkeyid) from sysreferences
--删除所有外键
open cur_fk
fetch cur_fk into @fk,@tbl
while @@fetch_status =0
begin
select @sql='alter table '+@tbl+' nocheck constraint '+@fk
exec(@SQL)
select @sql='delete from '+@tbl
exec(@SQL)
fetch cur_fk into @fk,@tbl
end
close cur_fk
declare cur_fks cursor local for
select name from sysobjects where xtype='U'
open cur_fks
fetch cur_fks into @tbl
while @@fetch_status =0
begin
select @sql='delete from ['+@tbl+']'
exec(@SQL)
fetch cur_fks into @tbl
end
close cur_fks
end

exec up_010

MSSQL生成整个数据库的SQL脚本的工具
scptxfr.exe的路径要正确
declare @cMd varchar(1000)
set @cmd = 'master.dbo.xp_cmdshell ' +
'''c:\"Microsoft ' +
'SQL Server"' +
'\MSSQL\Upgrade\scptxfr.exe ' +
' /s YourServerName /p YourSAPassword /I /d YourDBName /f ' +
'c:\YourDBName.sql'''
exec (@cmd)

工具参数说明：
SCPTXFR /s <server> /d <database> {[/I] | [/P <password>]}
{[/F <script files directory>] | [/f <single script file>]}
/q /r /O /T /A /E /C <CodePage> /N /X /H /G /Y /?
/s - Indicates the source server to connect to.
/d - Indicates the source database to script.
/I - Use integrated security.
/P - Password to use for 'sa'. Note that login ID is always 'sa'.
If /P not used or if a password does not follow the flag,
a null password is used. Not compatible with /I.
/F - The directory into which the script files should be generated.
This means one file is generated for each category of objects.
/f - The single file in which all script is to be saved.
Not compatible with /F.
/q - Use quoted identifiers in the generated scripts.
/r - Include drop statements for the objects in the script.
/O - Generate OEM script files. Cannot be used with /A or /T.
This is the default behavior.
/T - Generate UNICODE script files. Cannot be used with /A or /O.
/A - Generate ANSI script files. Cannot be used with /T or /O.
/? - Command line help.
/E - Stop scripting when error occurs.
Default behavior is to log the error, and continue.
/C - Indicate the CodePage which overrides the server CodePage.
/N - Generate ANSI PADDING.
/X - Script SPs and XPs to separate files.
/H - Generate script files without header (default: with header).
/G - Use the specified server name as the prefix for the generated
output files(to handle dashes in server name).
/Y - Generate script for Extended Properties (valid for 8.x server
only).
// 保存可執行文件到COMMON數據庫
long ll_filenum, ll_len, ll_ret
blob lblb_c, lblb_total
boolean lb_auto

lb_auto = gnv_app.inv_sec.autocommit
gnv_app.inv_sec.autocommit = true
If not fileexists(as_file) Then RETURN FAILURE

ll_filenum = fileopen(as_file, StreamMode!, Read!, Shared!)
ll_len = fileread(ll_filenum, lblb_c)
do while ll_len > 0
lblb_total = lblb_total + lblb_c
ll_len = fileread(ll_filenum, lblb_c)
loop

fileclose(ll_filenum)
updateblob app_files set content = :lblb_total where id = :al_id using gnv_app.inv_sec;
ll_ret = gnv_app.inv_sec.SQLNRows
gnv_app.inv_sec.autocommit = lb_auto
If ll_Ret <= 0 Then RETURN FAILURE

RETURN SUCCESS

Dlg_SetupDatabase://安装数据库的界面
szMsg="请选择应用程序安装类型";
szTitle="应用程序安装类型";
SetupDatabase="服务器端";
noSetupDatabase="客户端";
svCheck1=TRUE;
svCheck2=FALSE;
nResult=AskOptions(EXCLUSIVE,szMsg,SetupDatabase,svCheck1,noSetupDatabase,svCheck2);
if nResult=BACK goto Dlg_SdRegisterUserEx;
if svCheck1=TRUE then
goto Dlg_SdShowDlgEdit3;
endif;
//modify by sds
//if svCheck2=TRUE goto Dlg_SdAskDestPath;
if svCheck2=TRUE goto Dlg_SdShowDlgEdit3;

Dlg_SdShowDlgEdit3:
szTitle="请输入数据库信息";
szMsg="请输入数据库信息";
ServerName="服务器名称";
UserName="用户名称";
Password="数据库口令";
svEdit1="";
svEdit2="sa";
svEdit3="";
nResult=SdShowDlgEdit3(szTitle,szMsg,ServerName,UserName,Password,svEdit1,svEdit2,svEdit3);
//这个地方要产生一个脚本文件CreateDatabase.Sql;

//if (nResult=BACK) goto Dlg_SetupDatabase;
if (nResult=BACK) then
goto Dlg_SetupDatabase;
else
WriteProfString (WINSYSDIR^"DbInfo.ini", "DatabaseInfo", "ServerName",svEdit1);
WriteProfString (WINSYSDIR^"DbInfo.ini", "DatabaseInfo", "UserName",svEdit2);
WriteProfString (WINSYSDIR^"DbInfo.ini", "DatabaseInfo", "PassWord",svEdit3);
if (svCheck2=TRUE) goto Dlg_SdAskDestPath;
if svCheck1=TRUE then

OpenFileMode(FILE_MODE_APPEND);
CreateFile(DataBaseHandle,WINSYSDIR,"CheckDataBaseInfo.Bat");
//dbCheck.Sql－－应该是我们自己带的SRCDIR^
CheckDataBaseJbStr="@isqlw -S %s -d master -U %s -P %s -i "+SRCDIR^"dbCheck.sql -o "+WINSYSDIR^"out2.txt";
Sprintf(CheckDataBaseJbStr,CheckDataBaseJbStr,svEdit1,svEdit2,svEdit3);
WriteLine(DataBaseHandle,"@echo 正在检查数据库信息请稍等...");
WriteLine(DataBaseHandle,CheckDataBaseJbStr);
CloseFile(DataBaseHandle);
LaunchAppAndWait(WINSYSDIR^"CheckDataBaseInfo.Bat","",WAIT);
//写DBInfo文件让系统可以初始话BDE的配置

OpenFileMode(FILE_MODE_NORMAL);

OpenFile(OutHandle,WINSYSDIR,"out2.txt");
GetLine(OutHandle,OutStr);
if OutStr='1' then
MessageBox("该数据库存在,本次安装将不在安装数据库",WARNING);
CloseFile(OutHandle);
//DeleteFile(WINSYSDIR^"CheckDataBaseInfo.Bat");
//DeleteFile(WINSYSDIR^"out2.txt");
goto Dlg_SdAskDestPath;//不进行选择数据库的安装了
endif;

endif;
endif;
/*else
*/
//增加一个公司标示界面来插入数据---这个界面要负责产生InsertCompInfo.sql文件，让安装数据库的时候调用
Dlg_SdShowDlgEdit2:
szTitle="请输入公司信息";
szMsg="请输入公司信息";
CompName="公司名称";
CompInfo="系统标识";
nResult=SdShowDlgEdit2(szTitle,szMsg,CompName,CompInfo,CompNameStr,CompInfoStr);
if (nResult)=BACK then
goto Dlg_SdShowDlgEdit3;
else
OpenFileMode(FILE_MODE_APPEND);
CreateFile(CompHandle,WINSYSDIR,"InsertCompInfo.sql");
CompStr="insert into XTBZ(XTDM,XTMC) values("+"'" +"%s"+"'"+",'"+"%s"+"') ";
Sprintf(CompStr,CompStr,CompNameStr,CompInfoStr);
WriteLine(CompHandle,"use xs ");
WriteLine(CompHandle,CompStr);
WriteLine(CompHandle,"go");
CloseFile(CompHandle);
endif;
Dlg_DataBase: //选择安装数据库
szMsg="请选择数据库安装路径";
szTitle="数据库安装程序";
svDestDir="C:\\";
nResult=AskDestPath ("数据库文件路径", szMsg, svDestDir, 0);
//写文件

if (nResult=BACK) then
goto Dlg_SdShowDlgEdit2;
else
if GetDiskSpace(svDestDir)<10485760 then
MessageBox("磁盘空间太少了",WARNING);

goto Dlg_DataBase;
else
/*if (ExistsDir(svDestDir+"\\Data"))!=EXISTS then
CreateDir(svDestDir+"\\Data");
endif;*/
CreateDir(svDestDir+"\\Data");
//写批处理文件了创建数据库,svEdit1,是服务器名称svEdit2,是用户名称，svEdit3是数据库密码
//先要写创建数据库的脚本文件
OpenFileMode (FILE_MODE_APPEND);
CreateFile(DataBaseHandle,WINSYSDIR,"CreateDatabase.Sql");
// Sprintf
CDataBaseSql="CREATE DATABASE xs "+
" ON "+
"( NAME = xs_dat"+", "
+" FILENAME="+"'"+"%s"+"'"
+" )LOG ON (NAME=xs_log"+
",FileName="+"'"+"%s"+"'"
+")";
LogFileName=svDestDir+"Data\\"+"xs_log.ldf";
DataBaseFileName=svDestDir+"Data\\"+"xs_dat.mdf";
Sprintf(DataBaseMsg,CDataBaseSql,DataBaseFileName,LogFileName);

WriteLine(DataBaseHandle,DataBaseMsg);
CloseFile(DataBaseHandle);
OpenFileMode(FILE_MODE_APPEND);
CreateFile(DataBaseHandle,WINSYSDIR,"ExecuteSql.Bat");
//dbCheck.Sql－－应该是我们自己带的SRCDIR^
CheckDataBaseJbStr="@isqlw -S %s -d master -U %s -P %s -i "+WINSYSDIR^"CreateDatabase.Sql -o "+WINSYSDIR^"out1.txt";
TableXsStr="@isqlw -S %s -d master -U %s -P %s -i "+SRCDIR^"Table_xs.Sql -o "+WINSYSDIR^"out1.txt";
CompStr="@isqlw -S %s -d master -U %s -P %s -i "+WINSYSDIR^"InsertCompInfo.sql -o "+WINSYSDIR^"out1.txt";
Sprintf(CheckDataBaseJbStr,CheckDataBaseJbStr,svEdit1,svEdit2,svEdit3);
Sprintf(TableXsStr,TableXsStr,svEdit1,svEdit2,svEdit3);
Sprintf(CompStr,CompStr,svEdit1,svEdit2,svEdit3);
WriteLine(DataBaseHandle,"@echo 正在创建数据库请稍等...");
WriteLine(DataBaseHandle,CheckDataBaseJbStr); //创建数据库
WriteLine(DataBaseHandle,"@echo 正在更新脚本语言请稍等...");
WriteLine(DataBaseHandle,TableXsStr);//写Table_xs的文件;
WriteLine(DataBaseHandle,"@echo 正在插入公司信息请稍等...");
WriteLine(DataBaseHandle,CompStr);//插入公司标示
CloseFile(DataBaseHandle);
LaunchAppAndWait(WINSYSDIR^"ExecuteSql.Bat","",WAIT);
DeleteFile(WINSYSDIR^"CreateDatabase.Sql");
DeleteFile(WINSYSDIR^"InsertCompInfo.sql");
DeleteFile(WINSYSDIR^"ExecuteSql.Bat");
DeleteFile(WINSYSDIR^"out1.txt");
MessageBox("恭喜数据库安装成功",INFORMATION);
endif;
endif;

 

 

转载于:https://www.cnblogs.com/sanle/archive/2008/01/20/1046465.html