Variant in VB to C#

[klaydze]

Hi, I'm just starting to convert my vb6.0 program to c#.net and I have a function in vb6.0 below: Anyone can help me to translate this in c#.net.

Public Function GetData(SQL As String) As Variant
Dim RST As ADODB.Recordset
Set RST = Conn.Execute(SQL)
With RST
If .State = adStateOpen Then
If Not .EOF Then
If Not IsNull(RST(0)) Then
Select Case RST(0).Type
Case adVarChar
GetData = Trim(RST(0))
Case Else
GetData = RST(0)
End Select
Else
Select Case RST(0).Type
Case adBoolean
GetData = False
Case adVarChar
GetData = ""
Case adDouble, adInteger
GetData = 0
Case adDate, adDBTimeStamp
GetData = InvalidDate
End Select
End If
Else
Select Case RST(0).Type
Case adBoolean
GetData = False
Case adVarChar
GetData = ""
Case adDouble, adInteger
GetData = 0
Case adDate, adDBTimeStamp
GetData = InvalidDate
End Select
End If
Else
Select Case RST(0).Type
Case adBoolean
GetData = False
Case adVarChar
GetData = ""
Case adDouble, adInteger
GetData = 0
Case adDate, adDBTimeStamp
GetData = InvalidDate
End Select
End If
End With
Set RST = Nothing
End Function

THANKS AND REGARDS klaydze

if(you type your code here) { Messagebox.Show("You help me a lot!"); } else { You help me = null; }

[Simon P Stevens]

A variant in VB is something that can be any type. The closest equivalent in C# is an 'Object'. In C# all data types derive from Object, so you can return the value as an Object and then cast it to what ever type it actually is. (By the way, this is rather bad programming style, you should really be using parametrized queries, or stored procedures. This code is very vulnerable to SQL injection attacks. Also, by just chucking objects around everywhere you lose a lot of the benefits of strongly typed languages like C# such as compile time type checking. Also you seem to be repeating your select/case block about 3 times with the same code in it each time, it seems like you might have some redundant code here)

Simon

[klaydze]

Simon Stevens wrote:

A variant in VB is something that can be any type. The closest equivalent in C# is an 'Object'. In C# all data types derive from Object, so you can return the value as an Object and then cast it to what ever type it actually is. (By the way, this is rather bad programming style, you should really be using parametrized queries, or stored procedures. This code is very vulnerable to SQL injection attacks. Also, by just chucking objects around everywhere you lose a lot of the benefits of strongly typed languages like C# such as compile time type checking. Also you seem to be repeating your select/case block about 3 times with the same code in it each time, it seems like you might have some redundant code here)

Hi Simon, First, thanks for your reply. I already converted my code to C#.Net. Yeah I used object as equivalent of variant. My code something like this.

public object GetData(string SQL)
{
DataTable DT = new DataTable();

 DT = dbConn.ExecuteQuery(SQL); dbConn is my connection class

 if (!String.IsNullOrEmpty(DT.Rows\[0\]\[0\].ToString()))
 {
      switch (DT.Column\[0\].DataType.ToString())
      {
            case "System.ToInt32":
                //Code
                return;
            case "System.String":
                //code
                return;
      }
 }

}

Any piece of advise? The code just get the 1 field value in the query. Just thinking this is the best way, the shortest way of getting the 1 value in a query. Example:

string sName;
sName = GetData("Select txtFirstname From tblUsers Where intID = 1").ToString();

Regards, klaydze

if(you type your code here) { Messagebox.Show("You help me a lot!"); } else { You help me = null; }

[Simon P Stevens]

klaydze wrote:

Any piece of advise?

It's unmaintainable and insecure code. What if some nasty user comes along and calls

GetData("DROP DATABASE [yourdatabase]")

And this is just a minor problem. If you expose the ability to execute arbitrary SQL against your database you can wave goodbye to any serious form of security or data integrity. If your just writing a prototype or for fun, don't worry about it too much. Just be aware the code is dangerous and should never grow into production code. If this is production code I would advise a serious rethink about your architecture. You should create a separate data access layer. The DA layer should have strongly typed accessor methods, and they should being using pre written parametrized queries or stored procedures (Google those terms, it's pretty easy to use either with ADO.net) E.g. you would have a UserDA class with a public User GetUser(String userName) method. The method would run the parametrized query and create a User object to be returned to the caller. This prevents a lot of issues. 1) You are only passing in the user name, not the whole SQL string so the caller can't execute whatever SQL they want. 2) You are returning a strongly typed User object so the caller gets exactly what they are expecting. 3) The use of stored procs or parametrized queries prevents SQL injection attacks. 4) You can build in security if you want and only allow certain users to retrieve and modify certain data.

Simon

[klaydze]

Simon Stevens wrote:

