write stored procedure,Data Access layer(DAL) for given code

cmd.CommandText = "select [prod_code] as prod_code ,[prod_desc] prod_desc,prod_uom from products where comp_id='" +
                        cpnyid + "' and prod_deleted=0 and prod_cate='" + cfs.singlequotconver(ddl_Cate.Text) + "' order by prod_code";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        try
                        {
                            object[] obj = { "", dr["prod_code"].ToString(), dr["prod_desc"].ToString(), dr["prod_uom"].ToString(),
                                   cfs.getonhand(cfs.sqldateconverion(System.DateTime.Now), dr["prod_code"].ToString(), cpnyid), ""};
                            dataGridViewEx1.Rows.Add(obj);
                        }
                        catch { }

[prod_id] [bigint] IDENTITY(1,1) NOT NULL,
    [comp_id] [int] NULL,
    [prod_code] [nvarchar](30) NULL,
    [prod_desc] [nvarchar](200) NULL,
    [prod_bcode] [nvarchar](50) NULL,
    [prod_pack] [nvarchar](200) NULL,
    [prod_type] [nvarchar](50) NULL,
    [prod_cate] [nvarchar](30) NULL,