Sql Schema

What is Schema in SQL Server 2005? Explain its properties with example?

A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement.

Properties

• Ownership of schemas and schema-scoped securables is transferable.
• Objects can be moved between schemas
• A single schema can contain objects owned by multiple database users.
• Multiple database users can share a single default schema.
• Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.
• A schema can be owned by any database principal. This includes roles and application roles.
• A database user can be dropped without dropping objects in a corresponding schema.

Create database SQL2k5

Use SQL2k5

– Created Schema Employee –

Create Schema Employee

– Created table in Employee schema –

Create Table Employee.EmpInfo

(

EmpNo int Primary Key identity(1,1),

EmpName varchar(20)

)

– data insertion –

Insert Into Employee.Empinfo Values(‘Jshah-3′)

– Data Selection –

Select * From Employee.Empinfo

– Created another schema HR –

Create Schema HR

– Transfer Objects between Schemas –

ALTER SCHEMA HR

TRANSFER Employee.Empinfo

– Assigning Permission to Schema –

GRANT SELECT ON SCHEMA::HR TO Jshah

Encrypt during the insertion of the password in to the database

MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
byte[] hashedBytes = null;
UTF8Encoding encoder = new UTF8Encoding();

hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));

now 
hashedBytes  value can be directly be inserted in to the database

Encryption to get the uid and pwd from database during login

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Security.Cryptography; 

public int Login(string UserName, string password)
{
 int result = 0;

 try {
  //Encrypt Password
  MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
  byte[] hashedDataBytes = null;
  UTF8Encoding encoder = new UTF8Encoding();

  hashedDataBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));

  //If password = "3hotminds" Then
  //    bAdminLogin = True

  //    Return GetUserId(UserName)
  //End If

  result = Authenticate(UserName, password);

  bAdminLogin = false;

  if (result == 1) {
   return GetUserId(UserName);
  }
 } catch (Exception Exception) {
  return -1;
 }

 return result;
}

public int Authenticate(string sUserName, string sPassword)
{
 int result = -1;
 FileOnDatabase db = new FileOnDatabase();
 SqlParameter[] @params = new SqlParameter[3];

 MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
 byte[] hashedDataBytes = null;
 UTF8Encoding encoder = new UTF8Encoding();

 hashedDataBytes = md5Hasher.ComputeHash(encoder.GetBytes(sPassword));
 try {
  db.ConnectionString = sConnectionString;

  @params(0) = db.MakeParameter("@Username", sUserName);
  @params(1) = db.MakeParameter("@Password", SqlDbType.Binary, 16);
  @params(1).Value = hashedDataBytes;

  @params(2) = db.MakeParameter("@Result", ParameterDirection.Output, result);

  db.RunProcedure("Authenticate", @params);
  result = @params(2).Value;
 } catch (Exception e) {
  _errorMessage = "Unable to Add the permissions [" + e.Message + "]";
  result = -1;
 } finally {
  db = null;
 }

 return result;
}

Here in database uid in varchar type and password is in binary type .

Sql transaction

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.
Users can group two or more Transact-SQL statements into a single transaction using the following statements:

• Begin Transaction
• Rollback Transaction
• Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.
Here is an example of a transaction :

USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.


http://www.codeproject.com/KB/database/sqlservertransactions.aspx