SQL Short cuts


Sometimes if a table has many columns, inserting data can be tiresome as you have to write many input parameters from your  application, a smart way to handle this would be to pass the values as xml and use openxml to save data, below is an example:

Define a datatable with all the columns you need to save and add it to a Dataset, something like the one showed below

 DataSet dsAddServerDetails = new DataSet();
DataTable dtServer = new DataTable(“Server”);
dtServer.Columns.Add(“Server_Name”, Type.GetType(“System.String”));
dtServer.Columns.Add(“OS”, Type.GetType(“System.String”));
dtServer.Columns.Add(“Server_Type”, Type.GetType(“System.String”));
dtServer.Columns.Add(“CreatedBy”, Type.GetType(“System.String”));
dtServer.Rows.Add(//Insert logic to add row…);
dsAddServerDetails.Tables.Add(dtServer);

//While Saving
ArrayList arrList = BusinessLayer.GetData(dsAddServerDetails);
if (arrList.Count > 0)
{
 string strServerId = arrList[0].ToString();
}

//BusinessLogic and DataAccess Layer logic

public static class BusinessLayer
    {
        public static ArrayList GetData(DataSet ds)
        {
            int intRetValue;
            ArrayList arrRetVal = new ArrayList();
            try
            {
                arrRetVal = DataAccessLayer.GetData(ds);
            }
            catch (Exception ex)
            {
                throw ex;            }
            return arrRetVal;
        }
}

public static class DataAccessLayer
{
public static ArrayList GetData(DataSet ds)
        {
            int intRetValue;
            ArrayList arrRetVal = new ArrayList();
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand command = db.GetStoredProcCommand(“SaveServer”);
                db.AddInParameter(command, “@ServerDetails”, DbType.Xml, ds.GetXml());
                db.AddOutParameter(command, “@ServerId”, DbType.Int32, 4);

                intRetValue = db.ExecuteNonQuery(command);
                if (intRetValue == -1)
                {
                    int intServerId = Int32.Parse(db.GetParameterValue(command, “@ServerId”).ToString());
                    arrRetVal.Add(intServerId);
                }
            }
            catch (Exception ex)
            {
                throw ex;            }
            return arrRetVal;
        }
}

//Stored Procedure

ALTER PROCEDURE [dbo].[SaveServer]
 — Add the parameters for the stored procedure here
 @ServerDetails  xml,
 @ServerId int OUTPUT
AS
BEGIN
EXEC sp_xml_preparedocument @hDoc OUTPUT,@ServerDetails
BEGIN TRANSACTION
 INSERT INTO tblServer(
 Server_Name,
 OS,
 Server_Type,
 CreatedBy)
 SELECT
 Server_Name,
 OS,
 Server_Type,
 CreatedBy
 FROM OPENXML(@hDoc, ‘NewDataSet/Server’,2)
 WITH (
 Server_Name varchar(50),
 OS varchar(50),
 Server_Type varchar(50),
 CreatedBy datetime)
 SET @ServerId = @@IDENTITY
 SELECT @ServerId
 IF @@ERROR <>0
  BEGIN
   ROLLBACK TRANSACTION
   RAISERROR(‘Error Inserting Server items!’,11,1) with NOWAIT, SETERROR;
   RETURN
  END
 ELSE
  BEGIN

Advertisements

Sometimes it might be required to reset the identity value or Identity seed of a table, to do that write,

DBCC CHECKIDENT (table name, RESEED, 0)

SQL Server 2005 Query Analyzer shortcuts.CTRL+E : Execute Query

F5 : Execute Query

ALT+BREAK : Cancel Query

CTRL+D : Display results in grid format

ALT+F1 : Database object info.

CTRL+F5 : Parse query and check syntax

CTRL+K : Display/hide execution plan

CTRL+L : Display execution plan

CTRL+N : New Query window

CTRL+SHIFT+F : Save results to file

CTRL+Delete : Delete through the end of the line

Some General Shortcuts

CTRL+A : Select All

CTRL+C : Copy

CTRL+V : Paste

CTRL+F : Find

CTRL+P : Print

CTRL+S : Save

CTRL+Z : Undo

CTRL+Y : Redo

CTRL+X : Delete