Custom Search

Ms Sql Server

Any magento developer gathering in Singapore?

Aug 12th

Posted by julbx in Coding

No comments

I don’t know if there are any hardcore magento developers based in singapore but hope to find some and have cool collaboration of what we are capable of doing when it comes to magento.

I really love how magento architected these whole thing, not just in they GUI of the admin panel, but also of the ease of coding, you can call any type of data from the database without using any SQL statement.  The only problem is the lack of proper documentation of codes or maybe I just didn’t find the right documentation.

I still can’t imagine myself coding all this stuff if i work from scratch.

, ,

Displaying Data from Database using LINQ to SQL and C# in Asp.net MVC

Nov 19th

Posted by julbx in ASP.net MVC

No comments

In Asp.net MVC pattern, it is a good practice to Separate Domain Models (Model) in a separate project within the Project Solution,

And create namespaces Abstract, Concrete and Entities within that DomainModel Project.

Abstract is for the Interface class container, Concrete is for the Data Repository and Entities namespace is for the Model itself,

Wel need to add the assembly of System.data.linq to the DomainModel Project to use the Linq to Sql feature of the .net framework.

Now lets start coding,
I are going to pull a collection of name of people in the database

begin by creating a Model Class, call it Person here is the code:

namespace DomainModel.Entities
{
    [Table(Name = "mymvc_TABLE")]
    public class Person
    {
        [Column]public int mymvc_ID { get; set; }
        [Column]public string mymvc_NAME { get; set; }
    }
}

Create the model that will connect with the Database:

public class SqlProductsRepository : IPersonRepository
    {
        private Table<Person> personTable;
        public SqlProductsRepository(string connectionString)
        {
            personTable = (new DataContext(connectionString)).GetTable<Person>();
        }
        public IQueryable<Person> Persons
        {
            get { return personTable; }
        }
    }

Create a controller call it “Home” in the MVC web project,

namespace mymvc.Controllers
{
    public class HomeController : Controller
    {
       
             
        public ViewResult Index()
        {
           
            string connString = @"Server=juliusbacosa_server;Database=mymvc;Trusted_Connection=yes;";
            SqlProductsRepository personRepository = new SqlProductsRepository(connString);

            return View(personRepository.Persons.ToList());
        }

    }
}

now right-click the Index ViewResult Method and add a View…

here is how to display the data in the View

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

    <h2>FROM DATABASE REPOSITORY MVC</h2>
    <ul>
   
   
    <% foreach (var i in Model)
       { %>
       <li> <%= i.mymvc_NAME %> </li>
    <%} %>
   
</ul>
</asp:Content>

,

Sample Code for DataBaseHelper Class… Asp.net/C#

Nov 17th

Posted by julbx in ASP.net Framework

No comments

public class DataBaseHelper : DataAccessBase
    {
 
        private SqlParameter[ ] _parameters;
        public DataBaseHelper( string storedprocedurename )
        {
           
            StoredProcedureName = storedprocedurename;
        }
       
        public void Run( SqlTransaction transaction )
        {
            SqlHelper.ExecuteNonQuery( transaction , CommandType.StoredProcedure , StoredProcedureName , Parameters );
        }

        public void Run( SqlTransaction transaction , SqlParameter[] parameters )
        {
            SqlHelper.ExecuteNonQuery( transaction , CommandType.StoredProcedure , StoredProcedureName , parameters );
        }
   
        public DataSet Run( string connectionstring , SqlParameter[] parameters )
        {
            DataSet ds;
            ds = SqlHelper.ExecuteDataset( connectionstring ,StoredProcedureName , parameters );
            return ds;
        }
       
        public object RunScalar( string connectionstring , SqlParameter[] parameters )
        {
            object obj;
            obj = SqlHelper.ExecuteScalar( connectionstring ,
            StoredProcedureName , parameters );
            return obj;
        }
       
        public object RunScalar( SqlTransaction transaction , SqlParameter[] parameters )
        {
            object obj;
            obj = SqlHelper.ExecuteScalar( transaction , StoredProcedureName ,parameters );
            return obj;
        }
       
        public DataSet Run( string connectionstring )
        {
            DataSet ds;
            ds = SqlHelper.ExecuteDataset( connectionstring , CommandType.StoredProcedure , StoredProcedureName );
            return ds;
        }
       
        public void Run()
        {
            SqlHelper.ExecuteNonQuery( base.ConnectionString ,
            CommandType.StoredProcedure ,
            StoredProcedureName , Parameters );
        }
       
        public SqlDataReader Run( SqlParameter[] parameters )
        {
            SqlDataReader dr;
            dr = SqlHelper.ExecuteReader( base.ConnectionString , CommandType.StoredProcedure , StoredProcedureName , parameters );
            return dr;
        }
       
        public SqlParameter[] Parameters
        {
            get { return _parameters; }
            set { _parameters = value; }
        }
   
    }

