.NET GENES

Hello Everyone, Normally when working with real world information, we tend to have projects or tasks that will show employees with their supervisors or managers viewed in hierarchical structure. If the given project is a web application, the jsTree.js fits for this requirement. This article will show you how to populate a jsTree from SQL Server database table called ContosoRetailsDW which is a Microsoft sample database. It already has a table specifically DimEmployee that we can play with. To begin with lets define the models used. The JsTreeModel class contains the information used by the jsTree while the clsResourceManagers class receives the result from the query. public class EmpManagerTreeViewModel { public clsResourceManagers ResourceManager { get ; set ; } public JsTreeModel EmployeeTreeModel { get ; set ; } public EmpManagerTreeViewModel() { ResourceManager = new clsResourceManagers(); EmployeeTreeModel = new JsTreeModel(); } } public class clsRes

Good afternoon fellow developers! I have been working with Bootstrap Modals before and it's time to demonstrate its significance using a simple ASP.NET MVC CRUD (Create Update Delete) application project using Entity Framework Database First approach. First is to create a basic BookDetails table on your SQL Server instance. USE [DemoDB] GO /****** Object: Table [dbo].[BookDetails] Script Date: 11/2/2020 12:26:53 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BookDetails]( [BookSerialNo] [int] IDENTITY (1,1) NOT NULL , [BookISBN] [ nchar ](15) NULL , [BookTitle] [varchar](120) NULL , [BookAuthor] [varchar](60) NULL , [BookPublisher] [varchar](50) NULL , [BookCategory] [varchar](20) NULL , PRIMARY KEY CLUSTERED ( [BookSerialNo] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] GO Next is to create an ASP.

Aloha fellow developers! I have this scenario wherein I need to display row numbers in my result set given that the rows don't have a ID column and each row number represents the month number of a specific month. SELECT A.fsyear_num as 'Fiscal Year' , [MonthName] FROM CTE_GrossProfit_SalesByClass_Current_FSYear A I know that row number is the solution but I need to tweak this to handle my requirement. After reading the forums and docs, I found out that adding a select statement with COUNT() inside the Over By clause is the solution. SELECT A.fsyear_num as 'Fiscal Year' , ROW_NUMBER() Over( Order BY ( Select Count ( * ))) AS MonthNum, [MonthName] FROM CTE_GrossProfit_SalesByClass_Current_FSYear A ORDER BY MonthNum ASC

Hello fellow programmers! Here's a simple T-SQL statement that will check if a column value is null or empty. IIF( c .BillPhone is null , '' , IIF(Len( c .BillPhone) > 0, c .BillPhone , '' )) as BillingPhone First, it will check if the field is null. If true, it will return empty string. Else, another checking will be performed and this time if it's an empty string. If not an empty string, show the column value. Else show an empty string. That's it! :-)

Hi All, While restoring a new database backup which resides from the server in US, I encountered an error which is 'SQL Server error 4214 - BACKUP LOG cannot be performed because there is no current database backup' . Glad that I had a skype session with our software architect in the US and presented to him the issue. The solution that worked was when performing the restore of the backup database was to go to the Options of Restore Database dialog and uncheck these two checkboxes 'Take tail-log backup before restore checkbox' and 'Leave source database in the restoring state checkbox' . Then check 'Check Overwrite the existing database (WITH REPLACE)' box. After that, the restore operation succeeded.

Recently, our systems administrator has just finished migrating our workstations from an old domain to a new domain. But this caused an issue wherein most of the databases are not accessible. I've done several workarounds from stackoverflow and non of them worked. The solution that worked is to add my new domain user to the databases using SQL Server Authentication (sa) and set my new domain user as dbOwner.

Good Evening Gents! In the office, we were using SQLDataReader class to read from SQL Server database in a straight-forward manner. The SQLDataReader has several properties that are commonly used such as HasRows and FieldCount. However in one of my task, I want to retrieve the total number of rows returned from the SQLDataReader object and there's no such property that support this. Doing some search led me to this link How to get number of rows using SqlDataReader in C# . The one that worked for me was the answer using select @@ROWCOUNT query. I modified his answer by writing my own method that will accept a List of SQLParameters given that the original query requires parameters. private static void GetTotalRowCount( string query, string connectionString, ref int totalRows, List<SqlParameter> sqlParameters) { try { using ( var sqlCon = new SqlConnection(connectionString)) { sqlCon.Open(); var cmd = sqlCon.CreateCommand(); cmd.CommandText = q

Good evening Gents! I was given a task to export 100+GB of files from an MSSQL DB of which these files are saved in a table using Varbinary/Blob column. I've made some spike applications using BCP and .NET CLR but to no avail all the spike applications don't work since I don't have full permission to the database. The only solution that work for me was using the OLE Automation Procedures. So in order to export files from Blob, I'll present the steps below using AdventureWorks2012 database. 1. Enable OLE Automation Procedures by running script below. Use AdventureWorks2012 Go EXEC sp_configure 'show advanced options' , 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures' , 1; GO RECONFIGURE; GO 2. Run the stored procedure script below. The script will return the LargePhoto of a specific product. USE AdventureWorks2012 GO IF OBJECT_ID( 'SP_AdventureWorks_Export_Blob' , 'P' ) IS NOT NULL DROP PROCEDURE S

