- To backup sql server by command batch:
REM precondition: the current user has the permisstion to access the sql server
REM The first parameter: the data base name to be backed up
REM the second parameter: the path the backup files will be placed
SET DBNAME= %1
SET BACKUPPATH=% 2
osql -E -S . -d %DBNAME% -Q "backup database %DBNAME% to disk='%BACKUPPATH%%DBNAME%.bak' " - To start or stop a service by command batch in XP or Windows Server 2003 @ECHO off
REM Please input your service that you want to control
Set ServiceName=%1
REM stop
net stop %ServiceName%
REM start
net start %ServiceName% - To start or stop sqlserver service by command batch in XP or Windows Server 2003@ECHO off
Set ServiceName=MSSQLSERVER
REM stop
net stop %ServiceName%
REM start
net start %ServiceName%
- Serialize an object to a XML file or Deserialize a XML file as an object/// <summary>
/// To serialize an object to XML
/// namespace usings:
/// using System;
/// using System.Xml.Serialization;
/// using System.IO;
/// </summary>
/// <param name="obj">the object to be serialized</param>
/// <param name="type"> the type of the object</param>
/// <param name="xmlFullPath">the full path of the XML file to be created </param>
protected static void XmlSerialize( object obj, Type type, string xmlFullPath)
{
// create file
string path = xmlFullPath.Substring( 0, xmlFullPath.LastIndexOf('\\' ) + 1);
if (!Directory.Exists(path))
Directory.CreateDirectory(path);
FileStream stream = File.Create(xmlFullPath);
XmlSerializer serializer = new XmlSerializer(type);
serializer.Serialize(stream, obj);
stream.Close();
}
/// <summary>
/// Serialize a XML file as an object
/// namespace usings:
/// using System;
/// using System.Xml.Serialization;
/// using System.IO;
/// </summary>
/// <param name="xmlFullPath">the full path of the xml file </param>
/// <param name="type"> the type of the object to be created</param>
/// <returns>the created object </returns>
protected static object DeSerializeAsObj( string xmlFullPath,Type type)
{
FileStream stream = File.Open(xmlFullPath, FileMode.Open);
XmlSerializer serializer = new XmlSerializer(type);
return serializer.Deserialize(stream);
} - An Sql Helper Class
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
namespace SqlUtilities
{
public class SqlHelper
{
private static TimeSpan timeOut;
public static TimeSpan TimeOut
{
get{return timeOut;}
set { timeOut = value; }
}
/// <summary>
/// Here this method is unusable.
/// Because if you execute an store procedure with parameters by it, an exception will throw that
/// say you have not specify any parameter names
/// </summary>
/// <param name="connectionString">data base connection string</param>
/// <param name="spName"> store procedure name</param>
/// <param name="paramValues">parameter values </param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString ,
string spName,
params object[] paramValues)
{
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
//connection.
SqlCommand command = new SqlCommand(spName, connection);
//command.Connection.t
if( timeOut != null )
command.CommandTimeout = Convert.ToInt32(timeOut.TotalSeconds);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (object obj in paramValues)
{
System.Data.SqlClient.SqlParameter pa = command.CreateParameter();
pa.Value = obj;
command.Parameters.Add(pa);
//command.
}
return command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if(connection != null)
connection.Close();
}
}
/// <summary>
/// Here this method is unusable.
/// Because if you execute an store procedure with parameters by it, an exception will throw that
/// say you have not specify any parameter names
/// </summary>
/// <param name="connectionString"> data base connection string</param>
/// <param name="spName">store procedure name</param>
/// <param name="paramValues"> parameter values</param>
/// <returns></returns>
public static object ExecuteScalar(string connectionString,
string spName,
params object[] paramValues)
{
SqlConnection connection = null ;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
//connection.
SqlCommand command = new SqlCommand(spName, connection);
//command.Connection.t
if (timeOut != null )
command.CommandTimeout = Convert.ToInt32(timeOut.TotalSeconds);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach ( object obj in paramValues)
{
System.Data.SqlClient.SqlParameter pa = command.CreateParameter();
pa.Value = obj;
command.Parameters.Add(pa);
}
return command.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close ();
}
}
/// <summary>
/// This method is usable.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="spName"></param>
/// <param name="paramNames"></param>
/// <param name="paramValues"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString,
string spName,
string[] paramNames,
params object[] paramValues)
{
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
//connection.
SqlCommand command = new SqlCommand(spName, connection);
//command.Connection.t
if (timeOut != null)
command.CommandTimeout = Convert.ToInt32(timeOut.TotalSeconds);
command.CommandType = System.Data.CommandType.StoredProcedure;
for (int i = 0; paramValues != null && paramNames != null && i < paramNames.Length; i++)
{
System.Data.SqlClient.SqlParameter pa = command.CreateParameter();
pa.Value = paramValues[i];
pa.ParameterName = paramNames[i];
command.Parameters.Add(pa);
}
return command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close();
}
}
/// <summary>
/// This method is usable.
/// </summary>
/// <param name="connectionString"></param>
/// <param name="spName"></param>
/// <param name="paramNames"></param>
/// <param name="paramValues"></param>
/// <returns></returns>
public static object ExecuteScalar(string connectionString,
string spName,
string[] paramNames,
params object[] paramValues)
{
SqlConnection connection = null;
try
{
connection = new SqlConnection(connectionString);
connection.Open();
//connection.
SqlCommand command = new SqlCommand(spName, connection);
//command.Connection.t
if (timeOut != null)
command.CommandTimeout = Convert.ToInt32(timeOut.TotalSeconds);
command.CommandType = System.Data.CommandType.StoredProcedure;
for (int i = 0; paramValues != null && paramNames != null && i < paramNames.Length; i++)
{
System.Data.SqlClient.SqlParameter pa = command.CreateParameter();
pa.Value = paramValues[i];
pa.ParameterName = paramNames[i];
command.Parameters.Add(pa);
}
return command.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
connection.Close();
}
}
}
} - Get the current login user name in XP or Windows Server 2003 // Get the current login user name in XP or Windows Server 2003
string currentLoginUserName = System.Environment.UserName
--
Happy day, happy life!
No comments:
Post a Comment