Getting a file name from Query Analyzer

Hi all,

im trying to write a stored procedure that will basically browse a folder and get me the first file that it sees. Is there any way that I can do this in TSQL or using CLR in C#? I was thinking something along the lines of using the dos dir command and triyng to pipe it into a variable, not sure how to go about doing this. Any suggestions?

dir /b ...gives me the bare file names, but it lists all the files in the folder, any way that i can just get the first file ( i dont really care what file).

create table #filelist
(
files varchar(500)
)

truncate table #filelist

insert #filelist
EXEC xp_cmdshell 'dir c:*.* /b'

select top 1 * from #filelist

|||

This might help out:

http://stevekass.com/blog/wp-content/Folders/sql/TextDriver.htm

You can use TOP 1 to get just one file name.

Steve Kass

Drew University

http://www.stevekass.com

YoungEngineer@.discussions.microsoft.com wrote:

> Hi all,

>

> im trying to write a stored procedure that will basically browse a

> folder and get me the first file that it sees. Is there any way that I

> can do this in TSQL or using CLR in C#? I was thinking something along

> the lines of using the dos dir command and triyng to pipe it into a

> variable, not sure how to go about doing this. Any suggestions?

>

> dir /b ...gives me the bare file names, but it lists all the files in

> the folder, any way that i can just get the first file ( i dont really

> care what file).

>

>

>

>

Get the specific files from directory

Hi,

I have some some files names in SQL DATABASE but my actuall files are keep in a seperate folder. so please help me that becasue i dont know that how i can select the specific files from folder and fetch into the imageArray according to the database table. In my coding its get the all files from directory but i want to get from database with where class and then select from directory.

The structure of my table is

create table event_pic
(
event_sub_id integer,
event_pic_name varchar(50)
)

here is code below:

Sub displayMe()

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("select * from event_pic where event_sub_id='5' ",con)

con.open()

dim SDR as SQLDataReader
SDR = cmd.ExecuteReader()



con.close()


Dim imageArray() As String
Dim i As Integer

' grab full path and file of images on server in images folder
imageArray = Directory.GetFiles(Server.MapPath("upload/"), "*.*")

' remove the full path from the image filenames
For i = 0 To (imageArray.Length - 1)
imageArray(i) = Replace(imageArray(i), Server.MapPath("upload/"), "")

Next


ViewImages.DataSource = imageArray
ViewImages.DataBind()


End Sub

If you are using 2.0 you can solve it with a List.

Sub displayMe()Dim conAs New SqlConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")Dim cmdAs New SqlCommand("select * from event_pic where event_sub_id='5' ", con) con.Open() Dim SDR As SqlDataReader SDR = cmd.ExecuteReader() Dim imageList As New List(Of String)() While SDR.Read() imageList.Add(SDR("event_pic_name").ToString())End While ViewImages.DataSource = imageList ViewImages.DataBind() con.Close()End Sub

|||

but the thing is that the database table only keeps the name of images actuall images are in a seperate folder and I will trace from folder according to the sql query.

|||

? In your initial query you strip out the path of the image, so imageArray will only contain the actual name of the image - without path. How do you want it? Does the entries in the database contain full path? Do you want to strip the path prior to binding?

|||

In my first query I get the files from DIRECTORY.GETFILES and then from loop I remove the paths and then bind to datagrid. anyway just pls tell me that the script that how I can get the specific files from folder according to the sql query?

|||

my database entries only keep only the names of files not full path

|||

If all your files are placed in the same folder, you can simply prefix the path of the image with that folder name. You should not use Server.MapPath for this, as it translates to a physical path. You need a virtual path in order serve the image URL correctly to the client. Also you must make sure that the image folder is located under the web root, either physically or through a virtual directory.

To get the correct virtual path in an asp.net app, you should use Page.ResolveUrl method.

So, the updated code would be:

imageList.Add(Page.ResolveUrl("~/imagefolder/") + SDR("event_pic_name").ToString())

|||

when i declare the image list its give me this error

Dim imageList As New List(Of String)()

Error: Type 'List' is not defined.|||

imports System.Collections.Generic

(Requires .net 2.0 or higher)

|||

Ok, Thanks but its display no images when i set the

ViewImage.DataList = imageList

ViewImage.Databound()

<asp:DataGrid runat="server" ID="ViewImages" AutoGenerateColumns="false" >
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td width="210"><img src="http://pics.10026.com/?src=upload/<%# Container.dataItem() %>" border="0" /></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>

|||

