Help with sql connection in C#

I have always programmed in VB.net but was given this site that is written in C#. Basically for some reason they can not get the

IDataReader reader

To work on the IIS server they are loading it on to. They are now asking me to fix the website so it will work. I have no clue how to do this in C#.

In VB.net I would do this:

Dim ConnectionString As String = "server=;uid=;pwd=;database="
Dim CommandText As String
Dim myConnection As New SqlConnection(ConnectionString)
Dim Adapter As SQLDataAdapter = New SQLDataAdapter
Dim MyCommandBuilder As SQLCommandBuilder
Dim MatcherDS As DataSet = New DataSet
Dim Row As DataRow
Dim Count

SelectStatement = " select M_ID, M_FirstName, M_LastName, M_Email, M_Login, M_Password, M_Level from Member where M_Login = '" & txtuser.text & "' and M_Password COLLATE Latin1_General_CS_AS = '" & txtpassword.text & "'"
Adapter.SelectCommand = New SQLCommand(SelectStatement, MyConnection)
MyCommandBuilder = New SQLCommandBuilder(Adapter)
Adapter.Fill(MatcherDS, "temp")

count = MatcherDS.Tables("temp").Rows.Count

if count > 0 then
Row = MatcherDS.Tables("temp").Rows(0)
Session("M_FLevel") = Row.item("M_Level")
Session("M_ID") = Row.item("M_ID")
if Session("M_FLevel") = 0 then
Session("M_FLevel") = 5
end if

Response.Redirect("home.aspx")

else

response.write("Error Login")

end if

Now my question is how do I program this in C# so I can open the sql server and access the tables the same way??

Any help is appreciated...

The same the way you do it in vb.net except you use c#. In .NET all thelanguages use the same objects, but may differ in syntax. Look for avb.net to c# convertor on the net.

Rahul|||

Below is the C# equivelent.

string ConnectionString ="server=;uid=;pwd=;database=";
string CommandText;
SqlConnection myConnection =new SqlConnection(ConnectionString);
SqlDataAdapter Adapter =new SqlDataAdapter;
SqlCommandBuilder MyCommandBuilder;
DataSet MatcherDS =new DataSet;
DataRow Row;
int Count;

SelectStatement =" select M_ID, M_FirstName, M_LastName, M_Email, M_Login, M_Password, M_Level from Member where M_Login = '" + txtuser.Text +"' and M_Password COLLATE Latin1_General_CS_AS = '" + txtpassword.Text +"'";
Adapter.SelectCommand =new SqlCommand(SelectStatement, myConnection);
MyCommandBuilder =new SqlCommandBuilder(Adapter);
Adapter.Fill(MatcherDS,"temp");

count = MatcherDS.Tables["temp"].Rows.Count;

if (count > 0)
{
Row = MatcherDS.Tables["temp"].Rows[0];
Session["M_FLevel"] = Row.Item["M_Level"];
Session["M_ID"] = Row.Item["M_ID"];

if ((int)Session["M_FLevel"] == 0)
{
Session["M_FLevel"] = 5;
}

Response.Redirect("home.aspx");
}
else
{
Response.Write("Error Login");
}

HTH

|||Thank you, that worked...now I have something to working to work off of on the rest of this code. Thank you for your help...sql

Help with Remote Connection

I am attempting to connect to SQL 2005 server. The server and the VS 2005 are on the same computer. I have configured the db so it uses the security for asp. The log in section connects fine and checks for the user. I have combined the old db with the new ASP security. If the user is not found then I open a connection using the open command and check for the user in the old part of the db. The is where I have trouble. I get the message below that the server connect but then won't allow remote connections. I have check the setting and the allow remote connections is checked. Thank in advance if anyone can help.

Endeavor

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: 0 - No process is on the other end of the pipe.)

The error came from Named Pipes Provider. Please check the server network protocols to maks sure Named Pipes is enabled. You can check this in 'Configuration Tools'->'SQL Server Configuration Manager'->'SQL Server 2005 Network Configuration'.

You'd better also configure remote connections to use both TCP/IP and Named pipes in 'SQL Server 2005 Surface Area Configuration'.

|||

thanks for your response, I went throught a configured like you said, the named pipes were disabled in the configuration manager but the other were correct in the surface area config. But I am still getting the same error message when it get to the trying to open a connection useing the open command. Have you got any other ideas I can try to get it to work.

Endeaveor

sql

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

GO

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

GO

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

SqlConnection connection2 = new SqlConnection("context connection=true");

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Help with Multiple connections in a CLR stored procedure

Here's what I'm trying to accomplish:

1. Open a connection and retrieve a datareader, using the context connection.
2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection.

The problem I have is that I can't open the second connection. Here's some sample code:

public partial class StoredProcedures {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void up_TestClr() {

//Use the current context connection
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Context Connection=true";

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;

cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT TOP 100 AccountId FROM Account";

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {

SqlConnection conn2 = new SqlConnection();
conn2.ConnectionString = "Server=localhost;Database=TestDb;Integrated Security=SSPI";
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "up_TestProc";
int serviceFilterId = Convert.ToInt32(reader["accountId"]);
SqlParameter parm = new SqlParameter("@.accountId", serviceFilterId);
cmd2.Parameters.Add(parm);
conn2.Open();
cmd2.ExecuteNonQuery();
cmd2.Dispose();
}

reader.Close();
conn.Close();

}

};

When I attempt to execute the procedure, I get the following:

