Database and ADO.Net simple review

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Database and ADO.Net have finished, lead you to fight small Yang teachers also need to rest for a period of time, really a bit reluctant to go, hope the teacher is also very good. The brief review database.

Table 1 is the relationship in a relational database (this sentence refers to Miss Yang repeatedly emphasized, never forget)

2 create database, create table, create a column, data additions, constraints are able to write

3 null null: database, a column if you do not specify a value, then the value for null, database of null “ do not know &rdquo, but not say no. So the select null+1 is null, because “ don't know ” 1 or “ don't know”

4 order by data sorting, data packet group by, the having statement is in possession of a

5 types of conversion of cast and convent: CAST ( expression AS data_type), CONVERT ( data_type, expression,[style])

6: multiple result sets and as a result set. Union (removal of duplication, which corresponds to the default application distinct),union all

7 aggregation functions, string and date function: count(),max(),min(),avg();len(),datalength(),lower(),upper(),ltrim(),rtrim(),left(),right(),substring();getdate(),dateadd(),datediff();datepart()

8.ADO.Net

SqlHelper:

public static  class SqlHelper
    {
        private static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
        public static int ExecuteNonQuery(bool isproc ,string sql, params SqlParameter[] parms)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    if (isproc == true)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(parms);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }
        public static object ExecuteScalar(bool isproc ,string sql, params SqlParameter[] parms)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    if (isproc == true)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    if (parms != null)
                    {
                        cmd.Parameters.AddRange(parms);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }
        public static SqlDataReader ExecuteReader(bool isproc,string sql, params SqlParameter[] parms)
        {
            SqlConnection conn = new SqlConnection(connstr);

            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                if (parms != null)
                {
                    cmd.Parameters.AddRange(parms);
                }
                try
                {
                    conn.Open();
                    if (isproc == true)
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                    }
                    return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                }
                catch (Exception)
                {
                    conn.Close();
                    conn.Dispose();
                    throw;
                }
            }
        }
    }

9.app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add connectionString="Data Source=.;Initial Catalog=Itcast2013;Integrated Security=True" name="connstr" />
  </connectionStrings>
</configuration>

10.case function: the equivalent of switch case or if else if... Else

11 sub query: for example: all students query class one one and a senior high school class

select * from student where sClassId in

(select cId from class where cName='High class one one 'or cName=', a senior high school class')

The 12 page. Paging in a stored procedure must be very skilled

create proc usp_GetCustomersByPage
@pageindex int,--The first few pages
@pagesize int,--Each page number
@pagecount int output--The total number of pages
as
begin
    set @pagecount=CEILING((select COUNT(*) from Customers)/(@pagesize*1.0))
    select * from
    (select *,ROW_NUMBER() over(order by customerid) as rn from Customers) as cust
    where cust.rn between (@pagesize-1)*@pagesize+1 and @pagesize*@pageindex
end

13 table join join:inner join, left join, right join: for example

select tClassName,tsname,tsage  from TblClass

inner join TblStudent on tClassId= tSClassId

14 Views: a view is a virtual table; view query cannot use order by, unless the top statement specifies the create view vw_name as; not with begin end. (must be very skilled)

15 variables:

Local variables:

Statement: DECLARE @ variable name   data types [= default values]

Assignment: SET @ variable name = value

--set for general assignment

SELECT @Variable name = value   -- used to query the data and assigned from the table

Output: select, print

Global variables: system definition, @ as a prefix, to master the global variable @@ERROR-- commonly used in the last T-SQL error number

16.if else and while

17 things: it can be successful

Grammar.:

•Begin transaction: BEGIN TRANSACTION

•Transaction commit: COMMIT TRANSACTION

•Transaction rollback: ROLLBACK TRANSACTION

18 storage process: operation method as the database (function). Paging in a stored procedure must master

The 19 indexes, triggers, the cursor

18 database design

The three paradigm 19 database

A simple review, but also by writing code review, review database, and then review foundation strengthening, began to HTML tomorrow.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Lisa at November 14, 2013 - 8:40 PM