Hi,
I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.
At the final execution i am getting some security exception
The following is the exception I am getting….
CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)
AS
EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService
GO
EXECUTE GetProductVendorDetails 2
Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":
System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)
at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()
at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
System.InvalidOperationException:
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)
at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)
at StoredProcedures.CallWebService(Int32 ProductID)
.
My Web Service Method ….
[WebMethod]
private void GetProductDetails(int ProductID)
{
// String ProductVendorDetail="";
//Set the connection string for the database
string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";
//Create Connection and open it
SqlConnection conn = new SqlConnection(connectionstring);
conn.Open();
//Create the command object
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"
+ " FROM Production.Product P "
+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "
+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "
+ " WHERE P.ProductID =" + ProductID.ToString();
SqlDataReader thisReader = comm.ExecuteReader();
while (thisReader.Read())
{
//Console.WriteLine(myReader["Column1"].ToString());
//Console.WriteLine(myReader["Column2"].ToString());
pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());
pvinfo.ProductName = thisReader["ProductName"].ToString();
pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();
pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();
pvinfo.VendorName = thisReader["VendorName"].ToString(); ;
}
thisReader.Close();
conn.Close();
}
[WebMethod]
public ProductVendorInfo GetProductVendorDetails(int ProductID)
{
GetProductDetails(ProductID);
ProductVendorInfo pvi = new ProductVendorInfo();
pvi.ProductID = pvinfo.ProductID;
pvi.ProductName = pvinfo.ProductName;
pvi.ProductNumber = pvinfo.ProductNumber;
pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;
pvi.VendorName = pvinfo.VendorName;
return pvi;
}
My CLR Procedure code is as follows….
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(int ProductID)
{
// Put your code here
ProductVendorInfoService S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;
ProductVendorInfo pvi = new ProductVendorInfo();
pvi = S.GetProductVendorDetails(ProductID);
String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";
using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}
};
Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?
Thanks
Raj
Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.
Actually, try and sign your sqlclr assmembly anyway.
Niels
|||
It is already have strong key name...
I added "Integrated Security=true" in the connection string then the security exception was solved..
Still i am getting the exception:
Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":
System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.
System.InvalidCastException:
at System.Convert.ToInt32(Object value)
at StoredProcedures.CallWebService(SqlInt32 ProductID)
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here
S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;
ProductVendorInfo pvi = new ProductVendorInfo();
int ProdID = System.Convert.ToInt32(ProductID);
pvi = S.GetProductVendorDetails(ProdID);
String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";
using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}
};
the exeception seems to be related to conversion..... :-(
|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:
int ProdId = ProductId.Value;
All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.
Niels
|||
hi,
The following is the CLR code and i was able to compile and create the sp from the assembly
CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)
AS
EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers
GO
and on execution
EXECUTE GetProductSuppliersDetails 1
i am getting the following exception
Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at StoredProcedures.GetSuppliers(Int32 ProductID)
.
Is there any thing wrong in the code ....
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using TestProject.ProductSupplier;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetSuppliers(int ProductID)
{
// Put your code here
Service S = new Service();
S.UseDefaultCredentials = true;
DataSet ds = new DataSet();
ds = S.GetProductSupplier(ProductID);
DataTable dt = new DataTable();
dt = ds.Tables["ProductSuppliers"];
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
foreach (DataRow row in dt.Rows)
{
string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"
+ " VALUES ('";
int cCount = 0;
foreach (DataColumn col in dt.Columns)
{
if (dt.Columns.Count < cCount)
{
query += row[col.ColumnName].ToString() + ",";
}
else
{
query += row[col.ColumnName].ToString() + "')";
}
cCount++;
}
using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
// Console.WriteLine(row[col]);
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
};
Thanks in Advance...
|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.
I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?
Niels
|||
Issue solved . I got it corrected.
public static void GetSuppliers(int ProductID)
{
// Put your code here
Service objService = new Service();
objService.UseDefaultCredentials = true;
DataSet dsProdSupply = objService.GetProductSupplier(ProductID);
//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
if (dsProdSupply != null)
{
if (dsProdSupply.Tables[0] != null)
{
foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)
{
string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"
+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";
/*
int cCount = 0;
foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)
{
if (dsProdSupply.Tables[0].Columns.Count < cCount)
{
query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";
}
else
{
query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";
}
cCount++;
}* */
using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
// Console.WriteLine(row[col]);
insertCommand.ExecuteNonQuery();
cn.Close();
}
}
}
}
}
Thanks To Karthik Who helped me in correcting the issue...
oapException