Showing posts with label saved. Show all posts
Showing posts with label saved. Show all posts

Monday, March 19, 2012

Best Practice for SQL2005 CRUD of RSS

Has anybody determined an optimal strategy when storing RSS in the database?
Here are some qualifying reuse considerations once the RSS is saved to the
database:
* the XML needs to be accessible for searching the channel and feed item
descriptions and likely other elements of the feed so query results may be
returned to the page.
* the XML needs to be retrieved and loaded into a Wizard control for editing
and updating nodal values and then resaved to the database.
* the structure of the XML may need to be modified by editors who want
support for various
namespaces for enclosures and so on.
I like this MSDN document [1] but I'm still wondering what the optimal
method to save the XML to the database is and what the optimal method to
retrieve the XML is to optimally support the considerations which qualify
reuse?
Does "your" experience with RSS coincide with the methodologies discussed in
that MSDN document? Hasn't anybody written a best practice document for SQL
Server CRUD of RSS and ASP.NET?
Your comments?
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
MAP 432'17"N 882'37"W : 432'17"N 882'37"W
[1]
http://msdn.microsoft.com/library/de...xmloptions.asp
Hello clintonG,

> * the XML needs to be accessible for searching the channel and feed
> item descriptions and likely other elements of the feed so query
> results may be returned to the page.
Generally speaking, I'm only in favor of using the XML Data type if your
going to use the data as XML. That means that you are going to query it with
XQuery, you want/need to valid it against an suitable schema and you're okay
spending the disk space to store the primary and potentially secondary XML
indices.
So much depends on how and what you want to query for and how as to how you
want to store it. For example. you could get by with varchar(max) or nvarchar(max)
rather then XML if you use Free-Text searches on the body. Or you could just
the XML type with an Index on it.

> * the XML needs to be retrieved and loaded into a Wizard control for
> editing and updating nodal values and then resaved to the database.
There's nothing on this that help me pick one data type of over the other
here.

> * the structure of the XML may need to be modified by editors who want
> support for various
> namespaces for enclosures and so on.
Then if you pick the XML type, you'll probably have an issue with schema
binding since columnar instances can be bound to a either 0 or 1 XML schema
collection at a time. Text BLOB storage works around this course since it
never binds to any XML schema collection.

> I like this MSDN document [1] but I'm still wondering what the optimal
> method to save the XML to the database is and what the optimal method
> to retrieve the XML is to optimally support the considerations which
> qualify reuse?
Loading an saving would probably be done using the ADO or ADO.NET which,
for all practical purposes, treats XML and the other text BLOBs as the same
thing.

> Does "your" experience with RSS coincide with the methodologies
> discussed in that MSDN document?
Generally speaking, yes.

> Hasn't anybody written a best
> practice document for SQL Server CRUD of RSS and ASP.NET?
No, its probably a little too niche for MS to do.
Cheers,
kt
|||Hello Kent, thanks for replying inline...
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:f2b9912a78538c8b2b35980cce0@.news.microsoft.co m...
> Hello clintonG,
>
> Generally speaking, I'm only in favor of using the XML Data type if your
> going to use the data as XML. That means that you are going to query it
> with XQuery, you want/need to valid it against an suitable schema and
> you're okay spending the disk space to store the primary and potentially
> secondary XML indices.
> So much depends on how and what you want to query for and how as to how
> you want to store it. For example. you could get by with varchar(max) or
> nvarchar(max) rather then XML if you use Free-Text searches on the body.
> Or you could just the XML type with an Index on it.
I'm going to have to use the XML data type -- and -- nvarchar(max). The XML
data type because I will need to return an entire feed expresses as XML when
responding to an AJAX function or a webservice that returns a feed and an
nvarchar(max) because the Primary and three secondary indices of the XML
data type are insufficient to expose an entire feed to search by query.
I'm validating the data on the server when the channel and feed items are
created but I may still need a schema when?

> There's nothing on this that help me pick one data type of over the other
> here.
It looks like I need to get the contents of the nvarchar(max) and populate
an XmlDocument object which is stored in memory to allow each node to be
accessible to each step of a Wizard control that is used to edit any given
node of the feed's XML. I don't know of a more efficient choice that would
allow me to get a node of the XML document on demand and load it into the
step of a Wizard control. Do you?

