Search This Blog

Wednesday, October 31, 2012

How to write Oracle Stored Procedure for INSERT



1. create Table 

Create a table for inserting data.

CREATE TABLE Userdata( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  PASSWORD      VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

2. Write Stored Procedure

Write the  stored procedure, accept  four input  parameters and insert it into table “Userdata”.
CREATE OR REPLACE PROCEDURE insertUserdata(
    p_userid IN DBUSER.USER_ID%TYPE,
    p_username IN DBUSER.USERNAME%TYPE,
    p_Pass IN DBUSER.PASSWORD %TYPE,
    p_date IN DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
 
  INSERT INTO Userdata("USER_ID", "USERNAME", "PASSWORD", "CREATED_DATE") 
  VALUES (p_userid, p_username,p_Pass , p_date);
 
  COMMIT;
 
END;
/
3.Execute from pl/sql like this :
BEGIN
   insertUserdata(123,'nju123','system',SYSDATE);
END;

Tuesday, December 13, 2011

Linq Interview questins and ansers


What is Language Integrated Query (LINQ)? 

LINQ is a set of extensions to .NET Framework that encapsulate language integrated query, set and other 

transformation operations. It extends VB, C# with their language syntax for queries. It also provides class 

libraries which allow a developer to take advantages of these features. 

Difference between LINQ and Stored Procedures:

Stored procedures normally are faster as they have a predictable execution plan. Therefore, if a stored procedure is being executed for the second time, the database gets the cached execution plan to execute the stored procedure.

LINQ supports type safety against stored procedures.

LINQ supports abstraction which allows framework to add additional improvements like multi threading. It’s much simpler and easier to add this support through LINQ instead of stored procedures.

LINQ allows for debugging using .NET debugger, which is not possible in case of stored procedures.

LINQ supports multiple databases against stored procedures which need to be re-written for different databases.

Deploying LINQ based solution is much simpler than a set of stored procedures 
Pros and cons of LINQ (Language-Integrated Query) 

Pros of LINQ:

Supports type safety

Supports abstraction and hence allows developers to extend features such as multi threading.

Easier to deploy

Simpler and easier to learn

Allows for debugging through .NET debugger.

Support for multiple databases

Cons of LINQ:

LINQ needs to process the complete query, which might have a performance impact in case of complex queries

LINQ is generic, whereas stored procedures etc can take full advantage of database features.

If there has been a change, the assembly needs to be recompiled and redeployed. 

Disadvantages of LINQ over stored procedures:

LINQ needs to process the complete query, which might have a performance impact in case of complex queries against stored procedures which only need serialize sproc-name and argument data over the network.

LINQ is generic, whereas stored procedures etc can take full advantage of the complete database features.

If there has been a change, the assembly needs to be recompiled and redeployed whereas stored procedures are much simpler to update.

It’s much easier to restrict access to tables in database using stored procedures and ACL’s than through LINQ. 

Can I use LINQ with databases other than SQL Server? Explain how 

LINQ supports Objects, XML, SQL, Datasets and entities. One can use LINQ with other 

databases through LINQ to Objects or LINQ to Datasets, where the objects and datasets then 

take care of database specific operations and LINQ only needs to deal with those objects, not the 

database operations directly.


What is Linq to SQL Deferred Loading?

(C#) -Deferred Loading is a property of Linq to sql, by default it is set true,

Exam – Let’s have two tables -

Blogs Table (BlogID ,Blog Name,owner) and Post table ( PostID, BlogID, Title, Body)

To find Name and No’s of posts in each blog-

BlogDataContext ctx = new BlogDataContext( );

var query = from b in ctx.Blogs select b;

foreach (Blog b in query)
{
Console.WriteLine("{0} has {1} posts", b.BlogName, b.Posts.Count);
}


Output of queries Produce Blog name with no’s of post, But For each 
Blog 

Looping will be occurs (in case long list of blogs) that’s reduces Overall 

Performance that’s called the Deferred loading. 

Write a Program using Skip and Take operators. How can it beneficial for bulky data accessing on page?

“skip” and “take” Operator used for Paging. Suppose we have Customer table of 100 records. To

 find 10 records by skipping the first 50 records from customer table-

Public void Pagingdatasource ()

{

Var Query =from CusDetails in db.customer skip (50) Take (10)

ObjectDumper.Write(q)

}

Hence The LinQ “ SKIP” operator lets you skip the results you want, and “Take” Operator 

enables you yo select the rest of result . So By Using Skip and Take Operator, You can create 

paging of Specific sequence.

Write a Program for Concat to create one sequence of Data Rows that contains DataTables's 

Data Rows, one after the other.

(C#)

Public void Datasetlinq()
{
var numbersA = TestDS.Tables("NumbersA").AsEnumerable();

var numbersB = TestDS.Tables("NumbersB").AsEnumerable();

var allNumbers = numbersA.Concat(numbersB);

Console.WriteLine("All numbers from both arrays:");

foreach (object n_loopVariable in allNumbers) {

n = n_loopVariable;

Console.WriteLine(n["number"]);
}

}
How can you find average of student marks from student tables (Columns are StudentID, Marks)?

(C#)

Public void LinqToSqlAverage()
{
var query = (from p in db. student. Marks).Average();

Console.WriteLine(q);
}

What is Lambda Expressions? How can we optimize our linq code using this Expression?

Lambda expressions can be considered as a functional superset of anonymous

methods, providing the following additional functionality:

Lambda expressions can infer parameter types, allowing you to omit them.

Lambda expressions can use both statement blocks and expressions as bodies,

allowing for a terser syntax than anonymous methods, whose bodies can

only be statement blocks.

Lambda expressions can participate in type argument inference and

method overload resolution when passed in as arguments. Note: anonymous

methods can also participate in type argument inference (inferred return types).


In C#, a lambda expression is written as a parameter list, followed by the => token,followed by an 

expression or a statement block

What is “OfType” in linq?

public void TypeofExa()

{
Var numbers = {null,1.0,"two", 3,"four",5,"six",7.0 };

var doubles = from n in numbers where n is doublen;

Console.WriteLine("Numbers stored as doubles:");

foreach (object d in doubles) 
{

Console.WriteLine(d);
}
}

Output:

Numbers stored as doubles:
1
7

How can we find Sequence of Items in two different array (same Type) in the same order using linq query?

Public void MatchSequenceLinq()
{
Var wordsA = {"Rahul","ashok","sweta"};

Var wordsB = {"rahul","ashok","sweta"};

var match = wordsA.SequenceEqual(wordsB);

Console.WriteLine("The sequences match: {0}", match);
}

Output Result: True

Differentiate between Conversion Operator “IEnumerable” and “ToDictionary” of linq.

IEnumerable and To Dictionary both are Conversion Operator which are used to solved to 

conversion type Problems.

“AsEnumerable ” operator simply returns the source sequence as an object of type 

IEnumerable<T>. This kind of “conversion on the fly” makes it possible to call the general-

purpose extension methods over source, even if its type has specific implementations of them

Signature-

public static IEnumerable<T> AsEnumerable<T>


this IEnumerable<T> source

);

“ToDictionary ” Conversion Operator is the instance of Dictionary (k,T) .

 The “keySelector ”predicate identifies the key of each item while 

“elementSelector ”, if provided, is used to extract each single item.

Key and elementSelector Predicate can be Used in following ways-

Example-

Public void ToDictionatyExample()



Var scoreRecords=
{
new {Name = "Alice",Score = 50 },

new {Name = "Bob",Score = 40 },

new {Name = "Cathy", Score = 45} };

Var scoreRecordsDict = scoreRecords.ToDictionary(sr => sr.Name);

Console.WriteLine("Bob's score: {0}", scoreRecordsDict("Bob"));
}

Result: Bob's score: { Name = Bob, Score = 40 }

List out the Data Context Functions. Where do we use “SubmitChanges()”?

Create Database ()/Delete database ()

Database Exist ( )

Submit Changes ()

Create Query()

Log

SubmitChanges()- SunbmitChanges Data Context Fuction Used to submit any update to the actual 

database.

Why do we use “Contains” method for strings type functions?

Contains method Used to find all matching records From Given string using 

Matching keywords,

Example-

This Examples returns the Customer Names from Customer tables whose Names have contains 

“Anders”

Public void LinqToSqlStringUsingContains()

{
Var q = From c In db.Customers _

Where c.ContactName.Contains("Anders") _

Select c

ObjectDumper.Write(q)
}
Partition following list of numbers by their remainder when divided by “3”-{Var numbers() = {5, 4, 

1, 3, 9, 8, 6, 7, 2, 0}}

(Vb) 

Public Sub PartitioningExample()

Dim numbers() = {5, 4, 1, 3, 9, 8, 6, 7, 2, 0}

Dim numberGroups = From n In numbers Group n By key = n Mod 3 

Into Group _

Select Remainder = key, NumberGroup = Group

For Each g In numberGroups

Console.WriteLine("Numbers with a remainder of {0} when divided by 

3:", g.Remainder)

For Each n In g.NumberGroup

Console.WriteLine(n)

Next

Next

End Sub

Can you Concat two arrays to create one sequence that contains each array's values, one after the other?

Var ArrayA[]={4,8,7,9,4,7}

Var ArraB[] ={8,2,5,7,8,1}

Answers(vb)

Public Sub ConcatenateTwoArray()

Dim numbersA() = {4, 8, 7, 9, 4, 7}

Dim numbersB() = {8, 2, 5, 7, 8,1}

Dim allNumbers = numbersA.Concat(numbersB)

Console.WriteLine("All numbers from both arrays:")

For Each n In allNumbers

Console.WriteLine(n)

Next

End Sub

Output:{4,8,7,9,4,7,8,2,5,7,8,1}

Write small Program to generate Xml Document from table like (StudentRecord Table) using linq query

Public void CreateXmlDocFromArray()

{

// Now enumerate over the array to build an XElement.

XElement StudentRecords =

new XElement("StudentInfo",

from c in Student 

select new XElement("Student",

new XAttribute("Name", c.Name),

new XElement("RollNo", c.RollNo)
)
);
Console.WriteLine(StudentRecords);
}
What is Quantifiers in reference linq to Dataset?

Quantifier Operators return the Boolean value (either True or false) if some or all the elements in 

a sequence satisfy a condition,

Mainly two Quantifiers in linq –

Any-

All

Examples (vb)

“Any” to determine if any of the words in the array contain the substring

Public Sub ExampleAny()

Dim words() = {"believe", "relief", "receipt", "field"}

Dim iAfterE = words.Any(Function(w) w.Contains("ei"))

Console.WriteLine("There is a word that contains in the list that contains 'ei': {0}", iAfterE)
End Sub

Result:

There is a word that contains in the list that contains 'ei': True


“All “to return a grouped a list of products only for categories that have all of their products in stock

Public Sub Quantifier_All_Exam()

Dim products = GetProductList()

Dim productGroups = From p In products _
Group p By p.Category Into Group _
Where (Group.All(Function(p) p.UnitsInStock > 0)) _
Select Category, ProductGroup = Group

ObjectDumper.Write(productGroups, 1)
End Sub

Friday, October 21, 2011

Inserting and Retrieving images from SQL Server Database using C#

Database
1.Create a database named TestImage
2.create a table called test_table holding two columns id_image(datatype: nvarchar[50]) and pic(datatype: image). 
Front End 


1.create a new project in visual studio
2.select windows application, language c# and name it as TestImage

3. drop two labels, a textbox, a combo box, three buttons and two picture boxes on your win form as shown below

4.
The first picture box will only displays the image you are going to save in your database    providing some id in   the textbox (I used this to just recall the image from database) once      an image is loaded on the clicking      event of Store button we are going to insert that         image with provided ID into the database and the combo box will load itself with all   available IDs in our database. By selecting an id we and clicking Retrieve the respective image will be shown in the second picture box. 
5.double click the button.It will create a button click event.
Here declare a global string
string imagename;
a data adapter
SqlDataAdapter empadap1;
and a dataset
DataSet dset;
you will also require to use System.IO and System.Data.SqlClient
using System.IO;
 
using System.Data.SqlClient;

now we need to create a method to insert our image into the database and second method to retrieve all images. I named the method inserting my image into the database as update() and to retrieve my images I named my method as connection(). Now update() uses filestream to convert the image into binary data since image datatype in SQL Server 2005 use to store binary data in its image datatype. While connection() simply convert that binary data to an image using identical technique for us. Code for both the methods is:
private void updatedata()
{
 
//use filestream object to read the image.
 
//read to the full length of image to a byte array.
 
//add this byte as an oracle parameter and insert it into database.
 
try
{
 
//proceed only when the image has a valid path
 
if (imagename != "")
{
 
FileStream fs;
 
fs = new FileStream(@imagename, FileMode.Open, FileAccess.Read);
 
//a byte array to read the image
 
byte[] picbyte = new byte[fs.Length];
 
fs.Read(picbyte, 0, System.Convert.ToInt32(fs.Length));
 
fs.Close();
 
//open the database using odp.net and insert the data
 
string connstr = @"Data Source=.;Initial Catalog=TestImage;
                Persist Security Info=True;User ID=sa";
 
SqlConnection conn = new SqlConnection(connstr);
 
conn.Open();
 
string query;
 
query = "insert into test_table(id_image,pic) values(" + 
textBox1.Text + "," + " @pic)";
 
SqlParameter picparameter = new SqlParameter();
 
picparameter.SqlDbType = SqlDbType.Image;
 
picparameter.ParameterName = "pic";
 
picparameter.Value = picbyte;
 
SqlCommand cmd = new SqlCommand(query, conn);
 
cmd.Parameters.Add(picparameter);
 
cmd.ExecuteNonQuery();
 
MessageBox.Show("Image Added");
 
cmd.Dispose();
 
conn.Close();
 
conn.Dispose();
 
Connection();
 
}
 
}
 
catch (Exception ex)
{
 
MessageBox.Show(ex.Message);
 
}
 
}
 
//----------------------------------------
 
private void Connection()
{
 
//connect to the database and table
 
//selecting all the columns
 
//adding the name column alone to the combobox
 
try
{
 
string connstr = @"Data Source=.;Initial Catalog=TestImage;

            Persist Security Info=True;User ID=sa";
 
SqlConnection conn = new SqlConnection(connstr);
 
conn.Open();
 
empadap1 = new SqlDataAdapter();
 
empadap1.SelectCommand = new SqlCommand("SELECT * FROM test_table"

            , conn);
 
dset = new DataSet("dset");
 
empadap1.Fill(dset);
 
DataTable dtable;
 
dtable = dset.Tables[0];
 
comboBox1.Items.Clear();
 
foreach (DataRow drow in dtable.Rows)
{
 
comboBox1.Items.Add(drow[0].ToString());
 
comboBox1.SelectedIndex = 0;
 
}
 
}
 
catch (Exception ex)
{
 
MessageBox.Show(ex.Message);
 
}
 
}
now double click the Load button and write the code lines:

try
{
 
FileDialog fldlg = new OpenFileDialog();
 
//specify your own initial directory
 
fldlg.InitialDirectory = @":D\";
 
//this will allow only those file extensions to be added
 
fldlg.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif";
 
if (fldlg.ShowDialog() == DialogResult.OK)
{
 
imagename = fldlg.FileName;
 
Bitmap newimg = new Bitmap(imagename);
 
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
 
pictureBox1.Image = (Image)newimg;
 
}
 
fldlg = null;
 
}
 
catch (System.ArgumentException ae)
{
 
imagename = " ";
 
MessageBox.Show(ae.Message.ToString());
 
}
 
catch (Exception ex)
{
 
MessageBox.Show(ex.Message.ToString());
 
}
Now double click Store button and call the update () method to insert selected image into the database.
updatedata();
the update method itself calls connection() therefore combo box will be filled with IDs of images exits in database, one can use the connection method in anyway the person likes.
Finally double click Retrieve button and write
DataTable dataTable = dset.Tables[0];
 
//if there is an already an image in picturebox, then delete it
 
if (pictureBox2.Image != null)
{
 
pictureBox2.Image.Dispose();
 
}
 
//using filestream object write the column as bytes and store 
        it as an image
 
FileStream FS1 = new FileStream("image.jpg", FileMode.Create);
 
foreach (DataRow dataRow in dataTable.Rows)
{
 
if (dataRow[0].ToString() == comboBox1.SelectedItem.ToString())
{
 
byte[] blob = (byte[])dataRow[1];
 
FS1.Write(blob, 0, blob.Length);
 
FS1.Close();
 
FS1 = null;
 
pictureBox2.Image = Image.FromFile("image.jpg");
 
pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage;
 
pictureBox2.Refresh();
 
}
 
}