.NET Programming With Me: 2013

SQL Server: Repair SQL Server Database marked as Suspect or Corrupted

By Anaya at December 12, 2013

A SQL Server database can enter suspect mode for several reasons: the device hosting the data or log files went offline, the files became unavailable or corrupted, or the server shut down uncleanly mid-transaction. When this happens the database is inaccessible and SQL Server marks it as suspect to prevent further damage.

This post walks through the six steps to bring a suspect database back online. The examples use a database named test; substitute your own database name throughout.

Before you start: Work through these steps on a non-production copy first if at all possible. Step 5 uses REPAIR_ALLOW_DATA_LOSS, which, as the name states, may discard data to resolve corruption. If you have a recent backup, restoring from it is always the safer path.

1 Reset the suspect flag

sp_resetstatus clears the suspect and read-only bits in the database status flags without altering any other database state. This is a prerequisite before any of the subsequent ALTER DATABASE commands will succeed.

EXEC sp_resetstatus 'test'

2 Set the database to emergency mode

Emergency mode makes the database read-only and restricts access to members of the sysadmin fixed server role only. This lets you inspect and repair the database without other connections interfering during the process.

ALTER DATABASE test SET EMERGENCY

3 Check integrity across all objects

Run DBCC CHECKDB to get a full picture of the corruption before attempting any repair. The output will tell you which objects are affected and whether the damage can be repaired with or without data loss. Read this output carefully before proceeding to step 5.

DBCC CheckDB ('test')

4 Set the database to single user mode

Repair operations require exclusive access to the database. WITH ROLLBACK IMMEDIATE terminates any existing connections and rolls back their open transactions immediately rather than waiting for them to complete.

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

5 Repair the errors

This command instructs SQL Server to repair all errors it found during the CHECKDB run. The REPAIR_ALLOW_DATA_LOSS option is the most aggressive repair level and will deallocate corrupt pages if necessary to make the database consistent again.

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

Warning: REPAIR_ALLOW_DATA_LOSS is a last resort. Any pages that cannot be repaired in place will be discarded, meaning rows or entire tables could be permanently lost. If the CHECKDB output in step 3 showed that a less destructive repair level (REPAIR_REBUILD) is sufficient for your errors, use that instead.

6 Return the database to multi-user mode

Once the repair completes without error, lift the single-user restriction so normal application connections can resume.

ALTER DATABASE test SET MULTI_USER

Wrapping up

The full six-step sequence in one place, ready to adapt with your database name:

