Get All table name from Database in ms access

SELECT Name FROM MSysObjects WHERE

(Left([Name],1)<>"~") AND (Left([Name],4) <> "MSys") AND ([Type] In (1, 4, 6)) ORDER BY Name

                          

Sql server table columns with comma separated string using COALESCE

//Sql Query

DECLARE @ColStr VARCHAR(MAX)

SELECT @ColStr = COALESCE(''+@ColStr +',' ,'') +'['+ name+']' FROM sys.columns where object_name(object_id)='TableName'

Print @ColStr

// Result

 [Active],[StateCode],[StateName]

Create a field primary key to access database using c#

// Create with Single Filed

CREATE UNIQUE INDEX myNewIndex ON test (ID) WITH PRIMARY

// Create with Multiple Filed

CREATE UNIQUE INDEX myNewIndex ON test (ID,uname,ut) WITH PRIMARY





// Drop key index

Drop INDEX myNewIndex ON test

Divide by zero error encountered in sql

--// Details

SELECT COALESCE(SUM(isnull(ColumnName,0)) / NULLIF(SUM(isnull(ColumnName,0)),0), 0)

--// Function

SELECT COALESCE(SUM(isnull(0,0)) / NULLIF(SUM(isnull(0,0)),0), 0)

--// Result

0.000000

How to save and send email pdf on server map path using iTextsharp in asp.net

// Create data table

public void Tempdt()

    {

        DataTable dt = new DataTable();

        dt.Columns.AddRange(new DataColumn[3] {

                                  new DataColumn("OrderId"),

                                  new DataColumn("Product"),

                                  new DataColumn("Quanity")});

        dt.Rows.Add(101, "Name1", 5);

        dt.Rows.Add(102, "Name2", 2);

        dt.Rows.Add(103, "Name3", 12);

        dt.Rows.Add(104, "Name4", 9);

        dt.Rows.Add(104, "Name5", 9);

        Save_send_Email(dt);

   

    }

// save and send pdf

private void Save_send_Email(DataTable dt)

    {

        using (StringWriter sw = new StringWriter())

        {

            using (HtmlTextWriter hw = new HtmlTextWriter(sw))

            {

                string companyName = "ASPSnippets";

                int orderNo = 2303;

                StringBuilder sb = new StringBuilder();

                sb.Append("<table width='100%' cellspacing='0' cellpadding='2'>");

                sb.Append("<tr><td align='center' style='background-color: #18B5F0' colspan = '2'><b>Order Sheet</b></td></tr>");

                sb.Append("<tr><td colspan = '2'></td></tr>");

                sb.Append("<tr><td><b>Order No:</b>");

                sb.Append(orderNo);

                sb.Append("</td><td><b>Date: </b>");

                sb.Append(DateTime.Now);

                sb.Append(" </td></tr>");

                sb.Append("<tr><td colspan = '2'><b>Company Name :</b> ");

                sb.Append(companyName);

                sb.Append("</td></tr>");

                sb.Append("</table>");

                sb.Append("<br />");

                sb.Append("<table border = '1'>");

                sb.Append("<tr>");

                foreach (DataColumn column in dt.Columns)

                {

                    sb.Append("<th style = 'background-color: #D20B0C;color:#ffffff'>");

                    sb.Append(column.ColumnName);

                    sb.Append("</th>");

                }

                sb.Append("</tr>");

                foreach (DataRow row in dt.Rows)

                {

                    sb.Append("<tr>");

                    foreach (DataColumn column in dt.Columns)

                    {

                        sb.Append("<td>");

                        sb.Append(row[column]);

                        sb.Append("</td>");

                    }

                    sb.Append("</tr>");

                }

                sb.Append("</table>");

                StringReader sr = new StringReader(sb.ToString());

                Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);

                HTMLWorker htmlparser = new HTMLWorker(pdfDoc);

                using (MemoryStream memoryStream = new MemoryStream())

                {

                    PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);

                    pdfDoc.Open();

                    htmlparser.Parse(sr);

                    pdfDoc.Close();

                    byte[] bytes = memoryStream.ToArray();

                    memoryStream.Close();

                  

                 

                    MailMessage mm = new MailMessage("from@gmail.com", "to@gmail.com");

                    mm.Subject = "iTextSharp PDF";

                    mm.Body = "iTextSharp PDF Attachment";

                    mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "iTextSharpPDF.pdf"));

                    mm.IsBodyHtml = true;

                    SmtpClient smtp = new SmtpClient();

                    smtp.Host = "smtp.gmail.com";

                    smtp.EnableSsl = true;

                    NetworkCredential NetworkCred = new NetworkCredential();

                    NetworkCred.UserName = "from@gmail.com";

                    NetworkCred.Password = "passowrd";

                    smtp.UseDefaultCredentials = true;

                    smtp.Credentials = NetworkCred;

                    smtp.Port = 587;

                    smtp.Send(mm);

                    File.WriteAllBytes(Server.MapPath("~/Files/ddd1.pdf"),bytes);

                }

            }

        }

    }

Symbols using Key Board

Alt + 0153..... ™... trademark symbol
Alt + 0169.... ©.... copyright symbol
Alt + 0174..... ®....registered trademark symbol
Alt + 0176 ...°......degre­e symbol
Alt + 0177 ...±....plus-or­-minus sign
Alt + 0182 ...¶.....paragraph mark
Alt + 0190 ...¾....fractio­n, three-fourths
Alt + 0215 ....×.....multi­plication sign
Alt + 0162...¢....the cent sign
Alt + 0161.....¡..... .upside down exclamation point
Alt + 0191.....¿..... ­upside down question mark
Alt + 1...........smiley face
Alt + 2 ......☻.....bla­ck smiley face
Alt + 15.....☼.....su­n
Alt + 12......♀.....f emale sign
Alt + 11.....♂......m­ale sign
Alt + 6.......♠.....s­pade
Alt + 5.......♣...... ­Club
Alt + 3............. ­Heart
Alt + 4.......♦...... ­Diamond
Alt + 13......♪.....e­ighth note
Alt + 14......♫...... ­beamed eighth note
Alt + 8721.... ∑.... N-ary summation (auto sum)
Alt + 251.....√.....s­quare root check mark
Alt + 8236.....∞..... ­infinity
Alt + 24.......↑..... ­up arrow
Alt + 25......↓...... ­down arrow
Alt + 26.....→.....ri­ght arrow
Alt + 27......←.....l­eft arrow
Alt + 18.....↕......u­p/down arrow
Alt + 29......↔... left right arrow

Alt+190.....¥

Add Pre-Post fixed string in sql

Syntax of REPLICATE Function :

REPLICATE ( character_expression ,integer_expression )

character_expression is a string to repeat. 

integer_expression is number of times the character_expression to be repeated.

Return type of REPLICATE function is varchar data type.  

Example 1 : Use of REPLICATE function with select 

SELECT REPLICATE('Syntax-Example', 3) 

select (case when LEN([Columns Name])<5 then REPLICATE('0',5-LEN([Columns Name]))+[Columns Name] else Tally end)Tally from [Table Name] 

// Result :
//Before :  815
// After : 00815

Example 2 : Use of REPLICATE function with Update

update [Table Name]  set [Columns Name]=(case when LEN([Columns Name])<5 then REPLICATE('0',5-LEN([Columns Name]))+[Columns Name] else [Columns Name]end)