> Then if you pick the XML type, you'll probably have an issue with schema
> binding since columnar instances can be bound to a either 0 or 1 XML
> schema collection at a time. Text BLOB storage works around this course
> since it never binds to any XML schema collection.
Can I build a broadly scoped schema that declares all possible data types
for all of the known namespaces that I intend to support when extending RSS
and then use whichever data types the context of the feed requires or does
the schema map the entire XML file and then barfs when the XML file does not
contain all of the elements and respective data types that have been
declared in the schema?

> Loading an saving would probably be done using the ADO or ADO.NET which,
> for all practical purposes, treats XML and the other text BLOBs as the
> same thing.
I'll be using the XmlTextWriter and a SQL Provider with a stored procedure
to save the XML as nvarchar(max) until I learn there may be a better way.
I don't mind writing a lot of code. I just hate writing a lot of code I
would not have had to write after discovering I didn't know or gtry to learn
about a more efficient or easier way to write it. Thanks for your replies...
<snip />
<%= Clinton

Best Practice for SQL2005 CRUD of RSS

Has anybody determined an optimal strategy when storing RSS in the database?
Here are some qualifying reuse considerations once the RSS is saved to the
database:
* the XML needs to be accessible for searching the channel and feed item
descriptions and likely other elements of the feed so query results may be
returned to the page.
* the XML needs to be retrieved and loaded into a Wizard control for editing
and updating nodal values and then resaved to the database.
* the structure of the XML may need to be modified by editors who want
support for various
namespaces for enclosures and so on.
I like this MSDN document [1] but I'm still wondering what the optimal
method to save the XML to the database is and what the optimal method to
retrieve the XML is to optimally support the considerations which qualify
reuse?
Does "your" experience with RSS coincide with the methodologies discussed in
that MSDN document? Hasn't anybody written a best practice document for SQL
Server CRUD of RSS and ASP.NET?
Your comments?
<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://clintongallagher.metromilwaukee.com/
MAP 432'17"N 882'37"W : 432'17"N 882'37"W
[1]
http://msdn.microsoft.com/library/d...5xmloptions.aspHello clintonG,

> * the XML needs to be accessible for searching the channel and feed
> item descriptions and likely other elements of the feed so query
> results may be returned to the page.
Generally speaking, I'm only in favor of using the XML Data type if your
going to use the data as XML. That means that you are going to query it with
XQuery, you want/need to valid it against an suitable schema and you're okay
spending the disk space to store the primary and potentially secondary XML
indices.
So much depends on how and what you want to query for and how as to how you
want to store it. For example. you could get by with varchar(max) or nvarcha
r(max)
rather then XML if you use Free-Text searches on the body. Or you could just
the XML type with an Index on it.

> * the XML needs to be retrieved and loaded into a Wizard control for
> editing and updating nodal values and then resaved to the database.
There's nothing on this that help me pick one data type of over the other
here.

> * the structure of the XML may need to be modified by editors who want
> support for various
> namespaces for enclosures and so on.
Then if you pick the XML type, you'll probably have an issue with schema
binding since columnar instances can be bound to a either 0 or 1 XML schema
collection at a time. Text BLOB storage works around this course since it
never binds to any XML schema collection.

> I like this MSDN document [1] but I'm still wondering what the optimal
> method to save the XML to the database is and what the optimal method
> to retrieve the XML is to optimally support the considerations which
> qualify reuse?
Loading an saving would probably be done using the ADO or ADO.NET which,
for all practical purposes, treats XML and the other text BLOBs as the same
thing.

> Does "your" experience with RSS coincide with the methodologies
> discussed in that MSDN document?
Generally speaking, yes.

> Hasn't anybody written a best
> practice document for SQL Server CRUD of RSS and ASP.NET?
No, its probably a little too niche for MS to do.
Cheers,
kt|||Hello Kent, thanks for replying inline...
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:f2b9912a78538c8b2b35980cce0@.news.microsoft.com...
> Hello clintonG,
>
> Generally speaking, I'm only in favor of using the XML Data type if your
> going to use the data as XML. That means that you are going to query it
> with XQuery, you want/need to valid it against an suitable schema and
> you're okay spending the disk space to store the primary and potentially
> secondary XML indices.
> So much depends on how and what you want to query for and how as to how
> you want to store it. For example. you could get by with varchar(max) or
> nvarchar(max) rather then XML if you use Free-Text searches on the body.
> Or you could just the XML type with an Index on it.
I'm going to have to use the XML data type -- and -- nvarchar(max). The XML
data type because I will need to return an entire feed expresses as XML when
responding to an AJAX function or a webservice that returns a feed and an
nvarchar(max) because the Primary and three secondary indices of the XML
data type are insufficient to expose an entire feed to search by query.
I'm validating the data on the server when the channel and feed items are
created but I may still need a schema when?

> There's nothing on this that help me pick one data type of over the other
> here.
It looks like I need to get the contents of the nvarchar(max) and populate
an XmlDocument object which is stored in memory to allow each node to be
accessible to each step of a Wizard control that is used to edit any given
node of the feed's XML. I don't know of a more efficient choice that would
allow me to get a node of the XML document on demand and load it into the
step of a Wizard control. Do you?

> Then if you pick the XML type, you'll probably have an issue with schema
> binding since columnar instances can be bound to a either 0 or 1 XML
> schema collection at a time. Text BLOB storage works around this course
> since it never binds to any XML schema collection.
Can I build a broadly scoped schema that declares all possible data types
for all of the known namespaces that I intend to support when extending RSS
and then use whichever data types the context of the feed requires or does
the schema map the entire XML file and then barfs when the XML file does not
contain all of the elements and respective data types that have been
declared in the schema?

> Loading an saving would probably be done using the ADO or ADO.NET which,
> for all practical purposes, treats XML and the other text BLOBs as the
> same thing.
I'll be using the XmlTextWriter and a SQL Provider with a stored procedure
to save the XML as nvarchar(max) until I learn there may be a better way.
I don't mind writing a lot of code. I just hate writing a lot of code I
would not have had to write after discovering I didn't know or gtry to learn
about a more efficient or easier way to write it. Thanks for your replies...
<snip />
<%= Clinton

Wednesday, March 7, 2012

Best method for running several queries?

I have an SQL file saved from QA. It has several queries used for testing.
These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
specific statement to run in that file. This keeps everything from running
at once.
Problem is that I access the server from several computers via QA. The SQL
file with all of the above queries is usually on one computer. Should I
just store the SQL file on the SQL Server machine as an SQL file or a stored
procedure? What is best for this? This file isn't something I would ever
want an app to have access to. It's strictly for manual testing purposes
via QA.
Thanks,
BrettScript files are considered source code. So, you would want to put it in a
souce control server somewhere and just grab it when you need it. Storing
the script inside sqlserver is probably not a good idea in this case.
-oj
"Brett" <no@.spam.net> wrote in message
news:ORw4XLLLFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I have an SQL file saved from QA. It has several queries used for testing.
>These are DELETE, UPDATE, INSERT, SELECT of various types. I highlight the
>specific statement to run in that file. This keeps everything from running
>at once.
> Problem is that I access the server from several computers via QA. The
> SQL file with all of the above queries is usually on one computer. Should
> I just store the SQL file on the SQL Server machine as an SQL file or a
> stored procedure? What is best for this? This file isn't something I
> would ever want an app to have access to. It's strictly for manual
> testing purposes via QA.
> Thanks,
> Brett
>

Sunday, February 12, 2012

Beginner- Need Help With SQL Server 2005 Express

I am an absolute beginner with SQL Server, and I have now developed an app that needs to store, read, and search for saved data. Basically, my program will have to search through the databse to find a particular URL match to a string I supply, and then retrieve the Username and Password data that is associated with that URL, storing those to strings.

My problem is, I've learned how to create a database and tables in the IDE, but all of the examples on the web that I've found only talk about using SQL Server with Visual Basic 2005 in uses such as databinding to controls and displaying the database in datagrids and such on the form. I need help with how to interact with the database in code, seeing as the user of the program will likely never actually see the database contents- the data will be for the program's use only.

So, I was wondering if anyone could provide/guide me to some helpful information on interacting with SQL databases through code in VB Express 2005.

Thanks for any help.

In your VB code, define variables, and then instead of assigning data to control.text, assign data to the variables.

|||OK, but then how do I search through all of the fields in 1 column, and then if I find the right one, extract the adjacent columns for that field and save them as strings?