EXEC sp_resetstatus 'test'

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB ('test')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('test', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

After bringing the database back online, run a full DBCC CHECKDB one more time without a repair option to confirm there are no remaining consistency errors. Follow that with a full database backup so you have a clean recovery point before the database returns to production use.

Got a question or ran into a problem? Drop a comment below and I will reply.

SQL Server: Change the text to Title Case

By Anaya at December 06, 2013

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description: Change the text to Title Case
-- Referenced : http://joezack.com/2008/10/20/mysql-capitalize-function/
-- =============================================
CREATE FUNCTION CAP_FIRST
(
@myText VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @len INT;
DECLARE @i INT;

SET @len = LEN(@myText);
SET @myText = LOWER(@myText);
SET @i = 0;

WHILE (@i < @len)
BEGIN
IF (SUBSTRING(@myText,@i,1) = ' ' OR @i = 0)
BEGIN
IF (@i < @len)
SET @myText = LEFT(@myText,@i) + '' + UPPER(SUBsTrING(@myText,@i + 1,1)) + '' + RIGHT(@myText,@len - @i - 1);
END
SET @i = @i + 1;
END;

RETURN @myText;

END
GO

USAGE:
SeleCT dbo.CAP_FIRST(emp_name) as [Name]

VB.NET : Passing Parameter to CrystalReport

By Anaya at October 27, 2013

When a Crystal Report is designed with parameters, you need to supply those values in code before the report renders. If you leave it to the viewer to prompt the user at runtime, you lose control over formatting, validation, and the source of the value. Passing parameters programmatically keeps all of that in your hands.

This pattern comes up constantly in VB.NET Windows Forms applications that use Crystal Reports for invoices, term reports, date-filtered summaries, or any report that takes a runtime value.

The fix

Build a ParameterFields collection, populate it with one ParameterField per report parameter, and assign the collection to the viewer before it loads the report. Each parameter value is wrapped in a ParameterDiscreteValue, which represents a single concrete value as opposed to a range.

Dim paramFields  As New CrystalDecisions.Shared.ParameterFields()
Dim paramField   As New CrystalDecisions.Shared.ParameterField()
Dim discreteVal  As New CrystalDecisions.Shared.ParameterDiscreteValue()

paramField.ParameterFieldName = "date"
discreteVal.Value             = TermReport.PrintDate
paramField.CurrentValues.Add(discreteVal)
paramFields.Add(paramField)

CrystalReportViewer1.ParameterFieldInfo = paramFields

The string passed to ParameterFieldName must match the parameter name defined inside the Crystal Report exactly, including case. A mismatch will cause the viewer to fall back to prompting the user or throw a parameter missing exception at render time.

Note: Assign ParameterFieldInfo before setting ReportSource on the viewer, or before calling RefreshReport if the report is already loaded. Setting parameters after the report has already rendered has no effect until the next refresh.

Passing multiple parameters

The same pattern scales to any number of parameters. Create a new ParameterField and ParameterDiscreteValue pair for each one, add them all to the same ParameterFields collection, then assign the collection once at the end:

Dim paramFields As New CrystalDecisions.Shared.ParameterFields()

' First parameter: report date
Dim fieldDate  As New CrystalDecisions.Shared.ParameterField()
Dim valDate    As New CrystalDecisions.Shared.ParameterDiscreteValue()
fieldDate.ParameterFieldName = "date"
valDate.Value                = TermReport.PrintDate
fieldDate.CurrentValues.Add(valDate)
paramFields.Add(fieldDate)

' Second parameter: student ID
Dim fieldId As New CrystalDecisions.Shared.ParameterField()
Dim valId   As New CrystalDecisions.Shared.ParameterDiscreteValue()
fieldId.ParameterFieldName = "studentId"
valId.Value                = TermReport.StudentId
fieldId.CurrentValues.Add(valId)
paramFields.Add(fieldId)

CrystalReportViewer1.ParameterFieldInfo = paramFields

Tip: If your report uses subreports that share the same parameter name, you may need to set the ReportName property on each ParameterField to target the correct subreport. Leave ReportName as an empty string to target the main report.

When to use this

Any report that declares one or more parameters in the Crystal Reports designer and needs those values supplied from application data rather than user input.
When the parameter value comes from a form field, a database query result, or a session variable that the user should not be able to override directly.
When you need to suppress the Crystal Reports parameter prompt dialog entirely for a cleaner user experience.

Found this useful? Drop a comment below if you have a variation or ran into an edge case I did not cover.

Crystal Reports: Separating number and string from a string

By Anaya at October 07, 2013

/* Formula for CrystalReports */

The output from the dataset is as:
Output: 1.2.5.0.1*Computer

Now, i need to do is separate number from the string using the '*'
String:
MID({journal_view.ac_name}, INSTR({journal_view.ac_name},"*")+1) // Outputs : Computer

Number:
LEFT({journal_view.ac_name}, INSTR({journal_view.ac_name},"*")-1) // Outputs : 1.2.5.0.1

C#: Number Validation For DataGridView

By Anaya at October 06, 2013

private void DataGridView1_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
{
if (e.Control is TextBox)
{
TextBox tb = e.Control as TextBox;
tb.KeyPress += new KeyPressEventHandler(tb_KeyPress);
}
}

void tb_KeyPress(object sender, KeyPressEventArgs e)
{
if (!(char.IsDigit(e.KeyChar)))
{
if (e.KeyChar != '\b') // allow the backspace key
{
e.Handled = true;
}
}
}

SQL Server: WHERE Clause in INSERT Statement

By Anaya at September 02, 2013

insert into testMy (id, myname)
SELeCT 1, 'Me'
WHEre NOT Exists(select * from TestMy WHERe MyName = 'Me')

or, we can use insert and update in single statement without the use of stored procedure.

if exists(select * from testMy where id = 3)
update testMy set myname = 'They' where id = 3
else
insert into testMy (id, myname) values(3, 'They')

SQL Server: Like Operator in DateTime

By Anaya at August 29, 2013

Actually i've not used the like operator for searching since it tales timepart also. The easy way to search for a record using datepart from the datetime field is as:

SELECT *
FROM my_sales
WHERE DATEPART(yy, date) = 2013
AND DATEPART(mm, date) = 08
AND DATEPART(dd, date) = 29)

I find this way easy to read, as it ignores the time component, and you don't have to use the next day's date to restrict your selection.

SQL Server: Generating the Schema and Data with SqlServer 2008 R2 Management Studio

By Anaya at August 01, 2013

1. Right click on the database.
2. Select Tasks -> Generate Scripts.
3. Select the "Script entire database and all database objects" radiobutton.
4. Click Next.
5. In the "Scripting Options" screen, click the "Advanced" button.
6. In the "General" category change the "Types of data to script" value to "Schema and Data".
7. Click OK.
8. Click Next.

Now, you will get the schema scripts as well as the tabulated data of the database.

C# : Downloading Files using ASP.NET

By Anaya at June 21, 2013

Downloading Files From ASP.NET using C#.

protected void DownloadButton_Click(object sender, EventArgs e)
        {
            string fileName = "img1.jpg";
            fileDownload(fileName, Server.MapPath("~/Images/img1.jpg"));
        }

private void fileDownload(string fileName, string fileUrl)
        {
            Page.Response.Clear();
            bool success = ResponseFile(Page.Request, Page.Response, fileName, fileUrl, 1024000);
            if (!success)
                Response.Write("Downloading Error!");
            Page.Response.End();
        }

        public static bool ResponseFile(HttpRequest _Request, HttpResponse _Response, string _fileName, string _fullPath, long _speed)
        {
            try
            {
                FileStream myFile = new FileStream(_fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                BinaryReader br = new BinaryReader(myFile);
                try
                {
                    _Response.AddHeader("Accept-Ranges", "bytes");
                    _Response.Buffer = false;
                    long fileLength = myFile.Length;
                    long startBytes = 0;

                    int pack = 10240; //10K bytes
                    int sleep = (int)Math.Floor((double)(1000 * pack / _speed)) + 1;
                    if (_Request.Headers["Range"] != null)
                    {
                        _Response.StatusCode = 206;
                        string[] range = _Request.Headers["Range"].Split(new char[] { '=', '-' });
                        startBytes = Convert.ToInt64(range[1]);
                    }
                    _Response.AddHeader("Content-Length", (fileLength - startBytes).ToString());
                    if (startBytes != 0)
                    {
                        _Response.AddHeader("Content-Range", string.Format(" bytes {0}-{1}/{2}", startBytes, fileLength - 1, fileLength));
                    }
                    _Response.AddHeader("Connection", "Keep-Alive");
                    _Response.ContentType = "application/octet-stream";
                    _Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(_fileName, System.Text.Encoding.UTF8));

                    br.BaseStream.Seek(startBytes, SeekOrigin.Begin);
                    int maxCount = (int)Math.Floor((double)((fileLength - startBytes) / pack)) + 1;

                    for (int i = 0; i < maxCount; i++)
                    {
                        if (_Response.IsClientConnected)
                        {
                            _Response.BinaryWrite(br.ReadBytes(pack));
                            Thread.Sleep(sleep);
                        }
                        else
                        {
                            i = maxCount;
                        }
                    }
                }
                catch
                {
                    return false;
                }
                finally
                {
                    br.Close();
                    myFile.Close();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

Original article from ASP.NET

ASP.NET: Increasing the fileUpload size

By Anaya at June 20, 2013

The 4MB default is set in machine.config, but you can override it in you web.config.
For instance, to expand the upload limit to 20MB, you'd do this:

Since the maximum request size limit is there to protect your site, it's best to expand the file-size limit for specific directories rather than your entire application. That's possible since the web.config allows for cascading overrides.
You can add a web.config file to your folder which just contains the above, or you can use the tag in your main web.config to achieve the same effect:

SQL Server: Aggregate Function in an Update Query

By Anaya at June 04, 2013

The following code shows us how to use an aggregate function while updating a table.
I've used the sum function.

update t1
set t1.fieldName1 = t2.NewFieldName
from table1 t1
inner join (select fieldName1, sum(fieldName2) as 'NewFieldName' from Table99
where group by fieldName1) as t2
on t1.fieldName1 = t2.fieldName1
GO

Example:
update t1
set t1.total_balance = t2.totalPay
from employee t1
inner join (select empId, sum(payment) as totalPay
from employee_payment
where employee_payment.[year] = 2013
group by empId) as t2
on t1.empId = t2.empId
GO

VB.NET : Count the lines of code in the project.

By Anaya at May 23, 2013

1. Open the Powershell on your windows.
2. Go to the project directory
3. Execute the following line.

c:\project\(dir -include *.vb -recurse | select-string .).Count

Here, "c:\project\" is the directory having my vb.net project.

.NET : How to fix the 'Resources' is not a member of 'My'

By Anaya at March 03, 2013

The problem is caused by the following reasons:
1. If the resource is listed more than once.
2. If the resource folder is not found or deleted.
3. If any of the file inside the resources folder is deleted from outside the project.

The resources.resx file is located inside the MyProject folder of the project.

Try removing the duplicate entries from the resources.resx file.
Or, avoid deletion/exclusion of resources folder.

It may help you solve the problem.
In my case, there was a duplicate entry so removing it resolved my problem.

SQL Server: Generate delete queries for entire tables in a database.

By Anaya at March 01, 2013

SELECT 'DELETE FROM ' +TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

This query helps us to view the delete queries for all the tables in the used database.

C# : Filling a DataTable from a SqlDataReader

By Anaya at February 28, 2013

The following code snippet shows you how to fill a DataTable from an SqlDataReader with only a few lines of code.

private DataTable GetDataTable()
{
  string sql = "SELECT * FROM MyTable";
  using (SqlConnection myConnection = new SqlConnection(connectionString))
  {
    using (SqlCommand myCommand = new SqlCommand(sql, myConnection))
    {
      myConnection.Open();
      using (SqlDataReader myReader = myCommand.ExecuteReader())
      {
        DataTable myTable = new DataTable();
        myTable.Load(myReader);
        myConnection.Close();
        return myTable;
      }
    }
  }
}

This code executes a DataReader (a SqlDataReader in this case, but you can also use other types, like an OleDbDataReader). It then passes this open reader into the Load method of the DataTable that takes care of copying the data from the reader into the DataTable.

Referenced from : here

SQL Server : Display rows as columns

By Anaya at February 12, 2013

Let’s start with a fictional scenario.

In this case we have lots of vendors who report in their daily income to us, for this we have a simple table that looks like this.

create table DailyIncome(VendorId nvarchar(10), IncomeDay nvarchar(10), IncomeAmount int)

--drop table DailyIncome

Nothing odd here, just the Vendor id, the day of the week they are referring to and what the income on that day was.

So let’s fill it with some data.

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('SPIKE', 'MON', 300)

insert into DailyIncome values ('FREDS', 'SUN', 400)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'TUE', 200)

insert into DailyIncome values ('JOHNS', 'WED', 900)

insert into DailyIncome values ('SPIKE', 'FRI', 100)

insert into DailyIncome values ('JOHNS', 'MON', 300)

insert into DailyIncome values ('SPIKE', 'SUN', 400)

insert into DailyIncome values ('JOHNS', 'FRI', 300)

insert into DailyIncome values ('FREDS', 'TUE', 500)

insert into DailyIncome values ('FREDS', 'TUE', 200)

insert into DailyIncome values ('SPIKE', 'MON', 900)

insert into DailyIncome values ('FREDS', 'FRI', 900)

insert into DailyIncome values ('FREDS', 'MON', 500)

insert into DailyIncome values ('JOHNS', 'SUN', 600)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('SPIKE', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'FRI', 300)

insert into DailyIncome values ('JOHNS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'SAT', 800)

insert into DailyIncome values ('SPIKE', 'TUE', 100)

insert into DailyIncome values ('SPIKE', 'THU', 300)

insert into DailyIncome values ('FREDS', 'WED', 500)

insert into DailyIncome values ('SPIKE', 'SAT', 100)

insert into DailyIncome values ('FREDS', 'SAT', 500)

insert into DailyIncome values ('FREDS', 'THU', 800)

insert into DailyIncome values ('JOHNS', 'TUE', 600)

Now, if we select out the flat data that we have, we will get the following:

VendorId IncomeDay IncomeAmount

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

SPIKE FRI 100

SPIKE MON 300

FREDS SUN 400

SPIKE WED 500

SPIKE TUE 200

JOHNS WED 900

SPIKE FRI 100

JOHNS MON 300

SPIKE SUN 400

...

SPIKE WED 500

FREDS THU 800

JOHNS TUE 600

A lot of data that it is hard to make something useful of, for example, say that we would like to know what the average income is for each vendor id?

Or what the maximum income is for each day for a particular vendor? Enter the pivot table.

To find the average for each vendor, run this query:

select * from DailyIncome

pivot (avg (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as AvgIncomePerDay

Outcome:

VendorId MON TUE WED THU FRI SAT SUN

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

FREDS 500 350 500 800 900 500 400

JOHNS 300 600 900 800 300 800 600

SPIKE 600 150 500 300 200 100 400

The find the max income for each day for vendor SPIKE, run this query:

select * from DailyIncome

pivot (max (IncomeAmount) for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) as MaxIncomePerDay

where VendorId in ('SPIKE')

Outcome:

VendorId MON TUE WED THU FRI SAT SUN

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

SPIKE 900 200 500 300 300 100 400

The short story on how it works using the last query.

select * from DailyIncome -- Colums to pivot

pivot (

max (IncomeAmount) -- Pivot on this column

for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN])) -- Make colum where IncomeDay is in one of these.

as MaxIncomePerDay -- Pivot table alias

where VendorId in ('SPIKE') -- Select only for this vendor

The above post is taken from Pivot tables in SQL Server.

VB.NET : Alphanumeric increment

By Anaya at January 07, 2013

I wanted to generate an alphanumeric value for a number of repeated times. Each time the number and the alphabet should add 1 to their value

For example: 475Y, 476Z, 477A, 478B and so on..

I did it as following:

Dim alphaNumericNum As String = String.Empty
Dim number As Integer = 475
Dim alphabet As String = "Y"
Dim loopValue as integer = 5

For index As Integer = 0 To loopValue - 1
alphaNumericNum = number.ToString & alphabet

MsgBox("The new Symbol No. is " & alphaNumericNum)

number = number + 1

If Asc(alphabet) + 1 <= 90 Then
alphabet = Chr(Asc(alphabet) + 1)
Else
alphabet = "A"
End If

Next

Source (Zipped file)