Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Wednesday, May 13, 2020

.Net Database Connection String Samples

1. Web.Config Connection String

<configuration> <connectionStrings> 
<add name="myConnection" connectionString="server=localhost;database=mydatabase;" /> </connectionStrings> </configuration>

2. Get connection string using the ConfigurationManager class:
string conn = ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString;
3. EF Reference Connection String in DB Context Ctor:
public class DatabaseContext : DbContext {
public DatabaseContext() : base("myConnection")
{ } }
4. SQL Server Connection String Integrated Security:
<connectionStrings>
<add name="sqlServer" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True;" /> </connectionStrings>
5. SQL Server Connection String SQL Authentication:
<connectionStrings>
<add name="sqlServer" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;User Id=user;Password=pwd;" /> </connectionStrings>

6. My SQL Connection String:
<connectionStrings>
<add name="mySql" providerName="MySql.Data.MySqlClient" connectionString="Server=localhost;Database=MyDatabase;Uid=user;Pwd=pwd;" /> </connectionStrings>

7. Oracle Connection String
Data Source=ABC.Company.OraDSName;User Id=myUid;Password=mypwd;
8. SQL Server by Database Name
<add name="MyDBStage" connectionString="Database=MyDBStage;Server=.\sql2012;Integrated Security=SSPI;"
      providerName="System.Data.SqlClient" />
9. Connection String using Model:
<connectionStrings>
    <add name="MyDBEntities" connectionString="metadata=res://*/Data.MyDB.csdl|res://*/Data.MyDB.ssdl|res://*/Data.MyDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SP2013E\SPSQL;initial catalog=MyDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
10. MS Dynamics Connection String
<add name="Xrm" connectionString="Server=http://myCompany.crm5.dynamics.com; Domain=myCompany; Username=myUserId@myCompany.com.au; Password=Secret0102"/>
  </connectionStrings>
11. Named Connection String Integrated Security:
<add name="MyDBConnString" connectionString="Database=MyDB;Server=MyServer;Integrated Security=SSPI;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
12. References:

Connection Strings Samples
MS Connection Strings

Guide to Connection Strings

Thursday, March 26, 2020

Connect to SQLLocalDB from SSMS

SQLLocalDB is used by VS.Net for local db dev and to reduce the need for SQL Express. If you would like to use SSMS (SQL Server Management Studio) to query a SQLLocalDB, see below steps.


1. Find location of SqlLocalDB.exe, normally C:\Program Files\Microsoft SQL Server\[Version]\Tools\Binn 

2. Open cmd prompt in location;

3. run SqlLocalDB –?

4. run SqlLocalDB i

output:

5. Get Connection string:

run: SqlLocalDB i  MSSQLLocalDB

output

6. Copy Connection string: e.g:

np:\\.\pipe\LOCALDB#BBCAB010\tsql\query

7. Paste into SSMS:

example:

Thursday, September 27, 2018

Convert Excel Files to CSV in batch via context menu : Convert Many Excel files to CSV in 1 click

RunDTech.XLStoCSV.ReadMe

Summary: Convert xls and xlsx files to csv via a batch menu
Code: C#


