Read xml from sql server column(xml data type) and return as XmlDocument in C#

Hi Everyone:

I would appreciate it if someone can help me this problem as I am a c# beginner. I am writing a C# method that is supposed to read in XML from SQL Server 2005 Database. The table I am reading from has a column which is set to XML data type and has a well formed XML document. I would like to load that xml into a xml document in my c# and return back as a xmldocument in my method. Here is my method so far, as you can see i am utilizing the Enterprise Library Data Access Block for db access. I woud appreciate if you can provide me with some help. Thanks.

Note: What i am doing right now maybe completely off track from what I am trying to achieve. here is the code I have so far:

private const string SQLGETCNFS = "SELECT EntityDefinitionXML FROM EntityDefinition WHERE EntityID = ";

/// <summary>
/// Method retrieves XML Entity Definition from the configuration database
/// </summary>
/// <param name="entityID"></param>
/// <returns>XmlDocument Type</returns>
public XmlDocument GetCustomerCNSFData(string entityID)
{
//connect to config database
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = SQLGETCNFS + entityID.ToString();

IDataReader reader = db.ExecuteReader(CommandType.Text, sqlCommand);

while (reader.Read())
{
SqlXml sx = reader.GetSqlXml(1);
XmlReader xr = sx.CreateReader();
xr.Read();
}
return null;
}

You can create a new instance of XmlDocument and then call the Load method and pass the XmlReader that you created from SqlXml.

Regards,

Galex Yen

RDA.Pull and INNER JOINS...

When calling the method below, if I use the SQL in red, the Pull method populates my data correctly. But, If I use the sql in Green, I get an errror message. I have set breakpoints and un the SQL that is returned from the sql statement is green, and it returns records. What gives? Are Joins not allowed?

