robzyc

[C#] On Common StProc Code

2008-07-21T14:19:36-07:00

Great, my thread has been hijacked!

Purpose-of-website FAIL.
Posting-in-appropriate-thread FAIL
Explaining-problem-properly FAIL

Running-before-you-can-walk WIN!

[C#] On Common StProc Code

2008-04-02T16:29:36-07:00

Yeah same here, I added it ages ago then kind of forgot about it since not much has been happening on here. Recently started trying to dedicate some time to becoming more "agile", looking into TDD and obviously a huge part of this is the ability to refactor code effectively.

Hence me coming on here and just going for it, hopefully a few others will do the same and we can bring the site back to life. I don't want to join all the peeps in the ruby crowd, I think it looks awful! =(

Rated 4* since it needed a little tweak, nothing against you, it was more my fault, happy to change if people think otherwise, like I said, I'm a n00b! :)

Cheers Rik!

[C#] On Common StProc Code

2008-04-02T15:25:45-07:00

Ah ha! Great find Rik, thanks, I have learned stuff today! :)
comm.Prepare() deleted!

Thanks again for your help! How people gauge the ratings on this forum? Would I be right in assuming 5* is perfect?)

[C#] On Common StProc Code

2008-04-02T15:09:10-07:00

OK - no problem, I will look into that more.
Here's what I have so far. The stored procedures that are on the server differ slightly depending on backup/restore, I don't really want to go messing this them, so I added a little code to enable the Copy method to flip things around in restore mode...

void CopyTables(List<TableNameResult> tables, int id, bool restore)
{
    using (SqlConnection conn = tgtConnStr_.CreateConnection())
    {
        // specify the sp to use and what db's are acting as source/target.
        // obviously if we are RESTORING, the data is coming from the specified
        // "Target" (i.e. snapshot DB), so we need to swap them over.
        string spName = restore ? "sp_RestoreSnapshotTable" : "sp_CopySourceTableToTargetTable";
        string src = restore ? tgtConnStr_.DatabaseName : srcConnStr_.DatabaseName;
        string tgt = restore ? srcConnStr_.DatabaseName : tgtConnStr_.DatabaseName;

        conn.Open();

        int tblCount = tables.Count;
        int currTbl = 0;

        foreach (TableNameResult tbl in tables)
        {
            RaiseOpProgressEvent(
                new OpEventArgs("Copying Table Data: '" + tbl.Table + "'", tblCount, currTbl += 1));

            CopyTable(conn, src, tgt, tbl, id, spName);
        }
    }
}

void CopyTable(SqlConnection conn, string srcDB, string tgtDB,
    TableNameResult tbl, int snapshotID, string spName)
{
    using (SqlCommand comm = new SqlCommand(spName, conn))
    {
        comm.CommandType = CommandType.StoredProcedure;
        comm.Prepare();

        comm.Parameters.AddWithValue("@source", srcDB);
        comm.Parameters.AddWithValue("@target", tgtDB);
        comm.Parameters.AddWithValue("@schema", tbl.User);
        comm.Parameters.AddWithValue("@table", tbl.Table);
        comm.Parameters.AddWithValue("@tableOID", tbl.ID);
        comm.Parameters.AddWithValue("@snapshotID", snapshotID);

        comm.ExecuteNonQuery();
    }
}

[C#] On Common StProc Code

2008-04-02T13:56:56-07:00

Hi Rik,
Thanks for the response, makes complete sense, I am making changes as I type! It certainly does read a lot better :) I never knew about AddWithValue, so thanks for that!

One question though, does calling comm.Prepare for each table lose the benefit of the execution preparation? I would have thought it needed to be called outside the loop?

[C#] Common StProc Code

2008-04-02T12:53:37-07:00

Hi guys, following my previous post to see if people still lurk here, I have decided to throw myself to the wolves and submit my first code post!

With this code, I need use it twice, each one needing to use a different Stored Procedure name. So this is fine, I can bust it up to be a parameter. I am postive there is a better way to express this.

I am curious to see if you guys can improve the readability, since although it makes sense, it kinda looks messy.

The TableNameResult is a object that simply brings in the table info from another query.

Please be nice, but please be honest, I am here to learn from you good people :)

Many Thanks and Kind Regards,
Rob the n00b

void CopySourceTables(List<TableNameResult> tables, int id)
{
    // Execute the "Copy" Command on the Server for Each Table.
    using (SqlConnection conn = tgtConnStr_.CreateConnection())
    {
        conn.Open();
        using (SqlCommand comm = new SqlCommand("sp_CopySourceTableToTargetTable", conn))
        {
            // create params.
            SqlParameter source = new SqlParameter("@source", SqlDbType.VarChar, 255);
            source.Value = srcConnStr_.DatabaseName;
            comm.Parameters.Add(source);
            SqlParameter target = new SqlParameter("@target", SqlDbType.VarChar, 255);
            target.Value = tgtConnStr_.DatabaseName;
            comm.Parameters.Add(target);
            SqlParameter schema = new SqlParameter("@schema", SqlDbType.VarChar, 255);
            comm.Parameters.Add(schema);
            SqlParameter table = new SqlParameter("@table", SqlDbType.VarChar, 255);
            comm.Parameters.Add(table);
            SqlParameter tableID = new SqlParameter("@tableOID", SqlDbType.Int, 0);
            comm.Parameters.Add(tableID);
            SqlParameter snapshotID = new SqlParameter("@snapshotID", SqlDbType.Int, 0);
            comm.Parameters.Add(snapshotID);

            comm.CommandType = CommandType.StoredProcedure;
            comm.Prepare();

            int tblCount = tables.Count;
            int currTbl = 0;

            foreach (TableNameResult tbl in tables)
            {
                schema.Value = tbl.User;
                table.Value = tbl.Table;
                tableID.Value = tbl.ID;
                snapshotID.Value = id;

                RaiseOpProgressEvent(new OpEventArgs("Copying Table Data: '" + table.Value + "'", tblCount, currTbl += 1));

                comm.ExecuteNonQuery();
            }
        }
    }
}

[C#] On Since I Cannot Message People...

2008-03-31T12:01:56-07:00

Hi Guys, thanks for the [prompt] responses!
Didn't post any code as I don't actually have any to hand that is causing my concern as yet (will review some of the code later, may have some on there!).

Thanks again, appreciate it, this site looks like it could be an excellent addition to my arsenal of "god I wanna be good at my job" tools :)

[C#] Since I Cannot Message People...

2008-03-31T11:03:32-07:00

Didn't/couldn't see how to message people, but this site looks like a great idea, and I could find it so useful on improving my "code smarter" abilities!

So put simply, just trying to see if this place is still active/used? If not? Will it ever? :)

Kind Regards,
Rob

bool Do_People_Still_Read_This
{
     // Not sure what to put here? "return true;" or "return false;" ?
}