Delete Multipale Record Using Checkbox in asp.net c#

Delete Multipale Record Using Checkbox in asp.net c#

MVC AND ENTITY FRAMEWORK TUTORIAL

http://www.entityframeworktutorial.net/EntityFramework4.3/execute-stored-procedure-using-dbcontext.aspx
http://mvc4beginner.com/Tutorial/MVC-Application-Execution-Flow.html

how to create database with script using c#

using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
 private void CreateDatabase()
    {
        String str;
        string constr = "Data Source=111.111.1.111;Initial Catalog=master; User Id=sa;Password=password;";
        SqlConnection myConn = new SqlConnection(constr);
        str = "CREATE DATABASE MyDatabase";
        SqlCommand myCommand = new SqlCommand(str, myConn);
        try
        {
            myConn.Open();
            myCommand.ExecuteNonQuery();        
        }
        catch (System.Exception ex)
        {      
        }
        finally
        {
            if (myConn.State == ConnectionState.Open)
            {
                myConn.Close();
                string sqlConnectionString = "Data Source=111.111.1.111;Initial Catalog=MyDatabase; User Id=sa;Password=password;";
                FileInfo file = new FileInfo("C:\\esage.sql");
                string script = file.OpenText().ReadToEnd();
                SqlConnection conn = new SqlConnection(sqlConnectionString);
                Server server = new Server(new ServerConnection(conn));
                server.ConnectionContext.ExecuteNonQuery(script);
            }
        }



    }

datareader with sqlhelper and run time label bind in grid

SqlConnection con = new SqlConnection(obj.conString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "SHOWDATA";
        cmd.Parameters.Add("@Root", SqlDbType.Int).Value = DropDownList2.SelectedValue;
        cmd.Connection = con;

        try
        {
            con.Open();
            GridView2.EmptyDataText = "No Records Found";
            GridView2.DataSource = cmd.ExecuteReader();
            GridView2.DataBind();


            foreach (GridViewRow row in GridView2.Rows)
            {
                string id = ((HiddenField)row.Cells[2].FindControl("hnfID")).Value.ToString();
                Label lbtsanstha = ((Label)row.Cells[2].FindControl("lblsanstah"));
                SqlDataReader rdr = SqlHelper.ExecuteReader(obj.conString, CommandType.Text, "select * from SansthUserDetail where userid='" + id + "'");
                while (rdr.Read())
                {
                    string col1Value = rdr["Sanstha"].ToString();                
                    lbtsanstha.Text = lbtsanstha.Text + ' ' + col1Value + ",".ToString();                
                }
                lbtsanstha.Text = lbtsanstha.Text.Substring(0, lbtsanstha.Text.Length - 1);
            }

        }
        catch (Exception ex)
        { throw ex; }

        finally
        { con.Close(); con.Dispose(); }

insert update select storprocedure

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[AddUpdateCustomer]

      @CustomerID NCHAR(5),

      @ContactName NVARCHAR(30),

      @CompanyName NVARCHAR(40)

AS

BEGIN

      SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM Customers WHERE CustomerID = @CustomerID)

    BEGIN

            UPDATE [Customers]

            SET [CompanyName] = @CompanyName

               ,[ContactName] = @ContactName

            WHERE CustomerID = @CustomerID

    END

    ELSE

    BEGIN

            INSERT INTO [Customers]

           ([CustomerID]

           ,[CompanyName]

           ,[ContactName])

        VALUES

           (@CustomerID

           ,@CompanyName

           ,@ContactName)

    END

   

    SELECT [CustomerID]

          ,[CompanyName]

          ,[ContactName]

      FROM Customers         

END

Delete duplicate record in database

1:-

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
 FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

2.

WITH TempUsers (FirstName,LastName, duplicateRecordCount)

AS

(

SELECT FirstName,LastName,

ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount

FROM dbo.Users

)

DELETE

FROM TempUsers

WHERE duplicateRecordCount > 1

GO

n tire Architecture

http://www.mindstick.com/Articles/d36ceb0f-018c-4979-b2f5-a4a1e616cb5b/?N-Tier%20Architecture%20in%20ASP.NET