1. Copy this folder to C:\Temp\DTech.XLStoCSV\
2. you should see this in C:\Temp\DTech.XLStoCSV\ http://prntscr.com/kzn325
3. Run reg file RunDTech.XLStoCS.reg
4. Right click on the "C:\Temp\DTech.XLStoCSV\Test" folder (if any issues copy test folder to C:\Temp\Test
5. You should see http://prntscr.com/kzn372
6. Click the XLStoCSV menu.
7. You should see the below output


     C:\Temp\DTech.XLStoCSV>"C:\Temp\DTech.XLStoCSV\RunDTech.XLStoCSV.bat" C:\Temp\Te
     st
      ******* Start DTech.XLStoCSV.64.exe C:\Temp\Test  Thu 09/27/2018 23:55:19.84
     DTech.XLStoCSV START
     START CSV  C:\Temp\Test\1.xlsx
     DONE CSV  C:\Temp\Test\1.xlsx
     START CSV  C:\Temp\Test\2.xls
     DONE CSV  C:\Temp\Test\2.xls
     DTech.XLStoCSV DONE
      ******* Start DTech.XLStoCSV.32.exe C:\Temp\Test  Thu 09/27/2018 23:55:19.93
     DTech.XLStoCSV START
     START CSV  C:\Temp\Test\1.xlsx
     DONE CSV  C:\Temp\Test\1.xlsx
     START CSV  C:\Temp\Test\2.xls
     DONE CSV  C:\Temp\Test\2.xls
     DTech.XLStoCSV DONE
     ****************DONE  Thu 09/27/2018 23:55:19.98 synch
    
     8. If you encounter any error try downloading MS Access Redist kit
https://www.microsoft.com/en-us/download/details.aspx?id=13255

9. I wrote this code in 1 night, tested on over 50 xls and xlsx files


Download Here

Tuesday, August 21, 2018

SSRS SharePoint Integrated Mode Report Migration to Azure O365 SPO Options

References:

https://technet.microsoft.com/en-us/library/sharepoint-online-service-description.aspx#bkmk_tableo365

https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-show-ssrs-reports-in-sharepoint-online/1d94a167-6ac7-49f3-9201-c6d04df4ff9a?auth=1

http://mitrai.com/tech-guide/ssrs-report-server-configuration-on-azure-virtual-machines/

http://www.axioworks.com/2018/08/display-sql-server-reporting-services-reports-in-sharepoint-online/?utm_source=collab365&utm_medium=collab365today&utm_campaign=daily_digest

Support Matrix

clip_image002

Summary of Options:

Option 1: Preserve SSRS Reports:

Migrate from On-Prem SSRS to O365 SSRS

Benefit: Preserve RDL’s, do not have to re-develop reports

Disadvantage: O365 SPO does not support SSRS Integrated Mode, Need new or add an SSRS app server instance, possible difficulties with SSO between O365 and SSRS

Option 2: Replace SSRS Reports :

Replace RDL Report with SharePoint Excel Services, PowerBI or equivalent.

Benefit: Implement reports using new technology, integrated with SPO, reduced migration strain of not needed addiitonal app server,

Disadvantage: Re-code from scratch each report, risky.

Details:

Option 1: Keep SSRS: Migrate from On-Prem SSRS to O365 SSRS

1. Document SSRS App Server Migration:

From

Product: SSRS

Environment: OnPrem

Mode: SharePoint Integrated Mode

Version: SQL Server 2012 SP1 : Detected on Report View Web Part: Version=11.0.3000.0

To

Product: SSRS

Environment: OnPrem

Mode: Native

Version: SQL 2016(?, TBD)

2. VM Hosting Environment Migration:

a. Create Azure SSRS VM

i. New Azure VM:

1. Install SQL Server

2. Install / Config SSRS per this ref

ii. Existing Azure VM with SQL Role:

1. Enable SSRS

2. Configure SSRS Instance per section Configure Sql Reporting Service

b. Azure SSRS Native instance is online!

i. https://[host]/ReportServer/

3. OMS Report Site and Report Migration : Migrate the OMS RDL from OnPrem SSRS Integrated to Azure SSRS Native

a. Publish Reports to Azure SSRS Native:

i. Migrate the RDL from OnPrem SSRS Integrated to Azure SSRS Native

ii. Download RDLs from https://iportal.seacorholdings.com/reports/OMS%20Reports

iii. Create VS.Net BI sln to enclose

iv. Upload the RDLs to Azure SSRS Native per this reference, section To Create and Publish Reports to the Azure Virtual Machine

b. OMS Report Site : Create OMS Reports in SharePoint Online :

i. Create “OMS Reports” sub site

4. Integrate Azure SSRS Native and O365 SPO

a. Integrate Reports from SSRS Azure into SPO

b. Add SharePoint Site Page with PageViewer per this ref

5. Concerns:

1. SSO from Azure SSRS to O365 SPO

2. Lack of SharePoint

Option 2: Replace SSRS Reports :

1. Develop New Reports to replace OMS

a. Convert to Excel

i. https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-show-ssrs-reports-in-sharepoint-online/1d94a167-6ac7-49f3-9201-c6d04df4ff9a?auth=1

b. Rewrite RDLs as SPO Excel Services

c. Rewrite RDLs as Power BI Desktop

d. Publish Reports to SPO OMS Reports Site


Option 2: Replace SSRS Reports :

1. Develop New Reports to replace OMS

a. Convert to Excel

i. https://answers.microsoft.com/en-us/msoffice/forum/all/how-can-i-show-ssrs-reports-in-sharepoint-online/1d94a167-6ac7-49f3-9201-c6d04df4ff9a?auth=1

b. Rewrite RDLs as SPO Excel Services

c. Rewrite RDLs as Power BI Desktop

d. Publish Reports to SPO OMS Reports Site

Thursday, April 19, 2018

SQL Server Management Studio Font Error : SSMS Cannot create an editor

When trying to run a query or open ssms, you receive this error, related to fonts:

Screenshot_2

Error Desc:

Object reference not set to an instance of an object. (Microsoft.VisualStudio.Editor.Implementation) ------------------------------ Program Location: at Microsoft.VisualStudio.Editor.Implementation.VsTextViewAdapter.SetScrollPosition(Int32 iBar, Int32 iFirstVisibleUnit)


To Resolve:

Solution 1 - Run Fixit tool for KB4074906

Microsoft has released a Fixit tool for KB4074906, available on Windows Update, WSUS, Microsoft Update Catalog, and as a direct download.

and/or
Solution 2 - Replace GlobalUserInterface.CompositeFont Manually

Manually replace corrupted font file with correct version.

  1. Download GlobalUserInterface.CompositeFont (default download for Windows 7 is %USERPROFILE%\Downloads).
  2. Launch cmd as Administrator and navigate to %windir%\Microsoft.NET\Framework\v4.0.30319\WPF\Fonts and run:
    xcopy /y %USERPROFILE%\Downloads\GlobalUserInterface.CompositeFont .
    (or copy and paste the GlobalUserInterface.Composite file through Windows Explorer to %windir%\Microsoft.NET\Framework\v4.0.30319\WPF\Fonts)
  3. Re-launch WPF application.
  4. Reboot machine and re-launch application if you still have trouble.

Friday, May 5, 2017

Git Cheat Sheet for TFS UsersEnter a post title

 

If you r moving to Git from TFS to SVN or another centralized VC system, then you may find the below command mapping helpful. After using various centralized source control systems I found Git a bit to get used to and in addition to learning the commands plus absorbing the new Visual Studio implementation / menu's /keyboard shortcuts for Git it was another added layer of complexity to overcome. Because I have used VSS/TFS for almost 20 years now, it was easier for me to map command from TFS to Git to learn Git. I found some resources below to help,

Basic Mapping

TFS Version Control

Git

Workspace

Repository (aka. “Repo”)

Get Latest  (First time)

Clone

Get Latest (After first time)

Pull

Check in

Commit + Push

Check out

(just start typing)

Branch

Branch

Merge

Merge

Code Review

“pull request”

Shelveset

Stash

Undo Pending Changes

"Undo" to revert to the last committed version

View History

"View History"

Label

Tag

Shelve

Stash - just local ! (on local machine ...)

Included changes

Staged

Excluded changes

not staged

Solution : Clean

Clean (remove unmarked files)

Merge and Resolve Conflicts

Rebase (see below def)

   

Hope this helps.

 

Cheat Sheet

CheatSheet

Git to TFS Overview

1. Many commands in Git, Local and Remote Repo

2. A Repo is just a folder in simple terms

3. There is no checkout - lock !

a. There is, but it is completely different

2. You can irreversible override changes made by somebody other (and by yourself as well)

a. • git commit --amend

b. • Basic support in VS 2015 •

c. Suitable for basic scenarios + 3rd party + extensions for Visual Studio

d. All else command line git ! :-)

3. Resources:

a. https://jeremybytes.blogspot.com/2014/12/git-integration-in-visual-studio-2013.html

b. https://www.dotnetweekly.com/git-cheat-sheet-for-tfs-users/

c. http://vsarbranchingguide.codeplex.com/releases/view/117523

d. https://www.git-tower.com/blog/git-cheat-sheet/

e. https://wikileaks.org/ciav7p1/cms/files/atlassian_git_cheatsheet.pdf

4. Other Resources

Git for beginners: The definitive practical guide

Git Cheat Sheet for TFS Users

Try git

Online git command line tool for training purposes

Git - Wikipedia

Git “home page”

Download for windows

Git Extensions Windows tool/UI + VS 2015 plugin

Other Windows GUI tools

5. ReBase:

rebase: takes all the work u have done in a branch & changes the history of that branch so that your changes are based on a different version of code. Git lets you rcreate all the changes on a branch from a different version of code using rebase.

https://channel9.msdn.com/series/Team-Services-Git-Tutorial/Git-Tutorial-Rebase

6. Commit and Merge Scenario:

Git: How to commit and merge

Hi,
I'm using Git just one year, and in second team, so here are three "algorithms" or "patterns" which I recognized yet:

1. Git+Gerrit+Jira, branch, commit --amend, upload

create branch per Jira item  

develop code

try to pull

solve merge conflicts

commit

upload (push + upload to gerrit )

... wait for gerrit review ...

change code (regarding gerrit review)

try to pull

solve merge commits

commit --amend (rewrite your commit - be careful )

upload

...

This is probably good solution for big projects with long term gerrit review process ...

2. Git+Gitlab, master, stash, merge, push

Probable suitable just for not so big project (lines/changes/developers)
Just one branch used locally.

You will change code.

When there is something new on origin/master you will try to pull

If it is impossible to pull, you will stash changes

pull

stash apply - solve merge conflicts 

continue if needed

push ( no review before push)

3. Git+Gitlab, master, commit, merge, push

The same as above but stash is not used, just commit and push.
But then merge commits will appears.
Probable suitable just for not so big project (lines/changes/developers)
Just one branch used locally.

You will change code.

When there is something new on origin/master you will try to pull

If is impossible to pull, you will commit changes

pull

solve merge conflicts 

continue if needed

push ( no review before push)

How do I? (from MSDN)

Visual Studio's Team Explorer lets you perform Open Team Explorer through the View menu in Visual Studio, or with the Ctrl+\, Ctrl+M hotkey.

Team Explorer and the Git cmd work together. updates changes reflected in the other.

TIP

Windows users: If you aren't using Visual Studio, installing Git for Windows will set up the Git credential manager for Windows. The credential manager makes it easy to authenticate with your Team Services repos.

While in Visual Studio, open cmd in repo via Team Explorer’s Connect view. Right-click local repo | Open Command Prompt

clip_image003[10]

Repos

How do I ?

Git command line

Visual Studio

Create a repo in a new folder

git init foldername

Select the Connect button ( clip_image004[10] ) in Team Explorer to open the Connect view, then select New under Local Git repositories

Create a repo with code in an existing folder

git init foldername
git add --all
git commit -m "Initial commit"

Create the repo from the command line, then open Team Explorer's Connect view and select Add under Local Git repositories

Create a repo from an existing Visual Studio solution

git init foldername
cd foldername
git add --all
git commit -m "Initial commit"

Open the solution and select Publish ( clip_image005[10] ) from the status bar in the lower right.

Create a new repo in your Team Project

Not applicable

From the web, select Code, then select the drop-down next to the current repo name and choose New Repository...

Clone a repo into a local folder

git clone URL foldername

Select Clone under Local Git repositories in Team Explorer's Connect view

Clone a repo in your Team Project

git clone URL foldername

Open the Connect view in Team Explorer and right click the Git repo in your Team Project under the account name. Select Clone...

Add an existing repo to Visual Studio

Not applicable

Open the solution file in Visual Studio (this will automatically add the repo to Team Explorer) or select Add under Local Git repositories in the Connect view

Delete the Git repo and history, but keep the current version of the files

Delete the hidden .git folder created at the root of the repo

Delete the hidden .git folder created at the root of the repo from Windows Explorer or the command line

Delete a local repo and all files

Delete the folder containing your repo from your computer's filesystem

Close any open solutions using files in the repo, then delete the folder containing your repo from your computer's filesystem.

Delete a repo in your Team Project

Not applicable

Select the settings icon ( clip_image006[14] ) in Team Services/TFS, then select the Version Control tab. Find the Git repository to delete and select the ... next to the name. Choose Delete Repository from the options.

Add a remote

git remote add name url

Open the repository using the Connect view in Team Explorer, then open the Settings view in Team Explorer. Select Repository Settings, and select Add under Remotes

Update a remote

git remote set-url nameurl

Open the repository using the Connect view in Team Explorer, then open the Settings view in Team Explorer. Repository Settings, and select Edit under Remotes

Branches

How do I ?

Git command line

Visual Studio

Create a new branch

git branch branchname

Open the Branches view in Team Explorer, then right-click a branch and choose New Local Branch From...

Swap to a different branch

git checkout branchname

Open the Branches view in Team Explorer, then double click a local branch. Alternatively, click the current branch name from the status bar and select a different branch.

Delete a local branch

git branch -d branchname

Open the Branches view in Team Explorer, then right-click the branch and select Delete. You must be checked out to a different branch than the one you want to delete.

Delete a remote branch

git push origin --delete branchname

Open the Branches view in Team Explorer, expand the remote that has the branch you want to delete. Right-click the remote and select Delete Branch from Remote

