SQL Server C# Howwwwwww

WarAngelos
WarAngelos used Ask the Experts™
on
Okay Am totally Confused
I am using C#
Right I have SQL SERVER 2008 ENTERPRISE EDITION
-----I created a database in it called it TestDB
-----I made a Table called it TestTable
-----The columns I have are ID, NAME, PASS, SERVER, DATABASE.
Right what I want to do is say I have a TextBox and a Button right
and
When I type say SECRET1 in to the TEXTBOX and Press Enter I want it to go through the Database and find where SECRET1 is in PASS Column... and if it has it I want to the whole details of that Row...
So I want its ID, NAME, PASS, SERVER, DATABASE

How do I do this? I mean I know I could say

"SELECT * FROM TestTable WHERE PASS='"+ passwordTb.Text +"'"

But then If it cant find that Pass in there I want it to say No Such Password... T_T

soo I want it like a for Loop so I want it so I can do this query
"SELECT * FROM GATEWAY"
 FOR (next row still exist)
{
if Gateway.PASSWORD == passwordTb.Text
 do this this this this...
}

"Sorry I wrote it in simple understandable way.. didn't use proper coding stuff if you get what i mean..."

I know how to do this for Java/MySQL... where it would go through each available one... but did that using xml ofcourse.. LOL
But absolutely NO CLUE about SQL SERVER and C# am using this combination for the first time... please let me knwo if its possible a sample or example would be brilliant to.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try this:

you should neaten it up and create a class object to represent your sql table,