how add connection string

</configSections>
    <appSettings>
        <add key="strLocalCon" value="Data Source=EASY-857AC062F0;Initial Catalog=aaa;Integrated Security=True"/>
    <add key="strLocalCon1" value="Data Source=EASY-857AC062F0;Initial Catalog=aa;Integrated Security=True"/>
    </appSettings>
    <connectionStrings>
        <add name="VeePurchases" connectionString="Data Source=EASY-857AC062F0;Initial Catalog=aa;Integrated Security=True"/>
    <add name="VeeSale" connectionString="Data Source=EASY-857AC062F0;Initial Catalog=aa;Integrated Security=True"/>
    </connectionStrings>

//
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["VeePurchases"].ToString());

how send sms using api

protected void btnSend_Click(object sender, EventArgs e)

    {

       

        HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://www.sitename.in/pushsms.php?UserName=abc&Password=xyz&Type=Individual&To=" + txtMobileNo.Text + "&Mask=S H A M &Message=" + txtMessage.Text) as HttpWebRequest;

        HttpWebResponse response = (HttpWebResponse)request.GetResponse();

        Stream receiveStream = response.GetResponseStream();

        Encoding encode = System.Text.Encoding.GetEncoding("utf-8");

        StreamReader readStream = new StreamReader(receiveStream, encode);

        string a = readStream.ReadToEnd().ToString();

        response.Close();

        readStream.Close();

    }

    #region characters as byte array

   

    public string UTF8ByteArrayToString(byte[] characters)

    {

        UTF8Encoding encoding = new UTF8Encoding();

        string constructedString = encoding.GetString(characters);

        return constructedString;

    }

    #endregion

how pass sp perametrs in sqlhelper

           SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "spname", new SqlParameter("@username", txtuname.Text.ToString().Trim()));

change gridview checkbox color using javascript

 <script type="text/javascript">
        function changecolor(Id) {
            if (document.getElementById(Id).checked == true) {
                document.getElementById(Id).parentNode.style.backgroundColor = 'green';
            }
            else {
                document.getElementById(Id).parentNode.style.backgroundColor = 'red';
            }


        }
    </script>
</head>

onclick="javascript:changecolor(this.id)"

how get database table and no of rows in table

SELECT Name, DCount("*", Name) AS NumberOfRows
FROM MSysObjects
WHERE TYPE IN (1, 4, 6)
AND Name NOT LIKE "MSys*"
ORDER BY Name

how get max id in sql

            SELECT ISNULL(MAX(ISNULL(id, 0)), 0) + 1 FROM temp2

exeption handling in sql

 BEGIN TRANSACTION
        BEGIN TRY
            -- Generate a Constraint violation Error.
            DELETE FROM IRCContactDetails
                    WHERE id = 6
        END TRY
        BEGIN CATCH
            SELECT
                    ERROR_NUMBER() AS ErrorNumber,
                    ERROR_SEVERITY() AS ErrorSeverity,
                    ERROR_STATE() AS ErrorState,
                    ERROR_MESSAGE() AS ErrorMessage

            IF @@TRANCOUNT > 0
                    ROLLBACK TRANSACTION;                   
        END CATCH;
       
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;

how conver number into words

