How to make CLR stored procedure generate comments in TSQL when it is deployed via visual studio?
How to make CLR stored procedure generate comments in TSQL when it is
deployed via visual studio?
e.g. when i right click the project and hit deploy, i want some comments to
appear in or around the SQL wrapper that calls the CLR function. Tag: Anyone know of a Silverlight newsgroup? Tag: 144894
Call Dispose on OleDbCommand?
1. Should you explicitly call Dispose on OleDb objects like OleDbCommand or
OleDbConnection?
2. In VB.NET for the keyword USING (as in "Using cn As New
OleDbConnection(...)...")
Should you explicitly call the Close method on the OleDbConnection object or
will the object be closed and disposed automatically once the Using block is
exited?
Thanks for your insights. Tag: Anyone know of a Silverlight newsgroup? Tag: 144892
Varbinary col in a DataTable
Using SQL Server 2005, Visual Studio 2005 (8.0.50727.762) Framework 2.0.50727
SP1
I have a varbinary column in my database. I am trying to set the
DataColumn.DataType in the dataset designer to byte[] but it is not in the
selection list. I tried changing it in the xxxxx.Designer.cs file but of
course the changes were lost when the project was re-built. What is the
solution? Can I code
this.columnMissionStatusImage = new
global::System.Data.DataColumn("MissionStatusImage", typeof(byte[]), null,
global::System.Data.MappingType.Element);
in the .cs file? If so, what event do I put it in? Tag: Anyone know of a Silverlight newsgroup? Tag: 144891
OutOfMemoryException using .NewRow
I am dealing with a very large amount of data add it to a dataset
using DataTable.Rows.Add. When creating the new row using .NewRow I
get an OutOfMemoryException at around row 4 million plus. I know
that
this is a very large number of rows, but I have 4 GB of Ram and the
Task Manager shows that I am only using about 35% of system memory.
Can someone help me understand why I am getting this error when
apparently I am not running out of memory. Do I need to release any
objects during this process. I am pretty much following what every
example shows on using this code. The code can be seen below.
Dim dtLatestDate As DateTime
Dim dsData As New DataSet
Dim dsScadaHistory As New DataSet1
Dim drScadaHistory, drLookup, drNameLookup() As DataRow
Dim idxRows, idxColumns, iRowCount As Integer
Dim dtScadaDateTime As DateTime
Dim strName As String
Dim keyObject(1), keyNameObject(0) As Object
'set up connections to the two SQL Server
Dim conSurvalent As New
SqlClient.SqlConnection(My.Settings.conSurvalent)
Dim conSQL As New SqlClient.SqlConnection(My.Settings.conSQL)
'Set up commands and dataadapters
Dim cmdGetLatestDate As New SqlClient.SqlCommand("SELECT
MAX(DateTime) FROM ScadaHistory", conSQL)
Dim daSurvalent As New SqlClient.SqlDataAdapter("SELECT *
from
ARCHIVE_QSE_Rev3 WHERE TIME > @TIME", conSurvalent)
daSurvalent.SelectCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@TIME",
System.Data.SqlDbType.DateTime, 8, "TIME"))
'setup data adapter for the names pull
Dim daNames As New SqlClient.SqlDataAdapter("SELECT Name,
NameID FROM Names", conSQL)
'setup insert name into names table
Dim cmdInsertName As New SqlClient.SqlCommand("INSERT INTO
NAMES (Name) VALUES (@Name)", conSQL)
cmdInsertName.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Name",
System.Data.SqlDbType.VarChar, 40, "Name"))
'load names table into dataset
daNames.Fill(dsScadaHistory, "Names")
Dim Array(2) As String
'get the latest date for SCADA data
conSQL.Open()
dtLatestDate =3D IIf(IsDBNull(cmdGetLatestDate.ExecuteScalar),
Date.Now.AddYears(-20), cmdGetLatestDate.ExecuteScalar)
conSQL.Close()
'get SCADA data to convert
daSurvalent.SelectCommand.Parameters("@TIME").Value =3D
dtLatestDate
daSurvalent.Fill(dsData, "ScadaHistory")
iRowCount =3D dsData.Tables(0).Rows.Count
While (idxRows < iRowCount)
'get the date time
dtScadaDateTime =3D
dsData.Tables(0).Rows(idxRows).Item("TIME")
'initialize so that will skip TIME column
idxColumns =3D 1
'loop through all columns for each datetime and insert a
row for each match value/status pair
While (idxColumns < dsData.Tables(0).Columns.Count)
'get column name (which is point name)
strName =3D
dsData.Tables(0).Columns(idxColumns).ColumnName
'get NameID from foreign table
keyNameObject(0) =3D strName
drNameLookup =3D
dsScadaHistory.Tables("Names").Select(String.Format("Name =3D '{0}'",
keyNameObject(0)))
'if the name does not exist then insert the name into
the table, which will write back to the dataset
If (drNameLookup.Length =3D 0) Then
conSQL.Open()
cmdInsertName.Parameters("@Name").Value =3D strName
cmdInsertName.ExecuteNonQuery()
'reload dataset with names after insert
daNames.Fill(dsScadaHistory, "Names")
conSQL.Close()
'get NameID from foreign table
keyNameObject(0) =3D strName
drNameLookup =3D
dsScadaHistory.Tables("Names").Select(String.Format("Name =3D '{0}'",
keyNameObject(0)))
End If
'create SCADA history record with datetime and source
name
**************RIGHT HERE IS WHERE I GET THE
ERROR****************************
drScadaHistory =3D
dsScadaHistory.Tables("ScadaHistory").NewRow
'add datetime to row
drScadaHistory("DateTime") =3D dtScadaDateTime
'add name to row
drScadaHistory("NameID") =3D drNameLookup(0)("NameID")
'add value to row
drScadaHistory("Value") =3D
dsData.Tables(0).Rows(idxRows).Item(idxColumns)
'add status to row
drScadaHistory("Status") =3D
dsData.Tables(0).Rows(idxRows).Item(idxColumns + 1)
'add datarow to dataset
dsScadaHistory.Tables("ScadaHistory").Rows.Add(drScadaHistory)
drScadaHistory.AcceptChanges()
'increment column
idxColumns +=3D 2
End While
'increment index
idxRows +=3D 1
End While
*****************************THIS IS THE
ERROR****************************
System.OutOfMemoryException was unhandled
Message=3D"Exception of type 'System.OutOfMemoryException' was
thrown."
Source=3D"System.Data"
StackTrace:
at System.Data.Common.DoubleStorage.SetCapacity(Int32
capacity) at System.Data.RecordManager.set_RecordCapacity(Int32
value) at System.Data.RecordManager.GrowRecordCapacity() at
System.Data.RecordManager.NewRecordBase() at
System.Data.DataTable.NewRecord(Int32 sourceRecord) at
System.Data.DataTable.NewRow(Int32 record) at
System.Data.DataTable.NewRow() at
DBOSchema.Form1.Form1_Load(Object
sender, EventArgs e) in C:\Documents and Settings\All Users\Documents
\Visual Studio 2008\Projects\DBOSchema\DBOSchema\Form1.vb:line 74
at System.EventHandler.Invoke(Object sender, EventArgs e) at
System.Windows.Forms.Form.OnLoad(EventArgs e) at
System.Windows.Forms.Form.OnCreateControl() at
System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl() at
System.Windows.Forms.Control.WmShowWindow(Message& m) at
System.Windows.Forms.Control.WndProc(Message& m) at
System.Windows.Forms.ScrollableControl.WndProc(Message& m) at
System.Windows.Forms.ContainerControl.WndProc(Message& m) at
System.Windows.Forms.Form.WmShowWindow(Message& m) at
System.Windows.Forms.Form.WndProc(Message& m) at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&
m) at
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&
m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr
hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at
System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd,
Int32 nCmdShow) at
System.Windows.Forms.Control.SetVisibleCore(Boolean value) at
System.Windows.Forms.Form.SetVisibleCore(Boolean value) at
System.Windows.Forms.Control.set_Visible(Boolean value) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.Run(ApplicationContext context)
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun=
=AD
()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApp=
=AD
licationModel()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(S=
=AD
tring[]
commandLine) at DBOSchema.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at
System.AppDomain._nExecuteAssembly(Assembly assembly, String[]
args) at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence assemblySecurity, String[] args) at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state) at
System.Threading.ThreadHelper.ThreadStart()
InnerException: Tag: Anyone know of a Silverlight newsgroup? Tag: 144888
How to fill a listview from LINQ query
Currently using a legacy Winform listview, that is filled by a function from
datatable(0) in a dataset. This works fine as the datatable has column
headers and data rows that correspond to listview headers and listview rows.
Would like to use Linq to SQL to populate the same legacy WinForm listview.
The code below retrieves the IOrderedQuery object correctly:
Dim dc As New dcAdminDataContext
Dim query = From tblAdminAddressType In
dc.tblAdminAddressTypes _
Order By tblAdminAddressType.AddressTypeID
gvwAdminData.DataSource = query
The last statement fills a datagridview thru the .DataSource property.
Since, a listview has no .DataSource property, I am at an impasse as to how
to convert query to a datatable, or ? I do not want to specify the data
column names within each query nor declare a class for the query structure.
Any ideas?
Thanks in advance,
Dean S Tag: Anyone know of a Silverlight newsgroup? Tag: 144885
Microsoft architect to discuss LINQ, ADO.NET Entity Framework & Data
The program for DataServices World (June 24, New York City) has been
announced. The conference includes a distinguished lineup of speakers
from BEA, Blue Cross/Blue Shield, IBM, iWay Software, Microsoft,
Morgan Stanley and Sun Microsystems.
Mike Pizzo is the lead architect for Microsoft's data access and data
programmability initiatives. He's been responsible for developing the
ODBC Cursor Library, OLE DB, ADO and ADO.NET.
At DataServices World, Mike Pizzo will speak about LINQ, ADO.NET
Entity Framework and ADO.NET Data Services. He will also participate
in a Data Access and Data Services Workshop with Dr. Michael Carey
(BEA Systems) and Dr. Carlo Innocenti (DataDirect Technologies).
http://dataservicesworld.sys-con.com/general/session0608.htm
"LINQ and ADO.NET Entity Framework and Data Services for the Web"
When: June 24, 2008 (2:00 - 2:50 pm)
Where: Roosevelt Hotel, 45th and Madison Avenue, New York City
"The ADO.NET Entity Framework raises the level of abstraction for data
programming. It is the evolution of ADO.NET that allows developers to
program in terms of the standard ADO.NET abstraction or in terms of
persistent objects (ORM) and is built upon the standard ADO.NET
Provider model. The Entity Framework introduces a set of services
around the Entity Data Model (EDM) (a medium for defining domain
models for an application).
The goal of ADO.NET Data Services is to enable applications to expose
data as a REST-based data service that can be consumed by Web clients
within a corporate network and across the Internet. The data service
is reachable over HTTP, and URIs are used to identify the various
pieces of information available through the service. Interactions with
the data service happens in terms of HTTP verbs, such as GET, POST,
PUT, and DELETE, and the data exchanged in those interactions is
represented in simple formats, such as AtomPub and JavaScript Object
Notation (JSON). " Tag: Anyone know of a Silverlight newsgroup? Tag: 144884
Add column using Dataset Designer => hosed project
Context:
VS2008
SQL Server Compact 3.5 database
Problem:
I used the Dataset Designer to add a column to a table (right click on table
in designer, Add->Column).
How do I update the database to correspond with this change?
I tried locating the database in my Server Explorer and adding the identical
column by hand using the Table Properties.
Run the project and try to stuff some data in the newly modifed table...all
original columns get data but the new column only gets NULLs.
Try to use "Configure DataSet with Wizard" from Data Sources tab...get
warning that "The selected dataset contains objects that can only be viewed
with the Dataset Designer." Which I don't understand because I made what
seems a trivial modification to a table.
It seems there is no correspondence between the new column in the database
and the new column in the dataset. If I access the new column's DataColumn
properties in DataSet Designer, the entry for the "Source" property is empty,
and the dropdown selector only sees the original columns in the table, not
the new one. Tag: Anyone know of a Silverlight newsgroup? Tag: 144880
Databinding to a TextBox in WPF
Is it possible to bind an IList<string> to a TextBox and show the first value
and use Buttons to navigate forward and backwards through the data?
I have a window with just 3 controls:
1. Previous Button
2. TextBox
3. Next button
My goal is to bind a collection of names to the TextBox and be able to cyle
through them with the two navigation buttons.
Here's the code behind I have in place for a XAML (WPF) window:
------------------------------------
private ListCollectionView view;
public DataNavigator()
{
InitializeComponent();
ICollection<string> names = new Data().GetJustNames();
DataContext = names;
view = (ListCollectionView)
CollectionViewSource.GetDefaultView(DataContext);
view.CurrentChanged += view_CurrentChanged;
}
void view_CurrentChanged(object sender, System.EventArgs e)
{
btnPrevious.IsEnabled = (view.CurrentPosition > 0);
btnNext.IsEnabled = (view.CurrentPosition < view.Count-1);
}
private void btnNext_Click(object sender, RoutedEventArgs e)
{
view.MoveCurrentToNext();
}
private void btnPrevious_Click(object sender, RoutedEventArgs e)
{
view.MoveCurrentToPrevious();
}
---------------------------------------------------------------------------------------------
Now I'm pretty sure the navigation is working because the buttons become
disabled correctly.
My only question is how do I set the binding to the TextBox.Text property in
XAML?
<TextBox Margin="139,56.5,169,65.5" Name="tbName" Text="{Binding Path=?}"/>
I'm not sure what the Path should be.
I don't think I have to set the source because the TextBox is within the
DataContext right?
Thanks for any insight you might be able to provide!
Mark Moeykens Tag: Anyone know of a Silverlight newsgroup? Tag: 144878
Varbinary column in a DataTable
Using SQL Server 2005, Visual Studio 2005 (8.0.50727.762) Framework 2.0.50727
SP1
I have a varbinary column in my database. I am trying to set the
DataColumn.DataType in the dataset designer to byte[] but it is not in the
selection list. I tried changing it in the xxxxx.Designer.cs file but of
course the changes were lost when the project was re-built. What is the
solution? Can I code
this.columnMissionStatusImage = new
global::System.Data.DataColumn("MissionStatusImage", typeof(byte[]), null,
global::System.Data.MappingType.Element);
in the .cs file? If so, what event do I put it in? Tag: Anyone know of a Silverlight newsgroup? Tag: 144876
LINQ derived NullReference
NullReferenceException when Inserting from derived class
STEPS to reproduce:
1- Define a database table Foo(ID:int(PK), Name:nchar(10))
2- Create an application project.
3- Define a LINQ to SQL model and add entity 'Foo' from the created table.
4- Add a new class 'FooEx' to the project, inherit it from 'Foo',
5. Add an extra property of type 'int' named 'ExtraInfo' to FooEx.
6. Add a statically initialized DataContext to FooEx
7. Create a public method Insert in FooEx
8. In the insert method put db.Foos.InsertOnSubmit(this); where db is the
DataContext instance for the class
9. In the insert method put db.SubmitChanges; where db is the DataContext
instance for the class
10. Enter and execute the following code in 'Main' or button click or such.
FooEx f = new FooEx();
f.ID = 1;
f.Name = "OhOh";
f.Insert();
Actual ResultsUpon executing the described project, the following exception
is thrown when trying to attach the record:
NullReferenceException:
Message: "Object reference not set to an instance of an object."
Occurs on â?? InsertOnSubmit.
update fails similarly
Note: linq should have its own newsgroup- currently it is being entered in
C#, Framework, and this one Tag: Anyone know of a Silverlight newsgroup? Tag: 144870
How to Update an existing schema on a typed dataset
I need to do what seemes a simple task, but i am stuck. I have a
column in my database i need to change from 7 chars to 10 chars. I
have a number of strongly typed datasets in my system that use this
table.
I attempted to edit the xsd file in the designer and changed the 7 to
a 10.
But the underlaying class file did not get updated. When i searched
for the column i need to change it still was set to 7.
Then I attempted to run the "Table Adapter Configuration Widard".
This
actually broke the application and it would not compile. I then
noticed that duplicate colmns appeared in the table in the dataset.
For example there was ProjectId and ProjectId1. ProjectId1 did not
exist before.
I hope i am missing something simple here.
I did change the code generated files to widen the columns in
question
and the app seemed to work ok, but i am sure this is not the prefered
way to go,
any help would be appreciated. Tag: Anyone know of a Silverlight newsgroup? Tag: 144868
Replacement for dataadapter
Hi Experts,
I am having new problem again.
This is regarding SqlDataAdapter fill method performance. Even though
its discussed several times my problem is little different.
I had migrated one VB6 application to .Net 2.0. In VB6 application
they were using RecordSet.
Like,
----------------------
Dim rs As New ADODB.Recordset
cnn.Open GetDataProvider()
rs.Open cSQLUpdateTimeSeries, cnn, adOpenDynamic, adLockPessimistic
----------------------
Later, RecordSet get updated, Like
----------------------
For k = LBound(data, 1) To UBound(data, 1)
rs.AddNew
rs![sessionID] = session
.
.
.
rs![ReportingDate] = Dates(LBound(Dates, 1), k)
.
.
Next
rs.Update
End If
Next
rs.Close
cnn.Close
----------------------
When I migrated the code to ASP.Net 2.0, I used SqlDataAdapter.
----------------------
adap = New SqlDataAdapter(cSQLUpdateRequest, cnn)
Dim cmdBuilder As SqlCommandBuilder = New
SqlCommandBuilder(adap)
adap.InsertCommand = cmdBuilder.GetInsertCommand()
adap.Fill(dt)
----------------------
Here in between some calculation is happening and need to update in
database.
Dataset is updated like,
----------------------
For k = LBound(data, 1) To UBound(data, 1)
Dim dr As DataRow = dt.NewRow()
dr("sessionID") = session
.
.
.
dr("ReportingDate") = Dates(LBound(Dates, 1), k)
.
.
dt.Rows.Add(dr)
Next
adap.Update(dt)
----------------------
Here my problem is <b> adap.Fill(dt) </b> is extemly slow and Is there
any different approach for the above scenario to update the database
(otherthan using adapter) ?
Thank you
Sriharsha Karagodu Tag: Anyone know of a Silverlight newsgroup? Tag: 144867
Databinding to a listview
Hi,
I'm trying to populate a list box using a dataset. The data set is tested
fine to have data in it but when I run the app, the listbox does not get
populated. Below is what I'm doing:
Class1 myclass = new Class1();
myclass.Server = "LAPTOP";
myclass.DB = "Northwind";
DataSet ds = myclass.ReturnDS();
ListBox1.DataSource = ds.Tables[0];
ListBox1.DataTextField = "CustomerID";
ListBox1.DataBind();
// works ok, returns field so Dataset contains data
Page.Response.Write(ds.Tables[0].Rows[0][0].ToString();
Why doesn't this work?
Many thanks for any ideas in advance
Ant Tag: Anyone know of a Silverlight newsgroup? Tag: 144866
DataContext - how to use
Hi,
I'm using LINQ for the first time in a "full" projekt and need some
help on how to manage the datacontext. When having one function
calling other functions doing LINQ work (select and or update) should
I pass the datacontext along as a parameter (ei create one and use
that), og should I let functions create (and dispose) there own
datacontext? Do you have any advise about using a static global
datacontext?
Thanks Tag: Anyone know of a Silverlight newsgroup? Tag: 144863
Q: dynamically created databound controls persist but static controls
I am running into a case where I am databinding a control
(dropdown, checkboxlist, etc) to a datatable,
caching the datatable in session,
and on postback rebind the control.
After postback the control loses its value.
However if I try this with a dynamically created control,
it maintains state just fine.
(I would think it would be the other way around.)
You can cut/paste my aspx and codebehind into your project from here:
http://www.geocities.com/usenet_daughter/databind_persist_issue.htm
How would you get the static control (CheckBoxList1)
to maintain state after postback?
I am curious why this is happening.
Thanks Tag: Anyone know of a Silverlight newsgroup? Tag: 144860
Where to: Questions About EDM and CSDL
We are about to start serious design on a new app here. I would like
to use the Entity Framework stuff that's new in Framework 3.5 and
since it's not even officially released yet, I'm sure to have some
questions. So my question is
"microsoft.public.dotnet.framework.adonet" the best group to ask
questions about EDM and CSDL or is there a more appropriate place to
do so? Writing the schemas by hand doesn't look all that appealing but
it's still light-years better than writing a million SQL stored
procedures. Our userbase is relatively small but they want the app
ASAP, so I'm less worried about any potential loss of performance by
eliminating SQL stored procedures and more motivated by reduced
development time and lower maintenance requirements. Tag: Anyone know of a Silverlight newsgroup? Tag: 144857
How to read sequentially from a random point in a large Xml File.
Hello
Have a huge XML file with multitudes of "LogEntry" nodes / text
lines.
A small sample of this xml/text content is below.
The file could be anywhere between 200 to 2000 MB.
My questions comes in two parts.
(A)
I would like a solution (or ideas for it), in C#, -- to randomly
access a really huge xml file, and to sequentially read only the
"memory permiting" number of nodes into memory, from a place randomly
selected in the huge file. The application otherwise returns an out
of memory error or gets very slow, if i try to load the entire file,
because the user like to "scroll" through the file, viewing different
parts. Like when scrolling through a huge Word document.
How is this (best) done?
(B)
What is, and/or how would i estimate, the max amount of xml or text
from the file that the application can have in it's memory? The
application is both a web applicatin and a windows standalone.
On a 32bit machine with 2GB Ram, the virual memory is 2GB, which gives
an answer.
But i have a Java app that goes in a unhandled heap error already when
loading xml from a 200MB size file.
Any ideas, solutions, or links concerning the above (especially (A))?
One avenue is to try to base a sequential reader on a random access
stream.
I tried this idea. I based the XmlTextReader (for seqeuntial read) on
the FileStream (for randon access), but this didnt work. There is
some test code at the bottom of this email that shows some of this.
I used the FileStream for random access via the FileStream.Seek(..)
method.
But the XmlTextReader.Read() didn't start reading from the new
position.
The following:
FileStream.Seek(<Random NewPosition>, SeekOrigin.Begin);
FileStream.Read();
would read from a the new position, but it didnt effect the
positioning of XmlTextReader.Read().
Even though XmlTextReader is based on the same FileStream.
It caused though the last read of the XmlTextReader to validate the
xml erroneously (when the xml was actually ok).
An alternative is to base a StreamReader on a FileStream.
The StreamReader.BaseStream is available for random access, and the
StreamReader is there for sequential read.
But i think the same problem is there, as when basing the
XmlTextReader on the FileStream.
As a side thought to the problem, - it could be more easily solved if
MicroSoft offered an indexing mechanism (for application purposes) on
NTSF files. But this isn't the case. Or if i could load the huge
file into a database table, but the requirement is only to use xml
files (or flat files), so this isn't an option.
This question involves several "technologies". So i am posting it on
several newsgroups.
Here's a sample of the XML:
Each "LogEntry" node is viewed as line of text in a GridView
controller.
<Logs AtrA="AllTheLogs">
<Log AtrA="log1" AtrB="Machine nr 1">
<LogEntry AtrA="name1" AtrB="time" AtrC="location" />
<LogEntry AtrA="name2" AtrB="time" AtrC="location" />
<LogEntry AtrA="name3" AtrB="time" AtrC="location" />
<LogEntry AtrA="name4" AtrB="time" AtrC="location" />
</Log>
<Log AtrA="log2" AtrB="Machine nr 1">
<LogEntry AtrA="name5" AtrB="time" AtrC="location" />
<LogEntry AtrA="name6" AtrB="time" AtrC="location" />
</Log>
</Logs>
Some test code using XmlTextReader(FileStream) based on a file with
the above xml.
I used the VS debugger to look into the variables.
System.IO.FileStream fs = null;
int i = 0;
long[] bookMarks = new long[4000];
String[] linesOfText = new String[4000];
byte[] aBuffer = new byte[1000];
char[] charBuffer = new char[1000];
try
{
fs = new FileStream("c:\\aXMLfile.xml",
FileMode.OpenOrCreate);
System.Xml.XmlTextReader reader = new
XmlTextReader(fs);
long lngthOfFS = fs.Length;
Boolean a = false;
while (reader.Read())
{
bookMarks[i] = fs.Position;
StreamReader sr = new StreamReader(fs);
if (i == 2)
{
fs.Read(aBuffer, 0, aBuffer.Length);
fs.Position = 0;
fs.Read(aBuffer, 0, aBuffer.Length);
for (int g = 0; g < aBuffer.Length; g++)
{
charBuffer[g] = (char)aBuffer[g];
}
}
linesOfText[i] = "Attribute count: "
+ reader.AttributeCount
+ ", NodeType: "
+ reader.NodeType
+ ", Name: "
+ reader.Name
+ ", value: "
+ reader.Value;
a = reader.HasAttributes;
if (reader.HasAttributes)
{
for (int ii = 0; ii < reader.AttributeCount; ii
++)
{
reader.MoveToAttribute(ii);
linesOfText[i] = linesOfText[i]
+ "Attribute " + ii.ToString() + ":"
+ ", Name: "
+ reader.Name
+ ", value: "
+ reader.Value;
}
}
i++;
}
}
catch(Exception e)
{
String message = e.ToString();
}
finally
{
fs.Unlock(0, fs.Length);
}
Other references:
Efficient Techniques for Modifying Large XML Files
http://msdn2.microsoft.com/en-us/library/aa302289.aspx
XML Reader with Bookmarks
http://msdn2.microsoft.com/en-us/library/aa302292.aspx
The Best of Both Worlds: Combining XPath with the XmlReader
http://msdn2.microsoft.com/en-us/library/ms950778.aspx
Comments to references:
Helena Kupkova developed a XmlBookmarkReader class (based on
XmlReader). But when XmlBookmarkReader sets a bookmark on a read
node, it caches it and the following node, to be able to "replay" the
bookmark when it is needed. On huge files, an early bookmark will
cache the xml content of the file until the applicaiton runs out of
memory.
Dare Obasanjo XPathReader doesnt avoid a sequential read of the file,
testing for each read, for a match for one or more xpaths. For a new
XPath, the code would have to seqential reading from the start of the
file.
--
Regards,
Paul Tag: Anyone know of a Silverlight newsgroup? Tag: 144856
Question to the generals in this newsgroup including MSFT's
Is this a FrameWork.ADONET or a LINQ newsgroup?
As I see so many non LINQ to ADONET questions answered at the moment.
Cor Tag: Anyone know of a Silverlight newsgroup? Tag: 144843
Reading a text field
How do I get the data out of an MS-SQL Text field efficiently
A simple "SELECT [FieldName] FROM [Table]" unfortunately returns a truncated
version of the fields contents ( which actually contains an entire book ).
I know that your not supposed to do a simple select, but my Google-Fu is
weak and I dont know what words to search for.
Can anyone supply some hints?
Thanks Tag: Anyone know of a Silverlight newsgroup? Tag: 144836
SQL 2005 Error connecting to SQL2000
Hi,
I'm connecting to an SQL2000 Server, it runs a very slow SP that takes
around two minutes. I've set the command & connection timeout to handle this.
It used to work fine but now I get a seemingly unrelated error. I can connect
remotely to do simple queries & LINQ queries but the SP doesn't run.
Any ideas would be gratefully appreciated
Below is the error message:
"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)" Tag: Anyone know of a Silverlight newsgroup? Tag: 144830
Linq SubmitChanges and DataLoadOptions
I have some code that has a DataLoadOption to load Master/Detail records.
When I update the Master table, the object gets returned but the Details
records are not loaded. Is their a work around for this?
using (MyDataContext dc = new MyDataContext())
{
DataLoadOptions lo = new DataLoadOptions();
lo.LoadWith<Job>(s => s.JobSections);
var query = from obj in dc.Jobs
where obj.JobID == 1
select obj;
job = query.Single(); //yep job object has detail
section data
dc.SubmitChanges();
//nope job does not have detail section data.
} Tag: Anyone know of a Silverlight newsgroup? Tag: 144825
linq combined query
I tried to make some complex linq queries easier to read by pulling some of
the queries out.
This works fine:
var qAllJobDescriptions = from j in dc.Jobs
select j;
var qAllOracleJobCodes = from o in dc.OracleJobCodes
select o.JOBCODE;
GridView_Ex3.DataSource = from j in qAllJobDescriptions
where
!(qAllOracleJobCodes).Contains(j.OracleJobCode)
select j;
This works fine:
var qActiveJobDescriptions = from j in dc.Jobs
where DateTime.Today >= j.StartDate
&& (j.EndDate == null || (DateTime.Today <
j.EndDate.Value.AddDays(1)))
select j;
var qActiveOracleJobCodes ="";
GridView_Ex2.DataSource = (from j in qActiveJobDescriptions
where !(from o in dc.OracleJobCodes
where
DateTime.Today >= o.DATEFROM
&& (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
select
o.JOBCODE).Contains(j.OracleJobCode)
select new { j.OracleJobCode })
This blows up when I move the where !(....).Contains into its own query.
Seems odd because I did a similar thing in the first example.
:
var qActiveJobDescriptions = from j in dc.Jobs
where DateTime.Today >= j.StartDate
&& (j.EndDate == null || (DateTime.Today <
j.EndDate.Value.AddDays(1)))
select j;
var qActiveOracleJobCodes =from o in dc.OracleJobCodes
where
DateTime.Today >= o.DATEFROM
&& (o.DATETO == null ||
(DateTime.Today < o.DATETO.Value.AddDays(1)))
select o.JOBCODE;
GridView_Ex2.DataSource = (from j in qActiveJobDescriptions
where
!(qActiveOracleJobCodes).Contains(j.OracleJobCode)
select new { j.OracleJobCode })
((System.Exception)((new
System.Linq.SystemCore_EnumerableDebugView<<string>>(((System.Data.Linq.DataQuery<<string>>)(GridView_Ex2.DataSource)))).Items)).StackTrace
at System.Data.Linq.SqlClient.SqlFactory.Member(SqlExpression expr,
MemberInfo member)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMemberAccess(MemberExpression
ma)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitCast(UnaryExpression c)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitBinary(BinaryExpression
b)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitBinary(BinaryExpression
b)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression
sequence, LambdaExpression predicate)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitSelect(Expression
sequence, LambdaExpression selector)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitContains(Expression
sequence, Expression value)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitUnary(UnaryExpression u)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression
exp)
at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression
sequence, LambdaExpression predicate)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.VisitSelect(Expression
sequence, LambdaExpression selector)
at
System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)
at
System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)
at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)
at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)
at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query,
SqlNodeAnnotations annotations)
at
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
at
System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.SystemCore_EnumerableDebugView`1.get_Items() Tag: Anyone know of a Silverlight newsgroup? Tag: 144823
Error in field binding when using same table for combobox drop down and lookup
Hi
I have a sample vs 2008 winform project here;
http://www.infovis.biz/TestEMS-vs2800.zip . Please extract it in C:\ folder
so the project ends up in C:\Test EMS folder.
Please run the project and you will see a data entry form. Use the buttons
in the form's bottom area to scroll through the records. Everything works
fine. You can even change text in fields and moving away from record will
save it.
Now go back into project and uncomment line 196 in frmStaff.vb ie
Me.txtRate.Text = CStr(DLookup("[Rate]", "[Rates]", "[Rate ID] = """
& txtRateID.Text & """"))
Now run the project and scroll through records. Soon you will see that
information stops appearing in fields. The purpose of above line is that
when user selects a Rate ID from the combobox drop down, the system looks up
the corresponding rate amount and fills the Rate field with it. So my
question is, why is this lookup causing this problem and what is the correct
way to lookup a value and assign it to a bound field without causing this
problem?
Thanks
Regards Tag: Anyone know of a Silverlight newsgroup? Tag: 144822
Update not working
I'm developing a Web App in C#. The following method executes without
exceptions but the database does not get changed. The data is changed in the
record[0] at the point prior to executing the " adapter.Update(record);"
instruction.
Is there something else I should be doing to insure the update to take
effect???
Thanks, Neil
protected void UpdateUserFields(String id, String field, String value)
{
//***********************************************************// ***** Make
connection, process request, break connection
//***********************************************************
using (OdbcConnection connection =
new OdbcConnection(ConnectionBuilder().ConnectionString))
{
// ***** Make connection and get dataRows *****
String sql = "select * from Register where Id='" + id + "'";
OdbcDataAdapter adapter = new OdbcDataAdapter(sql,
connection);
DataSet dataSet = new DataSet();
try { connection.Open(); adapter.Fill(dataSet, "Register"); }
catch (Exception ex) { Console.WriteLine(ex.Message); }
DataRow[] record = dataSet.Tables["Register"].Select();
if (record.Count() == 0) return;
//****** Process update request ******
// Update the cpu id list in the user's registation record
if (field=="CpuId") record[0]["CpuId"]=value;
//****** Update the data base with changes ******
// Mark the changes for updating
record[0].AcceptChanges();
// Update the changes
adapter.Update(record);
}
} Tag: Anyone know of a Silverlight newsgroup? Tag: 144817
Internet - Take Advantage of Multiple Windows When Surfing
--
Internet - Take Advantage of Multiple Windows When Surfing
SUMMARY: Stop jumping back and forth between index pages by using several
browser windows.
Why use one web browser window when you can have multiple? If you have an
adequate amount of memory, open up several web browsers to maximize your
surfing experience.
One great way to do this is via a start page. Select a page from where you
would like to begin browsing, such as a table of contents or links page.
Now, instead of left-clicking on links or entries to view other pages,
right-click the links. From the pop-up menu that appears, look for something
like "Open in New Window" or "Browse in New Window." This starts up another
web browser window with the link you selected, keeping the original page on
the screen.
http://www.visitbazaar.com
VisitBazaar.com has you a browser application which gives the experience of
multi window surfing
simultaneously, and also increases the internet browsing speed. Tag: Anyone know of a Silverlight newsgroup? Tag: 144816
How to check if SQL Server is local or remote
Hello everyone,
I have a small c#1.1 app that uses an Oledb connection to connect to SQL
Server (2000/2005). What I'd like to know if there is an easy way to
understand if the connection points to a local SQL Server or a remote SQL
Server...
Of course I could inspect the connectionstring and check for (local) or
127.0.0.1 or resolve the machine name...
I was just wondering if there is an easier way...
TIA
Paolo Tag: Anyone know of a Silverlight newsgroup? Tag: 144815
Error : Failed to find or load the registered .Net Framework Data Provider
I get the error message "Failed to find or load the registered .Net
Framework Data Provider" when I drag database table from database explorer
window to Dataset designer window. I also get this error when I click New
file/Sql server database. I have Visual Web Developer 2008 Express Edition
and 2 instances of ms sql 2005 : developer and express (named .\sqlexpress).
Any idea? Tag: Anyone know of a Silverlight newsgroup? Tag: 144808
New db application advise
Hi
I need to write my first vb.net/sql server app and I just need pointers as
to how pros do it. My app is a multi-user apps which needs to refresh from
what other users are adding to the backend sql server db.
My question is;
1. Do I just drag the fields on to a form and let the vs 2008 generate all
code (I understand bindingsource will not automatically show the records
added by other user in this technique?), or
2. Write some sort of stored procedures to return one record or some records
at a time) at a time and assign manually to the form fields, or
3. Do something else?
Thanks
Regards Tag: Anyone know of a Silverlight newsgroup? Tag: 144806
Data is different than in database
I am using MS Access and VB .NET 2005 (the same problem happens in VB.NET
2008).
I am reading from a query using DataReader (the same problem happens if I
use OLEDBDataAdapter).
When I run the query in Access, the value for Cash = 830.004999999999, SC =
1692.5 and Profit = -862.495000000001.
Cash = SC + Profit.
But when I read the value from the program, this is what I got:
Cash = 830.000000000489
SC = 1692.5
Profit = -862.499999999511.
Why this difference and how can I fix it ?
Thank you.
Here are the codes:
Dim m_cmd As OleDb.OleDbCommand
Dim m_dr As OleDb.OleDbDataReader
Dim sSQL As String
m_cmd = New OleDb.OleDbCommand
With m_cmd
.Connection = adoConOLE
.CommandText = "select Cash,Profit from myQuery where Account =
'123'"
End With
m_dr = m_cmd.ExecuteReader()
If m_dr.Read Then
sSQL = sSQL & " " & m_dr.Item("Cash") --> this returns
830.000000000489 instead of 830.004999999999 when I run it from Access
sSQL = sSQL & " " & m_dr.Item("SC") --> this returns 1692.5,
which is the same as when I run it on Access
sSQL = sSQL & " " & m_dr.Item("Profit") --> this
returns -862.499999999511 instead of -862.495000000001 when I run it from
Access
End If Tag: Anyone know of a Silverlight newsgroup? Tag: 144802
Sync Services for ADO.Net and SQL 2008 Change Tracking, example co
I have been searching for a functional example that demonstrates function
between these two products for 8 days. I have exchanged emails with two
senior guys associate with the Sync Framework team (Steve Lasker and Liam
Cavanagh). Steve referred me to Liam and Liam referred me to this sample code:
http://msdn2.microsoft.com/en-us/library/cc305322(SQL.100).aspx
This code looked like salvation. Unfortunately, I cannot get it to compile
on the dev VPC I have dedicated for this purpose. There are versioning issues
with the Sync Service for ADO.Net and Sync Framework dlls on the machine. I
have uninstalled them and reinstalled them to no avail. I have a message from
a great help in another forum who can get the example code to compile in his
environment; he told me the version on one of the DLLs he's using and I can't
get/locate that version. I have two versions of the binary in question; an
older and a seemingly newer version, but no the middle version that works.
All of that rambling may be moot. I simply need a functional Sync Services
for ADO.Net example that makes use of SQL Server 2008's Change Tracking
functionality (brand new in CTP 5). I need to do bi-directional sync'ing from
a c# windows app to SQL Server 2008.
Any bidirectional/Change Tracking-based example will do, so long as it is
functional. I require MS support on this one. If you can refer me to a more
accurate place to get timely support, please do so. I can be contacted at
uwcanada at gmail com.
Thank you in advance Tag: Anyone know of a Silverlight newsgroup? Tag: 144792
Loading a generic collection from a SqlReader?
Hi, is there any underlying difference between loading a generic collection
from a reader using the two methods:
List<User> colUsers = new List<Users>();
while (reader.Read())
{
User user = new User();
user.FirstName = (string)reader["FirstName"];
user.LastName = (string)reader["LastName"];
colUsers .Add(user);
}
----vs----
while (reader.Read())
{
colUsers.Add(new User
(
(string)reader["FirstName"],
(string)reader["LastName"]
));
}
Creating the object first and then adding values worked fine. But using the
second method by loading the values in the constructor, the colUsers.Count
was correct based on the query, but each of the object's properties in the
collection were null. it's as if the object's weren't retaining their values
or state when added to the collection. Tag: Anyone know of a Silverlight newsgroup? Tag: 144790
LINQ-to-SQL transaction problem in calculated property
I have a DBML object that maps database to objects.
In the partial Text class I added a couple of calculated properties.
Several of them are dependant on how many child object are related to the
current object. To avoid duplicate database calls I got the idea to set local
variable _childrenCount in the Text OnLoaded event.
partial void OnLoaded()
{
// ChildrenCount is used in many calculated properties and is therefore
set already on Load.
using (MyDataContext dc = MyDataContext.CreateInstance())
{
_childrenCount = dc.Texts.Where(t => (t.TextIDParent == this.TextID)
&& (t.TextID != this.TextID)).Count();
}
}
I found no way to refer to and reuse the current datacontext object to do
the call, therefore I created an instance method on the datacontext object to
provide a new object. But it really looks like a workaround.
The above solution actually worked fine until I added TransactionScope to my
code. When a retrieved Text object is involved in a transaction it will fail
with the following message: "MSDTC on server XXX is unavailable". When the
above event handler is removed everything works fine.
I guess the transaction will detect a new datacontext object is called and
expect it to take part in the transaction. Actually it should not bother
beacuse it is only a passive read.
1) Is it possible to reuse the outer datacontext call for the inner
calculation?
2) Can I tell the inner datacontext not to take part in the outer transaction?
3) Is there a better way to set the value of the inner private variable
_childrenCount?
4) Should I configure DTC and/or exchange TransationScope with Transaction? Tag: Anyone know of a Silverlight newsgroup? Tag: 144785
The user instance login flag is not supported on this version of S
I copied a C# project from one machine to another both XP Pro with VS2005.
Then solution compiles but when I run the app I get the error message on the
first dataadapter.FILL() command:
"The user instance login flag is not supported on this version of SQL
Server. "
SQLExpress and SQ 2000 is installed, but I am specifiing SQL Express in the
connection string see below and the SQL 2000 server is not started.
Do I need to uninstall the SQL 2000 server? Shouldn't this work with the
SQL 2000 on the same machine?
When I just remove the "User Instance =True" from the connection string I
still get this message. What am I doing wrong?
<connectionStrings>
<add name="CMMB1.Properties.Settings.CallMasterSQLConnectionString"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename="C:\Program
Files\CALLMaster\Data\CallMasterSQL.mdf";Integrated Security=True;Connect
Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
--
Thanks
Morris Tag: Anyone know of a Silverlight newsgroup? Tag: 144779
DataColumn DataType Conversion
I have an application that takes a DataTable full of string values and then
inserts them into a SQL Table with SqlBulkCopy. I have one unresolved issue.
The values in the DataTable are all strings, but the fields in the SQL
Table, are not all string types. So we have the following dilemma:
Converting the DataType of a DataColumn in a DataTable that is already
populated is illegal. I certainly can use the Expression field of the
DataColumn class and create a new DataColumn with the appropriate DataType,
but I cannot leave the original column as all columns are used in SqlBulkCopy
and removing the original column invalidates the expressions. And so:
Is there any way to create a new DataTable, by copying the Columns that
don't need conversion and the values in the Expression columns, without going
value by value through the whole table? Tag: Anyone know of a Silverlight newsgroup? Tag: 144776
Get return code from SQL Server Sproc?
Say I have a procedure that returns a return code like the following...
CREATE PROCEDURE TestProc
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM MyTable;
RETURN 10
END
And I execute that in a sql command object, how does my client application
get the return code back? Which in this case is 10.. thanks! Tag: Anyone know of a Silverlight newsgroup? Tag: 144772
Obtaining a List of Databases
I can use the SqlDataSourceEnumerator to get a list of SQL Server instances.
How do I obtain a list of databases in a given SQL Server instance? Tag: Anyone know of a Silverlight newsgroup? Tag: 144764
Help with multiple connector/table query
As a newbie to ADO.NET I am struggling with the following:
I have an ODBC data table that I want to use a source for updates to a
SQL server table, I envisage the sql query having the following
structure:-
INSERT INTO SQL_TABLE FROM SELECT ODBC_TABLE WHERE ODBC_TABLE.KEY NOT
IN (SELECT SQL_TABLE.KEY FROM SQL_TABLE)
How do I do this in ado.net if a sql query is run against a specific
connection object?
Hope the above makes sense.
Help greatly appreciated.
Regards, Tag: Anyone know of a Silverlight newsgroup? Tag: 144755
OracleClient, Oracle 10g, Connection failure
Hi,
I'm trying to connect to an Oracle database via a .NET web service running
in IIS on a Windows 2000 server.
I am using the OracleClient (System.Data.OracleClient) provider . When I
call connection.Open() I get the following error -
'System.Data.OracleClient requires Oracle client software version 8.1.7 or
greater'
The Oracle 10 client is installed on the box as well as .NET framework V 2.0.
Any ideas as to why this is happening? Do I need to install something else?
Many thanks,
Dav Tag: Anyone know of a Silverlight newsgroup? Tag: 144754
Connection Open Error
Hi,
Below is the code written for opening a sql server connection and
database transactions.
private SqlConnection _mConnection;
private SqlCommand _mCommand;
private SqlTransaction _mTransaction;
public SqlDataAdapter CreateDataAdaptor(string comText,
CommandType ComType, string Attribute)
{
try
{
_mCommand = new SqlCommand();
_mConnection = new
SqlConnection(ConnectionString(Attribute));
_mCommand.Connection = _mConnection;
_mCommand.CommandType = ComType;
_mCommand.CommandText = comText;
if (_mConnection.State != ConnectionState.Open)
{
_mConnection.Open();
}
SqlDataAdapter da = new SqlDataAdapter(_mCommand);
return da;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_mConnection.State == ConnectionState.Open)
{
_mConnection.Close();
_mConnection.Dispose();
_mCommand.Dispose();
}
}
}
public void Insert(string comText, Parameters Params,
CommandType ComType, string Attribute)
{
try
{
_mCommand = new SqlCommand();
_mConnection = new
SqlConnection(ConnectionString(Attribute));
_mCommand.Connection = _mConnection;
_mCommand.CommandType = ComType;
_mCommand.CommandText = comText;
//_mCommand.Transaction = _mTransaction;
if (_mConnection.State != ConnectionState.Open)
{
_mConnection.Open();
}
if (ComType == CommandType.StoredProcedure)
{
if (Params != null)
{
foreach (Parameter oParam in Params)
{
_mCommand.Parameters.AddWithValue(oParam.Name, oParam.Value);
}
_mCommand.ExecuteNonQuery();
}
}
else
{
_mCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (_mConnection.State == ConnectionState.Open)
{
_mConnection.Close();
_mConnection.Dispose();
_mCommand.Dispose();
}
}
}
Though am opening and closing the connection properly,sometimes i am
getting this error "Connection is already open"."Please close the
connection".(when there are a no of users logon to the application)
Can someone tell me whether i am doing something wrong.
Romi Tag: Anyone know of a Silverlight newsgroup? Tag: 144747
Handling concurrency violations
Hi
I have developed the following logic to handle db concurrency violations. I
just wonder if someone can tell me if it is correct or if I need a
different approach.Would love to know how pros handle it.
Thanks
Regards
Dim dc As DataColumn
Dim drCache As DataRow
Dim drCurrent As DataRow
Try
' Attempt the update
daContacts.Update(ds.Contacts)
Catch Ex As DBConcurrencyException
' First - cache the row
drCache = ds.Contacts.NewRow()
For Each dc In ds.Contacts.Columns
If Not dc.ReadOnly Then
drCache(dc.ColumnName) = Ex.Row(dc.ColumnName)
End If
Next
' Refresh from database
daContacts.Fill(ds.Contacts)
' Position to the faulted row
drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID"))
' Apply User Changes
For Each dc In ds.Contacts.Columns
If Not dc.ReadOnly Then
drCurrent(dc.ColumnName) = drCache(dc.ColumnName)
End If
Next
' Save again
daContacts.Update(ds.Contacts)
End Try Tag: Anyone know of a Silverlight newsgroup? Tag: 144743
Connection Pooling, REVERT & "Connection Reset" in .NET 2.0 SP1+
Hi,
My company is building a multi-tenant internet-facing web app in ASP.NET
/ SQL 2005. We can't therefore use NT authentication, and must use
our own web forms authentication, but still need to use SQL's "Database
User"-based security so that we can encapsulate security in stored procs
etc by checking user_id() - we create these DB users (for each web end
user) "WITHOUT LOGIN" and multiplex through a single proxy account.
Legacy reasons mean we can't design around this requirement.
We have used Enterprise Library to standardize DB access, and
implemented a new Database Provider to implement the security
requirement above while still allowing scalability through connection
pooling: when the Connection's open event is called, we connect to SQL
Server using a proxy account and call "EXECUTE AS" the desired db user,
changing the connection context. We store the 'cookie' returned from
this call against the connection SPID so that on subsequent Connection
Open (which gives us a connection from the pool which might have already
been set to another user's context) we check the SPID to see if there's
already cookie, use REVERT WITH COOKIE to revert to proxy account,
before calling "sp_reset_connection" which resets the connection and we
can set to new user context.
Why do we try to REVERT on connection Open? We would rather REVERT just
before connection close but we cannot control when this occurs
(datareader consumers of the connection, which we cannot control, may
close the connection automatically) or hook into an event (statechanged
only tells us AFTER the connection is closed - cannot reopen!)
This all required us to use the "Connection Reset=false" connection
string setting, which ensures that sp_reset_connection (SQL internal SP)
is not called automatically because we must REVERT BEFORE this proc is
called otherwise the connection is dropped by SQL server because the
reverting context is different to original proxy login.
This slightly complicated (but quite compact and elegant) solution works
fine on .NET 2.0 RTM. BUT it seems that from .NET 2.0 SP1 onwards, the
"Connection Reset" connection string API has been deprecated! This means
the connection pooling mechanism ignores the setting and tries to
execute "sp_reset_connection" on each connection open as a different
context than the original login and SQL server duly kills the connection
(and crashes the app).
In short, I've run out of options for REVERTing the connection just
before it's closed. The connection statechange event is too late and
binding to 3rd party controls means they may use datareaders which
automatically close the connection before I can intervene.
The only relevant Microsoft documentation I have found (on App Roles and
connection pooling) states:
".. if pooling is enabled, the connection is returned to the pool, and
an error occurs when the pooled connection is reused. If you are using
SQL Server application roles, we recommend that you disable connection
pooling for your application in the connection string."
GRRR! How does Microsoft expect us to build scalable applications with
the features specifically designed for it (connection pooling, EXECUTE
AS + REVERT) if these features no longer work together?
Hosting this app without connection pooling is unacceptable, and I don't
want to have to rewrite the entire security infrastructure. It seems my
only option is to use compile my own version of Enterprise Library,
implementing my own Connection object which can intercept the Close
method calls - this is neither elegant, nor maintainable, and I'm hoping
that someone has a better idea? If there _is_ a way to grab an event on
the connection just before it closes, that would be perfect...
Anyone?
Many thanks for your time, and any help you can give...
Simon
RIP "Connection Reset":
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.connectionreset.aspx Tag: Anyone know of a Silverlight newsgroup? Tag: 144741
Getbytes usage from VS 2003 to VS2005
When accessing a BLOB from a DB using getbytes, 0 bytes are returned. The
same thing worked with VS2003. The recent environmental change was from
VS2003 to VS2005 and also from .NET 1.1 to .NET 2.0.
/* Here is the function that reads the blob from the db */
private bool GetImageSeq(ref byte[] SeqImage,string PatId,string AdmDate,int
ImgSeq,int SeqSz)
{
long RtnBytes=0,startIndex=0;
string MyStr="",SqlStr="";
SqlStr = "SELECT binimage FROM PUB.Images WHERE patid = '" + PatId +
"' AND admdate = '" + AdmDate + "' AND imgseq = '" + ImgSeq.ToString() +
"'";
OdbcConnection ImgConn = new OdbcConnection(Session["ConnStr"].ToString());
OdbcCommand ImgSel =
new OdbcCommand(SqlStr,ImgConn);
ImgConn.Open();
OdbcDataReader ImgRead;
if (ImgConn.State.ToString() != "Open")
return(false);
else
{
try
{
ImgRead = ImgSel.ExecuteReader(CommandBehavior.SequentialAccess);
MyStr = ImgRead.FieldCount.ToString();
//ImgRead.RecordsAffected.ToString();
ImgRead.Read();
/* with .net 1.1, RtnBytes equaled the blob size.
In .net 2.0, it is zero bytes getting returned. */
RtnBytes = ImgRead.GetBytes(0,startIndex,SeqImage,0,SeqSz);
ImgRead.Close();
}
catch (Exception ex)
{
MyStr = ex.Message;
}
}
ImgConn.Close();
return(true);
} Tag: Anyone know of a Silverlight newsgroup? Tag: 144736
Update query that won't take DBNull
I have a stored procedure that I let the wizard create a function in the
QueriesTableAdapter class. Some of the parameters to the stored procedure
clearly allow for nulls (AllowDBNull) but somehow the wizard refused to add
the Global.System.Nullable(Of String) to the generated function's parameter
list. Now I can't use the function if the parameter is DBNull.Value. Can I go
and edit the Designer.vb manually or is there a way to cause the wizard to
recognize that the function should make the parameter Nullable(Of Type)?
--
Michael Tag: Anyone know of a Silverlight newsgroup? Tag: 144735
Restore.Abort (SMO) throws Exception and Cannot Delete Database
Using Restore.SqlRestore (synchronous method call) followed by calling
Restore.Abort causes the following excepion to be thrown:
Restore failed for Server 'localhost\SQLExpress'.
Operation cancelled by user.
Then tried to delete database with:
Server srv = new Server("localhost\SQLExpress")
srv.KillDatabase("MyDatabase")
Another exception was thrown shown below:
Kill database failed for Server 'localhost\SQLExpress'.
ALTER DATABASE is not permitted while a database is in the Restoring state.
ALTER DATABASE statement failed.
However I was able to delete database using SQL Server Manager Studio Express.
In addition when viewing the database in SQL Server Manager Studio Express
the database name looked like this:
"MyDatabase(Restoring...)"
It is as though the restore never completed.
Does anyone know why this would happen and what is the work around to get
the Restore.Abort to work properly?
Thanks. Tag: Anyone know of a Silverlight newsgroup? Tag: 144732
Databinding en un Combobox
Hi, i am using a list named ListaCombo to populate a combo box with a
query, but when i execute the code,
comboBox1.DisplayMember = "Display"; it thows a "The
connection is already Open (state=Open)" error messages.
anyone can give me idea?
thanks
//TextDate.Text = "Hoy";
mySqlConnection =
new
SqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString"));
DateTime Date1 = System.DateTime.Now.AddDays(0);
string Day = System.Convert.ToString(Date1.Month);
string Month = System.Convert.ToString(Date1.Day);
string Year = System.Convert.ToString(Date1.Year);
string cadDate = Day +"/"+ Month +"/"+ Year;
TextDate.Text = cadDate;
// Cargando ComboBox
SqlDataReader myReader = null;
SqlCommand mySqlCommand = new SqlCommand("select zon_cve,
zon_Nombre from Zona", mySqlConnection);
try
{
mySqlConnection.Open();
myReader =
mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
ArrayList ListaCombo = new ArrayList();
while (myReader.Read())
{
//comboBox1.Items.Add(new
MyListItem(myReader["zon_cve"].ToString(),
myReader["zon_Nombre"].ToString()));
ListaCombo.Add(new
MyListItem(myReader["zon_cve"].ToString(),
myReader["zon_Nombre"].ToString()));
}
MessageBox.Show("All names loaded successfully!");
// Here, the code generate a error, already
open! ***
comboBox1.DataSource = ListaCombo;
comboBox1.DisplayMember = "Display";
comboBox1.ValueMember = "Value";
}
catch (Exception ex)
{
MessageBox.Show("Error occurred: " + ex.Message);
}
finally
{
if (myReader != null)
myReader.Close();
}
myReader.Close(); Tag: Anyone know of a Silverlight newsgroup? Tag: 144727
Guarantee read lock in transaction?
I have a SQL table where the PrimaryKey column ID is a long. There is a
ForeignKey relation from column ParentID back to the same table to track
relation. Default for new records is always to relate back to itself.
I don't really care what value is used for ID on new records, but as I need
to have the same value on ParentID as in ID I have so far created the value
manually based on the current used max value of all records.
Problem is I would like to guarantee the same value is not used for two
different inserts.
Is it possible to guarantee read lock in a transaction?
I am using LINQ-to-SQL and planned to use TransactionScope mechanism.
But then it struck me that this solution might not be safe for reads.
I would like to avoid two subsequent inserts to read the same
current-max-used-ID of my table which would mean they calculated the same new
ID for the PrimaryKey.
Is there a better way to accomplish the same thing? Tag: Anyone know of a Silverlight newsgroup? Tag: 144725
Compacting and repairing access 2000 db
Hi
Is it possible to compact and repair an external access 200 database via
code?
Thanks
Regards Tag: Anyone know of a Silverlight newsgroup? Tag: 144716
LINQ with switch statement
Hi,
I'm trying to use a swithc statement in a LINQ query but its not ahppy at all.
Any ideas as to the correct way to go about this?
Many thanks for any ideas
Ant
Below is the statement:
var resultSet = from p in db.Projects
where p.ProjectCode == textboxProjectCode.Text &&
p.RevisionStatusCode == 'A'
select new
{
p.ProjectCode,
p.RevisionNum,
"StatusCode" =
switch (p.StatusCode)
{
case 'I':
"Inactivated";
break;
case 'E':
"Engaged";
break;
case 'C':
"Closed";
break;
case 'P':
"Planned";
break;
default:
"default";
break;
},
p.RevisionName,
p.LastUpdateUserID,
p.LastUpdateDate
}; Tag: Anyone know of a Silverlight newsgroup? Tag: 144714
dbml metadata
Is their a way to get the dbml metadata at runtime.
For example I would like to see the datatype and length for the LastName
field. Tag: Anyone know of a Silverlight newsgroup? Tag: 144707
I am looking for a Silverlight newsgroup, I can't find one on
news.microsoft.com.