Re: Problem with Stored Procedure - C# Discussion Boards

Problem with Stored Procedure

22-Jun-04 3:14

That'd be ADO.NET itself. It already is an abstract API. In .NET 2.0, a provider pattern will make it more abstract, but for now you can simply use the System.Data.OleDb namespace. MySQL, Oracle, SQL Server, and many others (including Excel, Fox Pro, ...) all have OLE DB drivers, so all that would change is your connection string (although some providers don't provide all the same features, so make sure you do some exception handling).

So, just for a crude example:

public enum DbProvider
{
  MySQL,
  Oracle,
  MSSQL
}
// ...
public OleDbConnection GetConnection(DbProvider provider)
{
  OleDbConnection conn = null;
  switch (provider)
  {
    case DbProvider.MySQL:
      conn = new OleDbConnection("Provider=MySQLProv;Data Source=mydb;" +
        "User ID=me;Password=12345;");
      break;
    case DbProvider.Oracle:
      conn = new OleDbConnection("Provider=msdaora;Data Source=oradb;" + 
        "User ID=sa;Password=;");
      break;
    case DbProvider.MSSQL:
      conn = new OleDbConnection("Provider=SQLOLEDb;Data Source=DBSERV1;" +
        "Initial Catalog=northwind;Integrated Security=SSPI;");
      break;
  }
  return conn;
}

This isn't the best way to do it, though. By using an enum, you basically hard-code the choices (unless you want to). A better way might be to put the connection string in the .config file so you can change it to use a different provider at any time (though changes to the .config file in all but ASP.NET requires that you restart the application; even the ASP.NET web application must be restarted, though this happens automatically by a worker process).

You can also use the typed classes (like System.Data.SqlClient for SQL Server, System.Data.OracleClient for Oracle, etc.) and refer to them by their abstract base classes, or the interfaces (like IDbConnection and IDbCommand). You can find more providers online by doing a google search.

Microsoft MVP, Visual C#
My Articles

ronin177021-Jun-04 15:30

ronin1770

21-Jun-04 15:30

Hi,

I am using a C# code to insert a data in SQL Server.

Stored Procedure which inserts data is created as :

-----------------------------------------

CREATE PROCEDURE spAddNewAddress
@AddressID char, @AddressType char, @HomeAddress varchar,
@City varchar, @State varchar, @Zip varchar, @Country varchar
AS
INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress,
@City, @State, @Zip, @Country)
GO
-----------------------------------------

Then C# code which inserts data is :

public void insertNewData(Address a)
{
try
{
//create sql string
sqlAddress = "spAddNewAddress";

//Open the connection
scAddress.Open();

//create the cmdAddress
cmdAddress = new SqlCommand(sqlAddress, scAddress);
cmdAddress.CommandType = CommandType.StoredProcedure;

//adding parameters for command
cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10);
cmdAddress.Parameters["@AddressID"].Value = a.getAddressID();

cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1);
cmdAddress.Parameters["@AddressType"].Value=a.getAddressType();

cmdAddress.Parameters.Add("@HomeAddress",SqlDbType.VarChar,100);
cmdAddress.Parameters["@HomeAddress"].Value=a.getHomeAddress();

cmdAddress.Parameters.Add("@City",SqlDbType.VarChar,25);
cmdAddress.Parameters["@City"].Value=a.getCity();

cmdAddress.Parameters.Add("@State",SqlDbType.VarChar,25);
cmdAddress.Parameters["@State"].Value=a.getState();

cmdAddress.Parameters.Add("@Zip",SqlDbType.VarChar,25);
cmdAddress.Parameters["@Zip"].Value=a.getZip();

cmdAddress.Parameters.Add("@Country",SqlDbType.VarChar,70);
cmdAddress.Parameters["@Country"].Value=a.getCountry();

cmdAddress.ExecuteNonQuery();

//clean up code
cmdAddress.Dispose();
scAddress.Close();
}
catch(Exception ex) { Console.WriteLine(ex.ToString() ); }

-----------------------------------------

Code executes with out any trouble (no breaks/errors or exceptions).

Only problem is when i try to view this data in SQL Server's Enterprise Manager, only first letter of the inserted data appears.

e.g : If inserted AddressCode = A03C0001 then in the EnterPrise Managers it appears as :
"A".