It's unmaintainable and insecure code. What if some nasty user comes along and calls GetData("DROP DATABASE [yourdatabase]") And this is just a minor problem. If you expose the ability to execute arbitrary SQL against your database you can wave goodbye to any serious form of security or data integrity. If your just writing a prototype or for fun, don't worry about it too much. Just be aware the code is dangerous and should never grow into production code. If this is production code I would advise a serious rethink about your architecture. You should create a separate data access layer. The DA layer should have strongly typed accessor methods, and they should being using pre written parametrized queries or stored procedures (Google those terms, it's pretty easy to use either with ADO.net) E.g. you would have a UserDA class with a public User GetUser(String userName) method. The method would run the parametrized query and create a User object to be returned to the caller. This prevents a lot of issues. 1) You are only passing in the user name, not the whole SQL string so the caller can't execute whatever SQL they want. 2) You are returning a strongly typed User object so the caller gets exactly what they are expecting. 3) The use of stored procs or parametrized queries prevents SQL injection attacks. 4) You can build in security if you want and only allow certain users to retrieve and modify certain data.

Hi Simon, Thanks for the advise. In short don't passed a sql statement in a parameter method? How about if I want to create a method that is accessible in all my form the same with my previous code.

public object GetData(string SQL)

. How do I secured it? In your example public User GetUser(string sUser), your just passing a single field where "sUser" is my filter in my sql statement inside the User Method.

SELECT * FROM tblUsers WHERE txtUser = sUser

Thanks and Regards klaydze

if(you type your code here) { Messagebox.Show("You help me a lot!"); } else { You help me = null; }

[Simon P Stevens]

But you should be using parametrized queries not just build up the sql statement on your own out of strings.

Simon

[klaydze]

Simon Stevens wrote:

But you should be using parametrized queries not just build up the sql statement on your own out of strings.

Hi Simon, Sorry but I don't get it. "-( Regards klaydze

if(you type your code here) { Messagebox.Show("You help me a lot!"); } else { You help me = null; }

[Simon P Stevens]

Use a SqlCommand and insert parameters into the command. like this:

public static String GetUser(String userId)
{
// Create your connection however you normally do it.
SqlConnection connection = new SqlConnection();

// Create a sql query with parameters in any place you need user data to be inserted.
String sqlQuery = "SELECT userName FROM Users WHERE UserId = @UserIdParameter";
SqlCommand command = new SqlCommand(sqlQuery, connection);

// Add a parameter for each value entered by the user.
// By doing it this way, the caller can never inject extra SQL that.
command.Parameters.Add(new SqlParameter("@UserIdParameter", userId));

// Execute the reader to run the query.
using (SqlDataReader reader = command.ExecuteReader())
{
    if (reader.HasRows)
    {
        return reader.GetString(0);
    }
    else
    {
        return String.Empty;
    }
}

}

This means that your caller can't inject sql in because they only have control over the parameter and because it's a parameter, when it the query is executed, the parameter will be validated and surrounded with quotes and any command characters will be removed to prevent injection attacks. Read more here: SQL Injection Attacks and Some Tips on How to Prevent Them[^]

Simon

[klaydze]

Hi Simon, Thanks for giving sample program. I'll try this one. By the way, Can you look at the code below. That is my clsConnection where i execute my queries. Is it safe? And can you point out in my code if there is something bad code. This is the way i used it. E.g.

clsConnection myConn = new clsConnection();
DataTable DT = new DataTable();

string SQL = "SELECT \* FROM tblUsers";
DT = myConn.ExecuteQuery(SQL);

using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Windows.Forms;

namespace Micromix.Class
{
class clsConnection
{
public SqlConnection objConnection;
SqlTransaction objTransaction;

    public bool SqlConnect()
    {

        try
        {
            objConnection = new SqlConnection();
            objConnection.ConnectionString = Connect.ConnString.ToString();
            //objConnection.ConnectionString = "Persist Security Info=False;User ID=sa;Initial Catalog=MICROMIX\_PP;Data Source=(local)";
            //objConnection.ConnectionString = "Server=" + GetServer("\\\\conn.cfg") + "; DataBase=" + GetDatabase("\\\\conn.cfg") + "; User ID =" + GetUser("\\\\conn.cfg") + "; Integrated Security=false";

            if (objConnection.State == ConnectionState.Closed)
                objConnection.Open();

            return true;
        }

        catch (Exception ex)
        {
            MessageBox.Show("Failed to connect to data source.", "Connect Failed", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            MessageBox.Show(ex.Message);
            return false;
        }

        finally
        {
            objConnection.Close();

        }

    }

    public DataTable ExecuteQuery(string strSQL)
    {
        DataTable objDataTable;
        SqlDataAdapter objDataAdapter;

        try
        {

            SqlConnect();
            objDataAdapter = new SqlDataAdapter();

            {

                objDataTable = new DataTable();
                objDataAdapter.SelectCommand = new SqlCommand(strSQL, objConnection, objTransaction);
                objDataAdapter.Fill(objDataTable);

                return objDataTable;
            }

        }

        catch (Exception sqlex)
        {