You already have the image path specified in your asp.net code. You have to choose - either you have it there, or you have it in your page class. Can't have both, or else the path will be invalid. My suggestion is that you set the entire path from your page class, with the code I provided (Page.ResolveUrl). If you do a view source on the page, you should see that the images are there but the path is invalid.

|||

can you explain me that how i can set this path and shows the images through datagrid, My acutall path is in the root directory and name of the folder is "upload". and i use this script to get files

do while SDR.Read()

imageList.Add(Page.ResolveUrl("~/upload/") + SDR("event_pic").ToString())

Loop

|||

If you want to display only the pictures, I would recommend you to use a Repeater instead, as it is more logical.

See this tutorial:http://msconline.maconstate.edu/tutorials/ASPNET20/ASPNET08/aspnet08-01.aspx

Also, see this previous post:Using a repeater to display images side by side?

|||

hmmmm, My problem is still remain actually i am a newly person in asp.net and so thats why i have a some problem, suppose if i use repeater but how i can display the images because my database hold only the names of images and the images are in a seperate folder. Is it possible to write a short script for me I shall be very thankful to you.

|||

This is another way to do it, may not be the best, but it works.

<asp:GridViewrunat="server"ID="GridView1"AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<tableid="tt"border="1"cellpadding="5"cellspacing="0">
<tr>
<tdwidth="210">
<%# GetImage(Eval("imagename").ToString()) %>
</td>
</tr>
</table>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Then inside the codebehind

Sub DataBindPhotos()

Dim mConAsNew SqlConnection("server=london-home; Database=tony; uid=rashid2; pwd=test;")

Dim mComAsNew SqlCommand("select * from event_pic where event_sub_id='5' ", mCon)

Dim myAdapAsNew SqlDataAdapterDim myTableAsNew DataTable

myAdap.SelectCommand = mCom

myAdap.Fill(myTable)

GridView1.DataSource = myTable

GridView1.DataBind()

End Sub

Function GetImage(ByVal ImageNameAsString)AsString

Return"<img src=path/to/your/pictures/" + ImageName +" border=0 />"

EndFunction

Get the default snapshot folder path

How can i get the unc path to the default snapshot folder using TSQL? I am
using Transactional replication on SQLServer 2005. I have used
sp_helppublication but that only tells me whether my publication uses the
default snapshot folder or not. My publication does use the default snapshot
folder so the alt_snapshot_folder is NULL as expected.
Thanks,
Ian.
use [distribution]
select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
'dbo', 'table', 'UIProperties', null, null)
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dinnebier" <dinnebier@.community.nospam> wrote in message
news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
> How can i get the unc path to the default snapshot folder using TSQL? I am
> using Transactional replication on SQLServer 2005. I have used
> sp_helppublication but that only tells me whether my publication uses the
> default snapshot folder or not. My publication does use the default
> snapshot
> folder so the alt_snapshot_folder is NULL as expected.
> Thanks,
> Ian.
|||Thanks for your rapid response Hilary. That's great. I would have never
thought of using fn_listextendedproperty. However I have realised that that
would only work if the publisher is also the distributor. Having done some
more research on this I have found the following TQSL produces the result I
require:
DECLARE @.result nvarchar(255)
exec sp_helpdistributor @.directory = @.result OUTPUT
PRINT @.result
This works regardless of whether I run this at the publisher or the
subscriber.
In fact both solutions are fine for my requirements.
Thanks again,
Ian.
"Hilary Cotter" wrote:

> use [distribution]
> select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
> 'dbo', 'table', 'UIProperties', null, null)
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "dinnebier" <dinnebier@.community.nospam> wrote in message
> news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
>
>
|||Hello,
Thank you for posting here.
I am glad to hear that the resolution for your requirements has been found.
Thank you for investing time in this issue and for sharing the solution.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Get the default snapshot folder path
| thread-index: AcetnLeE/hNy56AyQvapXKihY41Auw==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?ZGlubmViaWVy?= <dinnebier@.community.nospam>
| Subject: Get the default snapshot folder path
| Date: Wed, 13 Jun 2007 02:25:00 -0700
| Lines: 8
| Message-ID: <B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| Newsgroups: microsoft.public.sqlserver.replication
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.replication:3509
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How can i get the unc path to the default snapshot folder using TSQL? I
am
| using Transactional replication on SQLServer 2005. I have used
| sp_helppublication but that only tells me whether my publication uses the
| default snapshot folder or not. My publication does use the default
snapshot
| folder so the alt_snapshot_folder is NULL as expected.
|
| Thanks,
| Ian.
|