Get connection String from Web.config in ASP.Net and C#

“Human Brain is amazing , When I don’t want to save something inside my Brain , It also does not want to save it and literally it does not do that. ” – This line is true for me ūüôā

And I always go to my previous written Code + Config file (Or Web) to know how to get Connection String from Web.config . I don’t want to use my Processor of the Brain a minimal percentage for this , And I guess , There are also so many people out there in the world , so this Post is for you guys ūüôā

So in my Web.config/App.config file , I will add the below thing .

    <add name="connectionStr" connectionString="Data Source=MASOOM-PC;Initial Catalog=ProjectManagerDB;Integrated Security=True" providerName="System.Data.SqlClient" />

And Below is the C# code for that .

string connectionString = ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;

We can also add Node to AppSettings in Web.config and can get it easily .

    <add key="EmailTo" value="emailid@yahoo.com" />

And this is how we can get this in C#

string emailTo = ConfigurationSettings.AppSettings["EmailTo"];

In future , I will definitely come to my own Blog for this ūüôā Cheers


Create Dynamic DataTable in C#

Many a time , we need to create DataTable dynamically inside our C# Code . So this is the easiest way how we can create DataTable dynamically and add data to it .

DataTable dtDatabaseInfo = new DataTable("tblData");
            dtDatabaseInfo.Columns.Add("ID", typeof(string));
            dtDatabaseInfo.Columns.Add("Destination", typeof(string));
            dtDatabaseInfo.Columns.Add("ServerName", typeof(string));
            dtDatabaseInfo.Columns.Add("sDB_Name", typeof(string));
            dtDatabaseInfo.Columns.Add("DbUserName", typeof(string));
            dtDatabaseInfo.Columns.Add("DbPassword", typeof(string));

            DataRow row = dtDatabaseInfo.NewRow();

            row["ID"] = "M001";
            row["Destination"] = "MyDestination";
            row["ServerName"] = "MyLocalServer";
            row["sDB_Name"] = "MyDatabase";
            row["DbUserName"] = "MyUser";
            row["DbPassword"] = "MyPassword";


Binding ASP.Net GridView with Custom Columns

In ASP.Net( aspx ) Markup will be like below :::

<asp:GridView ID="gvTicketDetails" runat="server" AutoGenerateColumns="false">       
                       <asp:TemplateField HeaderText="Tk#" >
                                <asp:Label ID="lblTicketNo" runat="server" Text='<%#Bind("TicketNum") %>'></asp:Label>            
                        <asp:TemplateField HeaderText="Ticket Title">
                                <asp:Label ID="lblTaskTitle" runat="server" Text='<%#Bind("TicketTitle") %>'></asp:Label>            
                        <asp:TemplateField HeaderText="Responsible Person" >
                                <asp:Label ID="lblComment" runat="server" Text='<%#Bind("FirstName") %>'></asp:Label>            

                        <asp:TemplateField HeaderText="">
                              <a href="ViewTicketNotes.aspx?TicketNum=<%# Eval("ProjectTicketNum") %>&&ProjectID=<%# Eval("tblProjects_ID") %>&&TblTicketsDetails_ID=<%# Eval("tblTicketsDetails_ID") %>&&TicketTitle=<%# Eval("TicketTitle") %>" target="_blank" >View Ticket Note(s)</a>


And The C# code for binding The ASP.Net Grid will be like below :::

DataTable ticketDetaisDT = TicketGateway.GetTicketDetailsForDashboard(projectId, taskStatus);
            gvTicketDetails.DataSource = ticketDetaisDT;


Populate DropDownList from List in SharePoint 2010 Visual Web Part

SPWeb web = SPControl.GetContextWeb(HttpContext.Current);
                SPList supervisorList = web.Lists["SupervisorList"];

                if(supervisorList !=null)
                    foreach (SPListItem items in supervisorList.Items)
                        string supervisorName = items["SupervisorName"].ToString();


Get all Groups of the Site and Groups of current loggedIn User in SharePoint 2010 porgrammatically

SPWeb web = SPContext.Current.Web;

//Retreiving the current logged in user
SPUser currentUser = web.CurrentUser;

// Get all the Groups of the Current Site
SPGroupCollection allGroups = web.Groups;

//Retrieving all the groups in the site/web by specific user
SPGroupCollection userGroups = currentUser.Groups;

string groupName_Member = "FirstSiteColl Members";
//Check is Current user a member of specific Group
bool memberInGroup = web.IsCurrentUserMemberOfGroup(userGroups[groupName_Member].ID);


How to add an Item in SP List in SharePoint 2010

