Well guys, in my previous articles I have given a preliminary description about the Stored Procedure and also had given a word that I will be dealing with it in more detail in my upcoming articles. As the saying goes “Once a word has been allowed to escape, it cannot be recalled”, and so I am back here with advanced stored procedure concepts.
Insertion using Stored Procedure’s Input Parameters
Let’s get on by creating a table name tblEmployee with the following fields as below:
Fig (a)
Now let’s create a Stored Procedure for insertion. Open a new Query window and type the below stored procedure.
Stored procedure named usp_insert gets created into database.
Execute Stored Procedure?? Yeah!! The below syntax is used for executing the above created stored procedure.
Here EmpId Column inside the table is a primary key with identity being set to [1].
Updating table - Stored Procedure using Input Parameters
Let’s have a glance over the fig (a) above. Now we are going to use the same table for creating the stored procedure for updating the table.
Portray the same as below in a new Query Window.
Stored procedure named usp_Update gets created into database.
Execute Stored Procedure?? Before executing let us see the records inside the table tblEmployee.
Deleting Records - Stored Procedure using Input Parameters
The last step in any CRUD operation is deleting the records in the table. Let’s create a stored procedure named usp_Delete
Execute Stored Procedure??
Stored Procedure using Output variables will be explained in my upcoming articles!!
(The author, Tauseef.M, is a Software Engineer at Binary Spectrum).
Using SMO for Dynamically Creating Stored Procedure and Database Tables in c#.net
-Tauseef.M
According to me “Giving a deadly desired storm to your brain yields in an ‘invention ‘.
What is SMO??
Server Management Object (SMO) is Microsoft® .NET Framework library of objects that let you manage one or more database servers. SMO can be used to examine a database’s objects and to perform a variety of administrative tasks like backing up and restoring databases, and issuing Data Definition Language (DDL) commands. Using SQL SMO you can also connect to SQL Server, iterate through a collection of database objects and perform a variety of tasks against them. Let me demonstrate the use of SMO for dynamically creating of Stored Procedures and Database Tables.
For start up, let us first reference the appropriate assemblies that use SMO.
The two main DLLs you must reference are:
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.ConnectionInfo.dll
You will also need the following assembly for creating indexes for primary key: Microsoft.SqlServer.SqlEnum.dll
After adding the above assemblies let’s begin Dynamic Creation of Stored Procedure and Database Tables.
Dynamically Creating Database Tables
Below is a simple Code that creates the database table known as tblEmployee in my personal database Employee with a primary key and identity on the ID column.
//Establish Conection SqlConnection conn = newSqlConnection ("Server=<servername>;DATABASE=<databasename>;uid=<userid>;pwd=<pwd>"); //Create Server instance Server server = newServer(newServerConnection(conn)); //Create Database Instance from existing databases Database database = server.Databases["Employee"]; //Create Table Microsoft.SqlServer.Management.Smo.Table table = new Microsoft.SqlServer.Management.Smo.Table(database, "tblEmployee"); //Create Column "ID" as Primary key Column IDcolumn = newColumn(table, "ID"); IDcolumn.DataType = DataType.Int; //Specify the datatype IDcolumn.Nullable = false;//Allow Nulls=False IDcolumn.Identity = true;//Set Identity=True IDcolumn.IdentitySeed = 1;//Set Identity to start from 0 IDcolumn.IdentityIncrement = 1; //Increment identity value by 1 //Create Column "Title" Column TitleColumn = newColumn(table, "Title"); TitleColumn.DataType = DataType.VarChar(50); TitleColumn.Nullable=true; //Add Columns to the "table" table.Columns.Add(IDcolumn); table.Columns.Add(TitleColumn); //Create indexfor primary key column "ID" Index prIndex = newIndex(table, "PK_ID"); //specify as primary key prIndex.IndexKeyType = IndexKeyType.DriPrimaryKey; //Assign the column "ID" to the created Index prIndex.IndexedColumns.Add(newIndexedColumn(prIndex,"ID")); //Add the new Index to the table table.Indexes.Add(prIndex); //PHYSICALLY create the table in the database table.Create();
Response.Write("Table Created Sucessfully...");
Dynamically Creating StoredProcedures Below is a simple Code that creates the Stored Procedure known as “dsp_created “in my personal database Employee with @cityid being the input variable.
//Establish Conection SqlConnection conn = newSqlConnection ("Server=<servername>;DATABASE=<databasename>;uid=<userid>;pwd=<pwd>"); //Create Server instance Server server = newServer(newServerConnection(db)); //Select the database where stored procedure needs to be added Database dsb = server.Databases["Employee"];
//Create a Stored Procedure named "dsp_created" in "Employee" StoredProcedure sp = newStoredProcedure(dsb, "dsp_created"); sp.TextMode = false; //set TextMode as false sp.AnsiNullsStatus = false; //set AnsiNullsStatus as false sp.QuotedIdentifierStatus = false;
//Add an input parameter "@cityid" to the Stored Procedure StoredProcedureParameter idparam = new StoredProcedureParameter(sp, "@cityid", DataType.Int); sp.Parameters.Add(idparam); //The Sql Statement sp.TextBody = "Select *from tblCity where Citycode= @cityid"; //PHYSICALLY create Stored Procedure sp.Create();
Response.Write("Stored Procedure Created Sucessfully....");
Dynamically deletion of stored procedure is also very simple, which will be explained later.
(The author, Tauseef.M, is a Software Engineer at Binary Spectrum.)
We can create an ongoing task in MS Project by creating a “Hammock Task”. Hammock task duration changes automatically when the project finish date is changed.
Steps for creating a Hammock Task: 1.Click on Project start date cell, the start date cell will be highlighted. 2.On the edit menu, click copy (cell) 3.Click on the ‘ongoing task’ start date, the start date cell will be highlighted. 4.On edit menu, click ‘Paste Special’ and click ‘Paste Link’. 5.Click on Project end date cell, the end date cell will be highlighted. 6.On the edit menu, click copy (cell) 7.Click on the ‘ongoing task’ end date, the end date cell will be highlighted. 8.On edit menu, click ‘Paste Special’ and click ‘Paste Link’.
I had to upload pin codes in excel to database. After procrastinating on net for considerable time, I decided to find a quick solution... :) Though I am aware of available excel api’s, I dint want to spend time in reading api docs for breaking a thin twig. This method can be followed if you want to export a single column from excel to a table.
Steps:
So i copied the pin code column from the excel and pasted on to a notepad
Save the file as ok.txt
then I wrote program that will read a the file line by line and a simple string operation
The program will generate a Sql script and will also execute the script and will insert the data into database.
"*********Sample output*********"
insert into tblpincode_zone(pincodes,zone)values(834001,9);
insert into tblpincode_zone(pincodes,zone)values(834002,9);
FileInputStream fstream = new FileInputStream("D:/ok.txt");
// Get the object of DataInputStream
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;
//Read File Line By Line
while ((strLine = br.readLine()) != null) {
pst=con.createStatement();
// Print the content on the console
System.out.println ("insert into tblpincode_zone(pincodes,zone)values("+strLine+",9);"); str="insert into tblpincode_zone(pincodes,zone)values("+strLine+",9);";
pst.executeUpdate(str);
}
//Close the input stream
in.close();
}catch (Exception e){//Catch exception if any
System.err.println("Error: " + e.getMessage());
}
}
}
(The author, Anjana.M, is a Software Engineer at Binary Spectrum).
The interest of developer will always be towards learning new technologies. It’s secondary that whether in real-time you are going to use it in or not. One of the most popular and fastest growing technologies that I came across is Ruby on Rails (RoR).
Ruby on Rails was developed by David Heinemeier Hansson from his work on BaseCamp; a project management tool, which offers to-do lists, wiki-style web-based text documents, milestone management, file sharing, time tracking, and a messaging system.
Like many web frameworks, Rails uses the Model View Controller (MVC) architecture to organize applications.
Features of Ruby:
Each and Everything is Object: Even numbers in ruby are treated as object. Looking at the example, what output you can expect from this?
8.times {print “Guess what”} its prints Guess what 8 times.
Powerful blocks: Code blocks can be passed as parameter to method. Following example prints out all the elements in an array
jArray.each{|element| print element}
Return is optional in Methods: In ruby return statement is optional in methods. The value of the last expression becomes the return value of that method. For example:
deftestReturnn = 2 * 3en
Parallel Assignment in Ruby: We can change the multiple variables in one assignment statement. Swapping of two variables is the good example for it.n1 = 1
n2 = 3
n1, n2 = n2,n1
Everything in Ruby is open: Including the built-in classes, in ruby, additional methods can be added to the classes even at run-time also. For example: FixNum is the build in Data Type for all to which I added the method called old.
class Fixnum
def oldreturn self-1
end
end
8.old # prints 7
(The author, Jagadish.M, is a Software Engineer at Binary Spectrum).
Recently, while I visited few sites, I was caught by this very interesting tale of ATG. I came across their dynamic capabilities and thought of sharing it and creating awareness to all.
ATG Technologies: In the line of ecommerce, ATG is the technology that is growing faster than any other technologies currently. ATG (Art Technology Group, Inc., NASDAQ: ARTG) develops software and delivers e-commerce and Web marketing solutions that many global brands utilize to empower their e-commerce Web sites. ATG's ecosystem of Complementary Software Providers extends the ATG Commerce suite with unique and innovative capabilities like dynamic imaging, ratings and reviews, and payment.
ATG Commerce Suite: ATG Commerce Suite provides a solid, stable, yet highly flexible and agile foundation for the highest volume e-commerce sites. With itsdynamic customization and targeting capabilities , ATG lets you drive cross-channel sales and marketing from a single platform — even for multiple brands, and across multiple geographies.
ATG provides direct control over user-friendly website management tools to various marketers and merchandisers. You can choose your delivery model as licensed e-commerce software, or a fully hosted on demand solution.
To learn more, you can visit http://www.atg.com and discover the comprehensive feature list they provide.
(The author, Jagadish, is a Software Engineer at Binary Spectrum)
We often see programmers jump to their seats and start coding as soon as they just hear any requirement. They may be able to complete the task, but may actually be lost in fixing the logical errors for a long time.
I have followed this approach that I am stating below, and yes, it worked well. I also believe that this is a stress free approach towards developing and implementing the requirement.
This is just doing object modeling in a well planned way. This surely can be achieved with UML tools. But paper work at times does prove to be quicker.
Let me know your thoughts on it and looking forward to know your development approaches as well.
Here are the 10 steps for a planned approach:
Step-1: Get requirement
Step-2: Don’t start development as soon as you get the requirement
Step-3: Lets follow the divide and rule approach - Divide the requirement into subtasks! We may have a list of subtasks
Step-4: Rule Them -Recognize the dependent task
Step-5: Create a class with requirement's name
Step-6: Make the subtasks as methods in the class
Step-7: Recognize the Inputs and Outputs for each method
Step-8: Build the DBQUERY
Step-9: Include the logic
Step-10: Unit test with SOP for each method in the class.
(The author, Anjana.M, is a Software Engineer at Binary Spectrum)
