Monday, June 23, 2014

How to search all columns of all tables in a database for a keyword

Create this procedure in the required database and here is how you run it:

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO


Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
               @SearchStr nvarchar(100)
)
AS
BEGIN

               -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
               -- Purpose: To search all columns of all tables for a given search string
               -- Written by: Narayana Vyas Kondreddi
               -- Site: http://vyaskn.tripod.com
               -- Tested on: SQL Server 7.0 and SQL Server 2000
               -- Date modified: 28th July 2002 22:50 GMT


               CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

               SET NOCOUNT ON

               DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
               SET  @TableName = ''
               SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

               WHILE @TableName IS NOT NULL
               BEGIN
                               SET @ColumnName = ''
                               SET @TableName =
                               (
                                              SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
                                              FROM        INFORMATION_SCHEMA.TABLES
                                              WHERE                     TABLE_TYPE = 'BASE TABLE'
                                                             AND          QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                                                             AND          OBJECTPROPERTY(
                                                                                            OBJECT_ID(
                                                                                                           QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                                                                                                            ), 'IsMSShipped'
                                                                                                   ) = 0
                               )

                               WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
                               BEGIN
                                              SET @ColumnName =
                                              (
                                                             SELECT MIN(QUOTENAME(COLUMN_NAME))
                                                             FROM        INFORMATION_SCHEMA.COLUMNS
                                                             WHERE                     TABLE_SCHEMA           = PARSENAME(@TableName, 2)
                                                                            AND          TABLE_NAME              = PARSENAME(@TableName, 1)
                                                                            AND          DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                                                                            AND          QUOTENAME(COLUMN_NAME) > @ColumnName
                                              )
              
                                              IF @ColumnName IS NOT NULL
                                              BEGIN
                                                             INSERT INTO #Results
                                                             EXEC
                                                             (
                                                                            'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                                                                            FROM ' + @TableName + ' (NOLOCK) ' +
                                                                            ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                                                             )
                                              END
                               END         
               END

               SELECT ColumnName, ColumnValue FROM #Results

END

Creating javascript alerts in ASP.NET with UpdatePanel

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="UpdatePanel.aspx.cs" Inherits="UpdatePanel" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>Untitled Page</title>
</head>
<body>
  <form id="form1" runat="server">
      <asp:ScriptManager ID="scriptMangerTest" runat="Server">
      </asp:ScriptManager>
      <asp:UpdatePanel ID="updateTest" runat="Server">
          <ContentTemplate>
              <div>
                  <table cellpadding="0" cellspacing="0" width="60%">
                      <tr>
                          <td align="right">
                              <asp:Label ID="lblName" runat="Server" Text="Enter E-mail Id">
          
                              </asp:Label>
                          </td>
                          <td>
                              <asp:TextBox ID="txtEmail" runat="Server"></asp:TextBox>
                          </td>
                      </tr>
                      <tr>
                          <td colspan="2" align="left">
                              <asp:Button ID="btnCheck" runat="Server" Text="Validate" OnClick="btnCheck_Click" />
                          </td>
                      </tr>
                  </table>
              </div>
          </ContentTemplate>
      </asp:UpdatePanel>
  </form>
</body>
</html>


---------------------------------------------------------------------------------
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public partial class UpdatePanel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnCheck_Click(object sender, EventArgs e)
    {
        if (!CheckEmail(txtEmail.Text))
        {

            Guid gMessage = Guid.NewGuid();
            string sMessage = "alert('Invalid E-mail Id ');";

            ScriptManager.RegisterStartupScript(updateTest, updateTest.GetType(), gMessage.ToString(), sMessage, true);
        }

    }

    private bool CheckEmail(string EmailAddress)
    {
        string strPattern = @"^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$";

        if (System.Text.RegularExpressions.Regex.IsMatch(EmailAddress, strPattern))

        { return true; }

        return false;


    }

}

Friday, June 20, 2014

LINQ Insert Update Delete

ASPX.CS
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        Class1 obj = new Class1();
        obj.insert(txtname.Text, txtaddress.Text);
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        Class1 obj = new Class1();
        obj.delete(Convert.ToInt32(txtid.Text));
    }
}
Class file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Linq;
using System.Web;
using System.Configuration;

/// <summary>
/// Summary description for Class1
/// </summary>
public class Class1
{
    public string aaa = ConfigurationManager.ConnectionStrings["nareshConnectionString"].ToString();
    DataClassesDataContext aaaa = new DataClassesDataContext();


    public Class1()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public void insert(string name, string address)
    {
        T_Linq obj = new T_Linq();
        //obj.id = id;
        obj.name = name;
        obj.address = address;
        aaaa.T_Linqs.InsertOnSubmit(obj);
        aaaa.SubmitChanges();
    }
    public void delete(int id)
    {
        T_Linq objid = (from p in aaaa.T_Linqs
                          where p.id == id
                          select p).SingleOrDefault();
        objid.id = id;
        aaaa.T_Linqs.DeleteOnSubmit(objid);
        aaaa.SubmitChanges();
    }
}