Another strange thing, if try to query database with AddressCode="A", it generates error (that data don't exists).

Although If you try to query database with AddressCode="A03C0001", then it extracts complete information as inserted.

any suggestions!!!!!

My articles and software tools

Xiangyang Liu 刘向阳21-Jun-04 15:52

Xiangyang Liu 刘向阳

21-Jun-04 15:52

ronin1770 wrote:
cmdAddress.Parameters.Add("@AddressID",SqlDbType.Char,10);
cmdAddress.Parameters["@AddressID"].Value = a.getAddressID();

cmdAddress.Parameters.Add("@AddressType",SqlDbType.Char,1);
cmdAddress.Parameters["@AddressType"].Value=a.getAddressType();

I didn't see AddressCode from your code, is it AddressID? You may have mixed up the order of the columns AddressID and AddressType. To prevent this, your insert statement should look like the following:

insert into Address(FieldName1,FieldName2,...) values(FileValue1,FieldValue2,...)

ronin177021-Jun-04 17:23

ronin1770

21-Jun-04 17:23

HI,

i tried the way u suggested... it was never the order of fields

but no success so far...

it is beyond my understanding... probably problem with SQL Server Instance

Christian Graus21-Jun-04 18:27

Christian Graus

21-Jun-04 18:27

If the database returns the data you asked it to insert, then it is there, the problem can only be in how you are viewing the data within SQL server. If you do a select of everything in the table, what does that look like ?

Christian

I have drunk the cool-aid and found it wan and bitter. - Chris Maunder

Iknow22-Jun-04 2:02

Iknow

22-Jun-04 2:02

Since You Have Given DataType As Char For AddressCode and AddressType, It will store Only one char. Better user Varchar(size) in the creation of Store proc, It will Execute fine and u can see what u have inserted.

Wender Oliveira22-Jun-04 2:17

Wender Oliveira

22-Jun-04 2:17

The problem isn't your code but your procedure. When you declared "varName varchar" SQL see it like one char variable. If you want to use more than one char you must to declare "varName varchar(10)" for a 10 chars variable.

Cya

Wender Oliveira
.NET Programmer

ronin177022-Jun-04 3:01

ronin1770

22-Jun-04 3:01

THANX it worked

Heath Stewart22-Jun-04 3:03

22-Jun-04 3:03

The last two replies are correct, in that you're not declaring your field as a string, but are instead declaring it as a char.

I wanted to add that the above code can be made much more efficient by not using the indexer to access your param. The SqlParameterCollection.Add overload you're using returns a SqlParameter, so you can either type .Value = value; immediately after your closing parenthesis, or store the return value from Add to a SqlParameter variable and assign the value from there. The latter way works best when executing a batch of statements (add the parameters to the command and loop to add each iteration's values without re-adding the params again; execute the command with each iteration).

This will result in better performance, since a string lookup on a collection can be very slow (mO(n) processing time, where n is the number of items in a collection and m is the number of iterations; compare that to O(m)).

Microsoft MVP, Visual C#
My Articles

marshalling a pointer member in struct from c#

Anonymous22-Jun-04 3:29

Anonymous

22-Jun-04 3:29

CREATE PROCEDURE spAddNewAddress
@AddressID char, @AddressType char, @HomeAddress varchar,
@City varchar, @State varchar, @Zip varchar, @Country varchar
AS
INSERT INTO Address VALUES(@AddressID, @AddressType, @HomeAddress,
@City, @State, @Zip, @Country)
GO

In the above parameters, a size needs to be added:

@AddressID char(nn), @AddressType char(nn), @HomeAddress varchar(nn),
@City varchar(nn), @State varchar(2), @Zip varchar(9), @Country varchar(nn)

where the "nn" is the size of the field.

rana7421-Jun-04 14:16

21-Jun-04 14:16

I have a struct explicitly defined in C# as

[
Guid("FE9814A3-D198-4bd0-8475-D055DAA7C659"),
StructLayout(LayoutKind.Sequential)
]
public struct _alloc
{
public _alloc(short a,short b)
{
sx= a;sy=b;
}
public short sx;
public short sy;

}

The following struct contains a pointer member to _alloc in COM IDL

typedef
[
uuid(649B2720-373B-46a4-BC1F-6B53CC38755D),
version(1.0),
helpstring("A Demo struct typedef for Marshal")
]
struct _groupalloc {

_alloc* lx;
short ly;

} _GroupAlloc;

I tried to define it as follows in C#
[
Guid("649B2720-373B-46a4-BC1F-6B53CC38755D"),
StructLayout(LayoutKind.Sequential)
]
public struct _groupalloc
{
public _alloc[] sx;
public short sy;

}

This is to be used in a method definition as follows,to marshal it to COM
int GetComplexStruct(ref _groupalloc _pc);

I get the runtime error - 'cannot marshal field sx of _groupalloc - it cannot be marshalled as member of struct'.
What is the principle behind this?Why cant an array be marshalled as member of a struct - if someone could give pointers to related articles too,apart from the answer to the above;that would be appreciated.

thanks and regards.

Heath Stewart22-Jun-04 2:56

22-Jun-04 2:56