sample storedprocedure for Search functionality in T-SQL (MS Sql Server)

Nov 16th

Posted by julbx in Coding

No comments

USE [ecommerce_advanced]
GO
/****** Object:  StoredProcedure [dbo].[SearchCatalog]    julius bacosa : asp.net / developer  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchCatalog]
(@DescriptionLength INT,
@PageNumber TINYINT,
@ProductsPerPage TINYINT,
@HowManyResults SMALLINT OUTPUT,
@AllWords BIT,
@Word1 VARCHAR(15) = NULL,
@Word2 VARCHAR(15) = NULL,
@Word3 VARCHAR(15) = NULL,
@Word4 VARCHAR(15) = NULL,
@Word5 VARCHAR(15) = NULL)
AS
/* Create the table variable that will contain the search results */
DECLARE @Products TABLE
(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,
product_Id INT,
product_Name VARCHAR(50),
product_Price MONEY,
product_Desc VARCHAR(1000),
product_Img VARCHAR(50),
Rank INT)
/* Populate @Products for an any-words search */
IF @AllWords = 0
INSERT INTO @Products
SELECT product_Id, product_Name,product_Price,
SUBSTRING(product_Desc, 1, @DescriptionLength) + ‘…’ AS product_Desc,
product_Img,
3 * dbo.WordCount(@Word1, product_Name) + dbo.WordCount(@Word1, product_Desc) +
3 * dbo.WordCount(@Word2, product_Name) + dbo.WordCount(@Word2, product_Desc) +
3 * dbo.WordCount(@Word3, product_Name) + dbo.WordCount(@Word3, product_Desc) +
3 * dbo.WordCount(@Word4, product_Name) + dbo.WordCount(@Word4, product_Desc) +
3 * dbo.WordCount(@Word5, product_Name) + dbo.WordCount(@Word5, product_Desc)
AS Rank
FROM Product
ORDER BY Rank DESC
/* Populate @Products for an all-words search */
IF @AllWords = 1
INSERT INTO @Products
SELECT product_Id, product_Name,product_Price,
SUBSTRING(product_Desc, 1, @DescriptionLength) +
‘…’ AS product_Desc, product_Img,
(3 * dbo.WordCount(@Word1, product_Name) + dbo.WordCount(@Word1, product_Desc)) *
CASE
WHEN @Word2 IS NULL THEN 1
ELSE 3 * dbo.WordCount(@Word2, product_Name) + dbo.WordCount(@Word2,product_Desc)
END *
CASE
WHEN @Word3 IS NULL THEN 1
ELSE 3 * dbo.WordCount(@Word3, product_Name) + dbo.WordCount(@Word3,product_Desc)
END *
CASE
WHEN @Word4 IS NULL THEN 1
ELSE 3 * dbo.WordCount(@Word4, product_Name) + dbo.WordCount(@Word4,product_Desc)
END *
CASE
WHEN @Word5 IS NULL THEN 1
ELSE 3 * dbo.WordCount(@Word5, product_Name) + dbo.WordCount(@Word5,product_Desc)
END
AS Rank
FROM Product
ORDER BY Rank DESC
/* Save the number of searched products in an output variable */
SELECT @HowManyResults = COUNT(*)
FROM @Products
WHERE Rank > 0
/* Send back the requested products */
SELECT product_Id, product_Name, product_Price,product_Desc, product_Img, Rank
FROM @Products
WHERE Rank > 0
AND RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1
AND @PageNumber * @ProductsPerPage
ORDER BY Rank DESC

Add this Function