Msg 6549, Level 16, State 1, Procedure up_TestClr, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'up_TestClr':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermission()

at System.Data.SqlClient.SqlConnection.PermissionDemand()

at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at StoredProcedures.up_TestClr(String& dbName)

. User transaction, if any, will be rolled back.
Any suggestions? I've tried unsuccessfully to create an Asymetric key to mark my assembly for External Access:

USE master

GO

CREATE ASYMMETRIC KEY SN FROM EXECUTABLE FILE = 'C:\Shc\SqlServerProject1.dll'

CREATE LOGIN TestLogin FROM ASYMMETRIC KEY SN

GRANT EXTERNAL ACCESS ASSEMBLY TO TestLogin

GO

Resulted in:

Msg 15208, Level 16, State 1, Line 2
The certificate, asymmetric key, or private key file does not exist or has invalid format.
Msg 15151, Level 16, State 1, Line 3
Cannot find the asymmetric key 'SN', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 4
Cannot find the login 'TestLogin', because it does not exist or you do not have permission.
ANY IDEAS/SUGGESTIONS?

<AKS@.discussions.microsoft.com> wrote in message news:68f11211-89fe-4a3f-972c-512cddfc8ec4@.discussions.microsoft.com... 1. Open a connection and retrieve a datareader, using the context connection.2. Iterate through the datareader & call another stored procedure per row in the datareader, using a second connection. Unfortunately, you can only have a single context connection open at once. Have you considered using a DataSet instead of the DataReader? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||Ugh. I didn't realize that I could use a DataSet inside a CLR-based SP. I guess somehow, all the examples I've seen used the SqlDataReader.

That should work fine. Thx.|||

Not sure how using a DataSet helps, but...

He is not opening a 2nd context connection. He is opening a 2nd connection that may happen to point to the same server/database as the context connection.

This may be needed for example to persist auditing information that needs to survive even if the context connection's transaction is later rolled back.

Are you saying this is not allowed?

My code attempts to create a 2nd connection like this, and I'm getting the same error when I attempt to .Open() it later:

<code>

public static SqlConnection NewConnection()

{

SqlConnection connection2 = new SqlConnection("context connection=true");

connection2.Open();

SqlCommand command = new SqlCommand("select @.@.servername, db_name()", connection2);

SqlDataReader sdr = command.ExecuteReader();

sdr.Read();

string serverName = sdr.GetString(0);

string dbName = sdr.GetString(1);

return new SqlConnection("Server=" + serverName + ";Database=" + dbName + ";Trusted_Connection=yes;Enlist=false");

}

</code>

|||I receive the same error when attempting to connect to another db instance. What was the solution for this problem?

Help with java connection to MS SQL 2000 with windows integrated security

Hi , I am trying to connect to MS Sql server 2000 from Java (1.4.2 /
1.5 ). I installed my Sql Server(8.00.382) from the one supplied with
VS.NET 2001. When I installed it on my laptop it did not ask me for a
user name and password. After install when I re-started my machine I
see the server started up with a green light. Now when I connect to the
server from VS.NET it works fine. This is because VS uses windows
integrated security. I now need to connect using Java , so I downloaded
the microsoft drivers for SQL2000-JDBC sp3 from the microsoft site. I
added the jar files to my Java project classpath. I manage to register
the driver in java :

Class dbClass = ClassLoader.getSystemClassLoader().

loadClass("com.microsoft.jdbc.sqlserver.SQLServerDriver");

DriverManager.registerDriver((Driver) dbClass.newInstance() );

Connection conn =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;_
integrated security=SSPI");

but cannot seem to get a connection as it gives an SQLException saying
that it is unable to connect:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error
establishing socket.

I cant seem to figure it out.Can some one help ??

I am a newbie to sqlserver so couldnt quite figure out how to change
admin password or create a new user with the tools provided with this
version of sql (SQL Server Desktop Engine).

Any help will be appreciated.

EbbyHi ... I also tried the following connection string which I made after
looking at other peoples problems.

jdbc:microsoft:sqlserver://EBBY/VSdotNET:1433;DatabaseName=Ebby");

where EBBY is the server name
VSdotNET the instance name
Ebby the database name|||Hi

You may want to check out
http://support.microsoft.com/defaul...kb;en-us;313100

The following would imply that a names instance would require a different
port (or possibly an alias) to connect
http://support.microsoft.com/defaul...kb;en-us;313225

John
<ebrahimbandookwala@.gmail.com> wrote in message
news:1111297471.633617.217830@.g14g2000cwa.googlegr oups.com...
> Hi ... I also tried the following connection string which I made after
> looking at other peoples problems.
> jdbc:microsoft:sqlserver://EBBY/VSdotNET:1433;DatabaseName=Ebby");
> where EBBY is the server name
> VSdotNET the instance name
> Ebby the database name|||(ebrahimbandookwala@.gmail.com) writes:
> Hi , I am trying to connect to MS Sql server 2000 from Java (1.4.2 /
> 1.5 ). I installed my Sql Server(8.00.382) from the one supplied with
> VS.NET 2001.

8.00.382 is SQL 2000 SP1. The current service pack of SQL 2000 is SP3 (and
SP4 is in beta). I strongly recommend you to upgrade to SP3, as SP3
has a fix for the Slammer worm which could attack your SQL Server if
you were to expose it on the Internet.

> Connection conn =
> DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;_
> integrated security=SSPI");

1433 is normally the port for the default instance, and apparently you have
a named instance. Check out the links posted by John, and see if they help.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp