I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
created a Database project where I've written some simple CLR Stored
Procedures. I can deploy and call the simple CLR Stored Procedures from my
host WinForm application. This all works great.
I'd now like to write a CLR Stored Procedure with a parameter of type
System.Data.DataRow. Doing this compiles just fine, but when I attempt to
deploy, I get the following error:
Cannot find data type DataRow.
Any suggestions on what I might do to get a CLR Stored Procedure with a
DataRow parameter to compile AND deploy?
Thanks,
--
Randyexamnotes <randy1200@.newsgroups.nospam> wrote in
news:4A6B0CA3-733E-4E43-AA94-CD9D98B295C2@.microsoft.com:
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
>
Can not be done. Your CLR procedures can only have params of types that are
T-SQL compatible.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||randy1200 (randy1200@.newsgroups.nospam) writes:
> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
You can't do that. A CLR stored procedure can only take parameters
that maps to types used in SQL Server, and DataRow is not such a type.
Keep in mind that even if the stored procedure is implemented in the CLR,
there is still is a CREATE PROCEDURE statement which looks just like
the CREATE PROCEDURE statement for a T-SQL procedure as far as the
parameter list is concerned.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could serialize the DataRow and use a VarChar or VarBinary parameter.
Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
"Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML Dat
a
Serialization." You could use an XML representation, however there is a
particularly interesting example of serializing/deserializing a DataTable an
d
the DataRows it contains using an efficient custom binary representation on
pages 424-428. The sample code is in C# and there isn't much code needed.
Hope this helps.
Jack Whitney
"randy1200" wrote:
> I'm using Visual Studio 2005, C#, and SQL 2005. In Visual Studio, I've
> created a Database project where I've written some simple CLR Stored
> Procedures. I can deploy and call the simple CLR Stored Procedures from my
> host WinForm application. This all works great.
> I'd now like to write a CLR Stored Procedure with a parameter of type
> System.Data.DataRow. Doing this compiles just fine, but when I attempt to
> deploy, I get the following error:
> Cannot find data type DataRow.
> Any suggestions on what I might do to get a CLR Stored Procedure with a
> DataRow parameter to compile AND deploy?
> Thanks,
> --
> Randy|||I have been playing around with Dino Esposito's example of custom binary
serialization of a DataTable and the DataRows it contains. (This is the
example that I mentioned in my earlier posting on this thread.) It is very
nicely implemented. The code to serialize a DataTable to a file and
deserialize the file back to a DataTable, including a worker class definitio
n
and comments is 78 lines of code. Dino provides a sample Windows applicatio
n
that connects to a Northwind database. You enter SQL in a textbox like
"SELECT * FROM [Order Details]" and the application gets the data from the
database into a DataTable, serializes the DataTable to a file, deserializes
the file back to a DataTable, and renders the DataTable in a DataGrid. He
actually serializes the DataTable using two methods and compares the output
for size efficiency. The two methods are ordinary .NET framework binary
serialization and his custom binary serialization. The custom binary
serialization produces much smaller output. The code was written for .NET
1.1, but I just converted it to .NET 2.0 and accessed SQL Server 2005 with n
o
hitches. If you are interested in pursuing this, you should definitely take
a look at this sample.
Note that to serialize just a DataRow, you would need to serialize some
properties of the containing DataTable object, minimally the column names an
d
types, in addition to the values in the DataRow. Dino's example demonstrate
s
this.
Hope this helps.
Jack Whitney
"Jack Whitney" wrote:
> You could serialize the DataRow and use a VarChar or VarBinary parameter.
> Dino Esposito covers serializing/deserializing ADO.NET objects in depth in
> "Applied XML Programming for Microsoft .NET" See chapter 9 "ADO.NET XML D
ata
> Serialization." You could use an XML representation, however there is a
> particularly interesting example of serializing/deserializing a DataTable
and
> the DataRows it contains using an efficient custom binary representation o
n
> pages 424-428. The sample code is in C# and there isn't much code needed.
> --
> Hope this helps.
> Jack Whitney
>
> "randy1200" wrote:
>