public void GetDiagnosisList()
{
// Connection string to SQL Server 2000
string rdaOleDbConnectString = "Provider=sqloledb; Data Source=WORKPC;Initial" +
" Catalog=database;User Id=username;Password=password";
// string SQLGet = "SELECT tblLUDiagnosis.strDiagnosisCode, tblLUDiagnosis.strDiagnosisDesc FROM tblLUDiagnosis" +
// " INNER JOIN tblPatientEpisode ON tblLUDiagnosis.strDiagnosisCode = tblPatientEpisode.strPrimaryDiagnosis" +
// " WHERE tblPatientEpisode.intHospitalID =" + intHospitalID;
string SQLGet = "SELECT TOP 15 tblLUDiagnosis.strDiagnosisCode, tblLUDiagnosis.strDiagnosisDesc FROM tblLUDiagnosis";
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
try
{
rda.InternetUrl = "http://WORKPC/SQLCE/sscesa20.dll";
rda.LocalConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;" + CONN_STRING;
rda.Pull("tblLUDiagnosis",SQLGet,rdaOleDbConnectString, RdaTrackOption.TrackingOnWithIndexes, "ErrorTable");
MessageBox.Show("Diagnosis Synchronized");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

nevermind, got it...it was the

TrackingOnWithIndexes...can't use that and join statements...Thanks

RDA tracking - no primary key on table

Hello,

Can a table be tracked if it does not have a primary key? I am using rda.Pull method in VS2005 using C#. If not, what are my options? Any help is appreciated.

Thanks.

A primary is required if using RDA tracking. See http://msdn2.microsoft.com/en-us/library/ms172971.aspx

"A primary key must be defined on the updatable recordset returned by the SELECT statement. "

RDA Pull method not creating tables/inserting data

I can't see what is going on, this is the situation:

I call the Pull method, specify the table to be affected, the query to be used, the connection string to the remote SQL server, the tracking options (On) and the Error table. The pull method executes with no errors however, no table is ever created. I don't know why, here's what I have done so far:

I read the SQL BOOKS ONLINE help on preparing RDA, I set up the IIS virtual directory for anonymous access and on the connection string I send in the user name and password for the SQL server, I went into the SQL Server and grated access to the user name to the database that I am going to access and I made the user a db_owner.

So, according to SQL BOOKS ONLINE I have everything right however, it won't populate, so right now I am open to suggestions on how to get this to work, heres the code:
---------
string rdaOleDbConnectString = "Provider=SQLOLEDB;Data Source=<Server>;Initial Catalog=<DB>; User Id=<User>;Password=<Password>"; (it's not exactly like this, but in it has the proper values)
string connectionString = "Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess("http://10.1.1.206/mobile/sqlcesa30.dll",
connectionString);

IList _tableNames = new ArrayList();
IList _queries = new ArrayList();

############
Code that prepares tables and queries
############

for (int counter = 0; counter < _tableNames.Count; counter++)
{
rda.Pull(_tableNames[counter].ToString(), _queries[counter].ToString(), rdaOleDbConnectString, RdaTrackOption.TrackingOn, "MobileError");
}

the For loop runs with no problems but no data is ever put (or tables created) into the Mobile DB.

A few things here -

1. from pocket internet explorer on your device or emulator, do you get a correct diagnostic message when you use the url http://10.1.1.206/mobile/sqlcesa30.dll ? if not, then your first issue is you aren't successfully getting from device to the server.

2. your connection string is screwed up. try this instead: connectionString = @."Data Source = \Program Files\client\db\MobileDB.sdf"

3. you do realize that the database must exist already before the pull? and that none of those tables can exist when you call pull? (you need to drop all the tables you want to pull from the server before you call rda.Pull()

4. the values you are using for <user> and <password> must represent a valid SQL Server login as well as have permissions on the specific database you are pulling from <DB>

5. what are you using for primary keys on the tables you are pulling? IDENTITY columns are going to get you into trouble with multiple users pulling with tracking turned on. better to switch to uniqueidentifiers (GUIDs).

Try that much and let me know.

Darren

|||

To answer your questions:

1. I get "SQL Server Mobile Server Agent 3.0" when I try to browse to the URL

2. I will try it without the quotes.

3. Yes, I know the tables must not exists prior to PULL

4. I had made the SQL user a db_owner

5. GUIDS

|||

It did not work, I changed the connection string from:

"Data Source=\"\\Program Files\\client\\db\\MobileDB.sdf\"";

to

"Data Source=\\Program Files\\client\\db\\MobileDB.sdf";

and it still didn't work

|||

if I understand correctly, you are not getting an exception, you're just not getting the tables created in the SQL mobile database after the pull? Are you just using a SELECT * FROM statement to pull each table? Maybe try reducing this to pulling a single table in case there is some issue with your logic to iterate through a collection of tables pulling each one. Also - not knowing the schema of the server side db, are there any constraints on those tables?

-Darren

|||

I just remembered something that I bet is your issue - when you install SQL Server, you have to specify an authentication mode for the server. By default, it is Windows Authentication. In your RDA connection string, you are using SQL Server authentication. As a result, you need to make sure your instance of SQL Server is set to "Mixed Mode (Windows Authentication and SQL Server Authentication).

Hope that helps.

Darren

|||Thanx, I'll try that|||we decided to give merge replication a shot, we may comeback to trying RDA later.

RDA problem

Im having problems with my Pocket PC application deployed in Pocket Pc 2003 se Emulator... Im using the Pull method of RDA. I get the error "A request to send data to the computer running IIS has failed.". I have setup my desktop IIS and I can access the dll agent through Internet explorer. But when I try to access it on the Internet Explorer of the Emulator using the ip of my desktop instead of localhost, I get the error "Unable to connect: You have no modem entries created, and no network card present".
I check the Emulator Connection Settings, and it is set to "My Work Network". I tried using ActiveSync 4.2 then using Device Emulator Manager to put the emulator in Cradle. But still I cant access the remote Internet URL (http://ipaddress/vdirectory/sqlcesa30.dll) on the emulator ...

I guess its the reason I cant use the Pull method... Can anyone help me with this? Its almost 2 days and yet I can't solve this.. Im just starting with C# and pocket PC.. Thanks..

Have you tried to "cradle" your emulator ? (from the Device Emulator Manager, right click and select Cradle). That will enable a network connection to your desktop PC.

|||Yes, I have done that. Fixed this one already, I just configured Activesync to accept DMA... Before it was not enabled so whenever I cradle the emulator, nothing happens, ActiveSync wont detect it...

Thanks

RDA Error

Hi all,

I am developing a Pocket PC application for synchronization SQL server 2005 and SQL CE whenever i am call a RDA's method like Pull() on my application all parameter is correct but this method give a error: "Header information is either corrupted or missing. [,,,Header name,,]", so Please help me how to solves this problem.

Hi Pavan,

Move to Sql Server Compact Edition forum, where would increases the chances for getting your question answered.

And, in most cases, this is the network configuration issue or parameters not set correct. So, could you access the server agent in IIS from your Pocket IE? And, if possible, could you please list out some code snippet, where throws the exception?

Thanks,

Zero Dai - MSFT

|||

Hi Zero Dai,

thanks for reply.

RDA access to multiple SQL Server instances

Hello,

There are several instances in SQL Server. I use RDA method to access SQL Server from mobile devices. But RDA method only gets the server IP,not the instance name. So, how can I define RDA to access to the instance that I need?

Can you provide some details on how exactly are you using RDA?

Are you going through an OLEDB provider? If so what is the connection string?

|||Yes it is possible. datasource=10.0.0.1\instance and virtual directory ip=10.0.0.1. by this way,it is possible..

RDA access to multiple SQL Server instances

Hello,

There are several instances in SQL Server. I use RDA method to access SQL Server from mobile devices. But RDA method only gets the server IP,not the instance name. So, how can I define RDA to access to the instance that I need?

Can you provide some details on how exactly are you using RDA?

Are you going through an OLEDB provider? If so what is the connection string?

|||Yes it is possible. datasource=10.0.0.1\instance and virtual directory ip=10.0.0.1. by this way,it is possible..

RDA access to multiple SQL Server instances

Hello,

There are several instances in SQL Server. I use RDA method to access SQL Server from mobile devices. But RDA method only gets the server IP,not the instance name. So, how can I define RDA to access to the instance that I need?

Can you provide some details on how exactly are you using RDA?

Are you going through an OLEDB provider? If so what is the connection string?

|||Yes it is possible. datasource=10.0.0.1\instance and virtual directory ip=10.0.0.1. by this way,it is possible..