You must Sign In to post a response.
  • Category: ASP.NET

    For truncating table what statement is used?

    I have created one sp that tuncate table.Table name is dynamic.Here I used dynamic sql.Sp is working fine.I want to execute that sp from C#.net(from cs file). I know executenonquery returns no of row affected.executenonquery is used for insert,update and delete command.Exectesclare is used for select which has only one cell.EceuteReader is used for selecting multiple record.What shall i use that tell my tuncate table clause executed properly or not?
  • #762496
    Hi

    You can use ExecuteNonQuery. This truncate is also a kind of delete but with out any where condition.

    SqlConnection cn=new SqlConnection("connection string");
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "your sp";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection=cn;
    try
    {
    cn.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    cmd.ExecuteNonQuery();

    }
    catch (Exception ex)
    {
    ex.ToString();
    }
    finally
    {
    cn.Close();
    }

    Sridhar Thota.
    Editor: DNS Forum.

  • #762497
    Hello Pinky,

    You can use ExecuteNonQuery for truncating table.

    Sample code below:

    SqlCommand cmd;
    SqlConnection con;
    con=new SqlConnection("Connection String here");

    try
    {
    con.Open();
    string strSql = "TRUNCATE TABLE table_name";
    cmd=new SqlCommand(strSql,con);
    cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.ToString());
    }
    finally
    {
    con.Close();
    }


    Hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #762498
    Thanks Sridhar and Nirav for ur reply.I used same.

  • #762499
    Hi

    You can follow this steps for execute Truncate Procedure in C# code.


    Create Table dynamic
    (
    Id int primary key identity(1,1),
    ProductName varchar(30)
    )

    Insert into dynamic VALUES('Mouse')
    Insert into dynamic VALUES('Optical-Mouse')
    Insert into dynamic VALUES('Keyboard')
    Insert into dynamic VALUES('101-Keyboard')


    CREATE PROCEDURE SP_TRUNCATETABLE
    AS
    Truncate table dynamic

    -- How to select
    Select * from dynamic

    --How to call From Sql Side
    Select * from dynamic
    Exec SP_TRUNCATETABLE



    Server Side Code c#



    protected void BtDel_Click(object sender, EventArgs e)
    {
    string constr = ConfigurationManager.AppSettings["s"].ToString();
    using (SqlConnection con = new SqlConnection(constr))
    {
    using (SqlCommand cmd = new SqlCommand("SP_TRUNCATETABLE", con))
    {
    cmd.CommandType = CommandType.StoredProcedure;
    con.Open();
    int result = cmd.ExecuteNonQuery();
    }
    }

    }



    I have attached snapshot given below.

    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

    Delete Attachment


This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post.