Inserting Bulk Data in SQL Server from Text file

There is a simple query to do this bulk insert in to sql server from a text file. The thing is that we have to keep a fully formatted text file with appropriate data’s which are to be inserted.

Here, we have a table to be inserted is,

The query for creating table,

CREATE TABLE Employee
(
FName varchar (100) NOT NULL,
LName varchar (100) NOT NULL,
Email varchar (100) NOT NULL
)

Then we have a text file with the bulk data’s like as follows,

vinoth,kumar,itvinoth83@gmail.com
emp1FName,emp1LName,emp1@company.com
emp2FName,emp2LName,emp2@company.com
emp3FName,emp3LName,emp3@company.com
emp4FName,emp4LName,emp4@company.com
emp5FName,emp5LName,emp5@company.com
emp6FName,emp6LName,emp6@company.com
emp7FName,emp7LName,emp7@company.com
emp8FName,emp8LName,emp8@company.com
emp9FName,emp9LName,emp9@company.com
emp10FName,emp10LName,emp10@company.com
emp11FName,emp11LName,emp11@company.com
emp12FName,emp12LName,emp12@company.com

Now the query for inserting bulk data is,

BULK INSERT Employee FROM 'c:\bulktext.txt' WITH (FIELDTERMINATOR = ',')

Now you can see the data's inserted in table by the select query as follows,

select * from Employee

The following image is being explained the above matters.





...S.VinothkumaR.

Multiple Colored texts in RichTextBox using C#

This article is created for making multiple colored texts in a textbox. By using the SelectionText property in richtextbox we have created a sample application for multiple colored texts in Microsoft’s richtextbox control. Let us see how to make it in below code.

Font font = new Font("Tahoma", 8, FontStyle.Regular);
richTextBox1.SelectionFont = font;
richTextBox1.SelectionColor = Color.Red;
richTextBox1.SelectedText = Environment.NewLine + textBox1.Text;

By using the above code, we can make a multiple colored texts in our richtextbox. This is basically used for doing some chat application. We will have to show the other end of person’s text in different color in chat application. So that we can use this method. And we can create a simple method and calling in every time of showing the text in richtextbox with the parameters of like “textbox1.text, color,..etc”.

For more visit my article in codeproject here with more explanation.

...S.VinothkumaR.

How to sum previous rows value SqlServer?

I needed to write a query for sum the values with the previous row value in current row of a column. So that I have written a single query after a long googled. Here I am trying to give you what I have done.

I have a table with three fields Id, Name and Mark. I have values of all fields like as follows.

Id Name Mark
--- ----- -----
1 aaaa 10
2 bbbb 20
3 cccc 30

Now I wants to get the results set of that table like as

Id Name Mark
--- ----- -----
1 aaaa 10
2 bbbb 30
3 cccc 60

So I need a single select query to do this performance. For that I have written a query using cross join.

Code Snippets for Table and Query

CREATE TABLE [dbo].[Marks]( [Id] [bigint] NOT NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Mark] [bigint] NOT NULL ) ON [PRIMARY]

select * from Marks

select a.Id, a.Name, sum(b.Mark) as Mark
from Marks a cross join Marks b
where b.Id <= a.Id group by a.Id, a.Name

Hence we have done the fetching the result set for sum of the previous rows in sql server.

For more ...

visit here

...S.VinothkumaR.

Multiple rows in a single row with separator in SqlServer

Here is a simple way to make a single row value from multiple rows with a separator in SqlServer.


I have a table like the following…

Table Name: People

select * from people

Now I need the ‘Name’ column values where matching the value of ‘aaaa’ in to a single row. For this I have tried to make a query.

Just use the following query to get the solution.

declare @res varchar(1000)
select @res = coalesce(@res + ',', '') +Name
from people
WHERE Name = 'aaaa'

select @res

See the result…

...S.VinothkumaR.

Simple recursive function for string folding in C#

Here is the sample function of string folding in c#. I just tried bind the body content of a mail in to a label box. For that I need to folding the string content. I have created a method to do this string folding as follows,

if (lblBody.Text.Length > 150)
{
lblBody.Text = lblbodystring(lblBody.Text,150);
}

//Simple recursive function for string folding.
private string lblbodystring(string bodyString, int Length)
{
string retVal = "";
if (bodyString.Length > Length)
{
retVal = bodyString.Substring(0, Length);
retVal = retVal + Environment.NewLine;
retVal = retVal + lblbodystring(bodyString.Substring(Length));
}
else
{
retVal = retVal + Environment.NewLine;
retVal = retVal + bodyString;
}
return retVal;
}


The thing is that we can use textbox with multiline attribute for this kind of process :). But I have used in label. So...

...S.VinothkumaR.

Query for importing data’s from XL to SqlServer DB

Here is a simple query for importing data's from Microsoft Excel sheet to Sql Server database.

Just copy the following code and run in your sql server query window. Before that make XL sheet with the appropriate fields and values and place it in c drive or wherever you wants.

INSERT INTO [dbo].[TableName] ([FieldName1] ,[FieldName2] ,[FieldName3] ,[FieldName4] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\db.xls;Extended Properties=Excel 8.0')...[SheetName$]

that's it...

...S.VinothkumaR.

Silverlight - Introduction

Microsoft Silverlight is a programmable web browser plug-in that enables features such as animation, vector graphics and audio-video playback that characterizes rich Internet applications.

Silverlight provides a retained mode graphics system similar to Windows Presentation Foundation, and integrates multimedia, graphics, animations and interactivity into a single runtime environment. In Silverlight applications, user interfaces are declared in XAML and programmed using a subset of the .NET Framework. XAML can be used for marking up the vector graphics and animations. Textual content created with Silverlight is searchable and indexable by search engines as it is not compiled, but represented as text (XAML).

Releases

1. Silverlight 1.0
2. Silverlight 2
3. Silverlight 3

Tools for Silverlight application

To developing Silverlight application, Microsoft has been released some set of tools. They are,

• Visual Studio 2008 with Service Pack 1
• Silverlight 2 Beta 2 SDK
• Expression Blend 2 July 2008 Preview

All of us known about Visual Studio. Expression Blend is Microsoft's user interface design tool for creating graphical interfaces for web and desktop applications. Blend is writing using the .NET Framework 3.0 and Windows Presentation Foundation (WPF). Microsoft Expression Blend does the same of Adobe Photoshop to edit your images as well as add effects to existing images.

How to create Silverlight applications?

There is a question in our mind raising that “How to create Silverlight applications?” Here is the answer for your question,

1. Use Visual Studio 2008 SP1 to create a new Silverlight project based on the Silverlight 2 Beta 2 Template. Visual Studio will do the necessary steps to add a single HTML page or a single website to host your Silverlight application. This is because Silverlight is a plug-in that renders in the browser.

2. Add your necessary XAML files needed for the user interface. This is similar to the forms that you would add to a Windows Form application.

3. Edit the XAML page using Expression Blend 2 July 2008 preview. Note that Because each page in Silverlight 2 Beta 2 is treated as a user control, only Expression Blend 2 July 2008 preview will be capable of opening these particular XAML files. Any older version of Expression Blend will throw an error message saying that it is unable to open that file type.

4. Switch to Visual Studio 2008 SP1 to write code behind files for the controls and pages that you created in Step 2.

5. Build and compile the application.

6. Host the same in IIS or any web server of your choice. For this demonstration, you will rely on Windows Vista for development and IIS for hosting the application.


...S.VinothkumaR.