But this code should get you on your way....
private void button1_Click(object sender, EventArgs e)
        {
            DataRow data = DataByPass(passwordTb.Text);

            if (data == null)
            {
                MessageBox.Show("Password Does Not Exist");
                return;
            }

            int ID = Convert.ToInt32(data["ID"].ToString());
            string NAME = data["NAME"].ToString();
            string PASS = data["PASS"].ToString();
            string SERVER = data["SERVER"].ToString();
            string DATABASE = data["DATABASE"].ToString();
        }

        public static DataRow DataByPass(string pass)
        {
            var conStr = "My connection string";

            using (var connection = new System.Data.SqlClient.SqlConnection(conStr))
            {
                connection.Open();

                var cmd = new System.Data.SqlClient.SqlCommand("up_GetDataByPass", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Password", pass);

                var dt = GetDataTable(cmd);
                if (dt == null || dt.Rows.Count == 0)
                {
                    //password does not exist
                    return null;
                }

                return dt.Rows[0];
            }
        }

        public static DataTable GetDataTable(SqlCommand sql_command)
        {
            DataSet ds = null;
            SqlDataAdapter adapter = null;

            try
            {
                if (sql_command.Connection == null)
                    sql_command.Connection = DbConnection.SqlConnection;

                ds = new DataSet("Temp");
                adapter = new SqlDataAdapter(sql_command);

                var rows = adapter.Fill(ds);
                if (rows > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            finally
            {
                if (adapter != null) adapter.Dispose();
                if (ds != null) ds.Dispose();
            }
        }

       

Open in new window

and run this on the database
create procedure up_GetDataByPass
	@Password nvarchar(100) -- OR WHAT EVER THE PASSWORD DATA TYPE IS
AS

BEGIN
	select * from TestTable where Pass = @Password
END

Open in new window

G GodwinDatabase Administrator

Commented:
Are you trying to validate users?
When it comes to names and passwords, I prefer a different approach.  If you do as you are wanting, you must give select permissions to the whole table what stores names and passwords ("TestTable" in your case).
I prefer to never grant those rights, and rather to simply submit the name and password supplied by the user to a stored procedure, which will return positive or negative results if the combination of name and password are valid.  This would make a better line of defense if you should be vulnerable to other attacks that could run select statements against your database.  
A positive result could contain the results you want, and a negative result could be represented by "No Such Password..."
-G
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Basically what My thing is that I have 3 SQL Databases... and Depending on What Password the User Choose I want to retrieve the details for that Database.. I hope you get what I mean..

also the top answer... I dun see how that works... mm cant even test it coz it has a error. says Error      1      The name 'DbConnection' does not exist in the current context      C:\Users\Rudu\Documents\Visual Studio 2008\Projects\TestHomes\TestHomes\Secure.cs      75      46      TestHomes

So again mmm.. sorry I didn't mention this before I THINK... anyways I want to be able to basically
Start of with the Password Textbox and a Submit Button, which will then check if that password actually exists... if it does then I want it to retrieve the details of that the database to that using that password.... I mean in Java/MySQL/XML all I had to do was use Enumeration... and go through each row of data and if it matches I could get the details by e.g. says TempPoi.ID... TempPoi.Name etc.. I dunno how to do that stuff on C#/SQL Server

The top one looks sorta promising but cant really test what it does coz of that error... anyways can some one tell me how to get retrieve the details from a SQL Server

Hmm I think am confusing you guyz and myself LOL anyways I hope you guyz get what I want thank you.

Author

Commented:
Hmmm...
Note: I have given random names to database(Not ACTUAL Names)


SQL Database 1(Secure)... contains the details ID NAME PASS SERVER DATABASE.
so i have in this things like
1, 'HQ', 'test', 'Rudu-Laptop\rudu', 'HQ'
2, 'branch1', 'test1', 'Rudu-Laptop\rudu', 'SQLDB2'
3, 'branch2', 'test2', 'Rudu-Laptop\rudu', 'SQLDB3'

SQL Database 2(SQLDB2)... contains information of employees of a branch 1...

SQL Database 3(SQLDB3)... contains information of employees of a branch 2...


So What I want is the password basically connect to database Secure... check what password is entered.. if test is entered I want it to be able to open a pre-made form called choose.. in whch the user is able to choose which branch he wants to look at...

if password entered is test1... i want it to be able to get the id name server and database details for that password from the database.. and put it in global variables..

thats pretty much What I want it to do hmmm yeah I don't think I have forgotten to say anything this time I Hope ^^

Author

Commented:
if your thinking why Rudu-Laptop/Rudu???? then its because I was using this to connect to the database

                SqlConnection myConnection = new SqlConnection("Data Source=Rudu-Laptop\rudu;Initial Catalog=Secure;Integrated Security=True;");

am guessing I cant use that anymore but yeah once I know how to actually connect and retrieve then I can redesign the database Secure to suit what I want but for now am just testing...

So mm there I hope someone can help thank you.
The problem is the escape char in the string

try using:
SqlConnection myConnection = new SqlConnection(@"Data Source=Rudu-Laptop\rudu;Initial Catalog=Secure;Integrated Security=True;");

or 

SqlConnection myConnection = new SqlConnection("Data Source=Rudu-Laptop\\rudu;Initial Catalog=Secure;Integrated Security=True;");

Open in new window

...

Laptop\rudu

actually means: Laptop{\r - RETURN CHAR}udu

Author

Commented:
right mm... I stilll have no Idea about this part... can you help?

Note: I have given random names to database(Not ACTUAL Names)

Hmmm...
SQL Database 1(Secure)... contains the details ID NAME PASS SERVER DATABASE.
so i have in this things like
1, 'HQ', 'test', 'Rudu-Laptop\rudu', 'HQ'
2, 'branch1', 'test1', 'Rudu-Laptop\rudu', 'SQLDB2'
3, 'branch2', 'test2', 'Rudu-Laptop\rudu', 'SQLDB3'

SQL Database 2(SQLDB2)... contains information of employees of a branch 1...

SQL Database 3(SQLDB3)... contains information of employees of a branch 2...


So What I want is the password basically connect to database Secure... check what password is entered.. if test is entered I want it to be able to open a pre-made form called choose.. in whch the user is able to choose which branch he wants to look at...

if password entered is test1... i want it to be able to get the id name server and database details for that password from the database.. and put it in global variables..

thats pretty much What I want it to do hmmm yeah I don't think I have forgotten to say anything this time I Hope ^^
Top Expert 2013

Commented:
is this a web app or windows Form?

Author

Commented:
Windows Form

Author

Commented:
Welll... even though I didn't really get what I wanted.. atleast you tried to help so thank you very much really appreciate it thank youl.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial