SharePoint List From SQL Server Table (External Content Type with SharePoint Designer)

SharePoint List From SQL Server Table

---

Many time we need to show custom data in a SharePoint list and you do not have an option to store data in SharePoint list .In most of the cases your data source can be used in some other legacy system and you have centralized data in SQL server  table. Now challenge is to integrate this data in SharePoint so that a SharePoint user can view ,modify and delete this data from a SharePoint list .
To handle this situation SharePoint provides External Content Types . We will see how to configure ECT with SharePoint Designer and show a list from SQL server table  .
Let’s start we have following customer table in our data base with some dummy values . We will make this table shown as a SharePoint list in few clicks with the help of SharePoint designer.
                   





Step 1 . To start with open your site in SP Designer .To do this either click on Site Action –> Edit in SharePoint Designer option from your site or open SP Designer 2010 and browse for your site .
Step 2.  Select External Content Type from  Site Objects and then click on the first icon in the new group of the  ribbon i.e  External Content Type
                   


Step 3. This  will open a ‘New External Content Type’ window , change the Name and Display Name in the External Content Type Information tab of the newly opened window.
Step 4. Click on the link ‘Click here to discover external data sources and define operations.’
                   

Step 5. This will open a new window with the name that you have provided in last screen for external content type , Click on ‘Add Connection’. In the External Data Source Type Selection choose SQL Sever. Fill the details for Database Server ,Database Name and user identity to connect with database .
                   
Step 6 . If you have provided the correct details , now you can see your database in the ’Data Source Explorer’  tab. Expand your database –> navigate till the table which you want to display as SharePoint List . Right click on the table and select ‘Create All Operations’ . With this option you can also edit and delete items from SharePoint list . The changes will be atomically reflect in SQL server after any operation in the SharePoint list .  

            

Step 7.  It will open up  ‘All Operations’  window . Click Next  and it will display all the fields of the selected table , change any properties if you want otherwise just click Next. The next section is for filter  parameter configuration, just click Finish.Save your external content type in SP Designer.
Step 8. Now our external content type is ready and we just need a list . Select ‘List and Libraries’  from ‘Site Objects’ tab and then click on ‘External List’ command from the ribbon .

              

Step 9. You will now see a ‘External Content Types  Picker ‘ window . Select your external content type which you have just created and then click Ok. Provide name and description  for the list in ‘Create External List’ window , Click oaky and you are done .Save everything in SP Designer go to your site  and you can can find newly created list under Lists section .
                    







You can also edit , delete and add new items from SharePoint List to the SQL server database .

---

SharePoint 2013 – Navigation using Managed Metadata (Term Store)

July 20, 2012Benjamin Niaulin, End User, Managed Metadata, SharePoint 2013, SPF 2013admin

---

You may also be interested in: Documentation Toolkit for SharePoint by Acceleratio Ltd.

---

Editor’s note: Contributor Benjamin Niaulin is a SharePoint Specialist at Groupe GSoft Inc. Follow him @bniaulin
Brand new in SharePoint 2013 is the ability to apply friendly urls to pages, goodbye to /Lists/Posts/Post.aspx?ID=568 urls. Another huge improvement to Navigation is that we can manage it using the Term Store or Managed Metadata.
You will find the Term Store very familiar, it looks and acts the same so creating terms won’t be too hard if you are used to SharePoint 2010. But in case it’s your first time, Managed Metadata means that “Terms” or words, if you will, centrally stored and managed. You can delegate the rights to manage the Term Group and provide ways of writing a word based on the language.

Here is how to manage SharePoint 2013 Navigation using Friendly URLs

1. Click on Site Settings (now on the top right)

2. Next, click on Navigation under “Look and Feel” usually on the top right

3. You will find yourself in the Global and Current Navigation settings menu. The Global Navigation is the menu at the top and the Current Navigation is the one on the left.
For each of these you can choose to use “Managed Navigation” or “Structural Navigation”
The one we are looking at today is Managed.


4. What we need to do next is create our Navigation items in the Term Store. Lower in the page you will find an option to Open the “Term Store Management Tool”.

5. In the Term Store Management Tool, use the left pane to navigate to the Term Set you want to use for the navigation.

6. Using the little drop down arrow on the Term Set, Create Terms to reflect your desired navigation.


7. Ffor each Term (navigation links) we have just created, use the tabs at the top to manage “Navigation” and “Term-Driven Pages”.

You can choose between two Navigation Node Types:

• Simple Link or Header
• Term-Driven Page with Friendly URLThe Simple Link or Header is the more traditional hyperlink to an item or create a heading to group links.
The Term-Driven is a little more powerful as it allows for friendly urls.

8. Next we will set the Friendly URL by moving to the next tab for the term, Term-Driven Pages. There you will find options to change the friendly url as well as setting the target page for the hyperlink. (Can even set SEO properties! – cool)

Click on Save when you are done

9. Time to see the results, notice my url

10. Don’t forget that we are using the Managed Metadata Service (Term Store) which means you can have more than 1 level in the navigation

The Value of Using ReSharper with Visual Studio

ReSharper is an absolutely fantastic complement to the Visual Studio development experience that provides immense value. It's a requisite in my day to day work with Visual Studio. In many ways ReSharper features form the basis for the criteria I use when evaluating a development environment for any platform.

ReSharper helps me reduce bugs.

Many bugs can be avoided with good automated testing up front. Testing also helps prevent recurrence of bugs. One barrier to testing is the wide variety of tools and the lack of integration with development environments. This barrier decreases the dividends testing can provide and justifiably in some cases costs more than it is worth, or at least mentally seems to.
ReSharper lowers this barrier by providing integration of a consistent test runner. Out of the box it supports NUnit (one of the oldest .net testing packages) and MSTest from Microsoft. Plugins can provide coverage for other frameworks. Recently ReSharper even added support for testing JavaScript via QUnit and Jasmine frameworks! The testing tools are one of my absolute favorite features. I can run a test or set of tests with a keystroke and get results in an integrated, visually appealing test runner. Running tests with this level of granularity and integration is a very rare feature in testing platforms. It allows me to thoroughly test components before assembling and testing the entire product.
If I have a bug in existing code, features like Stack Trace Explorer make it easy to track down and identify the issue.
Lowering the barrier to testing makes automated testing more economical versus manual verification. At the end of the day I can work faster and the resulting product is more reliable. In my opinion, this testing integration alone covers the cost of the license. I save at least 2 hours a week using automated testing with ReSharper over F5 debugging. It also leads to at least one less production bug a month and gives me immense confidence in what I do allowing me to work even faster.

ReSharper helps me maintain code and avoid "re-writes"

Poorly maintained code is difficult to change, difficult to understand and in some cases difficult to read! This is often referred to as spaghetti code. These design problems lead to stagnant progress evolving a software product to the ever changing demands of business. Writing, understanding, maintaining and evolving software is hard work and is often tedious.
ReSharper reduces repetitive and tedious tasks evolving code by providing a plethora of automated refactorings. It's challenging enough to decide WHAT refactoring to apply, let alone to carry it out. Most beneficial refactorings can be automated and ReSharper does just that with integration and shortcut keys! Having these tools available at a keystroke means I don't wait to change code that is troublesome. ReSharper allows me to avoid the need for a "re-write" by continuously evolving the code to meet the needs of business. Paired with easy automated testing and I have confidence that my refactorings aren't introducing bugs!
Maintainable code means I don't have to worry about re-writes, ReSharper can even help tackle the worst of spaghetti code and evolve it to something more maintainable over time. The costs and risks in a "re-write" are phenomenal. Day to day refactorings save me at least an hour every week manually refactoring my code and it expands my ability to do so. Continuous refactoring also reduces the likelihood of a delay from code that is difficult to change.

ReSharper helps keep my code consistently formatted

Style preferences aside, most developers agree consistency is a good idea. Each project often has a particular style that is an artifact of the time it was created in. Often there's no reason to change the style, but it can be extremely difficult to enforce it when I adopt new styles.
ReSharper allows me to setup shared settings for multiple machines or when I work on teams. They are applied hierarchically so I can have whatever level of consistency I desire. These settings allow decisions in the past to be enforced via simple Code Cleanup commands and don't require me to spend time worrying if I am following consistent rules for any particular project. Instead, I can focus on the content and get consistency for free!
I save at least 30 minutes each day, I know this because I use the silent cleanup ctrl+e,f immediately followed by ctrl+s and find myself doing it in applications that don't support it :) If I stumble on code that is inconsistent, I setup rules and apply cleanup as needed to get a better idea of what it does.

ReSharper helps me comprehend code faster

As systems grow, so does the code behind them. This can become difficult to organize and even more important to navigate. Folders and projects only go so far and the Solution Explorer is a very crude way to get where you need to go, it's not very keyboard friendly! I spend a significant amount of my time reading code, so having a barrier to navigation is extremely unproductive.
ReSharper provides a bevy of features to ease navigation. A single Navigate To shortcut with contextual support can get me where I am going quickly. The Go to Type, Go to File and other Go to features make searching and jumping to any place in code lightning fast. ReSharper even supports integrated decompilation and navigation with dotPeek. Paired with refactoring and testing, superb navigation further allows me to evolve and comprehend code.
ReSharper also makes identifying unused code with solution analysis a simple task and helps remove it with Safe Delete. Unused code just leads to confusion and begets more unused code!
Every day I navigate to different places in my code 100s of times, ReSharper provides features that shave multiple seconds off each navigation, the amount of time is incomprehensible, at least 30 minutes every day, and that's only estimating about 4 seconds saved per navigation, it takes longer than that to do anything with the solution explorer and a mouse.

ReSharper helps me type less

Even the best of languages can be verbose, C# is not immune and wow is all I can say about Visual Basic. Why should I spend time typing syntax when I could be focused on content?
ReSharper provides many completions to make writing code less burdensome. It also supports several levels of templating and generation to reuse common patterns. These templates are customizable and can be shared with other team members with ease. One of my favorite templates is a test method snippet. When I want to add a test I don't have to type the scaffolding, I can focus on the content. I write more fine grained tests because of this which increases maintainability of my tests! I also thoroughly enjoy code generation of method declarations so I can focus on what the method does!
Less typing saves me even more time every day, at least an hour a day versus manually typing everything out, the biggest savings is in avoiding compilation and other mistakes that shift my focus from content.

Summary

Of the above, which are by no means comprehensive of the value I get out of ReSharper, I've enumerated a savings of at least 13 hours per week. That's time I re-invest in content instead of tedium. This is likely a conservative estimate as I feel my productivity is more than doubled with ReSharper.
If you are a developer, what would these savings mean to you? If you fund development teams in your company, what would these savings mean for your company?