Set a default branch in your Team Services/TFS repo

Select the settings icon on the web ( clip_image006[15] ), then select the Version Control tab. Select your Git repository, then select the ... next to the branch name and choose Set as default branch

Same as command line

Commits

How do I ?

Git command

Visual Studio

Create a new commit

git commit -m "message"

Open the Changes view in Team Explorer. Stage changes by right-clicking on the modified files and selecting Stage. Enter a commit message and select Commit Staged.

Amend the last commit with staged changes

git commit --amend -m "Updated message"

Open the Changes view in Team Explorer, stage your changes, then select Amend Previous Commit from the Actions drop-down.

Stage all file changes

git add --all

Open the Changes view in Team Explorer. Select the + icon in the Changes list to stage all changes for the next commit.

Stage a specific file change

git add filename

Open the Changes view in Team Explorer. Stage changes by right-clicking on the changed file and selecting Stage.

Review unstaged changes

git status --untracked

Open the Changes view in Team Explorer. Unstaged changes are listed under Changes section.

Delete a file

git rm filename
git commit -m "Deleted filename"

Delete the file through Solution Explorer, the command line, or any other means. Right-click the deleted file in Team Explorer's Changes view and select Stage . Select Commit Staged to commit the deletion.

Move a file

git mv filename
git commit -m "Moved filename"

Move a file from one location to another in your repo through Solution Explorer, the command line, or any other means. Right-click the moved file in Team Explorer's Changes view and select Stage . Select Commit Staged to commit the move.

Tag a commit

git tag -a tagname -m "description"

Open the Changes view in Team Explorer, then choose View history..." from the Action drop-down. Locate the commit in thie History view, then right-click and select Create Tag

Compare files and versions

How do I ?

Git command

Visual Studio

Compare the current contents of a single file and the contents in the last commit

git diff HEAD filename

Right-click on the change in the Changes view in Team Explorer and select Compare with unmodified.

Compare your current version with a branch

git diff branchname

Right-click on a file in Solution Explorer and select View History..., then select both on the latest commit on your current branch and the latest commit on the remote branch. Right-click and select Compare

Compare changes between two branches

git diff branchname1branchname2

Right-click on a file in Solution Explorer and select View History..., then select the most recent commits for both branches. Right-click and select Compare

Share code with push | Team Services Git tutorial
Update your code with fetch and pull | Team Services Git tutorial
Resolve merge conflicts | Team Services Git tutorial

Merge and rebase

How do I ?

Git command

Visual Studio

Merge a branch into the current branch

git merge branchname

In the Team Explorer Branches view, right-click the branch you want to merge and select Merge From... Verify the options set and select Merge.

Merge a remote branch into the current branch

git pull origin branchname

In the Team Explorer Branches view, right-click the remote branch you want to merge and select Merge From... Verify the options set and select Merge.

Rebase your current branch onto the history of another branch

git rebase branchname

In the Team Explorer Branches view, right-click the branch you want to rebase your current branch changes onto and select Rebase Onto.."

Do an interactive rebase of the last n commits

git rebase -i HEAD~n (Linux and macOS)
git rebase -i "HEAD^n" (Windows)

Use command line

Cherry-pick a commit into the current branch

git cherry-pick commitID

Open the Changes view in Team Explorer, then choose View history..." from the Action drop-down. Locate the commit in thie History view, then right-click and select Cherry-pick

 

Undo

WARNING : Must be experienced

How do I ?

Git command

Visual Studio

Revert all changes and roll back to the most recent commit

git reset --hard HEAD

Open the Changes view in Team Explorer. Select Actions and choose **View History from the drop-down. Right-click the commit where the branch is currently located and select Reset and Delete changes....

Revert staging of files, but keep file changes

git reset --mixed HEAD

Open the Changes view in Team Explorer. Select Actions and choose **View History from the drop-down. Right-click the commit where the branch is currently located and select Reset and Keep changes....

Delete untracked files

git clean -f

In the Changes view in Team Explorer, right-click the files to remve under Changes marked with [add] and select Delete.

Reset your local branch to the most recent commit on a remote branch

git reset --hard remote/branchname
(for example, git reset --hard origin/master)

Right-click the branch from Team Explorer's Branches view and select Reset and Delete changes....

Revert a commit pushed to a remote repository

git revert commitID

Open the Changes view in Team Explorer. Select Actions and choose **View History from the drop-down. Right-click the commit to revert and select Revert.