Data Service that may be causing time out problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Service that may be causing time out problem

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-05-06 : 09:03:45

Perhaps some one can see something here that needs to be changed. this is the dataservice that is running the app and stored proc that is timing out on the first try from outside of the server. from the server it all works great.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web.Services;
using System.Web;
using System.ServiceModel.Activation;

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class DataService : IDataService
{
#region IDataService Members

public string GetDepartments(string customerName, string databaseName)
{
try
{
string connStringDB = string.Format("{0}ConnectionString", databaseName);
string connString = System.Configuration.ConfigurationManager.ConnectionStrings[connStringDB].ConnectionString;

//Create a connection to the SQL Server; modify the connection string for your environment.
SqlConnection conn = new SqlConnection(connString);

DataTable dataTable = new DataTable();
dataTable.TableName = "data";

SqlDataAdapter dataAdapter = new SqlDataAdapter("[SpendAnalyzer_GetDepartments]", conn);
dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
dataAdapter.SelectCommand.CommandTimeout=3600;

dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.VarChar, 50));
dataAdapter.SelectCommand.Parameters["@CustomerName"].Value = customerName;

dataAdapter.Fill(dataTable);

StringWriter sw = new StringWriter();
dataTable.WriteXml(sw);

dataAdapter.Dispose();
conn.Close();

return sw.ToString();
}
catch (Exception ex)
{
return null;
}
}

public string Get(string customerName, string databaseName)
{
/*
//Create a connection to the SQL Server; modify the connection string for your environment
//SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
SqlConnection sqlConn = new SqlConnection();//"server=(local);database=pubs;UID=myUser;PWD=myPassword;");

// Create a Command object, and then set the connection.
SqlCommand sqlCommand = new SqlCommand("procedureName", sqlConn);
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandTimeout=3600;

// Add params
SqlParameter sqlParam = new SqlParameter("@CustomerName", customerName);
sqlParam.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(sqlParam);

// Open the connection.
sqlCommand.Connection.Open();

// Run the SQL statement, and then get the returned rows to the DataReader.
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

// If any rows are returned, the stored procedure that you are trying
// to create already exists. Therefore, try to create the stored procedure
// only if it does not exist.
if (sqlDataReader.Read())
{
sqlDataReader.Close();
}

sqlCommand.Dispose(); //Dispose of the Command object.
sqlConn.Close(); //Close the connection.
*/
try
{
string connStringDB = string.Format("{0}ConnectionString", databaseName);
string connString = System.Configuration.ConfigurationManager.ConnectionStrings[connStringDB].ConnectionString;

//Create a connection to the SQL Server; modify the connection string for your environment.
SqlConnection conn = new SqlConnection(connString);

DataTable dataTable = new DataTable();
dataTable.TableName = "data";

SqlDataAdapter dataAdapter = new SqlDataAdapter("SpendAnalyzer", conn);
dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
dataAdapter.SelectCommand.CommandTimeout=3600;

dataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@CustomerName", SqlDbType.VarChar, 50));
dataAdapter.SelectCommand.Parameters["@CustomerName"].Value = customerName;

dataAdapter.Fill(dataTable);

StringWriter sw = new StringWriter();
dataTable.WriteXml(sw);

dataAdapter.Dispose();
conn.Close();

return sw.ToString();
}
catch (Exception ex)
{
return null;
}
}

[WebMethod(EnableSession = true)]
public string GetCustomerDatabaseNames(string username)
{
// username = "jeff";
//string username = (string)HttpContext.Current.Session["Username"];
if (string.IsNullOrEmpty(username))
{
return null;
}
try
{
string connString = System.Configuration.ConfigurationManager.ConnectionStrings["SecurityDBConnectionString"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(connString);

string sqlString = string.Format("SELECT CUSTOMERNAME as CustomerName, DATABASENAME as DatabaseName FROM vw_USERCUSTOMERS Where UserName = '{0}'", username);
DataTable dataTable = new DataTable();
dataTable.TableName = "data";

SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlString, sqlConn);
dataAdapter.Fill(dataTable);

StringWriter sw = new StringWriter();
dataTable.WriteXml(sw);

dataAdapter.Dispose();
sqlConn.Close();

return sw.ToString();
}
catch (Exception ex)
{
return null;
}
}

#endregion
}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources