ADD AND ALTER COLUMN IN A TABLE SCRIPT

---ALTER FOR COLUMN NAME NAME AND SIZE---

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CollegeInfo' 
AND COLUMN_NAME = 'CollegeName')
BEGIN
ALTER TABLE CollegeInfo ALTER COLUMN  CollegeName VARCHAR(200) NOT NULL
END

------ADD FOR COLUMN -------


IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CollegeInfo' 
 AND COLUMN_NAME = 'CollegeName')
BEGIN
ALTER TABLE CollegeInfo ADD COLUMN  CollegeName VARCHAR(200) NOT NULL
END

Can dataReader hold data from multiple tables?

Can dataReader hold data from multiple tables?
Yes, DataReader can hold data from multiple tables.

 SqlConnection con = new SqlConnection("server=sugandha;initial catalog = dotnet;uid=sa;pwd=sugandha");
        string query = "select * from Employee; select * from Employee1";
        con.Open();
        SqlCommand cmd = new SqlCommand(query, con);

       SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {

            dr.Read();

            GridView1.DataSource = dr;

            GridView1.DataBind();

            if (dr.NextResult())
            {

                GridView2.DataSource = dr;

                GridView2.DataBind();

            }

        }

        dr.Close();

        con.Close();
 
How can we retrieve two tables of data at a time by using DataReader?

If we pass two queries in the commandText then we get data from two tables.
It return 2 tables in DataReader.

like :
string str="Select * from a;select * from b";
cmd.CommandText=str;
dr=cmd.ExecuteReader();

What is difference between dataset and datareader ?

Following are some major differences between dataset and datareader :

1. DataReader provides forward-only and read-only access to data , while the DataSet object can hold more than one table   from the same data source as well as the relationships between them.
2. Dataset is a disconnected architecture while datareader is connected architecture.
3. Dataset can persists contents while datareader can not persist contents , they are forward only.

I want to force the DataReader to return only schema of the datastore rather than data ?

objDataReader = objCommand.ExecuteReader(CommandBehavior.SchemaOnly)

How can we force the connection object to close after my DataReader is closed ?

ExecuteReader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the DataReader is close.
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

Convert fucntion use in sql

select(CONVERT(VARCHAR(19),GETDATE()))
select(CONVERT(VARCHAR(10),GETDATE(),10))
select(CONVERT(VARCHAR(10),GETDATE(),110))
select(CONVERT(VARCHAR(11),GETDATE(),6))
select(CONVERT(VARCHAR(11),GETDATE(),106))
select(CONVERT(VARCHAR(24),GETDATE(),113))

some sql important queries

use testing
select * from sys.tables
select * from studentrecord

select * from sys.procedures
select * from sys.objects where type='U'

SELECT * FROM sys.all_objects where type in ('FN','AF','FS','FT','IF','TF')

--AF = Aggregate function (CLR)
--C = CHECK constraint
--D = DEFAULT (constraint or stand-alone)
--F = FOREIGN KEY constraint
--PK = PRIMARY KEY constraint
--P = SQL stored procedure
--PC = Assembly (CLR) stored procedure
--FN = SQL scalar-function
--FS = Assembly (CLR) scalar function
--FT = Assembly (CLR) table-valued function
--R = Rule (old-style, stand-alone)
--RF = Replication filter procedure
--SN = Synonym
--SQ = Service queue
--TA = Assembly (CLR) trigger
--TR = SQL trigger 
--IF = SQL inlined table-valued function
--TF = SQL table-valued function
--U = Table (user-defined)
--UQ = UNIQUE constraint
--V = View
--X = Extended stored procedure
--IT = Internal table

SELECT * FROM sys.all_objects where type in ('FN')

select * from testing.sys.objects where type='fn'