USE [ecommerce_advanced]
GO
/****** Object:  UserDefinedFunction [dbo].[WordCount]    Script Date: 11/17/2009 05:07:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[WordCount]
        (@Word VARCHAR(20),
        @Phrase VARCHAR(1000))
        RETURNS SMALLINT
        AS
        BEGIN
       
        /* If @Word or @Phrase is NULL, the function returns 0 */
        IF @Word IS NULL OR @Phrase IS NULL RETURN 0
        /* Calculate and store the SOUNDEX value of the word */

        DECLARE @SoundexWord CHAR(4)
        SELECT @SoundexWord = SOUNDEX(@Word)
        /* Eliminate bogus characters from phrase */
       
        SELECT @Phrase = REPLACE(@Phrase, ‘,’, ‘ ‘)
        SELECT @Phrase = REPLACE(@Phrase, ‘.’, ‘ ‘)
        SELECT @Phrase = REPLACE(@Phrase, ‘!’, ‘ ‘)
        SELECT @Phrase = REPLACE(@Phrase, ‘?’, ‘ ‘)
        SELECT @Phrase = REPLACE(@Phrase, ‘;’, ‘ ‘)
        SELECT @Phrase = REPLACE(@Phrase, ‘-’, ‘ ‘)

        /* Remove trailing spaces. Necessary because LEN doesn’t
        calculate trailing spaces */
       
        SELECT @Phrase = RTRIM(@Phrase)
        /* Check every word in the phrase */
        DECLARE @NextSpacePos SMALLINT
        DECLARE @ExtractedWord VARCHAR(20)
        DECLARE @Matches SMALLINT
       
        /* This variable keeps the number of matches */
        SELECT @Matches = 0
        /* Analyze the phrase word by word */
        WHILE LEN(@Phrase)>0
        BEGIN
        SELECT @NextSpacePos = CHARINDEX(‘ ‘, @Phrase)
        IF @NextSpacePos = 0
        BEGIN
        SELECT @ExtractedWord = @Phrase
        SELECT @Phrase=”
        END
        ELSE
        BEGIN
        SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1)
        SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos)
        END
        /* If there’s a match… */
        IF @SoundexWord = SOUNDEX(@ExtractedWord)
        SELECT @Matches = @Matches + 1
        /* To allow for more matches, use DIFFERENCE instead of SOUNDEX:
        IF DIFFERENCE(@ExtractedWord, @Word) >= 3
        SELECT @Matches = @Matches + 1 */
        END
        /* Return the number of occurences of @Word in @Phrase */
        RETURN @Matches
END

,

Deleting multiple rows in Database Table with comma separated parameter string using T-Sql

Nov 16th

Posted by julbx in ASP.net Framework

No comments

DECLARE @parameter VARCHAR(250)  

SET @parameter = ‘5,6′
DECLARE @SQL varchar(600)

SET @SQL =
‘ DELETE FROM [juliusbacosa].[dbo].[j_TABLE] WHERE j_ID IN (’ + @parameter + ‘)’

EXEC(@SQL)

thanks for Karl Castañeda for contributing…

,

Looping in T-SQL

Nov 16th

Posted by julbx in Coding

No comments

declare @counter int
SET @counter = 0

while @counter < 10

begin
SET @counter = @counter + 1
print ‘The counter is ‘ + cast(@counter AS char)
end

,

Creating StoredProcedures in Ms Sql Server

Nov 16th

Posted by julbx in Coding

No comments

creating StoredProcedude without parameters

CREATE PROCEDURE procedurename
 AS
 SELECT * FROM dbo.TableName

creating StoredProcedure with parameters

CREATE PROCEDURE procedurename
 @parameter1 int,
 @parameter2 string
 AS
 SELECT * FROM dbo.TableName WHERE column1 = @parameter1 AND column2 = @parameter2
, ,

Ms Sql Server Basics

Nov 16th

Posted by julbx in Ms Sql Server

No comments

How to Select:

SELECT * FROM dbo.TableName

How to Insert

INSERT INTO dbo.TableName(column1,column2)
VALUES(‘variable1′ ,‘variable2′)

How to Update

UPDATE dbo.TableName
SET column1 = variable1,
      column2 = variable2
WHERE row_ID = variableID

How to Delete

DELETE FROM dbo.TableName WHERE row_ID = variableID
, ,