13223

get data from variable table and return as datatable c#

Question:

I need to get retrive all of the data from specified tables and I don't need the data to be strongly typed so I am returning it as a data table.

public DataTable GetByTypeName(String t) { var type = Type.GetType(t); var dt = new DataTable(); using (var sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["MasterPlanConnectionString"].ConnectionString)) { var sqlComm = new SqlCommand("SELECT * FROM @table", sqlConn); sqlComm.Parameters.AddWithValue("@table", type.Name); sqlConn.Open(); var dr = sqlComm.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); } return dt; }

When I run this I get the error

System.Data.SqlClient.SqlException was unhandled by user code Message=Must declare the table variable "@table".

I cannot figure out why this isn't working as I have declared @table. I know this method is open to some bad sql attacks so I plan to add in some protection about exactly what types can be queried against.

Answer1:

You can construct your query dynamically - (should be ok over here, but may expose your query to sql injection)

var query = String.Fromat("Select * from [{0}]", type.Name); var sqlComm = new SqlCommand(query, sqlConn); /*sqlComm.Parameters.AddWithValue("@table", type.Name);*/

Recommend

  • C# SQLDataAdapter - Not inserting data into DB
  • How to create database backup, when DB not stored in Microsoft SQL Server?
  • TPL Tasks & Stored procedures
  • How to get a return value from a stored procedure in VB.NET
  • c# database connection string
  • My database system cannot find the file specified in asp.net
  • Is it possible to have an editable DetailsView for entity objects with subclasses?
  • Servicestack ORMLite/Massive managing multiple DataTables with Expandos / Dynamic?
  • Output from SQL Server and C# different
  • Creating a database programatically in nlog to enable using DatabaseTarget
  • An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.
  • How can I fix CA2100 Review SQL queries for security vulnerabilities issue
  • Why does opening a connection throw a distributed transactions error in MySQL? (.NET Connector)
  • uncommittable transaction is detected at the end of batch. the transaction is rolled back
  • Parse returned C# list in AJAX success function
  • Updating an Access Database via a DataGridView Using OLEDB in VB.NET
  • Azure service bus paired namespace - simulate failover
  • ASP.NET Gridview Paging Problem
  • How to make a UserDetailsManager available as a bean
  • Query timeout expired in django-mssql when executing custom SQL directly
  • mysql table locked after php crashes
  • Weird LEFT OUTER JOIN on Includes eager loading of rails 3
  • ASP.NET, C# How to Pass a StringQuery to a custom SQL Command
  • Connecting Google Cloud SQL with Wordpress on Google Compute Engine
  • Passing parameter through “window.location.href”
  • SQL Server re-calculate or not?
  • Possible to get the line number of the currently executing sproc in SQL Server?
  • How to set an entity field that does not exist on the table but does exists in the raw SQL as an ali
  • It is possible use the same sql azure instance from two different cloud service of two different sub
  • TSQL Rolling Average of Time Groupings
  • distinct values from multiple fields within one table ORACLE SQL
  • What is corresponding c++ data type to SQL numeric(18,0) data type?
  • SqlCommand back up Database
  • How can Delete be both a DDL and a DML statement
  • How to 'create temp table as select' in Slick?
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • pyodbc doesn't report sql server error
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?