.NET Tuts By Chirag Patel

SQL SERVER – Split Comma Separated List without Using a Function

2015-12-16T17:10:00.006+05:30

Create table “tblTest” in sql server

This is how data looks like in the table

With the above data available, they want it split into individual values. Searching for possible solutions, here is the solution query I came up with:

DECLARE @temp TABLE(

ID INT,

Name Varchar(MAX)

)

INSERT INTO @temp (ID, Name)

SELECT ID, LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) As Name

FROM ( SELECT ID, CAST('<XMLRoot><RowData>' + REPLACE(Name,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x FROM tblTest) tblTest CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

SELECT * FROM @temp WHERE Name = 'kinjal'

Output looks like below.

Hope this would help you in your project. Let me know if you are able to find a different solution to the same problem. The best way to learn is to learn from each other.

Auto Generate Paired Number

2015-02-16T15:48:00.004+05:30

Read Excel files data and insert in to SQL Table

2014-08-04T14:26:00.005+05:30

Create “Book1.xls” file in that add Header like “ProductID” and “vcSerialNo”

Imports System.IO

Imports System.Data.OleDb

Private Sub GetFile()

Dim conString As String = ""

Dim strFileType As String = Path.GetExtension(txtpath.Text).ToLower()

Dim xlspath As String = txtpath.Text

'Connection String to Excel Workbook

If strFileType.Trim() = ".xls" Then

conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & xlspath & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

ElseIf strFileType.Trim() = ".xlsx" Then

conString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1""", xlspath)

End If

Dim oledbConnection As New OleDb.OleDbConnection(conString)

Try

Dim query As String = "SELECT [ProductID],[vcSerialNo] FROM [Sheet1$]"

If oledbConnection.State = ConnectionState.Closed Then

oledbConnection.Open()

End If

Dim cmd As New OleDbCommand(query, oledbConnection)

Dim ds As New DataSet()

Dim adptr As New OleDbDataAdapter(cmd)

adptr.Fill(ds)

DataGridView1.DataSource = ds.Tables(0)

Dim i As Integer

For i = 0 To ds.Tables(0).Rows.Count - 1

Dim ProductID = ds.Tables(0).Rows(i)(0).ToString()

Dim vcSerialNo = ds.Tables(0).Rows(i)(1).ToString()

Dim sqlConnection As New SqlClient.SqlConnection

Dim sqlCommand As New SqlClient.SqlCommand()

Try

sqlConnection.ConnectionString = "Data Source=" + txtDBSource.Text + ";Initial Catalog=" + txtDBName.Text + ";User Id=" + txtUserName.Text + ";Password=" + txtPW.Text + ";Integrated Security=False;Connect Timeout=30;"

If sqlConnection.State = ConnectionState.Closed Then

sqlConnection.Open()

End If

sqlCommand.Connection = sqlConnection

sqlCommand = New SqlClient.SqlCommand("INSERT INTO tProdSerialNoInventory(lProdId, vcSerialNo, bIsAvailable, dtUpdDT, lUpdUserId) VALUES ('" & ProductID & "','" & vcSerialNo & "', 1, '" & Date.Now() & "', 0)", sqlConnection)

sqlCommand.ExecuteNonQuery()

Catch ex As Exception

Finally

If sqlConnection.State = ConnectionState.Closed Then

sqlConnection.Close()

End If

End Try

Catch ex As Exception

MessageBox.Show("Reocrds not found")

Finally

oledbConnection.Close()

End Try

End Sub

Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click

If txtDBSource.Text = "" Or txtDBName.Text = "" Or txtUserName.Text = "" Or txtPW.Text = "" Then

MessageBox.Show("Check Configuration")

Return

Else

Dim MyFolderBrowser As New System.Windows.Forms.OpenFileDialog

MyFolderBrowser.Title = "Select the ExcelSheet"

MyFolderBrowser.Filter = "All files (*.*)|*.*xls"

If MyFolderBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then

txtpath.Text = MyFolderBrowser.FileName

GetFile()

End If

End Sub

Demo Download

Client side HTML encoding

2014-04-23T12:09:00.002+05:30

<script src="JS/jquery-1.6.2.min.js" type="text/javascript"></script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <script type="text/javascript" >

        function validateTxt() {
            var div = document.getElementById("Div1");

            var str = "<h1> Hello.. </h1><br /> <b><i> Chirag Here :) </i></b>";

            str = this.replaceAll(str, "&", "&");
            str = this.replaceAll(str, "<", "<");
            str = this.replaceAll(str, ">", ">");
            str = this.replaceAll(str, " ", " ");
            var str4Div = str;
            div.innerHTML = str4Div;
        }

        function replaceAll(str, subStr, newStr) {
            var offset = 0;
            var index = str.indexOf(subStr);
            while (index != -1) {

                 str = str.substr(0, index) + newStr + str.substr(index + subStr.length);
                 offset = index + newStr.length;
                index = str.indexOf(subStr, offset);
            }
            return str;
         }

        $(document).ready(function() {
            validateTxt();
        });

    </script>
</head>

<body>
    <form id="form1" runat="server">
    <div id="Div1" onload="javascript:validateTxt();" >
    </div>
    </form>
</body>
</html>

OutPut::

Demo Download

XML banner fader in jQuery

2014-01-03T15:13:00.002+05:30

Here’s the XML code.


<gallery fade="500" ontime="5000">
    <picture src="banners/banner1.jpg" href="http://www.google.com" target="_blank"></picture>
    <picture src="banners/banner2.jpg" href="http://www.yahoo.com" target="_blank"></picture>
    <picture src="banners/banner3.jpg" href="http://www.bing.com" target="_blank"></picture></gallery>

The HTML code is very simple


<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>

<div id="banner">
           <a id="banner_href" href="#">
           <img id="banner_image" src="banners/banner1.jpg" alt="Banner Image" ></a>
</div>

JavaScript.jd Document

$(document).ready(function(){
    xmldata=new Array();         //initialise array for XML data
    fade=0;                                //time taken for transitions
    ontime=0;                           //time between transitions
    changenum=0;                    //current banner
    changetot=0;                      //total banners

    $("#banner").fadeTo(1,0);        //fade out the fallback banner

    $.ajax({                                            //get the XML data
              url: "banners.xml",                //URL to get the data from
              success: function(data) {    //if we succeed, set everything up. We don't expect failure.
                $(data).find("picture").each(function()
                 {
                    xmldata.push($(this));                //add item into array
                });
                changetot=xmldata.length;                               //get total length
                 ontime=eval($(data).find("gallery").attr("ontime"));    //get fade time
                fade=eval($(data).find("gallery").attr("fade"));       //get time between fades
                 change_banner(changenum);                               //change banner for first banner
                 }
        });
});

function change_banner(){
    data=xmldata[changenum];            //get current banner XML object
    img=$(data).attr("src");                    //retrieve variables
    href=$(data).attr("href");
    target=$(data).attr("target");

    $("#banner_href").attr("href",href);        //change variables on HTML
    $("#banner_href").attr("target",target);
    $("#banner_image").attr("src",img);

    //increment banner value, this is executed BEFORE the callback as we already have the variables.
    changenum++;
    if(changenum>=changetot)      //check for banner value bounds
        changenum=0;

    cache = document.createElement('img');            //create an image
    //pre-cache next image (means the browser will load it instantly)            cache.src = $(xmldata[changenum]).attr("src");
   //fade banner in, then wait, then fade out and call back to this function.
   $("#banner").fadeTo(fade,1).fadeTo(ontime,1).fadeTo(fade,0,change_banner);
}

Move Selected Gridview Rows to Another Gridview in Asp.net

2013-12-26T17:04:00.001+05:30

To implement this first we need to write the code in "aspx" page like this::

<html xmlns="http://www.w3.org/1999/xhtml">
<head><title>Move selected gridview rows to another gridview in Asp.net</title></head>
<body>
<form id="form1" runat="server"><div>
<asp:GridView ID="gvDetails" AutoGenerateColumns="false" CellPadding="5" runat="server">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="chkSelect" runat="server" AutoPostBack="true" OnCheckedChanged="chkSelect_CheckChanged" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField HeaderText="UserId" DataField="UserId" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Education" DataField="Education" />
<asp:BoundField HeaderText="Location" DataField="Location" />
</Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
<br /><b>Second Gridview Data</b>
<asp:GridView ID="gvTranferRows" AutoGenerateColumns="false" CellPadding="5" runat="server" EmptyDataText="No Records Found">
<Columns>
<asp:BoundField HeaderText="UserId" DataField="UserId" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Education" DataField="Education" />
<asp:BoundField HeaderText="Location" DataField="Location" /></Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" /></asp:GridView>
</div></form>
</body>
</html>

========================================================

C# Code

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridview();
BindSecondGrid();
}
}
protected void BindGridview()
{
DataTable dt = new DataTable();
dt.Columns.Add("UserId", typeof(Int32));
dt.Columns.Add("UserName", typeof(string));
dt.Columns.Add("Education", typeof(string));
dt.Columns.Add("Location", typeof(string));
DataRow dtrow = dt.NewRow(); // Create New Row
dtrow["UserId"] = 1;
dtrow["UserName"] = "ChiragPatel";
dtrow["Education"] = "M.sc IT";
dtrow["Location"] = "Kaalol";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow();
dtrow["UserId"] = 2;
dtrow["UserName"] = "ChinkiPandviya";
dtrow["Education"] = "BCA";
dtrow["Location"] = "Vadodara";
dt.Rows.Add(dtrow);
dtrow = dt.NewRow();
dtrow["UserId"] = 3;
dtrow["UserName"] = "KinZy";
dtrow["Education"] = "BCA";
dtrow["Location"] = "Vadodara";
dt.Rows.Add(dtrow);
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
protected void chkSelect_CheckChanged(object sender, EventArgs e)
{
GetSelectedRows();
BindSecondGrid();
}
protected void BindSecondGrid()
{
DataTable dt = (DataTable)ViewState["GetRecords"];
gvTranferRows.DataSource = dt;
gvTranferRows.DataBind();
}
private void GetSelectedRows()
{
DataTable dt;
if (ViewState["GetRecords"] != null)
dt = (DataTable)ViewState["GetRecords"];
else
dt = CreateTable();
for (int i = 0; i < gvDetails.Rows.Count; i++)
{
CheckBox chk = (CheckBox)gvDetails.Rows[i].Cells[0].FindControl("chkSelect");
if (chk.Checked)
{
dt = AddGridRow(gvDetails.Rows[i], dt);
}
else
{
dt = RemoveRow(gvDetails.Rows[i], dt);
}
}
ViewState["GetRecords"] = dt;
}
private DataTable CreateTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("UserId");
dt.Columns.Add("UserName");
dt.Columns.Add("Education");
dt.Columns.Add("Location");
dt.AcceptChanges();
return dt;
}
private DataTable AddGridRow(GridViewRow gvRow, DataTable dt)
{
DataRow[] dr = dt.Select("UserId = '" + gvRow.Cells[1].Text + "'");
if (dr.Length <= 0)
{
dt.Rows.Add();
int rowscount = dt.Rows.Count - 1;
dt.Rows[rowscount]["UserId"] = gvRow.Cells[1].Text;
dt.Rows[rowscount]["UserName"] = gvRow.Cells[2].Text;
dt.Rows[rowscount]["Education"] = gvRow.Cells[3].Text;
dt.Rows[rowscount]["Location"] = gvRow.Cells[4].Text;
dt.AcceptChanges();
}
return dt;
}
private DataTable RemoveRow(GridViewRow gvRow, DataTable dt)
{
DataRow[] dr = dt.Select("UserId = '" + gvRow.Cells[1].Text + "'");
if (dr.Length > 0)
{
dt.Rows.Remove(dr[0]);
dt.AcceptChanges();
}
return dt;
}
======================================================

Demo DownLoad

360 degree spin onMouseover and onMouseout

2013-12-17T17:20:00.002+05:30

------------------------------------------------------------------
<p id="socialicons">
    <a href="http://dot-nettuts.blogspot.in/"><img border="0" src="img/rss.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/delicious.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/facebook.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/twitter.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/yahoo.png" /></a>
</p>
<p><b>60 degree spin onMouseover and onMouseout</b></p>
<p id="socialicons2">
   <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/rss.png" /></a>
   <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/delicious.png" /></a>
   <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/facebook.png" /></a>
   <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/twitter.png" /></a>
   <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/yahoo.png" /></a>
</p>
<p><b>-360 degree spin onMouseover ONLY</b></p>
<p id="socialicons3">
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/rss.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/delicious.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/facebook.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/twitter.png" /></a>
    <a href="http://dot-nettuts.blogspot.in"><img border="0" src="img/yahoo.png" /></a>
</p>
------------------------------------------------------------------

p#socialicons img

{

/* 1st set of icons. Rotate them 360deg onmouseover and out */

-moz-transition: all 0.8s ease-in-out;

-webkit-transition: all 0.8s ease-in-out;

-o-transition: all 0.8s ease-in-out;

-ms-transition: all 0.8s ease-in-out;

transition: all 0.8s ease-in-out;

}

p#socialicons img:hover

{

-moz-transform: rotate(360deg);

-webkit-transform: rotate(360deg);

-o-transform: rotate(360deg);

-ms-transform: rotate(360deg);

transform: rotate(360deg);

}

p#socialicons2 img

{

/* 2nd set of icons. Rotate them 60deg onmouseover and out */

-moz-transition: all 0.2s ease-in-out;

-webkit-transition: all 0.2s ease-in-out;

-o-transition: all 0.2s ease-in-out;

-ms-transition: all 0.2s ease-in-out;

transition: all 0.2s ease-in-out;

}

p#socialicons2 img:hover

{

-moz-transform: rotate(70deg);

-webkit-transform: rotate(70deg);

-o-transform: rotate(70deg);

-ms-transform: rotate(70deg);

transform: rotate(70deg);

}

p#socialicons3 img

{

/* 3rd set of icons. Rotate them -360deg onmouseover ONLY. Note where the "transition prop is added */

}

p#socialicons3 img:hover

{

-moz-transition: all 0.5s ease-in-out;

-webkit-transition: all 0.5s ease-in-out;

-o-transition: all 0.5s ease-in-out;

-ms-transition: all 0.5s ease-in-out;

transition: all 0.5s ease-in-out;

-moz-transform: rotate(-360deg);

-webkit-transform: rotate(-360deg);

-o-transform: rotate(-360deg);

-ms-transform: rotate(-360deg);

transform: rotate(-360deg);

}

</style>
-------------------------------------------------

How to fit Windows Form to any screen resolution?

2013-11-12T16:20:00.002+05:30

" size = Screen.PrimaryScreen.WorkingArea.Size "
this line can only modify size of window-form

(it will not effect controls' locations & size).

for fit your form in any window use

"windowstate = FormWindowState.maximized"

Open Excel workbooks in VB.NET Solutions

2013-07-06T12:11:00.002+05:30

.NET WebBrowser Control

The following code is used to achieve the above:

Zip and Extract files in .NET

2013-07-06T12:02:00.006+05:30

You must reference the assembly in your project-- System.IO.Compression.FileSystem

using System;
using System.IO;
using System.IO.Compression;

namespace ConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            string startPath = @"c:\example\start";
            string zipPath = @"c:\example\result.zip";
            string extractPath = @"c:\example\extract";

            ZipFile.CreateFromDirectory(startPath, zipPath);

            ZipFile.ExtractToDirectory(zipPath, extractPath);
        }
    }
}

ProductVersion update for MSI Projects

2013-03-07T09:59:00.003+05:30

The script is written in VBScript and is quite simple.

read the project file passed in the command line, e.g., Setup.vdproj
backup the original file, just in case something goes wrong ;-)
find the version number
increment the version number
replace the version number with the new value
replace the product code with a new GUID
replace the package code with a new GUID
write the updated project file back to disk

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' Set version number of an MSI setup project from assembly
'' and update relevant GUIDs
'' Chirag Patel /03/03/2013

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

set args = wscript.arguments
if args.count < 2 then wscript.quit 1

set fso = CreateObject("Scripting.FileSystemObject")
set regex = new regexp
regex.global = true

'read current version from Version.txt file...
set versionFile = fso.OpenTextFile(args(1))
strVerTxtFile = versionFile.ReadAll
versionFile.Close

'find version text from file
regex.pattern = "(\d+(\.\d+)+)"
set Matches = regex.Execute(strVerTxtFile)

'split version info text using '.' and create new one with major.minor.svnrev
versionArray = Split(Matches(0).submatches(0), ".")
myNewVer = versionArray(0) & "." & versionArray(1) & "." & versionArray(3)

'Wscript.Echo myNewVer

'read all text from .vdproj of Setup Project
Set vdProjFile = fso.OpenTextFile(args(0))
textOfSetupProj = vdProjFile.ReadAll
vdProjFile.Close

'find and replace version number in .vdproj file
regex.pattern = "(""ProductVersion"" = ""8:)(\d+(\.\d+)+)"""
textOfSetupProj = regex.replace(textOfSetupProj, "$1" & myNewVer & """")

'replace ProductCode
regex.pattern = "(""ProductCode"" = ""8:)(\{.+\})"""
guid = CreateObject("Scriptlet.TypeLib").Guid
guid = left(guid, len(guid) - 2)
textOfSetupProj = regex.replace(textOfSetupProj, "$1" & guid & """")

'replace PackageCode
regex.pattern = "(""PackageCode"" = ""8:)(\{.+\})"""
guid = CreateObject("Scriptlet.TypeLib").Guid
guid = left(guid, len(guid) - 2)
textOfSetupProj = regex.replace(textOfSetupProj, "$1" & guid & """")

'write back to .vbproj file with all the changes that we made above
set vdProjFileNew = fso.CreateTextfile(args(0), true)
vdProjFileNew.write(textOfSetupProj)
vdProjFileNew.close

Save IncrementSetupVersion.vbs

For auto increment setup project version use scripts IncrementSetupVersion.vbs add them to pre-build events.

first argument is setup project file, second is file with assembly version Example: "$(ProjectDir)..\SetupScript\IncrementSetupVersion.vbs" "$(ProjectDir)Setup.vdproj" "$(ProjectDir)..\version.txt"

Insert SVNRevision and Build number in your AssemblyInfo

2013-03-07T09:35:00.003+05:30

Insert SVNRevision and Build number in your AssemblyInfo

MSBuild Community Task, you can easily automatically generate smart version numbers, you have to:

Download MSBuildCommunityTasks " https://github.com/loresoft/msbuildtasks/downloads "
Make sure your “svn.exe” binary is in C:\program files\subversion\bin
Add this at the end of your .csproj /.vbproj file

**AssemblyInfo Update when project build in release mode


<Import Project="$(MSBuildExtensionsPath)\MSBuildCommunityTasks\MSBuild.Community.Tasks.Targets" />

<Target Name="BeforeBuild" Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' ">
<Version Condition=" '$(SkipIncrementAssemblyVersion)' != 'true' " VersionFile="Version.txt" BuildType="None" RevisionType="None">
        <Output TaskParameter="Major" PropertyName="Major" />
        <Output TaskParameter="Minor" PropertyName="Minor" />
        <Output TaskParameter="Build" PropertyName="Build" />
    </Version>
    <SvnVersion LocalPath="$(MSBuildProjectDirectory)" ToolPath="$(ProgramFiles)\VisualSVN\Bin">
        <Output TaskParameter="Revision" PropertyName="Revision" />
    </SvnVersion>
<Time Format="MM/dd/yyyy hh:mm:ss tt">
            <Output TaskParameter="FormattedTime" PropertyName="buildDateTime" />
</Time>
<FileUpdate Files="Properties\AssemblyInfo.cs" Regex="SVN Revision: r(\d+)" ReplacementText="SVN Revision: r$(Revision)" />
<FileUpdate Files="Properties\AssemblyInfo.cs" Regex="Build On: (\d+)\/(\d+)\/(\d+) (\d+)\:(\d+)\:(\d+) ((A|P)M)" ReplacementText="Build On: $(buildDateTime)" />
<FileUpdate Files="Properties\AssemblyInfo.cs" Regex="Build Machine: ([A-Za-z0-9\-]+)" ReplacementText="Build Machine: $(COMPUTERNAME)" />
<FileUpdate Files="Properties\AssemblyInfo.cs" Regex="AssemblyVersion$'(\d+)\.(\d+)\.(\d+)\.(\d+)'$" ReplacementText="AssemblyVersion('$(Major).$(Minor).$(Build).$(Revision)')" />
<FileUpdate Files="Properties\AssemblyInfo.cs" Regex="AssemblyFileVersion$'(\d+)\.(\d+)\.(\d+)\.(\d+)'$" ReplacementText="AssemblyFileVersion('$(Major).$(Minor).$(Build).$(Revision)')" />
<FileUpdate Files="Properties\AssemblyInfo.cs" Regex="AssemblyInformationalVersion$'(\d+)'$" ReplacementText="AssemblyInformationalVersion('$(Revision)')" />
</Target>

You should only have a “</Project>” field left

How to Import Contacts From Gmail Using ASP.NET & C# or VB

2012-09-01T11:08:00.002+05:30

I used ASP.NET and C# for developing this application.

We need to follow below steps to get gmail contacts

Step-1: Download Google data API setup from the specified URL

http://code.google.com/p/google-gdata/

Or you can directly download with the following

http://google-gdata.googlecode.com/files/Google%20Data%20API%20Setup%281.4.0.2%29.msi

That Set Up will installs set of Google Data dll’s (Google.GData.Apps.dll, Google.GData.Client.dll, Google.GData.Contacts.dll, Google.GData.Extensions.dll) into client installed Machine, you should collect that dll’s for your program.

Step-2: Design our aspx page (Default.aspx) by using with the following UI as simple scenario.

<body>

<div>

<table>

<tr>

<td>UserName</td>

</tr>

<tr>

<td>Password</td>

</tr>

<tr>

<td>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>

Step-3: Add those downloaded google dll’s as reference to your website in visual studio->Solution Explorer ->Right Click-> Click on Add Reference….->Browse ->Get dll’s from Installed Location->Press OK.

Step-4:

A) Add namespace these namespace to your code behind

using Google.GData.Contacts;

using Google.GData.Client;

using Google.GData.Extensions;

using Google.Contacts;

Imports Google.GData.Contacts

Imports Google.GData.Client

Imports Google.GData.Extensions

Imports Google.Contacts

Imports System.Data

B) After that write following code in code behind

public static DataSet GetGmailContacts(string App_Name, string Uname, string UPassword)

{

DataSet ds = new DataSet();

DataTable dt = new DataTable();

DataColumn C2 = new DataColumn();

C2.DataType = Type.GetType("System.String");

C2.ColumnName = "EmailID";

dt.Columns.Add(C2);

RequestSettings rs = new RequestSettings(App_Name, Uname, UPassword);

rs.AutoPaging = true;

ContactsRequest cr = new ContactsRequest(rs);

Feed<Contact> f = cr.GetContacts();

foreach (Contact t in f.Entries)

{

foreach (EMail email in t.Emails)

{

DataRow dr1 = dt.NewRow();

dr1["EmailID"] = email.Address.ToString();

dt.Rows.Add(dr1);

}

ds.Tables.Add(dt);

return ds;

}

Protected void Button1_Click(object sender, EventArgs e)

{

DataSet ds = GetGmailContacts("MyNetwork Web Application!", txtgmailusername.Text,txtpassword.Text);

gvmails.DataSource = ds;

gvmails.DataBind();

}

Public Shared Function GetGmailContacts(App_Name As String, Uname As String, UPassword As String) As DataSet

Dim ds As New DataSet()

Dim dt As New DataTable()

Dim C2 As New DataColumn()

C2.DataType = Type.[GetType]("System.String")

C2.ColumnName = "EmailID"

dt.Columns.Add(C2)

Dim rs As New RequestSettings(App_Name, Uname, UPassword)

rs.AutoPaging = True

Dim cr As New ContactsRequest(rs)

Dim f As Feed(Of Contact) = cr.GetContacts()

For Each t As Contact In f.Entries

For Each email As EMail In t.Emails

Dim dr1 As DataRow = dt.NewRow()

dr1("EmailID") = email.Address.ToString()

dt.Rows.Add(dr1)

ds.Tables.Add(dt)

Return ds

End Function

Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click

Dim ds As DataSet = GetGmailContacts("MyNetwork Web Application!", txtgmailusername.Text, txtpassword.Text)

gvmails.DataSource = ds

gvmails.DataBind()

End Sub

Textbox Control with TEXT in background

2012-08-23T11:02:00.004+05:30

It would be better to create a custom TextBox control,
but here is something quickly hard-coded to give you a idea.

Find what event gives you desired result etc......

For simple example make sure textbox1 tabindex is not 1

Public Class Form1

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

TextBox1.ForeColor =
Color.LightSlateGray

TextBox1.Text = "Enter Search
Criteria"

End Sub

Private Sub TextBox1_Leave(sender As System.Object, e As System.EventArgs) Handles TextBox1.Leave

If TextBox1.Text = "" Then

TextBox1.ForeColor = Color.LightSlateGray

TextBox1.Text = "Enter Search
Criteria"

End If

End Sub

Private Sub TextBox1_Enter(sender As System.Object, e As System.EventArgs) Handles TextBox1.Enter

If

TextBox1.Text = "Enter Search
Criteria"

Then

TextBox1.Text = ""

TextBox1.ForeColor = Color.Black

End If

End Sub

End Class

Output::

convert number to words in Crystal Reports

2012-08-04T10:07:00.001+05:30

convert number to words in Crystal Reports

Copy code and Paste in Formula Field.

numbervar RmVal:=0;
numbervar Amt:=0;
numbervar pAmt:=0;
stringvar InWords :="Rupees ";
Amt := {txtRuppes.Text};
if Amt > 10000000 then RmVal := truncate(Amt/10000000);
if Amt = 10000000 then RmVal := 1;
if RmVal = 1 then
InWords := InWords + " " + towords(RmVal,0) + " crore"
else
if RmVal > 1 then InWords := InWords + " " + towords(RmVal,0) + " crores";

Amt := Amt - Rmval * 10000000;
if Amt > 100000 then RmVal := truncate(Amt/100000);
if Amt = 100000 then RmVal := 1;
if RmVal >=1 then
InWords := InWords + " " + towords(RmVal,0) + " lakhs";

Amt := Amt - Rmval * 100000;
if Amt > 0 then InWords := InWords + " " + towords(truncate(Amt),0);
pAmt := (Amt - truncate(Amt)) * 100;
if pAmt > 0 then
InWords := InWords + " and " + towords(pAmt,0) + " paisa only"
else
InWords := InWords + " only";
UPPERCASE(InWords)
OutPut

Write XML in VB.net

2012-08-04T09:54:00.000+05:30

Write XML in VB.net

Write XML in Vb.net Using Entity Data Model

Code for VB

Imports ProjectManager.ProjectManagerModel
Imports System.Xml

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim DataObj As Customer
        Dim Cust As String = txtCustCode.Text
        Using modelContext As New ProjectManagerEntities
                DataObj = modelContext.Customers.Where(Function(C) C.CustId = CDbl(Cust)).FirstOrDefault()
                txtCustCode.Text = DataObj.CustId.ToString
                txtCustAlias.Text = DataObj.Account.Alias
                txtCustName.Text = DataObj.CustName
        End Using
    End Sub

    Private Sub btnXML_Click(sender As System.Object, e As System.EventArgs) Handles btnXML.Click
        Dim DataObj As Customer
        Dim Cust As String = txtCustCode.Text

        Using modelContext As New ProjectManagerEntities

            DataObj = modelContext.Customers.Where(Function(C) C.CustId = CDbl(Cust)).FirstOrDefault()
            ' Create XmlWriterSettings.
            Dim settings As XmlWriterSettings = New XmlWriterSettings()
            settings.Indent = True
            ' Create XmlWriter.
            Using writer As XmlWriter = XmlWriter.Create("C:\Customer.xml", settings)
                ' Begin writing.
                writer.WriteStartDocument()
                writer.WriteStartElement("Customers") ' Root.
                writer.WriteStartElement("Customer")

                writer.WriteElementString("CustId", DataObj.CustId.ToString)
                writer.WriteElementString("CustAlias", DataObj.Account.Alias)
                writer.WriteElementString("CustName", DataObj.CustName)

                writer.WriteEndElement()

                ' End document.
                writer.WriteEndElement()
                writer.WriteEndDocument()
            End Using
        End Using

    End Sub

End Class

Output...

How to Change Permission of Folder in Visual Basic / C#

2012-07-31T11:16:00.001+05:30

VB.NET Set Folder Permission / C#
Set Folder Permission
Allow/Deny

In this snippet we create a new directory and set the permission of it to Fullcontrol using ACL.

VB code
Private Shared Sub ChangePermissionOfDir()
        'Folder
        Dim NewDirectory As DirectoryInfo = Directory.CreateDirectory("C:\Test001")

        Dim dSecurity As DirectorySecurity = NewDirectory.GetAccessControl()

        Dim fAccess As New FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Deny)

        dSecurity.AddAccessRule(fAccess)

        NewDirectory.SetAccessControl(dSecurity)

    End Sub

C# code
private static void ChangePermissionOfDir()
{
   //Folder
   DirectoryInfo NewDirectory = Directory.CreateDirectory("C:\\Test001");

   DirectorySecurity dSecurity = NewDirectory.GetAccessControl();

   FileSystemAccessRule fAccess = new FileSystemAccessRule("Everyone", FileSystemRights.FullControl, AccessControlType.Deny);

   dSecurity.AddAccessRule(fAccess);

   NewDirectory.SetAccessControl(dSecurity);
}

How to create DataTable on the fly and add Rows (data) using VB.NET/C#

2012-07-31T10:32:00.002+05:30

How to Populate DataGridView on the fly using VB.NET

Not all data come from a database table;

at times we need to create in on the fly.
we create a datatable and then create appropriate data columns.

Tested Code..

Dim oDataTable As New DataTable("CustomerInfo")

Dim oDR As DataRow

oDataTable.Columns.Add(New DataColumn("Name"))

oDataTable.Columns.Add(New DataColumn("Date"))

oDataTable.Columns.Add(New DataColumn("Address"))

oDataTable.Columns.Add(New DataColumn("ContactNum"))

….

Then data row is created and added to the table

oDR = oDataTable.NewRow

oDR("Name") = "shree"

oDR("Date") = Now.ToString()

oDR("Address") = "Gujarat"

oDR("ContactNum") = "09898989898"

oDataTable.Rows.Add(oDR)

This can be continued till all rows are added;

DataGridView is then bound to the datatable using DataSource property

DataGridView1.DataSource = oDataTable

DataGridView1.AutoResizeColumns()

Which gives you the screen below

--------------------------------------------------------------------------------
Public Class Form1

Private Sub Form1_Load(sender As System.Object,

e As System.EventArgs) Handles MyBase.Load

CreateOnTheFlyDataTable()

End Sub

Sub CreateOnTheFlyDataTable()

Dim oDataTable As New DataTable("CustomerInfo")

Dim oDR As DataRow

oDataTable.Columns.Add(New DataColumn("Name"))

oDataTable.Columns.Add(New DataColumn("Date"))

oDataTable.Columns.Add(New DataColumn("Address"))

oDataTable.Columns.Add(New DataColumn("ContactNum"))

oDR = oDataTable.NewRow

oDR("Name") = "shree"

oDR("Date") = Now.ToString()

oDR("Address") = "Gujarat"

oDR("ContactNum") = "09898989898"

oDataTable.Rows.Add(oDR)

DataGridView1.DataSource = oDataTable

DataGridView1.AutoResizeColumns()

End Sub

End Class

Create 30 Days Trial Version with ProductKey Application

2012-07-09T11:41:00.001+05:30

Create 30 Days Trial Version Windows Application With Product Key

Imports System.Windows.Forms

Public Class ProdVersoin

Private Sub ProdVersoin_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

If My.Settings.Register = True Then
            MainMdi.Show()
            Me.Close()
        End If

        lblCurrentdate.Text = DateTime.Now

        If My.Settings.Exdate = Nothing Then
            My.Settings.Exdate = DateTime.Now.AddDays(30)
        Else

        End If
        lblExdate.Text = My.Settings.Exdate
End Sub

Private Sub OK_Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK_Button.Click
If TextBox1.Text = "123-456-789" Then
            My.Settings.Register = True
            MainMdi.Show()
            Me.Close()
        Else
            MsgBox("Wrong " + TextBox1.Text + " Activation Key, Retry..!!")
        End If
End Sub

Public Sub RefreshTime()
        lblCurrentdate.Text = DateTime.Now
End Sub

Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        RefreshTime()
        If DateTime.Now > My.Settings.Exdate Then
            lblError.Text = "Sorry, Your Product Has Expired. Enter Product Your " + vbNewLine + "Product Key for Register or get Full Version!!"
End If
End Sub

-This cede is basically tested. It will work..

Create BackUp or Restore Sql server Database Using SMO in VB.Net

2012-06-05T11:15:00.000+05:30

Create BackupRestore.aspx

Add Code in BackupRestore.aspx.vb

For Backup Database

For Restore database

For Detail contact Add Comment...

Using SQL Membership with ASP.NET Application

2012-06-05T09:07:00.002+05:30

Introduction

Most ASP.NET projects implement features like Login, Security, Password encryption, User Roles, Access rights, etc. Lot of code and logic goes into implementing all of this stuff. SQL Membership resolves all these concerns just by a few clicks and configurations and above all (which I like the most) without a single line of code. :)

Background

In this article, I will provide a complete walkthrough about using SQL Membership in ASP.NET projects. I hope you find this stuff beneficial.

Using the Code

SQL Membership is a wonderful way of interaction between ASP.NET and SQL Server. SQL Membership provides 2 kinds of Authentication methods:

Using SQL Server Membership database (Kind of Form Authentication)
Using Local System authentication (Windows Authentication)

The process is relatively easy to implement. I will go step by step to show the implementation. Let's consider that you are going to develop an application from scratch, so here we go:

First, we need to setup the SQL Membership database. In order to create that database, please follow the steps:

Go to Windows Start menu>>All Programs>>Microsoft Visual Studio 2005/2008>>Visual Studio Tools and click Visual Studio 2008 Command Prompt
Type aspnet_regsql and press Enter key from the keyboard.
This will launch SQL Membership Wizard as follows. Click Next button to continue.
On the next screen select the first option to "Configure SQL Server for application services". Click Next to continue.
This screen is important as you will mention the Database information here. On the basis of that, table structures and other things will be created. You can use an existing database or create a new one.
After filling all the information click next and Finish. :) You guessed right, your SQL Membership database is ready now.

Now let's connect our ASP.NET Website application to this database. For this, first create an ASP.NET website in Visual Studio 2005/2008.
Now open the Web.Config file for the project and add a well qualified Connection String for the SQL Server Membership database you are going to use.
Collapse
```
<connectionstrings>
    <add name="abcdCS" connectionstring="
    Data Source=myserver\sqlexpress;Initial Catalog=abcd;
    User ID=sa;Password=pass" 
    providername="System.Data.SqlClient" />
</connectionstrings>
```
This Connection String will be used by the ASP.NET Configuration for connection with the database.
After that, click on VS 2005/2008 Project menu>>ASP.NET Configuration
This will launch the ASP.NET Web Site Administration Tool. Here, you will test the database connection, manage users, roles, permissions, etc.
First click on the Provider tab.
Here you will be testing the database connection. Click on both the links one by one to test the connectivity with database.
Clicking the above links will show screen as mentioned below:

Click the "Test" link in each screen to check the connection status.
If your connection is successful, you will get a screen like this:

So far so good. :) We are done with the connection part of SQL Membership database with the ASP.NET website application.
Now in order to select authentication types, create users, manage roles, and access rules, you need to see the Security tab.

In order to setup the SMTP settings, Application Settings, Debugging and Tracing settings, and default error page, you need to visit the Application tab.

Manage User Role in aspnet membership

2012-04-26T15:55:00.002+05:30

Manage User Role in aspnet membership

ManageUserRole.aspx

<h3>
        Role Membership</h3>
    <asp:Label ID="Msg" ForeColor="maroon" runat="server" /><br />
    <table cellpadding="3" border="0">
        <tr>
            <td valign="top">
                Roles:
            </td>
            <td valign="top">
                <asp:ListBox ID="RolesListBox" runat="server" Rows="5" AutoPostBack="true" Width="150px" />
            </td>
            <td valign="top">
                Users:
            </td>
            <td valign="top">
                <asp:ListBox ID="UsersListBox" DataTextField="Username" Rows="5" SelectionMode="Multiple"
                    runat="server" Width="150px" />
            </td>
            <td valign="top">
                <asp:Button Text="Add User(s) to Role" ID="AddUsersButton" runat="server" OnClick="AddUsers_OnClick" />
            </td>
        </tr>
        <tr>
            <td valign="top">
                Users In Role:
            </td>
            <td valign="top">
                <asp:GridView runat="server" CellPadding="4" ID="UsersInRoleGrid" AutoGenerateColumns="false"
                    GridLines="None" CellSpacing="0" OnRowCommand="UsersInRoleGrid_RemoveFromRole">
                    <HeaderStyle BackColor="navy" ForeColor="white" />
                    <Columns>
                        <asp:TemplateField HeaderText="User Name">
                            <ItemTemplate>
                                <%# Container.DataItem.ToString() %>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:ButtonField Text="Remove From Role" ButtonType="Link" />
                    </Columns>
                </asp:GridView>
            </td>
        </tr>
    </table>

ManageUserRole.aspx.vb

Imports System.Web.Security
Imports System.Web.UI

Public Class ManageUser
    Inherits System.Web.UI.Page

    Dim rolesArray() As String
    Dim users As MembershipUserCollection
    Dim usersInRole() As String

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Msg.Text = ""

        If Not IsPostBack Then
            ' Bind roles to ListBox.
            rolesArray = Roles.GetAllRoles()
            RolesListBox.DataSource = rolesArray
            RolesListBox.DataBind()

            ' Bind users to ListBox.
            users = Membership.GetAllUsers()
            UsersListBox.DataSource = users
            UsersListBox.DataBind()
        End If

        If Not RolesListBox.SelectedItem Is Nothing Then
            ' Show users in role. Bind user list to GridView.
            usersInRole = Roles.GetUsersInRole(RolesListBox.SelectedItem.Value)
            UsersInRoleGrid.DataSource = usersInRole
            UsersInRoleGrid.DataBind()
        End If
    End Sub

    Public Sub AddUsers_OnClick(ByVal sender As Object, ByVal args As EventArgs) Handles AddUsersButton.Click

        ' Verify that a role is selected.
        If RolesListBox.SelectedItem Is Nothing Then
            Msg.Text = "Please select a role."
            Return
        End If

        ' Verify that at least one user is selected.
        If UsersListBox.SelectedItem Is Nothing Then
            Msg.Text = "Please select one or more users."
            Return
        End If

        ' Create list of users to be added to the selected role.
        Dim newusers(UsersListBox.GetSelectedIndices().Length - 1) As String
        For i As Integer = 0 To newusers.Length - 1
            newusers(i) = UsersListBox.Items(UsersListBox.GetSelectedIndices(i)).Value
        Next

        ' Add the users to the selected role.
        Try
            Roles.AddUsersToRole(newusers, RolesListBox.SelectedItem.Value)

            ' Re-bind users in role to GridView.
            usersInRole = Roles.GetUsersInRole(RolesListBox.SelectedItem.Value)
            UsersInRoleGrid.DataSource = usersInRole
            UsersInRoleGrid.DataBind()
        Catch e As Exception
            Msg.Text = e.Message
        End Try
    End Sub

    Public Sub UsersInRoleGrid_RemoveFromRole(ByVal sender As Object, ByVal args As GridViewCommandEventArgs)

        ' Get the selected user name to remove.
        Dim index As Integer = Convert.ToInt32(args.CommandArgument)
        Dim username As String = (CType(UsersInRoleGrid.Rows(index).Cells(0).Controls(0), DataBoundLiteralControl)).Text

        ' Remove the user from the selected role.
        Try
            Roles.RemoveUserFromRole(username, RolesListBox.SelectedItem.Value)
        Catch e As Exception
            Msg.Text = "An exception of type " & e.GetType().ToString() & _
                       " was encountered removing the user from the role."
        End Try

        ' Re-bind users in role to GridView.
        usersInRole = Roles.GetUsersInRole(RolesListBox.SelectedItem.Value)
        UsersInRoleGrid.DataSource = usersInRole
        UsersInRoleGrid.DataBind()
    End Sub
End Class

Reset password In ASPNET membership VB

2012-04-26T15:25:00.000+05:30

Reset password In ASPNET membership VB

ResetPassword.aspx

<h3> Retrieve Password</h3>
    <p><asp:Label ID="Msg" runat="server" ForeColor="maroon" /><br />
    </p> Username:
    <asp:TextBox ID="UsernameTextBox" Columns="30" runat="server" AutoPostBack="True" />
    <asp:RequiredFieldValidator ID="UsernameRequiredValidator" runat="server" ControlToValidate="UsernameTextBox"
        ForeColor="red" Display="Static" ErrorMessage="Required" /><br />
    <asp:Button ID="ResetPasswordButton" Text="Reset Password" OnClick="ResetPassword_OnClick" runat="server" Enabled="False" />
    <br />
    <asp:Label ID="lblPassword" runat="server" ForeColor="maroon" Style="font-weight: 700;
        color: #FF0000; font-size: x-large" /><br />

ResetPassword.aspx.vb

Imports System.Web.Security

Public Class ResetPassword
    Inherits System.Web.UI.Page

    Dim u As MembershipUser

    Public Sub Page_Load(sender As Object, args As EventArgs) Handles Me.Load

        If Not Membership.EnablePasswordReset Then
            FormsAuthentication.RedirectToLoginPage()
        End If

        Msg.Text = ""

        If Not IsPostBack Then
            Msg.Text = "Please supply a username."
        Else
            VerifyUsername()
        End If
    End Sub

    Public Sub VerifyUsername()
        u = Membership.GetUser(UsernameTextBox.Text, False)

        If u Is Nothing Then
            Msg.Text = "Username " & Server.HtmlEncode(UsernameTextBox.Text) & " not found. Please check the value and re-enter."

            ResetPasswordButton.Enabled = False
        Else
            ResetPasswordButton.Enabled = True
        End If
    End Sub

    Protected Sub ResetPassword_OnClick(sender As Object, args As EventArgs) Handles ResetPasswordButton.Click
        Dim newPassword As String
        u = Membership.GetUser(UsernameTextBox.Text, False)

        If u Is Nothing Then
            Msg.Text = "Username " & Server.HtmlEncode(UsernameTextBox.Text) & " not found. Please check the value and re-enter."
            Return
        End If

        Try
            newPassword = u.ResetPassword()
        Catch e As MembershipPasswordException
            Msg.Text = "Invalid password answer. Please re-enter and try again."
            Return
        Catch e As Exception
            Msg.Text = e.Message
            Return
        End Try

        If Not newPassword Is Nothing Then
            Msg.Text = "Password reset. Your new password is: "
            lblPassword.Text = "" & Server.HtmlEncode(newPassword)
        Else
            Msg.Text = "Password reset failed. Please re-enter your values and try again."
        End If
    End Sub

End Class

Using a Custom Action to Create a Database at Installation

2012-04-06T09:01:00.000+05:30

Note: 1. This walkthrough requires SQL Server on the computer where you will deploy the application.
2.The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu.
3.The following instructions demonstrate creating a deployment project using a Visual Basic project; the general principles apply to all Visual Studio language projects that support deploying Windows-based applications.

To create an installer class

On the File menu, click New Project.
In the New Project dialog box, select Visual Basic Projects in the Project Type pane, and then choose Class Library in the Templates pane. In the Name box, type DBCustomAction.
On the Project menu, click Add New Item.
In the Add New Item dialog box, choose Installer Class. In the Name box, type VbDeployInstaller.vb.

To create a data connection object

In Server Explorer, select Data Connections. Right-click and choose Add Connection.
In the Choose Data Source dialog box, select Microsoft SQL Server.
In the Add Connection dialog box, do the following:
1. In the Server name drop-down list, type or select a server name.
2. Select Use Windows Authentication.
3. In the database box, type master.
4. Click OK to close the dialog box.
From the Data menu, click Add New Data Source, then use the wizard to add the connection that you established in the previous steps. To verify that the data source is in the project, click Show Data Sources on the Data menu.

To create a text file that contains a SQL statement to create a database

In Solution Explorer, select the DBCustomAction project. On the Project menu, choose Add New Item.
In the Add New Item dialog box, choose Text File. In the Name box, type sql.txt (must be in lower case).

Add the following to the sql.txt file:
------------------------------------------------------------------------------------------------------------

CREATE TABLE [dbo].[Employees] (
[Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Rsvp] [int] NULL ,
[Requests] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY];

ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD 
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
[Name]
) ON [PRIMARY];

------------------------------------------------------------------------

In Solution Explorer, select sql.txt. In the Properties window, set the BuildAction property to Embedded Resource.

To add code to the installer class to read the text file

In Solution Explorer, select VbDeployInstaller.vb. On the View menu, choose Code.
Add the following Imports statement at the top of the module:

Imports System.IO
Imports System.Reflection
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Configuration.Install
Public Class VbDeployInstaller Dim masterConnection As New System.Data.SqlClient.SqlConnection()
Public Sub New() MyBase.New() 'This call is required by the Component Designer.
InitializeComponent()
'Add initialization code after the call to InitializeComponent
End Sub
Private Function GetSql(ByVal Name As String) As String
Try
' Gets the current assembly.
Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly() ' Resources are named using a fully qualified name.
Dim strm As Stream = Asm.GetManifestResourceStream( _ Asm.GetName().Name + "." + Name) ' Reads the contents of the embedded file.
Dim reader As StreamReader = New StreamReader(strm) Return reader.ReadToEnd()
Catch ex As Exception MsgBox("In GetSQL: " & ex.Message)
Throw ex End Try
End Function
Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String) Dim Command As New SqlClient.SqlCommand(Sql, masterConnection) ' Initialize the connection, open it, and set it to the "master" database
masterConnection.ConnectionString = DBCustomAction.My.Settings.masterConnectionString
Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)
Try
Command.ExecuteNonQuery()
Finally
' Closing the connection should be done in a Finally block
Command.Connection.Close()
End Try
End Sub
Protected Sub AddDBTable(ByVal strDBName As String) Try
' Drop Database if exists.
ExecuteSql("master", "ALTER DATABASE TrainTrack SET SINGLE_USER WITH ROLLBACK IMMEDIATE") ExecuteSql("master", "Drop database TrainTrack")
Catch ex As Exception ' Reports any errors and abort.
End Try
Try
' Creates the database.
ExecuteSql("master", "CREATE DATABASE " + strDBName) ' Creates the tables.
ExecuteSql(strDBName, GetSql("sql.txt")) Catch ex As Exception ' Reports any errors and abort.
MsgBox("In exception handler: " & ex.Message) Throw ex End Try
End Sub
Public Overrides Sub Install(ByVal stateSaver As _ System.Collections.IDictionary)
MyBase.Install(stateSaver) 'AddDBTable(Me.Context.Parameters.Item("dbname"))
AddDBTable("TrainTrack")
End Sub
End Class

On the Build menu, choose Build DBCustomAction.

To create a deployment project

On the File menu, click Add, and then click New Project.
In the Add New Project dialog box, open the Other Project Types node and select Setup and Deployment Projects in the Project Type pane. Then select Setup Project in the Templates pane. In the Name box, type DBCustomAction_Setup.
In the Properties window, select the ProductName property and type DB Installer.
In the File System Editor, select the Application Folder. On the Action menu, click Add, and then click Project Output.
In the Add Project Output Group dialog box, select Primary output for the DBCustomAction project.

To create a custom installation dialog

Select the DBCustomAction_Setup project in Solution Explorer. On the View menu, point to Editor, and choose User Interface.
In the User Interface Editor, select the Start node under Install. On the Action menu, choose Add Dialog.
In the Add Dialog dialog box, select the Textboxes (A) dialog, then click OK.
On the Action menu, choose Move Up. Repeat until the Textboxes (A) dialog is above the Installation Folder node.
In the Properties window, select the BannerText property and type Specify Database Name.
Select the BodyText property and type This dialog allows you to specify the name of the database to be created on the database server.
Select the Edit1Label property and type Name of database:.
Select the Edit1Property property and type CUSTOMTEXTA1.
Select the Edit2Visible, Edit3Visible, and Edit4Visible properties and set them to False.

To create a custom action

Select the DBCustomAction_Setup project in Solution Explorer. On the View menu, point to Editor, and then click Custom Actions.
In the Custom Actions Editor, select the Install node. On the Action menu, choose Add Custom Action.
In the Select item in project dialog box, double-click the Application Folder.
Select Primary output from DBCustomAction (Active), then click OK to close the dialog box.
Make sure that Primary output from DBCustomAction (Active) item is selected in the Custom Actions Editor. In the Properties window, select the CustomActionData property and type /dbname=[CUSTOMTEXTA1].
On the Build menu, choose Build DBCustomAction_Setup.

To install on your development computer

Select the DBCustomAction_Setup project in Solution Explorer. On the Project menu, choose Install.

Entity Framework 4.0 - Learn to Create Databases from the Model

2012-02-22T12:08:00.000+05:30

The ADO.NET Entity Framework (EF) is object relational mapping software for ADO.NET, used to develop applications that interact with data. It provides a mapping between the relational database schemas and objects. This technology is helpful for architecting, designing and developing at a conceptual level without worrying about details. ADO.NET Entity Framework allows developers to program against the Entity Relationship (Object) model, as opposed to querying against the relational database, mainly concentrating on the data. The main benefit is to make data oriented applications maintenance friendly.

EF - Benefits

The application won’t be tied to hard-coded dependencies on the storage schema or database.
The conceptual model and the storage-specific schema mappings can change without changing the application code.
Developers can work more easily with an object model that can be mapped to various storage schemas, and can be implemented in different databases.
The ability to map multiple conceptual models to a single storage schema.

Model First Development was introduced in VS 2010, along with .NET Framework 4.0, and the essential idea behind the Entity Framework is to query the model, rather than the database. In this article I will walk through how to generate databases from the model.

Understanding the model

EF maps to the database tables using a model. This model is responsible for mapping the application’s entities and their relationships to the data held in the physical database. It’s a combination of three layers, and is stored as an Entity Data Model (.edmx) file. .edmxis an XML based file that consists of these 3 layers.

Storage Layer: this is defined with Store Schema Definition Language (SSDL). Table structures and their relationships are defined here.
Conceptual Layer: this is defined with Conceptual Schema Definition Language (CSDL). Business entities and their relationships are described in the conceptual schema.
Mapping Layer. This is defined with Mapping Specification Language (MSL). It maps the conceptual schema to the storage schema. The mapping schema describes how the business entities map to the database schema, and how database tables map to the entities in the model.

If you right click on an .edmx file and open it with XML Text Editor, you can view the EDMX schema layers.
At runtime, the .edmx is split into three different files: .ssdl, .csdl and .msl.
To construct any database, we need define the tables for the data storage. Here, tables are mapped to entities, hence the need to have entities and their relationships defined in the model. The model can be created using an .edmx file in Entity Framework and each entity in a specific Entity Framework is mapped as a table inside the database, and each property in an entity is mapped to a corresponding column in the specified table.

Creating the model

Let us start by creating a Class Library Application in Visual Studio 2010, so that we can re-use the models. We select the Add ->new Item , then the Data tab in the Visual Studio 2010 Installed Templates list, and finally select ADO.NET Entity Data Model. We’ll name our .edmx ‘Company’, and click the Add button, in the lower right corner, as shown below.

Figure 1: Adding a new item to the class library project.

Figure 2: Adding Company.edmx to the project.

The moment we click the Add button, the Entity Data Model Wizard appears. This gives us two options to select from:

Generate from database
Empty model

Figure 3: Adding an empty model.

We aren’t going to worry about the first option as we are interested in creating a database from the model only. We’ll select the second option, Empty model, and click the Finish button. As you can see from the screenshot above, this option creates an empty model as a starting point for visually designing a conceptual model from the toolbox. When the project is compiled, classes will be generated from the model. We’ll specify a database connection to map the conceptual model to the storage model later.
If you observe the Solution Explorer, it creates a new reference related to Entity Framework, System.Data.Entity, responsible for working with the Entity Framework related APIs. This reference is added only after adding an .edmx file.

Figure 4: The System.Data.Entity reference, added after an .edmx file is created.

Creating entities in the .edmx

Now we need to define the model in this .edmx file by creating entities, which can be done by dragging items from the EF toolbox or from the model itself.

Figure 5: Entity Framework toolbar for creating entities and relationships between entities.

In a Company, we’ll have Employees, Departments and Managers as entities. Let’s create the Employee entity.
We right click on the .edmx file and Click Add → Entity as shown below.

Figure 6: Adding a new entity to the Model.

A dialogue box for adding an Entity appears, as shown below.

Figure 7: Creating the Employee entity.

We’ll name the entity ‘Employee’. The Entity Set is automatically pluralized by the IDE, based on the Entity Name.
It also creates a Key property - ID - of type Integer. This Key is called the Entity Key in the model, and mapped as the Primary Key in the database. If you do not want to create the Entity Key property for a particular entity you can uncheck this property. Next, we’ll press OK.
Now we have created an entity, we can start adding some more properties for it. There are two ways of doing this:

Right click on the entity to add new properties such as EmpName, Description, DOB and Address.

Figure 8: Creating scalar properties for the entities.
2. Select the Id property and hit enter to add some more scalar properties (scalar properties map to a single field in the storage layer).

Editing entity properties

To edit the attributes for entity properties, you select the property and right click it, then navigate to properties. The properties that you set are reflected in the database that is going to be created from the model.

Figure 9: Selecting the Employee entity’s properties.

Figure 10: Editing the Employee entity’s properties.

Note that here the identity property for the primary key column is set automatically by the IDE. The Nullable attribute allows or disallows any null values inside the column.

Creating more entities for the model

Now let us create a department entity with the properties Id, DeptName, DeptDescription, and DeptCode, as shown to the left.

Fig11: The Department entity.

Let us also create a Manager entity with ManagerId, EmployeeID as properties. The key for this entity is the combination of ManagerId and EmployeeID. One employee can report to multiple managers.

Figure 12: The Manager entity.

Applying relationships to the entities

An employee cannot be member of multiple departments. A department, however, should accommodate multiple employees, so the relationship between employee and department is one to many. Let us add a new association between these two tables. For this, we right click on the model, and click Add → Association…

Figure 13: Adding an Association relationship to the table.

This opens the following window, with the option to add relationships between these two tables.

Figure 14: Adding an association between Employee and Department.

We need to make provision for the department entity to have many instances of the Employee entity, so we map the relationship between the department and the Employee entities as one to many, as shown above. Clicking the check box “Add foreign key properties to the ‘Employee’ entity” helps to create the foreign key relationships between these two tables automatically.
If we observe the Employee entity, we notice that departmentid is being added to the employees table for the foreign key relationship in the database.
A manager can have more than one employee reporting to him, and an employee can report to multiple managers, so the relationship between the Employee entity and the manager entity is many to many. We need to add a many to many association between these two entities. Because a manager is also an employee of the company, the manager class gets inherited from the Employee class. To add this inheritance relationship, right click on the model and select Add →Inheritance…

Figure 15: Adding an inheritance relationship for entities.

In the dialog box, select Employee as the base entity as and Manager as the derived entity.

Figure 16: Adding inheritance between the Employee and Manager entities.

Figure 17: The final model after applying all the relationships.

We’ve now finished adding relationships between our entities, and are ready to generate our database.

Changing DatabaseSchema Name

Entity Framework Model uses dbo as the default schema name. In order to change that, right click on the .edmx in the VS2010 IDE, and click on Properties. The ConceptualEntityModel properties window appears as shown below. Make sure that you change the name according to the project, so that the script files are generated accordingly.

Figure 18: Conceptual Model Properties.

If we look at Company.edmx in the automatic editor selector, we can view the XML schema code that’s been generated from the design. SSDL, CSDL, and MSL are created by the IDE, based on the entities and their relationships. Each time we make changes to the model, this schema gets recreated. To view this, we need to right click on the .edmx file and open with the XML (Text) Editor.

Figure 19: Opening the .edmx with the XML editor.

Here we can examine how the Entities and their relations are mapped in the conceptual model, and how the storage schema and conceptual schema are actually mapped in the mapping layer.
We can also examine the Company.designer.cs file, a partial class inherited from ObjectContext.
All the entities that are created from the IDE inherit from Entityobject. Their code is generated automatically with properties and datacontract attributes. Whenever we modify and save the .edmx file, the classes or the automatic code are regenerated by the IDE.

Generating the database from the model

Now we need to right click and select Generate Database from the Model, from the context menu.

Figure 20: Generate Database from the Model

We need to have an empty or existing database created in SQL Server to generate a database from the model. I have created an empty database, called Company, in SQL Server 2008. If you do not have existing connections, then click on new Connection button and specify your credentials, as shown below.

Figure 21: Connection string credentials.

Now, choose the appropriate data connection from the dropdown list, as shown below. Here we also have the option to save the connection string in app.config, which contains connection string metadata and various settings for the project. The syntax is somewhat different from the normal connection string.

Figure 22: Choosing the data connection for the creation of a database from our model.

Pressing Next generates a file called Company.edmx.sql. Note that the constraints, foreign key relationships, and primary keys in the tables are created based on the properties specified in the model.

Figure 23: The generated Company.edmx.sql file

Finally, click on the Finish button. The IDE creates this .sql script file for us:

-- ----------------------------------------------------------------------------------------------------------------------

-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure

-- --------------------------------------------------

-- Date Created: 04/25/2011 17:30:49

-- Generated from EDMX file: D:\Training\AJAX\ModelDatabase\ModelDatabase\Company.edmx

-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;

USE [Company];

IF SCHEMA_ID(N'Hima') IS NULL EXECUTE(N'CREATE SCHEMA [Hima]');

-- --------------------------------------------------

-- Dropping existing FOREIGN KEY constraints

-- --------------------------------------------------

-- Dropping existing tables

-- --------------------------------------------------

-- Creating all tables

-- --------------------------------------------------

-- Creating table 'Employees'

CREATE TABLE [Hima].[Employees] (

[Id] int IDENTITY(1,1) NOT NULL,

[EmpName] nchar(4000) NOT NULL,

[EmpDescription] nvarchar(max) NOT NULL,

[DOB] datetime NOT NULL,

[Address] nvarchar(max) NOT NULL,

[DepartmentId] int NOT NULL

);

-- Creating table 'Departments'

CREATE TABLE [Hima].[Departments] (

[Id] int IDENTITY(1,1) NOT NULL,

[DeptName] nvarchar(max) NOT NULL,

[DeptDescription] nvarchar(max) NOT NULL,

[DeptCode] nchar(4000) NOT NULL

);

-- Creating table 'Employees_Manager'

CREATE TABLE [Hima].[Employees_Manager] (

[ManagerId] int NOT NULL,

[EmployeeID] int NOT NULL,

[Id] int NOT NULL

);

-- Creating table 'EmployeeManager'

CREATE TABLE [Hima].[EmployeeManager] (

[Employees_Id] int NOT NULL,

[Managers_Id] int NOT NULL

);

-- --------------------------------------------------

-- Creating all PRIMARY KEY constraints

-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Employees'

ALTER TABLE [Hima].[Employees]

ADD CONSTRAINT [PK_Employees]

PRIMARY KEY CLUSTERED ([Id] ASC);

-- Creating primary key on [Id] in table 'Departments'

ALTER TABLE [Hima].[Departments]

ADD CONSTRAINT [PK_Departments]

PRIMARY KEY CLUSTERED ([Id] ASC);

-- Creating primary key on [Id] in table 'Employees_Manager'

ALTER TABLE [Hima].[Employees_Manager]

ADD CONSTRAINT [PK_Employees_Manager]

PRIMARY KEY CLUSTERED ([Id] ASC);

-- Creating primary key on [Employees_Id], [Managers_Id] in table 'EmployeeManager'

ALTER TABLE [Hima].[EmployeeManager]

ADD CONSTRAINT [PK_EmployeeManager]

PRIMARY KEY NONCLUSTERED ([Employees_Id], [Managers_Id] ASC);

-- --------------------------------------------------

-- Creating all FOREIGN KEY constraints

-- --------------------------------------------------

-- Creating foreign key on [Employees_Id] in table 'EmployeeManager'

ALTER TABLE [Hima].[EmployeeManager]

ADD CONSTRAINT [FK_EmployeeManager_Employee]

FOREIGN KEY ([Employees_Id])

REFERENCES [Hima].[Employees]

([Id])

ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating foreign key on [Managers_Id] in table 'EmployeeManager'

ALTER TABLE [Hima].[EmployeeManager]

ADD CONSTRAINT [FK_EmployeeManager_Manager]

FOREIGN KEY ([Managers_Id])

REFERENCES [Hima].[Employees_Manager]

([Id])

ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EmployeeManager_Manager'

CREATE INDEX [IX_FK_EmployeeManager_Manager]

ON [Hima].[EmployeeManager]

([Managers_Id]);

-- Creating foreign key on [DepartmentId] in table 'Employees'

ALTER TABLE [Hima].[Employees]

ADD CONSTRAINT [FK_DepartmentEmployee]

FOREIGN KEY ([DepartmentId])

REFERENCES [Hima].[Departments]

([Id])

ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartmentEmployee'

CREATE INDEX [IX_FK_DepartmentEmployee]

ON [Hima].[Employees]

([DepartmentId]);

-- Creating foreign key on [Id] in table 'Employees_Manager'

ALTER TABLE [Hima].[Employees_Manager]

ADD CONSTRAINT [FK_Manager_inherits_Employee]

FOREIGN KEY ([Id])

REFERENCES [Hima].[Employees]

([Id])

ON DELETE NO ACTION ON UPDATE NO ACTION;

-- --------------------------------------------------

-- Script has ended

-- ---------------------------------------------------------------------------------------------------------------------

This script can be given to the DBA to run, creating the database. Or, we can right click on the script file and press Execute SQL or Control + Shift +E, to run a script against SQL Server 2008 database from the IDE.
The SQL script is generated from the SSDL contained in the .edmx file. This script contains lots of DDL statements to create the database tables, which correspond to the tables described in the SSDL. Entities are mapped as tables in the database and scalar properties are mapped as columns in the corresponding tables.

Summary

In this article we have learnt how to create a database from the model in EF 4.0, an approach also called ‘Model First‘. We’ve looked at the structure of an .edmx file, and its significance, and learnt how to create entities, properties for the entities, and relationships between the entities. As we’ve seen, Model First Development helps to create a model for the entities, then has Visual Studio 2010 generate the DDL to create a database with matching tables, columns and relationships for the entities.

.NET Tuts By Chirag Patel

SQL SERVER – Split Comma Separated List without Using a Function

Auto Generate Paired Number

Read Excel files data and insert in to SQL Table

Client side HTML encoding

XML banner fader in jQuery

Move Selected Gridview Rows to Another Gridview in Asp.net

360 degree spin onMouseover and onMouseout

How to fit Windows Form to any screen resolution?

Open Excel workbooks in VB.NET Solutions

Zip and Extract files in .NET

ProductVersion update for MSI Projects

The script is written in VBScript and is quite simple.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Set version number of an MSI setup project from assembly'' and update relevant GUIDs'' Chirag Patel /03/03/2013

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Save IncrementSetupVersion.vbs

For auto increment setup project version use scripts IncrementSetupVersion.vbs add them to pre-build events.

first argument is setup project file, second is file with assembly version Example: "$(ProjectDir)..\SetupScript\IncrementSetupVersion.vbs" "$(ProjectDir)Setup.vdproj" "$(ProjectDir)..\version.txt"

Insert SVNRevision and Build number in your AssemblyInfo

Insert SVNRevision and Build number in your AssemblyInfo

How to Import Contacts From Gmail Using ASP.NET & C# or VB

Textbox Control with TEXT in background

convert number to words in Crystal Reports

convert number to words in Crystal Reports

Write XML in VB.net

How to Change Permission of Folder in Visual Basic / C#

How to create DataTable on the fly and add Rows (data) using VB.NET/C#

Create 30 Days Trial Version with ProductKey Application

Create BackUp or Restore Sql server Database Using SMO in VB.Net

Using SQL Membership with ASP.NET Application

Introduction

Background

Using the Code

Manage User Role in aspnet membership

Reset password In ASPNET membership VB

Using a Custom Action to Create a Database at Installation

To create an installer class

To create a data connection object

To create a text file that contains a SQL statement to create a database

To add code to the installer class to read the text file

To create a deployment project

To create a custom installation dialog

To create a custom action

To install on your development computer

Entity Framework 4.0 - Learn to Create Databases from the Model

EF - Benefits

Understanding the model

Creating the model

Creating entities in the .edmx

Editing entity properties

Creating more entities for the model

Applying relationships to the entities

Changing DatabaseSchema Name

Generating the database from the model

Summary

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' Set version number of an MSI setup project from assembly
'' and update relevant GUIDs
'' Chirag Patel /03/03/2013