You could declare the member as an IntPtr, then use GCHandle and the Marshal class (specifically it's PtrToStructure method).

If you want to marshal an array, you must also use the MarshalAsAttribute to provide additional information, like the size or size param. When marshaling from unmanaged to managed code, the size of an array is known so this information is needed for the marshaler to construct the managed array.

For more information on marshaling, read Interop Marshaling[^] in the .NET Framework SDK.

If you want more information, read about the classes and methods I mentioned above and continue this thread if you have additional questions.

Microsoft MVP, Visual C#
My Articles

rana7422-Jun-04 14:23

22-Jun-04 14:23

Thanks for the response.
I thought that when i am marshalling an array allocated from managed code - i need not give the siz parameter ,as the CLR knows the bounds anyway.
Also in the example i had given ,if I just marshal a _alloc* to COM as in
int GetStructandModify(ref Location _ps);
It just works fine .
However,when it is a member of a struct - it is not marshalled.
Is it something to do with value and reference type?-it would be very helpful if you could clarify this part.

Also ,I tried to marshal it as IntPtr using GCHandle.Alloc and Marshal.AllocCoTaskMem - it works successfully in either case.Also I think GCHandle uses managed memory while AllocCoTaskMem would use the native heap - but I am not sure how to decide which one to be used.

Is there some advice available on parameters to decide whether to use managed or unmanaged memory.
thanks.

Heath Stewart22-Jun-04 15:14

22-Jun-04 15:14

From managed to unmanaged code, the size param is not needed; but, as I said, when going from unmanaged to managed it is because an array is nothing more than an allocated block of memory with no type-safety.

Use GCHandle (pinning the object when it's a reference object) instead of Marshal.AllocCoTaskMem. Use that mainly when COM request the use of CoTaskMemAlloc. A GCHandle pins the object in memory so the GC doesn't move it around, which is what you want here. All you have to do is free the GCHandle, instead of calling Marshal.FreeCoTaskMem. This unpins the object and allows the GC to move it around again.

Microsoft MVP, Visual C#
My Articles

rana7422-Jun-04 16:28

22-Jun-04 16:28

Thanks for your advice.
So when a structure pointer is a member of another structure,can i also marshal the pointer member using marshalas too ,instead of using instptr.If so - can you give an example ,as all my attempts have failed.
Also I am yet not clear when I should prefer allocating an object into unmanaged heap(using marshal AllocCoTaskMem) ,rather than pinning it into managed heap and passing the address to unmanged code.

Sorry for the trouble.

Heath Stewart22-Jun-04 16:46

22-Jun-04 16:46

It's already allocated - why allocate it again? For a reference object, all you need to do is pin it in memory (a value type doesn't need (and can't be) pinned since it will not be moved by the GC, although it could be reclaimed so still use a GCHandle or HandleRef to reference it).

You could try:

// Excuse the names - I don't remember off-hand what you used
[StructLayout(LayoutKind.Sequential)]
internal struct S1
{
  public int x;
  public int y;
}
[StructLayout(LayoutKind.Sequential)]
internal struct S2
{
  [MarshalAs(UnmanagedType.LPArray, SizeParamIndex=1)]public S1[] arr;
  public int size;
}

This will help the marshaler create the array when marshaling from unmanaged code.

Otherwise, you can use the GC handle to pin the S1[] array in memory till after the P/Invoke method returns:

S1[] arr = new S1[];
// Fill S1
GCHandle handle = GCHandle.Alloc(arr, GCHandleType.Pinned);
S2 s2 = new S2();
s2.arr = (IntPtr)handle; // S2.arr is defined as IntPtr
s2.size = arr.Length; // Probably not required here, but oh well
CallMyPInvokeMethod(ref s2);
// Get new data (if any)
handle.Free();

It's crude, but should hopefully give you a good idea.

Microsoft MVP, Visual C#
My Articles

rana7422-Jun-04 21:03

22-Jun-04 21:03

If i try to marshal the pointer element using
[MarshalAs(UnmanagedType.LPArray, SizeParamIndex=1)]public S1[] arr;
as you suggested - I am back to the
"This type cannot be marshalled as a structure field" error,which formed my original question.

Heath Stewart22-Jun-04 21:06

Active Directory & Multiple Connections

22-Jun-04 21:06

Which is why I gave you the alternative to try below that.

Microsoft MVP, Visual C#
My Articles

inyoursadachine21-Jun-04 14:02

inyoursadachine

21-Jun-04 14:02

I have a C# program the asks the user for a UserID, Password, and Domain and does a WinNT Active Directory query to return a list of groups the specified user belongs to. I start the program and enter user1/password 1 on domain 1 and it works just fine. But when I (in the same invocation) try to enter user2/password2 on domain 1 I get the following COM exception:

"Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed....."

I've tried closing and disposing the DirectoryEntry object after each use but to no avail. What do I have to do (close) to allow my program to do 2+ separate Active Directory queries for 2+ userid/password combos in the same invocation??

TIA,
Matt

Re: Active Directory & Multiple Connections

Heath Stewart22-Jun-04 2:51