here web is the SPWeb object that is my Root Site on which am going to add an Item in the list named MyList

using (SPSite currentSite = new SPSite(siteUrl))
SPWeb web = currentSite.RootWeb;
SPListItemCollection listItemCollection = web.Lists["MyList"].Items;
SPListItem spListItem = listItemCollection.Add();
spListItem["Title"] = "Anything";
spListItem["CustomerName"] = "Shahjada";
spListItem["Address"] = "Dhaka";


What is Site collection in SharePoint?

SharePoint site collection is a logical grouping of multiple SharePoint site or hierarchical site structure. For e.g. Sites of various teams or departments of an organization can be grouped logically in one site collection. A site collection consists of a top-level site and one or more sites below it. Each top-level site and any sites below it in the site structure are based on a site template and can have other unique settings and content. Hence, SharePoint site collection is a hierarchical set of sites that can be managed together. Sites within a site collection have common features, such as shared permissions, galleries for templates, content types, and Web Parts, and they often share a common navigation. A sub site can inherit permissions and navigation structure from its parent site or these can be specified and managed independently. Creation of sub sites can be delegated to users of a site collection, but a service administrator must perform creation of site collections


Calling MS SQL SP (with param) from C#

By like below , we can call SP from C# . Here I am gonna show how a SP with Param can be called , which is little tricky rather than simple SP .

My SP name is ::: spU_ChangeProjectStatus Click Here to view the SP .

The SP has a param named @tblProjects_ID , and I am filling that param with projectId variable .

public static bool ChangeProjectStatus(int projectId)
                using (SqlConnection cn = new SqlConnection(DBConnector.GetConnectionStringFromServer()))
                    SqlCommand cmd = new SqlCommand("spU_ChangeProjectStatus", cn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tblProjects_ID", SqlDbType.Int).Value = projectId;
                    return  true;
            catch (Exception ex)
                // Logger will be added here  Later
                return false;

MSSQL StoredProcedure using a parameter

This SP has been developed using MS SQL . This SP takes parameter @tblProjects_ID and Updates The Column bClosed WHERE TABLE ID = That parameter .

At The End of this Post , Click the Link to know how to Call this SP from C# .

CREATE PROCEDURE [dbo].[spU_ChangeProjectStatus]
@tblProjects_ID INT
SELECT @B_CLOSED=bClosed FROM tblProjects WHERE ID=@tblProjects_ID
UPDATE tblProjects SET bClosed='true' WHERE ID=@tblProjects_ID
UPDATE tblProjects SET bClosed='false' WHERE ID=@tblProjects_ID

Click Here to see How to Call SP from C#


Sql Transaction in C# : Part 2 – Doing in different Database

Here ,I have tried to show how you can use Sql Transaction for 2 Databases with different conection String . So you can use Sql Transaction on two DB Servers .

When you will go to first run this Sql Transaction in a Transaction Scope , you will get exception . For this, Click¬†Enable ‚ÄúDistributed Transaction Coordinator‚ÄĚ in SQL Server.

private void btnTransactionInDifferentDatabase_Click(object sender, EventArgs e)
            string connectString1 = "Data Source=masoom-PC;Initial Catalog=TransactionDB;Integrated Security=true;";
            string connectString2= "Data Source=masoom-PC;Initial Catalog=TransactionDB2;Integrated Security=true;";

            string commandText1 = "INSERT INTO Transaction_T VALUES('2','30.33','Babu')";
            string commandText2 = "INSERT INTO Transaction2_T VALUES('2','20.3','Babu')";
            int returnValue = 0;
            System.IO.StringWriter writer = new System.IO.StringWriter();

               using (TransactionScope scope = new TransactionScope())
                    using (SqlConnection connection1 = new SqlConnection(connectString1))

                        // Create the SqlCommand object and execute the first command.
                        SqlCommand command1 = new SqlCommand(commandText1, connection1);
                        returnValue = command1.ExecuteNonQuery();
                        writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
                        using (SqlConnection connection2 = new SqlConnection(connectString2))
                            // The transaction is escalated to a full distributed
                            // transaction when connection2 is opened.

                            // Execute the second command in the second database.
                            returnValue = 0;
                            SqlCommand command2 = new SqlCommand(commandText2, connection2);
                            returnValue = command2.ExecuteNonQuery();
                            writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                    // The Complete method commits the transaction. If an exception has been thrown,
                    // Complete is not  called and the transaction is rolled back.
            catch (TransactionAbortedException ex)
                writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message);
            catch (ApplicationException ex)
                writer.WriteLine("ApplicationException Message: {0}", ex.Message);

            label1.Text = writer.ToString();