How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?

 SELECT TABLE_SCHEMA + '.' + TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies


ADVERTISEMENT

List Of All Tables Or Stored Procedures

Jan 17, 2008

Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.

View 3 Replies View Related

Search For A Table/string Within Job Steps, Dts, Database, Stored Procedures Etc

May 16, 2007

Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp

View 1 Replies View Related

Long Parameter List For Stored Procs

Nov 20, 2006

Hi All,

I've got a problem with increasingly long parameter lists for SProcs... Especially when one calls another SProc, and so on. Is there any way around this? Like can you dynamically construct a string and pass that? I'm just looking to see if more experienced players have found ways around this, or have just dealt with it by using well formed code.

Thanks

Chris

View 7 Replies View Related

A Stored Proc To Search All Procs, Views, Triggers And Functions

Jul 20, 2005

I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}

View 1 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Searching Thru Stored Procs Via The Sys Tables

Sep 3, 2003

Basically, I want to be able to have a stored procedure that will search through all the stored procedures looking for a given string and returning the names of all the stored procs that contain that string value.

I know I can script it off and then do a text search in Notepad or whatever, but I figured there must be a more elegant way to do it. More than likely dealing with the DB sys tables.

Is there already a tool in SQL Server that does this? Or has anybody had a chance to roll their own?

Thanks,
Tim

View 4 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

Working With SQL Server Temp Tables In Stored Procs

Nov 18, 2005

I am trying to create a SQL data adapter via the wizard, however, I get
the error "Invalid object name #ords" because the stored procedure uses
a temp table. Anyway around this? Thanks.

View 11 Replies View Related

$100 USD To The Winner... Stored Procs, Temporary Tables And Cursors... Oh My!

Oct 14, 2001

I'm so desperate, I'll pay $100 to the proper solution to this problem. I'm sure it's an easy fix, but I'm wasting more money every day trying to figure it out...

I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an "indented outline" of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it's very frustrating and extremely important.

I'd rather pay an expert here than try to navigate a tech help line.

ConnIS is defined in an earlier include file...

Set oCmd = Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = ConnIS
oCmd.CommandText = "dbo.Expandset" 'Name of SP
oCmd.CommandType = adCmdStoredProc 'ADO constant for 4
Set oTmp = oCmd.CreateParameter("@current", adInteger, adParamInput,, 892)
oCmd.Parameters.Append oTmp
Set oRs = Server.CreateObject("ADODB.Recordset")
oRs.Open oCmd
Response.Write oRs.RecordCount & "<hr>"
oRs.Close
Set oRs=Nothing


This code generates the following result when run from an active server page:

-1<hr>

When I execute the raw SQL code ("exec Expandset 892") against the stored proc in the query analyzer, I get:

item tier
----------- -----------
892 1
948 2
895 2
946 2
945 2
893 2
894 3
944 2
943 2
904 2
896 3
897 4
901 2
903 3
902 3
900 2
947 2
899 2

The source for the stored proc is:
------------------------------------------------------
CREATE PROCEDURE Expandset (@current int) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item int, tier int)
CREATE TABLE #output (item int, tier int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE tier = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE tier = @level
SELECT @line = space(@level - 1) + convert(varchar,@current)
INSERT INTO #output (item, tier) values (@current, @level)
DELETE FROM #stack
WHERE tier = @level
AND item = @current
INSERT #stack
SELECT ID, @level + 1
FROM SITE_Container
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
SELECT O.item, O.tier FROM #output O
-------------------------------------------------------------------

The relevant portions of the Table definitions for SITE_Container are:

CREATE TABLE [dbo].[SITE_Container] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Parent] [int] NULL)

-------------------------------------------------------------------

My contact information is:

Jared Nielsen
PO Box 600271
Jacksonville, FL 32260
904-230-1688
888-316-2357 vm / fax

View 3 Replies View Related

List Of Stored Procedures

Oct 12, 2006

How do I get a list of the stored procedures currently in the database? Is there a quick way to edit them?

Thank you!

View 3 Replies View Related

How Search In Stored Procedures...

Apr 14, 2008

Is there any way to search in stored procedures? If in one of the stored procedure there is an a query like "delete from myTable...."
and i want to get all those procedures that contained this query text.......

Muhammad Saifullah

View 9 Replies View Related

List All Stored Procedures Used In A VS.NET Project

Apr 29, 2004

Hi

This is kind of a visual studio question, but pertinent to db' s - due to many changes of our application I know that there are many stored procs in the database that are no longer used. Since I cannot get a list from the programmers, is there any way to get a list out of visual studio, of every SQL stored procedure called in that project? (Going through each form manually will just be too time consuming for me or the programmers)

Anyone know any tricks??
thx
Des

View 2 Replies View Related

Search For String In Stored Procedures

Aug 22, 2001

Ever needed to find a stored procedure with a specific string in it? You can pretty this up as a stored procedure and pass it a parm or cut and paste it into query analyzer.

select name from sysobjects
where id = (select id from syscomments
where text like '%like%')


Edit:

The above works only for a single hit. For multiple hits, this works

select name from sysobjects as A
join syscomments as B
on (text like '%cursor%')
where A.id = B.id

Live and learn,

Cat



Edited by - cat_jesus on 08/22/2001 10:09:49

Edited by - cat_jesus on 08/22/2001 10:10:29

View 15 Replies View Related

Search For A Field Name In Stored Procedures

Jul 23, 2005

Hi,Is there any way to find all stored procedures that contain a givenfieldExample: I want to find all stored procedures that work with the fieldShipDate in tblOrder tableThanks, Eugene

View 3 Replies View Related

Search Contents Of Stored Procedures?

Jul 20, 2005

Hi All,I'm wondering if anyone can tell me if it's possible to search forstored procedures by their contents?I tend to leave very descriptive notes in stored procedures andthey're beginning to build up in number, so I'm wondering if it'spossible to search in Query Analyzer for stored procedures thatcontain certain words / phrases etc.Alternatively, is there a way to do a system-level select that lookssomething like the following pseudo-gibberish (actually, it's actualgibberish, I guess, rather than pseudo-gibberish):SELECT * FROM [all stored procedures] where [contents] like '%mysearch phrase%'Any help much appreciated!Much warmth,Murray

View 4 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Search & Replace In Multiple Stored Procedures

Aug 31, 2007

Hi all,

Does anyone know how to do the search and replace in multiple stored procedures using SQL 2005 Management Studio?
Any help will be appreciated.

Thanks!

View 3 Replies View Related

List Of Stored Procedures With Permission For Executing For User

Jul 26, 2007

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

View 4 Replies View Related

Transact SQL :: Can Search All Databases And All Stored Procedures With A Certain Column Alias?

Oct 14, 2015

I am familiar with the sp_MSForEachDBand the USE Parameter I did Google and found [URL]

View 3 Replies View Related

T-SQL (SS2K8) :: Search Based On Table Name And Add Code To Existing Stored Procedures

Jun 30, 2014

Below is sample SQL:

SQL_1: Creates a database
SQL_2: Creates 2 stored procedures

