CODEWITHSUNDEEP
c#java.netdatabasevisual-studio-2008
C J
C J

Reputation: 417

Retrieving metadata (table name) from a SQL statement

I am using Visual Studio 2008.

I have created a Winforms application, and I am trying to extract the table name from a SQL statement.

con = new SqlConnection(connString);
String queryString = "Select CUSTOMER_NAME from CUSTOMER_DETAIL";

Is there any function to do so?

Please help me out.

Upvotes: 2

Views: 6381

Answers (4)

C J
C J

Reputation: 417

This is the Method which gives us tablename just change the SQL query string,connection String Works with simple query,Joins too

 public static List<string> getTablenames(string connString,string QueryString)
    {

        SqlConnection con = new SqlConnection(connString);
        con.Open();
        DataTable dt = con.GetSchema("Tables");

        List<string> getTableName = new List<string>();
        List<string> tablenames = new List<string>();

        foreach (DataRow dr in dt.Rows)
        {
           tablenames.Add(dr[2].ToString());
        }
        for (int ii = 0; ii < dt.Rows.Count; ii++)
        {
            string myTable = tablenames[ii];
            Boolean checkMyTable = QueryString.Contains(myTable);
            if (checkMyTable == true)
            {
                    getTableName.Add(myTable);
            }
        }
        con.Close();
        return getTableName;
    }

thanxx StackoverFlow

Upvotes: 0

Nika G.
Nika G.

Reputation: 2384

It's good that you have mentioned Java's ResultSetMetaData. Quoting that link:

public interface ResultSetMetaData

An object that can be used to get information about the types and properties of the columns in a ResultSet object.

As for C#, you can get the same information using the DataColumn Class.

DataType Property of DataColumn class can be used to get (and to set) the type of data stored in column, AllowDBNull can be used to get (and set) the value that indicates whether null values are allowed in this column, etc... To get the full list of DataColumn properties follow the link I have posted above.

Sample code (gets column datatype):

SqlConnection con = new SqlConnection(connString);
String queryString = "Select CUSTOMER_NAME from CUSTOMER_DETAIL";
SqlCommand cmd = new SqlCommand(queryString, con);
DataTable myTable = new DataTable();
myTable.Load(cmd.ExecuteReader());
DataColumn column = myTable.Columns[0]; // zero based index of column, alternatively use column name
string typeOfColumn = column.DataType.Name; // or column.DataType.FullName to get the fully qualified name of the System.Type

Upvotes: 1

Tathagat Verma
Tathagat Verma

Reputation: 548

  • Try using "Interop - SQLDMO"
  • Else you might have to write your own custom class
  • Rather than using a function that evaluates a string (in which the query is written), I'd suggest you make a custom SQL query builder class (as per your requirements) and then write your methods or extensions to achieve your goal of getting metadata of the query
  • That is if you have no option but to use SQL-queries directly in your c# code

Hope this helps.

Upvotes: 1

user1082916
user1082916

Reputation:

I found this site that has a GREAT parser....

http://www.sqlparser.com/

well worth it. Works a treat......

Upvotes: 1

Related Questions