Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using SQL Variables 1

Status
Not open for further replies.

jcs1953

IS-IT--Management
Nov 28, 2007
53
US
I'm having a real brain fart on this. I know how to use variables for column values in an sql statement in ASP.Net 2.0 but how do I word the function if the table name is the variable?

Public Shared Function GetSQLTQResults(ByVal strtablename As String) As DataTable

Dim strCon As String = GetConnectionString("FMC_ACCESSConnectionString")
Dim conn As New SqlConnection(strCon)
Dim cmdSelect As New SqlCommand("SELECT Convert(varchar(10),tablename.Date,101) As Date,tablename.Result As Result,tablename.Test As Test FROM tablename Where tablename = @tablename ", conn)
cmdSelect.Parameters.Add("@tablename", SqlDbType.NVarChar, 20).Value = strtablename
Dim dtrTable As New DataTable
conn.Open()
dtrTable.Load(cmdSelect.ExecuteReader(CommandBehavior.CloseConnection))
Return dtrTable
End Function

??
 
you can't. for that you build the sql by hand
Code:
var sql = "SELECT [put fields here] FROM " + strtablename +" the_alias;"

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259
 
in this scenario though a variable table name doesn't make sense.
1. you columns are static but the table name is dynamic. this means you need to manage how the user selects the table and manage the schema of the table is correct. too much "you just know" knowledge
2. you are not gaining anything by making the tablename dynamic. other than complexity.

I would approach this differently.
1. create a common Model
Code:
class Model
{
   public DateTime Date {get;set;}
   public string Result {get;set;}
   public string Test {get;set;}
}
2. create a strategies to retrieve the model
Code:
interface IStrategy
{
   IEnumerable<Model> GetData();
}

class TableAStrategy : IStrategy
{
   public IEnumerable<Model> GetModels()
   {
      DataTable table = GetRowsFromDatabase();
      foreach(DataRow row in table.Rows)
      {
          yield return new Model
                 {
                    Date = (DateTime) row["..."];
                    Result = (string) row["..."]
                    Test = (string) row["..."]
                 }
      }
   }
}

class TableBStrategy : IStrategy
{
   public IEnumerable<Model> GetModels()
   {
        yield return new Model{...};
        yield return new Model{...};
        yield return new Model{...};
   }
}

etc....
3. select the appropriate strategy and display data
Code:
class MyPage : Page
{
   private readonly IStrategy[] strategies = new IStrategy[]
          {
               new TableAModelStrategy(),
               new TableBModelStrategy(),
          };
}

public void Some_Event_Handler(...)
{
      int index = GetIndexOfStrategyToUse();
      AControl.DataSource = strategies[index].GetData();
      DataBind();
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259
 
Wow! Complex answer to what I thought was a simple to answer question (although not so simple I could answer it).
Here's my senerio. I have to pull data fro an Oracle Database (read only) and an sql database and combine the results. I used table.load() to combine the results into one table. The worked on my developement servers (databases on different servers) but on the production servers I kept getting "constraints" errors. I found that if I used bulkcopy to copy the data from each query (2 queries each 1 for Oracle and 1 for SQL) to a table in SQL that I no longer had that problem. The data combined just fine. I have 5 tables (tempQuery-tempQuery4). The origional function returns the needed data from tempQuery table only but I thought that if I could set the tablename as a variable I could use just one Function as all the queries to the tempTables are the same.
Oh Well. Thanks taking the time to try and help me out.
 
Complex != Complicated. Complexity can be encapsulated through segregation and validated with automated testing. Complicated means it's prone to error and difficult to test.
case in point: breaking each query into a separate operation. you can then test each command independent from the reset of the system.

what kind of "constraint" errors where you getting? solving this problem may be easier then a series of temp tables.
I also don't understand why you would have tables tempQuery[1-4]? if they really are temporary tables why not use true temporary table
Code:
create table #foo
(
  id long,
  field varchar(50),
  ...
)
select into #foo  select ...
drop table #foo
I'm sure Oracle has something similar.
I would also consider a different approach to you're connection mangement. no error handling and connection per query is very inefficient.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259
 
Not sure if this is what you are looking for but here you can pass a table name to a stored proc.

declare @tableName varchar(50)
declare @strSQL varchar(3000)
select @strSQL = "SELECT * FROM " + @tablename
exec(strSQL)


Ordinary Programmer
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top