public string retWord(int number)
    {

        if (number == 0) return "Zero";
        if (number == -2147483648) return "inus Two Hundred and Fourteen Crore Seventy Four Lakh Eighty Three Thousand Six Hundred and Forty Eight";
        int[] num = new int[4];
        int first = 0;
        int u, h, t;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        if (number < 0)
        {
            sb.Append("Minus");
            number = -number;
        }
        string[] words0 = { "", "One ", "Two ", "Three", "Four ", "Five", "Six ", "Seven", "Eight ", "Nine " };
        string[] words = { "Ten", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen " };
        string[] words2 = { "Twenty ", "Thirty", "Forty", "Fifty ", "Sixty ", "Seventy", "Eighty", "Ninety" };
        string[] words3 = { "Thousand ", "Lakh", "Crore" };
        num[0] = number % 1000; // units
        num[1] = number / 1000;
        num[2] = number / 100000;
        num[1] = num[1] - 100 * num[2]; // thousands
        num[3] = number / 10000000; // crores
        num[2] = num[2] - 100 * num[3]; // lakhs
        for (int i = 3; i > 0; i--)
        {
            if (num[i] != 0)
            { first = i; break; }
        }

        for (int i = first; i >= 0; i--)
        {
            if (num[i] == 0) continue;
            u = num[i] % 10; // ones
            t = num[i] / 10;
            h = num[i] / 100; // hundreds
            t = t - 10 * h; // tens
            if (h > 0) sb.Append(words0[h] + "Hundred ");
            if (u > 0 || t > 0)
            {
                if (h > 0 || i == 0) sb.Append("and ");
                if (t == 0)
                    sb.Append(words0[u]);
                else if (t == 1)
                    sb.Append(words[u]);
                else
                    sb.Append(words2[t - 2] + words0[u]);
            }
            if (i != 0) sb.Append(words3[i - 1]);
        }
        return sb.ToString().TrimEnd();

    }

how genrate 8 disit rendom number

    public string Get8Digits()
    {
        var bytes = new byte[4];
        var rng = RandomNumberGenerator.Create();
        rng.GetBytes(bytes);
        uint random = BitConverter.ToUInt32(bytes, 0) % 100000000;
        return String.Format("{0:D8}", random);
    }

DELETE TRIGGER

CREATE TRIGGER [dbo].[Student_Update]
ON [dbo].StudMarks
AFTER DELETE
AS
BEGIN
INSERT INTO StudMarks2 SELECT Regdno,MathsMarks,StatsMarks,CompScienceMarks,TotMarks,AvgMarks FROM DELETED
END
 

java script funcation for open panel

<script type="text/javascript">
        function ff() {
            document.getElementById("a").style.display = "block";
        }
        function ff2() {
            document.getElementById("a").style.display = "none";
        }
    </script>

 <div class="GridviewDiv" id="a" style="display: none">
contanet div that will be open
</div>

call
    <asp:DropDownList ID="DropDownList1" runat="server" onmouseover="javascript:ff()" >
    </asp:DropDownList>

auto referesh grid and update panel

.aspx;
<asp:ScriptManager ID="ScriptManager1" runat="server" />
                    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                        <ContentTemplate>
                            <asp:Timer ID="AutoRefreshTimer" runat="server" Interval="5000" OnTick="AutoRefreshTimer_Tick" />
                            <asp:GridView ID="gvrecords" runat="server" AutoGenerateColumns="False" AllowSorting="true"
                                Width="540px" CssClass="Gridview">
                                <Columns>
                                    <asp:TemplateField HeaderText="UserName">
                                        <ItemTemplate>
                                            <asp:Label ID="lblFirstname" Text='<%# Eval("name") %>' runat="server" />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                            </asp:GridView>
                        </ContentTemplate>
                    </asp:UpdatePanel>


.cs

  protected void AutoRefreshTimer_Tick(object sender, EventArgs e)
    {
        BindUserDetails();(fill grid funcation)
      
    }

Fill Dataset Using Reader

SqlDataReader dr;
            SqlConnection connection = new SqlConnection(sqlConnectString);
            SqlCommand command = new SqlCommand(sqlSelect, connection);
            connection.Open( );
            dr = command.ExecuteReader( );

            // Create the DataSet using the DataSet.Load( ) method
            DataSet ds = new DataSet( );
            ds.Load(dr, LoadOption.OverwriteChanges,
                new string[ {"Department", "Contact"});

            int tableCount = 0;
            foreach(DataTable dt in ds.Tables)
            {
                Console.WriteLine("Table {0}; Name = {1}", tableCount++,
                    dt.TableName);
                foreach(DataRow row in dt.Rows)
                {
                    for(int i = 0; i < dt.Columns.Count; i++)
                        Console.Write("{0} = {1};", dt.ColumnsIdea.ColumnName,
                            rowIdea);
                    Console.WriteLine( );
                }
                Console.WriteLine( );
            }

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey( );