sql Table Lookup best methods?
-
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups. What would be the more efficienct method to use? 'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReader Dim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnection myItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1" myItemCommand.Connection.Open() myItemReader = myItemCommand.ExecuteReader() Dim myfileout As StreamWriter myfileout = File.CreateText(Server.MapPath("out.txt")) myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id") While myItemReader.Read If myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then 'what method to to look up for example the category name using the category_quid referenced in the item master 'Write out record for parts file
Ronhawker
-
I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results to a tab delimited data file for a data conversion. I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups. What would be the more efficienct method to use? 'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReader Dim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnection myItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1" myItemCommand.Connection.Open() myItemReader = myItemCommand.ExecuteReader() Dim myfileout As StreamWriter myfileout = File.CreateText(Server.MapPath("out.txt")) myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id") While myItemReader.Read If myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then 'what method to to look up for example the category name using the category_quid referenced in the item master 'Write out record for parts file
Ronhawker
The simplest way is to use join in sql statement for i.e. select i.itemname, i.categorytype from item i inner join categorytype c on c.categorytype=i.categorytype pass above statement is sqlcommand object. Thanks & Regards Kumar Prabhakar
abc