Good afternoon! Before I go into details, this post is solely based on the solution provided in this link Bulk Update In C# . The purpose of this tutorial is to provide step by step approach on how bulk update can be achieved using SqlBulkCopy, C#.NET And SQL Server. First is to create a simple table in your database that holds some fictitious records. USE [testdatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [ID] [int] NOT NULL , [Name] [varchar](50) NULL , [Address] [varchar](50) NULL ) ON [ PRIMARY ] GO Database table with temp records Next is to create a console app that will perform the bulk update of records. The key to solving performance issues in updating records is to use a temporary table approach. class Program { static void Main( string [] args) { var listPerson = new List<Person> { new Person() {Id = 1001, Name = "James A." , Address = "US" }, new Person(

I have been generating insert data scripts through SQL Server Management Studio on the fly. However if you don't want to include the ID which is an identity column,this feature is not defined in the IDE. The workaround for this is to insert the records without the ID column into a temporary table and generate insert data scripts using that temp table. Select PartNum, PartDescription, Model, Category, SaleQTY, Price Into tmptblParts From tblParts The query above will insert records from tblParts to temp table tmptblParts. The column ID is omitted. After you have executed that statement, then generate insert scripts using tmptblParts.

Given that you'll have to delete records from an SQL server history table, you might come across with the issue as mentioned in the title of the post. After doing some research and experiments, there are three steps to delete records from a temporal history table. a. First is to remove the main table's System Versioning option. ALTER TABLE [dbo].[tblParts] SET ( SYSTEM_VERSIONING = OFF ) b. Next is to delete the records of the temporal history table. Delete from dbo.tblPartsHistory WITH (TABLOCKX); c. Then Set again the system versioning and history table of the main table. ALTER TABLE [dbo].[tblParts] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[tblPartsHistory])) Solved.. :-)

In the event that you need to check a T-SQL field value if it's a GUID or UniqueIdentifier, the same procedure is used in applying the logic in a where clause. SELECT CartObjectID, CartName, CartQuantity, CASE When TDD.FieldData like REPLACE ( '00000000-0000-0000-0000-000000000000' , '0' , '[0-9a-fA-F]' ) Then ( Select CartValue From tblCartList where ListID = TDD.FieldData ) ELSE TDD.FieldData END As FieldData

Hello, While working with an ASP.NET MVC app using SQL Server 2016, this error appears in the browser "Spatial types and functions are not available for this provider because the assembly 'Microsoft.SqlServer.Types' version 10 or higher could not be found. ". After searching the forums, the solution that worked for me was to add Microsoft.SqlServer.Types dependentAssembly element inside the assemblyBinding node in the web.config. <dependentAssembly> <assemblyIdentity name= "Microsoft.SqlServer.Types" publicKeyToken= "89845dcd8080cc91" culture= "neutral" /> <bindingRedirect oldVersion= "10.0.0.0-11.0.0.0" newVersion= "14.0.0.0" /> </dependentAssembly>

Here's how to update multiple rows in one SQL statement using MSSQL. UPDATE tblEmp SET empSalary = empSrc.empSalary, empLevel = empSrc.empLevel FROM dbo.tblEmployees AS tblEmp INNER JOIN ( VALUES ( 001 , 10000 . 00 , 10 ), ( 002 , 32000 . 00 , 15 ), ( 003 , 9500 . 00 , 3 ) ) AS empSrc (empID, empSalary, empLevel) ON tblEmp.empID = empSrc.empID; Reference: How to update multiple columns of multiple rows in one SQL statement

Good morning! In the event that you need to replace your queries using Union such as below: Use DBJersonsHW go SELECT DISTINCT [EmpName] AS [EmployeeName], EmpAddress As [Address] FROM tblEmployeesMain UNION SELECT DISTINCT [EmpName] AS [EmployeeName], EmpAddress As [Address] FROM tblEmployeesMetro ORDER BY [EmpName]; The alternative for that is to use Full Outer Join as presented below: Use DBJersonsHW go SELECT Distinct Coalesce(tblEmployeesMain.EmpName, tblEmployeesMetro.EmpName) AS EmployeeName, Coalesce(tblEmployeesMain.EmpAddress, tblEmployeesMetro.Address) AS Address, FROM tblEmployeesMain FULL Outer JOIN tblEmployeesMetro on tblEmployeesMain.EmpName = tblEmployeesMetro.EmpName order by EmpName Cheers! :-)

Good evening! An issue was brought up by a developer on how to convert rows of DateTime values into columns using MS Access DB with functionality similar to pivoting of MSSQL.The problem has an added complexity since specific portions of the DateTime will be extracted too. See original post here: Converting Columns to Rows in MS Access . The data presented is similar to a Timesheet entry wherein an employee has login/logout records. The solution presented in the thread involves usage of subquery and joins designed for MS Access Db. I modified the accepted answer to a T-SQL query using Group By, Max() function, Coalesce() and without subquery. SELECT LogInfo.fldMachineId, Year (LogInfo.fldLogDate) as [ Year ], Month (LogInfo.fldLogDate) as [ Month ], Day (LogInfo.fldLogDate) as [ Day ], Max (Coalesce( Convert ( varchar ( 5 ), T2.fldLogDate, 108 ), '' )) as TimeIn1, Max (Coalesce( Convert ( varchar ( 5 ), T3.fldLogDate, 108 ), '' )) as TimeOut1,

Hi all, In this demo, I have dummy XML files saved to an XML column in a table. The files have the same structure of nodes, except that some files have altered price value of 104.95. The is the structure of the dummy XML file. <catalog> <book id= "bk101" > <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44.95</price> <publish_date>2000-10-01</publish_date> <description> An in-depth look at creating applications with XML. </description> </book> <book id= "bk102" > <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5.95</price> <publish_date>2000-12-16</publish_date> <description> A former architect battles corporate zombies, an ev