Now, I need to search database SP`s for Table2 t2 ON t2.CID = t1.CID and ALTER them with Table2 t2 ON t2.CID = t1.CID.

INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'SQL_1:
USE [master]
GO

[code]...

View 6 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

How To Search In 2 Tables By Using SQL Stored Procedure??

Mar 7, 2008

Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you

View 4 Replies View Related

Tables And Stored Procedures

Jul 20, 2005

SQL Server 2000:Question 1If you drop / rename a table and then recreate the table with the SAMENAME, what impact does it have on stored procedures that use these tables?From a system perspective, do you have to rebuild / recompile ALL the storedprocedures that use this table?I had a discussion with someone that said that this is a good idea, sincethe IDs of the tables change in sysobjects and from a SQL SERVER query planperspective, this needs to be done...Question 2If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if anerror occurs? Will it Rollback? The theory is that it won't becauseTruncate doesn't utilize the logs where as Delete From uses the SQL Logs?Thanks!

View 3 Replies View Related

Stored Procedure To List Values From Different Tables

Jan 19, 2006

I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6

View 8 Replies View Related

INSERT INTO Tables Using Stored Procedures

Apr 28, 2007

Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using: 

View 5 Replies View Related

How To Copy Some Tables, Stored Procedures

Feb 14, 2008

hi I'm working in VS2005 with SQL server Express database.How can I copy some tables, stored procedures and diagrams from one database to another? thanks 

View 3 Replies View Related

Copy DB Tables And Stored Procedures To A New DB With Different Name

Feb 14, 2006

Hello all!
What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB???
So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?
The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!
Any help would be greatly appreciated!!!

View 3 Replies View Related

Stored Procedures And Multiple Tables

Mar 2, 2006

Hi,
     I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table.
i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates.
As I know nothing about stored procedures I'm not even sure how to go about this??
Regards.
Peter.

View 2 Replies View Related

Multiple Stored Procedures ,same Tables

Sep 3, 2007



Hi,

I am using multiple stored procedures which are using same set of tables at a time .
As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.
My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.

as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins
where i want to take just 1 min which is the time took by second sp

I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time

Please suggest me how to proceed here.i am stuck

what should i do with the tables or stored procedures?

Thank you
Raj Deep.A

View 4 Replies View Related

Stored Procedures With Temp Tables

Nov 23, 2007

If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?

View 3 Replies View Related

Multiple Stored Procedures,same Set Of Tables

Sep 3, 2007


Hi ,

I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.

So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.

what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable

can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.

please help me here,i am stuck

Thank you

View 2 Replies View Related

More Tables Do Search,please Help Me,sql Server Stored Procedure Problem

Oct 12, 2006

i have more tables

exmaple:

table1(id,title,content,date)

table2(did,type,title,text1,text2,requestdate)

table3(subid,value1,value2,value3,value4)

.......................15 tables left,some exmaple

now i want use stored procedure do search for asp.net

asp.net send search keyword to sql server,sql server get keywords execute search and return results to web application(asp.net)

i don't konw how what write t-sql,please friends help me.

because table colums is different,so need write t-sql put the temp DATA TO Temp DataTable,last return Temp DataTable to asp.net,asp.net execute split pager display data,eventable colums is different,so on asp.net links is different,need t-sql programming,hope friends can help me complete this effect(i need code,C#/VB.NET +T-SQL).

i at ago like this do.

<%@ Page Language="C#"  ValidateRequest="false"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    private void page_init()
    {
        if (!IsSecure())
        {
            Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
        }
    }


    private void page_load(object sender, EventArgs e)
    {
        string CurrentPageValue = "";
        string Qu = "";
        Double PageValue = 0;
        CurrentPageValue = Request.QueryString["PageN"];
        Qu = Request.QueryString["QuKey"];
        Qu = ReplaceStr(Qu);
        if (Qu != null)
        {
            if (IsNumber(CurrentPageValue))
            {
                PageValue = Convert.ToDouble(CurrentPageValue.Trim());
                Init_Data(PageValue,Qu);
            }
            else
            {
                PageValue = 1;
                Init_Data(PageValue,Qu);
            }
        }
        else
        {
            ExecuteError();
        }
    }


    private void Init_Data(Double CurrentPage,string QuKey)
    {
        try
        {
            string ConStr = GetConnectionString();
            string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
            string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
            string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
            string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%'  order by date desc";
            //expmale for test,i have 17 tables,so write some table do test
            System.Data.DataTable ResultsTable = new System.Data.DataTable();
            ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
            ResultsTable.Columns.Add("title", System.Type.GetType("String"));
            ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
            ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
            ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
            ResultsTable.Columns[0].Unique = true;
            ResultsTable.Columns[0].AutoIncrement = true;
            ResultsTable.Columns[0].AutoIncrementStep = 1;
            ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
            System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
            System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
            System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
            System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
            System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
            System.Data.DataSet Ds = new System.Data.DataSet();
            SearchConnect.Open();
            ada1.Fill(Ds, "table1");
            ada2.Fill(Ds, "table2");
            ada3.Fill(Ds, "table3");
            ada4.Fill(Ds, "table4");
            string ReTitle = "";
            string ReUrl = "";
            string ReIntro = "";
            DateTime ReDate = System.DateTime.Now;
            if (Ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
                {
                    System.Data.DataRow TempRow;
                    TempRow = ResultsTable.NewRow();
                    ReTitle = Ds.Tables[0].Rows[1].ToString();
                    //url need check before count get detail
                    //for test temp defined
                    ReUrl = "url1";
                    ReIntro = Ds.Tables[0].Rows[1].ToString();
                    ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
                    TempRow[1] = ReTitle;
                    TempRow[2] = ReUrl;
                    TempRow[3] = ReIntro;
                    TempRow[4] = ReDate;
                    ResultsTable.Rows.Add(TempRow);
                }
            }
            if (Ds.Tables[1].Rows.Count > 0)
            {
                for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
                {
                    System.Data.DataRow TempRow;
                    TempRow = ResultsTable.NewRow();
                    ReTitle = Ds.Tables[1].Rows[1].ToString();
                    //url need check before count get detail
                    //for test temp defined
                    ReUrl = "url2";
                    ReIntro = Ds.Tables[1].Rows[1].ToString();
                    ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
                    TempRow[1] = ReTitle;
                    TempRow[2] = ReUrl;
                    TempRow[3] = ReIntro;
                    TempRow[4] = ReDate;
                    ResultsTable.Rows.Add(TempRow);
                }
            }
            //........loop to table end
            //................................
            Ds.Dispose();
            ada1.Dispose();
            ada2.Dispose();
            ada3.Dispose();
            ada4.Dispose();
            SearchConnect.Dispose();
            if (SearchResults.Rows.Count > 1)
            {
                DisplayData(CurrentPage, QuKey, SearchResults);
            }
            else
            {
                SearchResults.Dispose();
                ExecuteError();
            }      
        }
        catch
        {
            ExecuteError();
        }
    }


    private void DisplayData(Double PageNumber, string Keywords, System.Data.DataTable CurrentData)
    {
        Double TotalPage = 0;
        Double TotalRow = 0;
        Double TempPage1 = 0;
        decimal TempPage2 = 0;
        int StartValue = 0;
        int EndValue = 0;
        int PageSize = 30;
        TotalRow = CurrentData.Rows.Count;
        TempPage1 = TotalRow/PageSize;
        TempPage2 = Convert.ToDecimal(TotalRow / PageSize);
        if (Convert.ToDouble(TempPage2 - TempPage1) > 0)
        {
            TotalPage = TotalRow / PageSize + 1;
        }
        else
        {
            TotalPage = TotalRow / PageSize;
        }
        if (TotalPage < 1) { TotalPage = 1; }
        if (PageNumber > TotalPage) { PageNumber = TotalPage; }
        if (PageNumber == TotalPage)
        {
            EndValue = TotalRow;
        }
        else
        {
            EndValue = PageNumber * PageSize;
        }
        StartValue = PageNumber * PageSize - PageNumber;
        for (int j = StartValue; j < EndValue; j++)
        {
            System.Web.UI.WebControls.TableRow Tr = new TableRow();
            System.Web.UI.WebControls.TableCell Td1 = new TableCell();
            System.Web.UI.WebControls.TableCell Td2 = new TableCell();
            System.Web.UI.WebControls.TableCell Td3 = new TableCell();
            Td1.Controls.Add(new LiteralControl(CurrentData.Rows[j][1].ToString()));
            Td2.Controls.Add(new LiteralControl(CurrentData.Rows[j][2].ToString()));
            Td3.Controls.Add(new LiteralControl(CurrentData.Rows[j][3].ToString()));
            Tr.Cells.Add(Td1);
            Tr.Cells.Add(Td2);
            Tr.Cells.Add(Td3);
            SearchResults.Rows.Add(Tr);
        }
        System.Web.UI.WebControls.TableRow StateTr = new TableRow();
        System.Web.UI.WebControls.TableCell StateTd = new TableCell();
        StateTd.ColumnSpan = 3;
        if (PageNumber - 1 > 0)
        {
            StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber - 1) + "&qu="+Keywords+">prev</a>"));
        }
        if (PageNumber !=TotalPage)
        {
            StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber + 1) + "&qu="+Keywords+">Next</a>"));
        }
        StateTr.Cells.Add(StateTd);
        SearchResults.Rows.Add(StateTr);
        CurrentData.Dispose();
        SearchResults.Dispose();
        //rows count > 200,runtime at 1.6 seconds left,
        //rows count > 1000,runtime at 8 seconds left,
        //rows count >10000,runtime at 12 seconds left
        //execute speed is very bad,so i want use sql stored procedure search,but i don't write t-sql,hope friends hope me,thanks
    }

    private void ExecuteError()
    {
        SearchResults.Controls.Add(new LiteralControl("we're sorry,we unable find even contain your key data,please try other keywords.suppot to ...."));
        SearchResults.Dispose();  
    }
   
    private bool IsSecure()
    {
        if (!Page.Request.IsSecureConnection)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    private bool IsNumber(string Restr)
    {
        try
        {
            if (Restr != null)
            {
                string TempStr = Restr.Trim();
                if (TempStr != "")
                {
                    System.Text.RegularExpressions.Regex MyTry = new Regex("@[0-9]*$");
                    if (MyTry.IsMatch(TempStr))
                    {
                        Double valueR;
                        Double = Convert.ToDouble(TempStr);
                        if (valueR >= 1 && valueR <= 10000)
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        return false;
                    }

                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        catch
        {
            return false;
        }
    }

    private string GetConnectionString()
    {
        try
        {
            string ConnectionString = "datasource=......,initial catalog=databasename,trusted_connection=yes;integrate security info=true;";
            return ConnectionString;
        }
        catch
        {
            return "!";
        }
    }

    private string ReplaceStr(string k)
    {
        try
        {
            if (k != null)
            {
                string r = k.Trim();
                if (r != "")
                {
                    r = r.Replace("~", "");
                    r = r.Replace("!", "");
                    r = r.Replace("@", "");
                    r = r.Replace("#", "");
                    r = r.Replace("$", "");
                    r = r.Replace("%", "");
                    r = r.Replace("^", "");
                    r = r.Replace("&", "");
                    r = r.Replace("*", "");
                    r = r.Replace("(", "");
                    r = r.Replace(")", "");
                    r = r.Replace("\", "");
                    r = r.Replace("|", "");
                    r = r.Replace("[", "");
                    r = r.Replace("]", "");
                    r = r.Replace("{", "");
                    r = r.Replace("}", "");
                    r = r.Replace(":", "");
                    r = r.Replace(";", "");
                    r = r.Replace("'", "");
                    r = r.Replace("", "");
                    r = r.Replace("<", "");
                    r = r.Replace(">", "");
                    r = r.Replace("!", "");
                    r = r.Replace(",", "");
                    r = r.Replace(".", "");
                    r = r.Replace("?", "");
                    r = r.Replace("/", "");
                    r = r.Trim();
                    return r; 
                }
            }
            else
            {
                return "";
            }
        }
        catch
        {
            return "";
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Do Search SQL Server Page</title>
</head>
<body>
    <asp:Table runat="server" ID="SearchResults">
    </asp:Table>
</body>
</html>

 

 

 

 

 

MyTest:

 

SQL SERVER 2005+WINDOWS SERVER 2003+.NET 2.0

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved