Lookup Arrays

I need to load an array of order numbers.. Then later I need to be able to
'lookup' in that area for a specific order number (for example one that user
keys into text box).
how is this accomplished? ive researched Array and Arraylist and cannot
figure how without a bunch of work.
There must be something simple for this.
Can you help?
Thanks.
jrHello Jr,
Welcome to the MSDN newsgroup.
From your description, you have an order number list which want to be
stored in a list container and you'll lookup some certain order numbers
from that container later in your SQL Reporting service's report, correct?
Based on my experience, since these code should be in your custom code
period or an separate assembly, so you're using them as custom code or
custom assembly in your report , right? If this is the case, what
available to us are all those utility or collection classes in the .net
framework system.collections namespace(Such as the Array, ArrayList you
mentioned).
For your scenario, I'm wondering how will you identify a certain order
number? Just through the orderID or will you give each order number another
identity(a key value) for lookup? Here are two generic cases:
1. If you just want to get the order item's index in the container(such as
Array) through the order number(suppose its of string type), you can just
Array.IndexOf method to locate a certain item in the Array:
#Array.IndexOf, m¨¦thode (Array, Object)
http://msdn2.microsoft.com/fr-fr/library/7eddebat.aspx
Here is a simple test class which intializing the Array(orderList) in the
type's static initializer and expose a public static method for lookup item
index in the array:
==================public class ArrayUtil
{
public static string[] OrderArray;
public ArrayUtil()
{
}
static ArrayUtil()
{
OrderArray = new string[10];
for (int i = 0; i < OrderArray.Length; i++)
{
OrderArray[i] = "Order_" + i;
}
}
public static int FindOrder(string num)
{
return Array.IndexOf(OrderArray, num);
}
}
=====================
2. If each order nunber will be stored with an identity value as the lookup
key, I think the "HashTable" class is the reasonable choice. HashTable
class support storing multiple key/value pairs and have method for looking
up a certain item in it via its key value:
#Hashtable Class
http://msdn2.microsoft.com/en-us/library/system.collections.hashtable.aspx
Hope this helps. If there is any paricular concerns in your scenario or if
you have any other ideas ,please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Jr,
How are doing on this issue, have you got any progress or does my last
reply helps you a little on this? If you have anything unclear or still
anything else we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema ?
This is urgent. Thnaks in advance.
Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ?
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca
|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ?
>
> This is urgent. Thnaks in advance.
>

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema '
This is urgent. Thnaks in advance.Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>